---
title: Over-Fetching
source: 'https://academia.sh/en/courses/data-access-layer/over-fetching'
course: 'The Data Access Layer and Business Logic'
language: en
updated: '2026-08-19T05:19:35+00:00'
license: 'CC BY-SA 4.0'
---

# Over-Fetching

Measuring transferred data in bytes: the difference between fetching every column and fetching only the needed ones, the contribution of row limiting, whether aggregation belongs in the application or the database, and star selection silently dropping data on column collision.

The previous lesson brought the query count down. Batch fetch returned 565 rows for five
hundred records, join 500, and row count looked like the right metric.

Row count is not the measure of transferred data. A row can carry five columns or twenty;
one of those columns can be a short date, or a thousand-character summary. This lesson
converts the metric to bytes and measures the two levels of limiting — which columns and
how many rows — separately.

## Measurement Data

For the measurement to be meaningful, the relations need realistic width: a summary and
cover text on the book, an address and notes on the member.

```js
// generate.mjs — measurement data with a book relation carrying a summary text
import { DatabaseSync } from "node:sqlite";
import { rmSync } from "node:fs";
rmSync("library.db", { force: true });

const db = new DatabaseSync("library.db");
db.exec(`
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                     email TEXT, address TEXT, notes TEXT);
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL,
                   isbn TEXT NOT NULL, summary TEXT NOT NULL, cover_text TEXT NOT NULL);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
                   member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);`);

const text = (n, seed) => `${seed} `.repeat(n).slice(0, n * 6);
db.exec("BEGIN");
const insertMember = db.prepare("INSERT INTO member VALUES (?,?,?,?,?,?)");
for (let i = 1; i <= 60; i++) {
  insertMember.run(i, `Member${i}`, `Last${i}`, `member${i}@example.test`,
                    text(20, `Street${i}`), text(30, "note"));
}
const insertBook = db.prepare("INSERT INTO book VALUES (?,?,?,?,?,?)");
for (let i = 1; i <= 200; i++) {
  insertBook.run(i, `Book ${i}`, `Author ${(i % 40) + 1}`,
                 `978-0-00-${String(100000 + i).slice(0, 6)}`, text(120, "summary"), text(60, "cover"));
}
const insertLoan = db.prepare("INSERT INTO loan VALUES (?,?,?,?,?)");
for (let i = 1; i <= 2000; i++) {
  const day = String((i % 28) + 1).padStart(2, "0");
  const month = String((i % 12) + 1).padStart(2, "0");
  insertLoan.run(i, (i % 200) + 1, (i % 60) + 1, `2025-${month}-${day}`, i % 4 === 0 ? null : "2025-12-31");
}
db.exec("COMMIT");
console.log("book:", db.prepare("SELECT count(*) AS n FROM book").get().n,
            " average summary length:", db.prepare("SELECT avg(length(summary)) AS n FROM book").get().n,
            " open loans:", db.prepare("SELECT count(*) AS n FROM loan WHERE return_date IS NULL").get().n);
```

```sh
node generate.mjs
```

```
book: 200  average summary length: 720  open loans: 500
```

## Limiting at Two Levels

The task is the same again: the open loan list. The fields shown on screen are the loan
ID, pickup date, book title, and the member's name. Three queries pull this list at three
different widths.

