One word in a CREATE TABLE statement took our nightly staging load from 40 minutes to 9: UNLOGGED. The pipeline bulk-copied half a billion rows of raw events, transformed them, and loaded the results into permanent tables — textbook scratch work. Two months later the primary had a hardware fault, we failed over, and the staging table on the new primary was empty. Not stale, not partially replicated — empty. A confused hour followed, because nobody on the on-call rotation had internalized what unlogged actually promises. The semantics are simple and they are absolute; the trouble is that most people learn them from a benchmark post that mentions speed and not much else.
The mechanism, precisely: an unlogged table's heap writes skip the write-ahead log. That is the entire feature and also the entire catch. The table still lives in shared buffers, its dirty pages are still written to data files and fsynced by the checkpointer, and autovacuum still visits it. What it does not have is the redo trail — no WAL records describing its inserts, updates, or deletes. Everything else about unlogged semantics is a consequence of that one absence, and once you hold the absence in mind, every supposedly surprising behavior becomes obvious.
What actually happens to an unlogged table after a crash?
Crash recovery truncates every unlogged table to zero rows. Not "may lose recent writes" — the table is reset to empty, by design, every time the cluster recovers from an unclean shutdown. The implementation is almost elegant in its brutality: every unlogged relation has an init fork, an empty template fork, and recovery re-derives the relation from that init fork before replay begins. Since there are no WAL records to replay for it, empty is the only consistent state the engine can offer. Your rows are not recoverable, and no recovery tool will change that.
The complement is just as important and half as well known: a clean shutdown is safe. The shutdown checkpoint flushes the table's dirty pages to durable storage, so a stopped-and-restarted cluster keeps its unlogged data. "Shutdown-safe but not crash-safe" is the one-line summary I give every team. It also means an operating-system crash, a kill -9, an OOM kill of the postmaster, a failover that promotes a replica — all of these are crashes from the table's perspective. Plan for zero rows after any event that was not a polite pg_ctl stop.
Why is the unlogged table empty on your streaming replica?
Because there is nothing to ship. Physical replication works by replaying WAL, and an unlogged table writes none, so its contents never cross the wire. The standby's catalog knows the table exists — catalogs are logged — but querying it there does not return zero rows; it errors out: cannot access temporary or unlogged relations during recovery, because in replay the standby can only offer the init fork. Promote that standby and the error turns into silence: the table is initialized from the init fork and you are holding an empty one. Logical replication cannot save you either: logical decoding reads WAL, and there is nothing to decode.
This breaks a pattern I see constantly: staging or rollup tables that reporting queries read from a hot standby to offload the primary. The queries fail instantly with that error, and the dashboard looks broken in the specific way that produces a "the replica is lagging" ticket first and a much worse realization later. If data must be readable on any node besides the primary that wrote it, that data cannot live in an unlogged table. Full stop.
Where does skipping WAL actually pay?
Anywhere the data is disposable or rebuildable, which covers more of a real workload than people admit. ETL and staging tables are the canonical case: land the raw extract unlogged, transform it into logged permanent tables, and truncate. The win is not only insert speed — on bulk COPY I typically measure two to four times the throughput, because every heap write used to generate a WAL record that had to be serialized, flushed, archived, and shipped to every standby. Skip the WAL and you also shrink the archive, cut replication bandwidth, and take pressure off the checkpointer. The speedup is a system effect, not a microbenchmark artifact.
Rebuildable caches and materialized rollups are the second honest use: precomputed reporting tables you can regenerate from source data in a bounded time. Session scratch is the third — though plain temporary tables usually serve sessions better, since they are session-local and skip WAL by nature. My line for everything else: if you cannot rebuild the contents in an hour from data you still have, the table must be logged. Audit-adjacent data, anything downstream consumers treat as a source of truth, anything you would need after a failover — all logged. The forty-to-nine-minute pipeline is the trade working as intended; an empty table after a failover that somebody needed is the trade collecting its fee.
Is ALTER TABLE ... SET LOGGED a cheap migration path?
No — it is a full table rewrite with a WAL burst roughly the size of the table, under an ACCESS EXCLUSIVE lock. The engine rebuilds the heap and, since the result is now logged, writes WAL for every row it copies, so the operation generates as much write-ahead traffic as a bulk load of the same data. I have watched a SET LOGGED on a 90 GB staging table saturate the archive pipeline, push standbys into lag, and leave pg_wal's archive_status directory full of .ready files for twenty minutes. It also blocks everything touching the table for the duration.
The practical playbook for converting a big table: create a new logged table, copy in batches or with one off-peak INSERT ... SELECT, verify, then swap names inside a short transaction — or schedule SET LOGGED for the quietest window you have and warn whoever watches the replication dashboards first. Going the other direction, SET UNLOGGED, rewrites the table too but emits no WAL for the copy, so it is the cheaper direction by exactly that burst. Either way, check what is unlogged on your cluster before you rely on any of it; the census is one query against pg_class.
-- census: every unlogged ordinary table in the database
SELECT n.nspname AS schema, c.relname, c.relpersistence,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND c.relpersistence = 'u'
ORDER BY pg_total_relation_size(c.oid) DESC;
ALTER TABLE staging.raw_events SET LOGGED; -- full rewrite + WAL burstrelpersistence is 'p' for permanent, 'u' for unlogged, 't' for temporary. Run that census on every cluster you inherit; finding an unlogged table holding something precious, weeks before the crash that would have emptied it, is the cheapest save in this entire piece.
Watching unlogged tables with MonPG
MonPG monitors PostgreSQL in production today, and the signals around this feature are the ones it already graphs: WAL generation rate, archive throughput, checkpoint spread, and per-table size growth — so a SET LOGGED burst shows up as the WAL spike it is, and a quietly growing unlogged table is visible before someone builds a dependency on it. The PostgreSQL monitoring surface covers how those counters are collected, and the notes on checkpoint tuning explain the flush behavior that makes clean restarts safe. Unlogged tables are a good deal. They are just a deal with specific terms, and the terms are not negotiable.