DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 53100 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

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

PostgreSQL error code 53100 (disk_full) is raised whenever the database engine attempts to write data to disk — whether for table storage, WAL segments, or temporary files — and finds no available space remaining. This is one of the most operationally severe errors a PostgreSQL DBA can face: it immediately rolls back the current transaction and, if left unresolved, can bring the entire database cluster to a halt.

Top 3 Causes

1. Uncontrolled WAL Accumulation

WAL segments pile up when archiving lags behind, replication slots go stale, or wal_keep_size is set too high. An inactive replication slot is particularly dangerous because PostgreSQL must retain all WAL files since the slot's restart_lsn.

-- Check WAL directory size
SELECT pg_size_pretty(sum(size)) AS wal_dir_size
FROM pg_ls_waldir();

-- Identify stale replication slots
SELECT slot_name, active, restart_lsn,
       pg_size_pretty(
           pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal
FROM pg_replication_slots
WHERE NOT active;

-- Drop an unused slot (use with caution)
SELECT pg_drop_replication_slot('stale_slot_name');
Enter fullscreen mode Exit fullscreen mode

2. Table and Index Bloat

PostgreSQL's MVCC model keeps dead tuples on disk until VACUUM reclaims them. If autovacuum is disabled, too slow, or blocked by long-running transactions, tables can balloon to many times their logical size.

-- Find the most bloated tables
SELECT relname,
       n_dead_tup,
       n_live_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Reclaim space immediately (causes table lock)
VACUUM FULL VERBOSE public.bloated_table;

-- Tune autovacuum per table to be more aggressive
ALTER TABLE public.bloated_table
SET (autovacuum_vacuum_scale_factor = 0.01,
     autovacuum_vacuum_threshold = 50);
Enter fullscreen mode Exit fullscreen mode

3. Excessive Temporary File Creation

When work_mem is insufficient for sort, hash join, or aggregation operations, PostgreSQL spills intermediate results to temporary files under pg_base/pgsql_tmp/. With many concurrent sessions running heavy queries, this can exhaust disk space rapidly.

-- Find queries generating the most temp file I/O
SELECT query,
       calls,
       temp_blks_written,
       pg_size_pretty(temp_blks_written * 8192) AS temp_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

-- Increase work_mem for a specific session to reduce spill
SET work_mem = '256MB';

-- Log temp file creation to catch offenders early (postgresql.conf)
-- log_temp_files = 10MB
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

When you're already in a disk-full crisis, act in this order:

-- 1. Force a checkpoint to flush and recycle WAL
CHECKPOINT;

-- 2. Kill long-running transactions blocking VACUUM
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '30 minutes'
  AND pid <> pg_backend_pid();

-- 3. Run VACUUM on the worst offenders immediately
VACUUM (VERBOSE, ANALYZE) public.your_table;

-- 4. Check overall database size trend
SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;
Enter fullscreen mode Exit fullscreen mode

At the OS level, also check for and remove old server log files, orphaned core dumps, and completed WAL archive files to recover space quickly.

Prevention Tips

Monitor proactively. Set up disk usage alerts at 70% (warning) and 85% (critical) using tools like Prometheus with node_exporter, Zabbix, or Datadog. Schedule the following health-check query via pg_cron to run every hour:

SELECT
    (SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir())      AS wal_size,
    (SELECT count(*) FROM pg_replication_slots WHERE NOT active) AS dead_slots,
    (SELECT sum(n_dead_tup) FROM pg_stat_user_tables)           AS dead_tuples,
    (SELECT pg_size_pretty(pg_database_size(current_database()))) AS db_size;
Enter fullscreen mode Exit fullscreen mode

Tune autovacuum aggressively for large tables. Default autovacuum settings are conservative. Increase worker count and reduce cost delay so dead tuples are cleaned up before they accumulate:

ALTER SYSTEM SET autovacuum_max_workers = 5;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.02;
SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

Also, regularly audit and drop inactive replication slots, keep wal_keep_size at a reasonable value, and size your work_mem appropriately to minimize temp file spill during peak query periods.

📖 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.

Top comments (0)