9 ms·

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

423 points by speckx 1y ago

deleted 1y ago
[deleted]
matthewaveryusa 1y ago
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.
- tuhgdetzhh 1y ago
  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.
  - deleted 1y ago
    [deleted]
  - tetha 1y ago
    In my experience, a decently managed database scales very hard. 3x EX44 running Patroni + PostgreSQL would give you 64GB of working memory, at least 512 GB NVMe of dataset (configurable with more for a one-time fee) at HA + 1 maintenance node. Practically speaking, that would have carried the first 5 - 10 years of production at the company I work at with ease, for 120 Euros hardware cost/month + a decent sysadmin. I also know quite a few companies who toss 3-4x 20k - 30k at DELL every few years to get a database cluster on-prem so that database performance ceases to be a problem (unless the application has bad queries).
  - ethanwillis 1y ago
    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 https://news.ycombinator.com/item?id=46124930
    - Nextgrid 1y ago
      > I want a provider that leaves me alone and lets me just throw money at them to do so. That’s been my experience with Hetzner. A lot of people get butthurt that a business dares to verify who they’re dealing with as to filter out the worst of the worst (budget providers always attract those), but as long as you don’t mind the reasonable requirement to verify your ID/passport they’re hands-off beyond that.
      - ethanwillis 1y ago
        That's fair and I don't have any major issues with that. I guess my concern on the bureaucracy is if you are unlucky enough to get flagged as a false positive it can be an annoying experience. And I can't really blame them too hard for having to operate that way in an environment of bad actors. You're definitely right that the budget providers do attract the types of people trying to do bad things/exploit them in some way.
    - earthnail 1y ago
      Can you elaborate on what the bureaucracy is you experienced? I'm a Hetzner customer since last month and so far I thoroughly enjoy it. Have not encountered any bureaucracy yet.
      - ethanwillis 1y ago
        I think I was still being a bit too harsh even after throwing into my comment that other providers aren't perfect either. But basically after the initial paperwork I had some issues with my account getting flagged even though I wasn't using it 99.999% of the time. It's not a huge deal for me because I wasn't trying them out for anything serious. I just questioned how often that might happen if I was actually using it seriously and what kind of headaches it could cause me while re-verifying everything with them. From people I know if everything is going good then their service is great. Server performance is good, pricing is good, etc.
        vachina 1y ago
  - cortesoft 1y ago
    There are no Hetzner servers that have 24TBs of RAM
  - closeparen 1y ago
    It’s too bad that there does not seem to be a comparable provider with datacenters in North America.
- DenisM 1y ago
  Scale-up solves a lot of problems for stable workloads. But elasticity is poor, so you either live with overprovisinoed capacity (multiples, not percentages) or fail under spiky load which often time is the most valuable moment (viral traffic, Black Friday, etc). No one has solved this problem. Scale out is typically more elastic, at least for reads.
  - kragen 1y ago
    That's a good point, but when one laptop can do 102545 transactions per second, overprovisioned capacity is kind of a more reasonable thing to use than back when you needed an Amdahl mainframe to hit 100 transactions per second.
    - DenisM 1y ago
      As compute becomes cheaper your argument becomes more and more true. But it only works if workloads remain fixed. If workloads grow at similar rates you’re back to the same problem.
      - kragen 1y ago
        Well, it doesn't work for the newly added workloads. But for the most part we instead have the same workloads performed less efficiently.
  - CuriouslyC 1y ago
    I love hetzner for internal resources because they're not spikey. For external stuff I like to do co-processing, you can load balance to cloudflare/aws/gcp services like containers/Run/App Runner/etc.
  - masterj 1y ago
    I suspect that for a large number of orgs accepting over-provisioning would be significantly cheaper than the headcount required for a more sophisticated approach while allowing faster movement due to lower overall complexity
- deleted 1y ago
  [deleted]
- embedding-shape 1y ago
  > The only caveat being this assumes all your data can fit on a single machine Does my data fit in RAM? https://yourdatafitsinram.net/ https://yourdatafitsinram.net/ Not sure using EC2/AWS/Amazon is a good example here, if you're squeezing for large single-node performance you most certainly go for dedicated servers, or at least avoid vCPUs like a plague.
  - jandrese 1y ago
    That site is a bit questionable. I entered "64TB" as the answer and it was very happy to show me a bunch of servers that maxed out at 6 or 8TB. Even the one server that listed 64TB of RAM might be questionable since it's not leaving room for the OS or your applications. That said 64 TB is a gargantuan amount of data, so I'm not too worked up over it not fitting in RAM. Lord help you if you have a power outage and have to reload the data from disk.
  - ErroneousBosh 1y ago
    Getting on for ten year's worth of forum posts on https://rangerovers.pub/ https://rangerovers.pub/ comes to about 32MB of SQL dump. So yeah, easily.
  - paulddraper 1y ago
    How does 25 TiB fit in RAM when the max machine has 24 TB?
- adityaathalye 1y ago
  Heh, the documentation calls out the limits. Maximum (theoretical) DB size is 281TB: https://sqlite.org/limits.html https://sqlite.org/limits.html > This particular upper bound is untested since the developers do not have access to hardware capable of reaching this limit. > However, tests do verify that SQLite behaves correctly and sanely when a database reaches the maximum file size of the underlying filesystem (which is usually much less than the maximum theoretical database size) and when a database is unable to grow due to disk space exhaustion.
