Twelve real scenarios, worked through

The method is only useful applied. Here are twelve real scenarios, each worked with the same verdict-and-reasoning structure. Read them not for the answers but for how the answer is reached — the questions asked, the trade-offs weighed, and how often the answer is "PostgreSQL, actually". Cover the verdict and try each yourself first.

Notation: Primary is the source of truth; + marks an added specialised store.

---

1. A blog / CMS. Posts, authors, comments, tags, categories. Verdict: PostgreSQL. Relational with clear relationships (posts→authors, posts↔tags), needs consistency, modest scale, ad-hoc queries for admin. A textbook relational fit. If posts have a wildly varying block structure, store that block tree as jsonb in a column — still PostgreSQL. The instinct to reach for MongoDB "because content" is the trap from module 10.

2. An e-commerce store. Products, inventory, orders, payments, customers. Verdict: PostgreSQL, emphatically. Orders and payments need ACID — you cannot lose an order or double-charge a card. Inventory decrements must be transactional (module 6). Relationships everywhere. This is the case where "just use MongoDB" does the most damage. Add Redis for the cart and session and product-page cache, and later Elasticsearch for product search with faceting — but PostgreSQL owns the money.

3. User sessions for a web app running on many servers. Verdict: Redis. Read on every request, shared across instances, must expire (module 11). Not PostgreSQL — a database hit per request for session data is pure overhead, and in-memory won't work across instances. This is Redis's home. (A signed JWT is the store-less alternative, with the no-revocation trade-off.)

4. A real-time leaderboard for a game. Millions of players, live ranks. Verdict: Redis sorted set for the live board; PostgreSQL as the durable record of scores. "Top 100 and this player's rank, updated live" is a sorted-set one-liner and an expensive ORDER BY+COUNT in SQL (module 11). But scores are real data, so PostgreSQL keeps the truth and Redis serves the ranking — a two-tier design.

5. IoT sensor data. 100,000 devices, a reading every 10 seconds. Verdict: TimescaleDB (still PostgreSQL) — or a plain partitioned table if smaller than it sounds. ~864M readings/day is append-only, time-ranged, aggregation-heavy — time-series (module 12). Start with a partitioned PostgreSQL table; add TimescaleDB's compression and continuous aggregates when volume demands. A dedicated engine (InfluxDB) only if time-series is the product.

6. A social network's "people you may know". Friend-of-friend suggestions. Verdict: Graph (Neo4j) for the traversal, if it goes deep and the network is large. This is the one clear case for a graph database — friends-of-friends-of-friends is multiplying self-joins in SQL (module 12). But the rest of the social network (profiles, posts) is still PostgreSQL; the graph handles only the connection queries.

7. Full-text product / document search with typo tolerance and facets. Verdict: PostgreSQL full-text first; Elasticsearch when you outgrow it. Word-aware search with ranking is to_tsvector + GIN in the database you have (module 12). Move to Elasticsearch when you need fuzzy typo tolerance, faceting at scale, or fine relevance tuning — keeping PostgreSQL as the source of truth and the search index as a derived, synced copy.

8. A chat / messaging app. Billions of messages, always available. Verdict: depends on scale. At normal scale, PostgreSQL with partitioned message tables and Redis pub/sub for real-time delivery. At WhatsApp scale — billions of writes, multi-region, never-down — Cassandra, because message history is exactly its write-heavy, partition-by- conversation, availability-first case (module 12). Most chat apps are the former; be honest about which you are building.

9. RAG for an AI assistant over company documents. Verdict: pgvector (PostgreSQL). Embed the documents, store vectors, retrieve nearest for the model's context (module 12). Up to millions of vectors this is PostgreSQL with an extension — no new system. A dedicated vector database (Pinecone) only at hundreds of millions of vectors or for vector-specific scale.

