postgres size analysis

gistfile1.sql

--Questions for customer:
--- How many client nodes are you running?
--- Are they windows, linux or mixed?

-- Oldest data in activities
select min(recorded_at) from public.activities;

--how many rows over the past week per day
select count(*), date_part('day', recorded_at) from public.activities where recorded_at > now() - INTERVAL '7 days' group by 2;

--how many rows in a month
select count(*) from public.activities where recorded_at > now() - INTERVAL '1 month';

--Size of all databases
select t1.datname AS db_name,  
       pg_size_pretty(pg_database_size(t1.datname)) as db_size
from pg_database t1
order by pg_database_size(t1.datname) desc;

-- total size of biggest tables
SELECT nspname || '.' || 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 20;

--Column size of activities table
select pg_column_size('payload') as payload, pg_column_size('tags') as tags, pg_column_size('properties') as properties;

-- Details of activities table 
WITH x AS (
   SELECT count(*)                AS ct
         ,sum(length(t::text))    AS txt_len  -- length in characters
         ,'public.activities'::regclass AS tbl
   FROM   public.activities t
   )
   , y AS (
   SELECT ARRAY [
             pg_relation_size(tbl)
            ,pg_relation_size(tbl, 'vm')
            ,pg_relation_size(tbl, 'fsm')
            ,pg_table_size(tbl)
            ,pg_indexes_size(tbl)
            ,pg_total_relation_size(tbl)
            ,txt_len
          ] AS val
         ,ARRAY [
             'core_relation_size'
            ,'visibility_map'
            ,'free_space_map'
            ,'table_size_incl_toast'
            ,'indexes_size'
            ,'total_size_incl_toast_and_indexes'
            ,'live_rows_in_text_representation'
          ] AS name
   FROM   x
   )
SELECT unnest(name)                AS what
      ,unnest(val)                 AS bytes
      ,pg_size_pretty(unnest(val)) AS bytes_pretty
      ,unnest(val) / ct            AS per_row_bytes
FROM   x,y
UNION  ALL
SELECT '----------'::text, NULL::int8, '----'::text, NULL::int8
UNION  ALL
SELECT 'row_count'::text, ct
      ,NULL::text, NULL::bigint FROM x
UNION  ALL
SELECT 'live_tuples'::text, pg_stat_get_live_tuples(tbl)
      ,NULL::text, NULL::bigint FROM x
UNION  ALL
SELECT 'dead_tuples'::text, pg_stat_get_dead_tuples(tbl)
      ,NULL::text, NULL::bigint FROM x;

--Bloat (wasted bytes taken up by unavailable rows)
SELECT
  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
  ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
  iname, /*ituples::bigint, ipages::bigint, iotta,*/
  ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
  SELECT
    schemaname, tablename, cc.reltuples, cc.relpages, bs,
    CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
  FROM (
    SELECT
      ma,bs,schemaname,tablename,
      (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
      (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM (
      SELECT
        schemaname, tablename, hdr, ma, bs,
        SUM((1-null_frac)*avg_width) AS datawidth,
        MAX(null_frac) AS maxfracsum,
        hdr+(
          SELECT 1+count(*)/8
          FROM pg_stats s2
          WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
        ) AS nullhdr
      FROM pg_stats s, (
        SELECT
          (SELECT current_setting('block_size')::numeric) AS bs,
          CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
          CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
        FROM (SELECT version() AS v) AS foo
      ) AS constants
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  JOIN pg_class cc ON cc.relname = rs.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC;

sudo psql -U chef-pgsql -d actions

or for older installs:

sudo psql -U opscode-pgsql -d actions

[root@atl1cheflytics01 ~]# sudo /opt/opscode-analytics/embedded/bin/psql -U chef-pgsql -d actions
psql: FATAL: Peer authentication failed for user "chef-pgsql"
[root@atl1cheflytics01 ~]# sudo /opt/opscode-analytics/embedded/bin/psql -U opscode-pgsql -d actions
psql: FATAL: no pg_hba.conf entry for host "[local]", user "opscode-pgsql", database "actions", SSL off

root@atl1cheflytics01 ~]# su - chef-pgsql
-bash-4.1$ /opt/opscode-analytics/embedded/bin/psql -U chef-pgsql -d actions
psql (9.2.8)
Type "help" for help.

actions=# ^D\q

fwiw