For most new apps, choose Postgres. It is a mature relational database that handles structured records, relationships, money, search, and JSON in one place, and nearly every host and tool supports it. Choose a document store only when your data has no stable shape, and a spreadsheet when a few people are simply keeping a list.
Every product we run that stores real data stores it in Postgres. CaseCobra keeps a law firm's cases and billing in it. CoverageCobra runs a marketplace on it. Regulars tracks loyalty cards and campaigns in it.
Those are three different kinds of application on the same kind of database. That is the point of having a default. The interesting question is when to leave it.
This post explains the default, the exceptions, and how to tell which case you are in.
What database should a new app use?
A new app should use Postgres unless there is a specific reason not to. Postgres is a relational database, which means it stores data in tables with defined columns and enforces the relationships between them. It is open source, decades old, well documented, and offered by almost every hosting provider.
The reasons are practical, not fashionable:
- Most business data is relational. Customers have orders. Orders have line items. Cases have clients, hearings, and invoices. Tables with relationships describe that directly.
- It enforces rules for you. A column can be required, unique, or tied to a row in another table. Bad data is rejected at the door instead of found six months later.
- Transactions keep money honest. Several changes can succeed or fail together, so a payment is never recorded without the order it belongs to.
- It bends when needed. Postgres has a JSON column type for the parts of your data that do not have a fixed shape, plus full-text search and many extensions.
- You can leave. Because it is a standard, open database, you can move between hosts without rewriting the application.
The last point matters more than it sounds. A database is the hardest part of an app to replace. Picking one that many vendors host keeps you out of a corner.
Why does a relational database matter for business software?
A relational database matters because it makes the database, not the application code, responsible for keeping data consistent. If two requests arrive in the same instant, or a bug slips into one screen, the rules stored in the database still hold. That protection is hard to add later.
Our marketplace is the clearest example. When two attorneys try to accept the same court appearance in the same second, exactly one has to win. As we wrote in what it takes to build a two-sided marketplace, that rule is enforced where the data lives. An interface can hide a button. Only the database can guarantee the outcome.
The same applies to anything involving money, inventory, bookings, or legal records. If being wrong is expensive, you want constraints and transactions.
When is a document database the right choice?
A document database is the right choice when your records do not share a stable structure, when each record is read and written as one whole unit, and when you rarely need to combine records in queries. Event logs, content with unpredictable fields, and cached copies of outside data are common fits.
A document store keeps each record as a self-contained blob, usually JSON, with no fixed columns. That is convenient at the start, because there is no schema to design. The cost arrives later, when you need a report that combines several kinds of record, or you discover that half your documents spell a field one way and half another.
Our honest view: most teams that reach for a document store first do it to skip schema design, and schema design is thinking you have to do anyway. If part of your data is truly shapeless, a JSON column inside Postgres often covers it without a second database to run.
When is a spreadsheet enough?
A spreadsheet is enough when a small number of people maintain a list by hand, nobody outside the business touches it, and nothing breaks if two people edit at once. Price lists, contact lists, and content calendars often live happily in a spreadsheet for years.
We say this because it saves people money. If the real need is "three of us keep track of forty suppliers", a shared spreadsheet is the correct tool and an app would be a waste.
The signs you have outgrown it are specific: customers or staff need their own logins, the same fact is typed into several sheets, a formula mistake has cost you money, or you want software to act on the data automatically. At that point the spreadsheet has become a database with no rules, and it is time for one with rules.
Which database fits which kind of data?
The right database depends on the shape of the data and what happens when it is wrong. Structured business records belong in a relational database. Shapeless or append-only data can suit a document or key-value store. Hand-kept lists can stay in a spreadsheet.
| What you are storing | Good fit | Why |
|---|---|---|
| Customers, orders, bookings, invoices | Postgres | Relationships and rules matter |
| Anything involving payments | Postgres | Transactions keep records consistent |
| A few fields that vary per record | Postgres with a JSON column | Flexibility without a second system |
| Records with no common structure | Document store | No schema to fight |
| Sessions, counters, short-lived cache | Key-value store | Fast, simple, disposable |
| Files, photos, PDFs | Object storage, with a row pointing to it | Databases are poor file cabinets |
| A list a few people edit by hand | Spreadsheet | No software needed |
| A single-user app on one device | An embedded database | No server to run |
Notice how many rows say Postgres. That is the argument for a default.
How does hosting affect the database choice?
Hosting affects how you connect to the database more than which database you pick. An always-on server keeps a pool of open connections. Serverless code starts and stops constantly, so it needs a pooler or an HTTP driver, and usually a hosted database built for that pattern.
We use both arrangements. CaseCobra runs in a container and uses an ordinary PostgreSQL database. Regulars runs on a serverless platform with a serverless Postgres service. The engine is the same. We explain the second arrangement in what is serverless Postgres, and the hosting side in serverless vs traditional hosting.
For local development on Regulars we skip installing a database at all and run Postgres compiled to WebAssembly inside the app. Same SQL on the laptop and in production.
Frequently asked questions
Is MySQL a bad choice?
No. MySQL is a mature relational database and plenty of serious software runs on it, including WordPress. If your team knows it well, use it. We prefer Postgres for new work because of its data types and extensions, not because MySQL fails at anything basic.
Do I need a separate database for search?
Usually not at first. Postgres includes full-text search that is good enough for searching names, titles, and documents inside a typical business app. A dedicated search engine earns its place when search is the product itself or the volume is very large.
Can I change databases later?
You can, but it is one of the more expensive changes in software, because every query and every stored record is affected. Moving between hosts of the same database is far easier than moving between kinds of database.
Does a small app need backups?
Yes. Size has nothing to do with it. Use a host that takes automatic backups, and restore one at least once so you know it works.
If you are planning an app and are unsure how its data should be stored, we are happy to look at it with you. Tell us what you are building.