Dive into new functionality for PostgreSQL 11

The initial PostgreSQL project started as a university project in 1986. The open source community took over the PostgreSQL project in 1996, and has consistently released a major version every year. Given the software’s complexity, this rapid release schedule has required the segmentation of major features into smaller, more basic elements. These smaller, foundational features combine to make up every major release of PostgreSQL, including PostgreSQL 11. PostgreSQL 11 is supported by both Amazon RDS for PostgreSQL and Amazon Aurora with PostgreSQL compatibility.

In this post, I take a close look at three exciting features in PostgreSQL 11: partitioning, parallelism, and just-in-time (JIT) compilation. I explore the evolution of these features across multiple PostgreSQL versions. I also cover the benefits that PostgreSQL 11 offers, and show practical examples to point out how to adapt these features to your applications.

Partitioning

As databases grow, a few tables typically are sources of the majority of the growth. Growth may center in tables that keep a historical log of all activity or in your user table.

You can deal with the high database growth through table partitioning, which means splitting a single large table into smaller, more manageable chunks (partitions) to speed up queries on those large tables. Performance improves when the database can prune away whole partitions during query execution, processing much less data. The concept of table partitioning isn’t new in PostgreSQL 11. PostgreSQL first introduced a form of table partitioning in version 8.1, released in 2005.

For example, consider the following orders table. An application adds a new row to this table for every sales order. As more sales occur, this table gets larger by the day. Tables that grow over time like this are prime candidates for time-based partitioning:

CREATE TABLE orders (
          o_orderkey INTEGER,
          o_custkey INTEGER,
          o_orderstatus CHAR(1),
          o_totalprice REAL,
          o_orderdate DATE,
          o_orderpriority CHAR(15),
          o_clerk CHAR(15),
          o_shippriority INTEGER,
          o_comment VARCHAR(79));

In version 8.1 through 9.6 of PostgreSQL, you set up partitioning using a unique feature called “table inheritance.” That is, you set up yearly partitions by creating child tables that each inherit from the parent with a table constraint to enforce the data range contained in that child table.

The PostgreSQL optimizer then uses a feature called constraint exclusion, which uses constraints to prune away partitions at query time and increase query performance:

CREATE TABLE orders_2015 (
CHECK ( o_orderdate >= DATE '2015-01-01'
AND o_orderdate < DATE '2016-01-01')
) INHERITS (orders);

CREATE TABLE orders_2016 (
CHECK ( o_orderdate >= DATE '2016-01-01'
AND o_orderdate < DATE '2017-01-01')
) INHERITS (orders);

Although constraint exclusion is effective, the method has a downside for the child inheritance model. Specifically, constraint exclusion complicates the loading data into the partitioned tables. As a result, a trigger is needed on the parent table to move rows into the appropriate child:

CREATE OR REPLACE FUNCTION partition_trigger()
  RETURNS TRIGGER AS $$
BEGIN
  EXECUTE 'INSERT INTO orders_' ||
          to_char(NEW.o_orderdate, 'YYYY') ||
          ' VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)'
  USING NEW.o_orderkey, NEW.o_custkey, NEW.o_orderstatus, NEW.o_totalprice,
        NEW.o_orderdate, NEW.o_orderpriority, NEW.o_clerk, NEW.o_shippriority,
        NEW.o_comment;

  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

PostgreSQL 10 offers a declarative partitioning syntax. You define the partitioning method and partitioning key. PostgreSQL 10 supports the range and list partitioning methods.

In addition to the clearer partitioning syntax, PostgreSQL 10 eliminates the need for triggers on parent tables. Without triggers, you can partition tables without code, and partitioning syntax makes the table easier to maintain. PostgreSQL now automatically routes rows to the correct partition, improving both data loading and maintenance.

As the name implies, range partitioning handles a range of values. For example, in time series data, a date range may be used to create daily, monthly, or yearly partitions. This is a perfect fit for our orders table:

CREATE TABLE orders (
        o_orderkey INTEGER,
        o_custkey INTEGER,
        o_orderstatus CHAR(1),
        o_totalprice REAL,
        o_orderdate DATE,
        o_orderpriority CHAR(15),
        o_clerk CHAR(15),
        o_shippriority INTEGER,
        o_comment VARCHAR(79))
PARTITION BY RANGE (o_orderdate);

CREATE TABLE orders_2015 
  PARTITION OF orders 
  FOR VALUES FROM ('2015-01-01') 
               TO ('2016-01-01');

CREATE TABLE orders_2016 
  PARTITION OF orders 
  FOR VALUES FROM ('2016-01-01') 
               TO ('2017-01-01');

