Keith's Ramblings…

Update 2016-07-20: Since this blog post, I’ve recently gone back and updated pg_doc_store to take advantage of the new INSERT ON CONFLICT (upsert) feature in 9.5. So the extension is much more ready for possible production use if anyone finds it useful.

This past week I’ve had two experiences that show the amazing benefits of having your code be part of an open source community. The first involves my pg_partman extension. People have been asking me for quite some time about having a more generalized partitioning solution beyond just time/serial. I’ve resisted because that’s really not the focus of the tool since outside of those two types, partitioning is usually a once and done setup and only rarely needs further maintenance. Also, that would add quite a bit more complexity if I wanted to support, for example, all the things that MySQL does (range, list, hash, key) and all the variations that can be possible in there. I’ve already been feeling feature creep as it is, so wasn’t in a hurry for this. But, a user of pg_partman submitted some great work for getting a generalized range partition setup going with hopes of possibly integrating it with pg_partman. I instead thought it would be better released as its own tool. You can find his repo here and give it a try if you’ve been needing that feature: (UPDATE: Range Partitioning project has been discontinued since native partitioning was included in PG10+. Link removed since repo is gone).

I told that story to tell another similar story with the roles reversed. Around the same time I was testing out that range partitioning code, I came across this talk from Rob Conery about the new JSONB document storage features in PostgreSQL 9.4 (as well as learning about his interesting Star Wars Theory (possible episode 7 spoiler alert)).

I still hadn’t had any chances for practical use of JSON in any work I’ve done, but the pg_docs_api he demos at the end caught my interest. And then his note at the bottom of his README requesting a plpgsql version gave me something practical to work on to learn a bit more.

The closest I’ve come to actually using any NoSQL has been using Redis as a caching frontend and even then it was for a third-party tool where I don’t really look at the contents much. I did do the MongoDB tutorial program so I’m familiar with how it basically works, but never used it in production. One thing I always thought was neat was how you can just throw schemaless data at it and it (mostly) stores it without argument, adding some extra meta-data to make it more useful later. The pg_docs_api is the first attempt I saw to bring something similar to this to PostgreSQL. And even if it turns out there’s something like this already out there and I’m duplicating work, or it doesn’t actually end up being very useful for others in the end, it was still useful for me as a learning opportunity.

So I forked the repo and did the work. I submitted it back as a push request, turning it into an extension as well. Turns out the author decided to do exactly what I did above. He suggested I release it as my own project since he felt it was unique enough from his. So I give you pg_doc_store.

Just as a quick demo, there are some examples below. More details can be found in the docs. The original author looked to be replicating the create, save, & find commands in MongoDB so I tried to keep that theme going. Just throw your json at it with the save_document() command and things should “just work”. Only caveat at this time is that it will require the UPSERT feature in PostgreSQL 9.5 to actually be 100% atomic and cause no consistency errors. I just use an exception loop for now, so beware of race conditions.

And while this functionality is currently all based on MongoDB, I don’t have any plans on restricting the interface or underlying structure to the way MongoDB is. Like Rob found, it does provide a good template to get an API going and it’s widely known so its basic functionality isn’t so foreign.

The create_document() function can be thought of like the create command in MongoDB. The tablename given is your collection. All functions return a set, so you can either call them with just SELECT or you can do SELECT * FROM to get more formatted output.

keith=# select create_document('public.mycollection');
    create_document    
-----------------------
 (mycollection,public)
(1 row)

Time: 99.392 ms
keith=# drop table mycollection;
DROP TABLE
Time: 16.149 ms
keith=# select * from create_document('public.mycollection');
  tablename   | schemaname 
--------------+------------
 mycollection | public
(1 row)

Time: 12.085 ms

keith=# \d public.mycollection
                        Table "public.mycollection"
   Column   |           Type           |             Modifiers              
------------+--------------------------+------------------------------------
 id         | uuid                     | not null default gen_random_uuid()
 body       | jsonb                    | not null
 search     | tsvector                 | 
 created_at | timestamp with time zone | not null default now()
 updated_at | timestamp with time zone | not null default now()
Indexes:
    "mycollection_pkey" PRIMARY KEY, btree (id)
    "mycollection_body_idx" gin (body jsonb_path_ops)
    "mycollection_search_idx" gin (search)
Triggers:
    mycollection_trig BEFORE INSERT OR UPDATE OF body ON mycollection FOR EACH ROW EXECUTE PROCEDURE update_search()

The save_document() function is the main workhorse, just as the save command is in Mongo.

