Extraneous fetching

Extraneous fetching means retrieving more data than an operation needs: every column when the screen shows four, every row when it shows twenty, a whole document to read one field. The extra bytes are not free. The database reads them, the network carries them, the app builds objects from them and the client downloads and parses them, so latency, memory and bandwidth bills all grow with data nobody looks at.

Picture it first: the whole godown for one display

You run a saree shop. A customer asks for red silk sarees under ₹5,000, and your counter fits 20. You phone the godown: “send the silk”. It sends all 500 silk sarees, each boxed with an invoice and a care card. Porters carry the bundles over, the counter overflows, and your assistant spends an hour discarding everything that isn’t red or costs too much. The customer looks at 20. Next season the godown holds 1,000, and the delivery takes twice as long for the same 20.

Map it back: the godown is the database or a downstream service; “send the silk” is a query with no column list, no filter and no limit; the porters are network hops; the counter and assistant are app memory and CPU doing the database’s filtering; and the invoices and care cards are wide columns and JSON the screen never shows.

The opposite mistake, phoning 20 times for one saree each, is chatty I/O. The good request says exactly what you want: “red silk under ₹5,000, the 20 newest, sarees only.”

What the extra costs

Extraneous fetching comes in four shapes, often several in one endpoint:

Every unused byte is paid for five times

1. The database reads it. In PostgreSQL, big values in rows wider than about 2 kB live out of line in TOAST storage and are fetched only if selected. SELECT * selects them.
2. The network carries it to the app.
3. The app builds objects from it and serialises them, creating garbage.
4. The network carries it again: billed egress and the user’s mobile data.
5. The client downloads and parses it to render a fraction.

Where chatty I/O costs round trips, this costs bytes, and on slow links bytes are what you wait for; see latency vs throughput vs bandwidth.

A worked example: the order history screen

A Swiggy-style app’s “Your orders” screen shows 20 orders per page, four fields each: restaurant, date, total and status. The endpoint, written when users had a handful of orders, returns every order as the same 40-field Order object the detail screen uses, items and tracking_events JSON included.

Take a customer with 500 orders. Assume 4 KB of JSON per full order (1 KB = 1,000 bytes here), 100 bytes for the four shown fields, a 4G link delivering 8 Mbit/s with a 60 ms round trip, and 250 requests per second at dinner peak. Measure your own; the ratios matter more than the inputs.

Each full request allocates the driver’s 2 MB of rows, about 4 MB of objects (assuming entities take twice their JSON size in memory) and a 2 MB output buffer: 8 MB of garbage, against perhaps 20 KB for a paged request. At 250 ms of server time per request, Little’s law puts 250 × 0.25 ≈ 62 requests in flight, each holding about 6 MB of objects and output: ≈ 370 MB of live heap for one screen.

The user waits 2 seconds of download plus a 2 MB parse, where the paged response is bound by the 60 ms round trip. Opened five times a day, the screen burns 5 × 30 × 2 MB = 300 MB of mobile data a month.

Symptoms you’d see

It rarely breaks on day one; it gets worse every week:

• Latency tracks payload size, and grows as data grows, with no deploy to blame.
• Big users and big tenants time out first. p50 looks fine while p99 is terrible: the merchant with two million UPI transactions hits the timeout. Azure’s guidance lists 500 and 503 errors from timeouts as typical.
• Large responses: p99 in megabytes; screens crawl on 4G and feel fine on office Wi-Fi.
• Egress and bandwidth bills grow faster than traffic, as does database-to-app traffic.
• A memory-hungry app tier: high heap, long GC pauses, out-of-memory kills when a big tenant clicks “export”, and profiles full of ORM hydration and JSON serialisation.
• The database returns thousands of rows per call for screens that show 20.

The arithmetic behind the line: 60 ms + n × 4 KB at 8 Mbit/s = 60 ms + n × 4 ms. At 30 orders that is 0.18 s; at 500, 2.06 s; a 5-second timeout is crossed at (5,000 − 60) ÷ 4 ≈ 1,235 orders.

