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

71–80 of 169 posts

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

#71

Post by andersmurphy »

Tractor8626 wrote: post #65

Sqlite is very cool. But what was the point of slowing postgress down? > But, wait our transactions are not serialisable, which they need to be if we want consistent transaction processing You either don't know what serializable does or trying to mislead the reader. There is zero reason to use searializable here. > Let's say you have 5ms latency between your app server and your database. 5ms latency is unrealistic. U…

> You either don't know what serializable does or trying to mislead the reader. There is zero reason to use searializable here.

If you're processing financial transactions you want your isolation level to be serialisable. As the order in which the transactions are processed matters.

> 5ms latency is unrealistic. Unless you use wifi or you database is in another datacenter.

Even with 1ms latency. Amdahl's law will still make you cap out at a theoretical 1000 TPS if you have 100% row lock contention.

> No they are not common at all. You probably invented them just to make pg look bad.

I'm confused. I invented transactions? Are you saying you don't use transactions with rollback when you use PG?

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

#73

Post by BenjiWiebe »

bob1029 wrote: post #61

This is great until you encounter a customer with a hard RPO requirement of 0. SQLite has a few replication options, but I would never trust this in high stakes domains over PGSQL/MSSQL/DB2/Oracle/etc.

I'm curious, is an RPO of 0 truly expected or needed? I can easily believe that some places would "require" it.

What kind of data is so critical that the data from a quarter second before catastrophic destruction must be saved?

I guess weapons testing, at least... But that wouldn't be streaming data of that importance for a very large % of time.

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

#74

Post by stickfigure »

sethev wrote: post #46
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…

Right - but SQLite handily beats the case where postgres is on the same box as well. And it's completely reasonable to test technology in the configuration in which it would actually run. As an industry, we seem to have settled on patterns that actually are quite inefficient. There's no problem that requires the solution of doing things inefficiently just because someone said databases should run on a different host.

If you're going to run on more than one piece of hardware, something is going to be remote to your single writer database.

As an industry, we've generally decided against "one big box", for reasons that aren't necessarily performance related.

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

#75

Post by andrewvc »

The HN SQLite worship posts have gotten out of hand. What’s next a post on how appending to files is faster than Kafka?

It’s great that some people have workloads that this is a fit for. What’s more common is the use case managed databases like RDS etc solves for. You have some quantity of data you want to always be there, be available over a network for whatever app(s) need it and want backups, upgrades, access control etc solved for you.

I love SQLite and reach for it for hobby projects, but as a product for general business apps it is quite niche. It has the qualities that make for any popular product on HN, a great getting started experience and a complex maintenance and operational experience.

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

#77

Post by andersmurphy »

jaketoronto wrote: post #62
jaketoronto wrote: post #60

Are you limiting your # of connections to postgres to 8? Is this unnecessarily throttling your throughput? This seems like quite the bottleneck... connection pools are good when your app is overwhelming your db.. but in this case, you really should be trying to put more load on Postgres... I'm concerned that this whole experiment is tainted by this choke point. I would love to see this tested again with a much larger…

To further explain : You mention setting the conn pool to 8 to match your # of cores. That would be fine if you didn't have any sleeps inside of your txns... But the moment you added the sleeps inside the txns, your limit of 8 kills through throughput... because no other thread can access the DB once 8 of them grab connections and start the 20ms of total sleep. Imagine instead if you had 64 connections... you would 8…

A larger pool actually makes the number worse because it adds more contention. I tested it with 64 and all the results were worse. The last example which was 348 TPS drops to 164 TPS!

Tangentially I also highly recommend this article on pool sizing.

https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-...

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

#79

Post by vhodges »

janci wrote: post #63

How does SQLite handle HA setups? The minimum I want is reliable automatic failover in reasonable time for user-facing service. Ideally an active-active setup.

sqlite is just a library (in C)

A few projects:

  * https://github.com/rqlite/rqlite Distributed, fault tolerant cluster
  * https://litestream.io/ Replication to S3 (or compatible) - more disaster recovery than fail over
  * https://fly.io/docs/litefs/ Same Author as litestream).  Distributed replication.  Requires writes to be redirected to the primary.
I am debating Postgres vs sqlite (probably with litestream) for a project right now.

And other than HW redundancy, I can get pretty far by scaling vertically on a single box. And for my app, I could probably (and my users!) live with some occasional downtime (as long as the data is replicated/backed up).

If I get 20-50K users, it'll be a successful venture so I don't need much these days and it will be cheaper and easier to run as well.

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

#80

Post by andersmurphy »

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".

PRAGMA synchronous="normal" is fine if you are in WAL mode. The database cannot be corrupted by power loss unlike in journal mode.

> The synchronous=NORMAL setting provides the best balance between performance and safety for most applications running in WAL mode. You lose durability across power lose with synchronous NORMAL in WAL mode, but that is not important for most applications. Transactions are still atomic, consistent, and isolated, which are the most important characteristics in most use cases.

Post reply on HN