keith=# drop table mycollection;
DROP TABLE
Time: 5.846 ms

keith=# select save_document('public.mycollection', '{"FirstName": "Keith", "LastName":"Fiske", "Company":"OmniTI"}');
                                                 save_document                                                  
----------------------------------------------------------------------------------------------------------------
 {"id": "3225d06c-1f49-44e8-b32f-a29234a7a5fb", "Company": "OmniTI", "LastName": "Fiske", "FirstName": "Keith"}
(1 row)

Time: 29.499 ms
keith=# select * from mycollection ;
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------
id         | 3225d06c-1f49-44e8-b32f-a29234a7a5fb
body       | {"id": "3225d06c-1f49-44e8-b32f-a29234a7a5fb", "Company": "OmniTI", "LastName": "Fiske", "FirstName": "Keith"}
search     | 'fisk':2 'keith':3 'omniti':1
created_at | 2015-10-29 19:00:43.413318-04
updated_at | 2015-10-29 19:00:43.413318-04

Time: 0.880 ms

You can see I dropped the table so it didn’t exist anymore. Just pass the tablename anyway, and it will create the table if it’s not already there. You can see it also added the primary key id value into the document itself. These are always kept in sync so you can then update your document later. If you pass an id value that does not exist, this will insert it as a new row. Keep in mind the id value here is the uuid data type, so it must meet certain criteria and can’t just be any random number/string. So it’s better to just let it generate it on its own if it’s a new document. This function is where the UPSERT is really needed.

keith=# select save_document('public.mycollection', '{"id":"3225d06c-1f49-44e8-b32f-a29234a7a5fb", "FirstName": "Keith", "LastName":"Fiske", "Company":"OmniTI", "Job":"DBA"}');
                                                        save_document                                                         
------------------------------------------------------------------------------------------------------------------------------
 {"id": "3225d06c-1f49-44e8-b32f-a29234a7a5fb", "Job": "DBA", "Company": "OmniTI", "LastName": "Fiske", "FirstName": "Keith"}
(1 row)

Time: 5.129 ms
keith=# select * from mycollection ;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------
id         | 3225d06c-1f49-44e8-b32f-a29234a7a5fb
body       | {"id": "3225d06c-1f49-44e8-b32f-a29234a7a5fb", "Job": "DBA", "Company": "OmniTI", "LastName": "Fiske", "FirstName": "Keith"}
search     | 'dba':1 'fisk':3 'keith':4 'omniti':2
created_at | 2015-10-29 19:00:43.413318-04
updated_at | 2015-10-29 19:00:43.413318-04

Time: 1.091 ms

Here I inserted another row and use the find_document() function to look for a document with a matching json key/value pair.

keith=# select save_document('public.mycollection', '{"FirstName": "Robert", "LastName":"Treat", "Company":"OmniTI", "Job":"CEO"}');                                                         save_document                                                         
-------------------------------------------------------------------------------------------------------------------------------
 {"id": "ac7b12fe-0627-48e0-85fc-ef0e3c19a813", "Job": "CEO", "Company": "OmniTI", "LastName": "Treat", "FirstName": "Robert"}
(1 row)

Time: 5.767 ms

keith=# select find_document('public.mycollection', '{"Company":"OmniTI"}');
                                                         find_document                                                         
-------------------------------------------------------------------------------------------------------------------------------
 {"id": "3225d06c-1f49-44e8-b32f-a29234a7a5fb", "Job": "DBA", "Company": "OmniTI", "LastName": "Fiske", "FirstName": "Keith"}
 {"id": "ac7b12fe-0627-48e0-85fc-ef0e3c19a813", "Job": "CEO", "Company": "OmniTI", "LastName": "Treat", "FirstName": "Robert"}
(2 rows)

Time: 5.061 ms

By default the result is sorted by id. There are parameters to find to sort by different keys and order. There is also a search_document() function that tries to make use of full-text search. I’ve not really done much with that before, and it seems pretty limited to me right now. It’s only populating the tsvector search column with data based on the document values. I hope to learn more about this and make it more effective. A trigger keeps this up to date after every insert/update. Both these methods take advantage of GIN indexing, so finding document data via either method should be extremely fast and efficient.

So two different PostgreSQL projects out there taking advantage of others building off their ideas. Some other interesting news following this same theme is Greenplum has open sourced their PostgreSQL fork and looks to try to contribute some of its features back to the core system. Hopefully the potential patent issues can be resolved and we’ll see some great new features in future versions of Postgres.

So thank you to Rob for releasing your code under the BSD license and letting me do this!

PG Partition Manager has been the most popular project I’ve ever done a significant amount of work on and I really appreciate everyone’s feedback for the roughly 2 years it’s been out there. I’ve got plenty more ideas for development and features and look forward to being able to move forward on them with this new major version released.

PostgreSQL 9.3 introduced the ability for user created, programmable background workers (BGW). 9.4 then introduced the ability to dynamically start & stop these with an already running cluster. The first thing that popped into my mind when I heard about this was hopefully having some sort of built-in scheduling system. There still hasn’t been a generalized version of anything like this, so in the mean time I studied the worker_spi contrib module. This is a very simple BGW example with a basic scheduler that runs a process with a configurable interval. This is basically all pg_partman needs for partition maintenance, and what required an external scheduler like cron before.

 
pg_partman_bgw.dbname
    * Required. The database(s) that `run_maintenance()` will run on. If more than one, use a comma separated list. If not set, BGW will do nothing.

pg_partman_bgw.interval
    * Number of seconds between calls to `run_maintenance()`. Default is 3600 (1 hour).
    * See further documenation on suggested values for this based on partition types & intervals used.

pg_partman_bgw.role
    * The role that `run_maintenance()` will run as. Default is "postgres". Only a single role name is allowed.

pg_partman_bgw.analyze
    * Same purpose as the p_analyze argument to `run_maintenance()`. Set to 'on' for TRUE. Set to 'off' for FALSE. Default is 'on'.

pg_partman_bgw.jobmon
     Same purpose as the p_jobmon argument to `run_maintenance()`. Set to 'on' for TRUE. Set to 'off' for FALSE. Default is 'on'.

The above are the new postgresql.conf options that pg_partman v2.0.0 can use to control the background worker it now comes with. These options can be changed at any time with a simple reload, but to start the BGW requires a restart of the database. This is my first venture into writing C, so it’s still very simplistic, and essentially does nothing different than an external scheduler would do. But now you no longer need that external scheduler if you don’t require calling run_maintenance() on any individual partition sets!

The BGW is completely optional and the Makefile has an option to not compile it if you’d just like to stick with the plain, plpgsql version of the extension.

Another major change with v2 is the contents of the partitioning trigger function. If you look back at my first post about pg_partman, you’ll see me mention there are two possible methods to writing the trigger: static & dynamic. You can read that post for more details. All you need to know now is that there are no longer distinct static & dynamic partitioning types. The different trigger methods have been combined into one trigger (thanks to a suggestion from Andrew Dunstan). An example below from the daily partitioning unit test:

keith=# \d+ partman_test.time_taptest_table
                              Table "partman_test.time_taptest_table"
 Column |           Type           |       Modifiers        | Storage  | Stats target | Description 
--------+--------------------------+------------------------+----------+--------------+-------------
 col1   | integer                  | not null               | plain    |              | 
 col2   | text                     |                        | extended |              | 
 col3   | timestamp with time zone | not null default now() | plain    |              | 
Indexes:
    "time_taptest_table_pkey" PRIMARY KEY, btree (col1)
Triggers:
    time_taptest_table_part_trig BEFORE INSERT ON partman_test.time_taptest_table FOR EACH ROW EXECUTE PROCEDURE partman_test.time_taptest_table_part_trig_func()
Child tables: partman_test.time_taptest_table_p2015_06_03,
              partman_test.time_taptest_table_p2015_06_04,
              partman_test.time_taptest_table_p2015_06_05,
              partman_test.time_taptest_table_p2015_06_06,
              partman_test.time_taptest_table_p2015_06_07,
              partman_test.time_taptest_table_p2015_06_08,
              partman_test.time_taptest_table_p2015_06_09,
              partman_test.time_taptest_table_p2015_06_10,
              partman_test.time_taptest_table_p2015_06_11
Has OIDs: yes

keith=# \sf partman_test.time_taptest_table_part_trig_func 
CREATE OR REPLACE FUNCTION partman_test.time_taptest_table_part_trig_func()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
            DECLARE
            v_count                 int;
            v_partition_name        text;
            v_partition_timestamp   timestamptz;
        BEGIN 
        IF TG_OP = 'INSERT' THEN 
            v_partition_timestamp := date_trunc('day', NEW.col3);
            IF NEW.col3 >= '2015-06-07 00:00:00-04' AND NEW.col3 < '2015-06-08 00:00:00-04' THEN 
                INSERT INTO partman_test.time_taptest_table_p2015_06_07 VALUES (NEW.*); 
            ELSIF NEW.col3 >= '2015-06-06 00:00:00-04' AND NEW.col3 < '2015-06-07 00:00:00-04' THEN 
                INSERT INTO partman_test.time_taptest_table_p2015_06_06 VALUES (NEW.*); 
            ELSIF NEW.col3 >= '2015-06-08 00:00:00-04' AND NEW.col3 < '2015-06-09 00:00:00-04' THEN 
                INSERT INTO partman_test.time_taptest_table_p2015_06_08 VALUES (NEW.*);
            ELSIF NEW.col3 >= '2015-06-05 00:00:00-04' AND NEW.col3 < '2015-06-06 00:00:00-04' THEN 
                INSERT INTO partman_test.time_taptest_table_p2015_06_05 VALUES (NEW.*); 
            ELSIF NEW.col3 >= '2015-06-09 00:00:00-04' AND NEW.col3 < '2015-06-10 00:00:00-04' THEN 
                INSERT INTO partman_test.time_taptest_table_p2015_06_09 VALUES (NEW.*);
            ELSIF NEW.col3 >= '2015-06-04 00:00:00-04' AND NEW.col3 < '2015-06-05 00:00:00-04' THEN 
                INSERT INTO partman_test.time_taptest_table_p2015_06_04 VALUES (NEW.*); 
            ELSIF NEW.col3 >= '2015-06-10 00:00:00-04' AND NEW.col3 < '2015-06-11 00:00:00-04' THEN 
                INSERT INTO partman_test.time_taptest_table_p2015_06_10 VALUES (NEW.*);
            ELSIF NEW.col3 >= '2015-06-03 00:00:00-04' AND NEW.col3 < '2015-06-04 00:00:00-04' THEN 
                INSERT INTO partman_test.time_taptest_table_p2015_06_03 VALUES (NEW.*); 
            ELSIF NEW.col3 >= '2015-06-11 00:00:00-04' AND NEW.col3 < '2015-06-12 00:00:00-04' THEN 
                INSERT INTO partman_test.time_taptest_table_p2015_06_11 VALUES (NEW.*);
            ELSE
                v_partition_name := partman.check_name_length('time_taptest_table', 'partman_test', to_char(v_partition_timestamp, 'YYYY_MM_DD'), TRUE);
                SELECT count(*) INTO v_count FROM pg_tables WHERE schemaname ||'.'|| tablename = v_partition_name;
                IF v_count > 0 THEN 
                    EXECUTE 'INSERT INTO '||v_partition_name||' VALUES($1.*)' USING NEW;
                ELSE
                    RETURN NEW;
                END IF;
            END IF;
        END IF; 
        RETURN NULL; 
        END $function$

You can see that the trigger function starts out like the static method, explicitly giving the SQL statements for inserting into each child table that it handles. But then the final condition falls back to using the dynamic method to first see if the relevant child table exists and then inserting it there (otherwise it goes to the parent). So this hybrid trigger now allows the performance benefit of the static method and the flexibility of the dynamic method, only losing performance when you try and insert data outside of the premake range. The time-custom type still exists and still uses the lookup-table method from v1, so nothing has changed there. This new hybrid function is only available for the standard time intervals and serial partitioning.

For those updating from the v1.x.x version of pg_partman, this new trigger function will be put in place automatically the next time a new child table is created for a partition set. If you’d like to convert all your trigger functions immediately, see the v2.0.0 extension update file for some simple plpgsql that can do that for you. I couldn’t make updating the trigger functions part of the extension update itself because doing so would then make all the trigger functions part of the extension (that was a fun accident to run into during testing).

There have been some other fixes to the extension as well, but these are the biggest and most relevant to most users. Please see the CHANGELOG for more info.

I’ve also taken this time to make pg_partman only compatible with PostgreSQL 9.4+. It uses the dynamic BGW feature, so I couldn’t go with 9.3. And while you can compile the extension without the BGW feature, I did include some new code that is only compatibly with 9.2+. I know this will inconvenience some users, but with the way extension versions are managed within the database, I couldn’t see any easy way to maintain both a v1 & v2 branch. For every new v1 release I’d make, I’d also have to maintain a separate update file to get that version to v2 as well as having to go back and update every previous v1 -> v2 update to include any relevant changes for people sticking with v1 that want to change to v2 in the future. I just don’t have that kind of time for extension management and I could see it very easily leading to a mess if I didn’t find a way to keep all that versioning straight. I will still fix bugs in v1, but all new feature development will be on v2 only. Drawing the line at 9.4 also gives me the freedom to use the latest and greatest features in Postgres and not have to compromise for backward compatibility, at least for the near future.

