PostgreSQL - How to find out the size of tables in a database

Colleagues hello to all.

In today's article, we'll talk about how to find out the size of tables in a PostgreSQL database.

Knowing the size of your tables is an important task and every DBA should know how to do it. We will look at several universal queries that will allow to find out the size of a particular table and all at once.

In the first option, we display the 5 largest tables in the postgres database by running a query.

SELECT nspname || '.' || relname AS "relationship",
pg_size_pretty(pg_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
LIMIT 5;

In this variant the query shows the total size of all tables, including all indexes in the current database.

SELECT nspname || '.' || relname AS "relationship",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC;

A query that shows the size of a particular table in a database.

SELECT pg_size_pretty( pg_total_relation_size( 'table_name' ));

A query that shows the index size.

SELECT pg_size_pretty(pg_indexes_size('index_empid'));

Thank you all, I hope that my article helped you in some way.