```js
// field-measurement.mjs — pulls the same list with different column sets, measures transferred bytes
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");

const measure = (label, sql) => {
  const t = performance.now();
  const rows = db.prepare(sql).all();
  const duration = performance.now() - t;
  const bytes = Buffer.byteLength(JSON.stringify(rows));
  console.log(`${label.padEnd(26)} rows=${String(rows.length).padStart(4)}  ` +
    `bytes=${String(bytes).padStart(7)}  per_row=${String(Math.round(bytes / rows.length)).padStart(5)}  ` +
    `duration=${duration.toFixed(1)} ms`);
  return bytes;
};

const allColumns = measure("all columns", `
  SELECT * FROM loan o JOIN book k ON k.book_id = o.book_id
                       JOIN member u ON u.member_id = o.member_id
  WHERE o.return_date IS NULL ORDER BY o.loan_id`);

const neededColumns = measure("only needed columns", `
  SELECT o.loan_id, o.pickup_date, k.title, u.first_name, u.last_name
  FROM loan o JOIN book k ON k.book_id = o.book_id JOIN member u ON u.member_id = o.member_id
  WHERE o.return_date IS NULL ORDER BY o.loan_id`);

const neededColumnsPage = measure("needed columns + 20 rows", `
  SELECT o.loan_id, o.pickup_date, k.title, u.first_name, u.last_name
  FROM loan o JOIN book k ON k.book_id = o.book_id JOIN member u ON u.member_id = o.member_id
  WHERE o.return_date IS NULL ORDER BY o.loan_id LIMIT 20`);

console.log(`\ncolumn selection ratio: ${(allColumns / neededColumns).toFixed(1)}x`);
console.log(`column + row ratio: ${(allColumns / neededColumnsPage).toFixed(1)}x`);
```

```sh
node field-measurement.mjs
```

```
all columns                rows= 500  bytes= 817162  per_row= 1634  duration=2.3 ms
only needed columns        rows= 500  bytes=  53244  per_row=  106  duration=0.3 ms
needed columns + 20 rows   rows=  20  bytes=   2087  per_row=  104  duration=0.0 ms

column selection ratio: 15.3x
column + row ratio: 391.5x
```

Row count is the same in the first two measurements: five hundred. The transferred data
differs by a factor of fifteen. The difference comes entirely from column selection; 106
bytes are transferred per row instead of 1634. The summary, cover text, address, and notes
columns that never appear on screen make up thirteen fourteenths of the transferred data.

Row limiting adds a second multiplier. For an interface that shows twenty rows per page,
pulling all five hundred rows is unnecessary; the two limits together push the ratio to
391-fold.

Durations are misleading here, and they are kept in on purpose because of that: since the
database runs in-process, 798 kilobytes get "transferred" in 2.3 milliseconds. Over a
network, the same data is carried multiplied by bandwidth and latency. The metric is set
again as bytes, not duration.

## Where to Put Aggregation

The sharpest form of over-fetching is the application taking over the database's job. The
question "how many loans are open" can be answered two ways.

```js
// where-to-aggregate.mjs — should counting happen in the application or the database
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");

let t = performance.now();
const rows = db.prepare("SELECT * FROM loan WHERE return_date IS NULL").all();
const inApplication = rows.length;
const appDuration = performance.now() - t;
const appBytes = Buffer.byteLength(JSON.stringify(rows));

t = performance.now();
const inDatabase = db.prepare(
  "SELECT count(*) AS n FROM loan WHERE return_date IS NULL").get().n;
const dbDuration = performance.now() - t;
const dbBytes = Buffer.byteLength(JSON.stringify({ n: inDatabase }));

console.log(`counting in the application: result=${inApplication}  bytes=${appBytes}  duration=${appDuration.toFixed(2)} ms`);
console.log(`counting in the database   : result=${inDatabase}  bytes=${dbBytes}  duration=${dbDuration.toFixed(2)} ms`);
console.log(`byte ratio: ${Math.round(appBytes / dbBytes)}x`);
```

```sh
node where-to-aggregate.mjs
```

```
counting in the application: result=500  bytes=45345  duration=0.30 ms
counting in the database   : result=500  bytes=9  duration=0.06 ms
byte ratio: 5038x
```

The same number, with five-thousand-fold different data. Counting in the application
grows with the record count; counting in the database returns a constant nine bytes.
Work like aggregation, filtering, and sorting is done where the data lives; only the
result travels to the application. This principle holds exactly the same way for
grouping and extreme-value queries.

## The Second Cost of Star Selection

