CODEWITHSUNDEEP
postgresql
neneo
neneo

Reputation: 5

What is included in the size of the database obtained by pg_database_size?

What is included in the size of the database obtained by the following system management functions?

select pg_database_size('DBNAME');

Even if you add the size of the table and the size of the index with the following SQL and add them together, it is considerably smaller than the size obtained by pg_database_size.

select sum(relpages * 8192) from pg_stat_user_tables LEFT JOIN pg_class ON pg_stat_user_tables.relname=pg_class.relname

select sum(relpages * 8192) from pg_stat_user_indexes LEFT JOIN pg_class ON pg_stat_user_indexes.relname=pg_class.relname

What other factors are there in the total size of the database obtained by pg_database_size?

Upvotes: 0

Views: 508

Answers (1)

Thom Brown
Thom Brown

Reputation: 2029

This is the size of the database across all tablespaces except for pg_global. This includes catalogs (including comments and statistics), freespace maps, visibility maps, views, functions etc, but most significantly, this includes pg_toast tables, which is where large columns are stored outside of the table files.

You're just checking user tables and indexes, which won't include the catalogs, functions, views or toast tables.

It's also better to join by oid rather than relname as relname can be duplicate in different tablespaces. But in any case, relpages is just a planner estimate and won't be that reliable.

You could run:

SELECT sum(relpages) * 8192 FROM pg_class;

as it will apply to all objects in the database, but this will still not be the true value. pg_database_size will check the actual space used by the database files.

Upvotes: 2

Related Questions