News for hackers. Stuff that matters.
Re:SQLite Internals: Pages and B-trees by packetlost on Wednesday July 27, 2022 @11:47AM (#32251936)
I've been doing something very similar, but based on extendible hashing algorithms and LSM trees. I've come to the conclusion that a DAG of constraints, triggers and validations on a flat KV + entity model is probably the ideal data structure for 99% of projects I've worked on. You can get the benefits of the Datomic-like history by... skipping compaction and including a TX instant next to all record/assertions. I've found SQLite, Postgres, the LSM papers, Bitcask, and many other papers to be very helpful in terms of inspiration.

Edit: I'm prototyping in Python and implementing in Rust with intent to create a C API and embedding a Scheme runtime for "server"-side query and constraint parsing.

Re:SQLite Internals: Pages and B-trees by ftdyuio9p0iugy on Wednesday July 27, 2022 @12:37PM (#32252704)
I've had some append-only tables in Postgres and only recently realised that Postgres' system columns (https://www.postgresql.org/docs/14/ddl-system-columns.html) already effectively enabled some Datomic-like structure for such append-only tables! Specifically the xmin column allows me to identify the rows to treat atomically as a unit, and to ignore if I'm querying for a historical view.
Re:SQLite Internals: Pages and B-trees by packetlost on Wednesday July 27, 2022 @01:09PM (#32253177)
You could probably do it with BRIN indexes similar to how TimescaleDB handles their time-series hypertables
Re:SQLite Internals: Pages and B-trees by LoriP on Wednesday July 27, 2022 @01:23PM (#32253394)
TimescaleDB is packaged as a postgres extension, there's a GitHub project here if anyone is interested to check in on that https://github.com/timescale/timescaledb
Re:SQLite Internals: Pages and B-trees by zresdtfugyihoi on Thursday July 28, 2022 @02:29AM (#32261086)
Indeed! That seems like quite the ideal use-case for BRIN indexes.
Re:SQLite Internals: Pages and B-trees by sauruk on Wednesday July 27, 2022 @03:13PM (#32255281)
Do you have a public repo for that yet? (Assuming you're planning to open-source)
Re:SQLite Internals: Pages and B-trees by packetlost on Wednesday July 27, 2022 @04:09PM (#32256151)
It's got a LICENSE but not publicly listed yet. It's very rough, and has a ton of work left. Once I get it to a workable state I'll open it up under AGPL, though probably with a CLA because I'd like to turn it into a marketable product in the long-run. If I make significant progress on it, I'll reply to this thread with updates :)