Before SQLite VACUUM, Verify What Your Saved Row IDs Identify

When an application or user stores SQLite rowids for later lookup, a database maintenance operation such as VACUUM can unexpectedly break that reference. Imagine an app that saves a numeric pointer (rowid) to a local record. After running VACUUM, which “rebuilds the database file”, you might find that the same saved number now selects a different surviving row, even though all the original records still exist. In our experiment, a hidden-rowid table with business-key/payload rows (A/alpha, B/beta, C/gamma) had saved handles 2, 3, 4. After VACUUM on SQLite 3.53.1, these rowids became 1, 2, 3, so saved handle 2 returned B instead of A, 3 returned C instead of B, and 4 returned nothing. (We emphasize this is an observed outcome on this build/schema, not a guaranteed renumbering scheme.)

A structurally consistent database (“ok” on PRAGMA integrity_check) can still violate an application’s identity contract. In other words, the same set of business rows and valid B-tree format does not guarantee each saved handle still names the same logical entity. This article gives a precise playbook: it defines what must hold true for application-level references, shows how to test the behavior on a local disposable database, and compares outcomes for an ordinary rowid table vs a table with an explicit INTEGER PRIMARY KEY. We also build a “migration candidate” with an explicit key from the original mapping and verify it remains correct across the VACUUM. The result is clear evidence and a decision guide: either accept that your identity mapping holds, migrate to a safer key and retest, rebuild references from pre-VACUUM data, or halt and fix things if the mapping cannot be trusted.

This local experiment uses a single file and single Python process (no concurrent writers, no WAL, no attached DBs or ORMs). We insert three distinct business-key rows (A, B, C) with payloads (alpha, beta, gamma) into two tables:

CREATE TABLE loose (
    business_key TEXT NOT NULL,
    payload TEXT NOT NULL
);
CREATE TABLE stable (
    id INTEGER PRIMARY KEY,
    business_key TEXT NOT NULL UNIQUE,
    payload TEXT NOT NULL
);

The loose table has no explicit primary key, so SQLite generates a hidden 64-bit rowid for each row. The stable table declares id INTEGER PRIMARY KEY, so id is an alias for rowid. We record an independent manifest of the expected mapping: rowid 2→(A, alpha), 3→(B, beta), 4→(C, gamma). After populating both tables with these triples (one transaction shared by both tables), we verify the baseline against that manifest. Then we run VACUUM and reopen the DB read-only to compare. We will see that although both tables still have three intact rows and integrity_check returns “ok”, the saved handles in loose no longer point to the original records, whereas the stable table (and a pre-vacuum “migrated” copy) preserve the mapping.

The audience for this playbook, DBAs and back-end developers, already knows SQL and is interested in maintenance correctness, not in database basics or general sizing strategies. Our scope is strictly: given a saved numeric rowid from before running VACUUM, does SELECTing by that rowid still fetch the same intended business record after VACUUM? We exclude WAL backups, copy-once logical backups, foreign-key cascades, auto_vacuum modes, AUTOINCREMENT, WITHOUT ROWID tables, Unicode collation issues, etc. This is a focused identity-check. We’ll explain the code, the evidence, and what decisions an operator can make. The safe verdict is based on complete evidence, not just trusting the database file is “openable.”

Define which reference must retain its meaning

The reference we care about is a saved handle (a numeric rowid plus an independently known business key and payload) that an application expects to still identify the same record after maintenance. In our example, imagine a task-tracking app that has queued up local record IDs for later processing, or bookmarks linking to SQLite rows. We treat one scenario to be concrete: say the user “Alice” saved a link to record A (alpha) via its numeric handle 2. Our manifest says 2→A (alpha), 3→B (beta), 4→C (gamma). We also store these expected values in an external list in our test to compare later, so we don’t accidentally just read the new database and assume it’s correct.

We assume these business keys (A, B, C) are guaranteed unique and not changed by maintenance; they form the true data content. The saved handle is purely an implementation-level ID. This separation (business key vs. storage ID) is a common data modeling concept: think of the business key as the logical row identity or grain in analytical terms. Our task is to ensure that handle=2 still corresponds to business_key=A after VACUUM. In other words, we want the two pieces (numeric handle, business key) to remain in sync as they were in our pre-VACUUM manifest.

In summary, the reference we must retain is a triple: (saved_rowid, expected business_key, expected payload). For each such expected triple, the post-VACUUM table must contain a row with that rowid and matching business_key/payload. Otherwise the mapping has drifted. We do not consider it sufficient that the table has exactly the same set of business rows; the application contracts that “handle 2 is A,” not just that A exists somewhere.

Inspect the declaration behind the number

Before testing, we compare the exact table schemas. The loose table was created as:

CREATE TABLE loose(
    business_key TEXT NOT NULL,
    payload      TEXT NOT NULL
);

It has no declared PRIMARY KEY at all. SQLite therefore implicitly uses a hidden ROWID column (a 64-bit signed integer) for each row. In effect, loose is a classic rowid table: it has a built-in rowid (alias rowid) that uniquely identifies each row in the storage B-Tree. We did not define any UNIQUE or index on business_key, so the only key is this rowid.

