How ZSTD Compression and FTS5 Turned a 250GB SQLite Database Into a Sub-40ms Lookup Engine

Part 2 of 2

In Part 1, I covered the foundation: why SQLite’s embedded architecture outperforms client-server databases for read-only workloads, and how integrity verification, kernel tuning, and PRAGMA configuration reduced per-operation overhead by 15×.

But reducing overhead per operation doesn’t help when the query reads every row in a 50-million-record table. The first three layers of optimization brought individual page reads from ~120μs to ~10μs. A *LIKE ‘%keyword%’* query still triggered millions of those page reads.

This article covers the three layers that changed the query economics: schema design to minimize I/O per lookup, ZSTD compression to make the database memory-resident, and FTS5 full-text search to change the algorithmic complexity from O(N) to O(log N).

Schema Design: Eliminating the Double Lookup

Every time you query a standard SQLite table using a secondary index, two B-Tree traversals happen:

1. Index lookup: SQLite searches the index B-Tree to find the *rowid* that matches your WHERE clause

2. Table lookup: SQLite takes that *rowid* and searches the main table B-Tree to fetch the actual row data

On a 250GB database, each B-Tree traversal reads 4–5 pages (the tree depth). With 64KB pages, that’s 256–320KB of data per traversal. The double lookup means 8–10 pages and 512–640KB — and if those pages aren’t in the page cache, each one costs 80–120μs from NVMe.

For a single-column equality lookup, the double lookup doubles your I/O and your latency. On a dataset this size, where cache space is at a premium, those extra page reads evict other hot pages, creating a cascading cache pressure problem.

Covering indexes solve this completely. A covering index includes all columns that the query needs — both the columns in the WHERE clause and the columns in the SELECT list:

-- Query: SELECT email, name FROM users WHERE domain = 'example.com'
-- Covering index:
CREATE INDEX idx_users_domain_covering ON users(domain, email, name);

With this index, SQLite satisfies the entire query from the index B-Tree alone. It never touches the main table. The query planner reports *USING COVERING INDEX*, and I/O drops by 50%.

The key insight is column order. The indexed columns (those in WHERE/ORDER BY) must come first, followed by the “payload” columns (those in SELECT). SQLite uses the leading columns for B-Tree navigation and reads the trailing columns from the same leaf page it landed on. Put the payload columns first and the index becomes useless for lookups.

*WITHOUT ROWID* tables take a different approach. Instead of a hidden integer *rowid* as the B-Tree key, the table uses the declared PRIMARY KEY as the clustered key:

CREATE TABLE lookups (
lookup_key TEXT PRIMARY KEY,
result_data TEXT,
source TEXT
) WITHOUT ROWID;

Now there’s only one B-Tree, keyed by *lookup_key*. A primary key lookup is a single traversal — no secondary index, no second lookup. But there’s a trade-off: if the primary key is large (a 128-byte text string, for instance), it inflates every interior node of the B-Tree, reducing fan-out and increasing tree depth. The SQLite documentation recommends *WITHOUT ROWID* primarily for tables with compact keys — integers or short strings.

We calculated the fan-out break-even point: with 64KB pages, an 8-byte integer key gives ~8,000 keys per interior node. A 128-byte text key drops this to ~500 keys per node. For 50 million rows, that’s the difference between 3 levels (8,000 fan-out) and 4 levels (500 fan-out) — one extra page read per query for the larger key.

Partial indexes address a different problem: index size. If 80% of queries filter for status = ‘active’ and only 30% of records are active, a full index on (status, email) wastes 70% of its space on rows that will never match.

CREATE INDEX idx_active_email ON users(email) WHERE status = 'active';

This index is 70% smaller, which means more of it fits in the page cache, which means more index lookups hit RAM instead of NVMe. On a memory-constrained system (250GB database, 64GB RAM), partial indexes can be the difference between cache-resident and cache-thrashing.

*ANALYZE* is the most commonly overlooked optimization. Without it, SQLite’s query planner uses hardcoded heuristics: it assumes every table has 1 million rows, every equality match returns 10 rows, and every range scan returns 33% of the table. On a 50-million-row table, these estimates are off by 50×, leading the planner to choose full table scans where an index lookup would be 1000× faster.