- kiitos 1y ago
  and that your application doesn't need to be resilient to host or network faults
- cryptonector 1y ago
  That's true of PG as well.
westurner 1y ago
That's a helpful TPS Report. TIL `SAVEPOINT` can occur in a BEGIN ... END SQLite transaction, and that works with optimizing batch size on a particular node with a given load. Is there a solution for SQLite WAL corruption? From https://news.ycombinator.com/item?id=45133444 https://news.ycombinator.com/item?id=45133444 : > "PSA: SQLite WAL checksums fail silently and may lose data" https://news.ycombinator.com/item?id=44672902 https://news.ycombinator.com/item?id=44672902 > sqlite-parquet-vtable, [...]
- matharmin 1y ago
  As mentioned in those threads, there is no SQLite WAL corruption if you have a working disk & file system. If you don't, then all bets are off - SQLite doesn't protect you against that, and most other databases won't either. And nested transactions (SAVEPOINT) won't have have any impact on this - all it does in this form is reduce the number of transactions you have.
  - westurner 1y ago
    > working disk & file system And a working ECC or non-ECC RAM bus, and [...]. How bad is recovery from WAL checksum / journal corruption [in SQLite] [with batching at 100k TPS]? And should WAL checksums be used for distributed replication "bolted onto" SQLite? >> (How) Should merkle hashes be added to sqlite for consistency? How would merkle hashes in sqlite differ from WAL checksums? SQLite would probably still be faster over the network with proper Merkleization
alexwennerberg 1y ago
Does anyone have examples of organizations that have leveraged SQLite and written about their experience? I've read a lot of theory and benchmarks about it lately and it seems extremely impressive, but I'm wondering if anyone has written about pushing it to its limits "in production"
- andersmurphy 1y ago
  https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a-single-server https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a... Linked at the bottom of the article is the most extreme I've seen.
  - alexwennerberg 1y ago
    Thank you! I read this a long time ago and could never remember it, thinking it was Shopify that wrote it.
  - yolo3000 1y ago
    Maybe they should have stuck with traditional tech and used their talent on something else. Stock is down 96%
diath 1y ago
> 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 your software, then SQLite is completely out of equation. If you can change the requirement, then you can achieve similar performance by tuning the local PGSQL instance -- and then it also becomes a valuation of features and not just raw throughput. I'm not saying SQLite is not an option either, but this article seems confusing in that it compares two different problems/solutions.
- embedding-shape 1y ago
  > If you have to keep the initial requirement for your software, then SQLite is completely out of equation. It'd be a very short article if so, don't you think? Full article would be something like: "Normally you'd have a remote connection to the database, and since we're supposed to test SQLite's performance, and SQLite is embedded, it doesn't compare. Fin"
  - stonemetal12 1y ago
    The table of data at the end of the article has 7 lines, only one has data for both DBs. What was the point of setting up the comparison if there is no comparison made?
    - andersmurphy 1y ago
      Because it shows that a network RDBS database cannot get you out of this predicament.
- andersmurphy 1y ago
  Paradoxically, raw throughput matters a lot more if you are going to scale on a single box. SQLite is 10x PG on a single box in this example. Considering databases tend to be the bottle neck that can take you an order of magnitude further. PG on the same server will also be slower the more complex the transaction as unix sockets are still going to be considerably slower than a function call. The other thing to point out is in this article is that the PG network example CANNOT scale horizontally due to the power law. You can throw a super cluster at the problem and still fundamentally do around 1000 TPS.
pmdulaney 1y ago
Prefer "surprising" to "unreasonable".
- jhoechtl 1y ago
  But that wouldn't be cool
weitendorf 1y ago
I've been working on a hybrid protobuf ORM/generic CRUD server based on sqlite (code at https://github.com/accretional/collector https://github.com/accretional/collector - forgive the documentation. I'm working on a container-based agent project and also trialling using agents heavily to write the individual features. It's working pretty well but the agents have been very zealous at documenting things lol). This is my first real project using sqlite and we've hit some similarly cool benchmarks: * 5-15ms downtime to backup a live sqlite db with a realistic amount of data for a crud db * Capable of properly queueing hundreds of read/write operations when temporarily unavailable due to a backup * e2e latency of basically 1ms for CRUD operations, including proto SerDe * WAL lets us do continuous, streaming, chunked backups! Previously I'd only worked with Postgres and Spanner. I absolutely love sqlite so far - would still use Spanner for some tasks with an infinite budget but once we get Collector to implement partitions I don't think I would ever use Postgres again.
- bombela 1y ago
  > * 5-15ms downtime to backup a live sqlite db with a realistic amount of data for a crud db Did you consider using a filesystem with atomic snapshots? For example sqlite with WAL on BTRFS. As far as I can tell, this should have a decent mechanical sympathy. edit: I didn't really explain myself. This is for zero downtime backups. Snapshot, backup at your own pace, delete the snapshot.
  - hedora 1y ago
    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 not clear backups are going to even be the maximum duration downtime while the system is healthy.
    - bombela 1y ago