The stable table was created as:

CREATE TABLE stable(
    id          INTEGER PRIMARY KEY,
    business_key TEXT NOT NULL UNIQUE,
    payload      TEXT NOT NULL
);

This declares an explicit column id INTEGER PRIMARY KEY. In SQLite, a single-column PRIMARY KEY of exact type INTEGER (case-insensitive) makes that column an alias for the internal rowid. In other words, stable.id and the rowid are the same 64-bit value. (If we had declared id INT PRIMARY KEY or BIGINT PRIMARY KEY, those would not alias rowid due to SQLite’s quirky alias rule. We deliberately stick to exactly INTEGER PRIMARY KEY.) The effect is that inserting rows into stable assigns id as the rowid, and we may refer to the row by id.

To summarize: loose has a hidden rowid with no persistent name, while stable has a named id column as its rowid. According to SQLite’s rowid documentation, a rowid that is not aliased by an INTEGER PRIMARY KEY is not persistent and can change. The documentation specifically identifies VACUUM as an operation that changes rowids in tables without an INTEGER PRIMARY KEY. In contrast, a declared INTEGER PRIMARY KEY is meant to survive VACUUM in the sense that the explicitly declared identifier goes through the rebuild unchanged.

Despite these differences, both tables currently have the same business_key and payload rows. The main schema difference under test is how we identify each row by an integer; stable also declares business_key UNIQUE. This sets up our test: we will compare how a vacuum affects a table with a hidden rowid vs. one with an explicit integer key.

Distinguish a hidden rowid from the explicit key contract

The critical question is: which SQL expression does the application use to look up a record by the saved handle? For the loose table, a lookup must use something like WHERE rowid = ?. (We’re not using any declared PRIMARY KEY or indexed column to find the row, since none was declared.) We exclude any fancy cases like virtual tables or WITHOUT ROWID tables, and we’re not using any collating or truncation here. The user explicitly relies on the raw integer rowid.

For the stable table, the lookup is WHERE id = ?. Since id is an alias of the rowid, this has the same effect. However, because id is an explicit column, it exposes the rowid under a declared name with non-null and unique identifier semantics. (SQLite ensures id is non-null and unique, per the SQL standard form of a PRIMARY KEY.)

Note that if we had declared some other PRIMARY KEY (say a TEXT PRIMARY KEY on business_key), or used AUTOINCREMENT, or a composite key, that would not satisfy this exact test and we are not doing those. We also are not dealing with any INT vs INTEGER trick or with descending/ASC clauses. We keep it simple: loose has the plain hidden rowid; stable has id INTEGER PRIMARY KEY. This is exactly the condition where SQLite’s docs say that only the unaliased rowid is unstable across VACUUM.

Freeze a disposable maintenance environment

We run our test in a brand-new local directory to avoid any interference. We create one SQLite file and set journal_mode=DELETE (the default rollback-journal mode) so that VACUUM acts on the main file with no WAL. We use a single Python (3.12.14) process with sqlite3 in autocommit mode (so each statement is its own transaction unless we explicitly BEGIN). We explicitly begin and commit transactions where noted in the script. Before VACUUM, we finalize cursors and ensure no transaction is open; after VACUUM, we close the connection and reopen the database read-only (since VACUUM will fail if the connection has an open transaction).

We record our environment metadata. For example, in Python we can query sqlite3.sqlite_version and run SELECT sqlite_source_id() to log that we are using SQLite 3.53.1 (source ID dated 2026-05-05 in this environment). We also retain the schema DDL in the fixture and log the fixture revision ID and query results, so the entire run is reproducible.

We deliberately avoid using WAL-mode or any attached databases. This is a pure offline test on one file. (By contrast, a WAL backup test is about open/closed snapshots; here our only focus is identity within one closed file.) We also avoid any external backups or copying: we trust VACUUM to rebuild internally and then we reopen the same file read-only for verification.

As a side note, the Refonte WAL backup validation article shows how to confirm that rows remain present after backup/restore, and it chose an explicit PK so that row identity wouldn’t change. In our case, we want hidden rowids to potentially change, to detect the problem. The WAL article is a good reference on committed-state completeness, but here our question is different: we ask “did handle 2 still select the same entity A after VACUUM?”

Capture the mapping before running maintenance

We start by writing down the expected mapping (the manifest) in our Python code, completely outside the database. For clarity, we define it as:

EXPECTED = [(2, "A", "alpha"), (3, "B", "beta"), (4, "C", "gamma")]

This tuple list is not retrieved from any SQL result; it is our independent source of truth about which handle→(key,payload) pairs should exist. We then insert each triple into both tables. (For loose, we explicitly set the rowid with INSERT INTO loose(rowid, business_key, payload) VALUES (?, ?, ?). For stable, we do INSERT INTO stable(id, business_key, payload) VALUES (?, ?, ?). In each case we use the values (2, "A", "alpha"), etc. We wrap these in a BEGIN/COMMIT transaction to make it atomic and then commit before proceeding.)

Now we have baseline snapshots. We fetch rows from each table ordered by rowid to make our evidence reproducible (SQL row order is not guaranteed, so we say ORDER BY rowid). For the loose table we see something like:

