Hacker News Reader

Comment by bob1029 | Hacker News Reader

I built a rules engine around SQLite queries for the last product I was working on. This article was the reason.

You can run an unbelievable # of select statements per unit time against a SQLite database. I think of it like a direct method invocation to members of some List<T> within the application itself.

Developers who still protest SQLite are really sleeping on the latency advantages. 2-3 orders of magnitude are really hard to fight against. This opens up entirely new use cases that hosted solutions cannot consider.

Databases that are built into the application by default are the future. Our computers are definitely big enough. There are many options for log replication (sync & async), as well as the obvious hypervisor snapshot approach (WAL can make this much less dangerous).

View on HN

> Databases that are built into the application by default are the future. Our computers are definitely big enough.

Interesting point. It's easy to forget that in the past local storage was small and expensive so that it was necessary to have a separate database machine.

It wasn't just storage. In the 90s, most servers were single core. Databases already had network interfaces so they could be shared by non-web applications running on desktops, and so were the natural place to split a website over multiple machines. This turned into the received wisdom of always putting your database on a separate machine, despite processing power and storage latency improving so much since the 486 and pentium days that network overhead dominated response times.

I'm still not understanding this push toward using SQLite as a production backend database. It's great for what it is, a tiny embeddable client-side application database. Like an address book on your phone. But even the developers themselves have steadfastly refused to allow it to expand beyond that scope. For instance, they won't add native types for any useful things like dates/times, or uuids. Because that would bloat the code and the size of the embedded object. So you're stuck with "everything is a string". Ref integrity can be enabled, but even those constraint options are very limited.

Not sure why people are still trying to shoe-horn it into a role that it's not meant to be in, and not even really supported to be.

It's absolutely supported for that use case if you can get away with single-writer multi-reader architecture, which IMHO most medium sized applications can. [1]

[1] https://www.sqlite.org/whentouse.html (see Server Side Database)

Maybe it's not for you, but the "everything is a string" thing is just the default. SQLite has STRICT table option since 2021 that people really should be using if possible: https://www.sqlite.org/stricttables.html

This brings strict types that people expect from the other server-based databases.

And even being this simple and having clear challenges it's worth using it for the latency gains.

When is it a good choice, and when is it not?

