I want to store a large snapshot in a table. This snapshot contains data for today and the next days related to specific regions. This snapshot is created every day and the old data should be discarded.
The reason why I can not truncate the table to load the new snapshot is because the table is populated from time to time by using the insert implementation of PostgreSQL (INSERT IN CONFLICT).
In this sense, I have a data window that moves every day to create this snapshot. For example, for day 1, the snapshot contains day-to-day dataN. For day 2, the snapshot contains data from day 2 to day N + 1.
I wonder if there is a better way to do it than to issue a declaration of elimination + multiple insertion in conflict declarations. I thought about creating a partition in this table to store the daily data, which should be deleted, and a partition for the next days data, which should be updated. But I do not know if it's a good idea to create two partitions in such a way that one is much larger than the other.