SET LOGGED / SET UNLOGGED rewriting the table

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

What it catches

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

Fires on

ALTER TABLE staging_events SET LOGGED;

Do this instead

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

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: npx bolvrk check migration.sql

Browse all 69 rules

← BV025 BV027 →