What's the right way to calculate table size in postgres?

Question

I'm working on a project where calculations are being made for certain tables in the postgres DB, and in different parts of code, there are 2 different calculations being made:

select pg_total_relation_size ('TABLENAME');

and the second one:

select (relpages*8/1024) from pg_class where relname='TABLENAME' (Size in MB)

I know that pg_total_relation_size is what I need to use, but I was wondering what does this other calculation represents, and if the second one is also some kind of size calculation, why am I getting different results for the same table?

The second one would calculate the overhead that occurs due to the fact that not every block is 100% full. The first one is the size of the data, the second one the size on disk (more or less) — user330315
– user330315, Commented Jul 18, 2014 at 12:38
Before you can ask for the right way, you need to define the target. "Table size" is pretty vague. There are a number of possible definitions as discussed in this related answer on dba.SE — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 18, 2014 at 13:25

Andomar · Accepted Answer · 2014-07-18 12:50:37Z

You can use pg_size_pretty(pg_total_relation_side(oid)) to pretty print table size:

select nspname , relname , pg_size_pretty(pg_total_relation_size(c.oid)) as "size" from pg_class c left join pg_namespace n on n.oid = c.relnamespace where nspname not in ('pg_catalog', 'information_schema') order by pg_relation_size(c.oid) desc;

Erwin Brandstetter · Accepted Answer · 2014-07-18 13:34:43Z

4

pg_total_relation_size - is the right way to see full table size because it includes disk space utilized by indices and TOAST data.

When you do the second-one select - you get table size only (relpages is table size in 8Kb pages). You can get TOAST and index sizes from pg_class as well but you need to do some more sql queries as described in here and sum all the sizes you've got.

edited Jul 18, 2014 at 13:34

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

answered Jul 18, 2014 at 12:39

Konstantin V. Salikhov

4,6512 gold badges38 silver badges48 bronze badges

2 Comments

Erwin Brandstetter Over a year ago

Please link to the current version of the manual. don't fall for outdated links from Google. And the TOAST table is not the only consideration. There are more depending objects like indexes, the free space map or the visibility map. Details.

Konstantin V. Salikhov Over a year ago

Thanks @ErwinBrandstetter - I've mentioned indexes, but didn't know about other object types. But the answer is still the same - second select is definitely underestimates table size (in common sense of the word)

Collectives™ on Stack Overflow

What's the right way to calculate table size in postgres?

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related