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 ?