pglite-fusion

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

pglite-fusion is a PostgreSQL extension that allows you to embed SQLite databases into your PostgreSQL tables by enabling the creation of columns with the `SQLITE` type. This means every row in the table can have an embedded SQLite database. In addition to the PostgreSQL `SQLITE` type, pglite-fusion provides the `query_sqlite`` function for querying SQLite databases and the `execute_sqlite` function for updating them. Additional functions are listed in the project’s README. The pglite-fusion extension is written in Rust using the pgrx framework [1]. ---- Implementation Details The PostgreSQL `SQLITE` type is stored as a CBOR-encoded `Vec<u8>`. When a query is made, this `Vec<u8>` is written to a random file in the `/tmp` directory. SQLite then loads the file, performs the query, and returns the result as a table containing a single row wi