Hacker News Reader

Comment by Glench | Hacker News Reader

Here's an all-time great post about why you might consider SQLite in production with data about performance: https://blog.wesleyac.com/posts/consider-sqlite

I use SQLite in production for my SaaS[1]. It's really great — saves me money, required basically no setup/configuration/management, and has had no scaling issues whatsoever with a few million hits a month. SQLite is really blazing fast for typical SaaS workloads. And will be easy to scale by vertically scaling the vm it's hosted on.

Litestream was the final piece of the missing puzzle that helped me use it in production — continuous backups for SQLite like other database servers have: https://litestream.io/ With Litestream, I pay literally $0 to back up customer data and have confidence nothing will be lost. And it took like 5 minutes to set up.

I'm so on-board the SQLite train you guys.

[1] https://extensionpay.com — Lets developers take payments in their browser extensions.

View on HN

This was so inspiring to read. It's a very balanced take about the pros and cons of using SQLite vs. Postgres at scale.

I say "inspiring" because using SQLite reminds me of the simplicity and productivity from coding for the "early web" that lost 10-15 years ago. The days when you could spin up a website without worrying about a bunch of ancillary services and focus on the app itself.

For me, SQLite's lack of online schema changes seems like perhaps the biggest blocker to actual production. I've never had a production project where the schema didn't change a lot.

I have this beef too. Tooling for dumping and restoring into a new schema are easy/simple/fast. So, these schema migrations can happen w/o issue. Some tricks with the PRAGMA directive in SQLite so you can roll out changes (eg: code supports old/new schema while migrating)

Interesting, that's super cool to read.

   Tooling for dumping and restoring into a new schema are easy/simple/fast.

Any resources you can point to that expand on this? Is this standard SQLite tooling? I'm curious how it would perform with large-ish databases - a few hundred GB or perhaps several TB.

(This is one of those things where I can "just Google it" but I was wondering if perhaps there was a particularly useful article that points out potential gotchas, etc)

I'm just using shell scripts and the SQLite CLI. I dump/restore files to the same FS so I can mv into place when ready.

Checkout the PRAGMA user_version, I just modify on schema change, so app knows where stuff is.

https://www.sqlite.org/pragma.html

We also used SQLite and leverage PRAGMA user_version to do the trick. But decided to move to PostgreSQL mainly due to the schema migration constraints.

Our experience is SQLite is OK for small to medium projects, but when the business logic/data model becomes more complex, SQLite is not sufficient.

More discussions are captured here https://news.ycombinator.com/item?id=31038614

> With Litestream, I pay literally $0 to back up customer data and have confidence nothing will be lost.

This is not a guarantee Litestream makes (nor it can, since replication is async).

You'll lose things to catastrophic failures, but chances are you'd be able to restore to a last known good checkpoint.

What is the point of using SQLite under a web service?

I thought people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant. (Things seem to be getting better with strict column types these days.)

I've recently migrated a smallish service from MySQL to PostgreSQL and figured it's quite a work if you're not careful writing by the SQL standard which means if the service had gotten bigger, your chance of moving away from SQLite kind of walks away.

So, why not use a safer choice to begin with? Nothing is complicated running MySQL/PostgreSQL unless you've sold yourself to AWS to care for the cost and don't know how to run a DB instance yourself.

> What is the point of using SQLite under a web service?

Take a look at the Consider SQLite post I linked. They address your performance questions too.

For me, SQLite was a nice way to simplify launching and running my SaaS business and has had no downsides.

Any tips you can share on how to deploy your saas app without downtime when using sqlite?

For my personal project I'm planning to use a VM with an SSD. Manually I'll use caddy to switch over to a new running backend service with readiness check.

As for scaling if I need it I can increase disk space for the app server or scale out horizontally/vertically. Don't need that yet so I'm waiting for more details in the future to decide how to handle that.

Which backend “owns” the sqlite database?

That’s what I haven’t seen mentioned anywhere, if the database is part of the application, how do you switch from one version to another without downtime.

The VM owns the database. Multiple backend service versions run on that same VM. Usually just one but 2 while migrating traffic to a new version.

I haven't figured out details regarding having 2 processes both writing and reading to the SQLite DB at once. It might just be fine. With a 1 minute request timeout I can just shut down the previous version after 2 minutes (should receive no new requests after 1 minute) and caddy will have sent requests to the new version for a while.

