Free Up Storage (PostgreSQL)¶

The storage alerts of a PostgreSQL instance link to this page. It shows how to find what uses the space, and how to remove it.

Find the largest databases and tables¶

List the databases by size:

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

Connect to the largest database, and list the largest tables (including their indexes):

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

Remove data you do not need¶

• Drop tables and databases you do not need. DROP and TRUNCATE free the space on the disk at once.
• DELETE does not free the space on the disk at once. VACUUM makes the space available for new data in the same table. Enable Run VACUUM During Maintenance for workloads with many deletes and updates. See Maintenance & optimization.
• A long running transaction stops VACUUM from cleaning up. See PostgreSQLLongRunningTransaction.

Check replication slots¶

An inactive replication slot keeps write-ahead log (WAL) files on the disk. The WAL files grow until the slot is used again or dropped. List the slots:

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Drop the slots you created and do not need anymore:

SELECT pg_drop_replication_slot('<slot_name>');

If the WAL files use a lot of space and no slot keeps them, contact support. The platform archives WAL files for backups. If the archiving fails, the WAL files stay on the disk.