Query optimization with table partitioned by day

[brunofrizzo]

I have a non-partitioned table that i want to migrate to native partition. This table store sensors data and i'll use the colum "date" as the partition key. Sensors stores data in, at least, each minute, and i have hundreds of them. The majority of my queries is filtering by one day, but i have some queries that need to read data about a entire month. I'm not sure if i create partitions by day or by month. My question is: If i choose partition by day, my monthly queries will have to scan each one of daily partitions? How this affect the perfomance?

[keithf4]

The bigger reason for partitioning is more around data retention and table maintenance (vacuuming, index creation, etc) than it is read performance. It's possible you may see a decrease in read performance after partitioning as well unless you're dealing with very large amounts of data. So if you are doing retention, I'd start your partitioning interval based on that and see how things work. If you're not doing retention, then starting on your expected query range would be good. If you partition by day, it's going to have to check each table that has data in your requested range when you do the larger range.

If you find yourself querying ranges of data and doing little to no updates, I'd highly recommend looking into BRIN indexing to reduce storage requirements and possibly increase read performance as well - https://www.postgresql.org/docs/15/brin.html