Integer Primary Keys for Embedded Writes

A high-write logger on an embedded device — a few hundred inserts a second of sensor readings into an eMMC or SD-backed database — is unusually sensitive to what you choose as the primary key, because that choice decides where every new row lands in the storage b-tree and how much extra work each insert costs. Pick a random TEXT UUID and every insert drops into an arbitrary interior page, splitting pages and dirtying blocks scattered across the file. Add AUTOINCREMENT to an integer key and every insert also writes a bookkeeping row to an internal table. Pick a plain INTEGER PRIMARY KEY and inserts append to the end of a single b-tree with no secondary index and no bookkeeping — the cheapest write SQLite offers. This page is the narrow schema decision within Schema Design for Edge Devices, part of the broader SQLite Architecture & Production Hardening discipline: on a write-dominated embedded table, which key minimises write amplification and index bloat, and how do you confirm the table is actually storing rows the cheap way.

The mechanism is the rowid. Every ordinary SQLite table is a b-tree keyed by a signed 64-bit rowid, and rows are stored physically in rowid order. When you declare a column INTEGER PRIMARY KEY, that column becomes an alias for the rowid — there is no separate primary-key index, because the table is the index. A TEXT or blob primary key cannot alias the rowid, so SQLite keeps the hidden rowid b-tree and builds a second b-tree to index your key, doubling the write per insert and scattering it if the key is random. AUTOINCREMENT keeps the integer alias but changes rowid allocation: instead of “largest existing rowid plus one,” it guarantees monotonic, never-reused values by persisting the high-water mark in an internal sqlite_sequence table that is read and rewritten on every insert.

Figure — Three key choices for the same insert-heavy table: a random TEXT key builds a second scattered index, AUTOINCREMENT appends but rewrites sqlite_sequence each insert, and a plain INTEGER PRIMARY KEY appends to one b-tree with no extra write.

Write cost of three primary-key choices on an insert-heavy embedded table One INSERT resolved three ways. A random TEXT or UUID key writes the row into the rowid b-tree and also into a second key index at a random position, splitting pages and dirtying scattered blocks — the most write amplification. AUTOINCREMENT appends the row to the end of the single b-tree but also reads and rewrites the internal sqlite_sequence row on every insert. A plain INTEGER PRIMARY KEY aliases the rowid and appends to the end of one b-tree with no secondary index and no bookkeeping write — the cheapest path. INSERT one row high-write embedded logger TEXT / UUID key WORST FOR WRITES rowid b-tree + a second key index b-tree random position splits pages across the file 2 b-trees, scattered dirty blocks per insert AUTOINCREMENT APPENDS, BUT COSTS integer alias, appends to end of one b-tree reads + rewrites sqlite_sequence every insert; monotonic, never reuses rowids INTEGER PRIMARY KEY CHEAPEST WRITE aliases the rowid — the table IS the index appends to end of one b-tree no 2nd index, no bookkeeping write

Diagnosis

Confirm two things: that a query plan against the table uses the rowid directly rather than a secondary index, and that the storage footprint is not inflated by an index you did not need. EXPLAIN QUERY PLAN on a point lookup shows how the engine reaches a row:

-- On an INTEGER PRIMARY KEY table this reports "SEARCH ... USING INTEGER PRIMARY KEY (rowid=?)"
-- On a TEXT-keyed table it reports "USING INDEX sqlite_autoindex_..." — a second b-tree.
EXPLAIN QUERY PLAN SELECT * FROM readings WHERE id = 42;

If the plan names an sqlite_autoindex_* index, your primary key is not aliasing the rowid and every insert is maintaining that extra structure. Corroborate with the physical size. PRAGMA page_count times PRAGMA page_size is the file size in bytes; comparing it against the number of rows tells you how many bytes of storage each logical row costs, and a UUID-keyed table carrying a redundant text index will be markedly heavier than an integer-keyed one holding the same data:

import sqlite3

with sqlite3.connect("/var/lib/logger/readings.db") as conn:
    try:
        pages = conn.execute("PRAGMA page_count;").fetchone()[0]   # total pages in the DB file
        psize = conn.execute("PRAGMA page_size;").fetchone()[0]    # bytes per page, usually 4096
        rows  = conn.execute("SELECT count(*) FROM readings;").fetchone()[0]
        bytes_per_row = (pages * psize) / max(rows, 1)
        print(f"file={pages * psize} B  rows={rows}  bytes/row={bytes_per_row:.1f}")
        # A high bytes/row on a narrow readings table points at a redundant key index.
    except sqlite3.Error as exc:
        raise SystemExit(f"diagnosis query failed: {exc}")

The signature of the problem is a query plan that hits a secondary index for a primary-key lookup, plus a bytes-per-row figure well above the raw column widths — both mean you are paying to maintain an index the workload does not require.

Solution

Declare the key as INTEGER PRIMARY KEY with no AUTOINCREMENT, so the column aliases the rowid and inserts append with a single b-tree write. Keep AUTOINCREMENT out unless you have a specific requirement that rowids never be reused after deletes — most loggers do not, because they either never delete or do not care that a deleted id could reappear. Verify the schema immediately with a probe insert, last_insert_rowid(), and an index count.

import sqlite3

