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
- PostgreSQL 9.5]
- logged/unlogged state of a table can be changed via
ALTER TABLE(f41872d0)
- logged/unlogged state of a table can be changed via
- PostgreSQL 9.1
- added (commit 53dbc27c)
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
References
- PostgreSQL documentation: CREATE TABLE
Useful links
- Unlogged table performance in postgresql 9.1 - systematic comparison of unlogged and normal tables by Michael Paquier