O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Which are my biggest tables?

We've looked at how to get the size of a specific table, so now it's time to widen the problem to related areas. Rather than an absolute value for a specific table, let's look at the relative sizes.

How to do it...

The following basic query will tell us the "Top 10 Biggest Tables":

SELECT		table_name
				,pg_relation_size(table_name) as size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size DESC
LIMIT 10;

Tables are shown in descending order of size, with at most 10 rows displayed. In this case, we look at all tables in all schemas, apart from tables in the information_schema or in pg_catalog. These latter two schemas are the locations where Postgres keeps its own ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required