TITLE : Unlogged tables
HEADER : When performance is more important than durability, unlogged tables provide a way to improve performance while keeping the data managed within PostgreSQL. Removing logging reduces I/O overhead, yielding performance improvements up to 10 times faster when compared to logged tables. Scenarios expected to leverage unlogged tables include web session data, real time logging, ETL and temporary/intermediate tables for functions.
Introduced in PostgreSQL 9.1, unlogged tables are designed for ephemeral data. They are much faster on writes, but won't survive a crash (it will be truncated at database restart in case of a crash). Currently, GiST indexes are not supported on unlogged tables, and cannot be created on them.
They don't have the WAL maintenance overhead, so they are much faster to write to. Here is a (non-realistic) example:
First let's create an unlogged relation:
# CREATE UNLOGGED TABLE testu (a int); CREATE TABLE
Now for comparison purposes, we'll create a second table – identical, but logged:
# CREATE TABLE test (a int); CREATE TABLE
Let's put an index on each table:
# CREATE INDEX idx_test on test (a); CREATE INDEX # CREATE INDEX idx_testu on testu (a ); CREATE INDEX
Now we'll see how faster we can go :
=# \timing Timing is on. =# INSERT INTO test SELECT generate_series(1,1000000); INSERT 0 1000000 Time: 17601,201 ms =# INSERT INTO testu SELECT generate_series(1,1000000); INSERT 0 1000000 Time: 3439,982 ms
With this example, the unlogged table is 5 times faster than the regular one. Even when using the COPY command, the write performance is much faster with unlogged tables.
SUBHEADER : Performances are greatly improved for ephemeral data
But remember ! If your PostgreSQL crashes you will lose all the content , even if you force a checkpoint :
$ cat test.sql INSERT INTO testu VALUES (1); CHECKPOINT INSERT INTO testu VALUES (2);
$ psql -f test.sql ; killall -9 postgres postmaster INSERT 0 1 CHECKPOINT INSERT 0 1 postmaster: no process found
The server was forced to shutdown immediatly. And after restart:
# select * from testu; a ----------- (0 rows)
Many “NoSQL” engines advertise a speed advantage over “traditional” RDBMS engines, by employing a similar unlogged approach. PostgreSQL 9.1 provides an option, allowing a database designer to choose between performance and crash safety for tables in the same database, without having to abandon the rich feature set provided by PostgreSQL.
In other words : unlogged tables are very efficient for caching data, or for anything that can be rebuilt in case of a crash.