Performance Tuning Lessons from a 5.36M Patent Database
Impressive_Tower_550 · reddit · 2026-07-11
The author shares engineering insights from scaling a SQLite/FTS5 patent database from 3.5 million to 5.36 million entries. The project is maintained as a free, ad-free, no-signup side project by a patent lawyer who started coding in December 2025.
Key Lessons
- PatentsView migrated to the USPTO Open Data Portal in March 2026; old S3 download links are dead, and abstracts were split into a separate table.
- Running ANALYZE after bulk inserts is crucial. Fixing a single EXISTS query with the wrong index dropped query time from 34 seconds to 0.16 seconds.
- Wide row updates are expensive: At an average of 19KB per row, a massive UPDATE practically rewrites the entire 119GB table. Moving variable fields to side tables is a better approach.
- In FTS5, AND / phrase intersections scale far better than OR. ORing common terms can balloon the candidate set to millions, whereas AND narrows it down to thousands.
Current Status
- The database now holds 5.36 million patents (2010–2025)
- The graph contains 108 million citation edges
- Assignee deduplication is complete, and USPTO's AIPD AI-patent flags are integrated
- Next step: continuing Nemotron annotations for 1.9 million newly added records
A demo site is available at patentllm.org.
More from Infra
- NVIDIA publishes Vera CPU architecture details before AMD’s AI event — ryanshrout · 2026-07-22
- oMLX 0.5.2 adds Mac menu-bar stats, low-bit decode kernels, and faster downloads — awnihannun · 2026-07-22
- Strangeworks launches Aura to turn enterprise ops into production optimization systems — whurley · 2026-07-22
- Graph workload 854.graph500 enters SPEC CPU 2026 as a new CPU benchmark — Prof_DavidBader · 2026-07-22
- HilbertRaum open-sources a fully local AI chat and document analysis app for private use — Vladowski · 2026-07-22
- Hybrid and local inference are emerging as a response to AI energy and token costs — dmitry140 · 2026-07-22