Should I create a new table with redundant data for optimization reasons?

Question

I have a table with multiple columns with numeric data, and it is inserted every second by a few microcontrollers.

I need to query that data in the following manner:

• The averages of all columns every 1, 5 and 15 minutes (three different queries).

• Also, those averages are requested relatively often (hundreds of requests per day by a website).

I'd like to know what are some options I may try for optimizing those queries and not having to create other three tables with redundant data (the averages are already calculated, so I would just need to query them without performing a lot of math).

It's worth mentioning that those queries can't take longer than a few miliseconds.

Answer 1

Not really enough information to give a good recommendation.

A few hundred queries a day is not much at all for Postgres, generally speaking.

As for queries, keep in mind that Postgres caches data in memory. As mentioned, do some study to learn about this caching and how to tune settings for Postgres. By default the settings are quite conservative, to favor erring in the side of safety and not overburdening the host server machine.

Adding a row every second means 86,400 per day, and 31.5 million per year. That is a serious number of rows, but many people handle such data with Postgres.

The key to fast queries here is in the indexing (assuming the RAM buffers issue is tuned). You've not described enough detail to help. If you are simply indexing a TIMESTAMP WITH TIME ZONE column, you may have no performance issues at all. Remember that with suitable hardware, tuned Postgres settings, and appropriate indexing, both your data and the relevant portions of your index will all be cached in RAM and very fast.

If you do decide to cache data in a redundant table, know that you can turn off the write-ahead log (WAL) on that table for faster performance as you do not care about its integrity.

Try some tests, running on hardware similar to your production system. Load up fake data to simulate real loads. Verify behavior with the EXPLAIN feature to make sure your queries act as you expect.

Another approach for performance is to have a background process perform the query and generate results every so many seconds (whatever your users’ tolerance for staleness of results). When user request arrives, your app uses the already determined result rather than performing a fresh query. This increases the burden on your database. And most of those queries are for nought given you have so few user requests per day. But the benefit is fastest possible response to user request. For Java, see ScheduledExecutorService.

Another option is to cache the data in your app rather than in the database if it easily fits in RAM. Java for example offers many kinds of collections and queues for such caching. This especially makes sense if the app gathering the data is also the app responding to the hundreds of requests per day. Use the database as history, but keep the recent data in memory. Keep three caches, one for each of your recent 1 minute, 5 minutes, and 15 minutes, so no queries needed at all, data ready to go at all times.

Answer 2

In addition to Basil's nice explanation, I'd just like to say the technical definition of what you're asking about is called "materialized view".

If you want to introduce redundancy without sacrificing consistency and performance, create a materialized view.