And for stable it looks like:

(We note that for stable, rowid and id are the same value.) We prepare a third table, migrated, in a later step before VACUUM. We store these baseline snapshots in our report and compare them to the manifest.

We require that the baseline tables exactly match our expected data. In code this means verifying there are 3 rows in each table, and each expected (id, key, value) triple is present with the correct rowid. If there is any discrepancy (say a missing row or wrong payload), we would abort the test (since it means the setup failed). We do not just hash the contents; we check equality element-wise to ensure we really match the manifest. We use ORDER BY to make the snapshots reproducible, then reconcile each saved handle against the manifest. At this point, both loose and stable have identical ordered content matching our manifest. In fact, our script enforces:

Exactly 3 rows in loose and stable.

Row (2, A, alpha) etc. in each.

One saved handle per record (the rowid for loose, the id for stable).

Only with the baseline verified do we proceed. Our script is set to stop if the baseline doesn’t exactly match EXPECTED.

Validate the baseline against the independent manifest

This step is crucial because it separates our external reference from any database output. We log, for each table, the snapshot of (rowid, business_key, payload) and check it against EXPECTED = [(2,A,alpha),(3,B,beta),(4,C,gamma)]. We report the “before” state in our results. For example, in JSON form one might record:

{
  "before": {
    "loose": [[2, "A", "alpha"], [3, "B", "beta"], [4, "C", "gamma"]],
    "stable": [[2, "A", "alpha"], [3, "B", "beta"], [4, "C", "gamma"]]
  }
}

The script’s require assertions ensure each row matches. If something were off, it would raise an error like “Bad baseline” or “Migration mismatch” (the latter after we build migrated). Because the fixture is tiny, we explicitly see all values; this is not just a checksum. By isolating the expected mapping outside of SQL and reading the rows in a consistent order, we guarantee our test’s integrity.

After this, we prepare the “migration candidate” table. In the same database (before VACUUM), we do:

CREATE TABLE migrated(
    id INTEGER PRIMARY KEY, 
    business_key TEXT NOT NULL UNIQUE, 
    payload TEXT NOT NULL
);
INSERT INTO migrated(id, business_key, payload)
  SELECT rowid, business_key, payload FROM loose;

This copies each row from loose into migrated, using the old rowid as the new id. We then snapshot migrated as well, before committing. It should match (2,A,alpha),(3,B,beta),(4,C,gamma) just like the others. This table simulates “if we had a safe explicit-key copy prepared beforehand”. We confirm it now and will later check it after VACUUM too.

Run VACUUM and observe the hidden-rowid counterexample

With the baseline set, we run the maintenance operation. First, we make sure no transaction is open (we committed the migration candidate, rolled back the separate transaction rejection control, and closed any cursors). Then we execute:

VACUUM;

Note: We do not wrap VACUUM inside an application BEGIN/COMMIT. (Indeed, SQLite forbids that: if a transaction is open, VACUUM raises “cannot VACUUM from within a transaction”. Our script even tests this by attempting a BEGIN;VACUUM and expecting an OperationalError.) Here we simply run VACUUM in its own statement. Under the hood, SQLite copies the main database into a new temp file and writes it back.

After VACUUM finishes, we close the database connection and then reopen the file in read-only mode to inspect. Now we take the same snapshots “after” as we did “before”. In our observed run on SQLite 3.53.1, the results were:

loose.after: [(1,"A","alpha"), (2,"B","beta"), (3,"C","gamma")]

stable.after: [(2,"A","alpha"), (3,"B","beta"), (4,"C","gamma")]

migrated.after: [(2,"A","alpha"), (3,"B","beta"), (4,"C","gamma")]

Put plainly, the loose table’s rowids shifted down by 1: A moved from rowid 2 to 1, B from 3→2, C from 4→3. During this rebuild, SQLite repacked the pages and assigned rowids 1, 2, and 3 to these rows. (Which exact renumbering occurs can depend on build and fill patterns, but the key is some change occurred.) The stable and migrated tables were unaffected: they still had rowids 2, 3, 4 for A, B, C.

We emphasize that this is an observed counterexample run, not a proof of a fixed algorithm: another run or SQLite build might by coincidence leave rowids unchanged. SQLite’s VACUUM documentation warns that hidden rowids may change, while the Rowid Tables documentation identifies unaliased rowids as nonpersistent and explicitly names VACUUM as an operation that changes them. In our case we saw a change. If by any chance another test showed no change in loose, that would merely underscore that anything can happen: the point is not a specific delta, but that one cannot rely on the old mapping afterward.

From the application’s point of view, the impact is: saved handle 2 now returns the row with business_key=B (beta) instead of A (alpha); handle 3 returns C instead of B; handle 4 returns no row (it’s beyond the new highest rowid). In short, all three saved handles in loose now select the wrong or no record. This is the identity failure we wanted to test.

Compare reference meaning with structural checks

