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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store