Range Partition Time Data Postgres

user_19313896 0
Viewed 102

When it comes to CRUD operations I am curious when it is better to range partition time series data with an index on the column being used as the range versus just having a index on time.

So for a simple example if you have a table with three columns (time, device_id, value):

  • In the first case I range partition the table on time along with create an index on time.
  • In the second case, I just have one monolithic table with just an index on time.

My questions based on anyone who has done this before is what are the benefits of the range parition option when it comes to performance (primarily speed) versus the monolithic table with an index for each of the CRUD operations and on a highlevel why is that based on how the database operates.

database-partitioning query-optimization postgresql
1 Answers

You can possibly improve the speed of some queries, particularly if you aggregate or join in a way that matches partitioning. However, most queries will become slower on a partitioned table.

One potential gain would be to drop the index on the partitioning key and use a sequential scan on the whole partition instead. That will perhaps be slower, but it can make data modifications faster.

user_64643080
Related