Now we compare various signals. First we run a structural integrity check. Executing PRAGMA integrity_check; on the reopened database returns a single row containing "ok", with no errors. This means SQLite found no corruption: all tables and indexes are consistent. According to the docs, PRAGMA integrity_check “does a low-level formatting and consistency check” for out-of-order pages, misformatted records, missing pages, index errors, etc., and returns a single row “ok” if it finds no issues. In all our tables (loose, stable, migrated) we saw that: three rows, properly indexed, matching constraints. We did not have foreign keys anyway, which integrity_check wouldn’t catch. But the key point is: integrity_check does not know about our application’s mapping. It only checks the database internals.

Next, we verify that the business content (key/payload pairs) was preserved. Each table still has 3 rows, and sorting by business_key yields (A,alpha),(B,beta),(C,gamma). We confirm that explicitly in the script. All tables passed these structural/content tests: same row count, same (unordered) values, integrity_check=ok.

Finally, we check the identity reconciliation (the saved-handle mapping) by comparing each table’s after-snapshot to the expected manifest. We build a small table of the results for clarity:

Here the columns come from our reconcile function in the code. In loose (the hidden-rowid table), none of the saved handles match the expected row. In both stable and migrated, all of them match. This shows precisely why three valid rows (unchanged content) can still break the application’s identity: only loose failed the reference contract.

All tables passed integrity_check (structure) and retained all three business rows (content). But that was not sufficient: the application’s requirement that “rowid 2 means business key A” failed in the loose case. The integrity check was a necessary but not sufficient signal. In fact, structural validity can mask these higher-level mismatches. The mismatch is independent of the data format; it is a semantic mapping issue. (For a contrast, see the SQLite NOCASE article: there a valid table can have “Équipe” and “équipe” distinct yet against an application’s uniqueness policy. Similarly, here the table is valid, but the saved rowid policy is broken.)

We conclude that only a table with an explicit INTEGER PRIMARY KEY column can be relied upon to preserve the mapping between a pre-VACUUM numeric handle and its meaning across VACUUM, at least in this test. The plain rowid (loose table) is not a stable reference. Of course, this test alone doesn’t prove all future operations won’t break an explicit key (a user could still change it later), but it does show vacuum itself will not alter it.

Read an ‘ok’ result within its documented scope

It’s worth pausing on the integrity check. As SQLite’s documentation makes clear, PRAGMA integrity_check is a low-level sanity scan. It will return "ok" if all B-trees and indexes are consistent internally, and any declared constraints (UNIQUE, NOT NULL, etc.) aren’t violated. But it has no knowledge of your “business key” policy or any external mapping list. In our test, integrity_check returned ok for all tables, even though loose had a failed reference mapping.

This illustrates the difference between structural correctness and application-level correctness. The former (integrity) was satisfied by all tables. But the application-level contract (“saved rowid X still selects the same intended business record”) was only met by those tables whose schema supports it. In other words, an ‘ok’ from SQLite’s consistency check is necessary (you definitely need that), but it does not suffice to prove your identity contract. (You must explicitly test the mapping.) We’re not saying structural checks are useless; on the contrary, we executed them and used SQLite’s documentation to define their scope. But we must treat them as one of multiple signals, not the sole deciding factor.

Reproduce the comparison with one complete fixture

Below is the complete Python script we used to perform this test in one go. It creates and populates the database, prepares the migration table, validates the baseline, tries VACUUM in a transaction (to capture the expected error), runs VACUUM, then reopens the DB read-only to snapshot and check everything. Each stage records its results and errors in a JSON report. We include it here for transparency and reproducibility. The script writes an output file sqlite_rowid_results-<run_id>.json in the same directory, logging all the before/after data.

"""Isolated VACUUM identity research; no external database is accepted."""
import json
import platform
import sqlite3
import tempfile
import traceback
import uuid
from datetime import datetime, timezone
from pathlib import Path

EXPECTED = [(2, "A", "alpha"), (3, "B", "beta"), (4, "C", "gamma")]

def require(condition, message):
    if not condition:
        raise RuntimeError(message)

def fetch(db, sql, params=()):
    cur = db.execute(sql, params)
    try:
        return cur.fetchall()
    finally:
        cur.close()

def snapshot(db, table):
    require(table in {"loose", "stable", "migrated"}, "Unknown fixture table")
    return fetch(
        db,
        f"SELECT rowid, business_key, payload FROM {table} ORDER BY rowid"
    )

def reconcile(rows):
    lookup = {row[0]: row[1:] for row in rows}
    return [{"saved_rowid": rid, "expected": [key, value],
             "actual": list(lookup[rid]) if rid in lookup else None,
             "identity_matches": lookup.get(rid) == (key, value)}
            for rid, key, value in EXPECTED]

run_id = uuid.uuid4().hex
report_path = Path(__file__).resolve().with_name(
    f"sqlite_rowid_results-{run_id}.json"
)
report = {
    "run_id": run_id,
    "report_path": str(report_path),
    "fixture_revision": "rowid-evidence-2",
    "started_at": datetime.now(timezone.utc).isoformat(),
    "python": platform.python_version(),
    "sqlite_runtime": sqlite3.sqlite_version,
    "stage": "initialize",
    "result": "FAIL",
    "expected": EXPECTED,
    "before": {},
    "after": {},
    "saved_handle_reconciliation": {}
}

