Postgres Query Review Before Agents Touch Production Data

Letting an agent touch production data should start with review, not permission. A Postgres workflow needs a simple packet: what query or migration is proposed, why it is needed, what it might lock or scan, how to observe it live, and who can stop it if the plan looks wrong.

In Short

Postgres is a bad place to learn whether an agent understood the request. A harmless-looking query can scan a large table, hold locks longer than expected, or turn a schema change into a production incident. The safer pattern is to make the agent prepare evidence first: an explainable query plan, a lock and wait snapshot, a migration lint result, and a bounded read-only session for review.

The official PostgreSQL documentation treats EXPLAIN as the way to show the execution plan a statement will use, and notes that EXPLAIN ANALYZE actually executes the statement. That distinction matters for agent workflows. Production review should separate planning, observation, and execution instead of giving one prompt a direct path from question to write.

Who this is for

This is for engineering managers, staff engineers, SREs, data platform owners, and small teams that already use agents for code or operations work but do not want those agents making unreviewed database changes. It is especially useful when product engineers ask agents to inspect customer data, tune slow endpoints, write migrations, or explain why a dashboard is timing out.

It is not a replacement for database ownership. If a system has no read replica, no role separation, no backups, and no rollback habit, add those controls before expanding agent access. The workflow below assumes the team can create read-only credentials, review SQL, and route risky changes through an existing deploy path.

Starter workflow

Start with a single request template. Ask the agent to state the goal, list the exact tables involved, classify the action as read, write, schema, or operational inspection, and produce the SQL separately from the explanation. This prevents the most common failure mode: a fluent paragraph hiding a risky statement.

For slow queries, use PostgreSQL Query Plan Analyzer as the first review step. The output should call out sequential scans, join choices, sort spill risk, missing indexes, and whether EXPLAIN ANALYZE is appropriate outside production. Keep actual execution behind an explicit reviewer decision.

For live incidents, add Inspect live PostgreSQL waits, locks, and pressure with pg_activity. The pg_activity project describes a terminal view for PostgreSQL activity, waits, locks, and pressure. In a review packet, that snapshot answers a concrete question: is the database already under stress before the proposed query runs?

For schema changes, use Lint PostgreSQL migrations and SQL changes with Squawk. Squawk documents rules for PostgreSQL migration hazards such as operations that can require heavy locks. Put this before code review approval, not after a migration has reached the deploy queue.

For manual inspection, keep pgcli Interactive PostgreSQL Client as the human-operated escape hatch. A reviewer can paste approved read-only statements, inspect results, and decide whether the agent’s next suggestion is still aligned with reality.

Recommended ASE skills

• PostgreSQL Query Plan Analyzer: turns proposed SQL into a plan review before anyone treats it as safe.
• Inspect live PostgreSQL waits, locks, and pressure with pg_activity: gives incident reviewers a live operational snapshot.
• Lint PostgreSQL migrations and SQL changes with Squawk: catches migration risks before deploy review turns urgent.
• pgcli Interactive PostgreSQL Client: keeps final inspection in a familiar human-controlled database client.

The combined pattern is intentionally plain: agent drafts, tools inspect, humans approve, and production access stays narrow.

What to watch

Watch for accidental execution. EXPLAIN ANALYZE can be valuable in the right environment, but it runs the statement. Use plain EXPLAIN, staging data, or a safe replica when the risk is unclear.

Watch for lock blindness. A migration that passes syntax review can still block reads or writes. Require a lock note for every schema change, including the expected table size, index behavior, and rollback plan.

Watch for over-broad credentials. The first useful database agent role is usually read-only, scoped to the smallest schema that answers the question. Write access, DDL, and maintenance commands need separate approval paths.

Watch for missing evidence links. A good packet should cite the proposed SQL, plan output, lint output, live observation window, and reviewer decision. If those pieces are scattered across chat, the workflow is not reviewable yet.

FAQ

Should agents ever run production SQL directly?

Only after the team has strong role separation, logging, rollback, and a proven review loop. For most teams, agents should prepare SQL and evidence while a person controls execution.

Is read-only access always safe?

No. Read-only queries can still expose sensitive data or create load. Limit schemas, use replicas where possible, and require plans for expensive reads.

Where should migration linting run?

Run it before merge and again before deploy when generated SQL changes. The earlier run catches design problems; the later run catches drift from manual edits.

What is the minimum packet for a small team?

Use four fields: proposed SQL, purpose, plan or lint output, and reviewer decision. That is enough to make agent-assisted database work visible and repeatable.