Tuning temp_store for Analytics Queries #

An edge device runs a nightly rollup — GROUP BY sensor, ORDER BY ts, a CREATE INDEX over a wide log table, a join that outgrows the query planner’s in-place strategy — and each of those operations materializes a temporary b-tree. By default SQLite may write that temporary structure to a file on disk, and on a slow-flash target the write amplification is brutal: the device thrashes its storage, the rollup takes minutes instead of seconds, and the flash wears for data that is discarded the moment the query returns. Setting PRAGMA temp_store=MEMORY keeps those transient b-trees in RAM, which on a query-heavy edge node is often the single largest speedup available — but it trades disk pressure for memory pressure, and a large enough sort can now trigger the OOM-killer instead of a slow query. This page tunes that trade precisely, as one case within the PRAGMA Optimization Guide, part of the broader WAL Optimization & Concurrency Tuning discipline. The goal is to move temp b-trees to memory only where they fit, with a bounded cache and a fallback path, not to blindly flip the switch and hope the biggest report of the month does not arrive.

Diagnosis #

Confirm two things before changing temp_store: that your analytics queries actually spill temporary b-trees, and that the spill is going to slow storage. If a query never materializes a temp b-tree, temp_store changes nothing for it.

The authoritative signal comes from the planner. EXPLAIN QUERY PLAN names every step that needs a transient structure — look for USE TEMP B-TREE, which appears for sorts and grouping the planner cannot satisfy from an index:

-- "USE TEMP B-TREE FOR ORDER BY" / "... FOR GROUP BY" == this query materializes a temp b-tree
EXPLAIN QUERY PLAN
SELECT sensor, avg(value)
FROM readings
WHERE ts >= :since
GROUP BY sensor
ORDER BY avg(value) DESC;

Every USE TEMP B-TREE line is a candidate to keep in RAM. A query whose plan shows only USING INDEX for its ordering does not spill and will not benefit.

Next, read where those temporaries land and confirm the current mode:

PRAGMA temp_store;       -- 0=DEFAULT (compile-time choice), 1=FILE (spills to disk), 2=MEMORY (RAM)
PRAGMA temp_store_directory;  -- deprecated but still read; empty = use SQLITE_TMPDIR / default temp dir
PRAGMA cache_size;       -- negative = KiB; the in-memory temp b-tree competes for this same budget

Then watch the device from outside during a representative report. A temp-file spill shows as new files appearing in the temp directory and a burst of writes unrelated to your row output:

# Watch the temp dir SQLite is using (SQLITE_TMPDIR, else /tmp or the db dir) while a report runs.
# Transient files appearing here + a write spike in iostat == temp b-trees spilling to disk.
ls -la "${SQLITE_TMPDIR:-/tmp}" ; iostat -x 2

If the plan shows USE TEMP B-TREE, temp_store is FILE or a DEFAULT that resolves to file, and you can see transient files plus write bursts on slow flash while the report runs, this is exactly the problem temp_store=MEMORY addresses — provided the temporaries fit in RAM.

Solution #

Set temp_store=MEMORY for the analytics connection, but pair it with a bounded cache_size and a defined SQLITE_TMPDIR fallback, so that if a query does spill despite the setting it lands on the fastest disk you have rather than an unknown default. Apply the settings per connection, read them back, and — because temp_store shares RAM with the page cache — size the two together:

import os
import sqlite3
import logging

logger = logging.getLogger("sqlite_temp_tuner")

TEMP_STORE_MEMORY = 2   # PRAGMA temp_store values: 0=DEFAULT, 1=FILE, 2=MEMORY


def init_analytics_connection(db_path: str, temp_cache_mib: int = 24,
                              timeout: float = 30.0) -> sqlite3.Connection:
    # Point any *fallback* temp files at the fastest / least-worn path BEFORE opening,
    # in case a sort exceeds the in-memory budget and SQLite must spill after all.
    os.environ.setdefault("SQLITE_TMPDIR", "/run/sqlite-tmp")   # tmpfs if available; read at open time

    conn = sqlite3.connect(db_path, timeout=timeout, isolation_level=None)
    try:
        conn.execute("PRAGMA journal_mode=WAL;")            # analytics reads run alongside writers
        conn.execute("PRAGMA temp_store=2;")                # 2=MEMORY: keep transient b-trees in RAM
        # Bound the cache so temp b-trees + page cache share a known ceiling (negative = KiB).
        conn.execute(f"PRAGMA cache_size=-{temp_cache_mib * 1024};")  # e.g. -24576 == ~24 MiB budget

        # Read every PRAGMA back: a compile-time SQLITE_TEMP_STORE can override the runtime request,
        # pinning it to FILE or MEMORY regardless of what we set.
        ts = conn.execute("PRAGMA temp_store;").fetchone()[0]
        cs = conn.execute("PRAGMA cache_size;").fetchone()[0]
        if ts != TEMP_STORE_MEMORY:
            logger.warning("temp_store not MEMORY: applied=%d (SQLITE_TEMP_STORE override?)", ts)
        if cs >= 0:
            logger.warning("cache_size is a page count (%d), not a KiB budget", cs)
        return conn
    except sqlite3.Error:
        conn.close()
        logger.exception("analytics tuning failed for %s", db_path)
        raise

