CODEWITHSUNDEEP
postgresqlheroku
Toldry
Toldry

Reputation: 981

How does Heroku Postgres count rows for purposes of enforcing plan limits?

I have received the following email from Heroku:

The database DATABASE_URL on Heroku app [redacted] has exceeded its allocated storage capacity. Immediate action is required.

The database contains 12,858 rows, exceeding the Hobby-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.

To avoid a disruption to your service, migrate the database to a Hobby Basic ($9/month) or higher database plan:

https://hello.heroku.com/upgrade-postgres-c#upgrading-with-pg-copy

If you are unable to upgrade the database, you should reduce the number of records stored in it.

My postgres database had a single table with 5693 rows at the time I received this email, which does not match the '12858 rows' mentioned by the email. What am I missing here?

It is perhaps worth mentioning that my DB also has a view of the table mentioned above, which Heroku might be adding to the count (despite not being an actual table), doubling the row count from 5693 to 11386, which still does not match the 12858 mentioned in the email, but it is closer.

Upvotes: 0

Views: 1951

Answers (2)

Toldry
Toldry

Reputation: 981

TL;DR the rows in views DO factor into the total row count, even when it isn't a materialized view, despite the fact that views do not store data.

I ran heroku pg:info and saw the line:

Rows: 12858/10000 (Above limits, access disruption imminent)

I then dropped the view I mentioned in the original post, and ran heroku pg:info again:

Rows: 5767/10000 (Above limits, access disruption imminent)

So it seems indeed that views DO get counted in the total row count, which seems rather silly, since views don't actually store any data.

I also don't know why the (Above limits, access disruption imminent) string is still present after reducing the row number below the 10000 limit, but after running heroku pg:info again a minute later, I got

Rows: 5767/10000 (In compliance)

so apparently the compliance flag is not updated at the same time as the row number.

What's even stranger is that when I later re-created the same view that I had dropped, and ran heroku pg:info again, the row count did not double back up to ~11000, it stayed at ~5500.

It is useful to note that the following SQL command will display the row counts of the various objects in the database:

select table_schema, 
       table_name, 
       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
  select table_name, table_schema, 
         query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
  from information_schema.tables
  where table_schema = 'public' --<< change here for the schema you want
) t

the above query was copy-pasted from here

Upvotes: 3

squeezer44
squeezer44

Reputation: 640

It sounds as you would have two different moments where usage of your postgresql database were measured: first one with higher values (12.858 rows is over the free limit and measured at Heroku) and the second one with less values (5693 rows which would be in free limit and could be measured on your local environment?).

Anyway - first things first: Take a look into your PostgreSQL database at Heroku - this can be done in two ways:

  1. Connect your local Heroku CLI with your dyno and check the info of the related PostgreSQL database HowTo see the database related info

  2. Login into your Heroku WebGUI and check size and rows in there Heroku Postgres

The background behind their database monitoring is Heroku explain in there: Monitoring Heroku Postgres

Upvotes: 1

Related Questions