DEV Community

Cover image for The Column I Appended Months Ago Costs Me the Whole File
Mustafa ERBAY
Mustafa ERBAY

Posted on Originally published at mustafaerbay.com.tr

The Column I Appended Months Ago Costs Me the Whole File

On 29 June 2026 I added incremental builds to this blog's publishing pipeline. The idea was plain: keep a digest of each article's source file in the database, compare on publish, skip re-rendering whatever has not changed. It took one line of schema change, and it still sits at line 44 of migrate-content.mjs:

try { db.exec('ALTER TABLE posts ADD COLUMN source_hash TEXT'); } catch { /* kolon var */ }
Enter fullscreen mode Exit fullscreen mode

ALTER TABLE ... ADD COLUMN puts the new column at the end of the table. I did not treat that as a decision at the time. Where SQLite put the column was not something I cared about; having the value there was enough.

Five months later, against last night's build, I ran the measurement on the query that reads it. SELECT id, source_hash FROM posts: 2,866 rows, a 287 KiB result set. The kernel's counter came back with 64.69 MiB. The entire database.

My first suspect was the query, so I looked at EXPLAIN QUERY PLAN: SCAN posts. A full scan, yes — but a full scan means "visit every row", not "read every row in full". What was missing was not an index. What was missing was the decision I had made in June without noticing: where the column sits inside the record.

In this same database the query plans did not change after ANALYZE, and the question there was what the planner picks. Here the plan never changes. What changes is how many bytes the same plan costs.

The setup

The file under test is the blog's live content database: the 19 November 17:15 build, /opt/mustafaerbay/dist/content.db. 67,829,760 bytes, 16,560 pages at a 4 KiB page size, one table (posts), 1,433 Turkish and 1,433 English rows. PRAGMA auto_vacuum is zero and PRAGMA journal_mode is delete, so there is no WAL file beside it. Both of those matter later.

I did not write to the live file. Everything ran against a /tmp copy on the same server over a read-only connection. Warm timings came from Python's sqlite3 module on my Mac (SQLite 3.49.1) and from the pipeline's own driver on the server (better-sqlite3, SQLite 3.53.0).

For the cold runs I dropped the file's page cache with posix_fadvise(POSIX_FADV_DONTNEED) and took the read_bytes delta from /proc/self/io. The kernel documentation defines that counter as an "attempt to count the number of bytes which this process really did cause to be fetched from the storage layer", counted "at the submit_bio() level"; its sibling rchar also counts reads served out of the page cache. The first was the one I wanted.

I owe you a warning here. POSIX_FADV_DONTNEED is a hint, not an order. The code in mm/fadvise.c returns zero to the caller even when pages are not dropped; a failure count is kept internally and never reported. So "I dropped the page cache" is, on its own, an assumption. My evidence that it actually happened is this: the same run repeated three times and returned a byte-identical number each time — the whole file. Had the cache survived, that number would have shrunk.

The table's column order, straight from pragma_table_info:

0  id              9  author         18 translated_from
1  url_slug       10  human          19 translated_to
2  lang           11  series         20 draft
3  title          12  cover_image    21 headings
4  description    13  cover_image_alt 22 body_html
5  category       14  reading_time   23 source_path
6  tags           15  word_count     24 rendered_at
7  publish_date   16  faq            25 source_hash
8  updated_date   17  social_media
Enter fullscreen mode Exit fullscreen mode

body_html sits at number 22 and averages 19,034 bytes. The source_hash I appended in June is dead last, at 25.

Where the record ends

SQLite writes a row as a single record: a header carrying one varint per column, then the values back to back in column order. The file format document says "the values for each column in the record immediately follow the header", and adds that the order is the order of the columns in the table definition.

The interesting part starts the moment a record does not fit on its page. The bytes kept on the leaf page come out of four quantities; in the document's own letters: U is the usable page size, X = U-35, M = ((U-12)*32/255)-23 and K = M+((P-M)%(U-4)), with P the total record size. The rule is in the same place: "If P is greater than X then the number of bytes stored on the table b-tree leaf page is K if K is less or equal to X or M otherwise."