Not sure which types of errors I'll be seeing but the client may need to retry requests in some cases.

This is all just what I've planned but hopefully kinda makes sense.

> So, why not use a safer choice to begin with?

I know several people who build projects like that. It take them months to get a working product, just to discover it doesn't interest people or doesn't work like they expected. If for every piece of tooling you go for the "safe" and most performant one you gain bloat and complexity real quick.

People underestimate "simple" tech performance, in 99% of projects by the time your bottleneck is your DB system I can assure you that it'll be the least of your concerns

Which part of running MySQL instead SQLite is over engineering?

- Setting up a MySQL server on both your dev machine and server, and making sure they're the same version (extra fun if they're on different OS versions)

- Setting up an out-of-repo config file on the server with your MySQL credentials

- Setting up a backup script for your server data

It's only about an hour of work total, but it's an hour of work that I hate doing.

So you trade for some risk for an hour.

Pieter Levels has been using SQLite for nomadlist and I think it's been going well for him.

Please do explain what risk you're thinking of, as anyone smart enough to write their own SaaS would not put resources in the web server's file system tree? You stick your db file in an normal secure location outside the server's root, chmodded appropriately so that it suffers the exact same risks as any other file on the OS. It's no more or less risky than /etc/shadow, while being considerably easier to work with (and less failure-prone for light db work) than an independently running database service.

your app has full access to the SQLite file. MySQL/PostgreSQL have users and permissions. Security is about layers, and SQLite is removing one layer of security. You can, for example, put DELETEs or access to certain tables on a separate user that your web app has no access to. With SQLite, if your app gets hacked then they can do anything with the whole DB they want to. In addition, with a separate DB process you get audit logs. If someone hacks your SQLite app they may have access for months before you realize it, if you ever do. Especially if they are doing something subtle like UPDATEs on specific tables/fields that may go unnoticed but provide the hacker some benefit. This is why you can't simply rely on the idea of using a backup. That's only going to help if the hacker totally trashes your DB.

With a separate DB you may have a hope of detecting when someone hacked your app. But without that firewall, the question becomes: how much of the data in my SQLite can now be trusted? If you don't know what backup is safe to restore, then you can't trust any of it.

Again, this is about layers. Not saying MySQL/Postgres will save you. But they can increase the odds.

If your server or API can be exploited, it doesn't matter whether there's an auth layer in between. Your SQL server runs as a service to connect to, your sqlite3 file is a file that you need access to. They're the same kind of layer: you need to break through the server's security to ever get to them directly, and if your app gets hacked such that the hackers gain file system access, then:

1. You're fucked. The end. It doesn't matter whether you were using mysql, postgres, or sqlite3, or S3, or Redis, or any other server your app was connecting to: they can just look at your environment vars.

That's not going to happen "because you're using Sqlite3", that's going to happen because you used some obscure server software, or worse, rolled your own.

People really do seem to put too much faith into "it has a username and password, it's more secure". It's not: if someone has access to your actual server, they have access to everything your server has access to. Sqlite3 is no more or less secure than a dbms daemon (or remote) in that sense.

With sqlite your server, api or application can be hacked. The most common and likely hack would be somewhere in your application. It really doesn't make sense to use sqlite here.

Setup a separate database server and use it for all of your projects. That one hour pays off each and every project.

The risk is as I had written previously that it takes some effort to move away from a db to another when the need arises when I see no benefit in choosing SQLite in the beginning.

I'm not a professional db engineer but one point is that there doesn't seem to be a way to create functions in SQLite which would mean creating triggers on various tables can cause excessive amount of duplicate code.

If I rely on PostgreSQL, I feel covered for my use case for web apps but once you hit some little gotchas in SQLite, you may regret about saving 10 minutes (install db and set up a password) for nothing.

That's not a risk, that's just an inefficiency further down the line (migrating data from sqlite to a "real" database can indeed be quite a chore, but far less so if you formalized your schema and constraints beforehand, so that a migration mostly involves exporting to SQL, rewriting where needed, and then importing into whatever dbms you end up using later on in the lifetime of your project).

When we're talking about risks, think security exploits: how is sqlite3 more likely to get your data leaked, or flat out copied in its entirety, compared to using a mysql/postgres/etc.

