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

I'm all-in on server-side SQLite (2022)

fly.io

Post reply on HN
Print view View original

41–50 of 167 posts

Re: I'm all-in on server-side SQLite (2022)

#41

Post by ketralnis »

ak39 wrote: post #36

SQLite not supporting "stored procedures" is a deal-breaker for me. The idea for stored procs is not to "put the process as close to the data" but simply that we have a single place for language-agnostic encapsulation of data procedures.

I don't think I've ever needed language-agnostic procedures in a project where sqlite is also a fit. I like them both but at different times. I'd love to hear your use case though. Do you have microservices in different languages running on the same machine that share a db file? Or maybe a web + command line interface?

Sqlite's internals actually could support something like this: it has a bytecode engine https://www.sqlite.org/opcode.html that's more oriented around executing query plans and it's missing some pieces (e.g. it has no stack, only registers) but much of the machinery is there to expand it to stored procedures

Re: I'm all-in on server-side SQLite (2022)

#42

Post by andrewstuart »

dimgl wrote: post #31
andrewstuart wrote: post #23

I can’t see any valid reason not to use Postgres at the back end, unless you are in some sort of environment such as embedded or cloudflare workers that requires it. Or if you need a graph database there are better choices than Postgres. Postgres is good on multi core, incredibly feature rich, multi user, supported by everything, lightweight and has all the tools for production workload and management. All stuff that…

> any valid reason Well, cost, right? Cost is a reason why someone may not want to use a traditional RDBMS. AWS RDS and GCP Cloud SQL aren't exactly the cheapest solutions out there.

Postgres is free. Put it on a computer. Cost is certainly not an argument against Postgres cause it can run on any back end that runs Linux.

Re: I'm all-in on server-side SQLite (2022)

#44

Post by meitham »

endisneigh wrote: post #32
meitham wrote: post #24

I recently wrote a production system that uses SQLite as the main backend. SQLite is in memory in this case and its entire state gets rebuilt from Kafka on start. The DB receives about 2 updates a second, wrapped with rest api aiohttp and odata filters. It has been able to handle close to 9k requests/second ands it’s a primary system in a financial institution. So yes SQLite is fully capable prod db.

You’re using SQLite and Kafka? Very ironic.

In large organisations you often have no choice of the type of queue between your team and other teams. That being said there’s nothing wrong with Kafka and the ability to seek back to the earliest timestamp since midnight and being able to rebuild our state from that is a godsend feature, in comparison to other queues. This means we can make our application stateless or at least afford to lose the state and be able to build it quickly from Kafka.

Re: I'm all-in on server-side SQLite (2022)

#45

Post by paulryanrogers »

meitham wrote: post #24

I recently wrote a production system that uses SQLite as the main backend. SQLite is in memory in this case and its entire state gets rebuilt from Kafka on start. The DB receives about 2 updates a second, wrapped with rest api aiohttp and odata filters. It has been able to handle close to 9k requests/second ands it’s a primary system in a financial institution. So yes SQLite is fully capable prod db.

So your source of truth is ... Kafka?

Re: I'm all-in on server-side SQLite (2022)

#46

Post by pstuart »

ak39 wrote: post #36

SQLite not supporting "stored procedures" is a deal-breaker for me. The idea for stored procs is not to "put the process as close to the data" but simply that we have a single place for language-agnostic encapsulation of data procedures.

What language(s) would the stored procedures be, and how would that look in keeping with the ethos of the project?

Their reasoning for not doing this is not unreasonable, but it certainly would be cool if such functionality existed.

Re: I'm all-in on server-side SQLite (2022)

#48

Post by endisneigh »

meitham wrote: post #44
endisneigh wrote: post #32

Earlier quoted context omitted.

You’re using SQLite and Kafka? Very ironic.

In large organisations you often have no choice of the type of queue between your team and other teams. That being said there’s nothing wrong with Kafka and the ability to seek back to the earliest timestamp since midnight and being able to rebuild our state from that is a godsend feature, in comparison to other queues. This means we can make our application stateless or at least afford to lose the state and be able…

Kafka is just fine. I just thought it was funny that SQLite would be involved at all.

Re: I'm all-in on server-side SQLite (2022)

#49

Post by [deleted] »

kentonv wrote: post #35
andrewstuart wrote: post #23

I can’t see any valid reason not to use Postgres at the back end, unless you are in some sort of environment such as embedded or cloudflare workers that requires it. Or if you need a graph database there are better choices than Postgres. Postgres is good on multi core, incredibly feature rich, multi user, supported by everything, lightweight and has all the tools for production workload and management. All stuff that…

Postgres is great at what it does, but it is extremely inefficient for storing a small amount of data, e.g. kilobytes or a few megabytes. sqlite, on the other hand, scales nicely all the way down to a few kb. This matters for cloud in that it means with Postgres you cannot take a "lots of small databases" strategy, e.g. database per user or database per document. You pretty much have to group a lot of data into one b…

[deleted]

Re: I'm all-in on server-side SQLite (2022)

#50

Post by zaphar »

andrewstuart wrote: post #42
dimgl wrote: post #31

Earlier quoted context omitted.

> any valid reason Well, cost, right? Cost is a reason why someone may not want to use a traditional RDBMS. AWS RDS and GCP Cloud SQL aren't exactly the cheapest solutions out there.

Postgres is free. Put it on a computer. Cost is certainly not an argument against Postgres cause it can run on any back end that runs Linux.

Out of not cheaper to run a postgres server than an embedded sqlite DB. There is more to the cost than just the software license.
Post reply on HN