The reasoning behind each line: temp_store=2 is the value that forces temporary b-trees into memory (1 is explicit file, 0 defers to the compile-time default). SQLITE_TMPDIR is set before connect() because SQLite reads it at open time, and pointing it at a tmpfs mount means even a fallback spill stays in RAM-backed storage rather than wearing flash. The bounded cache_size matters because temp_store=MEMORY temporaries draw from the same process memory as the page cache — leaving the cache unbounded lets a big sort and a big cache jointly overrun the device. This is the analytics-specialized counterpart to the general crash-safety settings in Configuring the synchronous PRAGMA for Crash Safety; durability and temp placement are independent knobs, and both are applied per connection.

Verification #

Confirm the setting took, then confirm the report no longer spills to disk.

First, assert the runtime PRAGMA was not overridden by a compile-time default — the single most common surprise here:

conn = init_analytics_connection("/var/lib/telemetry/sensors.db")
applied = conn.execute("PRAGMA temp_store;").fetchone()[0]
assert applied == TEMP_STORE_MEMORY, f"temp_store is {applied}, not MEMORY -- SQLITE_TEMP_STORE override"
assert conn.execute("PRAGMA cache_size;").fetchone()[0] < 0, "cache_size must be a bounded KiB budget"

If this fails with applied stuck at 1, the binary was compiled with SQLITE_TEMP_STORE=1 (always file) and the PRAGMA cannot move it — no runtime setting will help until the binary is rebuilt.

Second, run the real report and confirm no temp files appear. Snapshot the temp directory before and after; an empty diff proves the temporaries stayed in RAM:

import os

tmpdir = os.environ.get("SQLITE_TMPDIR", "/tmp")
before = set(os.listdir(tmpdir))
conn.execute(
    "SELECT sensor, avg(value) FROM readings "
    "WHERE ts >= ? GROUP BY sensor ORDER BY avg(value) DESC;", (since,)
).fetchall()
after = set(os.listdir(tmpdir))
assert not (after - before), f"query spilled temp files to disk despite temp_store=MEMORY: {after - before}"

If the diff is empty and the report time drops sharply, the temporaries are staying in memory. If files still appear, either the compile-time default won (see the first check) or the sort exceeded the memory budget and spilled anyway — which is the failure the next section covers.

Failure Modes & Gotchas #

temp_store=MEMORY turns a big sort into an OOM instead of a slow query. The whole point of the setting is that temporary b-trees live in RAM — so a GROUP BY or ORDER BY over a very large result now allocates that entire structure in memory, and on a constrained device the OOM-killer reaps the process rather than the query merely running slowly. temp_store=MEMORY is safe only when you can bound the temporaries: cap cache_size, keep an eye on the widest sort your reports produce, and where a query can legitimately exceed the budget, leave that specific report on temp_store=FILE with SQLITE_TMPDIR pointed at fast storage. Trading disk thrash for a crash is not a win.

A compile-time SQLITE_TEMP_STORE overrides the PRAGMA entirely. SQLite’s build can pin temp storage: compiled with SQLITE_TEMP_STORE=1 it always uses files and ignores PRAGMA temp_store=MEMORY; compiled with 3 it always uses memory and ignores a request to spill to file. The runtime PRAGMA only has effect for the intermediate build settings. This is why the read-back assertion is mandatory — if PRAGMA temp_store does not return the value you set, no amount of retrying will change it, and you must either rebuild the binary or design the report around the fixed behaviour.

temp_store is per-connection, so a pool or a fresh handle silently reverts. The setting applies only to the connection that ran the PRAGMA; a pooled handle that skipped the initializer, or a new connection opened for one report, falls back to the default and spills to disk without warning. Route every analytics connection through the same factory, reapply the PRAGMA on each handle, and read it back — the same discipline the PRAGMA Optimization Guide applies to every per-connection setting. Where the analytics workload also leans on the file mapping, coordinate the memory budget with Memory-Mapped I/O Configuration so the temp b-trees, the page cache, and the mapping do not each claim RAM independently.

Related Pages #

• PRAGMA Optimization Guide — the parent guide: the full per-connection PRAGMA baseline that temp_store slots into.
• Configuring the synchronous PRAGMA for Crash Safety — the independent durability knob to set alongside temp placement.
• Memory-Mapped I/O Configuration — budget the page cache, the mapping, and in-memory temporaries together so none overruns RAM.