Running *ANALYZE* populates the *sqlite_stat1* table with actual cardinality data. The planner then makes informed decisions based on real distributions. For a read-only database, *ANALYZE* should be run once during the build phase and the statistics shipped as part of the file. The query planner documentation explains how *sqlite_stat1* data influences plan selection.

The complete schema design guide — including anti-patterns, fan-out calculations, and EXPLAIN QUERY PLAN interpretation — is in Part 5 of the series.

ZSTD Compression: Making 250GB Fit in 64GB of RAM

All the optimizations so far reduce the number of I/O operations per query and the overhead per operation. But they don’t change one fundamental fact: 250GB of data does not fit in 64GB of RAM.

When a query needs a page that isn’t in the cache, it waits for NVMe. And even on a fast NVMe drive, a random 4KB read takes 80–100μs. That’s three orders of magnitude slower than a memory access at ~100 nanoseconds. For a sub-50ms query latency target, every NVMe read is a significant fraction of the budget.

Compression changes the equation by exploiting a hardware asymmetry that has grown more favorable every hardware generation: CPU decompression throughput exceeds storage I/O throughput.

ZSTD (Zstandard, developed by Yann Collet at Meta) decompresses at 2–4GB/s per core. NVMe random read throughput for small reads (the actual pattern of B-Tree traversals, not the sequential throughput on spec sheets) is roughly 500MB/s–1GB/s. Decompression is 2–8× faster than reading from storage.

This means that storing compressed data in the page cache and decompressing it on read is faster than storing uncompressed data on NVMe and reading it without decompression — even accounting for the CPU cost.

SQLite’s modular architecture enables this through its Virtual File System (VFS) layer. A compression VFS intercepts every page read, locates the compressed frame on disk, and decompresses it before handing the page to SQLite. Each page is compressed independently (seekable compression), so random access works — you don’t need to decompress the entire file to read page 50,000.

For our OSINT data — text-heavy records with repetitive JSON structures and common field names — ZSTD achieved a 3.5× compression ratio. The 250GB database shrank to approximately 70GB.

The impact on cache residency was transformative:

| Configuration | Effective cache coverage |
| - - - - - - - | - - - - - - - - - - - - -|
| Uncompressed (250GB file, 64GB RAM) | ~25% of data in cache |
| ZSTD compressed (70GB file, 64GB RAM) | ~90%+ of data in cache |

This is the page cache multiplier effect: every byte of RAM now holds 3.5 bytes of logical data. The workload transitions from I/O-bound (most queries waiting on NVMe) to CPU-bound (most queries decompressing cached pages). CPU-bound is faster.

Dictionary training pushed the ratio further. Standard ZSTD compression operates on individual pages in isolation. But across a database of scraped records, the same field names, URL patterns, and structural tokens appear in every page. ZSTD’s dictionary training — using the COVER algorithm — analyzes a sample of records and builds a compression dictionary that captures these repeated patterns.

We trained on 10,000+ representative records and used a 32KB dictionary. The dictionary improved the compression ratio by 20–40% beyond standard ZSTD, particularly effective on smaller pages where each page doesn’t contain enough repetition for ZSTD to exploit on its own. phiresky’s sqlite-zstd extension implements this pattern for production use.

One practical consideration: each active database connection holds a ZSTD decompression context in memory (~100KB). For our single-connection read-only server, this was negligible. For high-concurrency deployments with hundreds of connections, the aggregate memory should be budgeted.

We also increased *PRAGMA cache_size* to 256MB to retain decompressed pages in SQLite’s internal cache. Without this, the same compressed page might be decompressed multiple times within a single complex query — once for an index lookup and once for the table lookup. The internal cache retains the decompressed result, amortizing the decompression cost.

Filesystem-level compression (ZFS with ZSTD, Btrfs with ZSTD) is a zero-application-change alternative, but VFS-level compression with trained dictionaries consistently achieves better ratios because the dictionary is trained on the actual data patterns rather than on raw filesystem blocks.

