A kirana shop keeps four places. Each answers one question.
Ledger: the truth. Shelf: a cache. Godown: object storage. Price list: a search index rebuilt from the ledger.
Which store first?
Bad: “NoSQL, because it scales”
Scale is a number. You trade away transactions for writes you don’t have.
Good: Default, then earn it
PostgreSQL first. Add a store only for a pattern it serves badly.
Five questions, in order. The brand name comes out last.
Entities, access patterns, numbers, guarantees, then keys and stores. No key fits a pattern? Back to step 2.
40k/s
rider pings, each overwriting the last
200,000 riders ping every 5 s. Orders peak near 350/s, easy for one primary. Pings are a different job, for Redis GEO.
Menu search needs typo tolerance. Write each dish edit to PostgreSQL and the index?
The trick
Never dual-write: one failure and they drift forever. Stream row changes by CDC; the index trails about a second.
“Latest 50 messages, newest first.” Now show me the key.
Start with what you filter on, continue with what you sort by. Same idea in SQL and NoSQL.
Label each operation, not the whole system
- Debit wallet + create order: one transaction
- Claim a username: unique constraint
- “My orders” right after ordering: read your writes
- Like counts: eventual, batched from Redis
Say it like this
PostgreSQL is the system of record. Each other store earns its place with a pattern and a number; name its key, its consistency and how it stays in sync.