Scaling SQLite FTS5 patent DB to 5.36M records reveals performance lessons

The author shares engineering insights from scaling a SQLite database with FTS5 full-text search from 3.5 million to 5.36 million US patent records, focusing on optimization techniques for large-scale data.

• PatentsView data migrated to the USPTO Open Data Portal in March 2026, requiring updates to download sources and table structures.
• Running ANALYZE after bulk-loading resolved a correlated EXISTS query issue, reducing execution time from 34 seconds to 0.16 seconds on a 108 million-row citation table.
• Wide rows (averaging 19KB) penalize UPDATE operations; using side tables with JOINs is more efficient than mass-updating the main 119GB table.
• AND queries outperform OR for BM25 at this scale, cutting candidate results from ~1 million to thousands and improving query times significantly.

The database now contains 5.36 million patents from 2010–2025 with a 108 million-edge citation graph, and the next step involves tagging newly added records.