LMDB vs SQLite for Spatial Join Staging

Within the Local Spatial Processing Patterns guide, this page answers a question the parent guide on spatial joins in constrained environments leaves open: once reference features are off the wire and need somewhere to live between gateway restarts, what actually holds them — a hand-rolled in-memory grid rebuilt from a streaming parser every boot, or an embedded database that persists the grid itself? The parent guide’s static index is fast because it never touches disk during a lookup; the question here is what happens before that index exists in RAM, and what a gateway does across a power cycle if it cannot afford to re-parse a multi-megabyte GeoJSON file every time it restarts. Two embedded stores answer that differently: LMDB, a memory-mapped B+tree with no server process, and SQLite, a full relational engine that happens to ship as a single file and a stdlib import.

Both are legitimate choices and both are wrong for the other one’s use case. The rest of this page is about picking correctly rather than defaulting to whichever one is more familiar.

Staging engine selection rationale #

The core distinction is architectural, not a feature checklist. LMDB is a memory-mapped copy-on-write B+tree: reads are a pointer dereference into pages the kernel already paged in, there is no query planner, no SQL parsing, and no write-ahead log — a reader transaction is a lock-free snapshot of the mapped memory at the instant it opened. SQLite is a full SQL engine with its own page cache, a journal or WAL for durability, and a cost-based (if simple) optimizer; every read, however small, goes through statement preparation and page-cache bookkeeping even for a single-row lookup by primary key.

That difference shows up in three places that matter for gateway staging:

1. Read amplification. LMDB’s mdb_get() touches only the B+tree pages on the path to the target key — typically two to four page reads for a modestly sized index, and zero once those pages are resident in the OS page cache. SQLite’s read path, even for an indexed lookup, pays for statement preparation and its own buffer-pool bookkeeping on top of the underlying B-tree traversal; for single-key point lookups at high frequency, that overhead is the dominant cost, not the page I/O.
2. Write cost and durability model. LMDB commits are copy-on-write: a writer transaction never mutates pages readers might be looking at, so writers and readers never block each other, but every commit that touches even one key can dirty and rewrite whole B+tree pages up to the root, which is expensive for many small, scattered writes. SQLite’s WAL mode appends committed pages to a separate log and lets readers keep reading the old pages until a checkpoint folds the WAL back into the main file, which amortizes small scattered writes far better than LMDB’s page-copy model.
3. Crash safety. Both are crash-safe by design, but differently. LMDB relies on the fact that the last committed root pointer is atomic and the copy-on-write pages before it are never touched again, so a crash mid-write simply loses the incomplete transaction with no corruption possible. SQLite’s WAL mode is safe as configured — PRAGMA synchronous=NORMAL with WAL is safe against application crashes but can lose the last few committed transactions on an actual power loss unless synchronous=FULL is set, which costs an fsync per commit.

Neither engine is faster in the abstract — the workload shape decides, and the two shapes rarely overlap.

Staging the reference layer: two implementations #

Both examples stage the same data: reference feature envelopes keyed by a grid cell, exactly the shape that feeds the build_grid step described in the parent guide, except here it survives a restart without re-parsing the source GeoJSON.

LMDB staging reserves its address space up front and treats the store as an immutable snapshot once built:

# lmdb_stage.py — persist grid-cell -> feature-id buckets in a memory-mapped store.
import lmdb
import struct

MAP_SIZE = 64 * 1024 * 1024  # reserve 64 MB of address space; cheap, not allocated eagerly

def open_env(path: str, readonly: bool):
    # map_size is a ceiling on virtual address space, not committed RAM; the OS
    # pages in only what is touched. Oversizing it costs nothing but disk headroom.
    return lmdb.open(path, map_size=MAP_SIZE, readonly=readonly,
                      lock=not readonly, max_dbs=1)

def stage_cell(env, cell_key: tuple[int, int], feature_ids: list[int]):
    # One write transaction per rebuild pass, not per cell -- LMDB commits are
    # page-copy operations, so batching many puts into one txn is far cheaper
    # than committing after each individual cell.
    packed = struct.pack(f"<{len(feature_ids)}i", *feature_ids)
    with env.begin(write=True) as txn:
        txn.put(struct.pack("<ii", *cell_key), packed)

def read_cell(env, cell_key: tuple[int, int]) -> tuple[int, ...]:
    # Read-only transactions are lock-free snapshots; safe to open one per
    # lookup on the hot path with negligible overhead once pages are resident.
    with env.begin() as txn:
        raw = txn.get(struct.pack("<ii", *cell_key))
        if raw is None:
            return ()
        return struct.unpack(f"<{len(raw)//4}i", raw)

SQLite staging looks almost identical at the call site but carries a real query engine underneath, which pays off the moment the staging data needs to answer more than “give me this exact key”:

# sqlite_stage.py — same bucket data, staged in SQLite with WAL for durability.
import sqlite3

