How to find the tables that have the maximum number of records in Heroku using postgress

19 Nov How to find the tables that have the maximum number of records in Heroku using postgress

As you know there is a free plan in heroku.com and you can use only 10,000 rows in the database but sometimes your database exceeds those limits and you don’t want to pay because the app installation in only for testing purposes.

That is why you can use the following commands if you want to to search and find the tables that have the maximum number of records and be able to delete the ones that are not necessary for you

With the following command you can see your details application.

heroku pg:info

you will receive a log similar to:

=== HEROKU_POSTGRESQL_RED_URL (DATABASE_URL)
Plan:        Hobby-dev
Status:      Unavailable, operator notified
Connections: 4/20
PG Version:  9.3.3
Created:     2014-09-04 18:38 UTC
Data Size:   17.2 MB
Tables:      65
Rows:        7560/10000 (Write access revoked) - refreshing
Fork/Follow: Unsupported
Rollback:    Unsupported

In my case I’m using postgress so I want to search one by one in all the tables to do that run the following command:

heroku pg:psql HEROKU_POSTGRESQL_RED_URL

where “HEROKU_POSTGRESQL_RED_URL” is the database url you’re using.

now you can for example list the tables

\dt;     # list all tables in current db

And you can use a query to count how many records have each table

SELECT COUNT(*) FROM spree_addresses;
 count
-------
  4558
(1 row)
or
SELECT COUNT(*) FROM spree_state_changes;
 count
-------
  6558
(1 row)

and check each of the tables and just delete the records that are not necessary specifying your table.

DELETE FROM spree_state_changes;

## Some useful commands

psql database_name              # Starts new postgress session in the database specified
\d+ table_name/view_name;       # describes the structure of each table or view
\e;                             # open current query buffer in editor (vim)
psql -l;                        # list all databases
\connect db_name;               # connect a database
pg_dump dbName > dbName.sql;    # backup a database
DROP TABLE table_name;          # remove a table

# some query examples
SELECT tables_joined.field1, count(table1)
FROM table1
JOIN table2 ON table1.forgeign_id = table2.id
tables_joined
WHERE table2.condition_field = 4
GROUP BY field1;