How to Take Advantage of the New Partitioning Features in PostgreSQL 11

What is Partitioning?

Partitioning splits large tables into smaller pieces, which helps with increasing query performance, making maintenance tasks easier, improving the efficiency of data archival, and faster database backups. You can read more about PostgreSQL partitioning in our blog “A Guide to Partitioning Data In PostgreSQL”.

With the recent release of PostgreSQL 11 there are a lot of new amazing partitioning features. The details of these new partitioning features will be covered in this blog with a few code examples.

Updating The Partition Keys

Prior to PostgreSQL 11, Update statement that changes the value of partition key was restricted and not allowed. This is now possible in the new version. Update statement can change the value of partition key; it actually moves the rows to the correct partition table. Under the hood it basically executes DELETE FROM old partition and INSERT into new partition ( DELETE + INSERT).

Alright, let’s test this out. Create a table and verify how the update works on partition key.

CREATE TABLE customers(cust_id bigint NOT NULL,cust_name varchar(32) NOT NULL,cust_address text,
cust_country text)PARTITION BY LIST(cust_country);
CREATE TABLE customer_ind PARTITION OF customers FOR VALUES IN ('ind');
CREATE TABLE customer_jap PARTITION OF customers FOR VALUES IN ('jap');
CREATE TABLE customers_def PARTITION OF customers DEFAULT;

severalnines_v11=# INSERT INTO customers VALUES (2039,'Puja','Hyderabad','ind');
INSERT 0 1

severalnines_v11=#  SELECT * FROM customer_ind;
 cust_id | cust_name | cust_address | cust_country
  2039 | Puja      | Hyderabad    | ind
(1 row)

severalnines_v11=# UPDATE customers SET cust_country ='jap' WHERE cust_id=2039;
UPDATE 1
--  it moved the row to correct  partition table.

severalnines_v11=# SELECT * FROM customer_ind;
 cust_id | cust_name | cust_address | cust_country
---------+-----------+--------------+--------------
(0 rows)

severalnines_v11=# SELECT * FROM customer_jap;
 cust_id | cust_name | cust_address | cust_country
---------+-----------+--------------+--------------
    2039 | Puja      | Hyderabad    | jap
(1 row)

Caution: The UPDATE will error out, if there is no default partition table and updated values doesn’t match with partition criteria in any child table.

severalnines_v11=#  UPDATE customers1 SET cust_country ='ypp' WHERE cust_id=2039;
2018-11-21 00:13:54.901 IST [1479] ERROR:  no partition of relation "customers1" found for row
2018-11-21 00:13:54.901 IST [1479] DETAIL:  Partition key of the failing row contains (cust_country) = (ypp).
2018-11-21 00:13:54.901 IST [1479] STATEMENT:  UPDATE customers1 SET cust_country ='ypp' WHERE cust_id=2039;
ERROR:  no partition of relation "customers1" found for row
DETAIL:  Partition key of the failing row contains (cust_country) = (ypp).
[ -- the value of cust_country was not mapped to any part table so it failed]

Creating a Default Partition

The PostgreSQL 11 DEFAULT partition feature stores tuples that don’t map to any other partition. Prior to PostgreSQL 11, these rows would error out. A row that is not mapped to any partition table would be inserted in the default partition.

Lab Example: `USA` country code was not defined in the partition table below, but still it gets inserted in the default table successfully.

CREATE TABLE customers_def PARTITION OF customers DEFAULT;
severalnines_v11=#  INSERT INTO customers VALUES (4499,'Tony','Arizona','USA');
INSERT 0 1
severalnines_v11=#  select * FROM customers_def;
 cust_id | cust_name | cust_address | cust_country
---------+-----------+--------------+--------------
    4499 | Tony      | Arizona      | USA

Word of caution: Default partition will prevent any new partition addition if that partition value exists in the default table. In this case `USA` existed in Default partition so it won’t work like below.