def open_db(path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(path, isolation_level=None)  # autocommit; explicit BEGIN below
    conn.execute("PRAGMA journal_mode=WAL")        # readers never block the writer
    conn.execute("PRAGMA synchronous=NORMAL")      # safe vs crash, not vs power loss
    conn.execute("PRAGMA mmap_size=33554432")      # let SQLite mmap up to 32 MB of pages
    conn.execute("PRAGMA cache_size=-4000")        # ~4 MB page cache, negative = KB units
    conn.execute("""
        CREATE TABLE IF NOT EXISTS grid_cell (
            cx INTEGER NOT NULL, cy INTEGER NOT NULL,
            feature_id INTEGER NOT NULL,
            PRIMARY KEY (cx, cy, feature_id)
        ) WITHOUT ROWID
    """)
    return conn

def stage_cells(conn: sqlite3.Connection, rows: list[tuple[int, int, int]]):
    conn.execute("BEGIN")
    conn.executemany(
        "INSERT OR IGNORE INTO grid_cell (cx, cy, feature_id) VALUES (?, ?, ?)", rows)
    conn.execute("COMMIT")

def read_cell(conn: sqlite3.Connection, cx: int, cy: int) -> list[int]:
    cur = conn.execute("SELECT feature_id FROM grid_cell WHERE cx = ? AND cy = ?", (cx, cy))
    return [row[0] for row in cur.fetchall()]

WITHOUT ROWID matters here: it stores the table clustered by the primary key rather than adding a hidden rowid and a separate index, which for a key that is already the natural lookup path removes one level of indirection on every read. PRAGMA mmap_size is SQLite’s own nod toward LMDB’s model — it lets the engine map database pages directly rather than copying them through its page cache, closing part of the read-amplification gap for pages that are actually mapped, though statement preparation overhead remains.

Constraint validation table #

When each one wins #

LMDB wins when the staged data is read far more often than it is written and the read pattern is point lookups by a known key — exactly the grid-cell probe in the parent guide’s join. Its zero-copy reads and lock-free MVCC readers make it the right choice for the hot path a join runs thousands of times per second. It loses when the staging data needs ad-hoc queries — “which features overlap this bounding box,” “how many cells does this feature touch” — because LMDB has no query language; every such question has to be hand-coded against the B+tree’s key ordering.

SQLite wins when staging needs exactly that flexibility: a field diagnostic tool that lets an engineer run an ad-hoc SELECT over the reference layer, a staging pipeline that also needs transactional updates to multiple related tables, or a dataset small enough that per-query overhead is immaterial next to the convenience of SQL. It loses when the join’s hot path is calling into it directly at high frequency — the same query executed thousands of times per second pays its preparation cost every time unless the caller manages a prepared-statement cache explicitly, and even then it will not match LMDB’s near-zero read floor.

A workable middle path, and the one most gateway deployments land on: stage with whichever engine matches the build workload — SQLite if the ingestion pipeline benefits from SQL, LMDB if it does not — then materialize the final grid-cell buckets into LMDB specifically for the runtime join, so the hot path never pays SQLite’s per-query tax regardless of how the data arrived. Building the reference envelopes from a streamed parser rather than json.load matters equally for both engines, and that streaming step is the one detailed in reducing RAM usage for GeoJSON parsing on Raspberry Pi.

Gotchas and edge cases #

• LMDB’s map_size cannot grow while a reader has the environment open on some platforms. Resizing requires all readers to close first; size it generously at open time (tens of MB is nearly free in address-space terms on a 32-bit-clean gateway) rather than tuning it tightly.
• SQLite’s synchronous=NORMAL is not power-loss safe by itself. It protects against application and OS crashes but can lose the last committed WAL frames on an actual power cut — acceptable for a rebuildable join cache, not for data with no other source of truth.
• Multiple LMDB writers deadlock, not corrupt. LMDB allows only one writer transaction at a time; a second writer blocks until the first commits or aborts. Design the staging rebuild as a single writer process, never a pool of workers all trying to write the same environment.
• SQLite WAL leaves a -wal and -shm file alongside the main database file. A backup or field-copy of just the .db file without those siblings can silently miss recent commits; always checkpoint (PRAGMA wal_checkpoint(TRUNCATE)) before copying, or copy all three files together.
• mmap paging on either engine is at the mercy of the OS page cache, which a gateway with 256 MB total RAM may evict aggressively under memory pressure from other services; a cold read after eviction pays real disk I/O even though the code path looks identical to a warm one.

Related #

• Spatial Joins in Constrained Environments — the streaming grid-and-predicate join this staging choice feeds.
• Reducing RAM Usage for GeoJSON Parsing on Raspberry Pi — the streaming parse step that produces the envelopes staged here.
• Geohash Bucketing for Point-in-Zone Joins — an alternative bucket-key scheme that can sit on top of either staging engine.