try:
    with tempfile.TemporaryDirectory(prefix="refonte-rowid-") as directory:
        path = Path(directory) / "fixture.db"
        report["stage"] = "create_database"
        db = sqlite3.connect(path, autocommit=True)
        try:
            report["sqlite_source_id"] = fetch(
                db, "SELECT sqlite_source_id()"
            )[0][0]
            report["stage"] = "create_schema"
            report["journal_mode"] = fetch(db, "PRAGMA journal_mode=DELETE")[0][0]
            fetch(
                db,
                "CREATE TABLE loose(business_key TEXT NOT NULL, "
                "payload TEXT NOT NULL)"
            )
            fetch(
                db,
                "CREATE TABLE stable(id INTEGER PRIMARY KEY, "
                "business_key TEXT NOT NULL UNIQUE, payload TEXT NOT NULL)"
            )
            report["stage"] = "populate_baseline"
            fetch(db, "BEGIN")
            for row in EXPECTED:
                fetch(
                    db,
                    "INSERT INTO loose(rowid,business_key,payload) VALUES(?,?,?)",
                    row
                )
                fetch(
                    db,
                    "INSERT INTO stable(id,business_key,payload) VALUES(?,?,?)",
                    row
                )
            fetch(db, "COMMIT")
            report["stage"] = "validate_baseline"
            for table in ("loose", "stable"):
                report["before"][table] = snapshot(db, table)
            require(
                all(item["identity_matches"]
                    for item in reconcile(report["before"]["loose"])),
                "Bad baseline"
            )
            require(
                all(item["identity_matches"]
                    for item in reconcile(report["before"]["stable"])),
                "Bad baseline"
            )

            # Prepare migration candidate with explicit id key
            report["stage"] = "prepare_migration"
            fetch(db, "BEGIN")
            fetch(
                db,
                "CREATE TABLE migrated(id INTEGER PRIMARY KEY, "
                "business_key TEXT NOT NULL UNIQUE, payload TEXT NOT NULL)"
            )
            fetch(
                db,
                "INSERT INTO migrated(id,business_key,payload) "
                "SELECT rowid,business_key,payload FROM loose"
            )
            report["migration_before"] = snapshot(db, "migrated")
            require(
                all(item["identity_matches"]
                    for item in reconcile(report["migration_before"])),
                "Migration mismatch"
            )
            fetch(db, "COMMIT")

            # Test that VACUUM fails inside a transaction
            report["stage"] = "transaction_rejection_control"
            fetch(db, "BEGIN")
            try:
                fetch(db, "VACUUM")
            except sqlite3.OperationalError as exc:
                report["active_transaction_rejected"] = str(exc)
            else:
                raise RuntimeError("Active transaction did not block VACUUM")
            finally:
                fetch(db, "ROLLBACK")
            require(not db.in_transaction, "Transaction remains active")

            report["stage"] = "vacuum"
            fetch(db, "VACUUM")
        finally:
            db.close()

        # Reopen read-only for verification
        report["stage"] = "reopen_read_only"
        db = sqlite3.connect(path.as_uri()+"?mode=ro", uri=True, autocommit=True)
        try:
            report["stage"] = "post_maintenance_snapshots"
            for table in ("loose", "stable", "migrated"):
                report["after"][table] = snapshot(db, table)
            report["stage"] = "integrity_check"
            report["integrity_check"] = fetch(db, "PRAGMA integrity_check")
            require(
                report["integrity_check"] == [("ok",)],
                "Structural check failed"
            )
            report["stage"] = "business_content_checks"
            expected_content = sorted((key,value) for ,key,value in EXPECTED)
            for table, rows in report["after"].items():
                require(len(rows)==3, "Wrong record count")
                require(
                    sorted((key,value) for ,key,value in rows)==expected_content,
                    "Business content changed"
                )
            report["stage"] = "identity_reconciliation"
            for table, rows in report["after"].items():
                report["saved_handle_reconciliation"][table] = reconcile(rows)
            decisions = report["saved_handle_reconciliation"]
            for table in ("stable", "migrated"):
                require(
                    all(item["identity_matches"] for item in decisions[table]),
                    "Explicit-ID identity mismatch"
                )
            unstable_changed = any(
                not item["identity_matches"] for item in decisions["loose"]
            )
            report["implicit_rowid_changed_in_this_run"] = unstable_changed
        finally:
            db.close()

    report["stage"] = "complete"
    report["result"] = (
        "PASS" if unstable_changed else "COUNTEREXAMPLE_NOT_REPRODUCED"
    )

except Exception as exc:
    report["result"] = "FAIL"
    report["error"] = {"type":type(exc).__name__, "message":str(exc)}
    report["traceback"] = traceback.format_exc()
    raise
finally:
    report["finished_at"] = datetime.now(timezone.utc).isoformat()
    with report_path.open("x", encoding="utf-8") as output:
        output.write(json.dumps(report,indent=2)+"\n")
    print(json.dumps(report,indent=2))

