Why your idle Postgres database is quietly running out of disk

It is the middle of the night and your alerts are firing. Postgres is running out of disk. You log in, and the culprit is the pg_wal directory, growing by gigabytes.

The confusing part: this is a quiet database. A dev instance, or a service that handles a few queries an hour. Where is all the write-ahead log coming from?

When an idle database hoards WAL, something is pinning old segments so Postgres cannot recycle them. Here are the usual suspects, and one that catches people on managed cloud databases.

Suspect 1: a zombie replication slot

If you ever connected a replica, a pipeline or a CDC tool, it probably created a replication slot. Postgres makes a hard promise: it will not delete WAL until the slot's consumer confirms it has read it. If that consumer goes away, Postgres keeps the logs. Indefinitely.

Find the slots holding WAL back:

SELECT slot_name, plugin, slot_type, active,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

An active = false slot retaining gigabytes is your suspect. If nobody owns it, drop it:

SELECT pg_drop_replication_slot('your_abandoned_slot_name');

Suspect 2: broken WAL archiving

With archive_mode on, Postgres refuses to delete a WAL file until archive_command succeeds. A full backup bucket, a permissions change or a network blip stalls the archiver, and the files stack up.

SELECT * FROM pg_stat_archiver;

If last_failed_time keeps climbing, check the server logs and fix the destination path or credentials.

The idle database paradox

This one is sneaky, and it shows up on managed services like AWS RDS and Aurora when you use logical replication.

The platform writes its own housekeeping to the instance all the time. Those writes advance the instance's log position (LSN). But none of them touch your tables, so your replication slot sees nothing relevant to process. The consumer is not reading anything, so its confirmed position stays put while the database's position keeps moving. The gap widens, WAL piles up, and the database is completely idle from your application's point of view. (The team at Artie has a good write-up of this behavior on RDS.)

The fix: give the slot something to read

You need a small, regular write that the slot can see, so the consumer has something to acknowledge and the slot can move forward. There are two common ways:

• A heartbeat table. A tiny table that a scheduled statement upserts every so often. It works on any version, as long as the table is part of what the slot replicates.
• Logical messages (Postgres 14+). No extra table, just a message in the log. Your consumer has to be set up to read them.

-- Postgres 14+: advance the log without touching any table
SELECT pg_logical_emit_message(true, 'wal_advance', 'heartbeat');

Either way it is a job somebody has to run and monitor, ideally every minute or so. Cron on a bastion is the classic answer, and the classic thing to forget about when you migrate.

How Fabric handles it

We built Fabric so you do not have to babysit any of this. Every Postgres source runs a heartbeat by default, and there is nothing to configure:

• It writes on a timer. Every 30 seconds it upserts a single row into public.heartbeat, so an idle database still gives the slot something to read.
• It creates the table itself. The heartbeat table is created on start if it is missing, so there is no setup step to forget.
• It only confirms what is safe. After each heartbeat it tells Postgres it has flushed up to the last position it has durably checkpointed, never a position it has merely read. WAL is released without risking data loss.

The heartbeat is one row, once every 30 seconds. The alternative is a disk alert at 3 a.m.

It is Fabric's default — start for free, add your source, and the pulse is on.

Keep reading

• PostgreSQL docs: replication slots
• Postgres.ai: how to troubleshoot a growing pg_wal directory
• AWS: DiskFull errors on RDS for PostgreSQL
• Artie: unbounded WAL growth in idle RDS logical replication