Why use Postgres if all you need is sqlite? Postgres is way overkill for a simple app with few users and no advanced database functionality.
Why use Postgres if all you need is sqlite? Postgres is way overkill for a simple app with few users and no advanced database functionality.
Because Postgres is mature, works, and has a version number above v1.0?
If v1.0 is your North Star, you should re-evaluate a whole lot of software in your stack: https://0ver.org/#notable-zerover-projects
I think you're focusing on the wrong parts of the comment.
People care about things like long-term support. Postgres 13, from 2020, is still officially supported. Litestream 0.1.0 was the first release, also from 2020, but I can't tell if it is supported still. Worrying about the maturity, stability, and support of an application database is very reasonable in risk adverse projects.
Litestream is just a backup solution. Should probably be compared to a backup solution for postgres that does automated backups over the network etc. That isnt part of postgres.
Besides the question wasnt litestream vs postgres backup apps. It was sqlite vs postgres.
The original response at least concerned litestream because the not-1.0 comment only applies to that.
Version numbers dont mean anything as the whole Elixir ecosystem shows:D
this is essentially the "no one ever got fired for buying IBM" statement. One counter is why buy & manage a rack-mounted server when all you need is a raspberry Pi?
Postgres can also run on the Pi.
More than once I've started a project with sqlite and then had to migrate to postgres. In my experience it's because of the way sqlite has to lock the whole database file while writing to it, blocking concurrent reads - this isn't a problem in postgres. (There's WAL mode, but it still serialises all writes, and requires periodic explicit checkpointing IME)
You may also find you really want a feature postgres has, for example more advanced data types (arrays, JSON), more advanced indices (GIN inverted index on array members), replication...
I'm guessing this is a joke?
Why would saying that Postgres is a mature database - more mature than SQLite - be a joke?
SQLite is probably more mature than Postgres in terms of service hours.
I understood the comparison to be with Litestream, not SQLite in general.
Why would we compare Litestream (a backup solution) with Postgres (a database)? Would be like comparing Linux with NGINX.
Postgres has a similar replication solution built-in. It's like comparing Samba with Windows Server if your purpose is to run a SMB file server.
Both are mature. There are way more sqlite databases running than postgres. The code base is smaller and has less new features added to it every year. What is unstable with sqlite?
For a cloud service, I think it comes down to whether you’ll ever want more than one app server.
If you’re building something as a hobby project and you know it will always fit on one server, sqlite is perfect.
If it’s meant to be a startup and grow quickly, you don’t want to have to change your database to horizontally scale.
Deploying without downtime is also much easier with multiple servers. So again, it depends whether you’re doing something serious enough that you can’t tolerate dropping any requests during deploys.
This is the idea behind LiteFS --- to transparently scale out SQLite (in some very common configurations and workloads) to support multiple app servers. It's still there and it works! It's just a little ahead of its time. :)
That makes sense, and it seems really cool from a tech perspective. I guess I'm just inherently skeptical about using something shiny and new vs. battle hardened databases that were designed from the beginning to be client-server.
It's definitely really nice though that if you do choose sqlite initially to keep things as small and simple as possible, you don't immediately need to switch databases if you want to scale.
I think that's very fair. But the use case for Litestream is much simpler and you can get your head around it immediately. It also doesn't ask you to do anything that would commit you to SQLite rather than switching to Postgres later. It's just a way of very easily getting a prod caliber backend up for an app without needing a database server.
99,99% of apps dont need more than one app server. You can serve a lot of traffic on the larges instances.
For sure downtime is easier with kubernete etc but again overkill for 99,99% of apps.
Serving users is one thing. Then you want to run some interactive analytics or cronjobs for cleanup etc on the db. Even if the load can manage it, how would the admin jobs connect to the database. I’ve never seen a db with only one client. There is always some auxiliary thing, even when you don’t consider yourself a microservice shop.
For analytics the solution is very simple. Just copy the database and run the queries on the copy.
agree - with SQLite and DuckDB I've really switched my mindset from one perfect, pristine DB to multiple copies and a medallion architecture that looks more like participation ribbons for everyone! The resources required are so cheap & lightweight when you avoid the big "BI focused" tech stacks.
you can also scale out across unlimited tiny servers, because the entire stack is so lightweight and cheap. This will also force you to focus on devops, which otherwise can become a grind with this approach. The only challenge is when you have cross-DB concerns, either data or clients.
Right, but if your goal is to have a lot of users (and minimal downtime), there's no point in putting a big avoidable obstacle in your path when the alternative is just as easy.
If your goal is to serve billions of users you should probably use cassandra etc. Why limit yourself to postgres if your goal is to have a billion users online at the same time?
Because cassandra isn't easy to set up and has all kinds of tradeoffs on consistency, transactions, et al compared to an SQL db.
On the other side, why not just store everything in memory and flush to a local json file if you won't have any users? sqlite is overkill!
your goal should be to solve this problem when you have a million or 10M concurrent users. YAGNI
I'd argue that anything larger than a desktop app should not use SQLite. If you need Litestream for replication and backup it is probably better to just use Postgres. There are a ton of one-click deployment offerings for proper databases, Fly.io actually offers managed Postgres.
Why would you argue that? Do you have some benchmarks backing it up or is it more a personal preference?
It's literally what they're designed for.
SQLite is designed for one local client at a time. Client-server relational databases are designed for many clients at a time.
That's not entirely true. SQLite is designed to support many processes reading the same file on disk at once. It only allows one process to write at a time, using locks - but since most writes finish in less than a ms in most cases having a process wait until another process finishes their write isn't actually a problem.
If you have lots of concurrent writes SQLite isn't the right solution. For concurrent reads it's fine.
SQLite also isn't a network database out-of-the-box. If you want to be able to access it over the network you need to solve that separately.
(Don't try and use NFS. https://sqlite.org/howtocorrupt.html#_filesystems_with_broke... )
the reality is very few workloads have access patterns that SQLite can't support. I would much rather start with a strategy like 1. use sqlite for my beta / single client, 2. duplicate the entire environment for the next n clients, 3. solve the "my application is wildly successful" and SQLite is no longer appropriate problem at a future date. Spoiler: you're never going to get to step #3.
> 2. duplicate the entire environment for the next n clients
That becomes an instant problem if users ever write to your database. You can't duplicate the environment unless it's read-only.
And even if the database is read-only for users, the fact that every time you update it you need to redeploy the database to every client, is pretty annoying.
That's why it's usually better to start with Postgres or MySQL. A single source of truth for data makes everything vastly easier.
Not true. Can you back up your claim that the developers of Sqlite says they dont recommend it for webservers? (hint they recommend it).
If you have a read-heavy app (99% of saas) that runs on one server and dont have millions of users then sqlite is a great option.
I didn't say that. I said one local client at a time. If you're running on one server then your webserver is the one local client.
Usually you want to be able to run multiple webservers against a single database though, since that's the first thing you'll usually need to scale.
Let's say I'm building a small app that I'm hosting on some shared vps, if I think about the effort involved in setting up sqlite with litestream and just getting a $5 (or free) postgres provider I don't think sqlite makes my life easier.
Now if I'm building a local app then absolutely sqlite makes the most sense but I don't see it otherwise.
Litestream is dead simple to setup. You make an S3 bucket (or any compatible storage bucket), paste the access keys and the path to your db file in /etc/litestream, and then run
The fact it's so simple is my favourite thing about it.Are there any use cases/documentation about how litestream can be used within a docker based deployment? (Eg where systemctl wouldn't be used)
Here's their docs on running in a Docker container: https://litestream.io/guides/docker/
You'd probably want to put the sqlite db in a volume & run litestream in a separate container that restarts automatically on failure.
Systemctl's only in there to restart it if it crashes; litestream itself is (iirc) a single cli binary.
This is documented on the Litestream website.
Effort of setting up litestream and sqlite is less time than you spend signing up for supabase. And you can have 100 apps with their own databases for almost free (just a few cents of storage) vs 5*100 for postgres.
I love postgres but in no way is it as simple to run as sqlite (pretty sure even postgres core team would agree that postgres is more complex than sqlite).