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.
[cc lang="html"]
SELECT relname, reltuples, relpages * 8 / 1024 AS “MB” FROM pg_class ORDER BY relpages DESC;
[/cc]
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.
[cc lang="html"]
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
[/cc]
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!
[...] original post [...]
Check postgesql table size | 101TECH on April 3rd, 2009 at 1:51 pm
This only works if the tables have been analyzed or vacuumed recently. Nice try, but not incredibly accurate.
Bobby
on May 6th, 2009 at 7:30 pm
I know. Thats why I am using the term “Estimated” everywhere in the text and also have a little disclaimer in the end.
“Be aware that these numbers only gets updated when you run vacuum.”
Cheers!
Mathias Stjernström
on May 6th, 2009 at 8:47 pm