"Postgres or MongoDB?"
That is the first question in the design review of a podcast app, and as asked it has no good answer, because it asks for one database for the whole company, and databases are chosen per workload. This app has four:
- Billing. Accounts, plans, invoices, payments. Few writes, and every one matters: a renewal charges a card and extends the subscription. Finance asks new questions of it every quarter.
- Playback position. Where each listener stopped in each episode. The player saves it every 10 seconds while audio plays, so 200,000 people listening at once means about 20,000 writes a second, and every read asks for one value: this user, this episode.
- Show pages. A show with its hosts and episodes, each episode with its own chapters, transcript or video. Read as a whole, edited now and then, and no two shows carry quite the same fields.
- Listen events. One record per play, appended and never changed, read back as "plays of this episode per day" for the publishers' dashboards.
Each has an access pattern: what gets written, how often, and which questions are asked of it afterwards. The store follows from the pattern, and these four do not all point at the same one.
Where the app ends up: each service owns its data, and the access pattern on the edge chose the store.
What billing needs from a schema
A relational database (PostgreSQL, MySQL) stores rows in tables with a declared schema: every invoice has the same columns, of the same types. Billing uses everything that structure buys:
- Constraints. A payment's
invoice_idmust point to an invoice that exists, an email must be unique, an amount cannot be negative. The database rejects the write that breaks a declared rule, whichever service sent it. - Multi-row transactions. The renewal inserts a payment, marks the invoice paid and moves the subscription's end date forward, and either all three commit or none does; the card charge itself happens at the payment provider, outside that promise. What that promise covers under concurrency is in Transactions & isolation levels.
- Joins and ad-hoc queries. "Annual plans in Portugal that renewed last month, with their revenue" is one SQL statement over tables nobody designed for that question. With the right indexes (Indexing & query optimisation) it is fast as well.
The bill arrives when one machine is no longer enough. A relational database can be split across machines, but joins, constraints and transactions that cross the split become expensive, limited, or a feature only a distributed SQL engine offers; Sharding & partitioning covers the split. A podcast app's billing is unlikely to outgrow one primary with read replicas, so it stays relational.
"NoSQL" names what a store is not
NoSQL names stores by what they are not. The families share little beyond giving up part of the relational toolkit, each a different part, in exchange for spreading data across many machines or letting records take different shapes. The lines have blurred, too: PostgreSQL stores and indexes JSON in jsonb columns, MongoDB has supported multi-document transactions since version 4.0, and Cassandra is queried with CQL, a language that looks like SQL. Two families this app does not need complete the list below: graph stores, built for following relationships several hops (Graph databases), and time-series stores, built for timestamped measurements read by time range.
Playback position is the easy case. A key-value store keeps a value under a key and is built around get, put and delete by that key. The key here is user:4821:episode:977, the value is 1312 seconds, and the next save simply replaces it. Because every request names its key, the store can spread keys across many machines and send each request to the one that owns it, which is how it can take 20,000 writes a second and keep growing with the audience.
A relational table could hold this too, and many teams start there. What the key-value store gives up is everything that is not the key: "Which listeners stopped in the last ten minutes of episode 977?" has nothing to run against unless you built a key or an index for that question in advance. DynamoDB, for instance, reads by key (GetItem, or Query within one partition key) or through a secondary index you defined; anything else is a Scan of the whole table.
Show pages want the opposite of one table per thing. A document store (MongoDB, for example) keeps each record as a JSON-like document, and documents in one collection need not share fields: one show's episodes carry chapters, another's a video URL, and adding the second kind needs no schema change. The page is read whole, so the question is what goes inside each document:
- Embed what belongs to this record and is read with it. An episode's chapters exist only for that episode and appear on its page, so they live inside the episode's document.
- Reference what is shared or grows without bound. A host appears on several shows, so hosts are documents of their own. A show with ten years of weekly episodes and their transcripts would push one document toward MongoDB's 16 MB limit and make every page load carry the whole archive, so episodes are separate documents that point to their show, and the page fetches the latest few.
Embedding often means storing a fact more than once: the host's name copied into every episode so that a page needs one read. That copy is deliberate and has to be kept in step; Normalisation & denormalisation covers when it pays. A write to one document is atomic in MongoDB and most document stores, while a change that must land in several documents all-or-nothing needs multi-document transactions where the store has them, and those cost more.
When the key decides the questions
For listen events, the shape of the question becomes the shape of the data. A wide-column store such as Cassandra or ScyllaDB groups rows into partitions by a partition key and keeps the rows inside each partition sorted by clustering columns. Write plays with the partition key (episode_id, day) and a unique, time-ordered play id as the clustering column (without it, each new play would overwrite the last), and "plays of episode 977 on 3 May" is one partition, read from the few nodes that hold it, while millions of other plays land elsewhere. A hit episode still sends all its plays to one partition on the same few nodes, so a key that busy gets a small bucket number added, (episode_id, day, bucket), which spreads its writes over several partitions; a read then queries each bucket. The dashboard's daily totals are kept in a small rollup, not counted from those partitions on every view.
Now a listener asks to see every episode they played this month. That table has no efficient way to answer it: the user is not in its key, and Cassandra refuses to filter on a column outside the key unless you add an index or explicitly allow a scan with ALLOW FILTERING. The usual answer is a second table keyed by user, with every play written to both.
Each table answers the question its partition key names. A third question means a third table or an index.
That is the wide-column trade in one picture: writes that spread across nodes as the data grows, reads that are fast for the questions you designed for, and no efficient answer for the ones you did not. Slicing plays by country, device and week is another workload again, analytics (Analytics data: OLTP vs OLAP & the warehouse), and how many replicas must agree on each write is a separate dial (Consistency models: strong vs eventual (CAP)).
None of this is advice to run four databases: each one is another system to back up, monitor and get paged about, so a small team may keep positions and show pages in PostgreSQL until one of them actually hurts, and any fact held in two stores needs one of them named as its source of truth.
Where the choice goes wrong
- Choosing the store before writing down the access pattern, or for scale you don't have yet. The model then fights every new feature, and the joins and constraints you gave up get rebuilt in application code, by every service on its own.
- Choosing relational and refusing every copy, so the busiest page joins five tables on every read. An index or one deliberate copy is often the fix; Normalisation & denormalisation weighs the copy.
Write the pattern before the store
Open the URL Shortener challenge and, before placing any database, write the access patterns on the Grid. Creating a short link is a rare write whose code must be unique; a redirect is a read by exact code, about a hundred of them per write; click analytics is a stream of appends read back as totals. That is more than one workload; justify the store on each path.