This complete fixture covers all steps. The sections we explained above correspond to stages in the script: create_schema, populate_baseline, validate_baseline, prepare_migration, transaction_rejection_control, vacuum, reopen, snapshots, integrity/content checks, and identity reconciliation. Notice how each require call either ensures progression or stops with a clear error (so partial failures show up as JSON error fields). Also note that if the loose table had not changed its rowids, the final unstable_changed would be False and the result would be “COUNTEREXAMPLE_NOT_REPRODUCED”; in that unlikely case we’d log it and likely retest or investigate. In our run, it was True, so result = "PASS" meaning we observed the counterexample as expected.

Important: if you run this script, it will write files and print a report. It closes cursors and connections properly. We do one real VACUUM outside any transaction (as required), and we also verify the correct error when trying inside a transaction. We do not wrap the actual VACUUM in a rollback transaction; that would be meaningless. The ROLLBACK we used was only to undo the failed attempt in order to proceed to the real vacuum.

We now turn back to interpreting the results. The migration candidate table was built and checked before VACUUM, so it was complete and correct under the original mapping. We’ll test it after VACUUM next.

Prove the explicit-key control survives the same maintenance

Now we focus on the stable table (and similarly migrated) to show they did not exhibit the problem. Recall both stable and migrated had identical content and numeric values before VACUUM. The only difference is schema: stable always stored id as its rowid, and migrated was created as id from the original rowid of loose.

After VACUUM, we observed stable.after = [(2,A,alpha),(3,B,beta),(4,C,gamma)] and the same for migrated. This means for stable, rowid 2 is still (A,alpha), 3→(B,beta), 4→(C,gamma). We run the same reconciliation check as before. In the JSON output above, we see for stable and migrated all identity_matches=true. In words: every saved handle 2, 3, 4 pointed to the expected business record.

Since both tables still have the same row count and contents as before, the difference is clearly that their declared key “locked in” the ID. Even though we ran VACUUM, it did not reassign the integer ID values in these tables. The garbage pages were reclaimed, but the explicit id values survived. This demonstrates that declaring an INTEGER PRIMARY KEY effectively enforces stable identity through VACUUM.

We note the limitation: this test only shows the key’s stability through the VACUUM operation. It does not mean the primary key is immutable forever. Application code could still UPDATE or delete and reinsert rows, which could change IDs. For example, if the app later did DELETE FROM stable WHERE id=2; INSERT INTO stable(id, ...) VALUES (2, ...), the original mapping would break. That is a separate application-level policy (or conflict resolution issue), not related to VACUUM itself. Our script did not test any DML beyond the simple insert of the fixture.

Thus, an explicit key passing this VACUUM does not automatically make all future operations safe; it just passes this particular operation. If your application requires that identifiers never change (even beyond VACUUM), you must enforce that by never altering the ID or reusing old IDs, perhaps aided by additional constraints (e.g. AUTOINCREMENT if you want monotonically increasing non-recycled IDs). But remember, AUTOINCREMENT only affects how new IDs are chosen; it does not change how VACUUM behaves on existing rowids. We do not recommend blindly turning on AUTOINCREMENT as a fix here; it’s a different topic. For now we simply show that given an INTEGER PRIMARY KEY, the expected mapping held. Managing future INSERTs/DELETEs is up to the application's wider design.

Keep primary-key persistence separate from immutability

Having an INTEGER PRIMARY KEY means SQLite will treat that column’s values as the definitive row identifiers and preserve them through VACUUM. However, this is not a legal argument that the application can change keys arbitrarily or that the DB forever forbids reusing old IDs. The key is persistent during this maintenance operation. It does not mean developers can later UPDATE the PK or drop and re-add rows and expect the app to still have valid bookmarks. Those are separate design decisions.

In short, the explicit key survived this maintenance, but whether it remains a stable business identifier depends on broader policies: whether you allow ID updates, whether you cascade updates in related tables, etc. These are outside the scope of the VACUUM test. What we can say is: using an INTEGER PRIMARY KEY makes the saved-rowid test pass here. But you should still govern your IDs properly (for example, disallow changing them and ensure referential integrity) according to your application’s needs.

Migrate while the original mapping is still trustworthy

Next we simulate a proactive fix: what if we decide before maintenance to migrate the table to an explicit-key form using the old mapping? That’s what our migrated table does. We create it and fill it before VACUUM so that its id column contains the original rowids from loose. We verified before VACUUM that migrated had id=2→(A,alpha), etc.; its “migration_before” snapshot passed identity check.

We then let VACUUM run. After VACUUM, we snapshot migrated (now stable-like). The result was the same as for stable: it still has id 2→A, 3→B, 4→C. In other words, by establishing the explicit primary key via copying before the vacuum, the mapping was preserved exactly.

This demonstrates a migration workflow: if we have an existing database with a hidden-rowid table and clients that store those rowids, one remedy is to create a new table with an explicit key column that uses the old rowid as its new PRIMARY KEY. As long as we do this migration before any maintenance that would break the mapping, then even after that maintenance the new table’s IDs will match the old ones. Crucially, we must ensure this migration is done while the old mapping is still correct (no race with concurrent deletes, etc.). In practice one would probably do something like:

• Stop writes to the old table (or operate in a read-only phase).

• Create a new table with id INTEGER PRIMARY KEY and copy old rowid, business_key, payload.

