NoSQL and choosing a database cheat sheet

Document store or relational database?

A document store fits when data is naturally a self-contained tree read and written as a whole (a product with its variants, a CMS page), the shape varies a lot between records, and you rarely join across documents. A relational database fits when data is shared and referenced from many places, you need joins, constraints and multi-row transactions. PostgreSQL's JSONB covers many 'flexible fields' needs without a second database.

JSONB in PostgreSQL

Use a jsonb column: it stores parsed, binary JSON, supports containment (@>), key existence and path queries, and can be indexed with GIN. Keep fields you filter, join or constrain on as real columns, and the variable rest in JSONB. Limits: no foreign keys or per-key types inside the document, planner statistics are weaker, and updating one key rewrites the whole value.

CREATE TABLE items (id int PRIMARY KEY, attrs jsonb NOT NULL);
INSERT INTO items
SELECT g, jsonb_build_object('color', (ARRAY['red','blue'])[g % 2 + 1])
FROM generate_series(1, 1000) g;
CREATE INDEX items_attrs ON items USING gin (attrs jsonb_path_ops);
SELECT count(*) FROM items WHERE attrs @> '{"color": "red"}';

Partition keys in DynamoDB-style stores

Items are spread across partitions by hashing the partition key, and each partition has throughput limits, so the key must spread traffic evenly and match how you read. A user id often works; a date or a status value doesn't, because today's writes all land on one partition. Design from access patterns: list every query first, then shape partition and sort keys (and secondary indexes) so each query is a single key lookup or range read.

Wide-column stores like Cassandra

Cassandra is a leaderless, wide-column store built for high write throughput across data centers with tunable consistency. You model one table per query: the partition key decides which nodes hold the data, clustering columns order rows within a partition, and you denormalize freely because there are no joins. It's strong for time series, event logs and messaging inboxes; weak for ad hoc queries and multi-row transactions.

Search with Elasticsearch or OpenSearch

LIKE '%term%' can't use a normal index, doesn't rank results, and knows nothing about language. A search engine builds an inverted index (term → documents), analyses text (tokenising, lowercasing, stemming), ranks by relevance (BM25), and supports facets, typo tolerance and autocomplete. It's a secondary index, not the source of truth: feed it from the database through events or change data capture, and expect a short delay.

Storing time-series data

Time-series data is append-mostly and queried by time range and series. Partition by time (daily or hourly tables, or a time-series database like TimescaleDB or InfluxDB) so recent writes are local and old data can be dropped cheaply. Store raw points briefly and roll them up into minute and hour aggregates kept longer. BRIN or time-first indexes stay small on time-ordered data.

When a graph database helps

When the questions are about paths and relationships of unknown depth: friends of friends, shortest routes, fraud rings, dependency trees. A graph database stores relationships as direct links, so traversing many hops costs per hop rather than per join over whole tables. For fixed-depth or occasional traversals, PostgreSQL's recursive CTEs handle trees and small graphs fine.

Embedding vs referencing in MongoDB

Embed data that belongs to the parent, is read with it, and stays bounded: an order's line items, a user's addresses. Reference data that is shared, changes independently, or grows without limit: the product catalogue, or a post's comments once they can number in the thousands. Documents have a 16 MB limit, and updating one document is atomic, which is a strong reason to keep things that change together in one document.

Choosing a database for a new service

Start from the data and access patterns: what's read and written, how often, with what consistency, and which invariants must hold across records. Default to a well-understood relational database (PostgreSQL) because it covers transactions, constraints, joins, JSONB and full-text search. Add a specialised store only for a measured need it serves better: Redis for caching and counters, a search engine for ranked text search, a wide-column or time-series store for very high write volumes.

Object storage for files

Object storage keeps any number of files of nearly any size, durably and cheaply, behind an HTTP API, and doesn't tie files to one server's disk. Store the file there and its metadata (owner, key, size, checksum) in the database. Serve files through presigned URLs or a CDN, not through your app servers. Databases are the wrong place for large blobs: backups, replication and memory all suffer.