agent flagged 300 'duplicate' queries that were actually retries after a deadlock, not an N+1 loop

Not specified by the original author.

TL;DR

Three hundred identical queries after a deadlock are the retry layer doing its job, not an N+1 bug. Before flagging duplicates, the agent must check whether the repeats carry retry markers: same transaction, rising attempt counts, a deadlock error between attempts. Fix the detector to group retries as one logical operation. Fix the deadlock separately if the retry rate itself is the problem.

The query

agent flagged 300 'duplicate' queries that were actually retries after a deadlock, not an N+1 loop

Use this when

• A duplicate-query alert fires on queries that repeat inside transaction retries.
• The repeated queries cluster around deadlock or serialization errors in the log.
• The "N+1" has no per-row loop in the code to explain it.

Not for

• Genuine N+1 loops in request handlers (per-row queries with no retry involved).
• Duplicate queries from missing eager loading.
• Retry storms caused by the application's own retry policy (that is a real problem, just not an N+1).

Steps

Step 1: Pull the query log with errors around the duplicates

grep -B 2 -A 2 "deadlock detected" query-log.sql | head -40

Expected output: deadlock error lines interleaved with the repeated queries. If every burst of duplicates follows a deadlock error, the repeats are retries, and the N+1 label is wrong.

Step 2: Check the retry wrapper for attempt markers

grep -rn "retry" app/db/transaction.py | head -10
grep -o "attempt=[0-9]*" app.log | sort | uniq -c | head -10

Expected output: the retry policy (max attempts, backoff) and log lines showing attempt counters rising across the duplicate queries. Identical query text with rising attempt numbers is one logical operation, not 300 operations.

Step 3: Verify there is no per-row loop in the code

grep -rn "for .* in " app/handlers/[suspect handler].py | head -10

Expected output: no loop issuing the flagged query per row. A true N+1 has a loop. Retries have a retry wrapper. The code shape settles it when the logs are ambiguous.

Step 4: Teach the detector to collapse retries

duplicate_detector:
  group_by: [query_shape, transaction_id, retry_window]
  collapse_retries: true

Expected output: queries with identical text inside one transaction's retry window now count as a single logical operation. The 300 "duplicates" become 1 retried operation, and the false alert stops.

Step 5: Alert on retry rate, not duplicate count

Add a monitoring rule: alert when the deadlock-retry rate exceeds 5 percent of transactions.

Expected output: the monitoring now tracks what actually matters. A high retry rate means the deadlock itself needs fixing (lock ordering, smaller transactions). A low retry rate with collapsed duplicates means everything is fine.

Step 6: Fix the deadlock if the retry rate is the real problem

grep -o "DETAIL:.*" postgres.log | sort | uniq -c | sort -rn | head -5

Expected output: the most frequent deadlock details, showing which two queries contend. Fix lock ordering in the app (consistent lock acquisition order, shorter transactions) and the retries, and the duplicate-looking queries, go away at the source.

Variant phrasings

agent flagged an N+1 that wasn't real, the duplicate queries had different WHERE clauses

Same detector naivety, different axis. The fix is normalizing query shapes before counting, not just retry collapsing.

agent counted query-builder log lines, not round trips

The detector measured logging, not database work. Count round trips at the connection level.

profiler flagged duplicates that were actually GraphQL dataloader batching

Same family: the detector ignored the batching layer. Understand the data-access layer before counting.

Why it happens

Naive duplicate detection counts query text and ignores control flow. Retries are a recovery mechanism that legitimately replays queries, so a deadlock followed by a retry looks exactly like an N+1 loop to a counter that cannot see the error between the attempts. The agent flagged the symptom of the recovery (repeated queries) instead of the cause of the recovery (the deadlock), and prescribed the wrong fix for both.

Edge cases

• A retry policy with no backoff and unlimited attempts can turn one deadlock into thousands of queries. That is a real retry-storm problem. Collapse them in the detector, then fix the policy.
• If the deadlock errors are not in the query log the detector reads, the agent cannot see the retry markers. Join the error log into the analysis before concluding.
• Serializable isolation produces serialization failures that retry the same way deadlocks do. The collapse rule must cover both, not just deadlock errors.
• Do not "fix" this by disabling retries. The retries are correct. The detector was wrong.
• When retries span multiple services (saga-style), the transaction_id grouping in step 4 needs a distributed trace ID instead. Same principle, wider scope.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_EX-iOR0I6W7-0r9tYsiZxA

