# One analytics API over many data sources

By [Anubhav Choubey](https://anubhavchoubey.com/) · Data & Speed · 2026-11-03 · 2 min read

> One API that combines data from several sources, caches it in PostgreSQL and keeps a 30-day window ready, since most queries asked about last month.

```stats
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

```text
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.

```callout tip
Look at the queries before you design the cache. The right window came from how the data was used, not from a guess about how it might be.
```

## 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.