Just saying that it's "worth using it" gives the impression that it's a good choice for all levels of traffic, all security requirements, all isolation requirements (yeah it's serialisable, but this comes back to levels of traffic), the kind of use case where you need SKIP LOCKED instead of just a single writer, etc.

Just to clear up the error in the parent post: SQLite has native blobs, floats, and integers, not just strings. It doesn't have a bunch of other types for things like dates and JSON - you just represent those things using the native times of integer, float, string or blob. But it is not limited to only strings. This has been true for 20 years.

But it has functions for operating on dates and times though, right? date(), strftime() etc. Those just silently fail with a NULL if the value in the tuple isn't something that it recognizes.

So it seems like it _tries_ to do the things that people want, but only half-heartedly, and just fails silently if it can't. Was the column actually NULL? Or just a wrong string format? Who knows?

The point is, SQLite is very "flimsy". And that's perfectly fine for what it is (or what it intends to be). But if I try to insert or retrieve a date() or do strftime() on a string that isn't a valid date/time, don't just return a NULL. That's where the "flimsiness" makes it a deal-breaker for anything where integrity is important and the database itself is supposed to be the authority.

> It's great for what it is, a tiny embeddable client-side application database. Like an address book on your phone

Size is no real concern, if the user of a client side application has many gigabytes of data a sqlite database is still well suited for the role. There's no shoehorning, it just works.

> Databases that are built into the application by default are the future.

No they're not, because for web servers, you have many (tens? hundreds? thousands?) of web servers that all need to talk to a single database. And you sure don't want to replicate and sync a gigantic database across each web server -- that would be a disaster.

While for local apps on your phone or computer, usage of SQLite is already widespread -- it's not the future, it's here. And cloud-connected apps that work offline already do some sort of sync for that.

We have a giant Postgres DB, but instead of having every machine connect directly to it, we have a job that creates a smaller SQLite cache of relevant data and that’s pushed out to the machines who then reload that on the fly.

All this depends on your data being somewhat shardable of course.

You can’t really sync the data in real time tho. Transactions still need to hit your Postgres, and then you reload. You might as well do write thru cache.

Yeah, this sounds pretty not great if you need ACID consensus or locking between multiple machines. If what you need is to read a somewhat static state, fine, great.

Most people don't have tens or thousands of webservers and could be running with a more efficient inprocess database that syncs to another networked readonly replica for a very long time. I'm very surprised that MySQL or Postgres still don't have an inprocess mode because it's such an obvious win and on a technical coding level it should (naively) be very easy to switch from network calls to direct calls.

But it's standard to have at least 2 webservers.

And syncing databases is just waaay harder than having a dedicated one. It really seriously is a pain to configure and debug and manage storage and performance requirements around and dealing with scaling and managing failure modes and so forth. If you can even find someone who has the skills to do it properly so you never lose data.

It's far, far easier to just write normal SQL queries that don't have to make hundreds of requests, than to deal with syncing a database across all your webservers.

> But it's standard to have at least 2 webservers.

Why do you need 2 webservers (assuming that the cpu / network / disk throughput of a single server is sufficient for your application)?

So that when one breaks you have a failover.

And because designing for 2 is really different from designing for 1, but not much harder if you do it from the beginning. But expanding from 1 to 2 when you unexpectedly need to because of sudden traffic, and having to re-architect it, can be really hard.

I'm not talking about hobby sites here, I'm talking about anything designed for a business, where downtime of a day is unacceptable.

But doesn't the same logic apply to the database? Is the webserver / application layer really so much more CPU intensive (and/or prone to failure) than the database in sites "designed for business", such that you can't get away with not designing the webservers for load balancing, but you can get away with not designing the databases for sharding?

No to your first question. And yes to your second, unless you're basically at Facebook scale. That's exactly the difference.

Databases are astonishingly more optimized and predictable and reliable than web servers.

> for web servers, you have many (tens? hundreds? thousands?) of web servers that all need to talk to a single database

Maybe if you're in the top 1000 or so largest websites.

Back when the alexa 10k was a thing, $work was on it - and we're serving that level of traffic with a rails app running on 30 CPU cores. It would fit _easily_ onto a single machine.

Single machines aren't fault-tolerant. If your machine goes down, your site goes down. And god help you when you have to scale to a second machine and you haven't architected anything for that, and it's a project that takes months.

There are a lot of good reasons that we use a fleet of cheap scalable web servers in front of a beefy database server that has a live failover.

> There are a lot of good reasons that we use a fleet of cheap scalable web servers in front of a beefy database server that has a live failover.

"You can't fit on one big machine" is virtually never one of them - and at that point you're firmly in the realm of 'months of bespoke engineering to handle the load'.

Similarly, "beefy database server that has a live failover" is not inherently better than "beefy web+database server that has a live failover".

I agree there are a lot of good reasons. There used to be a _lot more_ good reasons, and - while I haven't actually tried it yet - I suspect that it's far more tractable than it used to be.

If I _were_ to try it, my design would be multiple failover targets (at least 3, geographically dispersed, probably 4), plus a separate control plane to configure BGP.

> "You can't fit on one big machine" is virtually never one of them - and at that point you're firmly in the realm of 'months of bespoke engineering to handle the load'.

That's just not true. There are a lot of web servers where their operations are CPU- or memory-intensive, depending on what they do. Are they working with images? Mapping? Video? Large data throughput? Especially when so much server code is written in slow interpreted scripted languages.

Well-architected databases often can fit on one machine, unless you're at Facebook scale. Webservers however -- absolutely not. The front-end for a simple CRUD interface, or for a blog with static pages, sure. But for a lot of interactive sites, definitely not.

> That's just not true. There are a lot of web servers where their operations are CPU- or memory-intensive, depending on what they do. Are they working with images? Mapping? Video? Large data throughput? Especially when so much server code is written in slow interpreted scripted languages.

So the "traditional scalable" way of doing things right now assumes your "app" layer (sorry if I'm not using the right term -- the thing between the HTTP request and the database) is inherently going to be CPU intensive in a way that the database is not; and so assumes you're going to need to make a distributed system with load balancing, network connection to a database, etc.

But it seems to me that making that distributed system 1) requires a huge amount of "complexity budget" 2) actually increases aggregate CPU utilization, since one way or another everything has to be marshaled across the network, usually involving interpretation, copies, verification, maybe encryption, and so on.

So the question is, could you instead spend your "complexity budget" on actually optimizing the "app" layer? Use a performant compiled language, like Go or Rust, use profiling and optimization, etc. Sure, you'll have to spend more of your development time optimizing inner loops, but you also spend less time designing and debugging distributed systems.

I remember hearing somewhere that the entirety of StackOverflow ran on two physical boxes. Given how much traffic they must have gotten, I'm betting that I can do the same for the website I'm currently developing.

And connected to SQLite: Combine the fact that databases are just files, and that you can easily run "ATTACH <file>" to connect multiple database files on the same "connection", you should in many cases be able to design your overall structure to effectively "pre-shard" data. Right now I've got a separate .sqlite file for the per-user data, but all users share common site data; so if I ever reach the point where I really can't optimize further, I should be able to break my app into multiple servers without having to do further work sharding the database.

If everything goes up in smoke, I'll write a blog post admitting I was wrong and DM you. :-D

> So the question is, could you instead spend your "complexity budget" on actually optimizing the "app" layer?

