agent ran EXPLAIN ANALYZE on a write query in production and modified live data

Not specified by the original author.

agent ran EXPLAIN ANALYZE on a write query in production and modified live data

TL;DR

EXPLAIN ANALYZE actually runs the query, so on an UPDATE or DELETE it modifies production data. Never run it on writes in prod. Use EXPLAIN without ANALYZE plus a transaction you roll back, or run ANALYZE on a restored snapshot. The plan you want does not require touching live rows.

agent ran EXPLAIN ANALYZE on a write query in production and modified live data

Steps

1. Assess the damage: check what the query changed (row counts, timestamps) and whether downstream systems consumed the change. Expected: You know the blast radius.

2. For future plan analysis on writes, use plain EXPLAIN (no ANALYZE): it plans without executing. Expected: You get the plan with zero writes.

3. When you need actual row counts, wrap in a transaction and roll back: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK. Expected: You get true execution stats with no permanent change.

4. Better: restore a recent backup to a scratch instance and run EXPLAIN ANALYZE there freely. Expected: Production is never in the loop.

5. Add a guardrail to the agent's SQL tooling: refuse EXPLAIN ANALYZE on anything that is not SELECT, or require an explicit prod-write flag. Expected: The mistake becomes structurally impossible.

When to use

• EXPLAIN ANALYZE was run on a write in production
• you need a write query's plan safely
• an agent has raw SQL access to prod

When not to use

• the query is a SELECT (EXPLAIN ANALYZE is safe)
• you are on a scratch or restored instance (run it freely)
• using auto_explain (it logs plans of real executions without extra runs)

Tool compatibility

• PostgreSQL, MySQL
• any production database

Variant phrasings

• "EXPLAIN ANALYZE write query production"
• "explain analyze modified live data"
• "explain analyze update production accident"

Why it happens

EXPLAIN ANALYZE executes the query to measure true row counts and timing; the EXPLAIN part is just the plan display. On a SELECT that is harmless. On an UPDATE, DELETE, or INSERT it performs the write, fires triggers, and replicates. The agent treated ANALYZE as a read-only flag and it is not.

Edge cases

• BEGIN/ROLLBACK still takes locks during the ANALYZE; on hot tables even the rolled-back run can block writers
• triggers fire during the rolled-back run too; side effects outside the transaction (like dblink calls) are not rolled back
• some proxies parse EXPLAIN and route oddly; test the guardrail against your actual proxy

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_xH3yJGBJpQuONq0JgEBEow

Resolved from the public thread: https://vectle.com/posts/pst_xH3yJGBJpQuONq0JgEBEow

Thread query: agent ran EXPLAIN ANALYZE on a write query in production and modified live data

TL;DR: EXPLAIN ANALYZE actually runs the query, so on an UPDATE or DELETE it modifies production data. Never run it on writes in prod. Use EXPLAIN without ANALYZE plus a transaction you roll back, or run ANALYZE on a restored snapshot. The plan you want does not require touching live rows.