Webinar: PostgreSQL Partitioning Roadmap [Follow Up]

Partitioning is an integral data management strategy for high-performance and high-volume database applications. PostgreSQL provides excellent partitioning support that is constantly evolving. 

In order to explore this topic in detail, 2ndQuadrant arranged a live webinar, “PostgreSQL Partitioning Roadmap”. The session was hosted by Simon Riggs, Founder, and CEO at 2ndQuadrant.

The webinar looked at the past and future development of partitioning in PostgreSQL, in addition to covering the following topics:

• Partitioning – The Road so Far
• New Partitioning Performance features in PostgreSQL 12
• A look ahead at PostgreSQL 13
• Future plans: Performance, Management, Sharding
• BDR Autoscale

Those who weren’t able to attend the live webinar can now view the recording here.

Due to limited time, some of the questions were not answered during the live webinar, so answers by the host are provided below:

Question: As Greenplum moves to merge with mainstream Postgres, does the greater PG community see any benefits from features available today in Greenplum?  Will there be a syntax change in the future?

Answer:  If developers of Greenplum or other databases wish to contribute features to PostgreSQL with the appropriate licence, I’m sure many people will be happy.

Question: I would like to know if the hash partitioning could help to address the issue of distribution data among partitions?

Answer:  They would, but don’t help much with other aspects that people require from partitioning.

Question: Should the number of partitions be proportional to CPU/memory?

Answer:  No.

Question: We have a PG12 database that has a 730-partition table where each partition is 1 million rows (growth expected to 5 million in 5 years) that is partitioned by date, and where partitions with past dates are dropped and new empty partitions are added and populated. Partitions are uniformly populated. Performance has been superb since PG12. We are having difficulty scheduling a daily job to perform this maintenance since database backups and other scheduled operations are involved. Since dropping a partition locks the entire table, the backup is stalled, and this is presenting problems. We’re considering truncating the to-be-dropped partition(s) first, then dropping later. Would that help? Could you offer advice?

Answer:  Yes, truncating the partitions and then dropping later will work.

Question: Any way to alleviate the AccessExclusiveLock on the parent table for drop/detach? What to do on a busy server?

Answer:  Yes, that is a roadmap item.

Question: Does pglogical 2 now support PostgreSQL 12

Answer:  I’m hopeful that it will do in the future, but no hard plans yet.

Question: We have timestamps as integers (Unix timestamp) with very uniformly spread data, would it make sense to partition in chunks of a hundred thousand for instance rather than trying to squeeze it into months.

Answer:  Yes, range partitioning as integers could work. Not sure why you would pick one hundred thousand rows though.

Question: When partitioning time series data (like in your example), would there be any concerns or benefits with adding an ID column to be used as a primary key?  When partitions and primary keys, PG requires that your primary key be composite and include the column that you are partitioning on.  Is it better to skip primary keys altogether and just use the BRIN index?

Answer: In some cases, yes.

Question: The documentation says about partitioning limitations:
“BEFORE ROW triggers, if necessary, must be defined on individual partitions, not the partitioned table.”
If I understand slide 22, we can forget about this limitation in PG13. Correct

Answer: Yes.

To be the first to know about upcoming PostgreSQL webinars by 2ndQuadrant, visit our Webinars page.

For any questions, comments, or feedback, please visit our website or send an email to [email protected].