Database Tuning

Improved Concurrency for Foreign Keys

The FOR SHARE or FOR UPDATE lock types have been used for changes to data relying on foreign keys to refererence tables. Unfortunately, this led to massive locking for many concurrent changes, which could impair application speed significantly.

PostgreSQL 9.3 offers two new lock types: FOR KEY SHARE and FOR NO KEY UPDATE. These lock types do not block each other. Now, if a tuple that has a foreign key is updated and the key is not a part of the update, a FOR NO KEY UPDATE lock request is issued. Tests of foreign key integrity have always been implemented as implicit triggers in PostgreSQL. Now, instead of FOR SHARE, they use FOR KEY SHARE, which speeds up most applications that have this kind of database requirement profile. In general, foreign key values are only rarely updated as such.

Background Worker API

For quite some time, the PostgreSQL community has been thinking about background processes that can be started in addition to the normal database connections – also separate processes – and can handle dedicated tasks, such as the periodic execution of specific tasks or commands.

PostgreSQL 9.3 provides the infrastructure and API necessary to implement background processes. A reference implementation is provided in the worker_spi contrib module, where interested developers can study the required steps.

Background processes launch when the database instance starts up and remain active throughout the duration of the instance. If a background process terminates, it is immediately restarted by the PostgreSQL database server.

Conclusions

The new features and additions described in this article represent the most important and far-reaching changes in PostgreSQL 9.3 [2]. A much larger number of smaller, but still significant, changes have been made, as well, especially related to speed improvements of the database server or streaming replication. These improvements alone are reason enough to justify taking a look at the new version.

The JSON data type introduced in PostgreSQL 9.2 now has a comprehensive set of additional functions for data access and manipulation. This functionality, in particular, simplifies interaction between interactive web applications and the database, for example, via scripting languages that themselves have extensive JSON functionality.

With the advent of DML-enabled FDWs in particular, PostgreSQL 9.3 has developed into a multifunction tool in heterogeneous, distributed database environments.