With the recent release of PostgreSQL 12, there’s been a lot of interest in the latest version and by popular demand, the team at 2ndQuadrant organized a live webinar on “PostgreSQL Partitioning”.
The webinar was hosted by Simon Riggs, CTO 2ndQuadrant, who gave an overview of PostgreSQL partitioning features and how they play a key role in performance improvement of very large databases.
Particular use-cases were also highlighted, along with recent partitioning improvements in open source PostgreSQL and what needs to be done to make it even better in the future.
Those who weren’t able to attend the live event can now view the complete recording.
To stay updated on upcoming webinars by 2ndQuadrant, you can visit our Webinars page.
Questions that Simon couldn’t respond to during the live webinar, due to time restrictions have been answered below.
Question: Are there any utilities/commands (either in PG12 or planned) that will allow “converting” very large table into patritioned one?
Answer: Using INSERT SELECT or COPY. Agree its desirable.
Question: Is it possible to partition by range list like (1) 1-100, (2) 101, (3) 100-… ?
Answer: Partition 3 overlaps with 1 and 2, so No. Use inheritance if you want that.
Question: Can partition feature be considered as a reliable solution for scaling a SaaS application w.r.t tenant id?
Answer: It works, but that is your decision.
Question: Why an “updated partition constraint for default partition … would be violated by some row” error appears when trying to attach a new partition with values available from a default partition ?
Answer: Because it doesn’t move the rows, it just issues an ERROR. You need to move them yourself. The manual doesn’t fully explain that it will throw an ERROR.
Question: Is it possible to partition on time ranges?
Answer: Yes, but TIMESTAMP is more sensible than TIME.
Question: When will PostgreSQL be able to run CREATE gist INDEX on partitions in parallel?
Answer: It will create an index on each partition in turn, each index can use parallel index build.
Question: Over 100k partitions and with all queries are WHERE id_customers = x context is an reasonable’s architecture ? The others customers’s partition impact performance on queries for one customer ? Thanks in advance
Answer: Try it and see.
Question: For the large fact table , BRIN index will help or bitmap index will help the performance.
Answer: No such thing as Bitmap, perhaps a Greenplum guy, Ignore.
For any questions, comments, or feedback, please visit our website or send an email to [email protected].