Requesting every column does not cost only bytes. In a join, columns with the same name
collide, and in the result object one overwrites the other.

```js
// star-trap.mjs — star selection in a join silently merges columns with the same name
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");

const JOINED = `FROM loan o JOIN book k ON k.book_id = o.book_id
                            JOIN member u ON u.member_id = o.member_id
               WHERE o.loan_id = 4`;

console.log("column total of the three relations: 5 + 6 + 6 = 17");
console.log("fields returned by star select:", Object.keys(db.prepare(`SELECT * ${JOINED}`).get()).length);

// Schema changes: a 'notes' column is added to the book relation too.
db.exec("ALTER TABLE book ADD COLUMN notes TEXT");
db.exec("UPDATE book SET notes = 'binding is worn'");

const star = db.prepare(`SELECT * ${JOINED}`).get();
const explicit = db.prepare(
  `SELECT k.notes AS book_note, u.notes AS member_note ${JOINED}`).get();

console.log("fields returned by star select after the schema change:", Object.keys(star).length);
console.log("  star.notes           :", JSON.stringify(star.notes.slice(0, 20)));
console.log("  explicit.book_note   :", JSON.stringify(explicit.book_note));
console.log("  explicit.member_note :", JSON.stringify(explicit.member_note.slice(0, 20)));
```

```sh
node generate.mjs
node star-trap.mjs
```

```
book: 200  average summary length: 720  open loans: 500
column total of the three relations: 5 + 6 + 6 = 17
fields returned by star select: 15
fields returned by star select after the schema change: 15
  star.notes           : "note note note note "
  explicit.book_note   : "binding is worn"
  explicit.member_note : "note note note note "
```

In the first line, seventeen columns were requested and fifteen fields came back: the
names `book_id` and `member_id` merged because they exist in two relations at once. After
a new column was added to the schema, the requested column count rose to eighteen, and
the returned field count still stayed at fifteen. The `notes` field carries the member's
note; the book's note silently dropped. Asked with explicit selection, both values are in
place.

The danger here is that it produces no error. The query succeeds, the field is filled, the
value is valid — but it comes from the wrong relation. A column added in the Schema
Migrations lesson can break a code path that uses star selection this way, and the
migration itself produces no warning.

## How Much to Limit

Limiting has an opposite direction too. A query that pulls fewer columns than needed
requires a second round trip for the missing field; the previous lesson's N+1 problem can
come back this way. For that reason, the metric is not "fewest columns" but the exact set
of shown fields.

Three rules work in practice. List views pull only the fields shown in the list; the
detail view is called with a separate query. Columns carrying long text and binary content
never enter list queries. Pagination writes the limit of the result set into the query
itself; slicing it in the application does not reduce the transfer, because the data has
already been carried over.

Applying these rules depends on being able to see which fields a query returns just by
reading the code. Star selection removes that visibility: someone looking at the query
cannot see which fields are carried, and the transferred data grows silently when the
schema changes.

## Summary

- The same five-hundred-row list transferred 817162 bytes with every column, 53244 bytes
  with only the needed ones; with the same row count, the difference is a factor of 15.3.
- Row limiting added a second multiplier; the two limits together pushed the ratio to
  391-fold.
- Counting in the application carried 45345 bytes, counting in the database 9 bytes;
  aggregation, filtering, and sorting are done where the data lives.
- Star selection merged columns with the same name in the join; the value of a column
  added to the schema silently dropped, and no error was produced.
- The metric is not "fewest columns" but the exact set of shown fields; an incomplete
  selection produces a second round trip.

## Next Step

These two lessons covered the read path: how many queries run and how much data each
query carries. The write path has its own scale. Suppose loan data is being imported from
another system: should two thousand records be written one by one, inside a single
transaction, or with a single statement? All three approaches produce the same rows; their
durations differ by an order of magnitude. The next lesson measures that difference, shows
where it comes from, and covers how batch size is chosen.