Upgrading to 9.4 is definitely worth the time to do if you’d like to keep using this extension easily and get the new features it will have in the future. If you’re 2 or 3 major versions behind (or more) and are curious why upgrading is worth the time, I’ll be giving an updated version of my Don’t Forget the Elephant talk at this year’s PGOpen in Dallas, Tx.

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!

This has been something I’ve had in the works since I started on this extension, but it took a while to settle on the best method to use. The pre-determined time ranges for PG Partition Manager were done because it was easy and, as it turns out, provide a very noticeable performance improvement when used with the static partitioning method. Note that custom time partitioning, while the most flexible, is the least performant of the 3 methods now supported with this extension.

The biggest roadblock to implementing this was trying to find an answer to the following question:

If an interval can be any value, how do you determine the partition that the currently inserted timestamp goes into?

Seems a simple enough question, but when you try and implement it, you realize the impact solving it can have on the potential performance of every insert to the table. My solution in the end (which I was trying to avoid) was to create a lookup table. Thankfully the new range type added in PostgreSQL 9.2 made this a whole lot easier than it could’ve been, but it does mean this feature is only supported on the latest versions. If anyone has an efficient solution as an algorithm that can be implemented solely in the partitioning function, I’d be extremely grateful for a contribution.

Until then, pg_partman 1.6.0 hopefully now supports any interval value from 1 second up to the minimum and maximum values for the timestamp type that PostgreSQL supports. Less than that is potentially possible, but even a partitioning interval of 1 second seemed ridiculous enough to me, so I doubt I’m going to pursue it. And I say hopefully because it’s impossible for me to make unit tests for every possible interval there is. I test for intervals less than 1 minute (due to the new child table naming pattern) and greater than 100 years (due to the seemingly non-standard century partition boundaries (2001, 2101, 2201, etc)) so hopefully that will be enough for now. Would greatly appreciate feedback.

This version also bring the ability to manually designate your starting partition at creation. Be aware that if you’re using static partitioning, the trigger function on the parent may not cover all the partitions this could create. The partitioning functions (partition_data_time() & partition_data_id()) will create these tables automatically when you migrate data out of the parent. But if you’re starting from scratch, I thought this might make things easier so if you’re loading data externally, you can have the tables already set to have data loaded directly into them. Below is an example of using both the new “time-custom” type and setting a custom start partition (by default it would’ve only created 4 partitions behind CURRENT_DATE)

keith=# select CURRENT_DATE;
    date    
------------
 2014-02-12

keith=# create table public.testing_custom_time(col1 serial, col2 text, col3 timestamptz not null);
CREATE TABLE

keith=# select partman.create_parent('public.testing_custom_time', 'col3', 'time-custom', '4 days', p_start_partition := '2014-01-01');
 create_parent 
---------------

(1 row)

keith=# \d+ public.testing_custom_time
                                                       Table "public.testing_custom_time"
 Column |           Type           |                             Modifiers                              | Storage  | Stats target | Description 
--------+--------------------------+--------------------------------------------------------------------+----------+--------------+-------------
 col1   | integer                  | not null default nextval('testing_custom_time_col1_seq'::regclass) | plain    |              | 
 col2   | text                     |                                                                    | extended |              | 
 col3   | timestamp with time zone | not null                                                           | plain    |              | 
Triggers:
    testing_custom_time_part_trig BEFORE INSERT ON testing_custom_time FOR EACH ROW EXECUTE PROCEDURE testing_custom_time_part_trig_func()
Child tables: testing_custom_time_p2014_01_01,
              testing_custom_time_p2014_01_05,
              testing_custom_time_p2014_01_09,
              testing_custom_time_p2014_01_13,
              testing_custom_time_p2014_01_17,
              testing_custom_time_p2014_01_21,
              testing_custom_time_p2014_01_25,
              testing_custom_time_p2014_01_29,
              testing_custom_time_p2014_02_02,
              testing_custom_time_p2014_02_06,
              testing_custom_time_p2014_02_10,
              testing_custom_time_p2014_02_14,
              testing_custom_time_p2014_02_18,
              testing_custom_time_p2014_02_22,
              testing_custom_time_p2014_02_26
Has OIDs: no

I’ve gotten a lot of positive responses (and bug fixes!) from the community so far and am glad that so many people are finding this a useful tool. It’s nice to see that the effort has been worth it.

