Short answers to what readers ask most about this topic.
How to choose between SQL and NoSQL for a new system: the four NoSQL families, modelling from access patterns, and when Postgres JSONB is enough.
Matthews Wong
Software Engineer
Key Takeaway
Choose a database by listing the queries the system must answer, not by SQL versus NoSQL labels. Default to PostgreSQL, whose jsonb type covers most flexible-schema needs. Move to a key-value, document, wide-column or graph store only when one specific access pattern, scale or data shape cannot be served by indexed tables.
Every time I start a new ERP or POS module, the same question shows up in the first design conversation: does this one need a different database? The question is usually phrased as SQL versus NoSQL, and that phrasing is the first mistake, because it asks you to pick a label before you know what the system has to do.
This post answers the question the way the vendor documentation does: start from the queries. It uses the PostgreSQL, DynamoDB, MongoDB and Redis docs plus Wikipedia for the families, and a small worked example with the arithmetic shown. It contains no benchmarks, because none were run for it.
The difference is where the design effort goes. In a relational database you normalise first and ask questions later, and AWS describes the contrast directly: in an RDBMS you design for flexibility without worrying about implementation details, while in DynamoDB you design the schema specifically to make the most common and important queries fast and cheap.
So the trade is flexibility of future questions against speed on known questions. A relational model answers questions you have not thought of yet, at the cost of joins. A NoSQL store answers the questions you planned for very efficiently, and the DynamoDB guide says plainly that queries outside those planned ways can be expensive and slow.
Wikipedia groups NoSQL systems into four main data models, and each is shaped around one kind of lookup.
| Model | You typically query by | Good fit | What you give up |
|---|---|---|---|
| Relational SQL (PostgreSQL, MySQL) | Any column, joins across tables | Transactions, reporting, questions not yet known | Schema changes need migrations |
| Key-value (Redis, DynamoDB) | The key, or a designed key and sort key | Sessions, caches, counters, known lookups at high volume | Ad hoc queries and joins |
| Document (MongoDB, Firestore) | Document id and fields inside the document | Data read and written as one self-contained unit | Cross-document consistency, duplicated data when you embed |
| Wide-column (Cassandra, Bigtable) | A partition key chosen for the query | Very large, write-heavy data spread over many nodes | Flexible querying; the table is built per query |
| Graph (Neo4j) | Traversals along relationships | Recommendations, fraud rings, dependency networks | A second engine to run for tabular workloads |
Read the last column first. Every family buys its strength by removing something a relational database gives you for free, which is why the cheapest question to ask about any of them is what you are giving up.
Write the access patterns down before you draw any schema. DynamoDB makes this a rule: do not start designing until you know the questions it must answer, and identify data size, data shape and data velocity first. MongoDB states the same principle in its own words: data that is accessed together should be stored together. Here is the exercise for an order system, using a carwash as the example.
// Worked example: a carwash order system. Five access patterns, written down FIRST.
// AP1 one order with its line items by orderId
// AP2 today's orders for one branch by branchId + date
// AP3 every order for one plate number by plateNo
// AP4 revenue per branch per day aggregate
// AP5 "which orders had a wax add-on?" ad hoc, not known in advance
// Key design in a DynamoDB-style single table (PK = partition key, SK = sort key):
// AP1 PK = ORDER#1042 SK = META | ITEM#1 | ITEM#2 | ITEM#3
// -> one Query returns 4 items (1 header + 3 line items) in one round trip
// AP2 GSI1PK = BRANCH#7#2026-10-10 GSI1SK = ORDER#1042
// -> one Query on a global secondary index
// AP3 GSI2PK = PLATE#B1234XYZ GSI2SK = ORDER#1042
// -> one Query on a second index
// AP4 no key serves it: needs a stream plus a counter item, or an export
// AP5 no key serves it at all
// Score: 3 of 5 patterns served by keys, 1 needs extra machinery, 1 is unserved.
// The same five in Postgres: 1 primary key + 2 indexes cover AP1-AP3,
// AP4 is a GROUP BY, AP5 is a WHERE clause. 5 of 5, no redesign.The arithmetic is the point. Five patterns, three served directly by keys, one needing a stream and a counter, one not served at all. In Postgres the same five need a primary key and two indexes, plus a GROUP BY and a WHERE clause. That is not a verdict against DynamoDB, it is the cost of the planned-queries bargain made visible.
The same exercise shows when NoSQL wins. If AP1 and AP2 were the only patterns and they ran at a volume one Postgres box could not serve, the key design above would be exactly right, and AP4 and AP5 would live in an analytics copy of the data.
Put a number beside each access pattern before choosing: how often it runs, how much data it touches, how fast it must return. A pattern with no numbers is a wish, and wishes make every database look equally good.
Because jsonb gives you the part of a document database most teams actually wanted, a flexible shape for some fields, without leaving a relational engine. The PostgreSQL docs say most applications should prefer jsonb over json: it is stored in a decomposed binary format, slower to input but significantly faster to process, since no reparsing is needed. It also supports containment with the @> operator and GIN indexing.
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
branch_id integer NOT NULL,
plate_no text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
total_idr integer NOT NULL, -- money and joins stay relational
attrs jsonb NOT NULL DEFAULT '{}' -- the flexible, per-service part
);
CREATE INDEX orders_branch_day ON orders (branch_id, created_at);
CREATE INDEX orders_plate ON orders (plate_no);
-- jsonb_path_ops: smaller and more specific than the default jsonb_ops,
-- but it only supports containment style operators (@>, @?, @@), not key-exists (?).
CREATE INDEX orders_attrs_gin ON orders USING GIN (attrs jsonb_path_ops);
-- Right: containment, which the GIN index can answer.
SELECT id FROM orders WHERE attrs @> '{"addons": ["wax"]}';
-- Wrong: extracting text with ->> bypasses the GIN index on the whole column.
-- You would need a separate btree index on that exact expression.
SELECT id FROM orders WHERE attrs ->> 'tier' = 'premium';The pattern above keeps money, branch and timestamps as real columns and puts only the variable part in attrs. The docs list two GIN operator classes: the default jsonb_ops indexes each key and value, while jsonb_path_ops is usually much smaller and more specific but supports only containment style operators. Choose by the operators your queries actually use.
JSONB stops being a good answer when the problem is not the shape of the data but the scale or the model of the workload. Three situations usually qualify.
Notice that none of these is simply that the schema changes a lot. Flexible schema alone is not a reason to leave Postgres.
The PostgreSQL docs warn that any update to a jsonb value takes a row-level lock on the whole row, and recommend keeping documents small and ideally each one an atomic datum. Two transactions editing different keys of the same large document still queue behind each other.
Run this in order and stop at the first step that forces a decision.
Step five is the one people skip. Polyglot persistence is a legitimate design, but it is paid for in operations, not in the schema.
My default for ERP and POS work is PostgreSQL as the system of record, with jsonb for the genuinely variable attributes, and Redis beside it as a cache for hot keys. Orders, invoices and stock movements need transactions and cross-table reporting, which is exactly what the relational column in the table gives you.
That is a default I chose for a Docker deployment on one VPS, where each extra datastore is another container to back up and watch, not a rule for every system. I would revisit it the moment a measured access pattern fails the step three check, and not before.
Do not choose between SQL and NoSQL; choose against a list of access patterns. Start with PostgreSQL and jsonb, add a specialised store only for the single pattern that fails on it, and count the operational cost of every datastore you add.