Comment by marginalia_nu
11 hours ago
If you held a gun to my head and forced me to make a guess I'd say you could push that approach to order of 100K, maybe 1M documents.
If sqlite had a generic "strictly ascending sequence of integers" type[1] and would optimize around that, you could probably push it farther in terms of implementing efficient inverted indexes.
[1] primary key tables aren't really useful here.
> If sqlite had a generic "strictly ascending sequence of integers" type
Is that not what WITHOUT ROWID does? My understanding is that it's precisely meant to physically cluster data in the underlying B-Tree
If that is not what you meant, could you elaborate on the "primary key tables aren't really useful here" footnote?
From my experience, SQLite's FTS5 is orders of magnitude more performant than that, i.e. for 100K documents, 7 queries/second on some of the cheapest 1 vCPU Virtual Machines.
But it is true that a specialized search engine using a more clever algorithm might be another order of magnitude faster.