Title: Postgres DDL blocking and non-blocking operations
URL: https://leopard.in.ua/2016/09/20/safe-and-unsafe-operations-postgresql#.YmRFt5NByDU

============================================================

Title: Postgres size functions (and many others)
URL: https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE

============================================================

Title: count number of rows for some time interval divided by time slots
Query: 
select (extract(minute FROM created_at)::int / 5) AS min5_slot, count(*) from some_events re where created_at > now() - interval '3 hour' group by 1 order by 1;
select (extract(minute FROM created_at)::int/1) AS min1_slot, count(*) from some_events  re where created_at > now() - interval '30 minute' group by 1;
select (extract(day FROM updated_at)::int / 1) AS d1_slot, (extract(hour FROM updated_at)::int / 1) AS h1_slot, count(*) from some_events re where created_at > now() - interval '7 day' group by 1,2 order by 1,2;

============================================================

Title: estimate number of rows without count
Query: SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'participants_2019_10';

============================================================

Title: list existing indices:
Query: select * from pg_catalog.pg_indexes where tablename = 'quests';

============================================================

Title: currently running queries (example for vacuum but good for all)
Source: 
Query: SELECT pid, age(clock_timestamp(), query_start), usename, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%' AND query LIKE '%autovacuum%';

And deleting such queries:
select pg_cancel_backend(pid) from pg_stat_activity where usename = 'XYZ' and state <> 'idle';


============================================================

Title: show last auto-vacuum and auto-analyze
Source: 
Query: select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

============================================================

Title: vacuum thresholds and more
Source: https://medium.com/gett-engineering/scaling-postgresql-check-your-vacuum-f03092a6399e
Query: SELECT
   pg_stat_user_tables.relname,
   pg_stat_user_tables.n_dead_tup,
   50 + 0.1 * pg_class.reltuples as vacuum_threshold,
   pg_class.reltuples,
   pg_stat_user_tables.n_live_tup,
   pg_stat_user_tables.n_tup_del,
   pg_stat_user_tables.n_tup_upd,
   pg_stat_user_tables.autovacuum_count,
   pg_stat_user_tables.last_vacuum,
   pg_stat_user_tables.last_autovacuum,
   now() as now,
   pg_stat_user_tables.n_dead_tup > (50 + 0.1 * pg_class.reltuples) as is_vacuum
FROM
   pg_stat_user_tables INNER JOIN pg_class ON pg_stat_user_tables.relname = pg_class.relname
ORDER BY
   pg_stat_user_tables.n_dead_tup > (50 + 0.1 * pg_class.reltuples) DESC;


==========================================

Title: estimate wasted space in DB
Source: https://wiki.postgresql.org/wiki/Show_database_bloat
Query: SELECT
  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
  ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,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::FLOAT/iotta END)::NUMERIC,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


==========================================
Title: size of tables/objects in the database
Query: 
SELECT
   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;
 NOTE: Size includes external size (that is for indexes, stored procedures, etc.)
 
Query:
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;
NOTE: entries - number of rows for table, size is for per relation (table w/o index)

Query - rows counter without count:
SELECT *  FROM pg_class WHERE relname = 'table_name';