Difference between total table size and size of all pages in postgresql

Question

Working with Postgres I noticed the following:

When I run this query I get the total table size:

SELECT pg_size_pretty( pg_table_size('tenk1') );

I get 384kb.

And when I run:

SELECT relpages FROM pg_class WHERE relname = 'tenk1';

I get 45 pages. I know that one page has 8kb. I confirmed that with SELECT current_setting('block_size');

Therefore, my table size should be 45 * 8kB = 360kB, instead of 384kb. Where does the difference of 24kb come from?

What else could take up this space? I know that indexes are excluded by pg_table_size. But its TOAST table if any, free space map, and visibility map are included. Is there a way to determine their sizes?

Answer 1

The function pg_table_size() includes auxiliary relations (TOAST table, free space map, visibility map).

The number in pg_class.relpages should correspond to the result of pg_relation_size(), which only includes the "main" relation (unless instructed otherwise with a 2nd parameter).

SELECT pg_size_pretty(pg_relation_size('tenk1'));

But keep in mind that pg_class.relpages ...

... is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX.

That includes autovacuum. But it's not always up to date after recent write activity.

---

TOAST table ... free space map, and visibility map ... Is there a way to determine their sizes?

SELECT pg_relation_size(oid, 'fsm')    AS free_space_map
     , pg_relation_size(oid, 'vm')     AS visibility_map
     , pg_table_size(reltoastrelid)    AS toast_size_incl_vm_fsm
     , pg_relation_size(reltoastrelid) AS toast_size_main
FROM   pg_class
WHERE  oid = 'tenk1'::regclass;

TOAST tables have their own, separate free space map and visibility map. toast_size_incl_vm_fsm shows the total TOAST size including these relation forks, and toast_size_main shows the size of the main TOAST relation.

See:

• Measure the size of a PostgreSQL table row

You can double-check by looking at actual files in the DB directory. See:

• How do I find PostgreSQL's data directory?

Each DB has its own subdirectory by the name of the database OID. It contains files representing all the above relation forks, named after the corresponding OID in pg_class.