Re: Postgresql 13 partitioning advice

Hello David,

Thank you for the valuable inputs.We will test these scenarios .

Regards,
Ameya

On Tue, Aug 2, 2022 at 12:16 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Tue, 2 Aug 2022 at 19:48, Ameya Bidwalkar
> <bidwalkar(dot)ameya10(at)gmail(dot)com> wrote:
> > 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 .
>
> Define "huge complications"?
>
> The capabilities of partitioned tables have changed quite a bit since
> the feature was added. It's very easy for knowledge to get out-dated
> in this area. I did quite a bit of work on them and I struggle to
> remember off the top of my head which versions saw which improvements.
> PG12 saw lots. See [1], search for "partition".
>
> One possible complication is what is mentioned in [2] about
> "serialization failure error". UPDATEs that cause a tuple to move to
> another partition can cause a serialization failure at transaction
> isolation level, not just serializable transactions. If it's not
> already, you might want to have your application retry transactions on
> SQL:40001 errors.
>
> Apart from that, assuming there's comparatively a small number of rows
> in the partition being updated compared to the partition with the
> static rows, then it sounds fairly efficient. As you describe it, the
> larger static partition is effectively INSERT only and auto-vacuum
> will need to touch it only for tuple freezing work. The smaller of
> the two tables will receive more churn but will be faster to vacuum.
> PG13 got a new feature that makes sure auto-vacuum also does the
> rounds on INSERT-only tables too, so the static partition is not going
> to be neglected until anti-wrap-around-autovacuums trigger, like they
> would have in PG12 and earlier.
>
> Another thing to consider is that an UPDATE of a non-partitioned table
> has a chance at being a HOT update. That's possible if the tuple can
> fit on the same page and does not update any of the indexed columns. A
> HOT update means no indexes need to be updated so these perform faster
> and require less space in WAL than a non-HOT update. An UPDATE that
> moves a tuple to another partition can never be a HOT update. That's
> something you might want to consider. If you're updating indexed
> columns already then it's not a factor to consider. There's also
> overhead to postgres having to find the partition for the newly
> updated version of the tuple. That's not hugely expensive, but it's
> generally measurable. RANGE partitioned tables with a large number of
> partitions will have the most overhead for this. HASH partitioned
> tables, the least.
>
> The best thing you can likely do is set up a scenario with pgbench and
> compare the performance. pgbench is a pretty flexible tool that will
> allow you to run certain queries X% of the time and even throttle the
> workload at what you expect your production server to experience. You
> could then run it overnight on a test server, or even for weeks and
> see how auto-vacuum keeps up when compared to the non-partitioned
> case. You can also check how much extra WAL is generated vs the
> non-partitioned case.
>
> > 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 ?
>
> Perhaps if you want to keep a small high-chun table in check you might
> want to consider if autovacuum_naptime is set low enough. You may not
> care if the space being consumed in the standard 1min
> autovacuum_naptime is small enough not to be of concern.
>
> David
>
> [1] https://www.postgresql.org/docs/release/12.0/
> [2] https://www.postgresql.org/docs/13/sql-update.html
>