Lead Image © Nuno Andre, 123RF.com

New in PostgreSQL 9.3

Database Tuning

In version 9.3, the PostgreSQL community once again introduces several important new features in a major release. The work of many developers worldwide, this release went through a rigorous review process (Commitfest) that ensures the quality of the source code. Typically, Commitfests see up to 100 patches; because of the sheer volume, the feature freeze deadline was regularly postponed. To compensate, the list of new features is impressive [1]. In this article, I present the most important ones.

Parallel Dump

A feature long requested by users and developers is the ability to perform a simultaneous database dump using multiple threads or processes. PostgreSQL uses a multiprocess architecture, which means it does not use threads (not even on platforms such as Windows, which actually prefers them). To dump multiple objects in parallel with pg_dump, the program must therefore open multiple database connections. However, because pg_dump guarantees the consistency of a dump, even for concurrent transactions, the database connections that belong to pg_dump now need to be synchronized. This was what made developing this feature tricky.

The infrastructure implemented to handle this process is called "snapshot cloning," which makes it possible for mutual synchronization of several transactions to the same state, although they use separate database connections. If you were to back up tables in parallel, and if these pg_dump transactions were not synchronized, data consistency between the tables could not be guaranteed. In this case, it would be possible for the concurrent processes to store new data in one of the tables during backup. Enter snapshot synchronization.

Basically, a transaction must first be started and can then be synchronized. All other transactions then import the

Buy ADMIN Magazine

SINGLE ISSUES

Print Issues

Digital Issues

 

SUBSCRIPTIONS

Print Subs

Digisubs

 

TABLET & SMARTPHONE APPS

US / Canada

UK / Australia