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

41–50 of 116 posts

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

#41

Post by otoolep »

emseetech wrote: post #33
TekMol wrote: post #27

Are there still reasons to use PostgreSQL? I like the simplicity of SQLite's "a file is all you need" approach so much, that I started to converge all my projects to SQLite. So far, I have not come across any roadblocks. Can anyone think of a use case where PostgreSQL is better suited than SQLite?

The biggest one is redundancy. Architecting with Read replicas is much easier with Postgres than Sqlite because of it's server model. Sqlite on the server is a fantastic starter database. Dead simple to set up, highly performant and scales way higher (vertically) than anyone gives it credit for. But there certainly is a point you'll have to scale out instead of up, and while there are some great solutions for that (r…

rqlite[1] creator here, happy to answer any questions about it.

[1] https://rqlite.io

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

#42

Post by TekMol »

evandrofisico wrote: post #39
TekMol wrote: post #38

Earlier quoted context omitted.

Should the concept of "machines" really be a concern of the DB layer? SQLite already allows multiple connections, so putting it on a server and adding a program that talks a network protocol and proxies the queries to the DB sounds more logical to me?

And after all of that you basically have something that looks like postgres or mysql.

My feeling is that I would have something better.

Because I can use SQLite and its "a file is all you need" approach as long as I don't need multiple machines.

And only bring in the other software (the proxy) when I need it.

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

#43

Post by emseetech »

TekMol wrote: post #40
emseetech wrote: post #33

Earlier quoted context omitted.

The biggest one is redundancy. Architecting with Read replicas is much easier with Postgres than Sqlite because of it's server model. Sqlite on the server is a fantastic starter database. Dead simple to set up, highly performant and scales way higher (vertically) than anyone gives it credit for. But there certainly is a point you'll have to scale out instead of up, and while there are some great solutions for that (r…

Should replication really be a concern of the DB layer? Replication means writing queries which alter the data to multiple machines, right? Shouldn't that be done by a software one level up? Which takes in the queries via some network protocol and then sends them to all machines. That would sound more logical to me.

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.

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

#44

Post by jitl »

munk-a wrote: post #23
simonw wrote: post #7

Earlier quoted context omitted.

I can think of plenty. The most interesting one for me is if you're running a SaaS product like Notion where your users create custom applications that manage their own small schema-based data tables. 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? I'd much rather manage tens of thousands of rows in a…

> 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

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

#45

Post by redwood »

TekMol wrote: post #27

Are there still reasons to use PostgreSQL? I like the simplicity of SQLite's "a file is all you need" approach so much, that I started to converge all my projects to SQLite. So far, I have not come across any roadblocks. Can anyone think of a use case where PostgreSQL is better suited than SQLite?

Concurrency.

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

#47

Post by [deleted] »

emseetech wrote: post #43
TekMol wrote: post #40

Earlier quoted context omitted.

Should replication really be a concern of the DB layer? Replication means writing queries which alter the data to multiple machines, right? Shouldn't that be done by a software one level up? Which takes in the queries via some network protocol and then sends them to all machines. That would sound more logical to me.

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.

[deleted]

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

#48

Post by TekMol »

emseetech wrote: post #43
TekMol wrote: post #40

Earlier quoted context omitted.

Should replication really be a concern of the DB layer? Replication means writing queries which alter the data to multiple machines, right? Shouldn't that be done by a software one level up? Which takes in the queries via some network protocol and then sends them to all machines. That would sound more logical to me.

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.

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

#49

Post by dataspun »

TekMol wrote: post #27

Are there still reasons to use PostgreSQL? I like the simplicity of SQLite's "a file is all you need" approach so much, that I started to converge all my projects to SQLite. So far, I have not come across any roadblocks. Can anyone think of a use case where PostgreSQL is better suited than SQLite?

MySQL has limited spatial data/function support versus PostGIS extension.
Post reply on HN