spb/ultra-sharp-agent-skills Public

1<!--2Author: Simon-Pierre Boucher3Contact: contact@spboucher.ai4-->56# PostgreSQL Administration — Patterns78## Contents9- Roles and privileges10- Configuration starting points11- Autovacuum tuning12- Monitoring queries13- Extensions14- Upgrades15- Gotchas1617## Roles and privileges1819```sql20-- Group roles hold privileges; login roles are members.21CREATE ROLE app_ro NOLOGIN;22CREATE ROLE app_rw NOLOGIN;2324GRANT USAGE ON SCHEMA app TO app_ro, app_rw;25GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;26GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;27GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_rw;2829-- Future objects too (run as the role that creates the tables):30ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_ro;31ALTER DEFAULT PRIVILEGES IN SCHEMA app32  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;3334-- Login roles:35CREATE ROLE svc_api LOGIN PASSWORD '...' IN ROLE app_rw;36CREATE ROLE analyst_anne LOGIN PASSWORD '...' IN ROLE app_ro;37```3839Audit: `\du+` (roles), `\dp app.*` (table privileges).4041## Configuration starting points4243```ini44# postgresql.conf — starting values for a dedicated 16GB server; measure after.45shared_buffers = 4GB              # 25% of RAM46effective_cache_size = 12GB       # 75% of RAM (planner hint, not allocation)47work_mem = 32MB                   # per sort/hash per query — keep conns × work_mem « RAM48maintenance_work_mem = 512MB      # vacuum/index builds49max_connections = 200             # use PgBouncer instead of raising this50wal_compression = on51shared_preload_libraries = 'pg_stat_statements'52```5354Reload vs restart:5556```sql57SELECT pg_reload_conf();                                   -- reloadable knobs58SELECT name FROM pg_settings WHERE pending_restart;        -- needs restart?59SELECT name, setting, source FROM pg_settings WHERE source <> 'default';60```6162## Autovacuum tuning6364```sql65-- Default scale factor 0.2 = vacuum after 20% dead rows — too lazy for hot tables.66ALTER TABLE app.events SET (67  autovacuum_vacuum_scale_factor = 0.02,   -- vacuum at 2% dead rows68  autovacuum_analyze_scale_factor = 0.0169);70-- Check autovacuum activity:71SELECT relname, last_autovacuum, n_dead_tup, n_live_tup72FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;73```7475## Monitoring queries7677```sql78-- What is running now (and what it waits on):79SELECT pid, state, wait_event_type, wait_event, now() - query_start AS runtime,80       left(query, 80) AS query81FROM pg_stat_activity WHERE state <> 'idle' ORDER BY runtime DESC;8283-- Top queries by total time (needs pg_stat_statements):84SELECT round(total_exec_time) AS ms, calls, rows, left(query, 100) AS query85FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;8687-- Table sizes incl. indexes and toast:88SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total89FROM pg_catalog.pg_statio_user_tables90ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;9192-- Unused indexes (candidates for removal — check replicas first):93SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))94FROM pg_stat_user_indexes WHERE idx_scan = 095ORDER BY pg_relation_size(indexrelid) DESC;9697-- Cache hit ratio (want > 0.99 on OLTP):98SELECT sum(blks_hit)::float / nullif(sum(blks_hit) + sum(blks_read), 0)99FROM pg_stat_database;100```101102## Extensions103104```sql105-- In a versioned migration, never ad hoc:106CREATE EXTENSION IF NOT EXISTS pg_trgm;      -- fuzzy text search107CREATE EXTENSION IF NOT EXISTS pgcrypto;     -- gen_random_uuid() pre-v13108SELECT extname, extversion FROM pg_extension;             -- installed109SELECT name, default_version FROM pg_available_extensions -- available110WHERE name LIKE 'pg%' LIMIT 20;111```112113## Upgrades114115```bash116# Same-host major upgrade (minutes of downtime, hard links, no data copy):117pg_upgrade --link \118  --old-datadir /var/lib/postgresql/15/main \119  --new-datadir /var/lib/postgresql/16/main \120  --old-bindir /usr/lib/postgresql/15/bin \121  --new-bindir /usr/lib/postgresql/16/bin122# Then ALWAYS:123vacuumdb --all --analyze-in-stages124```125126Near-zero-downtime alternative: logical replication — create publication on old,127subscription on new, wait for sync, switch the application, drop subscription.128Statistics are NOT migrated by either path — `ANALYZE` is mandatory.129130## Gotchas131132- `work_mem` is per **operation**, not per connection — a single query with 4133  sorts can use 4 × work_mem. This is the classic OOM cause.134- `GRANT ALL ON ALL TABLES` does not cover tables created later — you need135  `ALTER DEFAULT PRIVILEGES` (and it only applies to objects created by the136  role that ran it).137- `shared_buffers` beyond ~8GB often yields nothing — the OS page cache does138  the rest; measure before going higher.139- PgBouncer transaction mode breaks session state: no `SET`, no advisory locks,140  no `LISTEN/NOTIFY`, no prepared statements (before PgBouncer 1.21).141- `VACUUM FULL` takes an ACCESS EXCLUSIVE lock and rewrites the table — it is142  an outage, not maintenance. Prefer `pg_repack`.143- On managed services (RDS, Cloud SQL), `shared_preload_libraries` is set via144  parameter group + reboot, and there is no true superuser.145