Yet again SQlite is over represented here in the server context.
Show HN: Embed an SQLite database in your PostgreSQL table
51–60 of 116 posts
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#52I think SQLite columns for SQLite would be superior to SQLite’s JSON columns whose operators are a whole ‘nother query language you need to learn and seem comparatively limited.
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#53Yo, 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".
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#54This is likely great for serving SQLite data to frontends using the SQLite http-vfs.
Would be great combined with functions/triggers/views to mirror specific data/queries from Postgres as SQLite.
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#55Earlier quoted context omitted.
Historically, yes. Databases were software that were concerned with both storage and networking. It's fine to want to separate those out, but it's not easy to do so and there are reasons they've been coupled for decades.
What makes it hard? Having a single DB that takes write queries via a proxy which spreads them out to multiple read-only-DBs sounds easy at first.
When do you consider the write/transaction to be completed?
What do you do about out-of-sync read replicas?
ACID gets real hard real fast when introducing replication.
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#56Speed, 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 separately (unless it has some sort of support of partial string sharing, I don't think so). Maybe a better version of this would write every page of the SQLite DB as a separate row so that you only need to update the changed pages.
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#57Earlier quoted context omitted.
What makes it hard? Having a single DB that takes write queries via a proxy which spreads them out to multiple read-only-DBs sounds easy at first.
When do you consider the write/transaction to be completed? What do you do about out-of-sync read replicas? ACID gets real hard real fast when introducing replication.
> When do you consider the write/transaction to be completed?
Sending a UPDATE/INSERT/DELETE statement to SQLite is not blocking? I would think it is, because in my code I can read the number of affected rows right after I sent the query.
> What do you do about out-of-sync read replicas?
Delete them and replace them by uploading a checkpoint and replaying a log of the queries since then.
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#58Earlier quoted context omitted.
> Letting users create full custom PostgreSQL tables can get complex - do you want to manage tens of thousands of weird custom tables in a PostgreSQL schema somewhere? Yea, I'd be fine with that - postgres has the concept of databases and schemas within those databases. If you really want to build a product like that I'd suggest starting with per-tenant schemas that leverage table inheritance as appropriate. The perm…
Notion has 100 million users, managing schema-per-tenant at our scale sounds like a complexity nightmare. We have 480+ identical schemas across 100+ Postgres hosts, and that already takes a lot of brainpower & engineering time to manage T_T
> 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.
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#59Earlier quoted context omitted.
The top line of the README says: "Embed an SQLite database in your PostgreSQL table. AKA multitenancy has been solved." But I'm still having trouble trying to grok the intricacies of it. In a sense, I guess it has well isolated individual SQLite DBs and you'd have to go out of your way to join over them. With that said, does PostgreSQL manage and pool all the writes correctly? So you don't need to worry about SQLite…
If by solving multinenancy they mean CREATE TABLE tenants ( id BIGINT NOT NULL, database SQLITE DEFAULT execute_sqlite( empty_sqlite(), 'CREATE TABLE users (etc.)' and all the other tables for each tenant ) ); then they don't need to make joins between sqlite dbs. Your other concerns are very real. Those sqlite dbs could become very large. I prefer the use case depicted in another reply: preparing sqlite dbs before s…
> then they don't need to make joins between sqlite dbs.
The extension could also provide custom index access methods (considering that SQLite only has a handful of column types in the first place.) That would allow you to incorporate the keys in the index heaps, as opposed to table heaps, boom, you get bitmap index scans for Joins, i.e. GIN but with a bit more redundancy.
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#60Ok, 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.
“We need to go deeper” (https://i.kym-cdn.com/photos/images/newsfeed/000/384/176/d2f...)
