This is not as crazy as it sounds but I'd rather have PG-in-PG than the hetrogeneous arrangement here. PG in S3 would be useful too.
Show HN: Embed an SQLite database in your PostgreSQL table
81–90 of 116 posts
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#82What, no operators? I want indexes on these columns, and some weird and wonderful operator syntax for doing cross-database joins between multiple DATABASE columns! =)
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#83Yo, dawg, I heard you like databases... This is nuts. I can't think of a use for it, but I'm sure it's "a solution that will eventually find a problem".
I was _extremely disappointed_ not to see this meme when I clicked on the link. Will not consider using this extension until Xzibit is prominently featured.
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#84Please let us embed that sqlite-hosting postgres in a TXT DNS record. We really need that.
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#85Ok, hear me out: what if we make something that takes a postgres database dir, tars it together and encodes it as a binary blob in SQLite? We could have SQLite within postgres within sqlite within postgres! Is it practical or even slightly useful? Of course not - but it's SQL databases all the way down. Not that this is a good thing in itself.
This is some kind of RDBMS mutant CRUDucken.
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#86Speed, anyone? How long does it take to update a table of, say, 1k rows? 1m rows? Same when subqueries and joins are involved to calculate what's to be updated?
The current implementation is writing out the DB to `/tmp` then reading the resulting file back and writing it to the column. So on the bright side updating 1k rows takes the same amount of time as updating one row. On the other hand every write is a full table write (actually two). I don't think there is a way to do this efficently with the current API as PostgreSQL is MVCC so it needs to write out each version sepa…
>The current implementation is writing out the DB to `/tmp` then reading the resulting file back and writing it to the column.
I think there was already another comment where someone told OP how to solve that.
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#87Earlier quoted context omitted.
> managing schema-per-tenant at our scale sounds like a complexity nightmare. The per-tenant schema could be the tenant's responsibility. Most non-technical users can handle the idea of tables & columns, assuming you leverage UI/UX patterns they are already familiar with.
Our UI looks like a table: https://www.notion.so/help/intro-to-databases As long as we never add new features, never need to change how we map UI Postgres DDL, and our users never make any mistakes when they change their tables, it could work without being a complexity nightmare
Curious - so how do you manage client-specific schemas then? Do you just have mappings in postgres (column1, column2, column3, etc.) or maybe store a client specific schema in bson per client?
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#88Yo, dawg, I heard you like databases... This is nuts. I can't think of a use for it, but I'm sure it's "a solution that will eventually find a problem".
A different approach:
I had a project that stored a tremendous amount of spatial data. There were "sessions" of spatially-tagged time-series data that would be individually processed (think generating a map layer from time-series data). There were also reasons to perform higher level aggregations that did not dive into the time series data. The data density was high enough that it was impractical to build spatial indices over the entire dataset. Even using space-filling curves as multidimensional B-trees would require so many lookups that queries were impractically slow.
One POC I tried (and then rejected as an abomination) was to store each session's time-series data inside a SQLite database with SpatialLite extensions enabled. Then store each session's metadata, including spatial extent, in a Postgres database. The SQLite files were tossed in S3 and referenced from Postgres. I guess I could have inserted them directly to a BLOB column inside Postgres.
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#89If 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
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#90Earlier quoted context omitted.
But can I have one row that holds all the databases?
yes you can CREATE TABLE crime_against_humanity ( databases SQLITE[] );
With the expanded datum api you can also work with subscriptable array types to only expand elements lazily as needed. It might already works if you try it, but support for it might be hardwired only to nested stock arrays, something to look into.