2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / 2ndQuadrant3 / PostgreSQL 11: Partitioning Evolution from Postgres 9.6 to 11
David Rowley

PostgreSQL 11: Partitioning Evolution from Postgres 9.6 to 11

October 24, 2018/0 Comments/in 2ndQuadrant, David's PlanetPostgreSQL /by David Rowley

During the PostgreSQL 11 development cycle an impressive amount of work was done to improve table partitioning.  Table partitioning is a feature that has existed in PostgreSQL for quite a long time, but it really wasn’t until version 10 that it started to become a highly useful feature. We’d previously claimed that table inheritance was our implementation of partitioning, which was true.  It just left you to do much of the work yourself manually. For example, during INSERTs, if you wanted the tuples to make it to your partitions then you had to set up triggers to do that for you. Inheritance partitioning was also slow and hard to develop additional features on top of.

In PostgreSQL 10 we saw the birth of “Declarative Partitioning”, a feature which is designed to solve many of the problems which were unsolvable with the old inheritance method of partitioning. This has resulted in a much more powerful tool to allow you to horizontally divide up your data!

Feature Comparison

PostgreSQL 11 comes complete with a very impressive set of new features to both help improve performance and also to help make partitioned tables more transparent to applications.

Feature PG9.6 PG10 PG11
Declarative Partitioning
Auto Tuple Routing – INSERT
Auto Tuple Routing – UPDATE
Optimizer Partition Elimination
 1
1
Executor Partition Elimination
2
Foreign keys
3
Unique indexes
4
Default Partitions
Hash Partitions
FOR EACH ROW triggers
Partition-level joins
 5
Partition-level aggregation
Foreign partitions
Parallel Partition Scans
  1. Using constraint exclusion
  2. Append nodes only
  3. On partitioned table referencing non-partitioned table only
  4. Indexes must contain all partition key columns
  5. Partition constraint on both sides must match exactly

 

Performance

We’ve also got good news here!  A new method of performing partition elimination has been added. This new algorithm is able to determine matching partitions by looking at the query’s WHERE clause. The previous algorithm checked each partition, in turn, to see if it could match the WHERE clause of the query.  This resulted in an additional increase in planning time as the number of partitions grew.

In 9.6, with inheritance partitioning, routing tuples to a partition was generally done by writing a trigger function which contained a series of IF statements to conditionally INSERT the tuple into the correct partition.  These functions could be slow to execute. With declarative partition as added in v10, this gets significantly faster.

Using a partitioned table with 100 partitions we can see the performance of loading 10 million rows into a table of 1 BIGINT and 5 INT columns.

Partitioning evolution and PostgreSQL 11

Querying this table to perform a lookup of a single indexed record, and performing DML to manipulate a single record (using 1 CPU only):

Partitioning evolution and PostgreSQL 11

Here we can see that the performance of each operation has improved quite a bit since PG9.6. SELECT queries are looking much better, especially ones which are able to eliminate many of the partitions during query planning. This means the planner can skip much of the work it had to do previously. For example, We no longer build Paths for unneeded partitions.

Summary

Table partitioning is starting to become a very powerful feature in PostgreSQL. Partitioning allows data to be quickly brought online and taken offline without the need to wait for slow bulk DML operations to complete.  It also means related data can be stored together, which means the required data can be accessed much more efficiently.  The improvements made in this version wouldn’t have been possible without the developers, the reviewers and the committers who tirelessly worked on all of these features.

Thank you to all of them! PostgreSQL 11 looks like a fantastic release!

 

Tags: PostgreSQL, PostgreSQL 11, PostgreSQL 11 New Features, PostgreSQL11
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Adding new table columns with default values in PostgreSQL 11 Webinar: PostgreSQL is NOT your traditional SQL database [Follow Up]
Scroll to top
×