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.

Leave a comment