I am using postgresql 14. I have a table which is ranged partitioned by days, the table's retention is rather small - i.e have 14 days worth of data (and dropping partitions older than 14 days). I would like to introduce a new index, and was thinking if it is possible to create the index only for new partitions and not for old partitions,so I can avoid reindexing existing data currently on the "older partitions" table as these will anyways be deleted. My question - is this worth doing? if so, do I have to create the index on table level after all partitions available in the table have the new index? If not, would the best way to go is to create the index concurrently?
This is currently a thought, I do not have much experience with such operations on partitioned tables
Yes, you can just create the index for new partitions. When you create the new partition, just create an index for it, either manually or scriptedly. Once all extant partitions have the index, then you can create the index on the parent table very quickly, as it just sets some metadata and doesn't build any indexes. At that point, new partitions would automatically have the index created on them.
Whether it is worth doing, I don't think anyone can answer that for you. If each partition would take most of a day to index, maybe that would be worth avoiding. But in that case, can you even tolerate having the index in the first place?