The full compression architecture, including level selection, dictionary training, and benchmarking methodology, is in Part 6.

FTS5: Changing the Algorithm

Everything described in this article so far is optimization: making existing operations faster. FTS5 is different. It changes the fundamental algorithm — and that’s where the real breakthrough happened.

A *LIKE ‘%keyword%’* query has O(N) complexity. SQLite reads every row in the table, performs a substring comparison, and returns matches. On 50 million rows, this is 50 million comparisons regardless of how many results exist. If the keyword appears in 3 documents, you still read all 50 million to find them.

FTS5 (Full-Text Search version 5) builds an inverted index: a data structure that maps every unique token (word) to the list of documents containing it. The query “find documents containing ‘malware’” becomes a single B-Tree lookup on the token “malware” — O(log N) complexity. For 50 million documents, that’s roughly 8 page reads instead of millions.

CREATE VIRTUAL TABLE search_idx USING fts5(
content,
tokenize='unicode61 remove_diacritics 2'
);
-- Query: sub-millisecond
SELECT rowid, rank FROM search_idx WHERE search_idx MATCH 'malware'
ORDER BY rank LIMIT 20;

The tokenizer choice matters significantly for multilingual OSINT data. We used *unicode61* for its proper Unicode word boundary detection (critical for non-Latin scripts), with *remove_diacritics=2* for accent-insensitive matching. For fields requiring substring search (domain names, partial identifiers), we added trigram indexes:

CREATE VIRTUAL TABLE trigram_idx USING fts5(
content,
tokenize='trigram'
);

The trigram tokenizer indexes every 3-character substring, enabling *LIKE ‘%xyz%’*-style queries through B-Tree lookups instead of table scans. The trade-off is index size — trigram indexes are substantially larger than word-level indexes — but for critical search paths, the latency improvement justifies the storage cost.

A critical design decision: we used contentless FTS5 tables (*content=’’*). By default, FTS5 maintains a copy of the full indexed text alongside the inverted index — essentially doubling the storage for indexed columns. Contentless tables store only the inverted index; queries return document IDs, and the application fetches full records from the main table using those IDs. This halved the FTS storage overhead at the cost of requiring a second lookup for result data. For a 250GB dataset, the storage savings were essential. The FTS5 documentation covers the contentless table trade-offs.

Sharding: Operational Resilience at Scale

A single 250GB SQLite file has operational fragility. VACUUM requires 250GB of free disk space. A corrupted page anywhere in the file potentially affects every query. Backup means copying 250GB as an atomic unit.

We addressed this through hash-based sharding: the dataset is partitioned across multiple smaller SQLite files.

import hashlib
def get_shard(key: str, num_shards: int) -> int:
return int(hashlib.sha256(key.encode()).hexdigest(), 16) % num_shards

Each shard is an independent, self-contained SQLite database with its own FTS5 index, PRAGMA configuration, and *immutable=1* guarantee. The application-layer router maps a lookup key to its shard and opens a connection only to that file.

The benefits compound:

• Fault isolation: corruption in one shard affects only 1/N of the data. Rebuilding a single shard takes minutes, not hours.

• Parallel builds: shards can be indexed, compressed, and VACUUMed independently across multiple CPU cores. The build pipeline scales linearly with available cores.

• Blue-green deployment: updated shards can be swapped in atomically without touching unmodified shards.

• Targeted cache warming: *vmtouch* can prioritize warming the most frequently queried shards, making better use of limited RAM.

For queries that must span multiple shards (broad searches), the application fans out to relevant shards in parallel and merges results. Since each shard produces its own BM25 relevance scores, cross-shard ranking requires normalized corpus statistics — we precompute these during the build phase and embed them in each shard.

SQLite’s ATTACH DATABASE and unionvtab extension provide lower-level mechanisms for cross-database queries, but application-layer routing gives the most control over connection lifecycle and error handling.

The complete sharding architecture — including build pipeline scripts, cross-shard result merging, and production deployment diagrams — is in Part 7.

Measuring Everything

