Faster INSERTs: How to increase PostgreSQL write performance

UNLOGGED TABLE is a PostgreSQL feature introduced in the version 9.1 which allows to significantly increase the write performance. PostgreSQL is designed to be extremely protective of data which is the expected behaviour in most cases. By default, it tracks changes to tables using WAL (Write-Ahead Log). This operation carries an overhead (additional disk IO) which substantially reduces the write performance, but it guarantees that the table data is persisted in the event of an unclean shutdown. In other words, unlogged tables are automatically cleared if the server crashes (also their content does not propagate to replica servers).

Performance gains vary and are related to the table structure i.e. number of fields and indexes. Specific benchmarks should be performed to decide if it is a good idea to give unlogged tables a try for a particular case. Here are some scenarios where it could make sense to use that feature:

• large data sets (e.g. computation) which will be only used few times
• dynamic data with expiration date e.g. user sessions
• volatile data which can be easily regenerated

Unlogged tables are not the same as temporary tables. They are not dropped at the end of the current session/transaction and maintain atomicity, consistency and integrity properties i.e. the data will be persisted and not corrupted, but it may be lost in an event of a crash.

Create an unlogged table:

CREATE UNLOGGED TABLE name (
…
);

Since PostgreSQL 9.5 it is also possible to convert an existing table to unlogged one:

ALTER TABLE name SET UNLOGGED;

List all unlogged tables within the current database.

SELECT relname FROM pg_class WHERE relpersistence = 'u';