Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- To get an overview about how much space is taken by what database, use:
- SELECT
- pg_database.datname,
- pg_size_pretty(pg_database_size(pg_database.datname)) AS size_pretty,
- pg_database_size(pg_database.datname) AS size
- FROM pg_database
- ORDER BY pg_database_size(pg_database.datname) DESC;
- To get more details, use:
- -- get dead tuples, last vacuum, last analyze
- SELECT pg_size_pretty(pg_relation_size(relid)) AS tablesize, schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
- FROM pg_stat_user_tables
- --WHERE relname = 'fct_encounter'
- --WHERE schemaname = 'dm'
- ORDER BY n_live_tup DESC;
- SELECT
- schemaname,
- relname as "Table",
- pg_size_pretty(pg_total_relation_size(relid)) As "Size",
- pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
- FROM pg_catalog.pg_statio_user_tables
- ORDER BY pg_total_relation_size(relid) DESC;
- It will show 3 columns:
- Table – The name of the table
- Size – The total size that this table takes
- External Size – The size that related objects of this table like indices take
- If you want to dig in deeper and see the size of all objects (at least of 64kB):
- SELECT
- relname AS objectname,
- relkind AS objecttype,
- reltuples AS "#entries",
- pg_size_pretty(relpages::bigint*8*1024) AS size
- FROM pg_class
- WHERE relpages >= 8
- ORDER BY relpages DESC;
- This will show 4 columns:
- objectname – The name of the object
- objecttype – r for the table, i for an index, t for toast data, ...
- #entries – The number of entries in the object (e.g. rows)
- size – The size of the object
Advertisement
Add Comment
Please, Sign In to add comment