• Switch applications/queries to use the new table (or rename tables).

• Run VACUUM if needed.

Our test above was just a simplified version of step 2 done by hand. A real cutover would involve ensuring client code now references the new key. But the key insight is: performing the migration while the old mapping is still valid preserves it after VACUUM. The application can then switch to using the new integer PK consistently. We note that after this, the names of handles (the numeric IDs) stayed the same as before, which is what you want if you are trying to salvage existing references. We do not claim that this alone is a drop-in fix for all systems; you’d also need to verify, for example, that no foreign-key relationships or application caches break. It’s one tool in the toolbox.

Stop when transaction state makes the test invalid

Our script included a deliberate check for VACUUM behavior inside a transaction. We did:

BEGIN;
VACUUM;

which correctly raised an error (OperationalError: cannot VACUUM from within a transaction). We caught that exception and rolled back the transaction. The message we observed was like "cannot VACUUM from within a transaction", matching SQLite’s documentation. This is a sanity check: if you forgot to commit earlier transactions, your VACUUM wouldn’t run, so the identity contract test wouldn’t actually happen.

By capturing this error, we enforced a rule: if VACUUM fails due to an active transaction, we treat the test as invalid; we did not proceed to read-only checks in that case. In our script, we rolled back after catching the error, then did a fresh VACUUM. If your system logs such an error, it should stop the maintenance and fix the transaction logic. The lesson is: VACUUM must run on a clean commit state (the docs say “no open transaction”). We intentionally did not allow ourselves to “ignore” that failure.

We mention, as an aside, that this is unrelated to the issue of replace or cascades. The SQLite REPLACE vs UPSERT article covers how inserting or updating rows might delete related rows under some semantics. By contrast, our VACUUM does not modify business columns or delete anything by design; it only reorganizes storage. The only failure of VACUUM inside a transaction is a usage error, not a business-logic issue. Therefore, once we see that error, we simply back out of the transaction (via ROLLBACK), then proceed with VACUUM normally. We do not attempt to use rollback as an “undo” for a completed VACUUM; because VACUUM itself is atomic and write-locked, you cannot reverse it with a SQL ROLLBACK after the fact. Rolling back just discarded the failed VACUUM attempt.

In short, an active transaction is a precondition check, not part of the identity test. If that step fails, the test environment is not properly set up, and we must fix it rather than misinterpret it as “VACUUM broke my rows”. We captured that error explicitly to avoid confusion.

Recover references from independent identity evidence

Now suppose we are in a situation where the hidden-rowid table has already been VACUUMed (or otherwise changed) and the saved handles no longer match. What can we do? The principle is: do not renumber or fudge the current database just to make the handles work. Instead, we rely on external evidence of the old mapping. In our lab, that evidence is the EXPECTED manifest we recorded.

In practice, you might have a log or mapping table from before the maintenance, or you might still have the pre-migration table as a backup. Using that trusted prior mapping, you could rebuild a new table: for each old handle H and business key K, you find the row in the current database with key K and assign it handle H. In SQL, you could do something like:

CREATE TABLE repaired(
    id INTEGER PRIMARY KEY,
    business_key TEXT,
    payload TEXT
);
INSERT INTO repaired(id, business_key, payload)
  SELECT old_mapping.old_id, loose.business_key, loose.payload
  FROM loose -- or from the table after maintenance
  JOIN old_mapping ON old_mapping.business_key = loose.business_key;

Here old_mapping is a table with (old_id, business_key) pairs from before. The key is that you match rows by business_key (or some other immutable attribute) to recover the old IDs.

However, you can only do this if you have complete and correct pre-maintenance data. If you just have the current row count or some random table copy, you can’t deduce the old rowid. (For example, knowing there are three rows A,B,C now doesn’t tell you which rowid was which, because VACUUM can reorder them.) So you must freeze or export the mapping before the change.

If there is no external evidence (and no backup to compare), you must treat the mapping as lost. In that case you cannot safely remap the saved handles; you’d have to hold off on using them. This is akin to deciding that the maintenance has invalidated some application state.

In short: recover references by rebuilding them from a trusted prior map, not by inferring from the new DB alone. Do not attempt to recover the old rowids from only the VACUUMed file. If you do have the mapping, the vacuumed rows’ content can be matched to it to repopulate the id. This is not a general backup exercise (like WAL replay); it's a specific reference fix.

We included this “migration before/after” scenario in the lab to show both that the explicit mapping works and that if the original mapping is still valid, it should be used. The script’s migrated table is an example of preserving references. If the loose table had been updated without any mapping to the old IDs, the “saved handle reconciliation” check would fail for loose and we would then either migrate or halt.

Make the acceptance gate reject misleading evidence

Based on these experiments, how do we decide if the maintenance is “acceptable” or not? We propose separate checks and verdicts, rather than a single pass/fail. Each category must meet its criteria:

Fixture validity: The test itself must have run correctly (e.g. correct schema, exactly 3 rows inserted, scripts completed). If we didn’t see the expected baseline, or integrity_check failed, we cannot trust the results. That means HOLD: do not assume the contract passed.

