A Dangerous Game in PostgreSQL: Unlogged Table

There is a tricky feature in PostgreSQL: Unlogged Table

I’ll quote the description of the Unlogged Table from PostgreSQL.org:

Data written to unlogged tables is not written to the write-ahead log, which makes them considerably faster than ordinary tables. However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown.

Sounds scary, right?

Photo by Killian Cartignies on Unsplash

Let’s see if it’s really terrifying

I created a PostgreSQL 9.6 instance on AWS (RDS) and created two tables:

The user_messages table is a standard table. The user_logs table is an unlogged table.

I added 1 million records into both tables.

I initiated the upgrade from 9.6 to 13.4 via RDS Management Console:

When the upgrade finished, I logged into the PostgreSQL instance:

Whoops… The data in the user_logs table is gone.

Let’s take a look at the logs:

Sounds like something went wrong during the upgrade. It happens, you know. PostgreSQL managed to keep the data of the user_messages table. However, the data in the user_logs table is gone.

Let’s recall the second sentence from the description of the Unlogged Table:

However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown.

Avoid using the Unlogged Tables to avoid data loss.