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.
I can see this would be useful in multi-tenant applications