We’re inputting large volumes of time-series data into a hypertable in PostgreSQL with TimescaleDB and are experiencing slow pagination, especially as the pages increase. Currently, the primary index is set on the timestamp column, with both BRIN and B-tree indexes present. We’re using pagination with OFFSET ROWS FETCH NEXT ROWS ONLY. Users need to sort the data according to any column, which means every column other than the timestamp could potentially be included in the ORDER BY clause in either ascending or descending order, and sometimes no specific sorting is defined. Because users can customize filtering and sorting, optimizing the query is challenging. Additionally, pagination isn’t sequential as users might jump, for example, directly to the 20 millionth page, making cursor-based pagination difficult as well.
Please suggest a solution. The data is in the hundreds of millions, and the server has 64 GB of memory.
Welcome @qhqo177,
please, consider downsampling algorithms:
It can help in several ways to make faster scans, less memory consumption and also limit the unpredictable use of your server resources.
OFFSET is not ideal, if you can mix your queries with more where clauses that will rely on index, then offset will not be a great deal. If you can mix to allow them to resort after filtering the time period, it’s already a big win.
also consider flexible queries to detect the time frame, here is a great example of a smart query: 3 Ways to Plot Incremental PostgreSQL Materialized Views