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

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.

 

SIMILAR ARTICLES

PostgreSQL - How to determine which files on disk correspond to tables in a database
PostgreSQL - How to determine which files on disk correspond to tables in a database
POSTGRESQL
Read more
PostgreSQL - How to check user privileges
PostgreSQL - How to check user privileges
POSTGRESQL
Read more
PostgreSQL - How to enable archiving of WAL logs - archive_mode
PostgreSQL - How to enable archiving of WAL logs - archive_mode
POSTGRESQL
Read more