The most important engineering discipline in this project was verifying that each change had its expected effect. Configuration without measurement is superstition.

*strace -c -p <pid>* counts system calls per query. This is how we verified that *mmap*, *locking_mode=EXCLUSIVE*, and *immutable=1* were actually reducing kernel interaction. If you enable mmap and still see *pread64()* calls in strace output, the mmap region isn’t covering your database file — check the *mmap_size* value.

*EXPLAIN QUERY PLAN* reveals the query planner’s strategy. Three phrases to watch for:

| Phrase | Meaning | Action |
| - - - - | - - - - -| - - - - |
| `SCAN TABLE` | Linear scan - O(N) | Create an index. On 250GB, this is unacceptable. |
| `SEARCH … USING INDEX` | Index lookup + table lookup | Consider a covering index to eliminate the second lookup. |
| `SEARCH … USING COVERING INDEX` | Index-only access | Optimal - no table lookup required. |

*vmtouch -v database.db* shows page cache residency. After compression, this should show 90%+ of pages resident. If it doesn’t, the compressed file exceeds available RAM, or another process is competing for cache space.

*/usr/bin/time -v* distinguishes major page faults (NVMe reads) from minor page faults (cache remaps). For a cache-resident query, major faults should be zero.

SQLite’s .timer ON reports parse time, execution time, and the number of virtual machine operations per query. Combined with strace data, this pinpoints whether latency is in the query planner, the I/O layer, or the execution engine.

The Final Numbers

| Metric | Before | After | Factor |
| - - - -| - - - -| - - - | - - - -|
| Query latency | 4+ hours | ~40ms | 180,000× |
| System calls per query | ~180 | ~12 | 15× |
| On-disk size | 250GB | ~70GB | 3.5× reduction |
| Effective cache coverage | ~25% | ~90%+ | 3.6× |
| Search complexity | O(N) | O(log N) | Algorithmic change |

The 180,000× improvement is dramatic, but it’s not magic. It’s the product of six layers of systematic engineering, each targeting a specific measurable source of latency. No single layer produced more than a 10–15× improvement on its own. The gains compound multiplicatively: a 3× improvement in cache efficiency × a 15× reduction in syscall overhead × a 1000× improvement from algorithmic change = a massive aggregate improvement.

The Engineering Takeaway

Most “slow database” problems are not hardware problems. They’re problems at one of six layers:

1. Data integrity — you can’t optimize a broken database

2. Storage configuration — kernel defaults are wrong for your workload

3. Engine configuration — safety defaults for read-heavy workloads are pure overhead

4. Schema design — the wrong index costs more than no index

5. I/O economics — if your data doesn’t fit in RAM, compress it until it does

6. Algorithm choice — O(N) is O(N) no matter how fast each operation is

The tools to diagnose which layer is your bottleneck — *strace*, *EXPLAIN QUERY PLAN*, *vmtouch*, */usr/bin/time* — are free and available on every Linux system. The engineering work is measurement, not guesswork.

The complete 7-part series with tested configurations, benchmark scripts, and source references: From 4 Hours to 40ms: SQLite at 250GB.

References:

1. SQLite Official — Full-Text Search (FTS5)

2. SQLite Official — Clustered Indexes and WITHOUT ROWID

3. SQLite Official — The UNION Virtual Table

4. SQLite Official — EXPLAIN QUERY PLAN

5. SQLite Official — Query Planner Overview

6. SQLite Official — ANALYZE

7. SQLite Official — ATTACH DATABASE

8. Meta/Facebook — ZSTD Benchmarks

9. phiresky — sqlite-zstd: Dictionary-based compression

10. Atlas Guides — Partial Indexes in SQLite

11. trunc.org — Compressing SQLite databases with ZFS

12. Stanimirov — Introducing Static Sharding to an SQLite Backend

13. PowerSync — SQLite Optimizations For Ultra High-Performance

14. Jacob Filipp — Optimizing a large SQLite database for reading

Tags: SQLite, Database Engineering, ZSTD, Compression, FTS5, Full-Text Search, Sharding, Systems Engineering, Linux, NVMe