'How can I check how many pages/blocks are required for storing my table in PostgreSQL?

Let's suppose we have a table PEOPLE. How can I check how many pages/blocks are required for storing it in PostgreSQL?

postgresqlstorage


Solution 1:[1]

Get the actual number of blocks currently used from the system catalog pg_class:

SELECT oid::regclass AS tbl, relpages
FROM   pg_class
WHERE  relname = 'people'; -- or "PEOPLE"?

That's only the main relation. And the count may be somewhat outdated. Not including TOAST, auxiliary relations or indexes. But it includes any amount of bloat, that might be removed with VACUUM FULL or similar tools. More:

The formulation with oid::regclass defends against any confusion with the search_path. If the table is not visible (first one in the search_path), then the output is schema-qualified.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions