Contents

PG Partman – Sub-partitioning

Contents

After my talk at PGCon 2014 where I discussed pg_partman, someone I met at the bar track said they’d use it in a heartbeat if it supported sub-partitioning. Discussing this with others and reading online, I found that there is quite a demand for this feature and the partitioning methods in MySQL & Oracle both support this as well. So I set out to see if I could incorporate it. I thought I’d had it figured out pretty easily and started writing this blog post a while ago (last October) to precede the release of version 1.8.0. Then I started working on the examples here and realized this is a trickier problem to manage than I anticipated. The tricky part being managing the context relationship between the top level parent and their child sub-partitions in a general manner that would work for all partitioning types pg_partman supports. When I first started working on the feature, I’d get things like this:

...
  partman_test.time_static_table_p2013_p2013_12_30,
  partman_test.time_static_table_p2013_p2013_12_31,
  partman_test.time_static_table_p2013_p2014_09_06,
  partman_test.time_static_table_p2013_p2014_09_07,
  partman_test.time_static_table_p2013_p2014_09_08,
  partman_test.time_static_table_p2013_p2014_09_09,
...

Obviously, having 2014 child sub-partitions in the 2013 parent partition set doesn’t make sense. I believe I’ve gotten this figured out and handled now in version 1.8.0 and fixed several issues encountered since then in 1.8.1 and 1.8.2 (thanks to the users that reported them!). Also if the parent is serial and the child is time (or vice versa), there’s no contextual relationship and all the child tables will be created in every partition set.

When I first saw that all sub-partitioning did was move the data further down into the inheritance tree, I at first wondered at the expected gains from this, outside of just organizing the data better. The use case of the person I mentioned in the first sentence gave a bit of a hint to the gains. If I remember correctly, they had an extremely large amount of time-series data that needed to be queried as efficiently as possible. One of the advantages of partitioning is the constraint exclusion feature (see my other post for more details on this) which allows the query plan to skip tables that it knows don’t contain that data. But postgres still has to do some work in order to figure out that those tables can be excluded in the first place. For very large partition sets, even this is a noticeable performance hit. Or so I’m told, as I do not have data sets near big enough to really evaluate this claim. But knowing how constraint exclusion works, I could see that as a possibility. Sub-partitioning, with a known naming pattern to the child tables, allows an application to target directly the exact child tables it needs and avoid even the minor overhead of constraint exclusion in the query plan.

Let’s see how it works.

First I create a standard, yearly partitioned table set

keith=# CREATE SCHEMA partman_test;
CREATE SCHEMA
Time: 5.125 ms
keith=# CREATE TABLE partman_test.time_static_table (col1 serial primary key, col2 text, col3 timestamptz NOT NULL DEFAULT now());
CREATE TABLE
Time: 25.398 ms
keith=# CREATE INDEX ON partman_test.time_static_table (col3);
CREATE INDEX
Time: 15.003 ms
keith=# SELECT partman.create_parent('partman_test.time_static_table', 'col3', 'time-static', 'yearly');
 create_parent 
---------------
 t
(1 row)

keith=# \d+ partman_test.time_static_table
                                                          Table "partman_test.time_static_table"
 Column |           Type           |                                   Modifiers                                   | Storage  | Stats target | Description 
--------+--------------------------+-------------------------------------------------------------------------------+----------+--------------+-------------
 col1   | integer                  | not null default nextval('partman_test.time_static_table_col1_seq'::regclass) | plain    |              | 
 col2   | text                     |                                                                               | extended |              | 
 col3   | timestamp with time zone | not null default now()                                                        | plain    |              | 
Indexes:
    "time_static_table_pkey" PRIMARY KEY, btree (col1)
    "time_static_table_col3_idx" btree (col3)
Triggers:
    time_static_table_part_trig BEFORE INSERT ON partman_test.time_static_table FOR EACH ROW EXECUTE PROCEDURE partman_test.time_static_table_part_trig_func()
Child tables: partman_test.time_static_table_p2011,
              partman_test.time_static_table_p2012,
              partman_test.time_static_table_p2013,
              partman_test.time_static_table_p2014,
              partman_test.time_static_table_p2015,
              partman_test.time_static_table_p2016,
              partman_test.time_static_table_p2017,
              partman_test.time_static_table_p2018,
              partman_test.time_static_table_p2019

Next some data is added and I check that everything looks right

keith=# INSERT INTO partman_test.time_static_table (col3) VALUES (generate_series('2011-01-01 00:00:00'::timestamptz, CURRENT_TIMESTAMP, '1 hour'::interval));
INSERT 0 0
Time: 1000.392 ms
keith=# SELECT count(*) FROM partman_test.time_static_table;
 count 
-------
 36613
(1 row)

Time: 12.293 ms
keith=# select min(col3), max(col3) FROM partman_test.time_static_table;
          min           |          max           
------------------------+------------------------
 2011-01-01 00:00:00-05 | 2015-03-06 12:00:00-05
(1 row)

Time: 3.915 ms
keith=# select min(col3), max(col3) FROM partman_test.time_static_table_p2013;
          min           |          max           
------------------------+------------------------
 2013-01-01 00:00:00-05 | 2013-12-31 23:00:00-05
(1 row)

Time: 1.794 ms
keith=# select min(col3), max(col3) FROM partman_test.time_static_table_p2015;
          min           |          max           
------------------------+------------------------
 2015-01-01 00:00:00-05 | 2015-03-06 12:00:00-05
(1 row)

Time: 1.785 ms

Say now we want to subpartition by day to better organize our data because we’re expecting to get A LOT of it. The new create_sub_parent() function works just like create_parent() except the first parameter is instead an already existing parent table whose children we want to partition. In this case, we’ll be telling it we want each yearly child table to be further partitioned by day.

keith=# SELECT partman.create_sub_parent('partman_test.time_static_table', 'col3', 'time-static', 'daily');
 create_sub_parent 
-------------------
 t
(1 row)

Time: 3408.460 ms

Hopefully you’ve realized that all the data we inserted isn’t yet partitioned to the new daily tables yet. It all still resides in each one of the yearly sub-parent tables and the only tables that were created in 2015 are the ones around the current date of March 6th, 2015 +/- 4 days (since the premake config value is set to 4). For previous and future years, only a single partition was created for the lowest possible values. All parent tables in a partition set managed by pg_partman, at all partitioning levels, have at least one child, even if they have no data. You can see that for 2014 below. I don’t yet have things figured out for the data partitioning functions & scripts to handle sub-partitioning, but in the mean time, a query like the the one below the table definitions can generate the script lines for every sub-parent table for a given parent.

keith=# \d+ partman_test.time_static_table_p2014
                                                       Table "partman_test.time_static_table_p2014"
 Column |           Type           |                                   Modifiers                                   | Storage  | Stats target | Description 
--------+--------------------------+-------------------------------------------------------------------------------+----------+--------------+-------------
 col1   | integer                  | not null default nextval('partman_test.time_static_table_col1_seq'::regclass) | plain    |              | 
 col2   | text                     |                                                                               | extended |              | 
 col3   | timestamp with time zone | not null default now()                                                        | plain    |              | 
Indexes:
    "time_static_table_p2014_pkey" PRIMARY KEY, btree (col1)
    "time_static_table_p2014_col3_idx" btree (col3)
Check constraints:
    "time_static_table_p2014_partition_check" CHECK (col3 >= '2014-01-01 00:00:00-05'::timestamp with time zone AND col3 < '2015-01-01 00:00:00-05'::timestamp with time zone)
Triggers:
    time_static_table_p2014_part_trig BEFORE INSERT ON partman_test.time_static_table_p2014 FOR EACH ROW EXECUTE PROCEDURE partman_test.time_static_table_p2014_part_trig_func()
Inherits: partman_test.time_static_table
Child tables: partman_test.time_static_table_p2014_p2014_01_01


keith=# \d+ partman_test.time_static_table_p2015
                                                       Table "partman_test.time_static_table_p2015"
 Column |           Type           |                                   Modifiers                                   | Storage  | Stats target | Description 
--------+--------------------------+-------------------------------------------------------------------------------+----------+--------------+-------------
 col1   | integer                  | not null default nextval('partman_test.time_static_table_col1_seq'::regclass) | plain    |              | 
 col2   | text                     |                                                                               | extended |              | 
 col3   | timestamp with time zone | not null default now()                                                        | plain    |              | 
Indexes:
    "time_static_table_p2015_pkey" PRIMARY KEY, btree (col1)
    "time_static_table_p2015_col3_idx" btree (col3)
Check constraints:
    "time_static_table_p2015_partition_check" CHECK (col3 >= '2015-01-01 00:00:00-05'::timestamp with time zone AND col3 < '2016-01-01 00:00:00-05'::timestamp with time zone)
Triggers:
    time_static_table_p2015_part_trig BEFORE INSERT ON partman_test.time_static_table_p2015 FOR EACH ROW EXECUTE PROCEDURE partman_test.time_static_table_p2015_part_trig_func()
Inherits: partman_test.time_static_table
Child tables: partman_test.time_static_table_p2015_p2015_03_02,
              partman_test.time_static_table_p2015_p2015_03_03,
              partman_test.time_static_table_p2015_p2015_03_04,
              partman_test.time_static_table_p2015_p2015_03_05,
              partman_test.time_static_table_p2015_p2015_03_06,
              partman_test.time_static_table_p2015_p2015_03_07,
              partman_test.time_static_table_p2015_p2015_03_08,
              partman_test.time_static_table_p2015_p2015_03_09,
              partman_test.time_static_table_p2015_p2015_03_10