In this file U = 4,096 (no reserved space), so X = 4,061 and M = 489. The average record is 22,581 bytes, and 2,844 of the 2,866 rows overflow. Everything past the local part goes to overflow pages, which form a linked list: "the first four bytes of each overflow page are a big-endian integer which is the page number of the next page in the chain."

Diagram

Now I know what sits at the end of that chain: the column ALTER TABLE put there. Reading source_hash means walking that row's entire chain.

One sentence in the document taught me something here. Explaining why the thresholds were picked as they were, it says the point is to keep enough of the payload on the b-tree page that "the record header can usually be accessed without consulting an overflow page." What is guaranteed is the header, not the values. I have worked with this format for years and had never read that sentence this way.

The local part is not 489 bytes

The K formula deserves attention, because I skipped over it on first reading. The local part is not fixed at M, at 489 bytes; K is computed per row and wanders between 489 and 4,061. I counted it across my own table: the local part runs from 489 bytes at the low end to 4,057 at the high end, with a median of 2,069. In 13.3% of rows (380 of them) K exceeds X, the rule falls back to M, and the local part really is 489 bytes. In the rest, far more than that stays on the page.

The practical consequence: there is no "past byte 489 you are in the chain" shortcut. A column ending at byte 489 is local in every row. A column at byte 2,000 is still local in half of them (51.7%). Past 4,061, in none.

How many pages a column position costs

I counted this one too. A script computes, per row, the header size, the ending byte offset of every column, and that row's K by the rules above, then totals the question "how many overflow pages must be walked to reach this column" across the table.

column position avg. end offset rows kept local overflow pages walked
title 3 221 B 2,866 0
tags 6 432 B 2,824 42
cover_image_alt 13 650 B 2,379 487
faq 16 1,628 B 1,671 1,195
headings 21 2,813 B 960 1,956
source_hash 25 22,581 B 22 14,384

That 14,384 stopped me, because it had turned up elsewhere: the dbstat virtual table counts exactly 14,384 overflow pages for posts. Record arithmetic on one side, SQLite's own page accounting on the other — two independent methods, one number. Reaching the last column means walking every overflow page the table has.

Of the file's 16,560 pages, 14,384 are overflow pages: 86.86%. I had been thinking of this file as "metadata plus bodies for 1,433 articles". It is almost entirely body chain, with metadata as a thin strip along the edge.

The table holds one more detail: source_hash stays local in 22 rows. I looked at which ones — eleven articles and their Turkish/English twins, all published between 25 March and 19 May 2026, with records from 2,148 to 4,027 bytes. The oldest and shortest posts on the blog. Their records stay under X, so they never overflow.

There could have been one more exemption, and there is not. The file format document notes that "a record might have fewer values than the number of columns": a column added by ALTER TABLE ... ADD COLUMN is simply absent from older rows and comes back from the default when read, with the record body never touched. source_hash is exactly such a column. Hopeful, I checked: zero rows have a null value and every length is 40 characters. The pipeline rewrites every row at least once, so that exemption closed long ago.

The bill on a cold cache

The derived page counts come from a model. What does the kernel say?

Before each run I dropped the page cache, ran the query, and took the read_bytes delta. Beside it, the bytes the model predicts — leaf, interior and walked overflow pages:

column queried model (MiB) measured read_bytes (MiB)
title 7.79 10.96
tags 7.95 11.07
faq 12.46 14.75
headings 15.43 19.52
source_hash 63.98 64.69

Every measured row exceeds the model, as expected: read_bytes also counts the kernel's readahead, and a full scan is readahead's favourite pattern. What read_ahead_kb can get up to on this same machine has its own measurement. On the last row the gap narrows to 0.71 MiB, and that gap is exactly 182 pages: the index, schema and freelist pages the model leaves out. Nothing in the file goes unread.