10. Analytics dashboard. Aggregations over tens of millions of rows. Verdict: PostgreSQL, with indexes, materialised views (module 8) and possibly a read replica; a dedicated analytics warehouse (ClickHouse, BigQuery, Redshift) only at genuinely large scale or for heavy OLAP. Reporting is SQL's home turf; the mistake here is reaching for a warehouse before the data justifies it.

11. A feature-flag / configuration service. Small data, read on every request, rarely changes. Verdict: Redis (or even an in-process cache), backed by PostgreSQL as the source of truth. Tiny, hot, read-mostly — cache it. The database holds the authoritative flags; Redis serves the reads; a flag change invalidates the cache (module 11).

12. A rate limiter for an API gateway. Verdict: Redis. Atomic counters with TTLs, shared across gateway instances (module 11). Not PostgreSQL — the write volume and the per-request latency rule it out, and the data is ephemeral (a lost counter just resets a window). A textbook Redis-not-as-cache use.

---

What the twelve have in common

Step back and the pattern is unmistakable:

• PostgreSQL is the answer, or part of it, in almost every one. Even where a specialised store appears, PostgreSQL is usually still the source of truth.
• The specialised store is almost always additive, not a replacement. Redis in front of PostgreSQL; Elasticsearch derived from PostgreSQL; a graph for one query pattern. This is polyglot persistence, the next lesson.
• "It depends on scale" is a real answer (scenarios 5, 8, 10). The same problem chooses differently at 1,000 users and at 100 million, and pretending otherwise is how you over-build.
• The access pattern decided most of them. "Read on every request" → Redis. "Traverse to depth" → graph. "Aggregate over time" → time-series. The data shape mattered less than how it is used.

If you can reason your way to these twelve verdicts — and defend the "it depends" ones — you can make the call on a new problem. That is the skill this whole course was for.

Check your work

The default that recurs across the scenarios. PostgreSQL — the answer or part of it in almost all twelve.

Why e-commerce is emphatically PostgreSQL. Orders and payments need ACID and transactional inventory; it is where "just use MongoDB" does the most damage.

Why sessions and rate limiting are Redis. Read every request, shared across instances, ephemeral, must expire — with PostgreSQL as the source of truth where the data is durable.

Why a leaderboard splits across two stores. Redis sorted set for the live ranking; PostgreSQL for the durable scores.

When IoT/chat/analytics change their answer. With scale — a plain partitioned table or PostgreSQL at normal scale; TimescaleDB / Cassandra / a warehouse only when volume demands.

The one clear graph-database case, and its limit. Deep friend-of-friend traversal — but only that query; profiles and posts stay in PostgreSQL.

Where RAG belongs by default. pgvector in PostgreSQL, up to millions of vectors.

The shared pattern. The specialised store is additive, not a replacement; PostgreSQL usually remains the source of truth.

What decided most scenarios. The access pattern, more than the data shape.

Practice

1. Cover the verdicts and work all twelve yourself. Compare your reasoning, not just your answer.
2. For scenario 2 (e-commerce), list every place ACID is non-negotiable.
3. For scenario 5 (IoT), pick a scale where a plain table suffices and one where it does not. Justify the boundary.
4. For scenario 8 (chat), describe the app at which you would switch from PostgreSQL to Cassandra.
5. Take three scenarios and write the main access patterns that drove each verdict.
6. Invent a thirteenth scenario from an app you know and work it fully with the five questions.
7. Find a scenario here you would answer differently and argue it. There are defensible disagreements — make the case.
8. For any scenario using two stores, draw which store owns what and how they stay in sync.

Official documentation

• PostgreSQL — Feature matrix — Why it answers so many of these.
• Redis — Use cases — Sessions, rate limiting, leaderboards, caching.
• TimescaleDB — Documentation — The IoT/time-series answer that stays in PostgreSQL.
• Apache Cassandra — Documentation — For the genuine write-at-scale case.
• pgvector — The RAG answer inside PostgreSQL.

Next: the honest trade-off table.