badmonster 1y ago
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?"
- andersmurphy 1y ago
  (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.
- slashdave 1y ago
  Sure. Now keep everything in memory and use redis or memcache. Easy to get performance if you change the rules.
  - koakuma-chan 1y ago
    You can use SQLite for persistence and a hash map as cache. Or just go for Mongo since it's web scale.
  - Yodan2025 1y ago
    yep, then add an AWS worker in-between
  - SJC_Hacker 1y ago
    SQLite can also do in memory
    - slashdave 1y ago
      Yeah, very good point. It all comes down to requirements. If you require persistence, then we can start talking about redundancy and backup, and then suddenly this performance metric becomes far less relevant.
dmezzetti 1y ago
I've used SQLite as the content storage engine for years with TxtAI. It works great. Also plenty of good add-ons for it such as sqlite-vec for storing vectors. It can take you pretty far and maybe it's all you need in many circumstances.
busymom0 1y ago
Previously, I had always used Postgres for database and Rust or NodeJS for my backend. For my new website (https://limereader.com/ 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-threading use, SQLite needed to be initialized with a `SQLITE_OPEN_FULLMUTEX` flag. Since then, the website has been running seamlessly for about 3 weeks now.
- maxmcd 1y ago
  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.
  - busymom0 1y ago
    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?
    - maxmcd 1y ago
      Oh nice, yes I think your threads should be able to perform reads concurrently when the write lock is not held. Would make sure you are in WAL mode as well, since I think that will improve your concurrency.
- andersmurphy 1y ago
  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.
tomconnors 1y ago
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?
- andersmurphy 1y ago
  Oh there are a bunch of considerations. You're going to want persistent storage on your server, not ephemeral. You'll also want NVME. A lot of the time you're going to end up on bare metal running a single server anyway. You're going to have down time for migrations unless you're very clever with your schema and/or replicas. Litestream for me at least is what makes SQLite viable for a web app as prior to that there wasn't a good replication story. With litestream it's much easier to have a backup on standby. That being said where I have used it in production some amount of downtime has been acceptable so mileage may vary.
  - kiitos 1y ago
    > You're going to have down time for migrations unless you're very clever with your schema and/or replicas. probably worth stating these kinds of design considerations/assumptions up-front i'm sure lots of applications are fine with "downtime for [database] migrations" but lots more are definitely not, especially those interested in synthetic metrics like TPS
    - andersmurphy 1y ago
      I'd argue the opposite most applications are fine with an hour of downtime a month and arguably much more downtime then that. The recent AWS and Cloudflare outages have proven that. You can achieve zero downtime with Sqlite if you really need to. TPS is not a synthetic metric when you cap out at 100 TPS because of Amdahl's law and your users having a power distribution.
koakuma-chan 1y ago
Alternative title: SQLite is All You Need
deleted 1y ago
[deleted]
ducktective 1y ago
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....
- saberience 1y ago
  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).
gausswho 1y ago
SQLite db's do not reliably survive Syncthing across multiple devices. Until that's true I am always seeking a DB design that does.
jaketoronto 1y ago
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 connection pool. (Also, might be nice for you to share what the CPU and thread usage on the Postgres side was during this). (Side note: I'm a big fan of sqlite.. but I've run into lots of performance bottlenecks caused by arbitrarily low connection pools and the like)
- jaketoronto 1y ago
  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 8x your throughput... What if you were to go even higher? At some point you might start overloading the DB... at that point, you could consider tuning the db to accept more connections... or... maybe you've truly reached the DB's peak performance limit. I just don't think that 8 connections represents that limit... you need to do everything you can to open up your client config until you reach PG's limitations.
  - andersmurphy 1y ago
    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-Sizing https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-...
  - andersmurphy 1y ago
    Added an epilogue that covers larger connection pools and the results.
bob1029 1y ago
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.
- BenjiWiebe 1y ago
  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.
  - wbsun 1y ago
    My experience: customers with $$$ will always believe they are very important, so important that losing a single bit is the end of world. So you may not want to convince customers waving huge $$$ checks that their data are not that important. But instead, providing options to keep them once they realize that: their pockets are not that deep, and they are also totally ok losing some data.
janci 1y ago
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.
- vhodges 1y ago
  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.
  - otoolep 1y ago
    rqlite creator here, happy to answer any questions.
  - andersmurphy 1y ago
    Yeah if you're comfortable scaling vertically and potentially a little downtime. Sqlite massively simplifies your ops, backups litestream is fantastic. It's also as you mentioned dirt cheap (VPS or a hetzner box).
- nchmy 1y ago
  there's various options now, but im most interested in Marmot - which is multi-master and just came out of a 2 year hibernation with a big overhaul that introduced a native gossip mechanism to replace NATS/Raft https://github.com/maxpert/marmot/ https://github.com/maxpert/marmot/
jcadam 1y ago
I've been getting some good results from sqlite-vec on my current project. I need to look at geospatial extensions next.
Tractor8626 1y ago
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. Unless you use wifi or you database is in another datacenter. > I'm talking about transactions per second, specifically interactive transactions that are common when building web applications No they are not common at all. You probably invented them just to make pg look bad.
- andersmurphy 1y ago
  > 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?
  - Tractor8626 1y ago
    > If you're processing financial transactions you want your isolation level to be serialisable. As the order in which the transactions are processed matters. So you don't know what serializable level is. > 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. So why use 5ms and 10ms for examples? > I'm confused. I invented transactions? "Interactive" transactions.
- gunnarmorling 1y ago
  > No they are not common at all. You probably invented them just to make pg look bad. Interactive transactions are a well-defined concept in database technology, exactly with the semantics described by the author: transactions with multiple queries, with application logic in between, for instance taking a result from one query, processing it, and running a second query with the outcome of that processing as input. That said, the example in the blog post feels a bit contrived to me, the question being whether that transaction could be structured in a why so that both updates run right after each other towards the end of the transaction, thus significantly reducing the duration of the row lock being held (which is not to say that the general problem described in the article doesn't exist, it does for sure).
tomasol 1y ago
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".
- deleted 1y ago
  [deleted]
- andersmurphy 1y ago
  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.
  - tomasol 1y ago
    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 serializability but not durability?
    - andersmurphy 1y ago
      Very good point I've added an epilogue to the post with updated numbers Really shows the power of dynamic batching.
      - cryptonector 1y ago
        Thank you for doing this update!
andrewvc 1y ago
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.
- abrookewood 1y ago
  Honestly, I think it's a reaction to all of the over-optimisation that everyone gets caught up in - immediately starting on AWS, Kubernetes and micro-services. Most of the projects people work on will never reach the performance limits of SQLite and a single server. I'm not saying that there aren't valid reasons to use AWS & clustered solutions etc, but we shouldn't always take that as our starting position.
shadowgovt 1y ago
Good. I feel like people keep discovering this principle that "When the engine does far, far fewer things, it's faster," but I certainly won't bash on people learning this, however they get there.
CyberDildonics 1y ago
Those numbers are just called a database.
deleted 1y ago
[deleted]
SJC_Hacker 1y ago
SQLite is cool. You can create an in memory DB ands it’s super fast. Better stick with read only though With a few lines of code you can even copy an arbitrary DB from disk to memory.
NorwegianDude 1y ago
This is very misleading. The secure defaults for sqlite is changed, so commits are not actually written to the disk. Running sqlite like this will cause data loss on os crash or power loss.
- andersmurphy 1y ago
  I've added numbers for synchronous FULL to the article for those who are interested.
deleted 1y ago
[deleted]
rich_sasha 1y ago
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, can't write. - in some cases, WAL does not checkpoint. This leads to drastic growth in the size of the WAL file, and down the line in catastrophic slowdown of queries - things that take 10ms suddenly take 10 seconds. In my particular case, no tweaking of SQLite params fixed it. I had to monitor for it, and periodically force WAL file to be rolled into the main DB. - all of this gets harder on Windows, where eg.you cannot just 'lsof' a file. - the performance stats change somewhat for the worse in the cloud on drives that look local but actually aren't. Of course that is not sqlite's fault, but the blazing fast performance doesn't apply to all commonly encountered environments that look like real local drives. I'm not dissing SQLite, I use it despite these shortcomings. Equally, I'm happy to reach for something like Postgres, which, well, hasn't burned me yet. EDIT I should add that despite all this I never managed to corrupt the DB, or break any of the SQL promises - never messed up indices, never saw broken ACID compliance etc. And that's a massive endorsement, on reflection.
- asa400 1y ago
  From your experience, would you call these behaviors bugs, or are they more known issues that result from SQLites specific implementation quirks? What kinds of workloads were you throwing at it when these types of issues happened? Asking as someone who really enjoys and respects SQLite but hasn't encountered these specific behaviors before.
  - rich_sasha 1y ago
    I was pushing SQLite quite hard. My DB was at peak 25GB or so. Occasional queries of O(1e6) rows while simultaneously inserting etc. Many readers and a few writers too. Id expect some degradation, sure, but Id say it wasn't very graceful. I think, however, I was well within the parameters that SQLite maximalists would describe as within th envelope of heavy but fine usage. YMMV. I found a very small number of people online with the exact same issues. Enough to know I'm not hallucinating, but not enough to find good support for this :/ but, TLDR, forcing WAL truncation regularly fixed it all. But I had to do it from an external process on a heartbeat, etc etc
andersmurphy 1y ago
(author here) Thank you for the fantastic discussion so far. I've added an Epilogue to the article that covers: - Pragma synchronous "FULL" - Giving PG a bigger connection pool
cluckindan 1y ago
The first example uses a pattern where application code is run in the middle of a db transaction. That is almost certainly an anti-pattern, unless the computation is very lightweight - and even in that case it could probably be done in SQL.
- andersmurphy 1y ago
  You can definitely mitigate the power/amdahl's law problems with pure SQL and/or triggers/stored procedures. I mention that in the article. However, sacrificing interactive transactions is rough and not always possible. It's worth keeping in mind that the problem here is not heavyweight computation, but the network latency to the database. Your computations can be fast, but if your latency is still at best 1ms for each time you go to the database during that transaction. Every ms that high contention row is locked is kept your throughput degrades dramatically.
Jyaif 1y ago
> If you [..] do not fully understand the nuances and limitations of SQLite DO NOT USE IT. So, what are the limitations compared to Postgres?
- saberience 1y ago
  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.
  - andersmurphy 1y ago
    >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 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 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.
cryptonector 1y ago
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).

423 points by speckx 1y ago

deleted 1y ago

[deleted]

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.

tuhgdetzhh 1y ago
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.
- deleted 1y ago
  [deleted]
- tetha 1y ago
  In my experience, a decently managed database scales very hard. 3x EX44 running Patroni + PostgreSQL would give you 64GB of working memory, at least 512 GB NVMe of dataset (configurable with more for a one-time fee) at HA + 1 maintenance node. Practically speaking, that would have carried the first 5 - 10 years of production at the company I work at with ease, for 120 Euros hardware cost/month + a decent sysadmin. I also know quite a few companies who toss 3-4x 20k - 30k at DELL every few years to get a database cluster on-prem so that database performance ceases to be a problem (unless the application has bad queries).
- ethanwillis 1y ago
  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 https://news.ycombinator.com/item?id=46124930
  - Nextgrid 1y ago
    > I want a provider that leaves me alone and lets me just throw money at them to do so. That’s been my experience with Hetzner. A lot of people get butthurt that a business dares to verify who they’re dealing with as to filter out the worst of the worst (budget providers always attract those), but as long as you don’t mind the reasonable requirement to verify your ID/passport they’re hands-off beyond that.
    - ethanwillis 1y ago
      That's fair and I don't have any major issues with that. I guess my concern on the bureaucracy is if you are unlucky enough to get flagged as a false positive it can be an annoying experience. And I can't really blame them too hard for having to operate that way in an environment of bad actors. You're definitely right that the budget providers do attract the types of people trying to do bad things/exploit them in some way.
  - earthnail 1y ago
    Can you elaborate on what the bureaucracy is you experienced? I'm a Hetzner customer since last month and so far I thoroughly enjoy it. Have not encountered any bureaucracy yet.
    - ethanwillis 1y ago
      I think I was still being a bit too harsh even after throwing into my comment that other providers aren't perfect either. But basically after the initial paperwork I had some issues with my account getting flagged even though I wasn't using it 99.999% of the time. It's not a huge deal for me because I wasn't trying them out for anything serious. I just questioned how often that might happen if I was actually using it seriously and what kind of headaches it could cause me while re-verifying everything with them. From people I know if everything is going good then their service is great. Server performance is good, pricing is good, etc.
      - vachina 1y ago
- cortesoft 1y ago
  There are no Hetzner servers that have 24TBs of RAM
- closeparen 1y ago
  It’s too bad that there does not seem to be a comparable provider with datacenters in North America.
DenisM 1y ago
Scale-up solves a lot of problems for stable workloads. But elasticity is poor, so you either live with overprovisinoed capacity (multiples, not percentages) or fail under spiky load which often time is the most valuable moment (viral traffic, Black Friday, etc). No one has solved this problem. Scale out is typically more elastic, at least for reads.
- kragen 1y ago
  That's a good point, but when one laptop can do 102545 transactions per second, overprovisioned capacity is kind of a more reasonable thing to use than back when you needed an Amdahl mainframe to hit 100 transactions per second.
  - DenisM 1y ago
    As compute becomes cheaper your argument becomes more and more true. But it only works if workloads remain fixed. If workloads grow at similar rates you’re back to the same problem.
    - kragen 1y ago
      Well, it doesn't work for the newly added workloads. But for the most part we instead have the same workloads performed less efficiently.
- CuriouslyC 1y ago
  I love hetzner for internal resources because they're not spikey. For external stuff I like to do co-processing, you can load balance to cloudflare/aws/gcp services like containers/Run/App Runner/etc.
- masterj 1y ago
  I suspect that for a large number of orgs accepting over-provisioning would be significantly cheaper than the headcount required for a more sophisticated approach while allowing faster movement due to lower overall complexity
deleted 1y ago
[deleted]
embedding-shape 1y ago
> The only caveat being this assumes all your data can fit on a single machine Does my data fit in RAM? https://yourdatafitsinram.net/ https://yourdatafitsinram.net/ Not sure using EC2/AWS/Amazon is a good example here, if you're squeezing for large single-node performance you most certainly go for dedicated servers, or at least avoid vCPUs like a plague.
- jandrese 1y ago
  That site is a bit questionable. I entered "64TB" as the answer and it was very happy to show me a bunch of servers that maxed out at 6 or 8TB. Even the one server that listed 64TB of RAM might be questionable since it's not leaving room for the OS or your applications. That said 64 TB is a gargantuan amount of data, so I'm not too worked up over it not fitting in RAM. Lord help you if you have a power outage and have to reload the data from disk.
- ErroneousBosh 1y ago
  Getting on for ten year's worth of forum posts on https://rangerovers.pub/ https://rangerovers.pub/ comes to about 32MB of SQL dump. So yeah, easily.
- paulddraper 1y ago
  How does 25 TiB fit in RAM when the max machine has 24 TB?
adityaathalye 1y ago
Heh, the documentation calls out the limits. Maximum (theoretical) DB size is 281TB: https://sqlite.org/limits.html https://sqlite.org/limits.html > This particular upper bound is untested since the developers do not have access to hardware capable of reaching this limit. > However, tests do verify that SQLite behaves correctly and sanely when a database reaches the maximum file size of the underlying filesystem (which is usually much less than the maximum theoretical database size) and when a database is unable to grow due to disk space exhaustion.
kiitos 1y ago
and that your application doesn't need to be resilient to host or network faults
cryptonector 1y ago
That's true of PG as well.

westurner 1y ago

That's a helpful TPS Report. TIL `SAVEPOINT` can occur in a BEGIN ... END SQLite transaction, and that works with optimizing batch size on a particular node with a given load. Is there a solution for SQLite WAL corruption? From https://news.ycombinator.com/item?id=45133444 https://news.ycombinator.com/item?id=45133444 : > "PSA: SQLite WAL checksums fail silently and may lose data" https://news.ycombinator.com/item?id=44672902 https://news.ycombinator.com/item?id=44672902 > sqlite-parquet-vtable, [...]

matharmin 1y ago
As mentioned in those threads, there is no SQLite WAL corruption if you have a working disk & file system. If you don't, then all bets are off - SQLite doesn't protect you against that, and most other databases won't either. And nested transactions (SAVEPOINT) won't have have any impact on this - all it does in this form is reduce the number of transactions you have.
- westurner 1y ago
  > working disk & file system And a working ECC or non-ECC RAM bus, and [...]. How bad is recovery from WAL checksum / journal corruption [in SQLite] [with batching at 100k TPS]? And should WAL checksums be used for distributed replication "bolted onto" SQLite? >> (How) Should merkle hashes be added to sqlite for consistency? How would merkle hashes in sqlite differ from WAL checksums? SQLite would probably still be faster over the network with proper Merkleization

alexwennerberg 1y ago

Does anyone have examples of organizations that have leveraged SQLite and written about their experience? I've read a lot of theory and benchmarks about it lately and it seems extremely impressive, but I'm wondering if anyone has written about pushing it to its limits "in production"

andersmurphy 1y ago
https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a-single-server https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a... Linked at the bottom of the article is the most extreme I've seen.
- alexwennerberg 1y ago
  Thank you! I read this a long time ago and could never remember it, thinking it was Shopify that wrote it.
- yolo3000 1y ago
  Maybe they should have stuck with traditional tech and used their talent on something else. Stock is down 96%

diath 1y ago

> 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 your software, then SQLite is completely out of equation. If you can change the requirement, then you can achieve similar performance by tuning the local PGSQL instance -- and then it also becomes a valuation of features and not just raw throughput. I'm not saying SQLite is not an option either, but this article seems confusing in that it compares two different problems/solutions.

embedding-shape 1y ago
> If you have to keep the initial requirement for your software, then SQLite is completely out of equation. It'd be a very short article if so, don't you think? Full article would be something like: "Normally you'd have a remote connection to the database, and since we're supposed to test SQLite's performance, and SQLite is embedded, it doesn't compare. Fin"
- stonemetal12 1y ago
  The table of data at the end of the article has 7 lines, only one has data for both DBs. What was the point of setting up the comparison if there is no comparison made?
  - andersmurphy 1y ago
    Because it shows that a network RDBS database cannot get you out of this predicament.
andersmurphy 1y ago
Paradoxically, raw throughput matters a lot more if you are going to scale on a single box. SQLite is 10x PG on a single box in this example. Considering databases tend to be the bottle neck that can take you an order of magnitude further. PG on the same server will also be slower the more complex the transaction as unix sockets are still going to be considerably slower than a function call. The other thing to point out is in this article is that the PG network example CANNOT scale horizontally due to the power law. You can throw a super cluster at the problem and still fundamentally do around 1000 TPS.

pmdulaney 1y ago

Prefer "surprising" to "unreasonable".

jhoechtl 1y ago
But that wouldn't be cool

weitendorf 1y ago

I've been working on a hybrid protobuf ORM/generic CRUD server based on sqlite (code at https://github.com/accretional/collector https://github.com/accretional/collector - forgive the documentation. I'm working on a container-based agent project and also trialling using agents heavily to write the individual features. It's working pretty well but the agents have been very zealous at documenting things lol). This is my first real project using sqlite and we've hit some similarly cool benchmarks: * 5-15ms downtime to backup a live sqlite db with a realistic amount of data for a crud db * Capable of properly queueing hundreds of read/write operations when temporarily unavailable due to a backup * e2e latency of basically 1ms for CRUD operations, including proto SerDe * WAL lets us do continuous, streaming, chunked backups! Previously I'd only worked with Postgres and Spanner. I absolutely love sqlite so far - would still use Spanner for some tasks with an infinite budget but once we get Collector to implement partitions I don't think I would ever use Postgres again.

bombela 1y ago
> * 5-15ms downtime to backup a live sqlite db with a realistic amount of data for a crud db Did you consider using a filesystem with atomic snapshots? For example sqlite with WAL on BTRFS. As far as I can tell, this should have a decent mechanical sympathy. edit: I didn't really explain myself. This is for zero downtime backups. Snapshot, backup at your own pace, delete the snapshot.
- hedora 1y ago
  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 not clear backups are going to even be the maximum duration downtime while the system is healthy.
  - bombela 1y ago

badmonster 1y ago

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

andersmurphy 1y ago
(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.
slashdave 1y ago
Sure. Now keep everything in memory and use redis or memcache. Easy to get performance if you change the rules.
- koakuma-chan 1y ago
  You can use SQLite for persistence and a hash map as cache. Or just go for Mongo since it's web scale.
- Yodan2025 1y ago
  yep, then add an AWS worker in-between
- SJC_Hacker 1y ago
  SQLite can also do in memory
  - slashdave 1y ago
    Yeah, very good point. It all comes down to requirements. If you require persistence, then we can start talking about redundancy and backup, and then suddenly this performance metric becomes far less relevant.

dmezzetti 1y ago

I've used SQLite as the content storage engine for years with TxtAI. It works great. Also plenty of good add-ons for it such as sqlite-vec for storing vectors. It can take you pretty far and maybe it's all you need in many circumstances.

busymom0 1y ago

Previously, I had always used Postgres for database and Rust or NodeJS for my backend. For my new website (https://limereader.com/ 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-threading use, SQLite needed to be initialized with a `SQLITE_OPEN_FULLMUTEX` flag. Since then, the website has been running seamlessly for about 3 weeks now.

maxmcd 1y ago
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.
- busymom0 1y ago
  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?
  - maxmcd 1y ago
    Oh nice, yes I think your threads should be able to perform reads concurrently when the write lock is not held. Would make sure you are in WAL mode as well, since I think that will improve your concurrency.
andersmurphy 1y ago
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.

tomconnors 1y ago

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?

andersmurphy 1y ago
Oh there are a bunch of considerations. You're going to want persistent storage on your server, not ephemeral. You'll also want NVME. A lot of the time you're going to end up on bare metal running a single server anyway. You're going to have down time for migrations unless you're very clever with your schema and/or replicas. Litestream for me at least is what makes SQLite viable for a web app as prior to that there wasn't a good replication story. With litestream it's much easier to have a backup on standby. That being said where I have used it in production some amount of downtime has been acceptable so mileage may vary.
- kiitos 1y ago
  > You're going to have down time for migrations unless you're very clever with your schema and/or replicas. probably worth stating these kinds of design considerations/assumptions up-front i'm sure lots of applications are fine with "downtime for [database] migrations" but lots more are definitely not, especially those interested in synthetic metrics like TPS
  - andersmurphy 1y ago
    I'd argue the opposite most applications are fine with an hour of downtime a month and arguably much more downtime then that. The recent AWS and Cloudflare outages have proven that. You can achieve zero downtime with Sqlite if you really need to. TPS is not a synthetic metric when you cap out at 100 TPS because of Amdahl's law and your users having a power distribution.

koakuma-chan 1y ago

Alternative title: SQLite is All You Need

deleted 1y ago

[deleted]

ducktective 1y ago

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

saberience 1y ago
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).

gausswho 1y ago

SQLite db's do not reliably survive Syncthing across multiple devices. Until that's true I am always seeking a DB design that does.

jaketoronto 1y ago

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 connection pool. (Also, might be nice for you to share what the CPU and thread usage on the Postgres side was during this). (Side note: I'm a big fan of sqlite.. but I've run into lots of performance bottlenecks caused by arbitrarily low connection pools and the like)

jaketoronto 1y ago
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 8x your throughput... What if you were to go even higher? At some point you might start overloading the DB... at that point, you could consider tuning the db to accept more connections... or... maybe you've truly reached the DB's peak performance limit. I just don't think that 8 connections represents that limit... you need to do everything you can to open up your client config until you reach PG's limitations.
- andersmurphy 1y ago
  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-Sizing https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-...
- andersmurphy 1y ago
  Added an epilogue that covers larger connection pools and the results.

bob1029 1y ago

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.

BenjiWiebe 1y ago
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.
- wbsun 1y ago
  My experience: customers with $$$ will always believe they are very important, so important that losing a single bit is the end of world. So you may not want to convince customers waving huge $$$ checks that their data are not that important. But instead, providing options to keep them once they realize that: their pockets are not that deep, and they are also totally ok losing some data.

janci 1y ago

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.

vhodges 1y ago
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.
- otoolep 1y ago
  rqlite creator here, happy to answer any questions.
- andersmurphy 1y ago
  Yeah if you're comfortable scaling vertically and potentially a little downtime. Sqlite massively simplifies your ops, backups litestream is fantastic. It's also as you mentioned dirt cheap (VPS or a hetzner box).
nchmy 1y ago
there's various options now, but im most interested in Marmot - which is multi-master and just came out of a 2 year hibernation with a big overhaul that introduced a native gossip mechanism to replace NATS/Raft https://github.com/maxpert/marmot/ https://github.com/maxpert/marmot/

jcadam 1y ago

I've been getting some good results from sqlite-vec on my current project. I need to look at geospatial extensions next.

Tractor8626 1y ago

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. Unless you use wifi or you database is in another datacenter. > I'm talking about transactions per second, specifically interactive transactions that are common when building web applications No they are not common at all. You probably invented them just to make pg look bad.

andersmurphy 1y ago
> 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?
- Tractor8626 1y ago
  > If you're processing financial transactions you want your isolation level to be serialisable. As the order in which the transactions are processed matters. So you don't know what serializable level is. > 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. So why use 5ms and 10ms for examples? > I'm confused. I invented transactions? "Interactive" transactions.
gunnarmorling 1y ago
> No they are not common at all. You probably invented them just to make pg look bad. Interactive transactions are a well-defined concept in database technology, exactly with the semantics described by the author: transactions with multiple queries, with application logic in between, for instance taking a result from one query, processing it, and running a second query with the outcome of that processing as input. That said, the example in the blog post feels a bit contrived to me, the question being whether that transaction could be structured in a why so that both updates run right after each other towards the end of the transaction, thus significantly reducing the duration of the row lock being held (which is not to say that the general problem described in the article doesn't exist, it does for sure).

tomasol 1y ago

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

deleted 1y ago
[deleted]
andersmurphy 1y ago
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.
- tomasol 1y ago
  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 serializability but not durability?
  - andersmurphy 1y ago
    Very good point I've added an epilogue to the post with updated numbers Really shows the power of dynamic batching.
    - cryptonector 1y ago
      Thank you for doing this update!

andrewvc 1y ago

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.

abrookewood 1y ago
Honestly, I think it's a reaction to all of the over-optimisation that everyone gets caught up in - immediately starting on AWS, Kubernetes and micro-services. Most of the projects people work on will never reach the performance limits of SQLite and a single server. I'm not saying that there aren't valid reasons to use AWS & clustered solutions etc, but we shouldn't always take that as our starting position.

shadowgovt 1y ago

Good. I feel like people keep discovering this principle that "When the engine does far, far fewer things, it's faster," but I certainly won't bash on people learning this, however they get there.

CyberDildonics 1y ago

Those numbers are just called a database.

deleted 1y ago

[deleted]

SJC_Hacker 1y ago

SQLite is cool. You can create an in memory DB ands it’s super fast. Better stick with read only though With a few lines of code you can even copy an arbitrary DB from disk to memory.

NorwegianDude 1y ago

This is very misleading. The secure defaults for sqlite is changed, so commits are not actually written to the disk. Running sqlite like this will cause data loss on os crash or power loss.

andersmurphy 1y ago
I've added numbers for synchronous FULL to the article for those who are interested.

deleted 1y ago

[deleted]

rich_sasha 1y ago

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, can't write. - in some cases, WAL does not checkpoint. This leads to drastic growth in the size of the WAL file, and down the line in catastrophic slowdown of queries - things that take 10ms suddenly take 10 seconds. In my particular case, no tweaking of SQLite params fixed it. I had to monitor for it, and periodically force WAL file to be rolled into the main DB. - all of this gets harder on Windows, where eg.you cannot just 'lsof' a file. - the performance stats change somewhat for the worse in the cloud on drives that look local but actually aren't. Of course that is not sqlite's fault, but the blazing fast performance doesn't apply to all commonly encountered environments that look like real local drives. I'm not dissing SQLite, I use it despite these shortcomings. Equally, I'm happy to reach for something like Postgres, which, well, hasn't burned me yet. EDIT I should add that despite all this I never managed to corrupt the DB, or break any of the SQL promises - never messed up indices, never saw broken ACID compliance etc. And that's a massive endorsement, on reflection.

asa400 1y ago
From your experience, would you call these behaviors bugs, or are they more known issues that result from SQLites specific implementation quirks? What kinds of workloads were you throwing at it when these types of issues happened? Asking as someone who really enjoys and respects SQLite but hasn't encountered these specific behaviors before.
- rich_sasha 1y ago
  I was pushing SQLite quite hard. My DB was at peak 25GB or so. Occasional queries of O(1e6) rows while simultaneously inserting etc. Many readers and a few writers too. Id expect some degradation, sure, but Id say it wasn't very graceful. I think, however, I was well within the parameters that SQLite maximalists would describe as within th envelope of heavy but fine usage. YMMV. I found a very small number of people online with the exact same issues. Enough to know I'm not hallucinating, but not enough to find good support for this :/ but, TLDR, forcing WAL truncation regularly fixed it all. But I had to do it from an external process on a heartbeat, etc etc

andersmurphy 1y ago

(author here) Thank you for the fantastic discussion so far. I've added an Epilogue to the article that covers: - Pragma synchronous "FULL" - Giving PG a bigger connection pool

cluckindan 1y ago

The first example uses a pattern where application code is run in the middle of a db transaction. That is almost certainly an anti-pattern, unless the computation is very lightweight - and even in that case it could probably be done in SQL.

andersmurphy 1y ago
You can definitely mitigate the power/amdahl's law problems with pure SQL and/or triggers/stored procedures. I mention that in the article. However, sacrificing interactive transactions is rough and not always possible. It's worth keeping in mind that the problem here is not heavyweight computation, but the network latency to the database. Your computations can be fast, but if your latency is still at best 1ms for each time you go to the database during that transaction. Every ms that high contention row is locked is kept your throughput degrades dramatically.

Jyaif 1y ago

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

saberience 1y ago
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.
- andersmurphy 1y ago
  >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 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 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.

cryptonector 1y ago

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