Inefficiency or not, if you start down the path of SQLite, you need to invest good amount of time refactoring into another DB if you feel like migrating away.

When I realized SQLite would store any type of data in any kind of column type, it was obvious SQLite is different from others. They only added strict types about a year ago but scared me enough not to use it.

And how is SQLite any less secure? You can flat out copy its entirety using pg_dumpall. I'm not talking about security.

While that's an unfortunate discovery, I do need to point out that that's a problem of your own making, not SQLite's fault. SQLite is very clear on what you can expect, with dynamic typing being one of the main differences between it and "regular SQL" databases.

Yes, plenty of folks just go for SQLite blindly, but the consequences of that aren't SQLite's doing: if you take the time to read through https://www.sqlite.org/features.html because you want to know what it can actually do, you'll almost certainly click through to https://www.sqlite.org/omitted.html because you'll want to know what it doesn't do, and then you'll see the "See also the Quirks, Caveats, and Gotchas of SQLite." link and you're going to follow it an read through https://www.sqlite.org/quirks.html because those sound pretty important to know about before you commit to using something that is going to be your application/service data store for the foreseeable future.

If a project of mine ever gets enough traffic to force me away from SQLite, I expect I'll be rolling in dough and willing to put in the effort.

Some risk of what?

docker-compose is the way to go for keeping the dev versions synced with the production version. And for the backup script scheduled mysqldump and copy to storage should see you through quite far, so not really any more effort than copying an SQLite database.

and now you need to learn docker and 10 other things.

This person is talking about reducing complexity, so I don't think adding more moving pieces to the machine is the way to go.

Hum... MySQL is one of those labor-generating technologies that a Luddite would fall in love for. Those are best avoided. But for Postgres...

