Read more

Checking database size by row count

Dominik Schöler
October 11, 2019Software engineer at makandra GmbH

As an application exists, data accumulates. While you'll be loosely monitoring the main models' record count, some supportive database tables may grow unnoticed.

Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

To get a quick overview of database table sizes, you can view the row count like this:

PostgreSQL

SELECT schemaname,relname,n_live_tup 
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC
LIMIT 12;

 schemaname |                    relname                     | n_live_tup 
------------+------------------------------------------------+------------
 public     | images                                         |      17025
 public     | items                                          |      16918
 public     | tree_nodes                                     |       5018
 public     | page_versions                                  |       4049
 public     | pages                                          |       3919

Please note that this does not work reliably on database slaves Show archive.org snapshot .

MySQL

SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = (SELECT database())
ORDER BY table_rows DESC
LIMIT 12;

+---------------------+------------+
| table_name          | table_rows |
+---------------------+------------+
| addresses           |      48106 |
| orders              |      33740 |
| users               |       8703 |
| ...

If you're interested in the space a table takes up on the disk check table sizes by disk usage instead.

Connect to a Rails database with bin/rails dbconsole -p.

Posted by Dominik Schöler to makandra dev (2019-10-11 08:53)