I hope fly is able to make it. I’m rooting for them - however - I’m starting to wonder if the SQLite push isn’t more “this is fun and interesting to build” and less “customers want this”.<p>Don’t get me wrong - this is neat - but I’d never suggest anyone to actually use this outside of a fun experiment. The problem with existing SQL dbs isn’t really the architecture - its the awful queries that do in memory sorting or make temporary tables for no reason or read-after-write, etc, not network latency. SQLite won’t fix your current production problems.<p>If it turns out they’re building this for customers throwing cash at them, awesome. I just somehow doubt it. I think Planetscale has the better approach: a drop in replacement for MySQL/RDS with a smarter query planner. As a production engineer that’s what I want to pay for!
Been feeling a little miffed about this recently. Litestream is excellent but if you have multiple writers your db gets corrupted. Quite easy to do with rolling deploys.<p>LifeFS was announced and is intended to help this. Now seems like (<a href="https://fly.io/docs/litefs/getting-started-fly/">https://fly.io/docs/litefs/getting-started-fly/</a>) it requires an HTTP proxy so that the application can guess about sqlite write/read usage by reading the HTTP request method. This seems... to introduce a different (maybe better?) set of gotchas to navigate.<p>There are now SQLite cloud offerings but you pay the network overhead and avoiding that was so much of the appeal of using SQLite.<p>Are people successfully using SQLite in a work or production setting with a replication and consistency strategy that they like? I've had trouble getting a setup to the point where I can recommend it for use at my jarb.
I’m bullish on SQLite, and this is mostly a great article, but this kind of stuff is flat-out misleading:<p>> When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds.<p>As if postgres and others don’t have a way to run application logic at the database. I like the SQLite way of doing it — you pretty much freely choose your own host language — anything with a decent SQLite client will work. While in postgres, for example, you’ll probably end up with pgplsql (there are others, but there are constraints). So this isn’t about latency, as the whole section of the article suggests.<p>There’s actually a relative weakness in SQLite here, since it doesn’t include a built-in protocol to support running application logic separate from the database. That’s also architecturally useful, and so you may have to find/build a solution for this.<p>Just adding replicas isn’t a general solution either, because each replica has an inherent cost: changes have to somehow get to every replica.<p>E.g., systems can grow to have a lot of database clients. In traditional setups you begin to struggle with the number of connections. You might think with SQLite, “hey, no connections, to problems!” but now, instead of 1000 connections you’ve got 1000 replicas. That’s something you’re going to have to deal with… that’s 1000x write load, 1000x write bandwidth.<p>Perhaps fly.io has a solution for this, but I suspect it’s going to cost you.
I can’t see any valid reason not to use Postgres at the back end, unless you are in some sort of environment such as embedded or cloudflare workers that requires it. Or if you need a graph database there are better choices than Postgres.<p>Postgres is good on multi core, incredibly feature rich, multi user, supported by everything, lightweight and has all the tools for production workload and management. All stuff that is important.<p>Most important difference to me being SQLite I understand lacks flexibility in modifying table structures.
Author here. Cool to see the post make it up on HN again. I'm still as excited as ever about the SQLite space. So much great work going on from rqlite, cr-sqlite, & Turso, and we're still plugging away on LiteFS. I'm happy to answer any questions about the post.
I don't need to be sold on the virtues of applications running on systems like SQLite. The nineties had a lot of servers which were very simple (and performant) compared to LAMP, and I like systems like that.<p>What I would like is a good primer about the layers on top of SQLite. What does Litestream do for me? How does it compare to competitors? Why not just use SQLite directly? A more in-depth technical discussion would be nice. I'd also like to understand wrappers and ORMs for migration to other systems, should SQLite stop scaling.
If you need multiple writers and can handle eventual correctness, you should really be using cr-sqlite[1]. It'll allow you to have any number of workers/clients that can write locally within the same process (so no network overhead) but still guarantee converge to the same state.<p>[1] <a href="https://github.com/vlcn-io/cr-sqlite">https://github.com/vlcn-io/cr-sqlite</a>
Use Postgres. Or if you insist on this type of architecture use CouchDB. I shudder thinking about a SQLite schema migration across clients with potentially unknown versions.<p>Seems like a disaster waiting to happen unless you have a bunch of logic centralized somewhere to keep track of last know schemas per user client database. And if you’re going to do all that, unless you desperately need low latency (in which case you could use a multi region database like cockroach), why not just centralize?
SQLite not supporting "stored procedures" is a deal-breaker for me. The idea for stored procs is not to "put the process as close to the data" but simply that we have a single place for language-agnostic encapsulation of data procedures.
I recently saw the launch post of Electric SQL which syncs to SQlite, I like the pattern on how keeping the data close to the frontend can solve many problems, if synced with the main DB. I hate to run another docker or manage service to manage this layer but if somehow a part of data from the database like Postgres can be synced using something simple like litestream and can be placed either on edge or client can be a solution to many of the problems.
> <i>When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds. That’s micro, with a μ. A 50-100x improvement over an intra-region Postgres query.</i><p>Why compare the latency of a remote Postgres database with a local SQLite database? If your app is so simple and self-contained that it runs on a single EC2 instance using local files, nothing prevents you from installing Postgres on the same machine, whether inside a container or not.<p>I have some simple apps on EC2 with MariaDB on localhost, and well-tuned queries rarely take more than 100-200 microseconds. That's total query execution time, not just communication latency. RDS just sucks for this kind of use case. It's not a useful comparison.<p>> <i>As much as I love tuning SQL queries, it’s becoming a dying art for most application developers. Even poorly tuned queries can execute in under a second for ordinary databases.</i><p>Didn't you just say that milliseconds matter?
Related: I wrote a piece last week on deploying Rails apps to production on Fly.io at <a href="https://fly.io/ruby-dispatch/sqlite-and-rails-in-production/">https://fly.io/ruby-dispatch/sqlite-and-rails-in-production/</a><p>The work that’s made this possible is:<p>1. Litestack <a href="https://github.com/oldmoe/litestack">https://github.com/oldmoe/litestack</a> runs everything on Sqlite<p>2. Fly.io’s work on the dockerfile-rails generator detecting Sqlite and Litestack in a Rails project, then setting up sane defaults for where that data is stored and persisted in production. This is all done behind the scenes with no intervention required from the person deploying.<p>3. Servers are overall faster and more powerful<p>I hope more Rails hosts make it easier and safer to deploy Sqlite to production. It will lower costs and reduce complexity for folks deploying apps.
While different than the approach offered by Litestream, I am fairly excited by the direction of Cloudflare D1, making SQLite available at the edge without having to manage anything. Still in alpha but worth looking at if you're looking for cheap cloud option.
Me too. It’s just a .db file on the server. The same as MySQL but this one is on the same server like the clients site or my site. Get it? It’s a file! How crazy is that. If you wanted to outsource the sqlite the same way you do the myposmongresdb databases with separate login, scale from zero to ipo and all the trimmings you would have to put it on another server or a service even. Then you can call it long distance and have a dedicated dbdudeuser like with a grown up database and you get networklattemacciato for free! Endless possibilities and constellations.
A few years I made a decision to ship a SQLite database in an (internal) ruby on rails package. Why? Because there was a large set of (static) data that was required for the package to work, and it made no sense to make an API to query it from external sources (It wasn't that big, something like 5-10Mb if I recall). At the time it felt like a super dirty hack, but time seems to have validated that decision :)
I recently wrote a production system that uses SQLite as the main backend. SQLite is in memory in this case and its entire state gets rebuilt from Kafka on start. The DB receives about 2 updates a second, wrapped with rest api aiohttp and odata filters. It has been able to handle close to 9k requests/second ands it’s a primary system in a financial institution. So yes SQLite is fully capable prod db.
I dont want this to be taken the wrong way but I read about fly.io and sqlite atleast once a week.<p>Who is using this and why is it such a hot topic on HN?
Neither Litestream and LiteFS meet my SQL needs: Litestream is a single writer system, LiteFS has data consistency risk. I can't justify replacing Postgresql for them.<p>I do understand those tools expanded the use cases of sqlite a lot and they are pretty cool in how they pulled it off. But I'm surprised Fly's investing here; feels like it tarnishes their infra provider rep.<p>If they do want to continue this investment, maybe investing in things like rqlite will be more appropriate for an infra shop.
Did anybody try something like that: read/write to SQLite database file on backend, but also allow the database file to be downloaded at any time by rich JS frontend for read-only querying. I just wonder if the file is going to be (eventually-) consistent and not corrupted.
> <i>We’re beginning to hit theoretical limits. In a vacuum, light travels about 186 miles in 1 millisecond. That’s the distance from Philadelphia to New York City and back. Add in layers of network switches, firewalls, and application protocols and the latency increases further.</i><p>> <i>The per-query latency overhead for a Postgres query within a single AWS region can be up to a millisecond. That’s not Postgres being slow—it’s you hitting the limits of how fast data can travel.</i><p>No. An AWS region has a radius of less than a few dozen kilometers, more likely around 5km. Lightspeed doesn't factor into it at those small distances. That millisecond is indeed Postgres being "slow" in these terms. (Most of it is the networking stack, as noted.)<p>This basic error makes me question the validity of the document. I stopped reading here.<p>I agree that "networks are slow" but this sort of false justification is not the way to sell it. Is this an attempt to make the author seem like he knows what he is doing because he knows the speed of light?
My org's apps heavily use this simple key-value interface built on sqlite: <a href="https://github.com/aaviator42/StorX">https://github.com/aaviator42/StorX</a><p>Handles tens of thousands of requests a day very smoothly! :)
I really worry about split-brain with sqlite. These replication features just seem too immature for me.<p>That being said I love sqlite and it should be the DEFAULT database with any application until something is demanded otherwise.