You definitely could, and I know people who have ported things from e.g. PHP or Python or Java to Go, so that a service that required 100 servers then only required 1. But it's just expensive -- the time it takes, and hiring people who can do that and maintain that.

And I only know of that having been done for services that were already in an extremely stable state, and not expected to change -- API endpoints, not HTML generators.

Generally speaking, the "app" layer is just changing a lot as features get tested and added and changed and swapped out, and dev cost is the priority, not server cost. And load balancing web servers is very easy.

> And connected to SQLite: Combine the fact that databases are just files, and that you can easily run "ATTACH <file>" to connect multiple database files on the same "connection", you should in many cases be able to design your overall structure to effectively "pre-shard" data.

Distributing a database that is read-only is easy, sure. It's the writes that are hard, and when you shard it's the joins between shards that give you headaches. If you work on a site where data from one user never has to be joined to data from another, then lucky you. ;)

> Databases that are built into the application by default are the future. Our computers are definitely big enough.

That assumes local applications themselves are the future, and that assumption has grown ever weaker and weaker with everyone and their dog going cloud-only (or starting as a SaaS in the first place) to grab all the sweet sweet recurring subscription revenue.

| Databases that are built into the application by default are the future.

I'm not sold on client side, but a lot of great work is being done on putting the db and application on the same server, between SQLite replication and other approaches like SpacetimeDB. I'm interested to see where it goes.

SQLite underpins nearly every on-disk storage mechanism at Apple and Google as the OS level. You may not be sold but systems programmers elsewhere are.

What works for Apple and Google is rarely the best approach for the rest of us.

My concern is over-reliance on client side storage and the complexity it adds to have records of state on both client and server.

Sqlite isn't something that works for only apple or google. Point is it's sufficient for them, simple, and more than sufficient for you. He's explaining to you that this is something that has already happened as you're unaware.

I use SQLite all the time, I said in another comment it's my go-to for server-side green field projects. But there's a lot of push to create data solutions that are fully bidirectional between client and server, or to even make them client-driven in an awkward peer-to-peer model, and I'm not sold on either pattern.

Sqlite is at it's best in "pure client" situations where there is no server and there are no P2P things going on. Just the application, the user, and their data on their own drive with sqlite.

I often find myself thinking about what a database like SQLite that might look like if it had a native API instead of a query language. I guess it wouldn’t be so different from a dataframe API, but with persistence, locking, and relational features, and I guess an mmap under the hood.

> Databases that are built into the application by default are the future.

That’s a bit overzealous. SQLite is great, but it’s not a replacement for a hosted database. Not all data can live on the client. Use SQLite when you have the right use case for it (offline desktop app, etc.)

There are ways to use a hosted SQL, or from a cloud application.

I personally haven't used it.

What I am curious about is if "Many Small Queries Are Efficient in SQLite" when using the various hosted flavors of SQLite.

Nope. You get back to the roundtrip problem.

The SQLite db can easily live on the same machine as the app server.

> using the various hosted flavors of SQLite

I think the "hosted flavors of SQLite" would be doing the type of trick Litestream does: streaming the WAL to other copies of the database. The copy you interact with is still local.

> Not all data can live on the client

When you say "client" are you referring to the end user's machine, or the server hosting the application they are talking to?

End user machine.

Ok yeah - I wouldn't recommend storing any state on the user's machine unless it's absolutely required.

I only advocate for using SQLite on the server, integrated with the hosting application (i.e. your back-end systems).

It’s clients all the way down. ;)

If you have a database server and an application server, then move your application on the same machine as your database. That's basically the use case of sqlite server-side. You can easily fit 250TB on rack (or 64tb on an ec2.) That's a lot for non-blob storage: 750KB per us citizen

Who has "an" application server though? Those of us who came up through PHP/Rails/Flask and even much of the modern microservices world were taught to do stateless, probably load-balanced application tiers with shared DB, maybe cache, maybe message queue. Both for scalability and redundancy.

Redundant SQLite is not impossible [0] but it is definitely less mature and well-known than replicated MySQL or Postgres.

[0] https://litestream.io/

> Databases that are built into the application by default are the future. Our computers are definitely big enough.

Knowing this, I wonder if there will be another wave of object databases at some point.

> Databases that are built into the application by default are the future.

It still depends a lot. If your system is high-load enough, you may want sharding, for example.

Wouldn't a hosted database really only "make sense" if you don't want sharding? It's a lot easier to shard at the front end before it makes it to any business logic than to do that and also shard the database. It's when you don't want to deal with database sharding at all that you'd want all application instances to hit the same hosted database.

Sorry I forgot that people do "front end" these days. I was thinking of the old-school server-side-rendered web apps that load almost instantly, not the modern spinner-fest ones.

Reply on news.ycombinator.com