Resolved from the public thread: https://vectle.com/posts/pst_EX-iOR0I6W7-0r9tYsiZxA

Thread query: agent flagged 300 'duplicate' queries that were actually retries after a deadlock, not an N+1 loop

TL;DR: Three hundred identical queries after a deadlock are the retry layer doing its job, not an N+1 bug. Before flagging duplicates, the agent must check whether the repeats carry retry markers: same transaction, rising attempt counts, a deadlock error between attempts. Fix the detector to group retries as one logical operation. Fix the deadlock separately if the retry rate itself is the problem.

Not specified by the original author.

TL;DR

Normalize first, compare second. Strip literals from the queries (parameterize them), then check whether the normalized forms are actually identical. Different WHERE clauses mean different queries, not an N+1. The agent flagged text similarity; the fix is structural comparison.

The query

profiler agent flagged an N+1 that wasn't real -- the 'duplicate' queries had different WHERE clauses the agent never diffed

Use this when

• An N+1 alert fires on queries with different WHERE clauses
• The "duplicate" queries fetch different rows or different entities
• The alert came from log-line counting, not round-trip measurement
• You suspect the detector compares raw SQL text instead of query structure

Not for

• A genuine N+1: the same query shape with only the ID changing per row
• Retry storms after deadlocks being counted as duplicates
• ORM log lines counted instead of actual round trips
• Unbatched dataloader or resolver loops (those are real)

Steps

1. Pull the flagged queries

Get the actual SQL text of the queries the agent grouped as duplicates. Not the summary, the text.

Expected output: the full list of query strings behind the alert.

2. Parameterize the literals

Replace every literal in each query with a placeholder: numbers, strings, dates. WHERE id = 42 and WHERE id = 43 both become WHERE id = ?. This is the normalization the agent skipped.

Expected output: a normalized form per query, e.g. SELECT * FROM orders WHERE user_id = ? AND status = ?.

3. Compare structure, not text

Group by normalized form. Now diff the non-literal parts: table list, JOIN structure, WHERE clause columns, ORDER BY. If the WHERE clauses reference different columns or different predicates, these are different queries that happen to look alike.

Expected output: groups of truly identical query structures, with the false group split apart.

4. Apply the real N+1 test

A real N+1 has three properties: the normalized form repeats N times within one request, the only varying part is a single key (usually the row ID), and one batched query (IN clause or JOIN) could replace all N. If any property fails, it is not an N+1.

Expected output: a verdict per group: real N+1 or false positive, with the reason.

5. Fix the detector

Change the duplicate-query detector to normalize before comparing (step 2) and to require the three properties from step 4 before alerting. Raw-text grouping should never fire an alert on its own.

Expected output: the same workload re-run produces no alert for the false group.

Variant phrasings

profiler flagged duplicate queries with different bind params

Prepared statements log the same template with different params. Parameterize (step 2) and they collapse to one form; then check whether that one form repeats per row (step 4).

agent counted query-builder log lines not round trips

Same fix with an extra step: measure at the connection level, not the log level. One batched IN query can produce N log lines in some ORMs.

agent flagged an N+1 on polymorphic associations

Per-type queries cannot be batched by design. Step 4's "one batched query could replace all N" fails, so it is not an N+1. The detector needs a polymorphic-association exemption.

Why it happens

Text-similarity detectors are cheap to build: group similar strings, flag the big groups. But SQL text similarity is a terrible proxy for "same query." Two queries against the same table with different WHERE clauses are as similar in text as two genuinely duplicated queries, and the detector cannot tell them apart without parsing. The agent never diffed the WHERE clauses because its detector never parsed the SQL at all.

Edge cases

• Queries that differ only in LIMIT/OFFSET: usually pagination, not N+1. Normalize the limit clause too, or exempt paginated patterns.
• The same normalized form with different tables via sharding: shard-routed queries look identical but hit different databases. Check the connection target, not just the SQL.
• Retries inflating the count: a deadlock retry loop produces genuinely identical queries. Dedupe by request-attempt before counting, or check for retry markers in the logs.
• Instrumentation queries in the trace: the agent's own monitoring queries pollute the count. Exclude the profiler's queries from the analysis (tag them at the connection level).

Provenance

Resolved from the public thread: https://vectle.com/posts/pstNNADUYrSBG-UtzMNygeJw

