<!--
Author: Simon-Pierre Boucher
Contact: contact@spboucher.ai
-->

# PostgreSQL Administration — Patterns

## Contents
- Roles and privileges
- Configuration starting points
- Autovacuum tuning
- Monitoring queries
- Extensions
- Upgrades
- Gotchas

## Roles and privileges

```sql
-- 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;
```

Audit: `\du+` (roles), `\dp app.*` (table privileges).

## Configuration starting points

```ini
# 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'
```

Reload vs restart:

```sql
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';
```

## Autovacuum tuning

```sql
-- 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;
```

## Monitoring queries

```sql
-- 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;
```

## Extensions

```sql
-- 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;
```

## Upgrades

```bash
# 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
```

Near-zero-downtime alternative: logical replication — create publication on old,
subscription on new, wait for sync, switch the application, drop subscription.
Statistics are NOT migrated by either path — `ANALYZE` is mandatory.

## Gotchas

- `work_mem` is per **operation**, not per connection — a single query with 4
  sorts can use 4 × work_mem. This is the classic OOM cause.
- `GRANT ALL ON ALL TABLES` does not cover tables created later — you need
  `ALTER DEFAULT PRIVILEGES` (and it only applies to objects created by the
  role that ran it).
- `shared_buffers` beyond ~8GB often yields nothing — the OS page cache does
  the rest; measure before going higher.
- PgBouncer transaction mode breaks session state: no `SET`, no advisory locks,
  no `LISTEN/NOTIFY`, no prepared statements (before PgBouncer 1.21).
- `VACUUM FULL` takes an ACCESS EXCLUSIVE lock and rewrites the table — it is
  an outage, not maintenance. Prefer `pg_repack`.
- On managed services (RDS, Cloud SQL), `shared_preload_libraries` is set via
  parameter group + reboot, and there is no true superuser.