DDL = """
CREATE TABLE IF NOT EXISTS readings (
    id      INTEGER PRIMARY KEY,   -- aliases the rowid: no AUTOINCREMENT, no second index, cheapest insert
    device  TEXT    NOT NULL,      -- typed + NOT NULL rejects malformed payloads at the storage boundary
    ts      INTEGER NOT NULL,      -- unix epoch as INTEGER; narrower and faster to compare than TEXT
    value   REAL    NOT NULL
);
"""


def build_schema(db_path: str) -> None:
    conn = sqlite3.connect(db_path)
    try:
        conn.execute(DDL)

        # Probe insert: id omitted, so SQLite assigns the next rowid automatically.
        cur = conn.execute(
            "INSERT INTO readings (device, ts, value) VALUES (?, ?, ?);",
            ("sensor-1", 1_720_000_000, 21.4),
        )
        assigned = conn.execute("SELECT last_insert_rowid();").fetchone()[0]  # the rowid SQLite chose
        assert assigned == cur.lastrowid, "last_insert_rowid disagrees with cursor.lastrowid"

        # No secondary index should exist: INTEGER PRIMARY KEY needs none.
        idx = conn.execute(
            "SELECT count(*) FROM sqlite_master WHERE type='index' AND tbl_name='readings';"
        ).fetchone()[0]
        assert idx == 0, f"expected 0 auto indexes on readings, found {idx}"

        conn.commit()
        print(f"schema ok: rowid alias assigned id={assigned}, secondary indexes={idx}")
    except sqlite3.Error:
        conn.rollback()
        raise
    finally:
        conn.close()

The id INTEGER PRIMARY KEY line is the whole optimisation: because it aliases the rowid, sqlite_master holds no sqlite_autoindex_readings_* entry and the insert touches exactly one b-tree. Storing ts as an INTEGER epoch rather than an ISO TEXT string compounds the saving — it is eight bytes at most versus twenty-plus, and integer comparison in range scans is cheaper than string comparison. If you genuinely need natural-key uniqueness (say, one row per device per timestamp), add an explicit UNIQUE index on those columns knowingly, accepting its write cost, rather than smuggling the natural key in as the primary key.

Verification

The asserts in build_schema are the primary check: a run that prints schema ok: rowid alias assigned id=1, secondary indexes=0 proves the key aliases the rowid and no hidden index exists. Confirm the query plan independently, because that is what a range scan will actually use in production:

import sqlite3

with sqlite3.connect("/var/lib/logger/readings.db") as check:
    try:
        plan = check.execute(
            "EXPLAIN QUERY PLAN SELECT value FROM readings WHERE id = ?;", (1,)
        ).fetchall()
        detail = " | ".join(str(r[-1]) for r in plan)
        assert "INTEGER PRIMARY KEY" in detail or "rowid" in detail.lower(), \
            f"lookup is not using the rowid: {detail!r}"
        print(f"verified: point lookup uses the rowid -> {detail}")
    except sqlite3.Error as exc:
        raise SystemExit(f"verification failed: {exc}")

A plan containing USING INTEGER PRIMARY KEY (or a SEARCH ... rowid=?) confirms the lookup reaches the row through the table’s own b-tree with no secondary structure. If instead you see USING INDEX sqlite_autoindex_readings_*, the primary key is not the rowid and the schema needs correcting. For a table under sustained insert pressure, follow this by watching the write path itself — the checkpoint and batching mechanics in threshold tuning for high-write workloads are where the cheap-key saving turns into real device longevity.

Failure Modes & Gotchas

AUTOINCREMENT and plain rowid differ in reuse semantics, and the difference is a correctness question, not a style one. A plain INTEGER PRIMARY KEY allocates “the largest current rowid plus one,” which means after you delete the highest row, its id can be handed to the next insert. AUTOINCREMENT forbids that reuse by persisting a monotonic high-water mark in sqlite_sequence, at the cost of an extra read-and-write per insert and a hard failure with SQLITE_FULL if you ever exhaust the 64-bit space. Only add AUTOINCREMENT when a reused id would break something external — a downstream system that treats ids as globally unique forever, or an audit trail that must never repeat a value. For an append-only logger that never deletes, it is pure overhead.

WITHOUT ROWID is the right tool for a narrow key-value table, and the wrong one for a wide logging table. A WITHOUT ROWID table stores rows in a b-tree keyed by the declared primary key itself, with no separate rowid, which is efficient when the key is small and the rows are short — a settings or lookup table keyed by a short TEXT code. But for wide rows or a non-monotonic key it hurts: the primary key is duplicated into every secondary index, and non-sequential inserts fragment the table exactly as a UUID does in a rowid table. Reach for WITHOUT ROWID deliberately for compact key-value data, not as a blanket alternative to the rowid on your high-write logging table.

Foreign keys that reference the integer key are cheap; foreign keys that reference a rowid you did not declare are a trap. A child table’s REFERENCES readings(id) against an INTEGER PRIMARY KEY parent resolves through the rowid b-tree with no extra index on the parent side. But if you reference a column that is not the primary key, SQLite requires a UNIQUE index on that parent column, quietly adding the very secondary b-tree you were avoiding. Keep references pointed at the rowid alias, and remember foreign-key enforcement is off unless PRAGMA foreign_keys = ON is set on the connection — the same per-connection switch and typed-column discipline covered across schema design for edge devices.