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

151–160 of 169 posts

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

#151

Post by saberience »

rich_sasha wrote: post #119

I concur that sqlite is quite amazing. That said, I was a heavy user and have grown some skepticism as well: - it is not that hard to lock the db. Usually killing the process that caused the deadlock solves the issue - but you need to identify it / monitor for it. And yes, it happens with WAL too - but when it does happen, it is quite scary. Simply, anything that touches your DB suddenly stops working - can't read, c…

I was in love with sqlite too, until it just started getting randomly corrupted/locked and I kept having to restore it, and I never worked out why it was happening.

I appreciate its "simplicity" but ultimately I hated not knowing why it occasionally just shit the bed and ended up in an unrecoverable state. I also didn't like having to roll my own recovery system for it. Now I just use Postgres for all my hobby projects and it "just works" and I've never had it lock-up or corrupt itself...

Your mileage may vary, but sqlite definitely isn't as stable as it makes it seem.

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

#152

Post by saberience »

Jyaif wrote: post #150

> If you [..] do not fully understand the nuances and limitations of SQLite DO NOT USE IT. So, what are the limitations compared to Postgres?

It doesn't scale out, only up, is a fairly big limitation.

So if you have a single DB server running sqlite and your server goes down, well, your shit is down and there is no failover. I.e. no built in replication or clustering.

It doesn't support multiple simulataneous writes (like PostGres and SqlServer etc).

No stored procedures or functions.

There is no real client/server architecture. i.e. if you have applications on multiple servers which need access to the DB then you're in a bad place. The database has to be embedded along with the application.

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

#153

Post by saberience »

ducktective wrote: post #55

Does anyone have rough numbers (max daily users etc) on viability of SQLite vs PostgreSQL for a typical user-facing webapp or e-commerce application? I know due to some recent update, SQLite can support concurrent reads but still only a single writer. For which cases this would be a problem? Some recommend it's better to start with postgres anyway if you have any remote thoughts of scaling in mind....

Honestly, just use PostGres. It's easy enough and will scale with your business, also it won't randomly lock or corrupt your database (I've had sqlite do this to me several times).

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

#154

Post by andersmurphy »

saberience wrote: post #152
Jyaif wrote: post #150

> If you [..] do not fully understand the nuances and limitations of SQLite DO NOT USE IT. So, what are the limitations compared to Postgres?

It doesn't scale out, only up, is a fairly big limitation. So if you have a single DB server running sqlite and your server goes down, well, your shit is down and there is no failover. I.e. no built in replication or clustering. It doesn't support multiple simulataneous writes (like PostGres and SqlServer etc). No stored procedures or functions. There is no real client/server architecture. i.e. if you have applicatio…

>It doesn't scale out, only up, is a fairly big limitation.

This is the main limitation. That being said you can scale out with projections if event sourcing is your thing.

>It doesn't support multiple simulataneous writes (like PostGres and SqlServer etc).

A process with a single writer tends to be faster because it reduces contention. You only need MVCC in postgres because of the network.

What's even better is you can query across multiple databases seamlessly with ATTACH (https://sqlite.org/lang_attach.html). So it's very easy to split databases (eg: session database, database per company etc). Each database can have its own writer and eliminating contention between data that doesn't need to have atomic transaction across databases.

>No stored procedures or functions.

It's an embedded database the whole thing is effectively a stored procedure. You can even extend SQLite with your own custom functions in your application programming language while it's running (https://sqlite.org/appfunc.html).

In terms of access by multiple applications etc, if it's read access you can create read replicas/projections with litestream etc.

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

#155

Post by cryptonector »

My problem with SQLite3 is the poverty of types and the dynamic typing. I really want the wealth of types that PG brings. But I really like the SQLite3 implementation better than the PG implementation (granted, I'm talking about the core of the RDBMS, not anything to do with networking since SQLite3 lacks networking).

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

#156

Post by cryptonector »

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.

That's true of PG as well.

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

#157

Post by cryptonector »

tomasol wrote: post #72

Author is setting PRAGMA synchronous="normal", meaning fsync is not issued as part of every write tx, but eventually. In order to make the comparison fair it should be set to "full".

100%. TFA was NOT comparing apples to apples. Now that it's been updated the numbers do not look quite so fantastic. The version of PG used matters, too, since the latest adds async I/O support that greatly improves performance.

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

#158

Post by cryptonector »

andersmurphy wrote: post #125
tomasol wrote: post #83

Earlier quoted context omitted.

fsync is the most expensive operation during a write. NORMAL mode means you don't care whether last ~100 ms of transactions before a process crash / VM restart are going to be persisted or not. My suggestion is either to use synchronous="full" or disable `synchronous_commit` on Postgres to avoid comparing apples to oranges. Edit: Also, the example indicates financial transactions. Can you explain why you need seriali…

Very good point I've added an epilogue to the post with updated numbers Really shows the power of dynamic batching.

Thank you for doing this update!

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

#159

Post by andersmurphy »

cryptonector wrote: post #157
tomasol wrote: post #72

Author is setting PRAGMA synchronous="normal", meaning fsync is not issued as part of every write tx, but eventually. In order to make the comparison fair it should be set to "full".

100%. TFA was NOT comparing apples to apples. Now that it's been updated the numbers do not look quite so fantastic. The version of PG used matters, too, since the latest adds async I/O support that greatly improves performance.

SQLit went from 102545 -> 100405

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

#160

Post by hedora »

bombela wrote: post #116
hedora wrote: post #86

Earlier quoted context omitted.

If it’s at 5-15ms of downtime already, you’re in the space where the “zero” downtime FS might actually cause more downtime. In addition to pauses while the snapshot is taken, you’d need to carefully measure things like performance degradation while the snapshot exists (incurring COW costs) and while it’s being GCed in the background. Also, the last time I checked the Linux scheduling quanta was about 10ms, so it’s no…

I am not so sure you know what you are talking about. Feel free to provide some reading material for my education. Why would the scheduler tick frequency even matters for this discussion. Even on a single cpu/core/thread system. For what is worth, the default scheduler tick rate has been 2.5ms since 2005. Earlier this year somebody proposed switching back to 1ms. https://btrfs.readthedocs.io/en/latest/dev/dev-btrfs-d…

Well, I haven’t checked it for a while. Still, try measuring FS latencies during checkpoints, or just write a tight loop program that reads cached data and prints max latencies once an hour. Use the box for other stuff while it runs.
Post reply on HN