Excessive SQLite Write Amplification in High-Insert Blockchain Database

Understanding Write Amplification Patterns in Rowid-Based Tables with BLOB Keys and Multiple Indexes

The core challenge revolves around disproportionate write amplification in a SQLite database handling blockchain data, where small logical inserts (0.4GB/day) trigger massive physical writes (30-60GB/day). This occurs despite using WAL mode and having a schema with BLOB primary keys and four secondary indexes. The operational profile includes incremental inserts with potential hidden update/delete patterns common in blockchain implementations. Three critical factors emerge: 1) The interaction between BLOB primary keys and SQLite’s rowid allocation strategy, 2) Index update costs compounded by 32-byte hash values, and 3) WAL file management with concurrent temp file operations. Each component creates multiplicative effects on write operations through page splits, index maintenance, and WAL checkpointing behavior.

Root Causes of Hidden I/O Overhead in Hash-Intensive Blockchain Schemas

BLOB Primary Key Storage Mechanics
SQLite implements PRIMARY KEY constraints as unique indexes unless the key is INTEGER PRIMARY KEY. With BLOB primary keys, the database maintains two separate structures: the implicit rowid (64-bit integer) and the explicit BLOB-based index. Every insert writes both the rowid-ordered table data and the BLOB index entries. For 32-byte keys, this forces index entries to occupy multiple pages due to SQLite’s default 4KB page size. A single 100-byte record insert could require 10 page writes (40KB) from table+index updates.

Index Fanout Collapse with Fixed-Length Hashes
Each secondary index on 32-byte BLOB columns suffers from low fanout ratio. With 4KB pages, a non-clustered index stores approximately 128 entries per page (32 bytes key + 8 bytes rowid = 40 bytes per entry). Frequent inserts create dense index pages that split more often than indexes with higher-variance keys. The coin_record table’s four indexes multiply write operations – each insert triggers four index updates, each potentially requiring page modifications at multiple B-tree levels.

WAL File Growth and Checkpoint Stalls
WAL mode delays page writes to the main database file but accumulates changes in the -wal file. For databases exceeding RAM capacity (70GB in this case), checkpoint operations struggle to keep the WAL size manageable. The observed 21% write activity in the WAL file suggests either infrequent checkpoints or transactions holding WAL references open too long. Concurrent temp file usage (16% writes) indicates spillage of sorting/joining operations to disk, common in blockchain verification queries that process chained hashes.

Page Fragmentation from Update/Delete Patterns
Though described as "incremental inserts," blockchain databases often implement soft deletes (marking spent_index) and updates to chain integrity fields. These operations create page fragmentation when variable-length fields (like BLOB) are modified. SQLite’s auto_vacuum mode doesn’t fully compact tables with non-integer primary keys, leading to free space accumulation that inflates physical writes.

Optimizing Storage Layout and Transaction Patterns for Hash-Centric Workloads

1. Re-architecting Primary Key Strategy
Convert BLOB PK to INTEGER where possible: If coin_name contains sequential blockchain identifiers, consider adding an INTEGER PRIMARY KEY column and using the BLOB as unique constraint. This enables clustered organization by insertion order, reducing table heap fragmentation.

Implement prefix compression for BLOB keys: For 32-byte hashes that share common prefixes (common in blockchain merkle trees), store a separate prefix dictionary. Change coin_parent and puzzle_hash columns to INTEGER foreign keys referencing a hash_prefix table with first 4 bytes of hashes. This reduces index entry sizes from 32 bytes to 4 bytes + rowid (8 bytes), tripling index fanout.

2. Index Consolidation and Covering Index Optimization
Analyze index usage with PRAGMA index_list/STAT4: Identify rarely-used indexes via query plans. The four indexes on coin_record may have overlapping coverage. For example, if queries filtering on spent_index also sort by confirmed_index, a composite index on (spent_index, confirmed_index) could replace two single-column indexes.

Implement covering indexes for common access paths: Blockchain queries often verify transaction inclusion via multiple hash relationships. An index like CREATE INDEX coin_chain ON coin_record(coin_parent, puzzle_hash) INCLUDE (confirmed_index, spent_index) allows answering ancestor/descendant queries without table accesses, reducing write amplification from table row updates.

3. WAL and Checkpoint Configuration Tuning
Set PRAGMA wal_autocheckpoint to 4000 pages: Force more frequent WAL checkpoints to prevent unbounded growth. Calculate as (desired WAL size) / (page_size). For 16MB WAL target with 4KB pages: 16384 / 4 = 4096 → PRAGMA wal_autocheckpoint=4096;

Enable WAL truncation in vacuum mode: Schedule periodic PRAGMA wal_checkpoint(TRUNCATE) during low activity. This fully resets the WAL file instead of reusing it, avoiding file system preallocation overhead visible in the 21% WAL write percentage.

4. Temp Store and Page Size Optimization
Migrate temp storage to memory: Set PRAGMA temp_store=MEMORY to eliminate 16% writes to /private/var temp files. Monitor RAM usage to ensure sufficient headroom – with 70GB DB, allocate at least 8GB for temp space.

Increase page size to 8KB or 16KB: Recreate database with PRAGMA page_size=16384 before loading data. Larger pages better accommodate 32-byte hash entries, improving B-tree fanout. For coin_record’s primary key index, 16KB pages store 409 entries per page vs 128 with 4KB pages, reducing index depth and split frequency.

5. Batch Transaction and Delayed Index Maintenance
Wrap bulk inserts in explicit transactions: Ensure all hourly blockchain updates occur within single transactions to amortize WAL sync costs. SQLite flushes to disk on transaction commit – grouping 10,000 inserts per transaction reduces fsync operations 10,000-fold.

Defer index builds with CREATE INDEX … DEFERRED: When initializing new chain segments, create tables without indexes, load data, then build indexes with deferred option. This allows SQLite to sort all entries and build indexes in bulk rather than incremental updates, cutting write amplification by 3-5x during initial data load.

6. Free Space Management and Vacuum Strategies
Enable incremental auto_vacuum: Configure PRAGMA auto_vacuum=INCREMENTAL and run PRAGMA incremental_vacuum during maintenance windows. This gradually reclaims free pages from updates/deletes without full vacuum’s 70GB rewrite penalty.

Implement sparse rowid allocation: For the full_blocks table, set INTEGER PRIMARY KEY AUTOINCREMENT to prevent rowid reuse. This isolates new inserts to higher-numbered pages, reducing fragmentation from deleted blocks in lower pages.

7. Monitoring and Validation Framework
Profile page writes per operation: Use sqlite3_analyzer tool to measure actual pages modified per INSERT. Compare against theoretical minimum (table + index pages) to identify amplification hotspots.

Simulate workloads with different page sizes: Create test databases with 4KB, 8KB, 16KB pages using the same schema. Benchmark insert rates and I/O write volumes to quantify page size impact.

Implement write-ahead logging: Capture all application SQL statements to replay against optimized configurations. Use Linux blktrace to correlate SQL operations with block device writes, identifying which schema objects drive disproportionate I/O.

By systematically addressing B-tree fanout limitations, WAL checkpoint pressure, and index maintenance overhead, the write amplification factor can be reduced from current 75-150x (0.4GB logical → 60GB physical) to under 10x. For a 0.4GB/day logical insert load, achievable physical writes should approximate 4GB/day through combined schema tuning and transaction batching. Further gains require algorithmic changes like probabilistic data structures (Bloom filters for spent coin detection) or offloading historical data to columnar storage formats.