In many applications, we need to store data in time series.
There are various mechanisms in PostgreSQL that are designed to support this, and it is an area that has changed dramatically in PostgreSQL 11.
If you have a huge table and a query to select only a subset of that table, then you may wish to use a BRIN index (block range index). These indexes give performance improvements when the data is naturally ordered as it is added to the table, such as logtime
columns or a naturally ascending OrderId
column. Adding a BRIN index is fast and very easy, and works well for the use case of time series data logging, though it works less well under intensive updates. INSERTs into BRIN indexes are specifically designed to not slow down as the table gets bigger, so they perform much better than B-tree indexes.
You may also think that you need to manually partition a table. This can involve significant effort to set up an effective partitioning scheme using multiple...