I have a use case in which the most recent data experiences a lot of transactions (inserts and updates) and then the churn kind of calms down. Eventually the data is relatively static and will only be updated in special and sporatic events.
I was thinking about keeping the high churn data in a different table so that the vacuums on that table can go faster and the vacuums on the rest of the data will rarely be needed.
Ideally the high churn data would be in an in memory table but failing that I thought that an unlogged table would be good enough. So now I need a way to flush the oldest data in this table to another logged table and do it in an efficient manner. I don't think partitioning is a good idea in this case because the partitions will be for small time periods (5 to 15 minutes).
Anybody have a similar problem? If so how did you solve it?
Thanks.