PostgreSQL streaming replication characteristics on UNLOGGED tables

Ever wondered what happens if you switch an UNLOGGED PostgreSQL table to LOGGED while replication is active? Probably not, but now that you’re here you might as well find out.

Photo by Timelab on Unsplash – WAL shipping in action?

This is a bit of a rabbit hole, so let me explain. In brief: streaming physical replication ships the WALs from the primary to the standby servers. The documentation states that UNLOGGED tables are not written to the WALs¹, and therefore, their contents are not replicated to the standby servers (the WAL absence is the same reason their consistency cannot be guaranteed following a crash). For reasons as to why UNLOGGED tables are handy, think of things like caches, and intermediary (but eventually verifiable) imports.

Now what happens if we create an UNLOGGED table on a replicated setup, fill it with a bunch of data, and then run SET LOGGED²? Does it send all the data in the table to the standby instance, or does it do nothing on the standby? What happens when we switch it back off again? Stay tuned.

In case you’re wondering why this is a useful experiment: imagine you’re bulk loading a bunch of data. Setting the bulk load table to LOGGED might mean wasting a lot of time on the COPY even though you can easily replace it should anything go wrong later. Once the bulk load is done, you can switch it back on. Similarly, if you have data consumers on the standby side, the data wouldn’t be visible until you’re done loading everything, and the replication wouldn’t be kept busy until you found a quiet time to manually kick off the sync.

Setting things up

Let’s create our simple candidate table:

postgres=# create unlogged table t (id serial, value int); CREATE TABLE postgres=# \d List of relations Schema | Name | Type | Owner --------+----------+----------+---------- public | t | table | postgres public | t_id_seq | sequence | postgres (2 rows)

And fill it with some data:

postgres=# insert into t(value) select a from generate_series(1, 10_000_000) a;
INSERT 0 10000000

Ten million records ready to go. Now let’s have a look on a standby machine:

postgres=# \d List of relations Schema | Name | Type | Owner --------+----------+----------+---------- public | t | table | postgres public | t_id_seq | sequence | postgres (2 rows) postgres=# select * from t; ERROR: cannot access temporary or unlogged relations during recovery

So it’s visible on the standby, but cannot be queried. This makes sense, as the meta tables containing the schema are logged, and so they’re visible on the standby³.

Switching the logging on, mid flight

Now we can alter our table and set it to logged:

postgres=# alter table t set logged ; ALTER TABLE

It’s immediately noticeable that this takes a while.

Looking at the replicas, the streaming lag caused by the WAL generation is also apparent:

pid | usename | client_addr | state | replay_lag -----+-------------------+-------------+-----------+------------ 305 | streaming_replica | 10.244.0.7 | streaming | 529 | streaming_replica | 10.244.0.9 | streaming | (2 rows) ... pid | usename | client_addr | state | replay_lag -----+-------------------+-------------+-----------+----------------- 305 | streaming_replica | 10.244.0.7 | streaming | 00:00:00.54102 529 | streaming_replica | 10.244.0.9 | streaming | 00:00:00.540827 (2 rows)

This eventually settles down to no replay lag again. This means that:

Switching a table to logged immediately generates the WALs on the primary, which are then shipped off to the replicas.

We can now also query the table from the standby:

postgres=# select count(*) from t; count ---------- 10000000 (1 row)

Let’s switch on the query timing and have a look at it in more detail.

postgres=# \timing Timing is on.

And now let’s switch it on and off again:

postgres=# alter table t set logged ; ALTER TABLE Time: 172432.314 ms (02:52.432) postgres=# alter table t set unlogged ; ALTER TABLE Time: 75150.777 ms (01:15.151)

Generating the WALs for my ten million records takes about three minutes on this machine, and removing them takes about a third of that time. When we switch it off, the table becomes unqueryable on the standby again.

Another matter I found interesting is that, during the switch from unlogged to logged, attempting to query the table from the standby hangs until it is done.

In hindsight, the behaviour seems obvious, but this aspect of PostgreSQL in this situation is undocumented (in fact, at least from my point of view, the behaviour of unlogged tables is largely undocumented aside from the WAL detail). There’s e.g. the possiblity to set a table to logged, but it’s sequence to unlogged⁴. This seems quite unusual, and the implications of that are not clear to me. Perhaps it would be good to expand testing to such cases in the future.

---

For completeness’ sake, here’s the usual logged vs unlogged insert performance test, since everyone always brings it up when unlogged tables are mentioned:

postgres=# insert into t(value) select a from generate_series(1, 1_000_000) a; INSERT 0 1000000 Time: 955.216 ms postgres=# truncate table t; TRUNCATE TABLE Time: 6.227 ms postgres=# alter table t set logged ; ALTER TABLE Time: 5.508 ms postgres=# insert into t(value) select a from generate_series(1, 1_000_000) a; INSERT 0 1000000 Time: 1524.860 ms (00:01.525)

The usual result, then.