Most database choices get made by whoever argues loudest. This guide replaces the argument with three questions about your own app, answered in order in Steps 1 to 3. Each answer keeps the default or overturns it.
The default is PostgreSQL.
Prerequisites
One paragraph describing what the app stores
The five queries the app runs most
A rough estimate of peak writes per second
Ten minutes
Step 1: Can you name every query?
Write down the five most common queries. Then ask whether you can name every query this app will ever run.
AWS's own DynamoDB guidance says to know the questions before designing the schema. A relational model can be designed first and extended when new questions arrive.
Read the result:
New questions will keep arriving (reports, filters, admin screens, joins): choose SQL.
The list is closed and every query is a lookup by key: a key-value store like DynamoDB is a candidate.
Most products can't pass the second test. The questions keep coming.
Step 2: What happens when a write fails halfway?
Take the most important write in the app. Placing an order is the classic one:
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE sku = 'A100' AND stock > 0;
INSERT INTO orders (sku, user_id) VALUES ('A100', 42);
COMMIT;
Now ask what happens if the process dies after the first statement.
Partial state is a bug (money, inventory, bookings, permissions): you need transactions. Choose SQL. Both statements land or neither does.
Partial state is harmless (counters, sessions, caches, logs): any store works.
Without transactions, the guarantee moves into application code, where every service has to get it right.
Step 3: How many writes per second at peak?
Reads scale by adding replicas. Writes go to one primary. The number that decides the architecture is peak writes per second.
OpenAI serves 800 million users from a single primary PostgreSQL instance with nearly 50 read replicas, at millions of queries per second. The same post describes where it strained. Write-heavy workloads that partition cleanly moved to sharded systems like Azure Cosmos DB. Reads scaled. Writes hit the ceiling.
Read the result:
Hundreds to a few thousand writes per second: one PostgreSQL primary covers it.
Write volume far beyond one machine, and the data partitions by key (per user, per tenant, per device): that workload belongs in a sharded store.
Step 4: Does JSON data need a document store?
"Our data is JSON" is the most common reason teams reach for a document store. PostgreSQL stores JSON natively. Its docs recommend jsonb for most applications, and jsonb supports indexing.
CREATE TABLE events (
id bigserial PRIMARY KEY,
payload jsonb NOT NULL
);
CREATE INDEX events_payload_idx ON events USING GIN (payload);
SELECT id FROM events WHERE payload @> '{"type": "signup"}';
Keep documents small, since any update locks the whole row.
Step 5: Pick
Match the answers:
Open queries, transactions matter, one primary handles the writes: PostgreSQL. Most apps land here.
Embedded or single-machine app, one process writing: SQLite. Its own guidance covers sites under 100K hits a day and points to client/server databases once data crosses a network, writers multiply, or size nears a terabyte.
Closed query list, harmless partial failure, very high write volume: a key-value store.
A mix: PostgreSQL for the core, a specialized store for the one workload that broke the answers. OpenAI runs this pattern.
PostgreSQL is the default because it says yes to the most answers at once. It handles open queries, transactions and JSON in one system. Its main weakness, very high write volume, appears only at large scale, and Step 3 measures it.
What this costs
PostgreSQL handles heavy writes worse than reads. An update copies the entire row, which multiplies write load. Sharding later is a real project. OpenAI's post estimates it could take months or even years for existing workloads.
A key-value store costs flexibility. Queries outside the designed patterns turn expensive and slow, and every new question can mean a new table design.
Both costs are known in advance. That is the argument for deciding with numbers.
Default to PostgreSQL. Make every alternative earn its place with a number.