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.
3 comments
[ 3.1 ms ] story [ 18.0 ms ] threadI can see this would be useful in multi-tenant applications
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