- You make sure the production version is larger or equal than the development, or you make sure to not use new features before they reach production, what is quite easy. There is no problem with different OSes (except for Windows itself not being very reliable, but I imagine you are not using Windows on production, as it's another one of those labor-generating techs).

- Trusting a local user is the same level of security you get with SQLite, no credentials required.

- And setting a backup script... Wait, you don't do that for SQLite? There's something missing here.

Yes, there are a lot of small tasks that add up when setting some new software. It's a pain. But it's a pain you suffer once, and it's over. It's worth optimizing, but not at any ongoing cost.

Running MySQL/Postgres over SQLite:

- needs to be provisioned and configured

- needs additional tooling and operational overhead

- comes with a _large_ performance overhead that is only won back if you have quite a significant load - especially writes, which means the vast majority of web projects are slower and require more resources than they should.

- it makes the whole system more complex by definition

It is a cost-benefit thing that tilts towards RDBMS as soon as you need to sustain very high transactional loads and want a managed, individually accessible server that you can query and interact with while it's running in production.

But if it is just "a website that needs durability" then you haven't yet shown how that tradeoff is worth it.

Just wanted to add another’s scenario where postgresql has been useful to me. Functions. There are cases where you have expensive operation(s) that reference a lot of persistent data. Even without massive traffic these operations can be prohibitively expensive in the middleware. Leveraging database functions can be a massive performance improvement (100x + for me), especially if your middleware is slow (e.g. rails).

I’ve used SQLite in production once and it worked great. But that was a very simple app. For more complex (but not always higher traffic) I’m leaning more and more on postgresql and less on my middleware, like moving business logic to the database when it makes sense.

If you need the expressiveness and power of Postgres then sure, it has also way better JSON support for example, there is generally better tooling for it as well and so on. But in this case, your database becomes it's own _thing_, has much more value outside of being just durability for an application. Like for example Supabase is doing things. That's a very fundamental design decision IMO. I explored this and it is very attractive and robust, but serves different use-cases.

> What is the point of using SQLite under a web service? … people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant.
My understanding is that for read performance SQLite is pretty damn good, outperforming MySQL and Postgres in both single and concurrent tests. The key performance issue is the single global write lock. If your data access pattern is massively read biased then SQLite is a good choice performance wise, if you see a lot of write activity then it really isn't.

With regard to being correct*, it offers proper ACID transactions and so on. Typing is a big issue for some but far from all. It is significantly more correct than mysql used to be back before InnoDB became the default table type in ~2010, at least as correct as it now (aside from the data types matter depending on which side of that you sit on).

> I thought people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant

While "people" complaining is basically meaningless, I don't know why they'd be doing that about SQLite. It's used in most phones, all copies of Windows 10+, and countless other places.

Chrome browser has loads of them in your profile directory.

I would expect the performance of SQLite for queries against an index to outperform MySQL and PostgreSQL in all cases - because SQLite eliminates the need for network overhead by essentially executing those queries directly as a C function call.

So no matter how optimized MySQL and PostgreSQL are, SQLite will run rings around them for basic SELECT queries.

Running against a unix socket doesn't seem to make that a selling point for SQLite.

There's still serialization and deserialization overhead there. I would expect SQLite to win in benchmarks against MySQL or PostgreSQL on a Unix socket for basic "select * from table where id = 5" calls, but I've not done the work to prove it myself.

SQLite is far faster than Postgres or MySQL, however, the price you pay for this is having a single writer thread, and it's a library incorporated into your process, not a shared DB. It's faster because those other features of a server have a cost as well, particularly the cost of write arbitration.

SQLite is fine when all your load can be served by a single backend process on a single machine. The moment you need multiple backends to handle more load, or the moment you need high availability, you can't do it with SQLite. SQLite has very limited DDL operations, so you also can't evolve your schema over time without downtime. Now, for streaming backups - how do you come back from node failure? You're going to incur downtime downloading your DB.

I run many SQL backed production services, and my sweet spot has become a big honking Postgres instance in RDS in AWS, with WAL streaming based replicas for instant failover in case of outage, and read replicas, also via WAL. This system has been up for four years at this point, with no downtime.

I love SQLite, and use it regularly, just not for production web services.

SQLite has write ahead logging as well

https://sqlite.org/wal.html

It doesn't let you run an active replica from WAL.

> my sweet spot has become a big honking Postgres instance in RDS

Why do you prefer PostgreSQL RDS over Aurora RDS? Aurora seems better in every way but price[1]. (I know it also had some growing pains at launch.)

[1]: Amazon RDS for PostgreSQL is ideal when you have a small-to-medium intense workload. It works best when you have limited concurrent connections to your database. If you’re moving from commercial database engines such as Oracle or Microsoft SQL Server, Aurora PostgreSQL is a better choice because it provides matching performance with a lower price.

https://aws.amazon.com/blogs/database/is-amazon-rds-for-post...

Price is the main reason. Write performance is a second one.

The article I linked implies that Aurora offers higher write performance:

> If your database workload reaches the Amazon RDS max limit of 80,000 IOPS, and it requires additional IOPS, Aurora PostgreSQL is the preferred database solution. If database workload requires less than 80,000 IOPS, you can choose either Amazon RDS for PostgreSQL or Aurora PostgreSQL based on supported features.

The Consider SQLite post mentions that one of SQLite’s in the past decade as “WAL mode (enabling concurrent reads and writes)”. Does this mean that the official advice to avoid SQLite for concurrent writes [1] is no longer a big concern?

[1]: https://www.sqlite.org/whentouse.html

I think the way it's worded in that SQLite documentation page is still accurate:

> SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

If your application needs to support hundreds of concurrent writes a second you shouldn't use SQLite. But that's a pretty high bar!

Also it should be noted that SQLite is a library, so it charges against the quotas for the main process.

This may become an issue with large run-time datasets even in read-only shared access scenarios.

Take this with a huge grain of salt because I am by no means an expert, but I currently am working on some scripts that import a few million rows into SQLite. I am using bash and the sqlite command line. I was getting a lot of concurrent write errors from sqlite (bear in mind i am only doing inserts of separate rows so in theory there is never an actual conflict), so I tried using WAL mode. It actually resulted in more contention. I ended up just going back to non-WAL mode and implementing an exponential backoff in bash to retry writes.

Thanks for this comment! I had tried some bulk imports into SQLite and mostly gave up after hitting similar limits, thinking I was doing something wrong with the configuration.

are you ingesting the data under 1 transaction? This is a common SQLite issue as writes aren't slow but transactions are. By default 1 write = 1 txn but you can put millions of writes into 1 txn and get many orders of magnitude speedup

Yes, each row is written with one call to the sqlite command line. I was going to do something where I wrote inserts into a file and flushed them into sqlite in batches, but sqlite is not the bottleneck, so once retries were taken care of, it was good enough.

Litestream is indeed a missing piece of the puzzle. But it also defeats some of the purpose of using an embedded database library in the first place. Now you're back to juggling separate processes once again.

If the application is in go, you can likely embed litestream.

Good point! Although practically speaking I don't mind at all. "Juggling" is too strong a word — it's literally just starting the Litestream process and never thinking about it again. It's nice that it just slides into my existing app without any code changes.

https://github.com/backtrace-labs/verneuil is in-process (precisely to minimise deployment pain).

I had never heard of verneuil. Thanks for sharing. For anyone curious about the differences between the two:

"This effort is incomparable with litestream: Verneuil is meant for asynchronous read replication, with streaming backups as a nice side effect. The replication approach is thus completely different. In particular, while litestream only works with SQLite databases in WAL mode, Verneuil only supports rollback journaling"

> hits a month

Is not a very useful performance metric. What is your peak hits per second?

I love watching people use "x per month" as some sort of architecture selection argument, especially when these arguments conclude in a proud justification of cloud sprawl.

There are single node/CPU solutions that can process 10-100 million business events per second. I am almost certain that no one logged into HN right now has a realistic business case on their plate that would ever come close to exceeding that capacity.

E.g.: https://lmax-exchange.github.io/disruptor/disruptor.html

This stuff isn't that hard to do either. It's just different.

ESAIDBUSINESSTOOMANYTIMES

Then replace business with the problem you're trying to solve, the good you're trying to do for your users, etc. Keeping the big picture in view doesn't have to mean you're only interested in making money.

I'm not criticising making money. Just s/business//g and it reads better.

I was looking at SQLite for a product I’m working on. It looks awesome and has improved significantly since I last looked.

The reason I decided against it is that it doesn’t have proper stored procedures. I use them a lot in PGSQL. They result in far fewer lines of code.

They also have the benefit of significantly reducing round trip calls to the database, which is one of the key advantages of SQLite.

But having used stored procedures for years, I can no longer bear the thought of writing SQL code in a host language, so I’m going to stick with PG for the time being.

Would be great to see something similar in SQLite; there are other advantages such as the single file database, that would work well in a microservice environment.

So you want to write functions in SQL? With SQLite you can define your own functions which can be called in your queries, but they do need to be written in your application language.

Yeah I know. But I find stored SQL to be a lot more concise than the application language, and less prone to bugs. I also find that it creates a much better separation of concerns between stuff that touches the database versus non-database stuff.

Just one example, error behaviour is well defined in PLPGSQL so I don’t have to constantly check for errors. That’s not true in my host language.

I am super impressed with SQLite, it’s just not a good fit for how I use databases yet.

What about zero downtime deploy of new version of your application? You have to take it down to restart, right?

"(5) ... SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. ..." - https://www.sqlite.org/faq.html

You can start your new version of your application in a new process that opens the same database file, switch your load balancer to the new app, allow the old to drain all requests and then terminate the old app.

how do you handle things like encryption and access permissions?

The only thing I have against using SQLite in production (for my needs) is the lack of at rest encryption and row level permissions by user.

At rest encryption is a complicated subject (in general, I bet most people get negative net security from it). For SQLite, you can either encrypt your disk or get one of the versions with added encryption (I only know of proprietary ones).

You don't do row level permissions on your database. You keep it all on the application layer.

> You don't do row level permissions on your database. You keep it all on the application layer.

You say this as some sort of objective truth. Keeping security about data at the data layer can often be a really good idea and just as appropriate as GRANT/REVOKE at the table and column levels.

Well, if you want to use SQLite, you keep it at the application layer. That's an objective truth.

What is better, depends on what you are doing, and is not a simple choice in any way.

That's a tautology, isn't it? If the persistence engine you're using doesn't support a feature—no matter what that feature is—you'll have to fill in the shortfall somewhere else, such as the app layer.

I'm just saying if you have the option, if the engine does support the feature, and the solution is a good fit for your problem, you shouldn't shy away from it just because some other engine doesn't support it.

Hum, no. The word "tautology" doesn't mean that.

Anyway, yes, SQLite lacks a series of features you get on other SGDBs, often for good reasons. In exchange it brings an entirely different set of features. If you want Postgres, use Postgres.

Reply on news.ycombinator.com