severalnines_v11=# CREATE TABLE customer_usa PARTITION OF customers FOR VALUES IN ('USA');
2018-11-21 00:46:34.890 IST [1526] ERROR:  updated partition constraint for default partition "customers_def" would be violated by some row
2018-11-21 00:46:34.890 IST [1526] STATEMENT:  CREATE TABLE customer_usa PARTITION OF customers FOR VALUES IN ('USA');ERROR:  updated partition constraint for default partition "customers_def" would be violated by some row
severalnines_v11=#
Resolution - You need to move/remove those rows from Default table, then it will then let you create new part table like below.
severalnines_v11=# DELETE FROM customers_def WHERE cust_country in ('USA'); DELETE 1
severalnines_v11=# CREATE TABLE customer_usa PARTITION OF customers FOR VALUES IN ('USA');
CREATE TABLE
severalnines_v11=#
Nudgets :

DEFAULT partition cannot be specified for HASH partitioned table. There cannot be more than one DEFAULT table for partition table.

Hash Partitioning

It is a new partition mechanism, if you can not decide on a range or list partition (as you are not sure how big the bucket would be). Hash partitioning solves this data distribution issue.

The table is partitioned by specifying a modulus and a remainder for each partition. Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder. The HASH function ensures that rows will be distributed mostly evenly in all the partition table.

To begin with, you need to decide how many numbers of the partition table are required and, accordingly, modulus and remainder can be defined; if modulus would be 4, the remainder can only be from [0-3].

[Modulus – Number of tables | Remainder – Which value of remainder goes to which bucket ]

How to Setup a Hash Partition

-- hash partition
CREATE TABLE part_hash_test (x int, y text) PARTITION BY hash (x);
-- create child partitions
CREATE TABLE part_hash_test_0 PARTITION OF part_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE part_hash_test_1 PARTITION OF part_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE part_hash_test_2 PARTITION OF part_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE part_hash_test_3 PARTITION OF part_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Insert 50k records in the parent table:

severalnines_v11=# INSERT INTO part_hash_test SELECT generate_series(0,50000);
INSERT 0 50001

and see how it distributed records evenly in the child table …

severalnines_v11=# SELECT count(1),tableoid::regclass FROM part_hash_test GROUP by 2 order by 2 ;
 count |     tableoid
-------+------------------
 12537 | part_hash_test_0
 12473 | part_hash_test_1
 12509 | part_hash_test_2
 12482 | part_hash_test_3
(4 rows)

We can not change the number of partitions specified by `Modulus` earlier, so you need to plan well before the requirements for the number of partition tables.

It will error out when you try to add a new partition with a different remainder.

severalnines_v11=# CREATE TABLE part_hash_test_5 PARTITION OF part_hash_test FOR VALUES
WITH (MODULUS 4, REMAINDER 5);severalnines_v11-#
2018-11-21 01:51:28.966 IST [1675] ERROR:  remainder for hash partition must be less than modulus
2018-11-21 01:51:28.966 IST [1675] STATEMENT:  CREATE TABLE part_hash_test_5 PARTITION OF part_hash_test FOR VALUES  WITH (MODULUS 4, REMAINDER 5);

Hash partitioning can work on any data type and it can work for UUID type too. It’s always recommended that the number of tables should be a power of 2, and it is also not mandatory to use the same modulus while creating the table; this will help to create the partition table later as required.

This implementation would also make vacuum faster and can enable partition wise join.

Support for Foreign Keys

Prior to PostgreSQL 11, the foreign key in partition table was not supported. The foreign keys are possible in partition table now and below is how…

severalnines_v11=# CREATE TABLE customers2 ( cust_id integer PRIMARY KEY );
CREATE TABLE

severalnines_v11=# CREATE TABLE account (
    ac_date   date    NOT NULL,
    cust_id  integer REFERENCES customers2(cust_id),
     amount INTEGER NOT NULL) PARTITION BY RANGE (ac_date);
CREATE TABLE

Auto Index Creation on Child Tables

In previous versions of PostgreSQL it was a manual effort to create an index on every partition table. In PostgreSQL version 11, it’s quite convenient for users. Once the index is created on the master table, it will automatically create the index with the same configuration on all existing child partition and take care of any future partition tables as well.

Index Created on Master Table

severalnines_v11=# CREATE index idx_name ON customers(cust_name);
CREATE INDEX

It automatically created the index on all child tables as below. ( Verify with catalog table)

severalnines_v11=# SELECT tablename,indexname,indexdef FROM pg_indexes WHERE tablename ilike '%customer_%';
   tablename   |          indexname          |       indexdef