Use list partitioning for cases with a limited number of fixed values. You define each partition’s list of values during partition creation. Here, I use list partitioning for the customer table, partitioning by Region with a list of nations belonging to each Region:

CREATE TABLE customers (
        c_custkey INTEGER,
        c_name VARCHAR(25),
        c_address VARCHAR(40),
        c_nationkey INTEGER,
        c_phone CHAR(15),
        c_acctbal REAL,
        c_mktsegment CHAR(10),
        c_comment VARCHAR(117))
PARTITION BY LIST (c_nationkey);

CREATE TABLE customers_asia 
  PARTITION OF customers 
  FOR VALUES IN (8, 9, 12, 18, 21);

CREATE TABLE customers_europe 
  PARTITION OF customers
  FOR VALUES IN (6, 7, 19, 22, 23);

The right choice of partitioning method and partitioning key depends on the application. Your choice could even evolve as your data changes. You may need some range partitioning and some list partitioning in the same schema.

PostgreSQL 11 extends this syntax further, enabling you to define a default partition. A default partition helps if you obtain data with values outside of established partition ranges. For example, in the orders table, I have yearly partitions going back to 2015. But I could add a row with an empty order date or, for example, January 1, 1970. Rows with values outside the established partition range default to this row instead of causing an error:

CREATE TABLE orders_default
  PARTITION OF orders DEFAULT;

Index creation on partitioned tables is also simpler in PostgreSQL 11. In PostgreSQL 10, the only way to create local indexes on all the partitions is to create them individually on each partition. Attempting to create indexes on the main parent table returns an error.

For example, in the following statement, I try to create an index on the orders table in PostgreSQL 10. The attempt predictably returns an error. I then create the index on the partition:

=> CREATE INDEX orders_o_orderdate_o_orderkey_idx
-> ON orders (o_orderdate, o_orderkey);
ERROR:  cannot create index on partitioned 
        table “orders“

=> CREATE INDEX orders_2018_o_orderdate_o_orderkey_idx 
->     ON orders_2018 (o_orderdate, o_orderkey);
CREATE INDEX 

By contrast, when I attempt to create an index on the orders table in PostgreSQL 11, the statement succeeds in building local indexes on all the partitions:

=> CREATE INDEX orders_o_orderdate_o_orderkey_idx
-> ON orders (o_orderdate, o_orderkey);
CREATE INDEX

PostgreSQL 11 also introduces a hash partitioning method that adds to the range and list methods introduced in PostgreSQL 10. Hash partitioning is useful for large tables containing no logical or natural value ranges to partition.

In my sales database, the part table offers a perfect candidate for hash partitioning. Although the table is designed to grow over time, time values do not determine the table rows. Older data may be referenced just as frequently as new data. This table represents an ideal use case for hash partitioning on the table’s primary key:

CREATE TABLE part (
        p_partkey INTEGER,
        p_name VARCHAR(55),
        p_mfgr CHAR(25),
        p_brand CHAR(10),
        p_type VARCHAR(25),
        p_size INTEGER,
        p_container CHAR(10),
        p_retailprice REAL,
        p_comment VARCHAR(23))
PARTITION BY HASH (p_partkey);

Now I only need to decide how many partitions to create. The ideal number of partitions depends on the application, but if I wanted to have 10 partitions, I would define the partition with a modulus of 10:

CREATE TABLE part_p0 
  PARTITION OF part 
  FOR VALUES WITH (MODULUS 10, REMAINDER 0);
  
CREATE TABLE part_p1 
  PARTITION OF part 
  FOR VALUES WITH (MODULUS 10, REMAINDER 1);

PostgreSQL 11 provides even further enhancements in partition pruning, which means removing the need to scan whole partitions for a query. PostgreSQL 10 and earlier versions prune partitions away while a query is being planned. If provided with enough information to rule out a value range, PostgreSQL eliminates the corresponding partition from the scan. The needed information is typically a WHERE condition that contains a constant. This method works for simple queries.

Of course, you typically access large partitioned tables by joining them to other smaller tables. But PostgreSQL 11 allows you to prune away partitions at execution time. The result of a join removes the scans of unneeded partitions. The following query shows examples of both types of partition pruning:

=> EXPLAIN (ANALYZE, VERBOSE)
SELECT lineitem.l_quantity, part.p_name
  FROM lineitem
 INNER JOIN part ON (lineitem.l_partkey = part.p_partkey)
 WHERE lineitem.l_orderkey = 215528935
 ORDER BY lineitem.l_linenumber;

The query joins together two tables that are both hash-partitioned. The WHERE condition of the query "WHERE lineitem.l_orderkey = 215528935" allows PostgreSQL to prune away all but a single partition during query planning.

You can see this in the following PostgreSQL query plan, in the line starting with "Index Scan using lineitem_p78_pkey on public.lineitem_p78." The query plan never considers the other 99 partitions of line item:

QUERY PLAN
-------------------------------------------------------------------------------------------------
 Sort  (cost=1278.06..1278.10 rows=15 width=41) (actual time=0.074..0.074 rows=6 loops=1)
   Output: lineitem_p78.l_quantity, part_p0.p_name, lineitem_p78.l_linenumber
   Sort Key: lineitem_p78.l_linenumber
   Sort Method: quicksort  Memory: 25kB
   ->  Nested Loop (cost=0.86..1277.77 rows=15 width=41)(actual time=0.024..0.068 rows=6 loops=1)
         Output: lineitem_p78.l_quantity, part_p0.p_name, lineitem_p78.l_linenumber
         ->  Append  (cost=0.43..8.77 rows=15 width=12) (actual time=0.014..0.016 rows=6 loops=1)
               ->  Index Scan using lineitem_p78_l_orderkey_idx on public.lineitem_p78  
                     (cost=0.43..8.70 rows=15 width=12) (actual time=0.013..0.015 rows=6 loops=1)
                     Output: lineitem_p78.l_quantity, lineitem_p78.l_linenumber, 
                             lineitem_p78.l_partkey
                     Index Cond: (lineitem_p78.l_orderkey = 215528935)
         ->  Append (cost=0.43..84.50 rows=10 width=37) (actual time=0.007..0.008 rows=1 loops=6)
               ->  Index Scan using part_p0_pkey on public.part_p0  
                     (cost=0.43..8.45 rows=1 width=37) (never executed)
                     Output: part_p0.p_name, part_p0.p_partkey
                     Index Cond: (part_p0.p_partkey = lineitem_p78.l_partkey)
               ->  Index Scan using part_p1_pkey on public.part_p1  
                     (cost=0.43..8.45 rows=1 width=37) (actual time=0.006..0.006 rows=1 loops=2)
                     Output: part_p1.p_name, part_p1.p_partkey
                     Index Cond: (part_p1.p_partkey = lineitem_p78.l_partkey)
               ->  Index Scan using part_p2_pkey on public.part_p2  
                     (cost=0.43..8.45 rows=1 width=37) (never executed)
                     Output: part_p2.p_name, part_p2.p_partkey
                     Index Cond: (part_p2.p_partkey = lineitem_p78.l_partkey)
               ->  Index Scan using part_p3_pkey on public.part_p3  
                     (cost=0.43..8.45 rows=1 width=37) (never executed)
                     Output: part_p3.p_name, part_p3.p_partkey
                     Index Cond: (part_p3.p_partkey = lineitem_p78.l_partkey)
               ->  Index Scan using part_p4_pkey on public.part_p4  
                     (cost=0.43..8.45 rows=1 width=37) (never executed)
                     Output: part_p4.p_name, part_p4.p_partkey
                     Index Cond: (part_p4.p_partkey = lineitem_p78.l_partkey)
               ->  Index Scan using part_p5_pkey on public.part_p5  
                     (cost=0.43..8.45 rows=1 width=37) (actual time=0.007..0.007 rows=1 loops=1)
                     Output: part_p5.p_name, part_p5.p_partkey
                     Index Cond: (part_p5.p_partkey = lineitem_p78.l_partkey)
               ->  Index Scan using part_p6_pkey on public.part_p6  
                     (cost=0.43..8.45 rows=1 width=37) (actual time=0.007..0.007 rows=1 loops=1)
                     Output: part_p6.p_name, part_p6.p_partkey
                     Index Cond: (part_p6.p_partkey = lineitem_p78.l_partkey)
               ->  Index Scan using part_p7_pkey on public.part_p7  
                     (cost=0.43..8.45 rows=1 width=37) (actual time=0.006..0.007 rows=1 loops=1)
                     Output: part_p7.p_name, part_p7.p_partkey
                     Index Cond: (part_p7.p_partkey = lineitem_p78.l_partkey)
               ->  Index Scan using part_p8_pkey on public.part_p8  
                     (cost=0.43..8.45 rows=1 width=37) (never executed)
                     Output: part_p8.p_name, part_p8.p_partkey
                     Index Cond: (part_p8.p_partkey = lineitem_p78.l_partkey)
               ->  Index Scan using part_p9_pkey on public.part_p9  
                     (cost=0.43..8.45 rows=1 width=37) (actual time=0.006..0.007 rows=1 loops=1)
                     Output: part_p9.p_name, part_p9.p_partkey
                     Index Cond: (part_p9.p_partkey = lineitem_p78.l_partkey)

