4 ms·
Show HN: Postgres-SQLite: SQLite as first-class types in Postgres
- deleted 2y ago[deleted]
- kiwicopple 2y agoThis is a pretty clever use-case for the expanded datum. Any idea of the performance of this? I can see this would be useful in multi-tenant applications
- michelpp 2y agoThe performance while querying sqlite objects with sqlite_query() is identical to that of using an in-memory sqlite database. once Postgres 18 is released which contains some specific optimizations around expanded datum lifecycles there will be further improvements to sqlite_exec's behavior. For Postgres < 18 sqlite_exec() will incur a flatten/expand cycle, but this can be worked around in most cases by bundling SQL into a single sqlite_exec call instead of multiple ones. Once Postgres 18 is released this will no longer be an issue. This work is the result of a fairly long running discussion thread [1] on the pgsql-hackers mailing list around improving expanded datum handling for non-array types in general so that one of my other extensions, OneSparse, can take advantage of them. We discussed adding a "skeleton" expanded example into the Postgres test modules, but it wouldn't be useful. As I generally prefer to make useful things, I took a page from the ideas in pglite-fusion [2] and made it a true first class expanded datum which requires no server filesystem access. [1] https://www.postgresql.org/message-id/flat/CACxu%3DvKMWjj%2BXJ0%3DOk9iZmTwUfRXytVsdv8%2BU5X4LMUPTM5iiw%40mail.gmail.com#5cb440f6f4d767e01bb6a59545365101 https://www.postgresql.org/message-id/flat/CACxu%3DvKMWjj%2B... [2] https://github.com/frectonz/pglite-fusion https://github.com/frectonz/pglite-fusion