---------------+-----------------------------+------------------------------------------------------------------------------------------
 customer_ind  | customer_ind_cust_name_idx  | CREATE INDEX customer_ind_cust_name_idx ON public.customer_ind USING btree (cust_name)
 customer_jap  | customer_jap_cust_name_idx  | CREATE INDEX customer_jap_cust_name_idx ON public.customer_jap USING btree (cust_name)
 customer_usa  | customer_usa_cust_name_idx  | CREATE INDEX customer_usa_cust_name_idx ON public.customer_usa USING btree (cust_name)
 customers_def | customers_def_cust_name_idx | CREATE INDEX customers_def_cust_name_idx ON public.customers_def USING btree (cust_name)
(4 rows)

Index can only be created on a master table, it cannot be on a child table. Automatically generated indexes cannot be deleted individually.

Auto Trigger Creation on Child Tables

Once the trigger is created on the master table, it will automatically create the trigger on all child tables (this behavior is similar to the one seen for index).

Able to Create a Unique Index

In version 11 unique indexes can be added to the master table which will create the unique constraint on all existing child tables and future partition tables.

Let’s create a master table with unique constraints.

CREATE TABLE uniq_customers(  cust_id bigint NOT NULL, cust_name varchar(32) NOT NULL, cust_address text, cust_country text,cust_email text, unique(cust_email,cust_id,cust_country)  )PARTITION BY LIST(cust_country);

The unique constraint has been created on child table automatically like below.

severalnines_v11=# SELECT table_name,constraint_name,constraint_type FROM information_schema.table_constraints WHERE table_name ilike '%uniq%' AND constraint_type = 'UNIQUE';
    table_name     |                    constraint_name                    | constraint_type
-------------------+-------------------------------------------------------+-----------------
 uniq_customers    | uniq_customers_cust_email_cust_id_cust_country_key    | UNIQUE
 uniq_customer_ind | uniq_customer_ind_cust_email_cust_id_cust_country_key | UNIQUE
(2 rows)

Caution : A unique constraint on the parent table does not actually guarantee uniqueness across the whole partitioning hierarchy. It is not global constraint, it is local only.

Faster Query Performance

Dynamic Partition Pruning

In PostgreSQL 11, the binary search enables faster identification of required child tables whether it’s LIST or RANGE partitioned. The hashing function finds the matching partition for HASH partition. It actually dynamically eliminates the partition table(s) which are not required and boosts the Query performance.

The dynamic partition pruning can be controlled by `enable_partition_pruning` parameter.

severalnines_v11=# show enable_partition_pruning;
 enable_partition_pruning
--------------------------
 off
(1 row)

severalnines_v11=# EXPLAIN SELECT * from customers where cust_country = 'ind';
                             QUERY PLAN
---------------------------------------------------------------------
 Append  (cost=0.00..18.54 rows=5 width=154)
   ->  Seq Scan on customer_ind  (cost=0.00..1.01 rows=1 width=154)
         Filter: (cust_country = 'ind'::text)
   ->  Seq Scan on customer_jap  (cost=0.00..1.00 rows=1 width=154)
         Filter: (cust_country = 'ind'::text)
   ->  Seq Scan on customer_usa  (cost=0.00..15.50 rows=2 width=154)
         Filter: (cust_country = 'ind'::text)
   ->  Seq Scan on customers_def  (cost=0.00..1.00 rows=1 width=154)
         Filter: (cust_country = 'ind'::text)
(9 rows)
Enabled the parameter to ON.

severalnines_v11=# set enable_partition_pruning TO on;
SET

severalnines_v11=# EXPLAIN SELECT * from customers where cust_country = 'ind';
                             QUERY PLAN
--------------------------------------------------------------------
 Append  (cost=0.00..1.02 rows=1 width=154)
   ->  Seq Scan on customer_ind  (cost=0.00..1.01 rows=1 width=154)
         Filter: (cust_country = 'ind'::text)
(3 rows)

The other awesome implementation is like this.

Execution-Time Partition Pruning

