PostgreSQL vs NoSQL Databases
Pedal's mobile team wants to log every tap in the app, and someone suggests "a NoSQL database, because it is faster". Before you add a second system to run, back up and secure, you should know what NoSQL actually trades away, and how much of it PostgreSQL already does.
The rule
NoSQL trades guarantees for one shape of scale; PostgreSQL keeps the guarantees.
"NoSQL" is not one thing. It is a family of databases that each choose one data shape and optimise for it:
- Document stores (MongoDB, Couchbase) keep JSON-like documents.
- Key-value stores (Redis, DynamoDB) fetch a value by its key, very fast.
- Wide-column stores (Cassandra, ScyllaDB) spread huge write volumes across many machines.
- Graph databases (Neo4j) follow links between nodes.
Most of them relax something PostgreSQL insists on: a fixed schema, joins, multi-row transactions, or reading the very latest write everywhere. In return they get easier scale-out or a natural fit for one access pattern.
PostgreSQL narrows the gap from its side. Its jsonb type stores documents in a binary form that can be indexed and queried:
CREATE TABLE app_events (
event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
member_id bigint REFERENCES members (member_id),
payload jsonb NOT NULL
);
SELECT count(*) FROM app_events
WHERE payload @> '{"screen": "unlock"}';The events keep a flexible shape, yet they still sit inside transactions and can still join to members.
The shortcut
Ask three questions in order. Stop at the first "yes".
- Does the data need transactions across several records, or joins? Stay with PostgreSQL.
- Is the only problem a flexible shape? PostgreSQL with
jsonbis usually enough. - Is the write volume beyond what one well-sized server can take, or is the access purely by key at huge scale? Now a NoSQL store earns its place.
Transactions or joins?
Events are appended one by one, but analysts join them to
members. That is a join, a point for PostgreSQL.
The trap is "NoSQL is faster". It is faster at the one pattern it was built for, and often slower or impossible at everything else, such as ad-hoc reports that join data.
Drills
Answer fast — trust the rule.
Which PostgreSQL feature most directly replaces a simple document store?
A service needs to record millions of sensor readings per second across dozens of machines. Which store fits best?
Moving a payment and its ledger entries must succeed or fail together. What does that point to?
What does payload @> '{"screen": "unlock"}' test?
Remember
- NoSQL trades guarantees for one shape of scale; PostgreSQL keeps the guarantees.
- Ask: transactions or joins? only flexible shape? beyond one server? Stop at the first yes.
- "Faster" only holds for the pattern the NoSQL store was built for.