Understanding disk usage after deleting data from PostgreSQL

When deleting large amounts of data from a PostgreSQL database, you may notice that disk utilization increases instead of decreases. This can happen due to active replication slots, particularly when working with restored database backups.

Why does disk usage increase after deleting data?

If your database has active replication slots, PostgreSQL maintains Write-Ahead Log (WAL) files until the changes are replicated to all slots. When there are no active replicas to receive these changes, the WAL files accumulate, causing increased disk usage.

This commonly occurs in two scenarios:

• When working with a database restored from backup that had active replication slots

• When your production database has replicas that aren't actively receiving updates

How to check and remove replication slots

To view existing replication slots:

SELECT * from pg_replication_slots;

To drop a replication slot:

SELECT pg_drop_replication_slot(slot_name);

After removing unnecessary replication slots, your database should begin to reclaim disk space from deleted data.