1. The Hook

Every database call has a cost.

Not a big one, individually. A few hundred microseconds, maybe a millisecond if you’re unlucky with connection pooling. But costs compound. And nothing compounds faster than a loop that queries the database once per iteration.

I learned this the hard way in a Rust service I built to process blockchain-adjacent event streams — though the lesson has nothing to do with blockchain and everything to do with basic database hygiene. The service ingested batches of events, and for each event, it needed to check whether a corresponding record already existed in PostgreSQL.

The first version worked. It also made one query per event. At low volume, nobody noticed. At production volume — batches of a few thousand events, running continuously — it fell over.

This is the story of the N+1 query, why it’s one of the most common performance killers in backend systems, and how a single PostgreSQL ANY() query replaced thousands of round trips.

2. The Problem: One Query Per Event

The anti-pattern is deceptively easy to write, because it mirrors how you’d solve the problem in your head: loop through the events, handle each one.

for event in events {
    let existing = sqlx::query_as::<_, EventRow>(
        "SELECT id, status FROM events WHERE external_id = $1"
    )
    .bind(&event.external_id)
    .fetch_optional(&pool)
    .await?;

    match existing {
        Some(row) => process_existing(row),
        None => insert_new(&event, &pool).await?,
    }
}

It reads cleanly. It passes code review. It even passes your local tests, because your local test data is 20 rows, not 20,000.

The problem is structural, not cosmetic. For N events, you issue N queries. Each query is a full round trip: serialize the request, send it over the network (or socket), have PostgreSQL parse and plan it, execute it, serialize the response, send it back, deserialize it in your application. Do that once and it’s invisible. Do it a thousand times in a tight loop and you’re paying that overhead a thousand times over — sequentially, in most naive implementations, because each await blocks on the network before the next query can even be built.

At 1,000 events, if each round trip costs even a conservative 1–2ms, you’re looking at 1–2 seconds of pure network and parsing overhead — before you count actual query execution time. At 10,000 events, that’s 10–20 seconds. And that’s the optimistic case, assuming no connection contention, no lock waits, no queueing on the pool.

This is the classic N+1 query problem: 1 query to fetch the batch, N queries to resolve each item in it. It shows up everywhere — ORMs lazy-loading related records, GraphQL resolvers fetching child fields per parent, event processors checking existence per event. The shape is always the same: a loop, and a query inside it.

The scary part is that N+1 doesn’t show up in your development environment. It shows up in production, under load, exactly when you can least afford to debug it.

3. Think in Batches

The fix isn’t a clever trick. It’s a mental shift: stop asking the database one question per item, and start asking it one question per batch.

Instead of:

*“Does event A exist? Does event B exist? Does event C exist?” *(N queries)

Ask:

*“Which of these N external IDs already exist?” *(1 query)

The database is extremely good at set-based operations. It has indexes, statistics, and a query planner specifically built to answer “which of these rows match” efficiently — far more efficiently than your application code answering the same question one row at a time. The mistake in the N+1 pattern isn’t that PostgreSQL is slow. PostgreSQL is fast. The mistake is that you’re not using it as a set-based system — you’re using it as if it were a slow, remote hash map you have to call in a loop.

Batching means restructuring the flow:

  1. Collect the batch of identifiers you need to check.
  2. Issue one query that takes the whole batch as input.
  3. Get back a set of matches.
  4. Do the branching logic (existing vs. new) in memory, not in the database.

This is the same principle behind DataLoader in the GraphQL world, WHERE id IN (...) batching in ORMs, and bulk upserts in ETL pipelines. Different names, same idea: collapse N round trips into 1.

4. My Solution: ANY() Instead of a Loop

In PostgreSQL, the cleanest way to check membership against a batch is the ANY() operator combined with an array parameter. Unlike a hand-built IN (...) clause with dozens of positional placeholders, ANY($1) takes a single bound array parameter — which plays nicely with prepared statements and avoids query-plan cache bloat from variable-length IN lists.

Before — the N+1 version:

for event in &events {
    let exists: Option<(i64,)> = sqlx::query_as(
        "SELECT id FROM events WHERE external_id = $1"
    )
    .bind(&event.external_id)
    .fetch_optional(&pool)
    .await?;
    // ... branch on exists
}

After — the batched version:

// 1. Collect all external IDs from the current batch.
let ids: Vec<String> = events.iter()
    .map(|e| e.external_id.clone())
    .collect();

// 2. One query. One round trip. All matches returned at once.
let existing_rows: Vec<EventRow> = sqlx::query_as::<_, EventRow>(
    r#"
    SELECT id, external_id, status
    FROM events
    WHERE external_id = ANY($1)
    "#
)
.bind(&ids)
.fetch_all(&pool)
.await?;
// 3. Build a lookup set in memory.
let existing_map: HashMap<&str, &EventRow> = existing_rows
    .iter()
    .map(|r| (r.external_id.as_str(), r))
    .collect();