keith=# SELECT DISTINCT 'partition_data.py -p '|| inhparent::regclass ||' -t time -c host=localhost'
FROM pg_inherits
WHERE inhparent::regclass::text ~ 'partman_test.time_static_table'
ORDER BY 1;
                                      ?column?                                       
-------------------------------------------------------------------------------------
 partition_data.py -p partman_test.time_static_table_p2011 -t time -c host=localhost
 partition_data.py -p partman_test.time_static_table_p2012 -t time -c host=localhost
 partition_data.py -p partman_test.time_static_table_p2013 -t time -c host=localhost
 partition_data.py -p partman_test.time_static_table_p2014 -t time -c host=localhost
 partition_data.py -p partman_test.time_static_table_p2015 -t time -c host=localhost
 partition_data.py -p partman_test.time_static_table_p2016 -t time -c host=localhost
 partition_data.py -p partman_test.time_static_table_p2017 -t time -c host=localhost
 partition_data.py -p partman_test.time_static_table_p2018 -t time -c host=localhost
 partition_data.py -p partman_test.time_static_table_p2019 -t time -c host=localhost
 partition_data.py -p partman_test.time_static_table -t time -c host=localhost
(10 rows)

Time: 3.539 ms

After running the partitioning script for each parent, you can see it automatically created 365 child partitions for 2014 (because there was data in the tables for every day) and only 69 for 2015 since we’re only partway into the year. It did so for the other years as well, but I figured showing one should be proof enough it worked.

keith=# \d partman_test.time_static_table_p2014
                                   Table "partman_test.time_static_table_p2014"
 Column |           Type           |                                   Modifiers                                   
--------+--------------------------+-------------------------------------------------------------------------------
 col1   | integer                  | not null default nextval('partman_test.time_static_table_col1_seq'::regclass)
 col2   | text                     | 
 col3   | timestamp with time zone | not null default now()
Indexes:
    "time_static_table_p2014_pkey" PRIMARY KEY, btree (col1)
    "time_static_table_p2014_col3_idx" btree (col3)
Check constraints:
    "time_static_table_p2014_partition_check" CHECK (col3 >= '2014-01-01 00:00:00-05'::timestamp with time zone AND col3 < '2015-01-01 00:00:00-05'::timestamp with time zone)
Triggers:
    time_static_table_p2014_part_trig BEFORE INSERT ON partman_test.time_static_table_p2014 FOR EACH ROW EXECUTE PROCEDURE partman_test.time_static_table_p2014_part_trig_func()
Inherits: partman_test.time_static_table
Number of child tables: 365 (Use \d+ to list them.)

keith=# \d partman_test.time_static_table_p2015
                                   Table "partman_test.time_static_table_p2015"
 Column |           Type           |                                   Modifiers                                   
--------+--------------------------+-------------------------------------------------------------------------------
 col1   | integer                  | not null default nextval('partman_test.time_static_table_col1_seq'::regclass)
 col2   | text                     | 
 col3   | timestamp with time zone | not null default now()
Indexes:
    "time_static_table_p2015_pkey" PRIMARY KEY, btree (col1)
    "time_static_table_p2015_col3_idx" btree (col3)
Check constraints:
    "time_static_table_p2015_partition_check" CHECK (col3 >= '2015-01-01 00:00:00-05'::timestamp with time zone AND col3 < '2016-01-01 00:00:00-05'::timestamp with time zone)
Triggers:
    time_static_table_p2015_part_trig BEFORE INSERT ON partman_test.time_static_table_p2015 FOR EACH ROW EXECUTE PROCEDURE partman_test.time_static_table_p2015_part_trig_func()
Inherits: partman_test.time_static_table
Number of child tables: 69 (Use \d+ to list them.)

Since this is the first sub-partition level, that parent table argument to create_sub_parent() just happens to be the same as we originally used for create_parent(). If you then wanted to again further sub-partition one of the new child tables, you would feed that to create_sub_partition() and it would be different.

I’ve also included a howto.md file in pg_partman now that gives some more detailed instructions on this and also how to undo such partitioning as well. If anyone has any issues with this feature, I’d appreciate the feedback.

Also, as a sneak preview for what’s currently in development, I believe I’ve gotten a very simple background worker process to handle partition maintenance working. This means, for the general maintenance where you call run_maintenance() with no parent table argument, you will no longer need an external scheduler such as cron! Just set a few variables in postgresql.conf and pg_partman will take care of things all within postgres itself. This does mean the next major version of pg_partman (2.0.0) will be 9.4+ only (I’m creating a dynamic BGW), but it also allows me to simplify a lot of code I’d been keeping around for 9.1 compatibility and add more features that are only available in later versions. So, think of this as more motivation to get your systems upgraded if you want to keep up with new features in this extension!