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

11–20 of 116 posts

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

#11

Post by rswail »

bravura wrote: post #5
robertclaus wrote: post #2

What are the use cases for this? I can't imagine designing a database schemas to use this in a typical product. Is it intended for hybrid applications to back up local user data directly with their account info?

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…

Each of the columns is an instance of a SQLite database, so I assume (without looking at the source) that they properly multi-thread as needed.

So there's not cross-SQLite-database connections or multiple writers going on.

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

#12

Post by aerzen »

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.

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

#13

Post by rswail »

bravura wrote: post #5
robertclaus wrote: post #2

What are the use cases for this? I can't imagine designing a database schemas to use this in a typical product. Is it intended for hybrid applications to back up local user data directly with their account info?

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…

You could join over them, but not really in the way you're thinking.

Each of the columns that are databases would be updated when the functions execute.

You could do weird crap like INSERT/DELETE as part of a postgres level SELECT.

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

#14

Post by michelpp »

simonw wrote: post #4

They /tmp file mechanism sounds like a bit of a hack, is that definitely necessary? It may be possible to create a SQLite in-memory database instead and then load the binary blob data into it using the backup API or some kind of trick with VACUUM INTO.

I think the right approach would be to store the sqlite database as a varlena type that can be TOASTed and then "expanded" using the Expanded Datum API so that it's a live open database connection for the life of the transaction:

https://www.postgresql.org/docs/17/xtypes.html#XTYPES-TOAST

https://github.com/postgres/postgres/blob/master/src/include...

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

#15

Post by frectonz »

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.

My next project

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

#16

Post by pmontra »

bravura wrote: post #5
robertclaus wrote: post #2

What are the use cases for this? I can't imagine designing a database schemas to use this in a typical product. Is it intended for hybrid applications to back up local user data directly with their account info?

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 shipping them to their own devices. Or maybe receiving them and performing analysis, maybe after having imported it in overall psql tables. Or similar scenarios in which all the db is read or written at once. Anyway, once we have a tool we start using it.

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

#17

Post by frectonz »

michelpp wrote: post #14
simonw wrote: post #4

They /tmp file mechanism sounds like a bit of a hack, is that definitely necessary? It may be possible to create a SQLite in-memory database instead and then load the binary blob data into it using the backup API or some kind of trick with VACUUM INTO.

I think the right approach would be to store the sqlite database as a varlena type that can be TOASTed and then "expanded" using the Expanded Datum API so that it's a live open database connection for the life of the transaction: https://www.postgresql.org/docs/17/xtypes.html#XTYPES-TOAST https://github.com/postgres/postgres/blob/master/src/include...

Thanks i will look into this more, /tmp stuff is most definitely a hack.

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

#18

Post by sgarland »

> 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

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

#19

Post by sgarland »

zekenie wrote: post #6

I’m trying to think through when I’d reach for this over jsonb… I guess the fact that there’s an enforced schema? And that you could do aggregations on your SQLite db? Or maybe if you wanted to send the whole delete db to a client??

> enforced schema

I have bad news for you [0] about SQLite’s view on schema consistency.

[0]: https://www.sqlite.org/quirks.html

Post reply on HN