CODEWITHSUNDEEP
postgresqlsize
johan855
johan855

Reputation: 1626

Record table and schema sizes in PostgreSQL

Is thre a simple way to query for the size of a postgres database? I am trying to do something like this:

select 'session_metrics',pg_size_pretty(pg_total_relation_size('schema1.session_metrics'))
union
select 'transaction_metrics',pg_size_pretty(pg_total_relation_size('schema1.transaction_metrics'))
union
select 'cookie_user_metrics',pg_size_pretty(pg_total_relation_size('schema1.cookie_user_metrics'))
union
select 'cookie_transaction_metrics',pg_size_pretty(pg_total_relation_size('schema1.cookie_transaction_metrics'));

And store those values in a table so that I can later easily track the growth rates of my tables. The problem is I now have over 50 different tables and I don't want to add a line of query every time I create a new table.

I would appreciate if someone could orient me to something like:

select table_name, schema_name, size;

Upvotes: 0

Views: 2607

Answers (1)

Dmitri Goldring
Dmitri Goldring

Reputation: 4363

The 'table names' table you're looking for is pg_catalog.pg_namespace. The following query is adapted from the psql \d command: 

SELECT n.nspname as "Schema", 
    c.relname as "Name",
    pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size", 
    now() as "Timestamp"
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
  AND n.nspname !~ '^pg_toast'

I have taken the liberty of adding a timestamp since you are planning to compare sizes over time.

Upvotes: 3

Related Questions