How to Choose a Database for Your App: A Decision Guide

Quick answer
Default to PostgreSQL unless you can name a specific requirement it fails. It covers relational data, JSON, search, and vectors in one engine.
Five questions decide the choice: workload shape, consistency requirements, data shape, expected scale, and how much operational work you’ll accept.
Choose the data model carefully — that’s expensive to change. Choose the host loosely; moving from a VPS to a managed service is routine.
“NoSQL” describes a family, not a speed tier. Document stores are slower than relational databases for relational queries.
Add a second database only for a specific job it does better — a cache, a search index, an analytics store — never as a general replacement.
The comparison table below covers the realistic options; the questions before it are how you pick a row.

The five questions that actually decide this

Most database arguments are about brands. The decision is made by your workload, and a handful of questions narrow it faster than any feature comparison:

What does your data look like? Rows with relationships between them, or independent documents you fetch whole? If you’ll need joins, you want a relational engine.
What does your access pattern look like? Are queries known in advance and keyed (fetch this user’s orders), or ad-hoc and analytical (group revenue by region and month)? The first favors row stores, the second favors columnar ones.
What consistency do you need? A balance read after a transfer must be current. A feed of trending posts can lag ten seconds. The stricter the requirement, the more you want a single strong-consistency primary.
How big will this get, honestly? Hundreds of thousands of rows and hundreds of millions lead to different products — but almost every new app is in the first bucket, and Postgres handles the middle of it on one server.
How much operational work will you do? Self-hosting a distributed database means running backups, upgrades, failover, and monitoring yourself. A managed service is a line on a bill in exchange for that work.

Answer those five and the shortlist is usually one or two products. The rest of this page is the reference for picking between them.

The realistic options, compared

	PostgreSQL / MySQL	MongoDB (document)	Redis (key-value)	DynamoDB (wide-column, managed)	SQLite (embedded)	ClickHouse (columnar)	Vector index (pgvector, Pinecone)
Data model	Tables, rows, joins	JSON documents	Key → value	Items keyed by partition key	Tables in a file	Columns	Embeddings + payload
Best for	Transactional app data	Whole-document reads, schema that varies per record	Cache, sessions, queues, rate limits	Very large keyed workloads with predictable access	Local apps, tests, edge, embedded	Aggregating billions of events	Semantic search, RAG
Consistency	Strong, ACID	Strong on a primary	Strong per key	Tunable, eventually consistent by default on reads	Strong, local	Eventually consistent ingest, strong per query	Depends on implementation
Scaling model	Vertical; read replicas; sharding adds work	Sharding built into the product	In-memory, cluster for size	Partition-key based, automatic	Single file	Distributed columnar	Shards by index size
Ops burden	Low managed, moderate self-hosted	Low managed, moderate self-hosted	Low, but you must plan memory	Very low (fully managed)	None	Moderate to high	Low managed
Wrong when	Your data is genuinely keyed documents with no relationships	You need joins, aggregates, or ad-hoc queries	You need durability as the system of record	Your access pattern isn’t keyed, or queries change often	Multiple app servers write concurrently	You need single-row updates and transactions	You need transactional writes

Read the last row first. Every option above is excellent at what it was built for and a poor fit for at least one thing you might ask of it, and the failures are specific rather than gradual.

Start with Postgres, and know why

PostgreSQL is the default answer because it’s the widest safe choice, not because it’s the fastest at any single job. Within one engine you get transactions and joins, a JSONB column type for semi-structured data that doesn’t justify its own table, full-text search good enough to delay adding a search product, and vector similarity search through an extension — which matters if your app does retrieval over embeddings.

That breadth means one dump, one backup schedule, one set of credentials, and one query language across most of your app. The competing approach — a document store for records, a key-value store for sessions, a search index for text, a vector database for embeddings — is four things to operate, four things to back up, and four ways to be inconsistent with each other. Add a second engine when the job is clearly distinct and clearly worth the cost, not at the start.

What Are the Different Types of Databases (and What Is Each One For)? — The catalog behind the table above, family by family.
What Is an ORM (and When Should You Use Raw SQL)? — How your choice of engine reaches your application code.
How to Choose a Cloud Provider for Your AI-Generated App — Where the database runs, which is a separate decision from which database it is.

Match the engine to the access pattern, not the data’s name

Two apps can both store “users and their orders” and need different engines, because the deciding factor is how the data is read.

A transactional app reads and writes single records by key, keeps relationships consistent, and updates rows all day. That’s a row store: Postgres or MySQL.

An analytics workload scans enormous ranges of historical data and aggregates it — never updating one row, always reading millions. A columnar store like ClickHouse answers those queries in a fraction of the time a row store does, because it reads only the columns the query needs.

