We have a Postgresql 13 database where we have a single table with several millions of rows . We plan to partition it based on timestamp .
We have been seeking advice for best practices for building this.
This table will get lots of updates for the same rows during a short period of time.During this time rows would be in a single partition .
After this short time these rows would move to another partition .Where no more updates take place on these rows.But might have some SELECT queries running.
We plan to l have partitions based on months and then roll them up in a year and then archive these older partitions
One consultant we talked with told us this row movement between the partitions will have
huge complications .But this was an issue during the Postgres 10 version .
So we are seeking advice on the performance perspective and things we should take care of along with manual vacuums on a regular schedule and indexing.
Are there any tunables I should experiment with in particular ?