SQLite Internals: Pages and B-trees

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.

[ Parent | on HN ]

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

[ Parent | on HN ]

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

[ Parent | on HN ]

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.

[ Parent | on HN ]