How to test unlogged tables for performance in PostgreSQL

 

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!