Structural consistency: For every table considered, PRAGMA integrity_check should be ok and row counts should match expected. If not, the database is corrupt or content is missing; that is an automatic failure of the maintenance step (repair data first).

Business content preserved: The set of (business_key, payload) pairs must be the same as before. (Our keys were designed unique ASCII, so we just compare sorted sets.) If any row is lost or changed, that also fails the basic data preservation.

Saved-handle identity: Each saved reference in our manifest must match an actual row in the new table. In a table with an explicit PK, this should hold for all saved IDs. In a loose-rowid table, it might fail (as in our case).

Now, because our test includes both a “control” (stable, migrated) and a “treatment” (loose) scenario, we interpret them independently. The loose table’s failure to preserve identity is the counterexample. We do not average it with the controls. If the loose table scenario fails, the experiment is valid in the sense of illustrating the risk. We would mark “loose table identity = FAIL” and “stable, migrated identity = PASS” in our report.

Our script’s final result was “PASS” because we expected loose to fail (we called it a counterexample). But in a decision matrix, “PASS” means the experiment ran as designed and indeed found the loose table unsuitable. We would then draw these lessons:

For acceptance of a maintenance on a table with hidden rowids, the verdict is HOLD/REBUILD because the identity mapping cannot be trusted.

For a table with explicit ID, the verdict is OK (for this operation) because identity was preserved.

For migration candidates prepared in advance, they too are OK if we found no mismatches.

The acceptance of a future migration (i.e. to switch over to a stable table) is a separate decision. We only approve the evidence as valid or not. If stable/migrated pass, you have a tested candidate. If loose fails, you’ve proven the need to either migrate or rebuild references.

Distinguish a passing experiment from an approved application change

It is important to clarify: a successful experiment doesn’t mean “loose table is now fixed.” It means we have confirmed behavior. In our example run, the loose table failed the identity check. That is good, because it is the phenomenon we wanted to see (the counterexample). So the experiment result is “PASS” in the sense of “the vacuum did indeed invalidate the hidden-rowid mapping”. That does not mean we accept using hidden-rowid tables with impunity. On the contrary, it means: we see the problem clearly and should not rely on that table.

If the loose table had not changed (i.e. if our environment preserved rowids), giving “COUNTEREXAMPLE_NOT_REPRODUCED”, we would not flip the logic and say “loose is approved.” Instead, we would hold the result, keep the evidence, and re-run or inspect the environment. We would not rewrite our expected manifests to match whatever happened. In essence, any gap between expectation and reality means HOLD. Only when the evidence exactly matches either the good or bad scenarios do we move on.

Therefore, after the experiment, an approved application change would be: “we now commit to using the stable table (or the migrated copy) going forward, because it passed identity tests.” We do not approve continuing with the loose table. Our decision flow will reflect that: we basically have a separation between “this experiment setup was OK” vs “we change the schema.”

If any part of the evidence is missing or an error occurred (for example, integrity_check failed, or fewer than 3 rows), the correct action is HOLD maintenance until resolved. We don’t allow “silent passes.” Every issue uncovered should lead to a hold or fix in the process. The script’s final require statements ensure that any unexpected situation raises an exception and is flagged in the report as “FAIL,” rather than being glossed over. An environment mismatch (like “loose id=2 is now B”) is not an accidental bug; it is our intended result and is indicated by identity_matches=false.

Assign ownership for maintenance and saved references

Ultimately, deciding on SQLite maintenance and handling saved IDs involves both application and database owners. We recommend clear ownership and artifacts for each path:

The application owner (or product owner) is responsible for the meaning of those rowids and business keys in the feature. The DBA or maintainer is responsible for the schema, the VACUUM operation, and running this test. An independent reviewer (e.g. team lead or auditor) can check that the evidence is complete before sign-off. For each decision path, we store necessary artifacts: e.g. the JSON report from this test, the migration script, or the mapping file.

In the broader context of database administration skills and projects, this task falls under the DBA’s responsibility for backup and maintenance verification. But it also intersects with development, since the references come from the application. Therefore, cross-functional agreement is needed. If a hidden-rowid table fails this test, the DBA must escalate to developers to consider migrating or adjusting client logic. The matrix above delineates who does what and what proofs they need.

Build database maintenance judgment through practical projects

The artifact from this exercise (a schema comparison, an independent mapping manifest, the failing lookup evidence, and a verified migration plan) is exactly the kind of hands-on project a Database Administrator should be able to produce. It demonstrates not only SQL and maintenance skills, but also how to document a decision and evidence. If you enjoy building such concrete, testable proofs for operational changes, consider enriching your skillset through systematic training. The Refonte Database Administrator Essentials program (3 months, part-time) covers the fundamentals of SQL, backup/recovery, monitoring, security, and more. It emphasizes practical projects and mentor guidance, culminating in a training certificate.

Understanding subtle behaviors like hidden SQLite rowids is part of mastering database reliability. When planning maintenance, always gather independent evidence (schemas, mappings, and tests) before declaring success. This approach to careful verification is a valuable part of the DBA’s toolkit.

Explore the Database Administrator Essentials program to build a solid foundation in database theory and practice, and learn to document and review maintenance decisions rigorously.