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
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.
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 :)
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.