Postgresql table sizes
PostgreSQL has lots and lots of sweet internals but its not always easy to know how to find them. Here is an simple way of getting the disk size of a PostgreSQL table.
From within the database of choice.
Run the following query to get some useful table statistics.
1 | SELECT relname, reltuples, relpages * 8 / 1024 AS "MB" FROM pg_class ORDER BY relpages DESC; |
Block size
If you use another block size than 8kb you have do multiply relpages with your own block size. To find your block size you can use pg_controldata which is a command-line utility to display control information of a PostgreSQL database cluster.
Run it with your datadir as first parameter pg_controldata ./data/ and look for Database block size in the output.
Database query result
The outout gives you:
- relname: The name of the table.
- reltuples: Estimated numbers of rows.
- MB: Table size in Mbytes.
1 2 3 4 5 6 7 8 9 10 11 12 | relname | reltuples | MB -----------------------------------+-------------+------- history_uint | 1.685e+08 | 15396 history_uint_1 | 1.685e+08 | 12279 history | 1.40719e+08 | 8405 history_1 | 1.40719e+08 | 7153 trends | 1.03077e+07 | 929 trends_pkey | 1.03077e+07 | 423 items | 4698 | 83 history_str | 536599 | 53 history_str_1 | 536599 | 27 events | 166602 | 15 |
We can now see that history_uint is the biggest table and holds an estimate of 168,572,082 rows and takes about 15GB on disk.
Be aware that these numbers only gets updated when you run vacuum.
I have more PostgreSQL internals to come!
Pingback: Check postgesql table size | 101TECH