Every app that remembers anything keeps it in a database. When you run an app on your own server, for your company or your customers, the choice of that database decides more than speed. It decides how many things you have to keep running, how backups work, what happens when you move to another server, and how easy it is to say with confidence where your data is.
For most of what we build, the answer is SQLite. For some, it is PostgreSQL. Several of our server products support both and let the person running them choose. This post explains the difference in plain words and how we decide.
Two different ideas of a database
PostgreSQL is a database server. It is a separate program that runs all the time, usually next to your app or on a machine of its own. Your app connects to it over a connection, logs in with a username and password, and sends its questions. The data lives in a set of files that only the database server touches.
SQLite is a database inside the app. There is no second program. The app reads and writes one ordinary file on disk, and that file is the whole database: every table, every record, every setting. The SQLite project describes it well: it does not try to compete with database servers, it competes with saving data into a plain file, and wins by giving that file the safety of a real database.
Both are mature, free and open source, and both are trusted with very important data around the world. Neither is the “toy” option. They solve different problems.
Fewer moving parts, less to look after
Every extra program on a server is something to install, update, secure, monitor and pay for. A database server comes with its own list:
- A process to keep running and restart when it stops.
- Accounts and passwords for the app to log in, which have to be stored somewhere and rotated.
- A network port, even if it only listens inside the machine, which has to stay closed to the outside world.
- Its own upgrades. Moving PostgreSQL to a new major version is a planned job, not a click.
- More memory and disk, which on a rented server means a bigger bill.
With SQLite all of that disappears. The database is part of the app, so it updates with the app. There is no login to leak and no port to protect. A small app with SQLite can run comfortably on the cheapest server a hosting company sells.
For a privacy first studio this is not only about cost. Less software means less to attack and less to misconfigure. The same thinking is behind our habit of not collecting data in the first place: what is not there cannot go wrong.
Your data is one file you can see
The part we like most about SQLite is ownership. When an app keeps everything in one file on your server, you can point at it and say “that is all of it.”
- Moving to another server means copying the file.
- Handing your data to someone else, or taking it with you when you leave a vendor, means handing over the file. Any SQLite tool can open it, on any computer, without our software.
- Deleting it means deleting the file and its backups. When an app keeps a separate file per customer, removing one customer is just as direct.
With a database server the data is still yours, but it sits inside a running system. Getting it out means an export, and the export is only as complete as the person running it makes it.
One honest detail about backups: you cannot simply copy an SQLite file at a random moment while the app is writing to it, because the copy may catch a change halfway. SQLite has built-in ways to take a safe copy of a live database, and our backups use them, so each one is still a single, consistent file. PostgreSQL has the same idea in its own export tool, which takes a consistent snapshot while people keep working.
Where SQLite stops and PostgreSQL starts
SQLite has one real limit worth knowing: only one write can happen at a time. Any number of people can read at once, but changes take turns. A typical change takes a few milliseconds, so the queue is short and nobody notices. The SQLite project’s own guidance says a website with fewer than 100,000 visits a day should do fine, and calls that figure conservative. Their page on when to use SQLite is unusually honest about the limits of their own product.
PostgreSQL is the better choice when:
- Many people change data at the very same moment, all day. A busy shop checkout, a booking system at peak hours, a chat service with thousands of active users. PostgreSQL is built so that reading never blocks writing and writing never blocks reading.
- The app runs on several servers at once. An SQLite file lives on one machine. Several app servers sharing one database need a database server they can all reach.
- Your team already runs PostgreSQL. If your company has people who operate it every day, with monitoring and backups in place, adding one more database to their setup is cheaper than introducing something new.
- The data grows very large. SQLite’s size ceiling is far beyond anything a small business will reach, but one huge file on one disk is harder to manage than a database server built to spread out.
How we decide, in practice
For each new app or service we ask a few questions:
- Will it run on one server? Almost everything a small or medium business runs does. If yes, SQLite is the starting point.
- How many writes at the same moment, at the busiest hour? Tens per second: SQLite, comfortably. Thousands, steadily: PostgreSQL.
- Who will run it? A customer without their own IT team is better served by one program and one file. A company with a database team may prefer what they already know.
- Will the answer change? If growth could push an app past SQLite, we write it so that it can use either. Our open source analytics server, OXI Metric, works with SQLite or PostgreSQL. Its documentation recommends SQLite below about 10,000 daily users, either one up to 100,000, and PostgreSQL beyond that, with a way to move from one to the other later.
In our own work this plays out as you would expect. Our apps that keep records, like the habit tracker and the calendar, store them on your device in a local SQLite database. Our sync and analytics servers run on SQLite, each a single program with a single file. The project tracker our team works in, a multi-user tool built by a partner, runs on PostgreSQL. We picked the language for those servers with the same kind of reasoning; we wrote about that in Go or Rust, how we pick the language.
What to ask your developer
If someone is building or installing an app on your server, a few questions tell you a lot:
- Where exactly is my data stored, and can I get a copy without your help? A clear answer is one file, or one export command, that opens without their software.
- How are backups taken, and have you tested a restore? A backup nobody has restored is a hope, not a backup.
- What else has to run next to the app? Each extra service is something you pay for and someone has to keep safe.
- At what size would you change the database, and how hard is that? A good team knows the limit of its choice before you hit it.
There is no winner here, only a fit. For an app you run yourself, the simplest setup that handles your real load is usually the safest one. Most of the time that is a single file you can see, copy and delete.