Heroku Database Size Warning: Finding the Problem

- - posted in Ruby on Rails, database, heroku, postgresql, programming, web development, website | Comments

Heroku is a great platform, but it do has some limitations in its free service. One of those is the maximum on the number of rows in the PostgreSQL database and the total size limit. Recently I received an e-mail stating that my database exceeded that number of records. This post will show how to inspect your database to find the problem via the Heroku toolbelt and connect to your database with pgAdmin III.

The e-mail I received was the following:

1
2
3
4
5
6
7
Subject: [warning] Database disruption imminent, row limit exceeded for dev database on Heroku app [app-name]
Body: The database HEROKU_POSTGRESQL_ORANGE_URL on Heroku app [app-name] has exceeded its allocated storage capacity.
Immediate action is required.

The database contains 101,208 rows, exceeding the Dev plan limit of 10,000.
INSERT privileges to the database will be automatically revoked in 7 days.
This will cause service failures in most applications dependent on this database.

As the message says, there are just a few rows too many in our Heroku application. The solution is to (simply) delete rows until we are below the threshold of 10,000 (or just pay for their service). We want to take the first approach; delete rows from our database. But where are all these rows? Which tables are in need of a clean-up?

The first step is to list the tables with their number of rows. We can do that by connection to the PostgreSQL database using the Heroku toolbelt. When connected, we use a SQL-statement to list all the tables.

1
2
3
4
> heroku pg:psql
psql (9.3.0, server 9.1.9)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

When connected, we get a prompt (with our database name at the beginning). There we can insert the following SQL-statement:

1
2
3
4
sda34q2q2wf242=>
SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

The result will be something like the following table:

1
2
3
4
5
6
schemaname |        relname         | n_live_tup
-----------+------------------------+------------
public     | Comments               |      63896
public     | Posts                  |      14139
public     | Users                  |      11920
...        | ...                    |        ...

This can give you a clear indication where the problem of the large table is. You can now work further in the psql tool (or for instance via the Rails console) to remove superfluous database records. A method I prefer is to use pgAdmin III to connect to the Heroku PostgreSQL database. For this, we need to fetch the credentials of our Heroku database. Again, we use the Heroku toolbelt:

1
2
3
> heroku pg:credentials DATABASE
Connection info string:
"dbname=sda34q2q2wf242 host=ec2-53-241-241-24.compute-1.amazonaws.com port=5432 user=asfwqersaswrqw password=uioyLK78yiug876T765giuyt98 sslmode=require"

Here we see our database name we also saw on the prompt, the host we can connect to and finally the username and password to authenticate ourself. We can use this information to create a new connection in pgAdmin III. The following screenshot shows how to use the information. In the Advanced tab of the new connection dialog, we can add our database to the field DB restriction. By doing this, we only see our own database in the list of databases on the host (otherwise we would have to search for a single database in a list of thousands…)

With this information you now can connect to the Heroku PostgreSQL database from your desktop by using pgAdmin III (or an other application). This way you can easily inspect the tables with a large amount of rows and clean up your database (or use it for debugging).

Comments