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

100k TPS over a billion rows: the unreasonable effectiveness of SQLite

andersmurphy.com

Post reply on HN
Print view View original

31–40 of 169 posts

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#31

Post by maxmcd »

busymom0 wrote: post #28

Previously, I had always used Postgres for database and Rust or NodeJS for my backend. For my new website ( https://limereader.com/ ), I used Swift for my backend, SQLite for Database, Vapor for web server in the Swift app and am self-hosting the site on an old Mac mini. A sqlite related issue I ran into had to do with accessing the SQLite database from multiple threads. Found out a solution easily: for multi-threadi…

This will block threads while waiting for other threads to write. That might work great for your threading model but I usually end up putting the writer in one thread and then other threads send writes to the writer thread.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#32

Post by andersmurphy »

badmonster wrote: post #20

The real insight here is recognizing when network latency is your bottleneck. For many workloads, even a mediocre local database beats a great remote one. The question isn't "which database is best" but "does my architecture need to cross network boundaries at all?"

(author here) yes 100% this. This was never mean't to be a SQLite vs Postgres article per say, more about the fundamental limitations of the network databases in some contexts. Admittedly, at times I felt I struggle to convey this in the article.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#33

Post by busymom0 »

maxmcd wrote: post #31
busymom0 wrote: post #28

Previously, I had always used Postgres for database and Rust or NodeJS for my backend. For my new website ( https://limereader.com/ ), I used Swift for my backend, SQLite for Database, Vapor for web server in the Swift app and am self-hosting the site on an old Mac mini. A sqlite related issue I ran into had to do with accessing the SQLite database from multiple threads. Found out a solution easily: for multi-threadi…

This will block threads while waiting for other threads to write. That might work great for your threading model but I usually end up putting the writer in one thread and then other threads send writes to the writer thread.

I do open 2 connections:

First one for writing with flags:

    SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX
Second one for reading with flags:

    SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX
As you can note, I have SQLITE_OPEN_FULLMUTEX on both of them. Should I only have it for the writing one?

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#34

Post by andersmurphy »

busymom0 wrote: post #28

Previously, I had always used Postgres for database and Rust or NodeJS for my backend. For my new website ( https://limereader.com/ ), I used Swift for my backend, SQLite for Database, Vapor for web server in the Swift app and am self-hosting the site on an old Mac mini. A sqlite related issue I ran into had to do with accessing the SQLite database from multiple threads. Found out a solution easily: for multi-threadi…

You don't need fullmutex if you manage your connections correctly at the application level. I.e ensure each connection is only used from a single thread at a time. I also highly recommend having an MPSC queue for your batch/writes and make them go through a single connection so you don't have to deal with SQLITE_BUSY or SQLITE_LOCKED.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#35

Post by fulafel »

diath wrote: post #11

> Hopefully, this post helps illustrate the unreasonable effectiveness of SQLite as well as the challenges you can run in with Amdahl's law and network databases like postgres. No, it does not. This article first says that normally you would run an application and the database on separate servers and then starts measuring the performance of a locally embedded database. If you have to keep the initial requirement for…

What is says first is: "SQLite is for phones and mobile apps (and the occasional airliner)! For web servers use a proper database like Postgres!"

Though I'd say it's for a broader set of applications than that (embedded apps, desktop apps, low-concurrency server apps etc).

Phones and mobile apps installations of course outnumber web app deployments, and it doesn't say what you paraphrased about servers.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#36

Post by wmanley »

busymom0 wrote: post #28

Previously, I had always used Postgres for database and Rust or NodeJS for my backend. For my new website ( https://limereader.com/ ), I used Swift for my backend, SQLite for Database, Vapor for web server in the Swift app and am self-hosting the site on an old Mac mini. A sqlite related issue I ran into had to do with accessing the SQLite database from multiple threads. Found out a solution easily: for multi-threadi…

Use a connection per-thread instead. By sharing a connection across threads you’ll be limiting concurrency - and transactions won’t work as you’d expect. SQLite connections are not heavy.

Also: use WAL mode and enable mmap.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#37

Post by tomconnors »

Cool stuff as usual, Anders. One of the nice things about running a networked DB is that it makes redeploying the application a bit simpler. You can spin up a new EC2 instance or whatever and once it's online kill the old one. That gets 0 or close to 0 downtime. If the DB is on the same instance, replacing it requires loading up the DB onto the new instance, which seems more error prone than just restarting the app on the original instance, but in my experience that typically incurs downtime or some complicated handoff logic. Have you had to deal with anything like that running sqlite in prd?

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#38

Post by slashdave »

badmonster wrote: post #20

The real insight here is recognizing when network latency is your bottleneck. For many workloads, even a mediocre local database beats a great remote one. The question isn't "which database is best" but "does my architecture need to cross network boundaries at all?"

Sure. Now keep everything in memory and use redis or memcache. Easy to get performance if you change the rules.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#39

Post by BugsJustFindMe »

diath wrote: post #11

> Hopefully, this post helps illustrate the unreasonable effectiveness of SQLite as well as the challenges you can run in with Amdahl's law and network databases like postgres. No, it does not. This article first says that normally you would run an application and the database on separate servers and then starts measuring the performance of a locally embedded database. If you have to keep the initial requirement for…

> If you have to keep the initial requirement for your software, then SQLite is completely out of equation.

No it isn't? You can run a thin sqlite wrapping process on another server just fine. Ultimately all any DB service is, PostgreSQL included, is a request handler and a storage handler. SQLite is just a storage handler, but you can easily put it behind a request handler too.

Putting access to sqlite behind a serial request queue used to be the standard way of implementing multi-threaded writes. That's only spitting distance away from also putting it behind TCP.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#40

Post by ethanwillis »

tuhgdetzhh wrote: post #5
matthewaveryusa wrote: post #3

The only caveat being this assumes all your data can fit on a single machine, and all your processing can fit on one machine. You can get a a u-24tb1.112xlarge with 448 vcores, 24TB RAM for 255/hour and attach 64TB of EBS -- that's a lot of runway.

Or rent a bare-metal machine from hetzner with 2-3x performance per core and 90% less costs[1]. [1] Various HN posts regarding Hetzner vs AWS in terms of costs and perf.

This might be true in terms of direct monetary costs.

I want to like Hetzner but the bureaucratic paper process of interacting with them and continuing to interact with them is just... awful.

Not that the other clouds don't also have their own insane bureaucracies so I guess it's a wash.

I'm just saying, I want a provider that leaves me alone and lets me just throw money at them to do so.

Otherwise, I think I'd rather simply deploy my own oversized server in a colo even with the insanely overpriced hardware prices currently.

edit: And shortly after writing this comment I see: "Microsoft won't let me pay a $24 bill, blocking thousands in Azure spending" https://news.ycombinator.com/item?id=46124930

Post reply on HN