- Many sources
- 1 API
- PostgreSQL cache
- 30-day hot window
The problem
The data lived in several places, and each one told only part of the story. Getting the full picture meant pulling from every source, lining the pieces up and combining them, every time someone asked a question. That's slow, it puts load on systems that have their own job to do, and it repeats the same work over and over.
The goal was one API that could answer with the complete analysis of all the sources combined, and answer quickly.
One API in front of everything
source A ─┐
source B ─┼──▶ analytics API ──▶ combine + analyse ──▶ answer
source C ─┘ │
└──▶ PostgreSQL cache (last 30 days)The API was the only thing that talked to the sources. Clients asked it one question and got one answer, without knowing how many systems were behind it or how their data was shaped. Combining the data happened in one place, so every client saw the same numbers.
Caching in PostgreSQL
Pulling and combining from every source on each request was the expensive part, so the combined data was saved in PostgreSQL and served from there. Repeat questions read from the cache instead of going back to every source.
The 30-day window
Before deciding what to cache, I looked at what people actually asked for. Most queries were about the last month. Older data was needed sometimes, but rarely.
So the cache kept a rolling 30-day window ready for quick analysis. The questions most people asked were answered from a small, fast set of data, and only the rare question about older data had to go further.
Takeaway
When the answer needs data from many places, give it one front door. Combine once, cache the result, and size the cache to how people really use it: here, one month covered most of the questions.