Performance

In general, it’s worth benchmarking before commiting to stepping, see for example: test_profile.py.

Insert speeds are far slower than plain SQL, but once you cross a million rows or so, retrieving data with JOINs and GROUP BYs can become orders of magnitude quicker. When weighing up, also consider the developer experience, you have the whole of Python to play with, not just SQL. Insert time should be linear (give or take some writing to indexes).

Rough Benchmarks #

All on my M1 Macbook Air.

• For a fairly standard query (with a couple of joins, a group by, an integrate into a cache, 6 delay vertices), we can write 10,000 rows per second to Postgres and 15,000 rows per second to SQLite.
• For a more complex query (with >10 delay vertices and a recursive operator), we can write 2000 rows per second to SQLite, up to 4000 rows per second if we enable (transactionally dangerous) parallelism. Writing 100,000 rows (fairly small rows, but with indexes, nested ZSets etc.) takes up 120MB of space.
• Retrieving data from an indexed cache should take in the low ms.

Future #

• Profile with loads of data, how do insert times grow over time?
• How does performance look with read replica(s)?
• Look into doing something like pydantic-core and rewriting hot code in Rust. The main blocker for writing “ZSets in Rust” are probably:
  • Adding .st_bytes, .st_hash, .st_identifier to ZSetPython and Pair. Can we do some crazy hashing with bitmaps or something where we update the hash as we increment/decrement counts?
  • Should the underlying immutabledict[K, T] also be an immutable btree.
  • Some deep thought required with index ordering of ints, floats datetime (see _btree.py). Maybe we just split Ks between those that can be ordered lexicographically and those that can’t.
• Build some entirely different storage layer, eg. using something new and trendy like sled, or less trendy, like lmdb.
• How quick is it in pypy?
• Use a different SQLite adaptor, eg. apsw, or maybe even wrap a rust SQLite library.