Build an API for a graph app with + 30 millions data points

Question

I am quite new to design software and especially graphs. So I am working on a full-stack app with a back-end built on FastAPI (python) et front-end on React.

I need to create 4 graphs on a single page on my React app. Each graph is a time-series graph (simple) but with 3 millions data points (every 3 seconds since a couple of years) with 2 others data points (weather and humidity). They are currently on 4 PSQL tables but probably migration to timescaleDB on the long-run.

Note that the user can only watch graph (zoom) but this is read-only.

I never worked with graph before and such large amount of data, and I am wondering:

How should I built my endpoints? Should I send 3 millions data-points on each fetch call?

I was thinking about something but I have no idea if it's a good practice or it would make things worst:

Each graph must be displayed completely by default (full year, so 30 millions data points because I have data every 3 seconds). So when there is no zoom on a specific period, I could just display less data point (use intervals) and have an API that like only fetch 1 point per day so instead of fetching 30 millions pointsby default, I would have 365 points. But in this case, if the user zoom, I guess things will get a bit more complex.

Answer 1

I think you might be making this a more difficult than it is. You have M data points to show on a chart. You can only actually display N data points at a time. You simply need to divide your point into N intervals of size M/N and calculate single value for each interval.

Let's assume you want to display 365 data points in the default 1-year view. You need to find a way to translate 24x60x(60/3) points into one point. The most obvious way to do that is to calculate the arithmetic mean of the values for each day. There are other options such as the median value. There are really no limits on how you can solve for this. You simply need a function that takes a set of values and returns a single one.

When you zoom, the only thing that changes is the range of the data and perhaps how many intervals you display. A lot of that depends on your requirements for the chart's appearance.

As far as an API goes, you essentially need to know what the range is and how many intervals you need to slice it into. There are a few ways you can take that as parameters to your API:

• An interval size, start point (or end point), and number of intervals
• The start and end of the range and the number of intervals
• The start and end of the range and the size of the intervals

I would avoid anything where you make the caller provide parameters that can be derived from other parameters. For example, don't ask for a range, interval size, and a number of intervals. It's pointless to make the caller calculate all of these and what are you going to do if the parameters are inconsistent?

I would probably start with something like this:

@app.get("/humidity/{start}/{end}/{intervals}")
async def get_humidity(start: datetime, end: datetime, intervals: int):

You might find that a different way to pass things in makes more sense depending on your charting library.

One thing you might want to consider is returning a range or some non-scalar value for each aggregated data point. For example, instead of just returning the mean value, you could return a min and max tuple for interval and plot them as two lines on the graph.

Answer 2

The usual approach for allowing different zoom levels for 2D graphics is to use a tree-like data structure (for example a quadtree), or for a one-dimensional time-series just a binary tree, where you store simplified versions of sub-intervals of the graph at the different nodes.

The tree level should correspond to the degree of simplification, so at the root level, you may reduce the whole graph with 30 million points to, lets say, not more than 1000 points (roughly, you can try other numbers, if you like). For the leaf nodes, you will need around 30000 of them each one containing a subintervals of ~1000 points. Here, the data is stored unsimplified. If you use a binary tree, the tree needs 16 levels, each one with an increasing level of detail of the graph. The simplification can be done, for example, the way you already suggested by yourself, by picking every 2nd point, every 4th, every 8th, and so on. Another approach is to use some curve fitting algorithm, which will be more effort, but presumably create nicer looking results.

Now, when zooming in or shifting the view, you will typically pick one tree level of which you display the data. Use the level where the screen window covers only a few, maybe 2 nodes / intervals of the whole time series. That is the data you need to fetch, which means you will never fetch more than 1000 points in one call.

The whole tree should be prepared and stored on the server. That works best when you graph does not change any more, otherwise you will have to update your precomputed tree from time to time. The api has to be designed accordingly, where the client tells the server the time interval it wants to display as well as the horizontal screen resolution, then the server can pick the most appropriate tree level and return the data which fits best.