TopolearnKnowing the on-disk layout explains file size, fragmentation and many performance characteristics.
A SQLite database file is an array of fixed-size pages (4096 bytes by default, any power of two from 512 to 65536, set with PRAGMA page_size before the database is populated or followed by VACUUM). Page 1 starts with a 100-byte header — the magic string SQLite format 3, page size, file change counter, user_version, schema cookie and more.
Every table is a table B-tree keyed by rowid: interior pages hold keys and child pointers; leaf pages hold the full rows. Every index (and every WITHOUT ROWID table) is an index B-tree whose keys are the indexed values plus the rowid. The sqlite_schema table on page 1 records each object's SQL and root page. Rows too large for a page spill into chains of overflow pages.
Records are stored in a compact format: a header of type codes followed by values. Small integers take one byte or even zero (the constants 0 and 1 live in the header), which is why SQLite files are compact.
When you delete data, freed pages go onto the freelist and are reused, but the file doesn't shrink. `VACUUM` rebuilds the database into a fresh file, removing free pages and defragmenting B-trees. It needs free disk space up to twice the database size and an exclusive lock. PRAGMA auto_vacuum = INCREMENTAL plus PRAGMA incremental_vacuum returns free pages without a full rebuild (it must be set before tables are created, or followed by a VACUUM). VACUUM INTO 'copy.db' writes a compacted copy and is also a handy backup.
Example
import sqlite3, os, tempfile
path = os.path.join(tempfile.mkdtemp(), "big.db")
con = sqlite3.connect(path)
con.execute("CREATE TABLE blob (id INTEGER PRIMARY KEY, data TEXT)")
with con:
con.executemany("INSERT INTO blob (data) VALUES (?)", (("x" * 500,) for _ in range(20000)))
pragma = lambda p: con.execute(f"PRAGMA {p}").fetchone()[0]
print(pragma("page_size"), pragma("page_count"), pragma("freelist_count")) # 4096 2508 0
with con:
con.execute("DELETE FROM blob WHERE id <= 10000")
print(pragma("page_count"), pragma("freelist_count")) # 2508 1253: same size, pages on the freelist
print(os.path.getsize(path) // 1024, "KiB") # file did not shrink
con.execute("VACUUM")
print(pragma("page_count"), pragma("freelist_count")) # 1255 0
print(open(path, "rb").read(16)) # b'SQLite format 3\x00'Remember
- The file is an array of pages; tables and indexes are B-trees
- Deleted space goes to the freelist; the file doesn't shrink
- VACUUM rebuilds and compacts; VACUUM INTO makes a compact copy
Watch out for
- Running VACUUM on a large database without enough free disk space for a full copy.