The scan of the “part” table behaves differently. Multiple lines start with “Index Scan using part_pN_pkey.” The partkey table’s values are unknown during query planning. So PostgreSQL must consider scanning all the “part” table’s partitions. After PostgreSQL scans the lineitem table, PostgreSQL knows the values of partkey. PostgreSQL can then prune away the unnecessary partitions at execution time.

The query plan reflects this in the “Index Scan” lines containing the text “(never executed).” PostgreSQL can prune away five of the 10 partitions based on the rows returned from lineitem.

Parallelism

Older versions of PostgreSQL use a single CPU core for the whole execution of each query. PostgreSQL started moving away from reliance on one CPU per query in version 9.6, which first introduced parallelism.

Parallelism allows PostgreSQL to execute a query using multiple CPUs. To accomplish this, background workers share the query execution load and hand back the intermediary results to a leader process that combines and sends everything to the client. As a result, PostgreSQL can now run queries faster.

For example, look at a simple query that is standard for many businesses: a monthly sales average. The following query calculates the average sale price by month for the first quarter of 2018 against the orders table defined earlier:

=> SELECT date_part('month', o_orderdate), 
          to_char(o_orderdate, 'Month') as month,
          avg(o_totalprice::numeric)::numeric(10,2)
     FROM orders
    WHERE o_orderdate BETWEEN '2018-01-01' AND '2018-03-31'
    GROUP BY 1, 2
    ORDER BY 1;

 date_part |   month   |    avg
-----------+-----------+-----------
         1 | January   | 151136.72
         2 | February  | 151005.58
         3 | March     | 151134.33
(3 rows)

Time: 13143.755 ms (00:13.144)

PostgreSQL sequentially scans the entire 2018 partition of the orders table and then calculates the average of millions of values. Consequently, the results take more than 13 seconds to return.

With PostgreSQL 9.6 adding parallel sequential scans and parallel aggregates, you can execute the monthly sales average query using multiple CPUs. By turning up the parallelism to 2, you get the results more than twice as fast:

=> SET max_parallel_workers_per_gather = 2;
SET

=> SELECT date_part('month', o_orderdate), 
          to_char(o_orderdate, 'Month') as month,
          avg(o_totalprice::numeric)::numeric(10,2)
     FROM orders
    WHERE o_orderdate BETWEEN '2018-01-01' AND '2018-03-31'
    GROUP BY 1, 2
    ORDER BY 1;

 date_part |   month   |    avg
-----------+-----------+-----------
         1 | January   | 151136.72
         2 | February  | 151005.58
         3 | March     | 151134.33
(3 rows)

Time: 5113.373 ms (00:05.113)

PostgreSQL 10 enables you to run additional query execution operations in parallel. Parallel index scans make up the most common case, though some operations include parallel merge joins and parallel execution of stored functions. As tables grow larger, so do their indexes. When running queries against large indexes, parallel scanning help you obtain a faster result.

In the following query, I narrow the average sales price query down to a business week, which uses the index created earlier:

=> SELECT avg(o_totalprice::numeric)::numeric(10,2)
     FROM orders
    WHERE o_orderdate BETWEEN '2018-03-26' AND '2018-03-30';

    avg
-----------
 151286.04
(1 row)

Time: 1519.445 ms (00:01.519)

Using the index, the query obtains results in 1.5 seconds. By turning up the parallelism to 2, I obtain the results more than twice as fast:

=> SET max_parallel_workers_per_gather = 2;
SET
Time: 1.321 ms

=> SELECT avg(o_totalprice::numeric)::numeric(10,2)
     FROM orders
    WHERE o_orderdate BETWEEN '2018-03-26' AND '2018-03-30';

    avg
-----------
 151286.04
(1 row)

Time: 723.846 ms

Improving upon these established features, PostgreSQL 11 also introduces the ability to run maintenance operations in parallel. For example, PostgreSQL 11 enables the creation of indexes in parallel. Rapid execution of maintenance operations is critical to end users’ performance. Consequently, these operations typically run during a maintenance window.

In the following statement, I create indexes for all the partitions of the customer table:

=> CREATE INDEX customer_c_nationkey_c_custkey_idx 
       ON customer (c_nationkey, c_custkey);
CREATE INDEX
Time: 20850.740 ms (00:20.851)

The operation completes in a little over 20 seconds. By turning up the parallelism for maintenance commands to 4 for this session, this index is created almost three times as fast:

