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
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
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
The result will be something like the following table:
1 2 3 4 5 6
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
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.
Advanced tab of the new connection dialog, we can add our database to the field
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).