PostgreSQL Unlogged Sequences - Look Mum, no WAL!

In an earlier post, I went into a lot of detail about unlogged tables. But tables are not the only thing to get the unlogged treatment - as of version 15 of Postgres, sequences can be unlogged as well! If you want to create your own, it's simply a matter of adding the UNLOGGED keyword to your CREATE SEQUENCE statement:

CREATE UNLOGGED SEQUENCE foo_seq;

The use case for unlogged sequences in Postgres is primarily to keep the sequence data for an unlogged table out of the WAL stream. Although unlogged tables provide a significant performance boost, unlogged sequences have no similar advantage. The usual unlogged caveats apply as well: sequences reset on database crash, are not usable on replicas, and are not saved in backups. So while you can create unlogged sequences, it's usually better not to worry about it, and let Postgres create them in the background as needed.

Unlogged sequences in Postgres generate no WAL

We can check for changes in the WAL files to see if unlogged sequences do as they claim, and emit zero WAL. The tricky thing about sequences is that while they do generate WAL information, it's a very tiny bit of WAL information! But we can use the pg_waldump program to check the WAL files directly.

## Store the location of the WAL directory into a shell variable:
$ export MYWALDIR=$( psql -Atc "select setting||'/pg_wal' from pg_settings where name = 'data_directory'" )
$ echo $MYWALDIR
/home/greg/pg/15/data/pg_wal
## Force a new WAL file to be created:
$ psql -c 'select pg_switch_wal()'
## Create a dummy file to compare timestamps against
$ touch gregtest

## Create an unlogged sequence
$ psql -c 'create unlogged sequence seq_no_wal'
## What has changed? Just a single WAL file
$ find $MYWALDIR -newer gregtest -size 16M
/home/greg/pg/15/data/pg_wal/000000010000001300000077
## Peek inside it and see what happened
$ find $MYWALDIR -newer gregtest -size 16M  | xargs pg_waldump 2>/dev/null | grep -c Sequence
1
$ find $MYWALDIR -newer gregtest -size 16M  | xargs pg_waldump 2>/dev/null | grep Sequence
rmgr: Sequence    len (rec/tot):     99/    99, tx:     538604, lsn: 13/7700B9A8, prev 13/7700B978,
  desc: LOG rel 1663/5/1015946, blkref #0: rel 1663/5/1015946 fork init blk 0

So as expected, we generated a single WAL entry, corresponding to the creation and definition of the sequence. What if we call nextval a bunch of times?

## Get a new value from our unlogged sequence ten thousand times
$ psql -c 'select nextval($$ seq_no_wal $$) from generate_series(1,10000)' >/dev/null
## No new WAL activity at all:
$ find $MYWALDIR -newer gregtest -size 16M  | xargs -n 1 pg_waldump 2>/dev/null | grep -c Sequence
1

Let's try the same thing with a normal, logged sequence:

$ psql -c 'create sequence seq_yes_wal'
$ find $MYWALDIR -newer gregtest -size 16M  | xargs -n 1 pg_waldump 2>/dev/null | grep -c Sequence
2
$ psql -c 'select nextval($$ seq_yes_wal $$) from generate_series(1,10000)' >/dev/null
$ find $MYWALDIR -newer gregtest -size 16M  | xargs -n 1 pg_waldump 2>/dev/null | grep -c Sequence
336

Yes, it's not 10,000 entries because WAL is never straightforward, but the important point is that we are generating WAL for this logged sequence! It also made me curious - what is the total rate of sequence calls? On my test system, it turns out that nextval can return about 2.1 million values per second. Not too shabby! At that rate, we would fill up a table with an integer primary key in 17 minutes (34 minutes if we also allow negative numbers). What about a bigint? It would take over 139 thousand years! Moral here: bigints are really, really big.

Unlogged sequences in Postgres have no performance gain

Despite being unlogged, sequences are already so blazingly fast that there is no performance gain in making them unlogged:

$ psql -c 'create sequence loggy_mc_loggyface'
$ psql -c 'explain analyze select nextval($$ loggy_mc_loggyface $$) from generate_series(1,123456)'
                                QUERY PLAN
