Heroku Postgres Over Plan Capacity
Last updated November 28, 2022
If your Heroku Postgres database exceeds your plan allotment, you receive a warning email with directions on how to fix the issue. In some cases, we set an enforcement date when access is disabled until the database is within plan limits. Take action to avoid restricted access.
Over Plan Capacity Statuses
To see the status of your Heroku Postgres database, run the
heroku pg:info command. If your database is over capacity, under Status you see :
Over Plan Capacity: Action Required or
Over Plan Capacity: Access Restricted.
Your database is over its plan capacity so you must upgrade it or delete data before the enforcement date. You can find the enforcement date in your email or in your application logs.
Your database is over plan capacity beyond the enforcement date. Restricted access means:
- You’re allowed a single database connection
- Your connection allows
To regain full access, you can upgrade your database or delete data.
Steps to Resolve
Check Your Data Usage
To check your Postgres database use
heroku pg:info. The output shows your plan and database size so you can decide whether to upgrade your plan or remove data.
$ heroku pg:info HEROKU_POSTGRESQL_COBALT -a example-app === HEROKU_POSTGRESQL_COBALT_URL Plan: Standard 0 Status: Over Capacity: Action Required Data Size: 144.6 GB
Upgrade Your Plan
To get more storage, you can upgrade your Heroku Postgres plan, which is the recommended option. If you do upgrade, you avoid data loss and minimize downtime. Your database can be downgraded at any time.
The simplest way to upgrade is to use the
$ heroku addons:upgrade HEROKU_POSTGRESQL_COBALT_URL heroku-postgresql:standard-2 -a example-app
To learn about more upgrade tools, see our article about manually upgrading with pg:upgrade.
Delete Your Data
To return within your plan limits, you can delete data instead of upgrading.
Test Deletion with a Fork
Before you delete data from your database, create a fork so that you can experiment before you act on your data.
After you create a fork, you can follow these steps to delete your data. These examples use
pg:psql to modify the database directly with SQL queries. These SQL queries are examples.
To delete data, use a condition to remove specific rows or you can truncate the data.
$ heroku pg:psql HEROKU_POSTGRESQL_COBALT -a example-app # Delete records older than 30 days example-app::HEROKU_POSTGRESQL_COBALT=> DELETE FROM logs WHERE created_at < (now() - '30 days'::interval);
Restricted access doesn’t include
DROP. To remove all data from a large table of noncritical data, you can use
example-app::HEROKU_POSTGRESQL_COBALT=> TRUNCATE TABLE logs;
Re-index to reclaim space in indices after deleting or truncating:
example-app::HEROKU_POSTGRESQL_COBALT=> REINDEX TABLE logs;
Running VACUUM FULL on table takes an AccessExclusive lock on the table and it blocks other queries form reading it while it runs
Your Postgres plan is measured by disk usage. Postgres doesn’t perform deletes on disk, but marks space that can be reused. To release unused disk space, run
VACUUM FULL. When you test this on your fork, note how long the
VACUUM FULL takes so you can assess the production impact.
example-app::HEROKU_POSTGRESQL_COBALT=> VACUUM FULL;
See Managing VACUUM on Heroku Postgres for more info on types of
VACUUM operations and how they work.
Performing Deletion on Your Live Database
Put your app in maintenance mode while you use
VACUUM FULL so users can’t access it mid-operation. If your access is restricted, it’s possible that your application is inoperable already as your application wouldn’t be able to open the necessary connections against your database, but use maintenance mode to be safe.
Use Log Entries to Track Plan Capacity
Log entries follow the standard log format.
If your database is over capacity:
$ heroku logs -a example-app 2020-07-20T23:14:08.000000+00:00 app[heroku-postgres]: source=HEROKU_POSTGRESQL_GREEN_URL addon=postgresql-octagonal-423563 sample#restricted=false sample#database-capacity-used-bytes=107889578475 sample#database-capacity-used-percentage=157.00 sample#database-capacity-bytes=68719476736 sample#enforcement-date=2020-08-19 00:00:00 +0000 message=Database size is over plan capacity, access will be restricted by 2020-08-19 00:00:00 +0000.
If your enforcement date has passed:
$ heroku logs -a example-app 2020-08-28T23:14:08.000000+00:00 app[heroku-postgres]: source=HEROKU_POSTGRESQL_GREEN_URL addon=postgresql-octagonal-423563 sample#restricted=true sample#database-capacity-used-bytes=107889578475 sample#database-capacity-used-percentage=157.00 sample#database-capacity-bytes=68719476736 message=Database size is over plan capacity, access has been restricted.