2ndQuadrant | PostgreSQL
PostgreSQL Solutions for the Enterprise
+39 0574 159 3000
  • Contact Us
  • EN
    • FR
    • IT
    • ES
    • DE
  • Support & Services
    • Support
      • 24/7 PostgreSQL Support
      • Developer Support
      • IBM Z Production Support
    • DBA Services
      • Remote DBA
      • Database Monitoring
    • Consulting Services
      • Health Check
      • Performance Tuning
      • Database Security Audit
      • PostgreSQL Upgrade
      • Kubernetes for Postgres and BDR
    • Migration Services
      • Migrate to PostgreSQL
      • Migration Assessment
  • Products
    • PostgreSQL with High Availability
    • BDR
    • 2ndQPostgres
    • pglogical
      • Installation instruction for pglogical
      • Documentation
    • repmgr
    • Barman
    • Postgres Cloud Manager
    • SQL Firewall
    • Postgres-XL
    • OmniDB
    • Postgres Installer
    • 2UDA
  • Downloads
    • Postgres Installer
    • 2UDA – Unified Data Analytics
  • Postgres Learning Center
    • Webinars
      • You forgot to put the WHERE in DELETE?
      • BDR Overview
    • Whitepapers
      • Highly Available Postgres Clusters
      • AlwaysOn Postgres
      • BDR
      • PostgreSQL Security Best Practices
    • 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
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Training
      • Training Catalog and Scheduled Courses
        • Advanced Development & Performance
        • Linux for PostgreSQL DBAs
        • BDR
        • PostgreSQL Database Administration
        • PostgreSQL Data Warehousing & Partitioning
        • PostgreSQL for Developers
        • PostgreSQL Immersion
        • PostgreSQL Immersion for Cloud Databases
        • PostgreSQL Security
        • Postgres-XL-10
        • Practical SQL
        • Replication, Backup & Disaster Recovery
        • Introduction to PostgreSQL and Kubernetes
    • 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
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • Business Case for PostgreSQL
      • Security Information
    • Events
    • Blog
  • About Us
    • About 2ndQuadrant
    • What Does “2ndQuadrant” Mean?
    • 2ndQuadrant’s Passion for PostgreSQL
    • Ask Simon
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu
You are here: Home / Blog / Simon's PlanetPostgreSQL / Upcoming Enhancements to Partitioning & Indexes in PostgreSQL 11
Simon Riggs

Upcoming Enhancements to Partitioning & Indexes in PostgreSQL 11

January 30, 2018/7 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
7 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

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

Recent Posts

  • Setting SSL/TLS protocol versions with PostgreSQL 12 November 27, 2019
  • Webinar: Using SSL with PostgreSQL and pgbouncer [Follow Up] November 14, 2019
  • PostgreSQL 12: Implementing K-Nearest Neighbor Space Partitioned Generalized Search Tree Indexes November 5, 2019
  • Webinar: PostgreSQL Partitioning [Follow up] October 28, 2019
  • Postgres-BDR: It is also about fast safe upgrades October 15, 2019

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 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB kanban logical decoding logical replication monitoring open source performance PG12 pgbarman pgday pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL 11 PostgreSQL11 PostgreSQL 11 New Features postgresql repmgr Recovery release replication sql standby wal webinar
UK +44 (0)870 766 7756

US +1 650 378 1218

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

©2001-2019 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
×