SQL vs NoSQL: choosing a database by access pattern
A beginner tutorial on picking a database by writing down how the app reads and writes data first. Relational, document, key-value, wide-column and search, each with an example from shorty, and why I start with Postgres.
Most "SQL vs NoSQL" arguments start from the database. I think that's backwards. Start from the questions your app asks the data, write them down, and the choice gets a lot smaller. This is a beginner level post. You need Docker and a terminal, nothing else, and I ran everything here on Postgres 18.7, Redis 8.10 and MongoDB 9.0 in throwaway containers.
In this blog we will look at the five families of databases you hear about, each with one example from shorty, the URL shortener from designing a URL shortener from requirements to scale. Then why I still start with Postgres, and the cases where I wouldn't.
Write the access patterns first
An access pattern is one sentence: who reads or writes what, how often, and by which key. For shorty I wrote these down before touching any schema.
- Redirect: get the long URL for a code. Very hot, maybe 100 reads for every write.
- List my links, newest first, 20 at a time.
- Count clicks per link per country, for a report someone will ask for next month.
- Store some per link settings (UTM tags, an expiry date) that change shape over time.
- Search my links by words in the title. Nice to have.
That list does most of the work. Pattern 1 is a lookup by one key. Pattern 2 is a range scan on an owner. Pattern 3 is a join plus a group by that nobody planned for. Pattern 4 is a small document. Pattern 5 is text search. Different shapes. And each database family is good at some of them, bad at others.
Relational: Postgres
Tables, rows, joins, transactions and a query planner that answers questions you didn't plan for. This is the one I reach for first. Here is the shorty table with a jsonb column bolted on for pattern 4.
CREATE TABLE links (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code TEXT NOT NULL UNIQUE,
url TEXT NOT NULL,
owner_id BIGINT NOT NULL,
settings JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX links_owner ON links (owner_id, created_at DESC);
CREATE INDEX links_settings ON links USING gin (settings jsonb_path_ops);
CREATE TABLE clicks (
link_id BIGINT NOT NULL REFERENCES links (id),
at TIMESTAMPTZ NOT NULL DEFAULT now(),
country TEXT NOT NULL
);
CREATE INDEX clicks_link_at ON clicks (link_id, at);
Start a container and load full script from this post (it also inserts some rows).
docker run -d --name db1-pg -e POSTGRES_PASSWORD=dev postgres:18-alpine
docker exec -i db1-pg psql -U postgres < postgres.sql
Now all four patterns, in one language.
-- 1. redirect
SELECT url FROM links WHERE code = 'go-docs';
-- 2. my links, newest first
SELECT code, url FROM links WHERE owner_id = 1 ORDER BY created_at DESC LIMIT 20;
-- 3. the report nobody planned for
SELECT l.code, c.country, count(*)
FROM clicks c JOIN links l ON l.id = c.link_id
GROUP BY 1, 2 ORDER BY 1, 3 DESC;
-- 4. query inside the document
SELECT code FROM links WHERE settings @> '{"utm": {"source": "blog"}}';
Pattern 3 is the one that matters. In a relational database it is one query. In most NoSQL stores it's a new table, a batch job or an export to somewhere else, because you have to know your queries up front.
A small thing that caught me while writing the seed data. I picked a country with an array index like (ARRAY['IN','US','DE'])[n] and got n wrong by one. Postgres doesn't throw for an index out of range, it returns NULL, and the insert then failed on the NOT NULL on country. The constraint caught my bug. That's the kind of thing a schema gives you for free and a schemaless store will happily accept.
If you want to see how Postgres actually runs these, the EXPLAIN and query plans post covers that.
Key-value: Redis
Pattern 1 is a pure "get by key". A key-value store does that in well under a millisecond and nothing else, that's the deal. Shorty uses Redis as a cache in front of Postgres with the key prefix shorty:link:.
docker run -d --name db1-redis redis:8-alpine
docker exec -i db1-redis redis-cli <<'EOF'
SET shorty:link:go-docs https://go.dev/doc/ EX 3600
GET shorty:link:go-docs
TTL shorty:link:go-docs
INCR shorty:clicks:go-docs
EOF
Asking Redis "which links does owner 1 have" is a different story. You would have to keep a second structure for it by hand. So Redis sits next to the main database here, it doesn't replace it. DynamoDB is in the same family but scales and persists like a real primary store; the trick there is designing keys around every pattern up front.
Document: MongoDB
A document store keeps one record as one nested JSON like document. If the app always reads a link with all its settings, that's a single read with no join.
docker run -d --name db1-mongo mongo:9
docker cp mongo.js db1-mongo:/tmp/mongo.js
docker exec db1-mongo mongosh --quiet shorty /tmp/mongo.js
db.links.insertOne({
code: "go-docs",
url: "https://go.dev/doc/",
owner_id: 1,
settings: { utm: { source: "blog" }, expires: ISODate("2027-01-01") },
created_at: new Date()
});
db.links.createIndex({ code: 1 }, { unique: true });
db.links.createIndex({ owner_id: 1, created_at: -1 });
db.links.findOne({ code: "go-docs" }, { _id: 0, url: 1 });
db.links.find({ "settings.utm.source": "blog" }, { _id: 0, code: 1 });
Patterns 1, 2 and 4 are easy here. Pattern 3 is possible with an aggregation pipeline and $lookup, but it fights the model. Honestly, compare the Mongo query for pattern 4 with the Postgres jsonb one above. They are about the same. That's a big part of why I don't pick Mongo just for "flexible fields" any more. I set up MongoDB on EC2 back in 2019, and today I'd ask a lot more questions before doing it again.
Wide-column: Cassandra, ScyllaDB, Bigtable
These spread rows over many machines by a partition key and keep rows inside a partition sorted. You design one table per query. For shorty that would be a clicks_by_link_and_day table, partitioned by link code and day, sorted by time. Writes are cheap and they scale out almost linearly, which is why people use them for event and time series data at very high write rates.
The cost is that pattern 3 (any new question) means a new table and a backfill. I didn't run Cassandra for this post. A three node cluster is not light on a laptop and for shorty's numbers it's the wrong tool by a mile.
Search engines: OpenSearch, Elasticsearch
Pattern 5. A search engine builds an inverted index over text, handles stemming, typos and ranking, and answers "links whose title mentions golang and postgres" quickly. It is almost never the source of truth. You copy data into it from the main database and accept that it lags a little.
For a small app, Postgres full-text search with a tsvector column and a GIN index goes surprisingly far. I'd try that first and move to a search engine only when ranking or scale needs it.
Start with Postgres, and when not to
So my default is Postgres, with Redis next to it when a hot key lookup needs to be faster. One database covers relational data, documents in jsonb, simple queues with FOR UPDATE SKIP LOCKED (see Postgres job queues in golang) and basic search. It's one thing to back up, monitor and upgrade. For a small team that matters more than anything on a benchmark chart. This blog itself runs on MySQL 8, another relational database, and a nightly dump is all the backup it needs.
I'd pick something else in these cases:
- Write volume far beyond one primary, with a simple key, like click events at hundreds of thousands per second. Wide-column or DynamoDB.
- Pure key lookups that must be sub millisecond. Redis, as a cache or a store you can rebuild.
- Real search with ranking, facets and typo tolerance across millions of documents.
- Records that are always read and written whole, have no relations and really do vary in shape. A document store is pleasant there.
- A serverless app on AWS that wants no servers to manage. DynamoDB fits, if you know your access patterns well.
Each of those is an access pattern. None of them is a feeling about SQL.
Cleaning up
docker rm -f db1-pg db1-redis db1-mongo
Next time someone says "we should use NoSQL", ask them for the list of access patterns first. Usually the conversation gets shorter after that.