-- Group roles hold privileges; login roles are members.
CREATE ROLE app_ro NOLOGIN;
CREATE ROLE app_rw NOLOGIN;

GRANT USAGE ON SCHEMA app TO app_ro, app_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_rw;

-- Future objects too (run as the role that creates the tables):
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;

-- Login roles:
CREATE ROLE svc_api LOGIN PASSWORD '...' IN ROLE app_rw;
CREATE ROLE analyst_anne LOGIN PASSWORD '...' IN ROLE app_ro;

# postgresql.conf — starting values for a dedicated 16GB server; measure after.
shared_buffers = 4GB              # 25% of RAM
effective_cache_size = 12GB       # 75% of RAM (planner hint, not allocation)
work_mem = 32MB                   # per sort/hash per query — keep conns × work_mem « RAM
maintenance_work_mem = 512MB      # vacuum/index builds
max_connections = 200             # use PgBouncer instead of raising this
wal_compression = on
shared_preload_libraries = 'pg_stat_statements'

SELECT pg_reload_conf();                                   -- reloadable knobs
SELECT name FROM pg_settings WHERE pending_restart;        -- needs restart?
SELECT name, setting, source FROM pg_settings WHERE source <> 'default';

-- Default scale factor 0.2 = vacuum after 20% dead rows — too lazy for hot tables.
ALTER TABLE app.events SET (
  autovacuum_vacuum_scale_factor = 0.02,   -- vacuum at 2% dead rows
  autovacuum_analyze_scale_factor = 0.01
);
-- Check autovacuum activity:
SELECT relname, last_autovacuum, n_dead_tup, n_live_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- What is running now (and what it waits on):
SELECT pid, state, wait_event_type, wait_event, now() - query_start AS runtime,
       left(query, 80) AS query
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY runtime DESC;

-- Top queries by total time (needs pg_stat_statements):
SELECT round(total_exec_time) AS ms, calls, rows, left(query, 100) AS query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- Table sizes incl. indexes and toast:
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

-- Unused indexes (candidates for removal — check replicas first):
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Cache hit ratio (want > 0.99 on OLTP):
SELECT sum(blks_hit)::float / nullif(sum(blks_hit) + sum(blks_read), 0)
FROM pg_stat_database;

-- In a versioned migration, never ad hoc:
CREATE EXTENSION IF NOT EXISTS pg_trgm;      -- fuzzy text search
CREATE EXTENSION IF NOT EXISTS pgcrypto;     -- gen_random_uuid() pre-v13
SELECT extname, extversion FROM pg_extension;             -- installed
SELECT name, default_version FROM pg_available_extensions -- available
WHERE name LIKE 'pg%' LIMIT 20;

# Same-host major upgrade (minutes of downtime, hard links, no data copy):
pg_upgrade --link \
  --old-datadir /var/lib/postgresql/15/main \
  --new-datadir /var/lib/postgresql/16/main \
  --old-bindir /usr/lib/postgresql/15/bin \
  --new-bindir /usr/lib/postgresql/16/bin
# Then ALWAYS:
vacuumdb --all --analyze-in-stages