// 4. Branch in memory, not in the database.
for event in &events {
    match existing_map.get(event.external_id.as_str()) {
        Some(row) => process_existing(row),
        None => new_events.push(event), // collect for a bulk insert
    }
}

The corresponding SQL side is simple, but worth spelling out:

SELECT id, external_id, status
FROM events
WHERE external_id = ANY($1);
-- $1 is a text[] array, e.g. ARRAY['ext_1001', 'ext_1002', ..., 'ext_1999']

And for the insert side, the same principle applies — batch the writes too, using UNNEST to turn arrays back into rows:

INSERT INTO events (external_id, status, payload)
SELECT * FROM UNNEST(
    $1::text[],
    $2::text[],
    $3::jsonb[]
);

One query reads the batch. One query writes the batch. What used to be thousands of round trips per processing cycle became two.

5. Why It Works

The improvement isn’t magic — it comes from a few concrete mechanical wins:

Fewer network round trips. Each round trip carries fixed overhead: TCP/TLS framing, protocol serialization, connection pool checkout, and scheduling latency on both ends. Collapsing N round trips into 1 removes (N−1) times that fixed cost. This is usually the single biggest win, especially when the database isn’t on localhost.

Better index usage. A single WHERE external_id = ANY($1) lets the planner do one index scan (or bitmap index scan) across all requested values, instead of re-planning and re-executing the same query plan N times. PostgreSQL can also parallelize or optimize a bitmap heap scan across the whole set far more effectively than N sequential point lookups.

Lower latency, end to end. Latency isn’t just about the database’s execution time — it’s dominated by everything around execution: connection acquisition, network hops, context switches. Removing round trips removes that overhead multiplicatively, not just additively.

Higher throughput under concurrency. With N+1, each concurrent worker holds a connection from the pool for the entire duration of its loop — N sequential queries. That starves the pool under load. With batching, each worker holds a connection for a single query, releasing it immediately. You get dramatically better connection utilization, which directly translates to higher sustained throughput.

In the actual service, replacing the per-event loop with a batched ANY() query took the existence-check phase of processing a batch of ~2,000 events from low single-digit seconds down to tens of milliseconds — a two-orders-of-magnitude improvement, with no change to the underlying schema or hardware.

6. Trade-offs

Batching isn’t free, and pretending otherwise is how you trade one class of problem for another.

Batch size matters. Too small, and you’re back to something resembling N+1 — just with a smaller N repeated more often. Too large, and a single query can start doing more harm than good. There’s a sweet spot, usually somewhere in the hundreds to low thousands of items, that needs to be tuned against your actual workload.

Memory usage. Batching pulls more data into application memory at once — both the input array and the result set. For wide rows or large JSON payloads, a batch of 10,000 can mean real memory pressure, especially in high-concurrency services. Profile this rather than assuming it’s fine.

Query planning on large arrays. PostgreSQL’s planner estimates selectivity for = ANY($1) based on statistics that may not perfectly reflect a specific batch. With very large arrays (tens of thousands of elements), the planner can sometimes choose a suboptimal plan — for example, a sequential scan when a bitmap index scan would be faster, or vice versa. It's worth checking EXPLAIN ANALYZE on realistic batch sizes rather than assuming linear scaling.

Large arrays and parameter limits. Extremely large arrays can bump into practical limits — larger query payloads, longer serialization time on both client and server, and diminishing returns past a certain point. If your batches are unbounded (e.g., driven by unpredictable upstream traffic), you need an explicit chunking strategy: process in fixed-size sub-batches rather than passing an arbitrarily large array in one shot.

The right approach is usually: batch aggressively, but cap batch size explicitly, and measure — don’t assume “bigger batch is always better” any more than you’d assume “one query per row is fine.”

7. Lessons

A few things I’d tell any backend engineer before they write their next loop-with-a-query-inside-it:

  • Think in batches, not iterations. If you find yourself writing a for loop with a database call inside it, stop and ask: "What single query would answer this for the whole batch?"
  • Minimize round trips before you optimize query execution time. Round-trip overhead is often the dominant cost, not raw query execution — fix the shape of your access pattern first.
  • Your database is fast. Network latency isn’t. PostgreSQL can evaluate a well-indexed ANY() clause over thousands of values in milliseconds. The bottleneck was never the database engine — it was how many times you made it answer the phone.
  • Measure before and after. The two-orders-of-magnitude improvement described here wasn’t a guess — it came from profiling the batch-processing phase specifically, isolating round-trip time from actual query execution time.
  • This pattern generalizes. Existence checks, lookups, upserts, foreign key resolution — anywhere you’re resolving “one thing per item,” there’s almost always a batched equivalent waiting to replace it.

N+1 queries are rarely a sign of a bad engineer. They’re a sign of code that was written to be correct first, and never revisited under load. The fix is usually small — a reshaped query, an array parameter, a HashMap for in-memory lookups — but the performance impact is not.