Show HN: Postgres-SQLite: SQLite as first-class types in Postgres

The 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%2B...

[2] https://github.com/frectonz/pglite-fusion