PostgreSQL primary_slot_name behavior and replication risks explained

An analysis of the primary_slot_name parameter reveals how replication slots impact WAL retention and vacuum processes. If a standby disconnects while holding a slot, the upstream server continues to retain WAL files and prevents VACUUM from cleaning up dead rows. This can lead to disk exhaustion and significant table bloat if not managed via max_slot_wal_keep_size or idle_replication_slot_timeout.

Risks of standby slots in PostgreSQL

• WAL Retention: A slot forces the upstream to keep all WAL segments until the standby confirms receipt, potentially filling the primary's disk.
• Vacuum Interference: When using a slot, the standby's xmin is stored in pg_replication_slots; if the standby dies, this xmin persists, preventing VACUUM from removing dead rows.
• Double Leak: A dead standby with an active slot causes two types of leaks: WAL accumulation on disk and row bloat in tables.
• Configuration Failure: If primary_slot_name is configured with a non-existent name, the standby will fail to stream and enter a retry loop every five seconds.
• Mitigation: Use max_slot_wal_keep_size to cap WAL retention or idle_replication_slot_timeout to expire idle slots by time.

Why it matters

Database administrators must monitor pg_stat_wal_receiver and pg_replication_slots rather than just replay lag to detect misconfigured or abandoned standby slots.