How to Fix “disk quota exceeded” in PostgreSQL

How do I fix PostgreSQL "disk quota exceeded"?

“disk quota exceeded” appears when PostgreSQL cannot write to disk because the server, tablespace, or file-system quota is full.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why does PostgreSQL raise “disk quota exceeded”?

The server’s disk, tablespace, or OS quota reached its limit, so PostgreSQL cannot create WAL files, temporary files, or extend tables. Any write action fails until space is freed.

How do I see current database size?

Run SELECT pg_size_pretty(pg_database_size(current_database())); to measure the whole database. For per-table detail, query pg_total_relation_size and order by size.

Which tables consume the most space?

SELECT relname AS table, pg_size_pretty(pg_total_relation_size(relid)) AS total FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

This highlights bloated or log-style tables that can be trimmed or vacuumed.

How can I free disk space fast?

1. VACUUM (FULL) reclaims dead tuples.
2. DELETE or TRUNCATE archival data.
3. DROP unused indexes after verifying they are redundant.
4. Remove old WAL files with pg_archivecleanup if archiving is enabled.

How do I move large tables to another disk?

CREATE TABLESPACE fast_disk LOCATION '/mnt/ssd_ts'; ALTER TABLE Orders SET TABLESPACE fast_disk;

The command copies data files to the new location and frees space on the original volume.

How to prevent future quota errors?

• Monitor pg_database_size and OS disk usage.
• Run regular AUTOVACUUM and avoid disabling it.
• Partition log-like tables and drop old partitions automatically.
• Size WAL (max_wal_size) and temp (temp_file_limit) parameters to fit budgets.

Why How to Fix “disk quota exceeded” in PostgreSQL is important

How to Fix “disk quota exceeded” in PostgreSQL Example Usage


-- Remove two-year-old orders and order items
WITH old_orders AS (
    SELECT id FROM Orders
    WHERE order_date < CURRENT_DATE - INTERVAL '2 years'
)
DELETE FROM OrderItems WHERE order_id IN (SELECT id FROM old_orders);
DELETE FROM Orders     WHERE id        IN (SELECT id FROM old_orders);

-- Reclaim disk space immediately
VACUUM FULL Orders, OrderItems;

How to Fix “disk quota exceeded” in PostgreSQL Syntax


-- Reclaim space
VACUUM [ FULL ] [VERBOSE] [ table_name ];

-- Delete old ecommerce data safely
WITH old_orders AS (
    SELECT id FROM Orders
    WHERE order_date < CURRENT_DATE - INTERVAL '2 years'
)
DELETE FROM OrderItems WHERE order_id IN (SELECT id FROM old_orders);
DELETE FROM Orders     WHERE id        IN (SELECT id FROM old_orders);

-- Move a table to a new tablespace
ALTER TABLE table_name SET TABLESPACE tablespace_name;

-- Create a tablespace on a larger disk
CREATE TABLESPACE name LOCATION '/path/to/mount';

-- Check size of specific objects
SELECT pg_size_pretty(pg_total_relation_size('Products'));

Common Mistakes

Mistake: Deleting rows but forgetting VACUUM. Postgres keeps dead tuples, so disk is not freed. Fix by running VACUUM (or VACUUM FULL for maximum reclaim).
Mistake: Disabling autovacuum to "save CPU." This leads to table bloat and rapid disk growth. Keep autovacuum enabled and tune thresholds instead.

Frequently Asked Questions (FAQs)

Does TRUNCATE free space immediately?

Yes. TRUNCATE rewrites the table, returning space to the file system instantly and faster than DELETE when you can remove all rows.

Will VACUUM FULL block writes?

VACUUM FULL obtains an exclusive lock and rewrites the table, blocking other writes. Schedule it during low-traffic windows or use CLUSTER on a replica.