In PostgreSQL versions prior to 11, partition pruning can only happen at plan time; planner requires a value of partition key to identify the correct partition. This behaviour is fixed in PostgreSQL 11, as the execution time planner would know what value is getting supplied and based on that partition selection / elimination is possible and would run a lot faster. The use case can be a query which uses parameter (prepared statement) OR subquery which provides the value as a parameter.

Example : cus_country is partition key and getting value from subquery
severalnines_v11=# explain analyze  select * from customers WHERE cust_country = (select cust_count_x FROM test_execution_prun1);
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Append  (cost=23.60..42.14 rows=5 width=154) (actual time=0.019..0.020 rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on test_execution_prun1  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.006..0.007 rows=1 loops=1)
   ->  Seq Scan on customer_ind  (cost=0.00..1.01 rows=1 width=154) (never executed)
         Filter: (cust_country = $0)
   ->  Seq Scan on customer_jap  (cost=0.00..1.00 rows=1 width=154) (never executed)
         Filter: (cust_country = $0)
   ->  Seq Scan on customer_usa  (cost=0.00..15.50 rows=2 width=154) (never executed)
         Filter: (cust_country = $0)
   ->  Seq Scan on customers_def  (cost=0.00..1.00 rows=1 width=154) (actual time=0.003..0.003 rows=0 loops=1)
         Filter: (cust_country = $0)
 Planning Time: 0.237 ms
 Execution Time: 0.057 ms
(13 rows)

In explain plan above, we can see, at the time of execution, the planner on the fly identified the correct partition table based on parameter value, and ran much faster and did not spend time on scan/loop on other partition table (see never executed section in explain plan above). This is very powerful and started a new era of performance enhancement in partitioning.

Partition Wise Aggregate

Parameter: enable_partitionwise_aggregate

If the partition key matches the grouping key, every partition will produce a discrete set of groups instead of scanning all the partition at once. It will do the parallel aggregate for each partition and during the final outcome it concatenates all results.

severalnines_v11=# explain SELECT count(1),cust_country FROM customers GROUP BY 2;
                                 QUERY PLAN
----------------------------------------------------------------------------
 HashAggregate  (cost=21.84..23.84 rows=200 width=40)
   Group Key: customer_ind.cust_country
   ->  Append  (cost=0.00..19.62 rows=443 width=32)
         ->  Seq Scan on customer_ind  (cost=0.00..1.01 rows=1 width=32)
         ->  Seq Scan on customer_jap  (cost=0.00..1.00 rows=1 width=32)
         ->  Seq Scan on customer_usa  (cost=0.00..14.40 rows=440 width=32)
         ->  Seq Scan on customers_def  (cost=0.00..1.00 rows=1 width=32)
(7 rows)

severalnines_v11=# SET  enable_partitionwise_aggregate TO on;
SET

severalnines_v11=#  explain SELECT count(1),cust_country FROM customers GROUP BY 2;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Append  (cost=1.01..22.67 rows=203 width=40)
   ->  HashAggregate  (cost=1.01..1.02 rows=1 width=40)
         Group Key: customer_ind.cust_country
         ->  Seq Scan on customer_ind  (cost=0.00..1.01 rows=1 width=32)
   ->  HashAggregate  (cost=1.00..1.01 rows=1 width=40)
         Group Key: customer_jap.cust_country
         ->  Seq Scan on customer_jap  (cost=0.00..1.00 rows=1 width=32)
   ->  HashAggregate  (cost=16.60..18.60 rows=200 width=40)
         Group Key: customer_usa.cust_country
         ->  Seq Scan on customer_usa  (cost=0.00..14.40 rows=440 width=32)
   ->  HashAggregate  (cost=1.00..1.01 rows=1 width=40)
         Group Key: customers_def.cust_country
         ->  Seq Scan on customers_def  (cost=0.00..1.00 rows=1 width=32)
(13 rows)

This is surely faster as it includes parallel aggregation processing and per partition scanning.

Catalog query can be used to know all parent partition tables.

SELECT relname FROM pg_class WHERE oid in (select partrelid FROM  pg_partitioned_table);

Brief Partition Feature Matrix

What’ Next?

Partitioning Performance

This is one of the most active work areas now in PostgreSQL community. PostgreSQL Version 12 will be packaged with even more performance improvements in the partitioning space. Version 12 is expected to release in November of 2019.