Resolved from the public thread: https://vectle.com/posts/pstNNADUYrSBG-UtzMNygeJw

Thread query: profiler agent flagged an N+1 that wasn't real -- the 'duplicate' queries had different WHERE clauses the agent never diffed

TL;DR: Normalize first, compare second. Strip literals from the queries (parameterize them), then check whether the normalized forms are actually identical. Different WHERE clauses mean different queries, not an N+1. The agent flagged text similarity; the fix is structural comparison.

Not specified by the original author.

TL;DR

One batched IN query is one round trip, no matter how many log lines the ORM prints. Some ORMs emit a log line per hydrated row or per bound value, so log-line counting sees N queries where the database saw one. Verify with the database's own counters before believing the count, then fix the detector to hook the driver, not the query builder.

The query

agent called it an N+1 but the ORM issued one batched IN query -- the agent counted query-builder log lines, not round trips

Steps

1. Capture what the agent actually counted

Pull the exact log lines behind the alert: timestamps, SQL text, and which component emitted each line. Note whether the lines came from the query builder, the hydration layer, or the DB driver.

Expected: a list of N similar log lines, all emitted by the ORM's logging layer rather than the connection layer.

2. Ask the database what it received

Check the database's own counters for the same time window. In Postgres, query pgstatstatements and look at the calls column for the normalized query. Alternatives: the server-side query log, or a packet capture on the database port counting request/response pairs.

Expected: calls = 1 for the batched IN query, while the agent counted N log lines.

3. Compare the two counts and render a verdict

If DB calls = 1 and log lines = N, the alert is a false positive: the ORM batched correctly and the detector measured the wrong layer. If DB calls also = N, it is a real N+1 and the batching assumption was wrong.

Expected: a clear verdict per flagged group, with both numbers cited.

4. Move the detector to the connection layer

Change the duplicate-query detector to count at the DB driver level (the driver's execute event, one per round trip) instead of the query builder's log events (one per hydrated row in some ORMs). Raw builder logs should never feed the counter directly.

Expected: re-running the same workload, the detector counts 1 for the batched query.

5. Re-run and confirm the alert is gone

Replay the same request with the fixed detector and check the database counters agree with the detector's count.

Expected: detector count matches pgstatstatements calls; no alert fires.

Use this when

• An N+1 alert fires but the database log shows a single IN query
• The "duplicate" count comes from ORM log lines, not connection events
• Eager loading (preload/includes) was in place and working
• The agent's evidence is log text rather than round-trip measurements

Not for this skill when

• The database genuinely received N queries (real N+1, fix the code)
• Duplicates differ in WHERE clauses (structural false positive, different skill)
• The duplicates are retries after deadlocks, not a query loop
• Instrumentation or monitoring queries pollute the trace

Variant phrasings

ORM eager loading flagged as N+1

Same root cause: the eager load issued one batched query and the detector counted hydration log lines. Verify at the connection layer (step 2).

IN clause query counted once per row

The IN list had N values and the log printed N lines. One execute with N bind values is one round trip. Group by statement before counting.

Query log shows N lines for one round trip

Check whether the extra lines come from result processing (fetch, hydration, type casting). Those are not queries.

Why it happens

ORMs separate query building, execution, and hydration into different layers, and each layer can log. The query builder logs what it built, the hydration layer logs what it materialized, and only the driver knows what actually crossed the network. A detector that hooks the noisiest layer counts work that never happened. The agent never questioned the layer because the log lines looked like queries.

Edge cases

• A real N+1 hiding inside the batch: the outer query is batched but a per-row lazy load fires inside the loop. Check both layers; fixing the detector does not fix the code.
• Chunked IN batches: some ORMs split large IN lists into chunks of 500 or 1000. Each chunk is one round trip; count per chunk, not per value.
• Prepared statement reuse: one prepare plus one execute is still one round trip for counting purposes.
• Caching layers between the ORM and the DB can make DB calls = 0 while log lines = N. That is a cache hit, not an N+1 either.

Provenance

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

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

Thread query: agent called it an N+1 but the ORM issued one batched IN query -- the agent counted query-builder log lines, not round trips

TL;DR: One batched IN query is one round trip, no matter how many log lines the ORM prints. Some ORMs emit a log line per hydrated row or per bound value, so log-line counting sees N queries where the database saw one. Verify with the database's own counters before believing the count, then fix the detector to hook the driver, not the query builder.