The source_hash run repeated three times and came back byte-identical each time: 64.69 MiB. Wall clock wandered between 1.89 and 3.82 seconds; the server is not idle, so I draw no conclusion from the time. The bytes are the solid part.

The query delivers a 287 KiB result set, which makes the ratio 231x. Look only at the digest column — 112 KiB — and it is 592x, but the query also returns id, so the honest number is 231.

Does it really walk, in the code

That is the behaviour. I wanted the mechanism in the source. The accessPayload function in SQLite's btree.c reaches an offset beyond the local part of a record exactly this way: it takes the first overflow pointer from the cell, then advances through a while( nextPage ) loop, reading each page's own "next" pointer. The comment in that loop was the sentence I was after: "The only reason to read this page is to obtain the page number for the next page in the overflow chain. The page data is not required."

SQLite is not handing me body_html, not decoding it, not wanting it; it visits every page to ask "where is the next one" so it can step over 19 KB. The price of stepping over is fetching the page.

Two shortcuts exist, and neither works on this file — I have to write down why, because my first draft claimed flatly that there is no way to jump ahead, and that claim is false.

The first is the cursor cache. Once a row's chain has been walked, the page numbers live in a BtCursor.aOverflow array, and the function's header comment introduces it as a cache that "subsequent calls use ... to make seeking to the supplied offset more efficient". The cache is invalidated when the cursor moves. In a full scan the cursor moves at every row, so each of the 2,866 rows walks its chain from scratch.

The second is more interesting. The header comment on getOverflowPage says that where possible it "uses the auto-vacuum pointer-map data instead of reading the content of page ovfl" to find the next page: it guesses that the next page is ovfl+1, checks the guess against the pointer map, and on a hit never reads the overflow page at all. So SQLite does have a way to jump ahead — but only in auto-vacuum databases. In my file PRAGMA auto_vacuum is zero, there is no pointer map, and the shortcut is inoperative. The number I got is therefore this file's number, not a universal property of SQLite.

One more note: the file format document says nothing about this cost. It describes how a record is laid out, not what reaching a column costs.

Three layouts, all measured

I set up both fixes and put them through the same cold run. I also added a control, because my first comparison was flawed: it set a file that had grown over months against a freshly written one, and there was no telling how much of the gap came from the column moving and how much from the rewrite. The control is simple — VACUUM with the same column order.

layout cold read_bytes note
today's schema 64.69 MiB —
same order + VACUUM (control) 64.05 MiB the rewrite on its own changes nothing
body_html last 25.61 MiB 2.5x
covering index 0.41 MiB 158x

All four ran three times, and the byte figures repeated exactly. The control did its job: a fresh rewrite takes 64.69 down to 64.05, so the gain really does come from where the column sits.

The control had a side effect I was not looking for. The bytes did not move, but the wall clock did: 1.89 to 3.82 seconds on the aged copy, 0.26 to 0.40 seconds on the vacuumed one. Same page count, far quicker. Scattered chains must be defeating readahead. That is not what I set out to time, so I claim no factor for it, but it does mean a long-lived database can do worse than 231x on a cold cache, not better.

Why the covering index wins so completely: CREATE INDEX idx_hash ON posts(id, source_hash) holds both columns the query wants, so the table is never consulted. SQLite's optimization document describes the case: when every column to be fetched is already available in the index itself, "SQLite will use the values contained in the index and will never look up the original table row." The plan agrees: SCAN posts USING COVERING INDEX idx_hash. It costs 82 pages, 328 KiB — roughly five thousandths of the file. In exchange it creates a second b-tree to maintain on every publish; for 2,866 rows that is small, but it is not free.

Column order helped less than I expected, and the table says why: faq and headings still come before source_hash, and both run to kilobytes. "Put the big column last" is half a recipe here.