A cache or session store answers “what’s the value for this key” millions of times and can lose its contents and recover from the source of truth. Redis. Note that the recovery-from-source part is the requirement: a cache that can’t be rebuilt without data loss is a database wearing a cache’s name.

A vector workload asks “which of these million embeddings is closest to this one” for retrieval-augmented features. pgvector inside Postgres handles this until the index is large enough that a dedicated service earns its operational cost.

Most apps are the first category with a little of the third. That combination is why Postgres plus Redis covers so much ground.

What changes when you scale

The scaling question is usually asked too early, but it’s worth knowing which direction each choice grows. Row stores grow up: a bigger server, then read replicas for read-heavy traffic, then partitioning. Document and key-value stores grow out: adding nodes distributes keys automatically, which is their main structural advantage, at the cost of cross-node queries being awkward or impossible.

Columnar stores are built to grow out from the start, because their workload is analytics rather than transactions.

The practical consequence for a new app: vertical scaling on a row store gets you surprisingly far, and the three fixes that buy the most headroom — indexes, eliminating N+1 queries, caching — don’t depend on which engine you chose. Reach for distribution when you’ve measured saturation, not before.

What Is Database Indexing (and Why Is My Query Slow)? — The first lever, on any engine.
What Is the N+1 Query Problem? — The second, and the one generated code produces most.
How to Reduce Your Cloud Bill Without Breaking Production — Database sizing is usually the largest line item to review.

Managed versus self-hosted, and the real cost

The hosting decision is separate from the engine decision, and much easier to reverse. A managed database gives you automated backups, point-in-time recovery, patching, connection pooling, and failover; you pay a premium over raw compute for them. Self-hosting on a VPS is cheaper per unit of CPU and makes you responsible for every one of those, including the backup you have to restore at 2 a.m.

The honest comparison isn’t the monthly price. It’s the price plus the hours of work you’ll do on upgrades, monitoring, and recovery — and the cost of the one time the restore doesn’t work because it was never tested.

How to Set Up Automated Database Backups — Whether managed or not, verify a restore before you need one.
Budget Cloud and PaaS Compared: Hetzner, DigitalOcean, Railway, Fly.io, Render, Vercel, Netlify — What managed hosting costs at the small end.
What Is a Connection Pool? — The detail that breaks first when you move hosts.

Where this bites vibecoders
Ask an AI assistant to pick a database and you’ll get an answer shaped by frequency in its training data, stated with the same confidence as a correct one. Ask for “a fast NoSQL database” and you may get one chosen to match the adjective rather than your queries; ask for a schema for a relational app and it will happily emit documents. The failure isn’t usually the engine being bad — it’s the engine being wrong for the access pattern while looking productive: a document store with no joins means the assistant starts fetching related records in loops, which is the N+1 problem with no index to fix it. The way to catch this is to state your access patterns in the prompt before asking for the choice, and then to check the answer against the questions above rather than against how modern the product sounds. The decision is cheap to get right early and expensive to change after the data model is in production.

Checklist

Write your five most important queries down before choosing an engine.
Default to Postgres; require a named reason for anything else.
Pick the data model deliberately — it’s the expensive half of the decision.
Add a second database only for a specific job it clearly does better.
Make sure anything you call a cache can be rebuilt from the source of truth.
Use a managed service unless you want to own patching, failover, and restores.
Test a restore before you depend on the backup.
Revisit the decision when you’ve measured saturation, not when you read about sharding.

FAQ

What database should I use for a new app?

PostgreSQL, in the overwhelming majority of cases. It handles relational data, JSON documents, full-text search, and vector embeddings, it runs on every host from a $5 VPS to a managed cloud service, and every AI assistant writes it competently. Choose something else only when you can name the specific requirement Postgres doesn’t meet — not because the workload sounds modern.

Is NoSQL faster than SQL?

No. Both are fast for the access patterns they were designed around, and both are slow for the ones they weren’t. A key-value store answers a lookup by key faster than a relational database because it does less work, not because it’s a better engine. If your queries are relational — joins, aggregates, ad-hoc filters — a document store will be the slower choice.

How do I know if I need to scale beyond one database server?

You need to scale when a single server’s CPU, memory, or disk throughput is consistently saturated after you’ve added indexes, fixed N+1 queries, and added a cache. That’s a measured condition, not a guess. Most applications never reach it, and those that do usually get years of headroom from those three fixes first.

Can I switch databases later?

Swapping the host or the managed provider is routine. Changing the data model — relational to document, or adding a graph — means rewriting every query and migrating every record, and it almost always costs more than the original build. Decide the data model carefully now; keep the hosting decision loose.