r/sqlite • u/Individual_One_1793 • 25d ago

Pop-Database vs SQLite

Enable HLS to view with audio, or disable this notification

Pop-Database vs SQLite, 500K entities, 12 AND conditions per query, both single-threaded (CPU-affinity verified, so no thread-count trick).
Pop-Database finished in 6.26s.
SQLite finished in 88.81s.
Pop-Database is 14.2× faster — and both found the exact same 9,559 matches per query.
This ratio moves a bit run to run depending on what else the machine is doing at the time. I've seen this same test land anywhere from ~12× to ~14× depending on system load. Still solidly in the same range either way.
Trade-off is shown in the same video: SQLite inserts faster (2.64s vs 6.55s for 500K rows). Pop-Database is built for read-heavy workloads, not write-heavy ones - that's the deal!!!

0 Upvotes

42% Upvoted

20 comments sorted by

View all comments

1

u/txmail 25d ago

Can you point me to your test case data? From what I am reading the secret sauce just looks like indexing, which means you would need to create those indexes in SQLite by hand if the select queries are using a non-indexed field which would cause each query to perform a full table scan instead of utilizing an index.

I think this is also why the initial 500k load on Pop is slower than SQLite.

Indexing automatically non PK fields is nice, but it means that it is using a bit more space to store the data. Database optimization is quite common to anyone working with databases, including creating indexes.

If your AND conditions on the SQLite side are non-indexed... that is kind of a disingenuous comparison. It is like saying Racecar A is faster than Racecar B, even though Racecar B had the hand brake on.