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:

  1. relname: The name of the table.
  2. reltuples: Estimated numbers of rows.
  3. 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!

Posted in PostgreSQL at March 16th, 2008. Trackback URI: trackback Tags: Written by: 
  • Pingback: Check postgesql table size | 101TECH()

  • Bobby

    This only works if the tables have been analyzed or vacuumed recently. Nice try, but not incredibly accurate.

  • Mathias Stjernström

    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!

It's past my bedtime is using WP-Gravatar