Azpg config tuning

Tuning server parameters on Azure Database for PostgreSQL Flexible Server

On Flexible Server you do not edit postgresql.conf and you cannot ALTER SYSTEM most parameters — the control plane owns the config. You change parameters through the Azure surface: az postgres flexible-server parameter set, the portal, Bicep/Terraform, or the REST API. Postgres then applies them either immediately (dynamic) or on the next restart (static).

This skill is read-write and explicit. Reading a parameter's current value and listing which parameters need a restart change nothing and are auto-runnable. Persisting a new value with parameter set is the mutation — for a static parameter it can trigger a server restart (a brief outage), so it is guarded and requires an explicit go-ahead.

Live instance context (dynamic injection)

Inject the real config so tuning advice matches the actual tier and current values, not defaults:

• Tier / memory (sizing drives every memory parameter): !az postgres flexible-server show --resource-group "$RG" --name "$SERVER" --query "{sku:sku.name, tier:sku.tier, storageGB:storage.storageSizeGb, ha:highAvailability.mode}" -o tsv
• Key memory + connection settings right now (unit-aware, shows source): !psql "$PGCONN" -tAc "SELECT name, setting, unit, source, pending_restart FROM pg_settings WHERE name IN ('shared_buffers','work_mem','maintenance_work_mem','effective_cache_size','max_connections','max_worker_processes') ORDER BY name;"
• Anything staged but awaiting a restart: !psql "$PGCONN" -tAc "SELECT name, setting FROM pg_settings WHERE pending_restart;"
• Is the built-in pooler on? !az postgres flexible-server parameter show --resource-group "$RG" --server-name "$SERVER" --name pgbouncer.enabled --query value -o tsv

pending_restart = t means a static change is staged and the value is not live yet — the server must be restarted for it to take effect.

When to use this skill

• "Queries spill to disk / sorts are slow" → raise work_mem (carefully — it's per-operation, not per-connection).
• "Index builds and VACUUM are slow" → raise maintenance_work_mem.
• "Autovacuum can't keep up / bloat is growing" → make autovacuum more aggressive.
• "Too many connections" errors → pool with PgBouncer before raising max_connections.
• "I need to change <parameter> on the server."
• Setting safe guardrails: statement_timeout, lock_timeout, idle_in_transaction_session_timeout.

Use azpg-stat-diagnostics / azpg-explain-analyze first to prove which knob matters — tuning blind wastes a restart.

Decision flow

1. Identify the symptom precisely (read · auto) — ideally from azpg-stat-diagnostics. "Slow" is not a tuning target; "sorts spilling to disk under concurrency" is.
2. Read the current value and its context (read · auto) — scripts/inspect_params.sql shows setting, unit, context (postmaster/sighup/user), source, and pending_restart. context tells you whether a restart is required (postmaster = static/restart).
3. Test session-scoped first where possible (read · auto, non-persistent) — for work_mem, SET work_mem='64MB'; then re-EXPLAIN (ANALYZE) in the same session. This proves the value helps without touching the server and affects only your session.
4. Persist the change (write · guarded · explicit) — once proven, apply it server-wide with scripts/set_parameter.sh (previews the az command, warns if a restart is required, --dry-run by default until you pass --apply).
5. Restart only if the parameter is static (write · guarded · explicit) — the script tells you; a restart is a brief outage (HA fails over; single-server blips). Never restart silently.
6. Verify (read · auto) — re-read pg_settings; confirm pending_restart cleared and source = 'user-override'.

Which parameter for which symptom

Symptom (from diagnostics)	Parameter	Restart?	Notes
Sorts/hashes spill to disk	work_mem	No (dynamic)	Per operation per connection — total = work_mem × concurrent sorts. Raise gradually.
Slow CREATE INDEX / VACUUM	maintenance_work_mem	No	Only a few run at once, so it can be large.
Low cache hit ratio, RAM to spare	effective_cache_size	No	A planner hint (no RAM reserved); set ≈ 50–75% of memory so index scans are costed correctly.
Autovacuum lagging, bloat rising	autovacuum_vacuum_scale_factor, autovacuum_vacuum_cost_limit, autovacuum_naptime	No	Lower scale factor = vacuum sooner; higher cost limit = vacuum faster.
"too many connections"	PgBouncer (pgbouncer.enabled, pgbouncer.pool_mode)	See note	Pool before raising max_connections.
Runaway queries / stuck idle txns	statement_timeout, idle_in_transaction_session_timeout	No	Cheap safety rails.
max_connections, shared_buffers	—	Yes (static)	context = postmaster; Azure sizes these to the tier — change only with reason.

Connection pooling (PgBouncer) — prefer over raising max_connections

Each Postgres connection costs memory and a backend process; thousands of idle app connections starve real work. Flexible Server ships built-in PgBouncer on port 6432 (same hostname, port 6432 instead of 5432). Enable and shape it with parameters, then point the app at 6432:

• pgbouncer.enabled = true
• pgbouncer.pool_mode = transaction (highest reuse; app must not rely on session-level state / session-scoped SET, advisory locks, etc.)
• pgbouncer.default_pool_size, pgbouncer.max_client_conn — size the pool.

Raising max_connections is a static change (restart) and multiplies memory pressure; pooling usually solves "too many connections" without either cost.

Safety protocol

1. Prove the knob matters first. A restart or a fleet-wide work_mem bump without evidence is how you turn one slow query into an OOM. Come from azpg-stat-diagnostics / azpg-explain-analyze.
2. Session-scope before server-scope. For user-context parameters, SET in one session and re-measure — zero blast radius — before persisting.
3. set_parameter.sh is dry-run by default. It prints the current value, the az … parameter set it would run, and whether the parameter is static (restart). It changes nothing until --apply.
4. Restart is never implicit. Static parameters stage as pending_restart; the restart is a separate, explicit, announced step (it is a brief outage).
5. work_mem is a trap. It's per sort/hash per connection. 64MB × hundreds of connections each doing multiple sorts can exceed server memory. Multiply it out against max_connections before applying.
6. Don't fight Azure's tier defaults blindly. shared_buffers, max_connections, and friends are sized to the SKU; the right fix for "not enough memory" is often a bigger tier, not an override that destabilises the box.

Bundled files

• reference.md — parameter-by-parameter guidance (memory model, autovacuum math, timeouts), the dynamic-vs-static rule via pg_settings.context, and how Azure derives defaults from the tier.
• azure-constraints.md — no ALTER SYSTEM / postgresql.conf; parameters Azure locks; static-parameter restart semantics and HA failover behaviour; PgBouncer specifics; --source user-override to revert.
• scripts/inspect_params.sql — read-only dump of the tuning-relevant pg_settings rows with unit, context, source, and pending_restart.
• scripts/set_parameter.sh — guarded write; previews the az parameter set, flags static/restart parameters, --dry-run default, --apply to execute.
• scripts/README.md — script catalog + safety class.
• examples/tuning_work_mem.md — worked end-to-end: prove with session SET, do the memory arithmetic, persist safely.
• EVALUATION.md — trigger prompts, expected behaviour, conventions self-review.