Does SET LOGGED or SET UNLOGGED rewrite the table?

SET LOGGED / SET UNLOGGED rewriting the table

Warning: this works, but blocks traffic or rewrites data at scale.

What happens

Switching a table between LOGGED and UNLOGGED rewrites the whole table (SET LOGGED additionally writes every row to WAL) under ACCESS EXCLUSIVE.

Why it is dangerous on a populated table

It completes, but the time it holds its lock or rewrites rows scales with the table, and on a busy one that is long enough for traffic to queue.

Fires on

ALTER TABLE staging_events SET LOGGED;

The safe pattern

Create tables with the right durability from the start. To promote an existing large UNLOGGED table, create a logged copy, dual-write, batch-copy the rows, and swap names, the in-place SET LOGGED rewrite (which also WAL-logs every row) is only reasonable for small tables or a maintenance window, guarded by lock_timeout.

-- small table or maintenance window, guarded:
--    SET lock_timeout = '5s';
--    ALTER TABLE staging_events SET LOGGED;
-- large + hot: create a logged twin, dual-write, batch-copy, swap names

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (1)

set logged

ALTER TABLE staging_events SET LOGGED;

Stays silent (1)

plain alter

ALTER TABLE staging_events ADD COLUMN note text;

How to check locally

Catch this before it ships

This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BV025 BV027 →