Two more options sit on the table, untested by me. The first is PRAGMA page_size: at 8 KiB, M = 1,003 and X = 8,157, so every record under 8,157 bytes stops overflowing altogether. The second is what production should actually do: move body_html into a separate table keyed by id. Metadata rows then stay narrow enough never to overflow, and this article's subject disappears.

Warm — the conditions the pipeline actually runs in — the same query against better-sqlite3 on the server takes 68.78 ms on today's schema and 3.89 ms with the covering index (median of seven runs). A factor of 17.7.

What the bill actually comes to

Here is the deflating part. How often does this query run? Counting the last seven days of deploy-only runs: three a day. On a warm cache the same chain walk bills me roughly 206 milliseconds a day. The 231-fold amplification is a cold-cache figure; if every deploy hit a cold cache it would come to 194 MiB of reads a day.

This is not a problem. The covering index is not going into the pipeline today, because changing a schema in the same breath as measuring it undercuts my trust in the measurement. It goes on the open list with its condition attached: the day a second full scan over metadata lands on this table — the search index or the newsletter query reading metadata per request — the index ships.

A small bill does not make the mechanism unimportant. In my pipeline this query runs three times a day; the same pattern running per request would be different arithmetic entirely. Three things are worth checking in your own schema.

Queries on the serving path come first. This blog's list query reads up to the draft column, position 20, which the model puts at 3,262 pages, or 12.74 MiB. It stays cheap because it is cached once per process lifetime — without that cache, every request would carry it.

Second, what drives the cost is row width, not row count. 2,866 rows is a small table; the bill here comes from the 22 KB average record. On a narrow table with a hundred thousand rows, this article's subject never surfaces.

Third, the places where the cache is absent: a network filesystem, a memory-capped container, a freshly started process. A warm cache hides this cost, and when whatever hides it goes away, the cost comes back.

Checklist

If you want to look at your own schema, the order is this:

  1. Get the ground truth with PRAGMA page_size, PRAGMA page_count and PRAGMA auto_vacuum. The last one matters: auto-vacuum on means the pointer-map shortcut is live.
  2. Run SELECT pagetype, count(*), sum(pgsize) FROM dbstat WHERE name='<table>' GROUP BY pagetype. Overflow pages in the tens of percent means wide records.
  3. Pull your column order from pragma_table_info, then compute K for your own average record size. Do not trust a "489 bytes" threshold; in my file the real median came out at 2,069.
  4. Do not estimate the cost. On Linux, posix_fadvise(POSIX_FADV_DONTNEED) plus /proc/self/io is a ten-line script; read read_bytes, not rchar, and repeat the run at least three times — the call can fail silently, and a repeated identical number is your only evidence that it did not.
  5. Before reaching for a fix, multiply unit cost by frequency. Mine came out at 206 ms/day, which is a sufficient answer for doing nothing today.

Closing

When I write SELECT id, source_hash FROM posts I think I am writing a narrow query. Two columns, both small, neither of them a body. What the measurement shows is that the width of a query is set not by the size of the columns you ask for but by where those columns sit inside the record. title is at byte 221; free. source_hash is at byte 22,581; the whole file.

The uncomfortable part is who wrote that cost. I wrote the query this week, but its price was set in June, when ALTER TABLE appended the column and I thought I was adding a column. Adding one to a schema always looks like cheap work; it returns in a second and nothing breaks. Nothing does break. But every new column lines up behind the ones before it, and that order becomes the read cost of a query somebody writes months later.

I could have caught this earlier. When four libraries were asked about mmap_size they gave two different answers — the same database, but not the same file: that snapshot was 52.2 MiB, 13,365 pages and 2,498 rows, where this one is 64.69 MiB, 16,560 pages and 2,866 rows. Do not line the two up and derive a growth rate from them; I am not doing that either. Two snapshots, nothing more. What they have in common is that both times the question went to the file itself. Database files hide nothing. It is just that nobody asks.

Official Sources

Top comments (0)