Config drift is silent: settings tuned for the hardware of three years ago, defaults nobody revisited, one mystery parameter from a long-gone consultant. Dump the non-default settings plus a workload profile, get engine-version-aware anti-pattern flags — and apply exactly nothing except through change control.
Tier: Foundation · Workflow area: Performance Diagnostics · ~30 minutes
Instance configuration review is ideal AI work — hundreds of parameters, deep interactions, version-specific advice scattered across a decade of blog posts — with one hard rule attached: the output is a review memo, and every change it inspires goes through change control with a measured rollback plan. Config changes feel small and hit everything at once; that combination is exactly why the gate exists.
Dump non-default settings — the honest inventory.
-- PostgreSQL: everything changed from default, with where it was set
SELECT name, setting, unit, source, sourcefile
FROM pg_settings
WHERE source NOT IN ('default', 'override')
ORDER BY name;
Pair it with the context that makes advice possible: hardware (RAM, CPU count, storage type), engine version and patch level, and a one-paragraph workload description (OLTP/analytics mix, connection count, peak write rate). Pull connection reality too:
SELECT max(numbackends) AS current_conns,
(SELECT setting FROM pg_settings WHERE name = 'max_connections') AS max_conns
FROM pg_stat_database;
Prompt with version, hardware and the memo format — and forbid a tuning script.
You are reviewing a PostgreSQL 16.3 instance configuration.
Hardware: 16 vCPU, 128 GB RAM, NVMe storage. Workload: OLTP,
~2k TPS peak, 400 app connections through pgbouncer, nightly
analytics batch. Below: all non-default settings, plus current
connection counts.
Produce a REVIEW MEMO, not a tuning script:
1. RED FLAGS — settings that are dangerous or clearly wrong for
this hardware/version, each with: the risk, the evidence from
my numbers, and the version-specific reasoning.
2. STALE TUNING — settings that look sized for different hardware
or an older version.
3. MYSTERY SETTINGS — non-defaults with no obvious rationale;
phrase each as a question for the team's history.
4. LEAVE ALONE — flagged-looking settings that are actually fine
here, and why.
For each red flag: a suggested target value AND the verification
query or metric that would prove the change helped or hurt.
Do not output a combined "apply this" script.
The LEAVE ALONE section is the review's credibility test — a pass that flags everything is as useless as one that flags nothing.
Fact-check the flags against version documentation. Config advice is where LLMs most often ship stale knowledge — parameters renamed, defaults changed, guidance revised (checkpoint_segments is long gone; max_wal_size semantics evolved; JIT and parallelism defaults shifted across majors). For every red flag, confirm the parameter exists and means what the model says in your version before it enters the memo:
psql -c "SELECT name, short_desc, boot_val, min_val, max_val
FROM pg_settings WHERE name = 'max_wal_size';"
Turn accepted flags into gated changes — one at a time. Each surviving flag becomes a change ticket: current value, proposed value, reasoning, verification metric, rollback (the previous value, and whether a reload suffices or a restart is needed — check pg_settings.context). Then stage it:
-- Staging first, via ALTER SYSTEM so it is visible and revertable
ALTER SYSTEM SET shared_buffers = '32GB';
-- context = 'postmaster' -> needs restart; schedule it
SELECT name, setting, pending_restart FROM pg_settings
WHERE pending_restart;
Run the verification metric for a full business cycle in staging (or a replica) before production. Batch nothing: two config changes at once means an unattributable outcome.
Automate the drift check so next year's review is a diff. Put the desired state in code — an Ansible task per instance role — and let the review memo update the code, not just the instance:
- name: postgresql.conf managed settings
community.postgresql.postgresql_set:
name: "{{ item.name }}"
value: "{{ item.value }}"
loop:
- { name: shared_buffers, value: "32GB" }
- { name: max_wal_size, value: "8GB" }
notify: restart postgresql when required
Next review, the input is git diff plus the settings dump — mystery settings can no longer accumulate silently.
sys.configurations where value_in_use differs from default, plus tempdb file layout and MAXDOP/cost-threshold context; the classic red flags (MAXDOP unset on many cores, cost threshold at 5, tempdb single-file contention) are exactly what an engine-aware pass should catch, and sp_Blitz output is a strong second input to the same prompt.SELECT * FROM performance_schema.variables_info WHERE variable_source <> 'COMPILED' with SHOW GLOBAL VARIABLES values; the buffer-pool size, redo-log sizing and sync_binlog/durability trio are where stale hardware assumptions concentrate.V$PARAMETER WHERE isdefault = 'FALSE'), SGA/PGA targets and the memory advisories from AWR; ask the model to reason against the advisories' own recommendations, and route everything through your normal parameter-change process with the DBA team.pg_settings.context checked per change.Never apply an AI-generated configuration script wholesale — one plausible-sounding memory setting sized for the wrong unit (MB vs GB confusion is a classic) can take the instance down at startup. Every change is individually reviewed, staged, and reversible.
AI turns a hundred-parameter dump into a readable review memo in minutes; change control turns the memo into safe improvements over weeks. Collapse those two speeds into one and the config review becomes the incident.