PostgreSQL 53100 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 53100: disk full — Causes, Fixes & Prevention

PostgreSQL error code 53100 (disk full) is a critical resource-exhaustion error thrown when the database server cannot write data to disk because the underlying filesystem has no remaining space. This error belongs to Class 53 ("Insufficient Resources") and can crash active transactions, halt autovacuum, and in severe cases bring down the entire PostgreSQL cluster. Unlike most query errors, disk full demands immediate operational response — every second of delay risks further data corruption.

Top 3 Causes

1. Replication Slot WAL Accumulation

Inactive replication slots are the single most common cause of sudden disk full events in production. When a logical or physical replication slot's consumer disconnects, PostgreSQL retains all WAL files needed to resume that consumer — indefinitely.

-- Identify slots hoarding WAL space
SELECT slot_name,
       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;

-- Drop an unused slot after verifying it is safe to do so
SELECT pg_drop_replication_slot('stale_slot_name');

2. Temporary File Explosion from Large Queries

When sort, hash join, or aggregation operations exceed work_mem, PostgreSQL spills to disk under $PGDATA/base/pgsql_tmp. Multiple concurrent heavy queries can fill the disk rapidly without any obvious warning.

-- Check how much temp space each database is consuming
SELECT datname,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_disk_used
FROM pg_stat_database
ORDER BY temp_bytes DESC;

-- Find currently running queries generating temp files
SELECT pid,
       usename,
       state,
       pg_size_pretty(temp_blks_written * 8192) AS est_temp_written,
       left(query, 120) AS query_snippet
FROM pg_stat_activity
WHERE state = 'active'
  AND temp_blks_written > 0
ORDER BY temp_blks_written DESC;

3. Table Bloat from Unvacuumed Dead Tuples

Heavy UPDATE and DELETE workloads produce dead tuples that inflate table and index sizes until VACUUM reclaims them. If autovacuum is misconfigured or disabled on large tables, bloat can consume tens of gigabytes silently.

-- Identify the most bloated tables
SELECT schemaname,
       tablename,
       n_dead_tup,
       n_live_tup,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
       round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup,0)*100,1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 5000
ORDER BY n_dead_tup DESC
LIMIT 15;

-- Reclaim space on a bloated table
VACUUM VERBOSE ANALYZE public.your_table;

-- Full reclaim (requires exclusive lock — use during maintenance window)
VACUUM FULL public.your_table;

Quick Fix Solutions

-- 1. Immediately cancel long-running queries consuming temp space
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '30 minutes';

-- 2. Check WAL archive lag (slow archiver = WAL pile-up)
SELECT last_archived_wal,
       last_failed_wal,
       pg_size_pretty(
           pg_wal_lsn_diff(pg_current_wal_lsn(),
               pg_lsn(last_archived_wal))
       ) AS archive_lag
FROM pg_stat_archiver;

-- 3. Confirm overall database sizes
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

Beyond SQL, at the OS level:

Run df -h to identify which filesystem is full ($PGDATA, WAL, or tablespace).
Use du -sh $PGDATA/pg_wal to confirm WAL accumulation.
Remove old OS-level log files or unused tablespace files after careful verification.

Prevention Tips

Monitor proactively with alerting thresholds.
Set up Prometheus with postgres_exporter or a custom cron job to alert when disk usage exceeds 70% (warning) and 85% (critical). Include WAL directory size and temp_bytes from pg_stat_database in your metrics.

Audit replication slots and autovacuum daily.
Add a daily check of pg_replication_slots to your runbook — any slot with active = false for more than a few minutes needs investigation. Likewise, ensure no critical tables have autovacuum_enabled = false set, and tune autovacuum_vacuum_scale_factor downward (e.g., 0.01) for large, frequently updated tables to keep dead tuple accumulation under control.

-- Daily slot health check (add to monitoring)
SELECT slot_name, active, slot_type,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained
FROM pg_replication_slots
WHERE active = false;

-- Tables with autovacuum disabled
SELECT schemaname, tablename, reloptions
FROM pg_tables
JOIN pg_class ON relname = tablename
WHERE reloptions::text LIKE '%autovacuum_enabled=false%';

Error 53100 is preventable with consistent monitoring and disciplined slot management. Treat disk capacity as a first-class operational metric — not an afterthought.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community