Waiting for 9.0 – table and index sizes

On 19th of January Tom Lane committed really brilliant patch:

Log Message:
-----------
Add pg_table_size() and pg_indexes_size() to provide more user-friendly
wrappers around the pg_relation_size() function.
 
Bernd Helmle, reviewed by Greg Smith

Why is it brilliant? Because I'm lazy. And I think it's a virtue, and not flaw.

Let's consider this example:

CREATE TABLE x (
    id serial PRIMARY KEY,
    text1 text,
    text2 text,
    text3 text
);

Table is pretty simple, and so is data:

INSERT INTO x (text1, text2, text3)
    SELECT
        repeat( 'text1:' || i, 10 ),
        repeat( 'text2:' || i, 100),
        repeat( 'text3:' || i, 1000)
    FROM generate_series( 1, 500000 ) i;

But, let's check how big the table really is.

Theoretically I could:

# SELECT pg_relation_size('x');
 pg_relation_size
------------------
        816275456
(1 ROW)

But that's not real true. There is also TOAST. Of course I could:

# SELECT pg_total_relation_size('x');
 pg_total_relation_size
------------------------
              827760640
(1 ROW)

But this also doesn't solve my problem, as it contains also size of indexes.

Of course, in my trivial example I can:

# SELECT pg_total_relation_size('x') - pg_total_relation_size('x_pkey');
 ?COLUMN?
-----------
 816504832
(1 ROW)

But just imagine how “fun" it would be if the table was more realistic – more columns and 12 indexes on it.

But now, we have these 2 new function. Which solve the problem once and for all:

# SELECT pg_table_size('x'), pg_indexes_size('x');
 pg_table_size | pg_indexes_size
---------------+-----------------
     816504832 |        11255808
(1 ROW)

Simple, and to the point. No more guessing, finding name of toast table, or getting summarized size of all indexes on given table. Thank you Bernd, I really appreciate it.