When I say version specific here, I’m speaking of the PostgreSQL version (9.1, 9,2, etc), not the extension version. An upcoming update to PG Partition Manager takes advantage of the range data type, which is a 9.2 feature. But I also wanted to keep backward compatibility with 9.1 by excluding this new feature.

The more important thing is to get the extension installed in the first place, so the Makefile was where I started.

EXTENSION = pg_partman
EXTVERSION = $(shell grep default_version $(EXTENSION).control | \
               sed -e "s/default_version[[:space:]]*=[[:space:]]*'\([^']*\)'/\1/")

DATA = $(filter-out $(wildcard updates/*--*.sql),$(wildcard sql/*.sql))
DOCS = $(wildcard doc/*.md)
SCRIPTS = bin/dump_partition.py bin/partition_data.py bin/reapply_indexes.py bin/undo_partition.py
PG_CONFIG = pg_config
PG91 = $(shell $(PG_CONFIG) --version | egrep " 8\.| 9\.0" > /dev/null && echo no || echo yes)
PG92 = $(shell $(PG_CONFIG) --version | egrep " 8\.| 9\.0| 9\.1" > /dev/null && echo no || echo yes)

ifeq ($(PG91),yes)
all: sql/$(EXTENSION)--$(EXTVERSION).sql

ifeq ($(PG92),yes)
sql/$(EXTENSION)--$(EXTVERSION).sql: sql/types/*.sql sql/constraints/*.sql sql/tables/*.sql sql/functions/*.sql sql/92/tables/*.sql
	cat $^ > $@
else
sql/$(EXTENSION)--$(EXTVERSION).sql: sql/types/*.sql sql/constraints/*.sql sql/tables/*.sql sql/functions/*.sql
	cat $^ > $@
endif

DATA = $(wildcard updates/*--*.sql) sql/$(EXTENSION)--$(EXTVERSION).sql
EXTRA_CLEAN = sql/$(EXTENSION)--$(EXTVERSION).sql
endif

PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Keep in mind, I know very little about Makefiles in the first place, so my solution is using just what I already knew how to do in the original Makefile. There may be a better way, and if there is, I’d love feedback. I just expanded on the already existing lines that check for a minimal version of 9.1 to even allow the extension to install. And I organized my code folders to have one specifically for 9.2+ features (sql/92/tables/). After that it’s just another check to see if the PG92 variable is true, and if so, grab the code from that additional folder.

Now the next challenge is checking the version when installing an update to an already existing installation. First, we need a means of checking if the current version is new enough. I made up the below function for inclusion in my extension since I’ll actually be doing version checking elsewhere as well.

/*
 * Check PostgreSQL version number. Parameter must be full 3 point version.
 * Returns true if current version is greater than or equal to the parameter given.
 */
CREATE FUNCTION check_version(p_check_version text) RETURNS boolean
    LANGUAGE plpgsql STABLE
    AS $$
DECLARE

v_check_version     text[];
v_current_version   text[] := string_to_array(current_setting('server_version'), '.');

BEGIN

v_check_version := string_to_array(p_check_version, '.');

IF v_current_version[1]::int > v_check_version[1]::int THEN
    RETURN true;
END IF;
IF v_current_version[1]::int = v_check_version[1]::int THEN
    IF v_current_version[2]::int > v_check_version[2]::int THEN
        RETURN true;
    END IF;
    IF v_current_version[2]::int = v_check_version[2]::int THEN
        IF v_current_version[3]::int >= v_check_version[3]::int THEN
            RETURN true;
        END IF; -- 0.0.x
    END IF; -- 0.x.0
END IF; -- x.0.0

RETURN false;

END
$$;

Now we can just take advantage of the DO block syntax to allow script code in a plain SQL file.

/********* 9.2+ stuff ********/
DO $$
BEGIN
IF @extschema@.check_version('9.2.0') THEN
    CREATE TABLE custom_time_partitions (
        parent_table text NOT NULL
        , child_table text NOT NULL
        , partition_range tstzrange NOT NULL
        , PRIMARY KEY (parent_table, child_table));
    CREATE INDEX custom_time_partitions_partition_range_idx ON custom_time_partitions USING gist (partition_range);
END IF;
END
$$;
/********* end 9.2+ stuff ********/

The full implementation of this will be available in the 1.6.0 update for pg_partman that I’ll be releasing in the near future. It contains a feature I’ve wanted to get done for a while now: custom time intervals!