Why it happens

Nobody decides to fetch 2 MB for a 2 KB screen. It accumulates from defaults and good intentions.

ORM defaults and one fat object

findAll(), findById() and ToList() return whole entities, so code that reads one property loads all forty, and eager loading (Include, join fetch) pulls in whole object graphs. Then one OrderDto serves the list, the detail screen, the support console and the export; each consumer adds fields and none removes any.

Overcorrecting chatty I/O

After a painful round of N+1 queries, “fetch everything in one go” sounds like the fix. Azure’s guidance names this directly: extraneous fetching often comes from overcompensating for chatty I/O.

Filtering in the app because the query layer is awkward

In LINQ, a condition the provider can’t translate to SQL (a custom C# method, say) pushes developers to add AsEnumerable() or ToList() before the Where, and every row then comes back to be filtered in memory. Java code streams over findAll(). Someone sorts in app code because ORDER BY was slow, when an index was missing. Each is a filter, sort or limit that crossed the network before it ran.

Missing pagination and analytics on the request path

The endpoint was born when users had five orders, so it returns “all of them”; two years later, “all” is 500, and for some accounts 50,000. Any list that can grow needs a page size and a cursor from day one; see offset vs keyset pagination and pagination: offset vs cursor. Totals computed per request by loading rows are analytical work on an OLTP path; see OLTP vs OLAP.

“Just in case” and one-size APIs

A screen prefetches details for every row in case the user taps one. A REST resource built for the web admin is reused by the mobile app, which renders a quarter of it. A GraphQL server lets clients select fields, yet each resolver still loads the whole row.

It survives review because the development database has five orders and sits on your laptop.

How to detect it

To prove extraneous fetching, weigh what moves and compare it with what is used.

Measure bytes and rows, not just milliseconds

• Response size per route. Keep a histogram of response bytes. A list endpoint with a 40 KB p50 and a 2 MB p99 has a data-skew problem waiting to become a timeout.
• Rows fetched ÷ rows rendered, per route. On a 20-row page, near 1 is healthy; 25 means the app is filtering what the database should have.
• Bytes from the store ÷ bytes to the client, a ratio Azure’s guidance suggests tracking. In its load test of a method that loaded whole products to return two fields, the database sent 80,503 bytes per request and the client got 19,855, about a quarter.
• Heap and GC by route or tenant, and egress per request. If bytes grow faster than requests, responses are getting fatter.

Ask the database

pg_stat_statements records, per normalised statement, calls, total rows retrieved, and shared blocks found in cache (shared_blks_hit) or read (shared_blks_read). Divide by calls:

-- Statements that return the most rows (and touch the most blocks) per call
SELECT left(query, 48)                                         AS statement,
       calls,
       rows / NULLIF(calls, 0)                                 AS rows_per_call,
       (shared_blks_hit + shared_blks_read) / NULLIF(calls, 0) AS blocks_per_call,
       round(mean_exec_time::numeric, 1)                       AS mean_ms
FROM pg_stat_statements
WHERE calls > 1000
ORDER BY rows_per_call DESC
LIMIT 10;

statement                                        | calls   | rows_per_call | blocks_per_call | mean_ms
SELECT * FROM orders WHERE seller_id = $1        |  41,220 |        23,870 |          26,410 |   612.4
SELECT * FROM orders WHERE user_id = $1 ORDER BY | 902,115 |           318 |           1,240 |    38.9
SELECT * FROM cities                             |  12,004 |            31 |               1 |     0.1
(illustrative output)

The seller dashboard returns about 24,000 rows to show 20; the order history averages 318 for a 20-row screen, hiding users with thousands. The 31-row reference table is fine.

Then examine a suspect with EXPLAIN, adding BUFFERS and, on recent PostgreSQL versions, SERIALIZE:

EXPLAIN (ANALYZE, BUFFERS, SERIALIZE, COSTS OFF)
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC;

Sort (actual time=3.912..4.106 rows=500 loops=1)
  Sort Key: created_at DESC
  Sort Method: quicksort  Memory: 905kB
  Buffers: shared hit=506
  ->  Index Scan using orders_user_id_idx on orders (actual time=0.028..3.377 rows=500 loops=1)
        Index Cond: (user_id = 42)
        Buffers: shared hit=506
Planning Time: 0.142 ms
Serialization: time=41.870 ms  output=2004kB  format=text
  Buffers: shared hit=1530
Execution Time: 46.211 ms
(illustrative output)

Read it in three steps:

• The plan is fine: an index scan returns 500 rows in about 4 ms, which is why this pattern hides from people who only read plans.
• Serialisation is ten times the scan: 42 ms and 1,530 more buffer reads, because the JSON lives in TOAST storage and is fetched only when rows are output. The docs warn this can be significant when TOASTed values must be fetched, and EXPLAIN skips the step unless you add SERIALIZE, so the same query would look like a healthy 4 ms.
• 2 MB leaves the database, and since EXPLAIN ANALYZE never sends rows to the client, the network transfer is invisible here.

A neighbouring signal means something else. rows in EXPLAIN counts rows a node emits; a large Rows Removed by Filter means the database scanned rows and discarded them itself. That points at a missing or wrong index, though the two often travel together; see query execution plans.

What a trace looks like

In a trace, extraneous fetching is a short database span followed by a long stretch with no child spans while the app maps and serialises rows. Server-side tracing reports a respectable latency; real-user monitoring on phones reports far worse, because the download happens after the server span ends. A server-to-client latency gap that grows with response size is the fingerprint.

Telling it apart from look-alikes

They overlap: Azure notes that extraneous fetching looks much like monolithic persistence in telemetry, and pushing every computation into the database can tip you into a busy database.

How to fix it

Every fix moves the decision about what’s needed closer to the data.

Return one page with the columns the screen uses

For the order history, name the columns and replace “all orders” with a keyset page:

-- BEFORE: every order the user ever placed, every column, JSON included
SELECT * FROM orders
WHERE user_id = $1
ORDER BY created_at DESC;

-- AFTER: one page, only what the list shows
SELECT id, restaurant_name, total_paise, status, created_at
FROM orders
WHERE user_id = $1
  AND (created_at, id) < ($2, $3)   -- cursor: the last row of the previous page; omit on page 1
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- An index that answers it, ideally without visiting the table at all
CREATE INDEX orders_user_recent
    ON orders (user_id, created_at, id)
    INCLUDE (restaurant_name, total_paise, status);

The keyset condition jumps straight to the next 20 rows in index order, so page 50 costs the same as page 1 (offset vs keyset pagination). The INCLUDE columns make it a covering index, so PostgreSQL can answer from the index alone in an index-only scan: the earlier EXPLAIN now shows 20 rows, a handful of buffers and about 2 kB of output. Order the key with composite index design.

You pay with a query and DTO per screen, and ORM projections you can’t save back. Keyset pages can’t jump to “page 37” and need a cursor in the API. The covering index slows writes, and the PostgreSQL docs add two cautions: be conservative with wide payload columns, and expect heap visits anyway on recently changed pages, so orders whose status keeps updating benefit less.

Push filters, sorts, limits and sums into the query

A Flipkart-style seller dashboard shows a seller’s 20 newest pending orders from the past week. The first version couldn’t translate a helper method to SQL:

// BEFORE: IsRecent() can't become SQL, so AsEnumerable() pulls every order this
// seller has ever had (all columns), then filters, sorts and pages in memory
var page = db.Orders
    .Where(o => o.SellerId == sellerId)
    .AsEnumerable()
    .Where(o => o.Status == OrderStatus.Pending && IsRecent(o.CreatedAt))
    .OrderByDescending(o => o.CreatedAt)
    .Take(20)
    .ToList();

// AFTER: the query stays IQueryable, so it becomes one SQL statement
// with WHERE, ORDER BY, LIMIT and a column list
var since = DateTime.UtcNow.AddDays(-7);
var page = await db.Orders
    .Where(o => o.SellerId == sellerId
             && o.Status == OrderStatus.Pending
             && o.CreatedAt >= since)
    .OrderByDescending(o => o.CreatedAt)
    .Take(20)
    .Select(o => new PendingOrderRow(o.Id, o.BuyerName, o.TotalPaise, o.CreatedAt))
    .ToListAsync();

With an index on (seller_id, status, created_at), the database walks straight to the 20 rows. For a seller with 24,000 orders of about 2 KB each, that is 48 MB shipped to the app versus a few kilobytes.

Aggregates follow the same rule:

// BEFORE: every order row in the table comes into the JVM to add up one column
BigDecimal revenue = orderRepository.findAll().stream()
    .filter(o -> o.getSellerId() == sellerId)
    .filter(o -> o.getStatus() == Status.DELIVERED)
    .map(Order::getTotal)
    .reduce(BigDecimal.ZERO, BigDecimal::add);

// AFTER: the database adds them up and returns one number (null if no rows)
@Query("select sum(o.total) from Order o where o.sellerId = :sellerId and o.status = :status")
BigDecimal revenueFor(@Param("sellerId") long sellerId, @Param("status") Status status);

In Azure’s load test of this mistake, a request pulled over 280 KB from the database to return a 14-byte total. Summing in the query cut the read to 53 bytes and raised sustained throughput from about 2,000 to over 25,000 requests per minute.

You pay with database CPU. Indexed filtering is cheap, but heavy aggregations on every request can create a busy database; for dashboards, keep a running total updated on write or use an analytical store. Logic SQL can’t express must be rewritten (compute since outside the query) or precomputed.

Let clients ask for the fields they show

APIs over-fetch too. In GraphQL the client writes a selection set, and the response has exactly the shape of the query:

query OrdersList($after: String) {
  me {
    orders(first: 20, after: $after) {
      nodes { id restaurantName total status placedAt }
      pageInfo { endCursor hasNextPage }
    }
  }
}

In REST, Google’s AIP-157 makes views the primary pattern: a small documented set such as BASIC and FULL, with BASIC the default for lists. For finer control, Google’s APIs take a field mask as a system parameter, a fields query parameter or X-Goog-FieldMask header; with no mask, you get every field.

GET /v1/users/me/orders?page_size=20&view=BASIC HTTP/1.1
Accept-Encoding: gzip

GET /v1/users/me/orders/9182?fields=id,items,tracking_events HTTP/1.1
Accept-Encoding: gzip

Make the selection reach the query. A mask applied only while serialising trims the last hop, but the database still read every column and the app still built every object. Map views and selection sets to column lists, as a backend-for-frontend does per screen (API composition, BFF and GraphQL federation).

You pay with cache hit rate and complexity. Each field set is a different response, so a cache holding one entry per order now holds one per shape. Arbitrary masks make cost harder to predict; a few named views keep most of the benefit, and GraphQL needs depth and page-size limits.

DynamoDB: projections cut bytes, not capacity

DynamoDB returns every attribute unless you pass a ProjectionExpression:

resp = orders.query(
    KeyConditionExpression=Key("user_id").eq(user_id),
    ProjectionExpression="order_id, restaurant, #t, #s, placed_at",
    ExpressionAttributeNames={"#t": "total", "#s": "status"},  # both are reserved words
    ScanIndexForward=False,   # newest first
    Limit=20,
)

That shrinks the response, but not the bill. A strongly consistent read costs one read unit per 4 KB of item size, an eventually consistent one half that, and a Query sums the sizes of the items it reads before rounding up to 4 KB. The docs state that requesting fewer attributes doesn’t change that calculation, and that a Scan is charged for every item it evaluates.

So 20 full orders of 4 KB are 80 KB: 20 read units strongly consistent, 10 eventually consistent, projection or not. To cut capacity, make the item smaller: a 100-byte summary item per order, or a secondary index that projects only the list attributes. Then 20 × 100 B = 2 KB, rounded up to 4 KB: 1 read unit, or half of one. A projection controls what you receive; what the store reads depends on layout.

Split hot fields from cold ones

When a few small fields are read constantly and a few big ones rarely, store them apart. This vertical partitioning gives lists a narrow orders table and puts the JSON in order_details, read by primary key on tap. Invoice PDFs and photos go to object storage, and the row keeps only a key.

CREATE TABLE orders (            -- hot: every list, every page view
  id bigint PRIMARY KEY, user_id bigint NOT NULL, restaurant_name text,
  total_paise bigint, status text NOT NULL, created_at timestamptz NOT NULL
);
CREATE TABLE order_details (     -- cold: read by id when the user taps an order
  order_id bigint PRIMARY KEY REFERENCES orders (id),
  items jsonb, delivery_address jsonb, tracking_events jsonb,
  invoice_key text               -- object-storage key, not the PDF bytes
);

PostgreSQL’s TOAST already stores big values out of line and fetches them only when selected, and its docs note that a smaller main table fits more rows in cache. An explicit split still helps when your ORM loads whole entities, when the store treats the item as the unit (DynamoDB, document stores, caches), and to keep the hot table dense. It is also the cure for monolithic persistence.

You pay with an extra read on the detail screen, two rows to write per order (in one transaction), joins for reports that need both halves, and a migration. Object storage is a second system to keep in step: an orphaned file if a transaction rolls back, a dangling key if a file is deleted.

Compress and bound what’s left

Once responses carry only what’s needed, compress them (gzip or br) and cap them: every list endpoint gets a server-enforced maximum page size. Exports stream rows through a server-side cursor, or run as a background job writing to object storage, so a merchant’s statement of two million UPI transactions never sits in one process’s memory.

You pay with CPU at both ends, and compression shrinks only the wire: the database, the app and the phone still handle every byte. It is the last fix, not the first.

Compare the fixes

The other ditch: chatty I/O

Fixing extraneous fetching usually means splitting one fat endpoint into a lean list and a detail call. Overdo it and the client calls the detail endpoint once per row to fill in a field the list left out: 20 tiles, 20 round trips, 20 × 60 ms = 1.2 s on 4G if sequential. That is chatty I/O, and it gives back about half of the 2.2 seconds you saved.

Decide with the bandwidth-delay product: bandwidth × round-trip time is how many bytes arrive in the time one extra round trip takes. On the assumed link, 8 Mbit/s × 60 ms = 480 kbit ≈ 60 KB. Bundling extra data pays when its transfer time is less than the waiting it saves:

bundle if   extra bytes ÷ bandwidth  <  P(needed) × round-trip time
i.e.        extra bytes  <  P(needed) × 60 KB   on this link

• If half of users tap the first order, bundling its 3.9 KB of details costs about 4 ms and saves 0.5 × 60 = 30 ms on average. Bundle it.
• Details for all 20 orders are 20 × 3.9 KB = 78 KB, about 78 ms, to save at most one 60 ms round trip. Don’t.
• All 500 orders, to avoid a “next page” request, cost 2 seconds to save 60 ms. Never.

So the target is one round trip per screen, shaped to that screen, plus small extras that are likely to be needed.

When it’s fine

Fetching more than you show is sometimes right:

• Small, bounded, slow-changing data, such as a 31-row cities table loaded at startup.
• Prefetching one page ahead while the user scrolls, or the detail they will very likely open next. Both pass the bandwidth-delay test.
• Caching a whole object many screens share. Cache the full product once and let each screen pick fields from memory; one key per object beats one per projection. See no caching.
• High-latency links, where a second round trip costs more than the extra bytes.
• Deliberate bulk work: an offline-first app syncing its catalogue on Wi-Fi, or a nightly export that streams rows.

Cheat sheet

How it shows up in interviews

Extraneous fetching hides inside list-heavy designs: a news feed or Instagram-style grid shipping full-size images, a food-delivery order history, a product grid, a merchant revenue dashboard, or “download all my UPI transactions”.

The probes: “What exactly does your list endpoint return?”, “How many bytes is one page?”, “What happens for a merchant with two million transactions?” Answer with arithmetic (rows × bytes per row ÷ link speed), then the fix, then its cost.

How to say it in the interview

“Extraneous fetching is retrieving more than the operation uses: extra columns, extra rows, or a response wider than the screen. Every unused byte is read, shipped, built into objects and shipped again, so latency tracks payload size and memory and egress track traffic. For an order history showing 20 orders, I’d return one keyset page with the five fields the tile needs, about 2 KB, not every order as a 4 KB object: that’s 2 MB and two seconds on 4G, and a timeout for big accounts. The query names its columns and puts filter, sort and limit into an index, and details load on tap. I’d watch response-size p99 and rows fetched per row rendered.”

Isn’t one big response better than many small ones? Only while the extra bytes cost less than the round trip they save. Bandwidth × RTT is the break-even, about 60 KB on 4G at 8 Mbit/s and 60 ms. Bundle a small detail the user will probably open; don’t ship 500 orders to avoid a “next page” call.

Won’t gzip fix it? It shrinks the wire, but the database still reads the bytes, the app still builds them, and the phone still inflates and parses them. Compress what you need; don’t compress your way out of fetching what you don’t.

Does GraphQL solve over-fetching? For the client, yes. But resolvers can still load whole rows, and each distinct query is another shape to cache and cost-limit. Push the selection into the query; cap depth and page size.

Does a DynamoDB ProjectionExpression lower the bill? No. Read units are charged on item size in 4 KB steps; a projection only shrinks the response. Make the item smaller: a summary item, or an index projecting only list attributes.

How do you stop it coming back? Make size a tested contract: a byte budget per list endpoint in CI, a server-enforced maximum page size, no SELECT * on hot paths, and dashboards for response-size p99 and rows fetched per row rendered.

Where to go next

• Chatty I/O: the ditch on the other side.
• Busy database: when pushing work into the database goes too far.
• Monolithic persistence and no caching: neighbouring anti-patterns.
• Offset vs keyset pagination and pagination: offset vs cursor: bounding lists.
• Database indexes, composite index design and index scan vs full table scan.
• Query execution plans: reading EXPLAIN output.
• REST vs GraphQL vs gRPC and API composition, BFF and GraphQL federation: shaping responses per client.
• Latency vs throughput vs bandwidth: when bytes matter and when round trips do.

References and further reading

1. Extraneous Fetching antipattern (Azure Architecture Center)learn.microsoft.comOvercompensating for chatty I/O, filtering and summing in application memory, the database-to-client byte ratio, and load tests where a 280 KB read became a 14-byte answer.
2. PostgreSQL documentation: Using EXPLAINpostgresql.orgRows emitted versus rows scanned, Rows Removed by Filter, buffer counts, and why EXPLAIN ANALYZE leaves out network transmission.
3. PostgreSQL documentation: EXPLAINpostgresql.orgThe BUFFERS and SERIALIZE options, and why output conversion can dominate when TOASTed values must be fetched.
4. PostgreSQL documentation: Index-Only Scans and Covering Indexespostgresql.orgINCLUDE columns, the visibility-map condition for skipping the heap, and the warning against wide payload columns.
5. PostgreSQL documentation: TOASTpostgresql.orgRows wider than about 2 kB have big values moved out of line, and those values are fetched only if selected, when results are sent.
6. PostgreSQL documentation: pg_stat_statementspostgresql.orgPer-statement calls, rows, and shared block hit and read counters.
7. GraphQL: Queriesgraphql.orgSelection sets: the client names every field it wants and the result has the same shape as the query.
8. AIP-157: Partial responses (Google API Improvement Proposals)google.aip.devView enums (BASIC and FULL, BASIC by default for List) as the primary pattern, and field masks as the fine-grained alternative.
9. Google Cloud APIs: System parameterscloud.google.comThe fields query parameter and X-Goog-FieldMask header for response filtering.
10. Amazon DynamoDB Developer Guide: Read and write operationsdocs.aws.amazon.comRead units per 4 KB of item size, Query and Scan sizing, and why requesting fewer attributes does not change consumed capacity.