Tablespace Disk Full

Scenario

All INSERT and UPDATE operations fail with ERROR: could not extend file "base/12345/54321": No space left on device. The DBA checks and finds the /data filesystem (where the default tablespace lives) is at 100% capacity. A nightly batch job that runs at 2 AM inserted 50 million rows into a logging table and filled the disk. PostgreSQL cannot extend any relation on the full filesystem.

How to Identify

Conditions:

• ERROR: could not extend file: No space left on device
• df -h shows the PostgreSQL data filesystem at 100%
• All write operations fail; read-only queries still succeed
• Autovacuum and WAL writes also fail if they need to extend files

Analysis Steps

-- Check database and table sizes
SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;

-- Find largest tables
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog','information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

-- Check tablespaces and their locations
SELECT spcname, pg_tablespace_location(oid) AS location,
       pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace;

-- Find temporary files (they consume space too)
SELECT * FROM pg_ls_tmpdir();

-- Check for unlogged tables or large indexes
SELECT relname, relkind, pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relpersistence != 'p'
ORDER BY pg_relation_size(oid) DESC;

Pitfalls

• You cannot DROP or TRUNCATE a table to free space if the filesystem is completely full — the DROP itself needs to write to transaction log (WAL). You need at least a few MB free first.
• VACUUM doesn’t reduce physical file size — it marks space as reusable but the file doesn’t shrink. VACUUM FULL or TRUNCATE can reduce size.
• Temporary files in pg_base/pgsql_tmp/ can fill the disk during sorts and hash operations. These can be safely deleted if PostgreSQL is stopped or if the backends using them are terminated.
• pg_dump output files written to the same filesystem will make the problem worse — always dump to a different filesystem.
• Creating a new tablespace on a different filesystem and moving tables is a medium-term fix.

Resolution Approach

Immediately free space by truncating or dropping the offending large table (after DBA confirms with business). If you can’t drop data: add a new disk/filesystem and create a tablespace there. Move large tables to the new tablespace.