Is an UNLOGGED table safe for real data?

UNLOGGED table holding data that cannot survive a crash

Note: this works and blocks nothing, but a performance regression is likely.

What happens

Unlogged tables skip WAL: writes are faster, but the table is truncated to empty on crash recovery and its contents never reach physical replicas. This will work, until the first failover or crash quietly empties it.

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

CREATE UNLOGGED TABLE session_cache (id bigint PRIMARY KEY);

The safe pattern

Default to a regular (logged) table: it survives crashes and reaches replicas. Keep UNLOGGED only for data you can rebuild from scratch without notice, and treat it as a cache: never the only copy, never referenced by logged data you cannot repair.

CREATE TABLE session_cache (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload jsonb
);
-- keep UNLOGGED only for data that is rebuildable at any moment

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)

unlogged

CREATE UNLOGGED TABLE session_cache (id bigint PRIMARY KEY, payload jsonb);

Stays silent (1)

logged table

CREATE TABLE session_cache (id bigint PRIMARY KEY, payload jsonb);

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

← BV022 BV024 →