SQLite Temporary Database Behavior: Storage, Cache, and Journal Mode Configuration

Temporary Database Storage Rules and Configuration Interactions

Issue Overview

The core challenges revolve around understanding how SQLite manages temporary objects, including implicit temporary databases (created during query execution for sorting/grouping) and explicit temporary databases (created via CREATE TEMP TABLE). Key areas of confusion include:

1. TEMP_STORE Configuration Scope: Whether the TEMP_STORE setting applies to both implicit and explicit temporary databases.
2. TEMP Database Cache Behavior: The implications of the default cache_size pragma value (0 pages) for the explicit TEMP database.
3. In-Memory Database Cache Configuration: How cache_size interacts with :memory: databases.
4. Implicit Temporary Database Page Size: Whether the page size for implicit temporary databases uses the compile-time default.
5. Memory-Only TEMP_STORE and Page Cache Growth: Whether implicit temporary databases ignore memory constraints when TEMP_STORE is set to memory.
6. TEMP Database Journal Mode Discrepancy: Why the observed journal mode (DELETE) conflicts with documented behavior (PERSIST).

These issues stem from SQLite’s layered configuration system, where compile-time defaults, runtime pragmas, and environment-specific settings interact in non-intuitive ways. Misinterpretations arise from conflating temporary file storage policies with database-specific pragmas, as well as version-specific changes in default behaviors.

Temporary Storage Mechanics and Configuration Conflicts

TEMP_STORE and Explicit TEMP Database Behavior

The TEMP_STORE compile-time directive and runtime pragma determine whether temporary files are stored in memory or on disk. However, this setting has different implications for implicit and explicit temporary databases. Implicit temporary databases (e.g., those created for ORDER BY or GROUP BY operations) respect TEMP_STORE directly: if set to 2 (memory), they remain in RAM unless memory pressure forces spilling to disk. Explicit temporary databases (those in the TEMP schema) follow a different rule set. The TEMP database is treated as a regular database with a persistent schema but transient data. Its storage location depends on the operating system’s handling of temporary files, not solely on TEMP_STORE. For example, on Unix-like systems, the TEMP database is stored in /tmp by default, while on Windows, it uses the user’s temporary directory.

Cache Size Pragmas and Spill Behavior

The cache_size pragma for the TEMP database defaults to 0, which does not mean "no cache." Instead, SQLite interprets a cache_size of 0 as a directive to use the default cache size (typically 2000 pages). This misunderstanding arises from ambiguous documentation phrasing. When the TEMP database’s page cache exceeds the cache_size limit, SQLite spills pages to disk, but only if the underlying storage allows it. If TEMP_STORE is set to memory (value 2), the TEMP database will ignore cache_size and keep all pages in RAM, risking out-of-memory errors.

In-Memory Databases and Cache Size

For :memory: databases, the cache_size pragma has no effect because the entire database resides in memory. SQLite does not partition memory for :memory: databases into page caches; instead, it allocates memory dynamically as needed. Setting cache_size on a :memory: database is silently ignored, which can lead to confusion if users assume it controls memory allocation thresholds.

Implicit Temporary Database Page Size

Implicit temporary databases inherit the page size of the primary database connection. For example, if the main database uses a 4KB page size, implicit temporary databases will also use 4KB pages. This is determined at runtime, not compile time. The confusion arises from SQLite’s initialization process: when no databases are attached, the first database connection establishes the default page size, which is then reused for implicit temporary databases.

TEMP_STORE=2 and Memory Allocation Failures

When TEMP_STORE is set to 2 (memory-only), SQLite attempts to keep all temporary pages in RAM. However, the page cache does not grow indefinitely. SQLite uses soft heap limits (configured via sqlite3_soft_heap_limit64()) to constrain memory usage. If the heap limit is reached, SQLite returns SQLITE_FULL errors, even for temporary databases. Without a heap limit, memory allocation continues until the operating system denies further allocations, which typically results in an application crash.

Journal Mode for TEMP Database

SQLite’s documentation historically states that the TEMP database uses journal_mode=PERSIST. However, starting in version 3.34.0 (2020-12-01), the default journal mode for TEMP databases was changed to DELETE to improve performance. This discrepancy indicates outdated documentation. Users can override this by manually setting PRAGMA temp.journal_mode=PERSIST, but this is not recommended for most workloads.

Resolving Configuration Conflicts and Misinterpretations

Confirming TEMP_STORE Impact on Explicit TEMP Databases

To determine whether TEMP_STORE affects the TEMP database:

1. Set TEMP_STORE to 2 (memory) at compile time or via PRAGMA temp_store=MEMORY.
2. Create a temporary table: CREATE TEMP TABLE test (id INTEGER);.
3. Insert data until the TEMP database exceeds the page cache.
4. Check if temporary files are created on disk. On Linux, use lsof -p <PID> to list open files. If no files appear, the TEMP database is in memory.

If files are still created, the TEMP database is not fully controlled by TEMP_STORE. Instead, it relies on the OS’s temporary file handling.

Adjusting Cache Size for TEMP Databases

To prevent premature spilling:

1. Set PRAGMA temp.cache_size=2000; to allocate a 2000-page cache.
2. Monitor spills using sqlite3_status(SQLITE_STATUS_TEMP_STORE_SIZE, ...).
3. If spills occur despite a large cache_size, check TEMP_STORE settings—if set to 2, spills should not occur unless memory limits are hit.

Validating In-Memory Database Cache Behavior

For :memory: databases:

1. Execute PRAGMA cache_size=10000; on a :memory: database.
2. Use PRAGMA cache_size; to verify the setting is ignored (returns 0 or a placeholder value).
3. Insert large datasets and monitor memory usage via sqlite3_memory_used(). The absence of cache partitioning will show linear memory growth.

Testing Implicit Temporary Database Page Size

To confirm page size inheritance:

1. Create a primary database with a non-default page size: sqlite3 --page-size 8192 main.db.
2. Execute a query requiring an implicit temporary database (e.g., SELECT * FROM large_table ORDER BY random();).
3. Use PRAGMA main.page_size; to confirm the primary database’s page size.
4. Check the temporary database’s page size via debugging tools (e.g., SQLite’s sqlite3_db_status with SQLITE_DBSTATUS_CACHE_USED).

Diagnosing Memory-Only TEMP_STORE Failures

To simulate memory exhaustion:

1. Set PRAGMA temp_store=MEMORY; and disable heap limits.
2. Generate a large temporary dataset (e.g., recursive CTEs).
3. Observe error codes: SQLITE_FULL indicates heap limits, while crashes suggest OS-level memory denial.

Updating Journal Mode for TEMP Databases

To reconcile documentation discrepancies:

1. Check SQLite’s version-specific changelog for journal mode defaults.
2. Execute PRAGMA temp.journal_mode; on versions ≥3.34.0 to verify DELETE is the default.
3. Submit documentation feedback via SQLite’s GitHub repository to clarify the change.

By systematically addressing each configuration layer (compile-time settings, runtime pragmas, and OS-specific behaviors), users can resolve ambiguities in SQLite’s temporary database management.