How to Check Database Size and Table Sizes From the Shell

"How big is the database?" is two questions: how much disk does it use now, and how fast is that growing. The first has a one-line answer per database system, and the second needs the answer logged on a schedule. Knowing which tables and indexes hold the bytes decides what to archive, partition or drop. This post gives the queries for the common systems and a small script for history. It feeds capacity checks. Examples use PostgreSQL 17, MySQL 8.4, SQLite 3.46 and Redis 7.4.

PostgreSQL

psql -At -c "select pg_size_pretty(pg_database_size(current_database()))" app
psql -d app -c "
select relname as table,
       pg_size_pretty(pg_total_relation_size(c.oid)) as total,
       pg_size_pretty(pg_relation_size(c.oid)) as table_only,
       pg_size_pretty(pg_indexes_size(c.oid)) as indexes
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r'
order by pg_total_relation_size(c.oid) desc limit 10"

  table   |  total  | table_only | indexes
----------+---------+------------+---------
 events   | 18 GB   | 11 GB      | 6 GB
 sessions | 2308 MB | 1100 MB    | 1208 MB

pg_total_relation_size counts the table, its indexes and its TOAST storage (where large values are kept); pg_relation_size is the main table only. When the index total rivals the table, as for sessions, look for indexes you do not use: select * from pg_stat_user_indexes where idx_scan = 0. The psql shortcuts \l+ (databases) and \dt+ (tables) print sizes too. Dead rows inflate tables: a table much larger than its live data suggests bloat, and vacuum or a rewrite reclaims it.

MySQL

mysql -e "
select table_schema, round(sum(data_length + index_length)/1024/1024) as mb
from information_schema.tables group by table_schema order by mb desc"
mysql -e "
select table_name, round(data_length/1024/1024) as data_mb, round(index_length/1024/1024) as index_mb, table_rows
from information_schema.tables where table_schema = 'app' order by data_length + index_length desc limit 10"

The figures are in bytes, so divide for megabytes. For InnoDB, table_rows is an estimate, and the sizes lag actual disk use; information_schema.innodb_tablespaces or the .ibd file sizes under /var/lib/mysql/app/ are closer to the truth. Free space inside tablespaces after deletes is not returned to the filesystem until optimize table.

SQLite

ls -lh app.db
sqlite3 app.db "pragma page_count; pragma page_size; pragma freelist_count"
sqlite3 app.db "select name, sum(pgsize)/1024 as kb from dbstat group by name order by kb desc limit 10"

A SQLite database is one file, so ls is the answer; page count times page size is the same figure, and the freelist shows unused pages that vacuum would give back. The dbstat virtual table, available in most builds, lists the space per table and index.

Redis

redis-cli info memory | grep -E 'used_memory_human|used_memory_peak_human|maxmemory_human'
redis-cli --bigkeys
redis-cli memory usage mykey

Redis holds data in memory, so size means RAM. --bigkeys samples the largest key of each type and MEMORY USAGE key reports the bytes one key takes. See redis-cli for safe scanning.

Disk use of the data directory

sudo du -sh /var/lib/postgresql /var/lib/mysql
sudo du -sh /var/lib/postgresql/17/main/{base,pg_wal}
df -h /var/lib/postgresql

The data directory also holds write-ahead logs, binary logs and temporary files that the queries above do not count. pg_wal growing without bound points at a stuck replication slot or failing archiving; MySQL's binary logs are cleaned by binlog_expire_logs_seconds. Compare the database figure with the disk figure to see how much is overhead.

Log it daily

#!/usr/bin/env bash
set -euo pipefail
log=/var/log/db-size.csv
[ -s "$log" ] || echo "date,database,bytes" > "$log"
psql -At -F, -c "select current_date, datname, pg_database_size(datname) from pg_database where not datistemplate" postgres >> "$log"

A line per database per day is cheap, and the growth curve it draws answers "when do we run out of disk" far better than a snapshot; the projection method is in capacity checks.

Where this does not apply

Logical size and physical size differ with compression (columnar tables, ZFS, TOAST), replicas and backups that multiply the footprint. Managed databases report size in their consoles, and the storage you pay for includes provisioning slack. The numbers say where the bytes are, not what is safe to delete: retention decisions need the business context.

Finding the big tables and what the numbers include

In PostgreSQL, pg_database_size('name') gives a database's size and pg_total_relation_size('table') gives a table with its indexes and the out-of-line storage for large values, while pg_relation_size counts only the main data and pg_indexes_size only the indexes. Wrap them in pg_size_pretty for readable output, and order a catalog query by the total to list the largest tables first. The indexes are often as large as the table, and unused ones are a cheap way to reclaim space, which pg_stat_user_indexes reveals through its scan counts. MySQL reports data_length and index_length per table in information_schema.TABLES, and these are estimates for InnoDB that can be off by a margin, so treat them as a guide. Neither counts space that has been freed inside the file and not given back to the operating system, which is the gap between what the database says and what the disk says.

That gap is bloat. Deleted and updated rows leave dead space that is reused but not returned, and a table can occupy several times the size of its live data until a rewrite such as VACUUM FULL or OPTIMIZE TABLE runs, which locks the table.

SQLite, Redis and the files on disk

SQLite's size is the page count times the page size, from PRAGMA page_count and PRAGMA page_size, which equals the file size, and the free page list shows reusable space inside it. The dbstat virtual table, when compiled in, breaks usage down by table and index, and the sqlite3_analyzer tool reports it in more detail. A VACUUM rewrites the file and returns free space. For Redis, INFO memory prints the used memory in human-readable form along with the peak and the fragmentation ratio, and MEMORY USAGE key gives the size of one key. The persistence files on disk, the snapshot and the append-only log, are additional and separate from memory. Always compare with the files: du -sh on the data directory shows what the database occupies, which includes write-ahead logs, transaction logs and temporary files that the SQL view of size leaves out, and a data directory far larger than the sum of the tables usually has a runaway log or an orphaned replication slot holding old WAL segments.

Turning it into a record

Write a small script that prints a line with the date and the sizes of the database and the largest few tables, and run it daily from a timer, appending to a file. After a few weeks the growth per day is a number, and the days until the disk fills is a division. Alert on both the absolute size and the rate of growth, since a table that doubles overnight is a bug worth hearing about before it fills the disk. Keep the log for a year, which is tiny, and note changes that explain steps in it, such as a migration or a new customer. Include the free space on the volume in the same line, so one file answers whether the database fits and for how long. Run the queries as a read-only monitoring user, with no access to the data itself, since size queries need only catalog access, and store its password as described in the post on credentials for database scripts.

What to do when the numbers are large

Large tables are not a problem by themselves, and growth that is unexplained is. Compare the size with the row count and the expected row width, and a table far larger than that product has bloat. Look for large text or binary columns that could live in object storage, for logs and audit tables with no retention policy, and for indexes that nobody uses. Partitioning old data by date makes deleting it a metadata operation instead of a long delete, and archiving to cheaper storage keeps recent data fast. Set a retention rule, write it down, and automate it, since a table that only grows will eventually decide the disk size for you.

See PostgreSQL database object size functions and the MySQL TABLES table.