Last updated April 29, 2024

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.

Action Required

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.

Access Restricted

Your database is over plan capacity beyond the enforcement date. Restricted access means:

• You’re allowed a single database connection
• Your connection allows READ, DELETE, and TRUNCATE access

To regain full access, you can upgrade your database or delete data.

If your database has been over capacity for more than 30 days, Heroku attempts to reduce your usage by running VACUUM FULL on your database to remove dead rows.

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 addons:upgrade command:

$ 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.

Delete Data

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 TRUNCATE. term example-app::HEROKU_POSTGRESQL_COBALT=> TRUNCATE TABLE logs;

Re-Index

Re-index to reclaim space in indices after deleting or truncating:

example-app::HEROKU_POSTGRESQL_COBALT=> REINDEX TABLE CONCURRENTLY logs;

Vacuum

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

Heroku Postgres emits log messages to Logplex when your service goes over capacity. The log messages are available via the CLI or via logging add-ons.

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.