Unlogged table

Table type with WAL logging disabled, improving write performance at the expense of reliability

An unlogged table is a type of a table where data written to it will not be written to write-ahead logs. This improves write speed considerably compared to normal tables. However, unlogged tables are not crash-safe and will be automatically truncated following a server crash or unclean shutdown. Unlogged tables will also not be replicated.

Unlogged tables were introduced in PostgreSQL 9.1. From PostgreSQL 9.5, ALTER TABLE can be used to change a normal table to an unlogged one, and vice-versa.

Change history

Examples

Unlogged table insert speed compared with normal table

Note: the below example is very simplistic and serves only to show that inserts on unlogged tables are faster than on logged tables, but not to show a particular speed ratio.

test=# CREATE UNLOGGED TABLE unlogged_table (id int);
CREATE TABLE

test=# CREATE TABLE normal_table (id int);
CREATE TABLE

test=# \timing 
Timing is on.

test=# INSERT INTO unlogged_table (id) SELECT generate_series(1,10000);
INSERT 0 10000
Time: 7.107 ms

test=# INSERT INTO normal_table (id) SELECT generate_series(1,10000);
INSERT 0 10000
Time: 32.574 ms

Categories

DDL, Storage

See also

Initialization fork