An unlogged PostgreSQL table keeps normal SQL transaction behavior while giving up crash recovery for its contents. It is useful for reproducible staging data, but a committed row is not guaranteed to survive a server crash. The distinction is between clean operation and recovery after an unclean shutdown, not between ordinary queries and every restart.

What bypasses WAL

The table’s data changes and those of its indexes are not written to the write-ahead log. Catalog changes still need WAL, and the database may produce WAL for other work in the same transaction. Calling an unlogged workload “zero WAL” therefore overstates the feature. Its pages also occupy storage and require normal buffer management; UNLOGGED does not mean an in-memory table.

Transactions, snapshots, constraints, and rollback still apply while the server runs. A rejected statement or a disconnected application does not automatically erase previously committed rows. A crashed backend can, however, cause the server to restart other processes and perform recovery. Avoid classifying incidents solely as “application” or “infrastructure”: determine whether PostgreSQL actually entered crash recovery.

Restart and replication boundaries

A clean shutdown preserves unlogged contents for the next start. PostgreSQL’s fast shutdown rolls back active transactions and shuts down cleanly; its immediate shutdown interrupts processes without that cleanup. The latter requires recovery, during which unlogged relations are reset. Their definitions survive, but previously committed rows do not. Normal maintenance must not be described as inevitably deleting them, nor should one successful clean restart be offered as evidence of crash safety.

Physical standbys do not receive the unlogged contents. While recovery is active, querying such a relation raises an error; it does not successfully return an empty result. After promotion, the relation can be read but has no replicated rows to recover. A failover runbook must repopulate it before consumers rely on it. Sharing a schema definition across nodes is different from replicating the table’s data.

A logged table cannot have a foreign key referencing an unlogged table. PostgreSQL rejects that definition because durable references cannot safely depend on rows that may disappear during recovery. The experiment checks this restriction instead of suggesting that ordinary durable child rows survive with silently broken references.

When it breaks

An ingestion pipeline may acknowledge a file once its rows reach an unlogged staging table, then delete the only source copy. A crash makes the acknowledgment irreversible data loss. Keep the source until the durable destination commits, and make the rebuild process identify exactly which input version it needs. Deduplication and checkpoints must belong to the durable part of that contract.

A reporting service can also fail when routed to a standby or started immediately after promotion. Empty-table detection alone misses the standby error, while blindly retrying cannot manufacture missing contents. Handle rebuilding as an explicit service state with admission checks. Test rebuild duration, upstream availability, and validation of the completed dataset; a script that merely creates an empty table is not recovery.

Check it quickly

Save the Bash block as unlogged.sh. It uses PostgreSQL 18 command-line tools as a non-root user. This container invocation supplies those tools without publishing ports or mounting host data:

docker run --rm --network none --user postgres --entrypoint bash -i \
  postgres:18-alpine < unlogged.sh

#!/usr/bin/env bash
set -euo pipefail
pg_lab=$(mktemp -d /tmp/unlogged-lab.XXXXXX)
cleanup() {
  for cluster in standby primary; do
    if [ -f "$pg_lab/$cluster/postmaster.pid" ]; then
      pg_ctl -D "$pg_lab/$cluster" -m immediate -w stop >/dev/null
    fi
  done
  rm -rf -- "$pg_lab"
}
trap cleanup EXIT
primary() { psql -X -h "$pg_lab" -p 55432 -d postgres -v ON_ERROR_STOP=1 "$@"; }
standby() { psql -X -h "$pg_lab" -p 55433 -d postgres -v ON_ERROR_STOP=1 "$@"; }
start_primary() {
  pg_ctl -D "$pg_lab/primary" -l "$pg_lab/primary.log" \
    -o "-k $pg_lab -p 55432 -c listen_addresses=''" -w start >/dev/null
}
initdb -D "$pg_lab/primary" --no-locale --encoding=UTF8 --auth=trust >/dev/null
start_primary
primary <<'SQL'
CREATE TABLE durable (id integer PRIMARY KEY);
CREATE UNLOGGED TABLE scratch (id integer PRIMARY KEY);
INSERT INTO durable VALUES (1);
INSERT INTO scratch VALUES (1);
BEGIN;
INSERT INTO scratch VALUES (2);
ROLLBACK;
DO $$
BEGIN
  EXECUTE 'CREATE TABLE durable_ref (id integer REFERENCES scratch(id))';
  RAISE EXCEPTION 'a logged table unexpectedly referenced an unlogged table';
EXCEPTION WHEN invalid_table_definition THEN
  RAISE NOTICE 'logged-to-unlogged foreign key rejected';
END $$;
SQL
counts='SELECT (SELECT count(*) FROM durable), (SELECT count(*) FROM scratch)'
test "$(primary -Atc "$counts")" = '1|1'
pg_ctl -D "$pg_lab/primary" -m fast -w stop >/dev/null
start_primary
test "$(primary -Atc "$counts")" = '1|1'
printf 'clean restart: 1|1\n'
pg_basebackup -h "$pg_lab" -p 55432 -D "$pg_lab/standby" -R -X stream -c fast
pg_ctl -D "$pg_lab/standby" -l "$pg_lab/standby.log" \
  -o "-k $pg_lab -p 55433 -c listen_addresses=''" -w start >/dev/null
test "$(standby -Atc 'SELECT pg_is_in_recovery()')" = t
test "$(standby -Atc 'SELECT count(*) FROM durable')" = 1
if standby -Atc 'SELECT count(*) FROM scratch' 2>"$pg_lab/read-error"; then
  echo 'unlogged read unexpectedly succeeded on standby' >&2
  exit 1
fi
grep -F 'cannot access temporary or unlogged relations during recovery' "$pg_lab/read-error"
pg_ctl -D "$pg_lab/standby" -w promote >/dev/null
test "$(standby -Atc "$counts")" = '1|0'
printf 'promoted standby: 1|0\n'
pg_ctl -D "$pg_lab/primary" -m immediate -w stop >/dev/null
start_primary
test "$(primary -Atc "$counts")" = '1|0'
printf 'crash recovery: 1|0\n'
primary -Atc 'SHOW server_version'
echo 'PASS: rollback, foreign key restriction, clean restart, standby, promotion, crash'

The primary and standby use separate Unix sockets inside one temporary directory. The trap stops only these experimental clusters and removes their files. The deliberate immediate shutdown affects the disposable primary, never an existing database. On PostgreSQL 18.6, rollback and clean restart leave counts 1|1; the recovery-mode standby rejects the scratch-table query. Promotion and primary crash recovery each produce 1|0. The exact error text is checked with the cluster’s C locale.

These observations establish lifecycle and constraint behavior, not a throughput improvement or a power-loss guarantee for every filesystem. Benchmark the real workload separately, including rebuild cost. Keep WAL and durability settings enabled for the durable destination; disabling them would invalidate the comparison.

Sources