Ask HN: Have you used SQLite as a primary database?
I periodically hear about projects that use/have used sqlite as their sole datastore. The theory seems to be is that you can test out an idea with fewer dependencies (and cost) and that it scales surprisingly far.
There are even distributed versions being built for reliability in the cloud: dqlite by canonical (of Ubuntu fame) and rqlite
Given the complexity it seems like there are use cases or needs here that I'm not seeing and I'd be very interested to know more from those who've tried.
Have you tried this? Did it go well? Or blow up? Were there big surprises along the way?
- https://sqlite.org - https://dqlite.io - https://github.com/rqlite/rqlite
329 comments
[ 2.8 ms ] story [ 487 ms ] threadThe use case is storing trace/profiling data, where we use one sqlite file for each customer per day. This way its easy to implement retention based cleanup and also there is little contention in write locking. We store about 1 terrabyte of data over the course of 2 weeks this way.
Metadata is stored in Elasticsearch for querying the search results and then displaying a trace hits the Sqlite database. As looking at traces is a somewhat rare occurence we iterate over all fileservers and query them for trace data given an ID until we find the result.
Reference https://www.sqlite.org/fasterthanfs.html
As he notes https://www.mozilla.org/ uses this pattern:
> They started using SQLite back in 2018 in a system they call Bedrock ... Their site content lives in a ~22MB SQLite database file, which is built and uploaded to S3 and then downloaded on a regular basis to each of their application servers.
I'm particularly interested in the "Sessions" extension (https://www.sqlite.org/sessionintro.html) and would love to hear if anyone has successfully used it for an eventually consistent architecture built on top of SQLite?
Unfortunately it was far too advanced for the org and no one else understood it so it was canned in favour of a connected solution under the guise of ubiquitous internet access being available. This is proving to be a poor technical decision so my solution may have some legs yet.
It's not perfect and people still complain, to do it cleanly I would need to prompt the user which change to take but I haven't figured out a clean way to do that yet.
How do you handle this? Do you store the SQLite file somewhere like s3 or just in memory?
How does this work for such high traffic sites?
Then I thought this is great and makes it easier to move the db file if it just has 1 table. So I can download it easily for local dev for ex.
And yes in case there's corruption which never rly happens, only one file thus table would be affected.
PS: one thing that really helped reduce issues was setting PRAGMA MODE to WAL
Except for some rare exceptions, it's been doing pretty great. I don't have any plans to migrate from SQLite any time soon.
Also they're where the real insights are.
By default, the connection pool in Rails contains 5 connections, and they time out within 5 seconds.
I switched from Postgres to SQLite for a couple of versions, put mainly because Postgres wasn't "supported" I called SQLite an "internal database thing".
Worked flawlessly for about 7-8 years before both services were gobbled up into micro API services.
At the last count, we have about 14,000 services checked by uptime (about 1,000 every 5 minutes, 2,000 every 10 minutes, the rest every 15). Probably had about 60,000 tinyurls in MyTurl. We also ran the MyTurl urls through uptime every night to look for bad links. The system go hammered, often.
It took minor tweaking to get the the best performance out of the database and AOLserver has some nice caching features, which helped to take the load off the database a bit. But overall, it worked as well as the Postgres counterpart.
And now, I have to figure out why I never released the SQLite version of both.
The only issue is that you'll need to take special care when backing up the DB file (but this is probably the same for most DBs even today.)
https://sqlite.org/faq.html#q5
It's also powering another one and I really like the fact that I can just commit the whole DB to the GIT repo.
I've never (deliberately) considered committing a DB to git. Although there was that one time when I was straight out of college...
Pro tip: surprising your colleagues in the morning with a 40 minute wait to pull master (because you committed a ???GB db) is a good way to feel like a right eegit.
For backup, I have a small bash script that creates a git commit and pushes it to the github.
It works great - there are ergonomic APIs in most languages, it’s fast and reliable, and great to be able to drop into an SQL shell occasionally to work out what’s going on. A custom binary format might be slightly more optimal in some ways but using sqlite saves so much work and means a solid base we can trust.
This is a very respectable goal. I wish you great success!
Good full text search without pulling in another dependency would be quite a win. I'll add fts5 to my reading list. :)
Out of interest, what kind of compute and storage resources do you have underneath that?
It’s a linode. Shared cpu Plan.
1 CPU Core 50 GB Storage 2 GB RAM
It’s just $10 per month.
With linode even shared cpus are powerful and I’m yet to hit any overload. I’m sure it will at some point, I might upgrade then.
Production uses: 0 (1 if my Ph.D. thesis code is included, which had some C++ code that linked against version 2 of the SQLite library).
Glad to hear its type enforcement situation is improving.
Recently I stumbled over a potential fix[1], which I will try in my next project.
[1] https://ja.nsommer.dk/articles/thread-safe-async-sqlite3-ent...
One to look out/test for early if I go in this direction though. Thanks for the heads up!
I think so. sqlite is pretty reliable in my opinion and I never hat these issues anywhere else, but this bug made me switch my DMBS for a small side project from sqlite to postgres.
> One to look out/test for early if I go in this direction though.
You're welcome!
When not to use sqlite:
- Is the data separated from the application by a network?
- Many concurrent writers?
- Data size > 280 TB
For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always better.
[1] https://www.sqlite.org/whentouse.html
Isn't MySQL MyISAM faster and this way constitute a better choice for a scientific number crunching application? I mean near 4GB DB, very simple schema, heavy reading load, little/no inserts and no updates.
Since this is a no-update and no live-insert scenario we're talking about, it's fairly easy to produce code that is an order of magnitude faster than a DBMS, since they're not only primarily optimized for efficiently reading off disk (an in-memory hash table beats a B-tree every day of the week), they've got really unfortunate CPU cache characteristics, and additionally need to acquire read locks.
Two features I enjoy in ActiveRecord and other ORMs, and why I would consider them a good standard practice for most things that aren't "toy" projects.
1. Easy chainability. In ActiveRecord you can have scopes like `User#older_than_65` and `User.lives_in_utah` and easily chain them: `User.older_than_65.lives_in_utah` which is occasionally very useful and way more sane than dynamically building up SQL queries "by hand."
2. Standardization. Maintenance and ongoing development (usually the biggest part of the software lifecycle) tend to get absolutely insane when you have N different coders doing things N different ways. I don't love everything ActiveRecord does, but it's generally quite sane and you can drop a new coder into a standard Rails project and they can understand it quickly. On a large team/codebase that can equate to many thousands or even millions of dollars worth of productivity.
100% agree.ActiveRecord strikes a good balance here IMO. An explicit goal of ActiveRecord is to make it painless to use "raw" SQL when desired.
On non-toy projects, I think a policy of "use the ORM by default, and use raw SQL for the other N% of the time when it makes sense" is very very sane.
If you're just using the database for object persistence, which is common, it doesn't matter all too much. But that's not really the scenario we're discussing here, since the data is by the original problem statement, immutable.
There's a performance hit relative to skipping the database altogether and simply allocating 4GB of RAM and accessing it directly, of course.
I think the performance MySQL has over sqlite comes from its multithreading more than the storage engine.
In my experience sqlite is just as fast as MyISAM for single threaded work.
Faster, but at what price?
[1]: https://pouchdb.com/
[2]: https://couchdb.apache.org/
[3]: https://nozbe.github.io/WatermelonDB/
SQLite proved to be phenomenal. We spec'ed hardware with enough RAM to hold the FR DB in memory, and damn SQLite is fast enough to keep up with the optimized FR system performing 24M face compares per second. With a 700M face training set, SQLite also proved instrumental in reducing the training time significantly. These daze, if given the opportunity to choose a DB I always choose SQLite. I use SQLite for my personal projects, and I go out of my way to not use MySQL because SQLite is so much faster.
Out of interest, were you running on bare metal/cloud? And what kind of CPU was behind those 24M face compares per second?
Our product is a self-hosted IoT & hub unit solution, so we have no requirements to work with thousands of users doing who knows what. For our use case, sqlite is perfect. We don’t need to work with millions of rows, don’t need to stress the relatively low-power server units with another long lived network process, have no requirements of authentication since the user owns all the data, and can easily get insights into the database both during development and during troubleshooting at customer locations.
I’d sooner leave the project than move to anything else.
This [0] is a good article with some benchmarks, misconceptions about speed, and limitations.
[0]: https://blog.wesleyac.com/posts/consider-sqlite
That seems like a pretty big flaw as your data grows. Zero downtime migrations are really nice. Anyone here got a war story / experience with this one?
I've migrated database versions 35 times over the last 13 years, ie, every individual HB database has been migrated 35 times. You don't always need to make a new table, do a copy, and switch over. In the latest migration, I added new columns, initialized them, dropped columns, etc. without doing a db copy.
For this migration I wanted to switch to strict tables, where typing is strict. I could have done this by just altering the schema (it's just a bunch of text in the SQLite db) and then using SQL to make sure existing columns had the right data (using CAST). But instead, I created a general framework to allow me to migrate data from one schema to another, mainly so I could reorder columns if I wanted. That can't be done with ALTER statements, so I did end up doing a complete copy, but I've done many migrations without a copy.
I found this paper interesting on "zero downtime migrations".
https://postgres.ai/blog/20210923-zero-downtime-postgres-sch...
After reading it, the bottom line is that changes happen within transactions (true for SQLite too), and the key to zero downtime migrations is to use short transactions, use timeouts, and use retries on all database operations, including the migration commands. You can do all these with 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.
"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"
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.
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.
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.
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.
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.
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
- 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.
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.
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.
Setup a separate database server and use it for all of your projects. That one hour pays off each and every project.
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.
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.
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.
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.
- 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.
- 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.
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.
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).
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.
So no matter how optimized MySQL and PostgreSQL are, SQLite will run rings around them for basic SELECT queries.
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.
https://sqlite.org/wal.html
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...
> 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.
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.
Is not a very useful performance metric. What is your peak hits per second?
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.
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.
(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)
Checkout the PRAGMA user_version, I just modify on schema change, so app knows where stuff is.
https://www.sqlite.org/pragma.html
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
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.
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.
What is better, depends on what you are doing, and is not a simple choice in any way.
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.
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.
[1]: https://www.sqlite.org/whentouse.html
> 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!
This may become an issue with large run-time datasets even in read-only shared access scenarios.
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.
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.
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.
> Given the complexity
Which complexity? It is the simplest possible widespread, reliable and effective solution. Which makes it a primary choice.
> it seems like there are use cases or needs here that I'm not seeing
On the contrary, the use cases for the traditional Relational DB engines are defined: when you need a concurrency manager better than filesystem access. (Or maybe some unimplemented SQL function; or special features.) Otherwise, SQLite would be the natural primary candidate, given the above.
Edit:
I concur about https://blog.wesleyac.com/posts/consider-sqlite being a close to essential read if one has the poster's doubt.
To its "So, what's the catch?" section, I would add: SQLite does not implement the whole of SQL (things that come to mind on the spot are variables; the possibility of recursion was implemented only recently, etc).
Noting that "recently" was August 2014 (version 3.8.6), according to https://sqlite.org/oldnews.html.
The "missing" right/full join types just hit trunk this past week and are still being debugged: https://sqlite.org/src/info/f766dff012af0ea3
Right. This as an issue has little to do with deployment the way the submitter intended, and has to do with software that was implemented using specific static versions of SQLite and that some sometimes use as no better alternative meanwhile emerged. The "delay" is more evident (more "daily present") to said users, and has little to do with new products.
Nonetheless, given that, I would first check which subset of SQL you may need - I am not sure on how much overlapping you have with other mainstream products.
[1] https://www.gaia-gis.it/fossil/libspatialite/index
[2] http://web.archive.org/web/20190224100905/https://core.ac.uk...