Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: Embed an SQLite database in your PostgreSQL table

github.com

Post reply on HN
Print view View original

101–110 of 116 posts

Re: Show HN: Embed an SQLite database in your PostgreSQL table

#101

Post by luismedel »

c0balt wrote: post #21
aerzen wrote: post #12

Ok, 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.

Take it one step further, the table-oriented database(tm) , embed clickhouse, MongoDB, Redis and PostgreSQL to ensure you have more flexibility than anyone can utilize efficiently. The one database to rule them all.

Now you only need support for qcow columns which you can mount in your embedded engines....et volia, enjoy your storage and compute separation.

Re: Show HN: Embed an SQLite database in your PostgreSQL table

#102

Post by sgarland »

traeregan wrote: post #85
aerzen wrote: post #12

Ok, 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.

‘Tis the season.

Re: Show HN: Embed an SQLite database in your PostgreSQL table

#103

Post by klysm »

michelpp wrote: post #93
klysm wrote: post #89

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

RLS is very useful and can solve multi tenancy and other problems, but it is complicated and can add a significant per row cost to queries if your policies get complicated. The common path of comparing some constant like the role name to some column in the table is fine, and it's fast enough as the policy checker already has the row in hand when it does the check, but the natural tendency for people to want to abstra…

Sure if you start using it for more than just multitenancy you can get into performance trouble or other complexities. I haven’t felt tempted to put anything beyond the tenant level isolation though yet and it’s served us very well

Re: Show HN: Embed an SQLite database in your PostgreSQL table

#104

Post by jitl »

mbesto wrote: post #87
jitl wrote: post #66

Earlier quoted context omitted.

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?

It's all JSON in two Postgres tables: `collection` which represents a Notion Database and has a `collection.schema` JSONB column, and `block` which has a `block.properties` JSONB column that stores the property values - the stuff in the Notion Database cells - for each row. We apply "schema on read" when querying or rendering a Notion Database, and we have a service on the backend that builds indexes/caches for hot collections on the fly. The service handles all the queries for collections larger than X rows. For smaller collections, we just give the client the whole thing modulo permissions and it does the query locally.

Re: Show HN: Embed an SQLite database in your PostgreSQL table

#106

Post by ffsm8 »

sgarland wrote: post #18

> Most relational database management systems do not support nested records, so tables are in first normal form by default. In particular, SQL does not have any facilities for creating or exploiting nested tables. [0] “Not with that attitude.” – frectonz [0]: https://en.wikipedia.org/wiki/First_normal_form

Your link already points out that this isn't followed anymore since Json has been added as a default SQL feature

Re: Show HN: Embed an SQLite database in your PostgreSQL table

#109

Post by sgarland »

ffsm8 wrote: post #106
sgarland wrote: post #18

> Most relational database management systems do not support nested records, so tables are in first normal form by default. In particular, SQL does not have any facilities for creating or exploiting nested tables. [0] “Not with that attitude.” – frectonz [0]: https://en.wikipedia.org/wiki/First_normal_form

Your link already points out that this isn't followed anymore since Json has been added as a default SQL feature

No? It says that SQL99 allows non-atomic types, and SQL16 allows JSON. That doesn’t mean that 1NF is dead, or even that JSON is allowed in 1NF, only that the standard (which RDBMS providers may choose to implement in part or whole) allows for their existence.

Atomicity of values has been debated for a long time. I’ve come around to the idea that flat arrays can be included in a 1NF table, because they don’t imply any additional structure to the schema. The problem with JSON is that it supports arbitrary K:V pairs as well as nesting, and so can introduce a schema within a schema, which is prone to referential integrity violations (not to mention generally poor performance in RDBMS).

Embedding an entire DB is of course beyond the pale, and my comment was an attempt at wit.

Re: Show HN: Embed an SQLite database in your PostgreSQL table

#110

Post by ecuaflo »

ray_v wrote: post #64
ecuaflo wrote: post #52

I 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.

wouldn't that just be a foreign key to another table or, a list of keys or am i missing something?

I think it’s useful when you only need to query one way and can then avoid a join each time/extra schema complexity of a join table.
Post reply on HN