----------------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..1543.20 rows=123456 width=8)
                                   (actual time=7.188..41.760 rows=123456 loops=1)
 Planning Time: 0.027 ms
 Execution Time: 44.617 ms

Normal sequences are pretty fast, right? Let's see how well an unlogged sequence does:

$ psql -c 'create unlogged sequence the_logless_wonder'
$ psql -c 'explain analyze select nextval($$ the_logless_wonder $$) from generate_series(1,123456)'
                                QUERY PLAN
----------------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..1543.20 rows=123456 width=8)
                                   (actual time=8.177..42.222 rows=123456 loops=1)
 Planning Time: 0.033 ms
 Execution Time: 45.330 ms

So even at 100,000 sequences, the time is pretty much exactly the same. Sequences are so fast that any gain they might get from being unlogged is practically unmeasurable.

Unlogged sequences in Postgres cannot be used on replicas

An unlogged sequence will not be usable on a Postgres replica, although its metadata is still replicated. Thus, we can describe an unlogged sequence on a replica, and get complete information about its initial state. Here, port 5515 is a Postgres primary, and port 7777 is its replica:

$ psql -p 5515 -c 'create unlogged sequence my_walfree_sequence'
CREATE SEQUENCE

$ psql -p 5515 -c '\d my_walfree_sequence'
                Unlogged sequence "public.my_walfree_sequence"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

$ psql -p 7777 -c '\d my_walfree_sequence'
                Unlogged sequence "public.my_walfree_sequence"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1

However, calling select, nextval, or setval on an unlogged sequence on a Postgres replica gives an error:

$ psql -p 5515 -c 'select * from my_walfree_sequence'
 last_value | log_cnt | is_called
------------+---------+-----------
          1 |      32 | t

$ psql -p 7777 -c 'select * from my_walfree_sequence'
ERROR:  cannot access temporary or unlogged relations during recovery

$ psql -p 5515 -c 'select nextval($$ my_walfree_sequence $$)'
 nextval
---------
       1

$ psql -p 7777 -c 'select nextval($$ my_walfree_sequence $$)'
ERROR:  cannot execute nextval() in a read-only transaction

$ psql -p 5515 -c 'select setval($$ my_walfree_sequence $$, 123)'
 setval
--------
    123

$ psql -p 7777 -c 'select setval($$ my_walfree_sequence $$, 123)'
ERROR:  cannot execute setval() in a read-only transaction

Interestingly enough, calling currval on an unlogged sequence returns the exact same error on the replica as on the primary - until you call the value inside the session on the primary:

$ psql -p 5515 -c 'select currval($$ my_walfree_sequence $$)'
ERROR:  currval of sequence "my_walfree_sequence" is not yet defined in this session

$ psql -p 7777 -c 'select currval($$ my_walfree_sequence $$)'
ERROR:  currval of sequence "my_walfree_sequence" is not yet defined in this session

$ psql -p 5515 -c 'select nextval($$ my_walfree_sequence $$), currval($$ my_walfree_sequence $$)'
 nextval | currval
---------+---------
     125 |     125

$ psql -p 7777 -c 'select nextval($$my_walfree_sequence$$), currval($$ my_walfree_sequence $$)'
ERROR:  cannot execute nextval() in a read-only transaction

Unlogged sequences in Postgres are not persistent

Just like unlogged tables, unlogged sequences lose any changes they have accumulated should the database restart after an unclean shutdown. While an unlogged table is truncated, an unlogged sequence is reset to its initial state, which is exactly what you want to happen if the unlogged sequence is associated with an unlogged table.

$ psql -c 'create unlogged sequence no_wal_here start with 123'
$ psql -c 'select nextval($$ no_wal_here $$) from generate_series(1,5)'
 nextval
---------
     123
     124
     125
     126
     127

$ pg_ctl stop -m immediate ## Never use this
$ pg_ctl start
$ psql -c 'select nextval($$ no_wal_here $$)'
 nextval
---------
     123

In the example above, a logged sequence would have returned 128, not 123

Unlogged sequences in Postgres are not backed up

Just like unlogged tables, there is no need to backup the current state of unlogged sequences, so programs like pgBackRest will exclude that information from the backup. Let's create two sequences, one regular and one unlogged, then advance them both by 500:

$ psql -c 'create sequence loggy' -c 'create unlogged sequence nologgy'

$ psql -c "select nextval('loggy'), nextval('nologgy') from generate_series(1,500)" >/dev/null

$ psql -c "select nextval('loggy'), nextval('nologgy')"
nextval | nextval
--------+---------
    501 |     501

Next we create a backup using pgBackRest:

$ pgbackrest --stanza=mydemo backup
2023-03-03 17:13:41 INFO: backup command begin 2.44: --archive-timeout=5 --backup-standby --exec-id=10775-fc2a19f1
  --log-level-console=info --log-level-file=detail --pg1-path=/home/greg/pg/15/data
  --pg2-path=/home/greg/pg/15/replica1 --pg1-port=5515 --pg2-port=7777
  --repo1-path=/var/lib/pgbackrest --repo1-retention-full=3 --stanza=mydemo --start-fast
2023-03-03 17:13:42 INFO: last backup label = 20230211-143150F_20230303-164230I, version = 2.41
2023-03-03 17:13:42 INFO: execute non-exclusive backup start:
                          backup begins after the requested immediate checkpoint completes
2023-03-03 17:13:43 INFO: backup start archive = 000000010000001300000086, lsn = 13/86000028
2023-03-03 17:13:43 INFO: wait for replay on the standby to reach 13/86000028
2023-03-03 17:13:43 INFO: replay on the standby reached 13/86000028
2023-03-03 17:13:43 INFO: check archive for prior segment 000000010000001300000085
2023-03-03 17:13:45 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-03-03 17:13:45 INFO: backup stop archive = 000000010000001300000086, lsn = 13/86000100
2023-03-03 17:13:45 INFO: check archive for segment(s) 000000010000001300000086:000000010000001300000086
2023-03-03 17:13:45 INFO: new backup label = 20230211-143150F_20230303-171342I
2023-03-03 17:13:46 INFO: incr backup size = 4.4MB, file total = 2869
2023-03-03 17:13:46 INFO: backup command end: completed successfully (4900ms)

Now we can turn around and restore the backup to a new directory, then start it up:

$ pgbackrest --stanza=mydemo restore --db-path=/tmp/testrestore --archive-mode=off
2023-03-03 17:15:54.481 INFO: restore command begin 2.44: --archive-mode=off --exec-id=10848-3d8ec129
  --log-level-console=info --log-level-file=detail --pg1-path=/tmp/testrestore
  --pg2-path=/home/greg/pg/15/replica1 --repo1-path=/var/lib/pgbackrest
  --stanza=mydemo
2023-03-03 17:15:54 INFO: repo1: restore backup set 20230211-143150F_20230303-171342I,
                          recovery will start at 2023-03-03 17:13:42
2023-03-03 17:15:54 INFO: remap data directory to '/tmp/testrestore'
2023-03-03 17:16:30 INFO: write updated /tmp/testrestore/postgresql.auto.conf
2023-03-03 17:16:30 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2023-03-03 17:16:30 INFO: restore size = 471.4MB, file total = 2869
2023-03-03 17:16:30 INFO: restore command end: completed successfully (36291ms)
$ echo port=4444 >> /tmp/testrestore/postgresql.conf

$ pg_ctl -D /tmp/testrestore/ start
server started

If we check the values, we see that the unlogged sequence's changes were not backed up:

$ psql -p 4444 -c "select nextval('loggy'), nextval('nologgy')"
 nextval | nextval
---------+---------
     502 |       1

Changing a Postgres logged sequence to unlogged is cheap

Cheap, but really not needed

$ alter sequence my_sequence set unlogged;
ALTER SEQUENCE

Super fast, and a trivial amount of change to the WAL stream.

Changing a Postgres unlogged sequence to logged is also cheap

$ alter sequence myseq abc set logged;
ALTER SEQUENCE

Also super fast, and also creates a very tiny WAL entry.

PostgreSQL unlogged sequences - final judgment

The ability to do unlogged sequences in Postgres is a welcome addition, but at the end of the day it's mostly for transparently supporting the unlogged tables that use them.