Get largest table sizes in PostgreSQL

In this tutorial, we are going to get top 5 largest table in PostgreSQL and sort the tables of database by size in descending order.

Top 5 largest tables in descending order:

SELECT nspname || '.' || relname AS "relation", 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 limit 5; relation | total_size -------------------+------------ public.accounts | 8192 bytes public.orders | 8192 bytes public.orderlines | 8192 bytes accounts.account | 0 bytes test.a | 0 bytes (5 rows)

If we want to get top 10 largest tables of PostgreSQL server database in descending order put limit 10 command

Table with sizes in descending order.

SELECT nspname || '.' || relname AS "relation", 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; relation | total_size -------------------+------------ public.accounts | 8192 bytes public.orders | 8192 bytes public.orderlines | 8192 bytes accounts.account | 0 bytes test.a | 0 bytes test.b | 0 bytes test.c | 0 bytes (7 rows)

Note: If you want to get particular database’s tables sizes, use \c database_name and run the above query to get sizes information.

Related Posts

Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: