When your database is full

1. What happens

At the plan's storage (for example 20 GB on Starter), open sessions are ended and new ones start read-only. Writes then fail with:

ERROR:  cannot execute INSERT in a read-only transaction

When data arrives very fast (a bulk load), writes stop a little before the plan size, so they never overrun it. Storage counts everything on the database's disk: tables, indexes, and the write-ahead log (WAL) that PostgreSQL keeps for recovery and replication.

You get emails at 85% and 95%, and when the database becomes read-only. The database page shows a warning too.

2. Get write access in a session

Read-only is a default, not a lock. Connect as usual (psql, your SQL client, or your app's connection) and turn it off for your session:

SET default_transaction_read_only = off;

Everything you run in that session can now write, including the commands below. Other sessions stay read-only until the database is writable again. Use this session only to free space: the storage limit is still enforced, so a new bulk load from here stops again.

3. Free space

Drop or empty what you do not need. These return the space to the disk at once:

DROP TABLE old_events;           -- a whole table
TRUNCATE TABLE import_staging;   -- keep the table, remove all rows
DROP INDEX unused_index;          -- indexes take space too

For a partitioned table, dropping or detaching old partitions is the fastest way to free space.

Delete rows, then vacuum. DELETE alone does not free disk space; it marks rows as dead. VACUUM makes their space reusable for new rows, and returns to the disk only the empty pages at the end of the table:

DELETE FROM events WHERE created_at < now() - interval '90 days';
VACUUM events;

So after a large delete the database may still be close to full on disk, even though new rows now fit into the freed space. VACUUM FULL rewrites the table to shrink its file, but it needs free space as large as the table while it runs, which a full database does not have. Drop or truncate first if you need the disk space itself.

To see where the space goes:

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

4. When writes come back

The database becomes writable again by itself, within about a minute, once its storage is 5% of the plan below where it stopped (and under 95%). For a 20 GB plan that stopped at 20 GB, that is under 19 GB. The margin keeps it from switching between read-only and writable while it is close to the limit.

After a large cleanup, the usage shown can take a few minutes to go down: PostgreSQL recycles its write-ahead log at the next checkpoint.

5. Or change to a bigger plan

If you need the data, change to a plan with more storage on the database page (Databases, then your database, then Change plan). On high-availability plans this happens with no downtime, and writes come back as soon as the new storage is in place. See Plans, resizing and storage limits.