=> SET max_parallel_maintenance_workers = 4;
SET

=> CREATE INDEX customer_c_nationkey_c_custkey_idx 
       ON customer (c_nationkey, c_custkey);
CREATE INDEX
Time: 7909.719 ms (00:07.910)

JIT compilation

One new feature offered with PostgreSQL 11 is just-in-time (JIT) compilation. JIT compiles portions of a SQL expression into a native program that the CPU can execute quickly. Generating these programs incurs a high cost. On some queries, JIT program generation and query optimization can take over a second. After the query is optimized, it can run faster than the interpreted SQL expressions.

Typically, queries that perform calculations on many rows display faster query times. In the following query, I calculate the total sales, average sales price, minimum sale, and maximum sale by sales status over three years:

=> SELECT o_orderstatus, o_shippriority, sum(o_totalprice), avg(o_totalprice),
          min(o_totalprice), max(o_totalprice), count(*) as status_count
  FROM orders
 WHERE o_orderdate BETWEEN '2016-01-01' AND '2018-12-31'
GROUP BY 1, 2
ORDER BY 1, 2;
 o_orderstatus | o_shippriori |     sum     |       avg        |   min   | max   | status_count
---------------+--------------+-------------+------------------+---------+--------+--------------
 F             |            0 | 7.46565e+12 | 149822.502426566 | 811.73  | 586945 |     50324873
 O             |            0 | 2.07397e+12 | 146551.492403067 | 821.82  | 543957 |     14163724
 P             |            0 | 7.09769e+11 | 184767.360053128 | 1914.25 | 550128 |      3841445
(3 rows)

Time: 51036.862 ms (00:51.037)

This query returns results from the three aggregated rows in about 51 seconds. After I turn on JIT, the query returns results on the same three rows in about 35 seconds:

=> SET jit = on;
SET

=> SELECT o_orderstatus, o_shippriority, sum(o_totalprice), avg(o_totalprice),
          min(o_totalprice), max(o_totalprice), count(*) as status_count
  FROM orders
 WHERE o_orderdate BETWEEN '2016-01-01' AND '2018-12-31'
GROUP BY 1, 2
ORDER BY 1, 2;
 o_orderstatus | o_shippriori |     sum     |       avg        |   min   | max   | status_count
---------------+--------------+-------------+------------------+---------+--------+--------------
 F             |            0 | 7.49949e+12 | 149822.502426566 | 811.73  | 586945 |     50324873
 O             |            0 | 2.07342e+12 | 146551.492403067 | 821.82  | 543957 |     14163724
 P             |            0 | 7.0979e+11  | 184767.360053128 | 1914.25 | 550128 |      3841445
(3 rows)

Time: 35645.533 ms (00:35.646)

By enabling JIT, I can improve this query’s performance by 30%. For those queries taking tens of seconds or longer to run, JIT can deliver a significant performance gain. For most common queries, however, enabling JIT slows them down to the extent of disabling the feature by default. This feature shows promise and the infrastructure is in place to improve the features and functionality of JIT in future versions of PostgreSQL.

Summary

In this post, I explored just three of the major improvements in PostgreSQL 11: partitioning, parallelism, and JIT compilation. PostgreSQL 11 also brings you transaction control in stored procedures, covering indexes, and several performance improvements. For a full list of features in PostgreSQL 11, check out the documentation at PostgreSQL Version 11.1 on Amazon RDS.

Combine PostgreSQL 11 with Amazon Aurora with PostgreSQL compatibility to gain access to the PostgreSQL community’s many impressive features, plus all the benefits of Aurora. Aurora provides managed database capabilities like automatic failover, backup and recovery, automated patching, and push-button scaling. Aurora storage offers improved performance and durability, and this service also includes more advanced features such as fast database cloning, query plan management, and cluster cache management.

Looking forward to PostgreSQL 12, you can expect to see more usability improvements on partitioning, the start of the SQL/JSON path implementation, and much more. Although the PostgreSQL database is more than 30 years old, the project’s rate of innovation continues to accelerate.

About the Author

Jim is a Principal Database Engineer at AWS. Prior to joining AWS, Jim was CTO at OpenSCG and CEO at StormDB, a Postgres-XC scalable public cloud solution. Jim was chief architect and part of the founding team of EnterpriseDB. Jim also very active in the PostgreSQL community, co-founding both the New York and Philadelphia PostgreSQL Meetup groups, co-organizing the largest PostgreSQL conferences, and serving on the Board of the PostgreSQL Foundation. Jim also speaks regularly at PostgreSQL and other open sources conferences.