9 ms·
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 htt
by Glench 4y ago
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 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/ 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 https://extensionpay.com — Lets developers take payments in their browser extensions.
- fauigerzigerk 4y agoLitestream 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.
- fsaintjacques 4y agoIf the application is in go, you can likely embed litestream.
- Glench 4y agoGood 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.
- pkhuong 4y agohttps://github.com/backtrace-labs/verneuil https://github.com/backtrace-labs/verneuil is in-process (precisely to minimise deployment pain).
- juancampa 4y agoI 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"
- mekster 4y agoWhat 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.
- Glench 4y ago> 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.
- iampims 4y agoAny tips you can share on how to deploy your saas app without downtime when using sqlite?
- srcreigh 4y agoFor 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.
- iampims 4y agoWhich 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.
- ignoramous 4y ago> 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.
- therealdrag0 4y ago> hits a month Is not a very useful performance metric. What is your peak hits per second?
- bob1029 4y agoI 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 https://lmax-exchange.github.io/disruptor/disruptor.html This stuff isn't that hard to do either. It's just different.
- robertlagrant 4y agoESAIDBUSINESSTOOMANYTIMES
- mwcampbell 4y agoThen 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.
- robertlagrant 4y agoI'm not criticising making money. Just s/business//g and it reads better.
- JohnBooty 4y agoThis 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.
- djbusby 4y agoI 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)
- JohnBooty 4y agoInteresting, 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)
- djbusby 4y agoI'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 https://www.sqlite.org/pragma.html
- tianzhou 4y agoWe 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 https://news.ycombinator.com/item?id=31038614
- no_wizard 4y agohow 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.
- marcosdumay 4y agoAt 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.
- ttfkam 4y ago> 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.
- marcosdumay 4y agoWell, 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.
- ttfkam 4y agoThat'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.
- divbzero 4y agoThe 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 https://www.sqlite.org/whentouse.html
- simonw 4y agoI 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!
- zoomablemind 4y agoAlso 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.
- woah 4y agoTake 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.
- hampelm 4y agoThanks 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.
- ledgerdev 4y agoWhat about zero downtime deploy of new version of your application? You have to take it down to restart, right?
- adamckay 4y ago"(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 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.
- doctor_eval 4y agoI 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.
- config_yml 4y agoSo 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.
- doctor_eval 4y agoYeah 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.