←back to thread

175 points frectonz | 2 comments | | HN request time: 0.401s | source

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 with an array of JSON-encoded values.

The `execute_sqlite` function follows a similar process. However, instead of returning query results, it returns the contents of the SQLite file (stored in `/tmp`) as a new `SQLITE` instance.

[1] https://github.com/pgcentralfoundation/pgrx

Show context
klysm ◴[] No.42186653[source]
If you’re using Postgres, multi tenancy has been solved with row level security. It’s super easy to add a tenant id column to every table and a policy that only allows connections to see data from one tenant
replies(2): >>42186822 #>>42187409 #
1. toasterlovin ◴[] No.42187409[source]
Multi-tenancy causes performance issues that simply don't exist if each customer's data is in it's own database.
replies(1): >>42207680 #
2. klysm ◴[] No.42207680[source]
Yeah really depends on the volume of the data and how sensitive the workload is to a few milliseconds. For a lot of business use cases, it's totally worth it to maintain just one database.