SQLite PRAGMA cache_size Discrepancies and Documentation Clarifications

Issue Overview: Conflicting Cache Size Values and Documentation Ambiguities

The SQLite PRAGMA schema.cache_size directive governs the maximum number of database pages held in memory by the pager cache. Its behavior hinges on whether the assigned value is positive (page count) or negative (memory budget in kibibytes). However, discrepancies arise between documented defaults, observed runtime values, and interpretations of page size dependencies. A user reported that the default cache_size returned 2000 instead of the documented -2000, with further confusion about whether negative values reference a legacy page size (1024 bytes) or the current page size (often 4096 bytes). This raises three critical questions:

1. Why does the default cache_size differ between SQLite builds?
   The observed 2000 vs. -2000 suggests compile-time customization or page size adjustments.

2. How do negative cache_size values interact with page size changes?
   The documentation states negative values compute pages as abs(N)*1024 bytes, but modern page sizes (post-3.12.0) default to 4096 bytes, creating ambiguity.

3. Are the cache_size pragma syntax examples accurate?
   The distinction between PRAGMA cache_size = pages (positive) and PRAGMA cache_size = -kibibytes (negative) requires validation against SQLite’s internal logic.

Possible Causes: Compilation Settings, Page Size Evolution, and Unit Misinterpretations

Cause 1: Nonstandard Compile-Time Defaults

SQLite allows custom builds to override default pragma values. If a distribution (e.g., macOS’s system sqlite3) sets -DSQLITE_DEFAULT_CACHE_SIZE=2000 during compilation, the default cache_size becomes 2000 instead of -2000. This explains why the user observed 2000 on macOS but -2000 in standard builds. Compile-time overrides bypass the documented behavior, prioritizing distribution-specific optimizations.

Cause 2: Legacy vs. Current Page Size Assumptions

Prior to SQLite 3.12.0 (2016), the default page size was 1024 bytes. Negative cache_size values were designed to approximate memory usage using this legacy size: abs(N)*1024 bytes. However, the default page size changed to 4096 bytes in 3.12.0, while the documentation retained the 1024 multiplier. This creates confusion: does PRAGMA cache_size = -2000 allocate 2000*1024 bytes (legacy) or 2000*4096 bytes (current)? The documentation’s reference to 1024 is outdated but intentional—it preserves backward compatibility for applications relying on kibibyte calculations irrespective of page size.

Cause 3: Misunderstanding Negative Value Semantics

Negative cache_size values specify a memory budget in kibibytes (1024 bytes), not page-count equivalents. For example, PRAGMA cache_size = -2000 configures the cache to use approximately 2000*1024 = 2,048,000 bytes, divided by the current page size to determine the page count. If the page size is 4096 bytes, this becomes 2,048,000 / 4096 = 500 pages. Thus, negative values abstract away page size specifics, while positive values directly set the page count. Misinterpreting negative values as page-size-agnostic leads to incorrect memory estimates.

Troubleshooting Steps, Solutions & Fixes: Aligning Practice with Documentation

Step 1: Verify SQLite Build Configuration

Command:

sqlite3 :memory: 'PRAGMA compile_options;'

Analysis:
Check for SQLITE_DEFAULT_CACHE_SIZE in the output. If present, the default cache_size was overridden at compile time. For example, macOS’s system SQLite might set this to 2000, altering the default from -2000. Recompile SQLite without this option to restore documented behavior.

Step 2: Correlate Page Size and Cache Size

Commands:

PRAGMA page_size;         -- Check current page size (e.g., 4096)
PRAGMA cache_size = -2000; 
PRAGMA cache_size;        -- Returns 2000*1024 / page_size

Example Calculation:
If page_size is 4096, -2000 sets cache_size to (2000 * 1024) / 4096 = 500. Executing PRAGMA cache_size; returns 500, not -2000. To achieve a 2000-page cache with a 4096-byte page size, use PRAGMA cache_size = 2000.

Step 3: Adjust Cache Size Dynamically

Use Case:
An application requiring a 100MB cache with a 4096-byte page size:

PRAGMA cache_size = -102400;  -- 102400 KiB = 100 MiB
PRAGMA cache_size;            -- Returns 102400*1024 / 4096 = 25600 pages

Recommendation:
Prefer negative values for memory budgeting, as they automatically adjust to page size changes (e.g., VACUUM resets page size). Positive values fix the page count, risking memory overallocation if the page size increases.

Step 4: Update Documentation Understanding

Clarifications:

• The 1024 multiplier in cache_size documentation refers to kibibytes, not the legacy page size. Negative values always use 1024 bytes/KiB, regardless of page_size.
• The default -2000 suggests 2000 KiB ≈ 2 MB for the pager cache, which becomes 2000*1024 / page_size pages.

Revised Documentation Proposal:

If the argument N is positive, the cache holds exactly N pages. If N is negative, the cache size is adjusted to use approximately abs(N)*1024 bytes of memory, divided by the current page size. The default suggested cache size is -2000 (2000 KiB).

Step 5: Handle Cross-Version Page Size Defaults

Scenario:
Migrating a pre-3.12.0 database (1024-byte pages) to a newer SQLite version (4096-byte pages).

Procedure:

1. Pre-migration: Set cache_size using negative values to maintain memory budget:

   PRAGMA page_size = 4096;
   VACUUM;
   PRAGMA cache_size = -2000;  -- Now allocates 500 pages (2000*1024 / 4096)
   

2. Post-migration: Re-evaluate cache performance. Larger pages may reduce the required page count for the same workload.

Step 6: Profile Cache Performance

Tool: Use sqlite3_analyzer or EXPLAIN QUERY PLAN to monitor cache efficiency.

Metrics:

• Page Cache Hits/Misses: A high miss rate indicates undersized cache.
• Memory Usage: Validate with sqlite3_status(SQLITE_STATUS_PAGECACHE_USED, ...).

Adjustment Example:

-- Double the cache size if misses exceed 10%
PRAGMA cache_size = -4000;

Final Recommendations:

• For Distributors: Avoid overriding SQLITE_DEFAULT_CACHE_SIZE unless necessary. Use negative values to respect user-configured page sizes.
• For Developers: Prefer PRAGMA cache_size = -kibibytes for portable memory budgets. Explicitly set page_size and cache_size during database initialization.
• For Documentation Maintainers: Clarify that 1024 in negative cache_size refers to kibibytes, independent of page_size. Add examples contrasting legacy and modern page sizes.

By methodically aligning compile settings, page size assumptions, and documentation interpretation, users can resolve cache_size discrepancies and optimize SQLite’s memory management effectively.