Postgres Reference

Queries

Size statistics by table

WITH table_data AS (
    SELECT c.oid,
           nspname                               AS table_schema,
           relname                               AS table_name,
           c.reltuples::BIGINT                   AS rows,
           pg_total_relation_size(c.oid)         AS total_bytes,
           pg_indexes_size(c.oid)                AS index_bytes,
           pg_total_relation_size(reltoastrelid) AS toast_bytes,
           pg_total_relation_size(c.oid)
            - pg_indexes_size(c.oid)
            - COALESCE(pg_total_relation_size(
                reltoastrelid), 0)               AS table_bytes
      FROM pg_class c
 LEFT JOIN pg_namespace n
        ON n.oid = c.relnamespace
     WHERE relkind = 'r'
)
    SELECT table_name,
           rows,
           pg_size_pretty(total_bytes)        AS total_size,
           pg_size_pretty(index_bytes)        AS index_size,
           pg_size_pretty(toast_bytes)        AS toast_size,
           pg_size_pretty(table_bytes)        AS table_size,
           pg_size_pretty(total_bytes / rows) AS total_per_row,
           pg_size_pretty(index_bytes / rows) AS index_per_row,
           pg_size_pretty(table_bytes / rows) AS table_per_row
      FROM table_data
     WHERE table_schema = 'public' AND rows > 0
  ORDER BY total_bytes DESC;

Size statistics by index

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY pg_relation_size(quote_ident(indexrelname)::text) desc;

Index stats

select * from pg_stat_user_indexes order by indexrelname;

Note that it is possible to examine the number of index scans (including index-only scans and bitmap index scans) by examining pg_stat_user_indexes.idx_scan. If your covering index isn't being used, you're essentially paying for the overhead of maintaining it during writes with no benefit in return. Drop the index!
https://wiki.postgresql.org/wiki/Index-only_scans

Check last autovac times, stats, expected upcoming

adapted from https://dba.stackexchange.com/a/147121 - takes into account specific table settings

Autovacuum stats

WITH config AS (
  SELECT oid, reltuples, CAST(
         coalesce(nullif(split_part(split_part(array_to_string(reloptions, ','),
                  'autovacuum_vacuum_threshold=', 2), ',', 1), ''), 
                  current_setting('autovacuum_vacuum_threshold'))::BIGINT
         + coalesce(nullif(split_part(split_part(array_to_string(reloptions, ','),
                  'autovacuum_vacuum_scale_factor=', 2), ',', 1), ''), 
                  current_setting('autovacuum_vacuum_scale_factor'))::NUMERIC
         * reltuples AS NUMERIC) AS threshold
    FROM pg_class
) 
  SELECT stats.relname,
         TO_CHAR(config.reltuples, '9G999G999G999')           AS tuples,
         TO_CHAR(stats.n_dead_tup, '9G999G999G999')           AS dead_tuples,
         TO_CHAR(config.threshold, '9G999G999G999')           AS threshold,
         ROUND(stats.n_dead_tup / config.threshold, 2)        AS autovacuum_percent,
         autovacuum_count                                     AS autovacuum_count,
         TO_CHAR(stats.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum,
         vacuum_count                                         AS vacuum_count,
         TO_CHAR(stats.last_vacuum, 'YYYY-MM-DD HH24:MI')     AS last_vacuum
    FROM pg_stat_user_tables stats
    JOIN config  ON stats.relid = config.oid
ORDER BY config.reltuples DESC;

Autoanalyze stats

WITH config AS (
  SELECT oid, reltuples, CAST(
         coalesce(nullif(split_part(split_part(array_to_string(reloptions, ','),
                  'autovacuum_analyze_threshold=', 2), ',', 1), ''), 
                  current_setting('autovacuum_analyze_threshold'))::BIGINT
         + coalesce(nullif(split_part(split_part(array_to_string(reloptions, ','),
                  'autovacuum_analyze_scale_factor=', 2), ',', 1), ''), 
                  current_setting('autovacuum_analyze_scale_factor'))::NUMERIC
         * reltuples AS NUMERIC) AS threshold
    FROM pg_class
) 
  SELECT stats.relname,
         TO_CHAR(config.reltuples, '9G999G999G999')              AS tuples,
         TO_CHAR(stats.n_mod_since_analyze, '9G999G999G999')     AS mod_tuples,
         TO_CHAR(config.threshold, '9G999G999G999')              AS threshold,
         ROUND(stats.n_mod_since_analyze / config.threshold, 2)  AS autoanalyze_pct,
         autoanalyze_count                                       AS autoanalyze_count,
         TO_CHAR(stats.last_autoanalyze, 'YYYY-MM-DD HH24:MI')   AS last_autoanalyze,
         analyze_count                                           AS analyze_count,
         TO_CHAR(stats.last_analyze, 'YYYY-MM-DD HH24:MI')       AS last_analyze
    FROM pg_stat_user_tables stats
    JOIN config  ON stats.relid = config.oid
ORDER BY config.reltuples DESC;

misc stats

select relname, idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, n_mod_since_analyze, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count from pg_stat_user_tables;

Ideal column order

Sort the columns by their type length as defined in pg_type

Much like filling a jar with rocks, pebbles, and sand, the most efficient way to declare a Postgres table is by the column alignment type. Bigger columns first, medium columns next, small columns last, and weird exceptions like NUMERIC and TEXT tacked to the end as if they were dust in our analogy.

source

Resources

Postgres temp table quick update method

Query Performance and Autovacuuming for Large Tables

ALTER TABLE table_name SET autovacuum_vacuum_scale_factor = 0.02;

Fast batch update using COPY

Char/Date formatting functions