could not extend file: No space left on device

This error occurs when PostgreSQL runs out of disk space. Learn how to diagnose and prevent disk space issues.

The No space left on device error means PostgreSQL can't write because the disk is full.

Understanding the Error

ERROR: could not extend file "base/16384/12345": No space left on device
HINT: Check free disk space.

The disk or partition where PostgreSQL stores data is out of space.

Common Causes

1. Large Tables Growing

Tables growing faster than expected due to data volume.

2. WAL Files Accumulating

Write-ahead log files building up, often from replication lag.

3. Temp Files from Large Queries

SQL

-- Queries creating large temporary files
SELECT * FROM huge_table ORDER BY random();

4. Unvacuumed Dead Tuples

Dead rows not being cleaned up by autovacuum.

5. Log Files Growing

PostgreSQL logs filling up the disk.

How to Fix It

Solution 1: Check Disk Usage

BASH

# Check overall disk space
df -h

# Check PostgreSQL data directory size
du -sh /var/lib/postgresql/data/

# Find large files
du -sh /var/lib/postgresql/data/* | sort -h

Solution 2: Clean Up WAL Files

SQL

-- Check WAL retention
SHOW wal_keep_size;

-- If using replication, check lag
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;

-- Remove old WAL files (if safe)
-- Reduce wal_keep_size in postgresql.conf

Solution 3: Vacuum Tables

SQL

-- Full vacuum to reclaim space (locks table)
VACUUM FULL large_table;

-- Regular vacuum (doesn't lock)
VACUUM ANALYZE large_table;

-- Check for tables needing vacuum
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Solution 4: Remove Unused Data

SQL

-- Delete old data
DELETE FROM logs WHERE created_at < NOW() - INTERVAL '90 days';

-- Truncate table (faster than DELETE)
TRUNCATE TABLE old_events;

-- Drop unused tables
DROP TABLE IF EXISTS temp_migration_data;

Solution 5: Move Data Directory

BASH

# Stop PostgreSQL
sudo systemctl stop postgresql

# Move data to larger disk
sudo mv /var/lib/postgresql/data /new-disk/postgresql/data

# Create symlink
sudo ln -s /new-disk/postgresql/data /var/lib/postgresql/data

# Start PostgreSQL
sudo systemctl start postgresql

Solution 6: Configure Log Rotation

BASH

# In postgresql.conf
log_rotation_age = '1d'
log_rotation_size = '100MB'
log_truncate_on_rotation = on

Monitoring Disk Space

SQL

-- Table sizes
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 10;

-- Database size
SELECT pg_size_pretty(pg_database_size(current_database()));

-- Bloat check
SELECT schemaname, tablename,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
  n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Best Practices

Monitor disk space with alerts at 80% threshold
Configure autovacuum appropriately
Implement data retention policies
Use table partitioning for large tables
Plan for growth with adequate disk space