A new product appears to need five stateful systems - a relational database, a search index, a cache, a message broker and a time-series store. The team is five engineers sharing one on-call rotation. Your constraint is that they can operate two. Design the data layer and say which number sets that limit.
Show the full answer Hide the answer
The number that sets the limit
Not storage cost and not performance. It is the standing operating attention each stateful system demands once it holds production data: version upgrades, backup verification, a restore rehearsal somebody has actually run, capacity headroom, a failure runbook, and a retention policy. Budget 0.3 to 0.5 of an engineer continuously per system, and note that a sustainable rotation wants about six people - this team is already at one-in-five.
Five systems is 1.5 to 2.5 engineers of standing attention out of five, before a line of product code. That is the constraint, and it is arithmetic rather than preference.
The design
One primary store does four of the five jobs; the second slot stays unspent.
- Transactional data: a relational database, with separate connection pools per workload and a statement timeout on each, so an analytical query cannot starve the order path. This is the isolation the five-system design was buying, achieved inside one system.
- Work queue: a table plus
SELECT ... FOR UPDATE SKIP LOCKED, available in PostgreSQL since 9.5 (2016). A claim is a row update, retries are a column, and the dead-letter queue is aWHEREclause. - Search: the database's own full-text index, or a trigram index for fuzzy matching, until measured insufficient.
- Time series: a partitioned table with a retention policy that drops partitions. Dropping a partition is a catalogue operation, not a delete of millions of rows.
- Cache: the application's in-process cache plus the database's buffer cache. A separate cache service is the easiest of the five to justify and the easiest to defer, because its absence shows up as latency you can measure rather than as a capability you lack.
The evidence threshold, stated in advance
The second slot is bought when a named measurement crosses a named line, decided now rather than argued later:
| Spend the slot on | When this measurement crosses |
|---|---|
| A search engine | relevance, not latency - users cannot find things the corpus contains, measured on a labelled query set |
| A cache | read p95 above target at the measured working-set size, after indexes have been fixed |
| A log or broker | more than two independent consumer groups need the same stream with replay |
| A time-series store | partition count or ingest rate makes the retention job the longest nightly task |
Writing the threshold down before the pressure arrives is the whole technique, because the request always arrives as "we need Elasticsearch" rather than as a measurement.
What it costs
The single store becomes the blast radius for all five workloads. A runaway analytical query, a long vacuum, or a connection exhaustion event now touches search, the queue and the order path together. The connection pools and statement timeouts make that survivable rather than impossible, and you must rehearse it: run the restore drill with all five workloads present, because restoring a database that is also the queue means replaying work, not just rows.
When this is the wrong answer
When the product's core value is one of the five jobs. If relevance is the product - a marketplace, a documentation search - the search engine is not the second slot, it is the first, and something else collapses into the database. The constraint is a budget of two, not a prescription about which two.
And above roughly 15 engineers with two rotations the budget changes, so this is a decision to revisit on headcount rather than on volume. The failure mode is treating the budget as a principle after the organisation that produced it has doubled.