deeejay

psql - Get database and table attributes

Jul 13th, 2015
469
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. To get an overview about how much space is taken by what database, use:
  2.  
  3. SELECT
  4.     pg_database.datname,
  5.     pg_size_pretty(pg_database_size(pg_database.datname)) AS size_pretty,
  6.     pg_database_size(pg_database.datname) AS size
  7.     FROM pg_database
  8.     ORDER BY pg_database_size(pg_database.datname) DESC;
  9.  
  10. To get more details, use:
  11.  
  12. -- get dead tuples, last vacuum, last analyze
  13. 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
  14. FROM pg_stat_user_tables
  15. --WHERE relname = 'fct_encounter'
  16. --WHERE schemaname = 'dm'
  17. ORDER BY n_live_tup DESC;
  18.  
  19. SELECT
  20.    schemaname,
  21.    relname as "Table",
  22.    pg_size_pretty(pg_total_relation_size(relid)) As "Size",
  23.    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
  24.    FROM pg_catalog.pg_statio_user_tables
  25.    ORDER BY pg_total_relation_size(relid) DESC;
  26.  
  27. It will show 3 columns:
  28.  
  29.     Table – The name of the table
  30.     Size – The total size that this table takes
  31.     External Size – The size that related objects of this table like indices take
  32.  
  33. If you want to dig in deeper and see the size of all objects (at least of 64kB):
  34.  
  35. SELECT
  36.    relname AS objectname,
  37.    relkind AS objecttype,
  38.    reltuples AS "#entries",
  39.    pg_size_pretty(relpages::bigint*8*1024) AS size
  40.    FROM pg_class
  41.    WHERE relpages >= 8
  42.    ORDER BY relpages DESC;
  43.  
  44. This will show 4 columns:
  45.  
  46.     objectname – The name of the object
  47.     objecttype – r for the table, i for an index, t for toast data, ...
  48.     #entries – The number of entries in the object (e.g. rows)
  49.     size – The size of the object
Advertisement
Add Comment
Please, Sign In to add comment