Query table size and count in Postgres

queries.md

• Query table size:

  SELECT 
      relname AS table_name,
      pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
      pg_size_pretty(pg_relation_size(relid)) AS data_size,
      pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
  FROM 
      pg_stat_user_tables
  WHERE 
      relname = '$TABLE_NAME';

• Query all table sizes:

  SELECT 
      relname AS table_name,
      pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
      pg_size_pretty(pg_relation_size(relid)) AS data_size,
      pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
  FROM 
      pg_stat_user_tables
  ORDER BY 
      pg_total_relation_size(relid) DESC;

• Query table count:

  ANALYZE $TABLE_NAME;
  
  SELECT 
      relname AS table_name,
      n_live_tup AS approximate_row_count
  FROM 
      pg_stat_user_tables
  WHERE 
      relname = '$TABLE_NAME';

• Query all table counts:

  SELECT 
      relname AS table_name,
      n_live_tup AS approximate_row_count
  FROM 
      pg_stat_user_tables
  ORDER BY 
      n_live_tup DESC;