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 / Simon's PlanetPostgreSQL3 / Upcoming Enhancements to Partitioning & Indexes in PostgreSQL 11
Simon Riggs

Upcoming Enhancements to Partitioning & Indexes in PostgreSQL 11

January 30, 2018/8 Comments/in Simon's PlanetPostgreSQL /by Simon Riggs

My colleague Alvaro Herrera has been working on a series of connected features for PostgreSQL 11. It’s worth explaining what these are rather than trying to piece together what is happening from reading commit messages.

The overall idea is to allow Partitioned tables to have Referential Integrity, by way of Primary Keys and Foreign Keys, as well as some additional tweaks.

To achieve that, we need to understand the structure of features in PostgreSQL.

Foreign Keys (FKs) are implemented using row Triggers, so we must allow Triggers to be executed on Partitioned Tables. FKs also require Primary Keys (PKs), so we must add those also.

Primary Keys are implemented using Unique Indexes, so we need to add indexes and allow them to be unique. I’ll write about partitioned indexes separately, because they are very cool. The best bit is that they don’t actually exist, its just metadata! That requires some explanations and a longer post.

So that gives us a set of features and an order in which they should be implemented:

  • Create Index on Partitioned Tables
  • Allow Unique Index on Partitioned Tables
  • Create Triggers on Partitioned Tables
  • Allow FKs on Partitioned Tables

Taken together, these features are now looking pretty good after lots of work and review. So PostgreSQL 11 is looking like we’ll be able to add referential integrity to partitioned tables.

Tags: PostgreSQL, PostgreSQL 11, PostgreSQL 11 New Features, PostgreSQL11
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
8 replies
  1. Luca Veronese
    Luca Veronese says:
    January 30, 2018 at 4:46 pm

    That is really cool. PostgreSQL is really shining nowadays.

    Reply
  2. Vincenzo Romano
    Vincenzo Romano says:
    January 30, 2018 at 4:57 pm

    I fear we are going to wait until PG12 or PG13 before we get partitioning really usable.
    Partitioning needs to be viable with thousands of partitions, not a dozen or two.

    Yes, referential integrity for partitioned tables is great, but if partitioned tables aren’t usable, then also RI won’t be.

    “Real life partitioning”™ involves tables with tens and hundreds of billion or rows. Splitting them into a dozen sub-tables won’t help much. It’s rather easy to write software to do “logical partitioning” with some code to choose the right partition!

    As of now the weak point in partitioning is the sub-table selection algorithm, not the RI. I mean, RI is important, but I feel it’s way less important than real life partitioning.

    So, if I had to work on PG partitioning I’d focus on this area first. But this is just my opinion.

    Reply
    • Simon Riggs
      Simon Riggs says:
      January 30, 2018 at 5:33 pm

      Good points. My colleague David Rowley has been working with Amit Langote on precisely the issues you mention. Hopefully, PG11 will meet your needs.

      Reply
  3. Peter Sylvester
    Peter Sylvester says:
    January 30, 2018 at 7:13 pm

    I concur with Vincenzo’s comments about handling large numbers of partitions. Oracle currently supports thousands of partitions without significant issues, really need the same in PostgreSQL. Chances are that if the tables are big enough to be using partitions there is a good chance it is a data warehouse and would typically not implement declarative FKs due to the overhead involved.

    Reply
    • Simon Riggs
      Simon Riggs says:
      January 30, 2018 at 7:51 pm

      Yes, the developers understand the issue. Efficient partition pruning was not considered an essential part of the first phase for partitioning for PG10 and we were unable to get that working in time for that release. Correcting that issue, we’ve been trying hard with a number of approaches to reduce the planning time. So I’m hopeful the situation will change for PG11.

      Reply
  4. Jörg Sonnenberger
    Jörg Sonnenberger says:
    January 31, 2018 at 3:11 pm

    I disagree quite a bit with the sentiment raised here. There are important use cases for partitions with few tables, i.e. factoring off read-only parts of tables. For those cases, referential integrity is one of the show stoppers. So please, just because it doesn’t fill your niche yet doesn’t make it unusable.

    Reply
  5. Luca Veronese
    Luca Veronese says:
    April 11, 2018 at 8:43 pm

    I strongly agree with Jörg here.
    My use case for example would be partitioned tables used in a multi-tenant OLTP database partitioned by tenant where statements are executed mostly on a single partition. In this case referential integrity is a must for me.

    Reply
  6. Marcin
    Marcin says:
    September 14, 2018 at 11:40 am

    True you can add index but there are still some limitations eg.:
    – partition by range (create_date)
    alter table table_name constraint uq_a_b_c UNIQUE (a, b, c)
    ERROR: insufficient columns in UNIQUE constraint definition
    DETAIL: UNIQUE constraint on table “table_name” lacks column
    “create_date” which is part of the partition key.
    SQL state: 0A000

    Reply

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
PG Phriday: Adventures in BAR Management Using Multimaster and BDR appropriately – LinuxConfAu
Scroll to top
×