WARNING: Unlogged tables are not crash safe. In case of a crash or without a complete shutdown, an unlogged table is automatically truncated.
In this post, we are going to see what an unlogged table in PostgreSQL is, why we need it, and how to test unlogged tables for performance.
This feature is implemented starting with PostgreSQL version 9.1. If we specify an unlogged keyword while creating the table, then we can say the table is created as an unlogged table:
postgres=# create unlogged table test(n int,n1 int);
CREATE TABLE
postgres=# \d test
Unlogged table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
n | integer | | |
n1 | integer | | |
Data written to unlogged tables is not recorded to the WAL (write-ahead log), which makes it faster than ordinary tables and increases the write performance.
Unlogged vs. Ordinary Tables
Let's assume we have two tables (one is an unlogged table and the other is ordinary table) and see how much time an INSERT operation takes.
- Unlogged table INSERT operation
postgres=# \timing
Timing is on.
postgres=# insert into test values (generate_series(1,1000000));
INSERT 0 1000000
Time: 527.246 ms
postgres=# insert into test values (generate_series(1,1000000));
INSERT 0 1000000
Time: 511.469 ms
postgres=# insert into test values (generate_series(1,1000000));
INSERT 0 1000000
Time: 533.142 ms
- Ordinary table (or we can say logged table) INSERT operation
postgres=# create table test3(n int,n1 int);
CREATE TABLE
postgres=# insert into test3 values (generate_series(1,1000000));
INSERT 0 1000000
Time: 1189.840 ms (00:01.190)
postgres=# insert into test3 values (generate_series(1,1000000));
INSERT 0 1000000
Time: 1165.808 ms (00:01.166)
postgres=# insert into test3 values (generate_series(1,1000000));
INSERT 0 1000000
Time: 1148.615 ms (00:01.149)
We can see that ordinary table insert operations take more time than unlogged tables.
Unlogged vs. Temporary Tables
Unlogged tables are not the same as temporary tables. They don’t vanish after the end of the session.
Crash Protection
Unlogged tables are not crash safe. In case of a crash or without a complete shutdown, an unlogged table is automatically truncated.
Example
Restart the server using -mi (quit without complete shutdown):
-bash-4.2$ ./pg_ctl -D /tmp/data/ restart -m i
If we again connect to the psql terminal and check the count of the unlogged table test, we will see it is 0:
postgres=# select count(*) from test;
count
-------
0
(1 row)
Converting Ordinary Tables to Unlogged Tables
From PG v. 9.5 onwards, we have the option to convert an ordinary table into unlogged table using ‘Alter table’ command
postgres=# alter table test3 set unlogged;
ALTER TABLE
postgres=#
Checking Unlogged Table Data
We can identify all the unlogged tables from the pg_class system table:
postgres=# SELECT relname FROM pg_class WHERE relpersistence = 'u';
relname
---------
test
test3
(2 rows)
postgres=#
We cannot access data from the unlogged table on standby servers:
postgres=# select count(*) from test;
ERROR: cannot access temporary or unlogged relations during recovery
postgres=#
Also, indexes created on an unlogged table are automatically unlogged as well.
For more information, please refer to the online PostgreSQL documentation,
https://www.postgresql.org/docs/12/sql-createtable.html.
Hope it helps!