What does REPLICA IDENTITY FULL cost in WAL?
REPLICA IDENTITY FULL logging whole rows
Note: this works and blocks nothing, but a performance regression is likely.
What happens
REPLICA IDENTITY FULL writes the complete old row into WAL for every UPDATE and DELETE, and logical decoding must compare entire rows downstream. On write-heavy tables that is a permanent WAL and CPU tax; an index-based replica identity carries only the key.
Why it is dangerous on a populated table
Nothing blocks, but the cost scales with the table and shows up as a slower query plan or a longer maintenance window rather than an outage.
Fires on
ALTER TABLE orders REPLICA IDENTITY FULL;The safe pattern
Point the replica identity at a unique index (or keep the DEFAULT, which uses the primary key) so WAL carries only key columns for UPDATE/DELETE. Reserve FULL for tables that genuinely have no usable unique, non-partial, non-deferrable index, and treat that as the problem to fix.
SET lock_timeout = '5s';
ALTER TABLE orders REPLICA IDENTITY USING INDEX orders_pkey;
-- (with a primary key, REPLICA IDENTITY DEFAULT already does this)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)
ALTER TABLE orders REPLICA IDENTITY FULL;Stays silent (1)
ALTER TABLE orders REPLICA IDENTITY USING INDEX orders_pkey;How to check locally
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