How to Get Sizes of Database Objects in PostgreSQL

Dmytro Tus

Full Stack Web developer

You can use the pg_total_relation_size() function to find the size of the biggest tables including indexes.

For example, the following query returns the top 5 biggest tables in the dvdrental database:

SELECT
    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;

Here is the output:

relation  | total_size
------------+------------
 rental           | 2352 kB
 payment      | 1816 kB
 film               | 936 kB
 film_actor    | 488 kB
 inventory     | 440 kB
(5 rows)