Checking database size by row count

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

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.

Dominik Schöler

makandra.de

Say thanks1

Last edit

2023-07-20

Dominik Schöler

License

Source code in this card is licensed under the MIT License.

PostgreSQL: Show size of all databases

Use this:

SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024 AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;

Want to see [database sizes in MySQL](https://makandracards.com/makandra/1447-...

PostgreSQL: How to show database size

SELECT pg_size_pretty(pg_database_size('some-database'));

Example

SELECT pg_size_pretty(pg_database_size('cards_p'));
----------------
 13 GB
(1 row)

SELECT pg_database_size('cards_p');
 pg_database_size 
-------------...

Select a random table row with ActiveRecord

Use this scope:

class Stick
  named_scope :shuffled, lambda {
    last_record = last
    { :conditions => [ 'id >= ?', rand(last_record.id) ] } if last_record
  }
end

You can now pick a random stick by saying

Stick.s...

SQL: Find out number of rows of all tables within a MySQL database

Here you are:

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database' order by table_rows;

List directories ordered by size (with human-readable output)

You know there is the du command to fetch the disk usage of a directory (“.” in this example). By default, output is sorted by directory name, not size:

du -h --max-depth=1 .
40K  ./a
2.3G ./b
3.1M ./c
500M ./d
2.8G ...

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

Checking database size by row count

PostgreSQL

MySQL

Related cards:

size

count

Example