Payload, Overflow Pages, Large Rows, and BLOB/Text Storage

Learning outcomes

A B-tree leaf page has finite usable space. SQLite therefore cannot promise that an arbitrarily large TEXT or BLOB value will fit entirely inside one cell on one page. Instead, a cell keeps some payload locally and links additional overflow pages when the record is too large. That mechanism is why a row can be much larger than the database page size—and why large payloads have locality and operational consequences.

Why overflow exists

Suppose a database uses 4 KiB pages and you insert a 200 KiB BLOB. The table cell cannot consume dozens of neighboring bytes outside its page; page boundaries are structural. SQLite stores an amount of the record payload locally according to documented page-format rules, then stores the remainder in one or more overflow pages linked as a chain.

table leaf page
+--------------------------------------+
| cell: rowid + local payload + ptr ----+----> overflow page #1
+--------------------------------------+               |
                                                       v
                                                overflow page #2
                                                       |
                                                       v
                                                     ...

The exact local/overflow split depends on page type, usable page size, and payload length. Practitioners normally need the consequence, not the formula: a large row may require multiple page reads and additional page writes.

Measure small versus large payloads

Use separate tables so the contrast is easy to inspect. The BLOB values are generated locally with zeroblob(); there is no download and no real document content.

DROP TABLE IF EXISTS small_payload;DROP TABLE IF EXISTS large_payload;CREATE TABLE small_payload(  id INTEGER PRIMARY KEY,  body TEXT NOT NULL);CREATE TABLE large_payload(  id INTEGER PRIMARY KEY,  body BLOB NOT NULL);PRAGMA page_size;PRAGMA page_count;WITH RECURSIVE seq(x) AS (  VALUES(1) UNION ALL SELECT x+1 FROM seq WHERE x<2000)INSERT INTO small_payload(body)SELECT printf('small-%05d-%0200d',x,x) FROM seq;PRAGMA page_count;WITH RECURSIVE seq(x) AS (  VALUES(1) UNION ALL SELECT x+1 FROM seq WHERE x<20)INSERT INTO large_payload(body)SELECT zeroblob(262144) FROM seq;   -- 256 KiB × 20 rowsPRAGMA page_count;SELECT COUNT(*), sum(length(body)) FROM large_payload;

The 20 large BLOB rows contain about 5 MiB of logical payload, so the file should grow by many pages. Exact overhead depends on page size and packing. This is a storage observation—not a speed benchmark.

Observe overflow with dbstat when available

SELECT sqlite_compileoption_used('ENABLE_DBSTAT_VTAB') AS has_dbstat;-- Only if has_dbstat=1:SELECT name, pagetype, COUNT(*) AS pages,       SUM(payload) AS payload_bytes,       SUM(unused) AS unused_bytes,       MAX(mx_payload) AS largest_cell_payloadFROM dbstatWHERE name IN ('small_payload','large_payload')GROUP BY name, pagetypeORDER BY name, pagetype;SELECT pageno, path, pagetype, payload, unused, mx_payloadFROM dbstatWHERE name='large_payload' AND pagetype='overflow'LIMIT 12;

Expect large_payload to show overflow pages in a dbstat-enabled build. If dbstat is unavailable, use the page_count/file-size experiment plus the official file-format diagram; the overflow mechanism is not optional even though this inspection interface is.

Large rows change locality

A narrow row often lets many logical records share one leaf page. Large fields reduce that packing density and can create overflow chains. Therefore, fetching one logical row may touch multiple database pages, and a scan that selects wide payload columns can transfer far more data through the pager and application boundary than a metadata-only query.

BLOB inside SQLite or external file/object?

There is no universal threshold at which BLOBs “must” leave SQLite. The decision is operational. SQLite can store binary data transactionally with related metadata, while a filesystem/object store can offer independent streaming, CDN/object lifecycle tools, and avoid copying huge blobs during some database operations.

Measure with your real payload sizes, backup method, write pattern, and restore requirements. “Filesystem is always faster” and “SQLite is always simpler” are both too broad.

Incremental BLOB I/O: avoid materializing the entire value at once

SQLite’s C API exposes sqlite3_blob_open(), sqlite3_blob_read(), and sqlite3_blob_write(). Many language drivers expose corresponding functionality. An incremental BLOB handle can read or overwrite subsections of an existing TEXT/BLOB value without first copying the entire value into one application buffer.

Important limits matter: the handle targets a rowid-table row; it does not work on WITHOUT ROWID tables; writes cannot change the BLOB size; and the row/column has additional restrictions when used by certain indexes/constraints. A common pattern is to create a fixed-size value with zeroblob(N), then fill it incrementally.

import sqlite3con = sqlite3.connect("fieldnotes-storage.db")con.execute("DROP TABLE IF EXISTS blob_stream")con.execute("CREATE TABLE blob_stream(id INTEGER PRIMARY KEY, data BLOB NOT NULL)")rowid = con.execute(    "INSERT INTO blob_stream(data) VALUES(zeroblob(?)) RETURNING id", (1024*1024,)).fetchone()[0]# Python 3.11+ sqlite3 exposes Connection.blobopen().with con.blobopen("blob_stream", "data", rowid, readonly=False) as blob:    blob.seek(0)    blob.write(b"FIELDNOTES")    blob.seek(0)    print(blob.read(10))       # b'FIELDNOTES'    print(len(blob))           # 1048576con.commit()con.close()

If your driver lacks an incremental-BLOB API, bind BLOB parameters normally and design chunking at the application layer. Do not concatenate binary data into SQL text.

Failure cases and diagnosis

Design exercise: three payload classes

Bridge

Large payloads explain one way files grow. Deletions explain another surprise: removing data often does not make the operating-system file smaller. Lesson 4 follows freed pages onto the freelist and compares auto-vacuum modes, full VACUUM rebuilds, incremental vacuum, and VACUUM INTO.

Authoritative references

• SQLite — Database File Format: cell payload overflow pages.
• SQLite C API — Incremental BLOB I/O.
• SQLite — DBSTAT Virtual Table.
• SQLite — 35% Faster Than The Filesystem (measured BLOB study, context not universal rule).