{"id":12487,"date":"2019-06-18T09:19:29","date_gmt":"2019-06-18T07:19:29","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/"},"modified":"2019-06-18T09:19:29","modified_gmt":"2019-06-18T07:19:29","slug":"postgresql-partitioning-8-sub-partitioning","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/","title":{"rendered":"PostgreSQL partitioning (8): Sub-partitioning"},"content":{"rendered":"

We are slowly coming to the end of this little series about partitioning in PostgreSQL. In the last post we had a look at indexing and constraints and today we will have a look at sub partitioning. Sub partitioning means you go one step further and partition the partitions as well. Although it is not required to read all the posts of this series to follow this one: If you want, here they are:<\/p>\n

    \n
  1. PostgreSQL partitioning (1): Preparing the data set<\/a><\/li>\n
  2. PostgreSQL partitioning (2): Range partitioning<\/a><\/li>\n
  3. PostgreSQL partitioning (3): List partitioning<\/a><\/li>\n
  4. PostgreSQL partitioning (4) : Hash partitioning<\/a><\/li>\n
  5. PostgreSQL partitioning (5): Partition pruning<\/a><\/li>\n
  6. PostgreSQL partitioning (6): Attaching and detaching partitions<\/a><\/li>\n
  7. PostgreSQL partitioning (7): Indexing and constraints<\/a><\/li>\n<\/ol>\n

    <\/p>\n

    Coming back to our range partitioned table this is how it looks like currently:<\/p>\n

    \npostgres=# d+ traffic_violations_p\n                                      Partitioned table \"public.traffic_violations_p\"\n         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description \n-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------\n seqid                   | text                   |           |          |         | extended |              | \n date_of_stop            | date                   |           |          |         | plain    |              | \n time_of_stop            | time without time zone |           |          |         | plain    |              | \n agency                  | text                   |           |          |         | extended |              | \n subagency               | text                   |           |          |         | extended |              | \n description             | text                   |           |          |         | extended |              | \n location                | text                   |           |          |         | extended |              | \n latitude                | numeric                |           |          |         | main     |              | \n longitude               | numeric                |           |          |         | main     |              | \n accident                | text                   |           |          |         | extended |              | \n belts                   | boolean                |           |          |         | plain    |              | \n personal_injury         | boolean                |           |          |         | plain    |              | \n property_damage         | boolean                |           |          |         | plain    |              | \n fatal                   | boolean                |           |          |         | plain    |              | \n commercial_license      | boolean                |           |          |         | plain    |              | \n hazmat                  | boolean                |           |          |         | plain    |              | \n commercial_vehicle      | boolean                |           |          |         | plain    |              | \n alcohol                 | boolean                |           |          |         | plain    |              | \n workzone                | boolean                |           |          |         | plain    |              | \n state                   | text                   |           |          |         | extended |              | \n vehicletype             | text                   |           |          |         | extended |              | \n year                    | smallint               |           |          |         | plain    |              | \n make                    | text                   |           |          |         | extended |              | \n model                   | text                   |           |          |         | extended |              | \n color                   | text                   |           |          |         | extended |              | \n violation_type          | text                   |           |          |         | extended |              | \n charge                  | text                   |           |          |         | extended |              | \n article                 | text                   |           |          |         | extended |              | \n contributed_to_accident | boolean                |           |          |         | plain    |              | \n race                    | text                   |           |          |         | extended |              | \n gender                  | text                   |           |          |         | extended |              | \n driver_city             | text                   |           |          |         | extended |              | \n driver_state            | text                   |           |          |         | extended |              | \n dl_state                | text                   |           |          |         | extended |              | \n arrest_type             | text                   |           |          |         | extended |              | \n geolocation             | point                  |           |          |         | plain    |              | \n council_districts       | smallint               |           |          |         | plain    |              | \n councils                | smallint               |           |          |         | plain    |              | \n communities             | smallint               |           |          |         | plain    |              | \n zip_codes               | smallint               |           |          |         | plain    |              | \n municipalities          | smallint               |           |          |         | plain    |              | \nPartition key: RANGE (date_of_stop)\nPartitions: traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),\n            traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),\n            traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),\n            traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),\n            traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),\n            traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),\n            traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),\n            traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),\n            traffic_violations_p_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'),\n            traffic_violations_p_default DEFAULT\n<\/pre>\n
    Lets assume that you expect that traffic violations will grow exponentially in 2022 because more and more cars will be on the road and when there will be more cars there will be more traffic violations. To be prepared for that you do not only want to partition by year but also by month. In other words: Add a new partition for 2022 but sub partition that by month. First of all you need a new partition for 2022 that itself is partitioned as well:<\/p>\n
    \ncreate table traffic_violations_p_2022\npartition of traffic_violations_p\nfor values from ('2022-01-01') to ('2023-01-01') partition by range(date_of_stop);\n<\/pre>\n
    Now we can add partitions to the just created partitioned partition:<\/p>\n
    \ncreate table traffic_violations_p_2022_jan\npartition of traffic_violations_p_2022\nfor values from ('2022-01-01') to ('2022-02-01');\n\ncreate table traffic_violations_p_2022_feb\npartition of traffic_violations_p_2022\nfor values from ('2022-02-01') to ('2022-03-01');\n\ncreate table traffic_violations_p_2022_mar\npartition of traffic_violations_p_2022\nfor values from ('2022-03-01') to ('2022-04-01');\n\ncreate table traffic_violations_p_2022_apr\npartition of traffic_violations_p_2022\nfor values from ('2022-04-01') to ('2022-05-01');\n\ncreate table traffic_violations_p_2022_may\npartition of traffic_violations_p_2022\nfor values from ('2022-05-01') to ('2022-06-01');\n\ncreate table traffic_violations_p_2022_jun\npartition of traffic_violations_p_2022\nfor values from ('2022-06-01') to ('2022-07-01');\n\ncreate table traffic_violations_p_2022_jul\npartition of traffic_violations_p_2022\nfor values from ('2022-07-01') to ('2022-08-01');\n\ncreate table traffic_violations_p_2022_aug\npartition of traffic_violations_p_2022\nfor values from ('2022-08-01') to ('2022-09-01');\n\ncreate table traffic_violations_p_2022_sep\npartition of traffic_violations_p_2022\nfor values from ('2022-09-01') to ('2022-10-01');\n\ncreate table traffic_violations_p_2022_oct\npartition of traffic_violations_p_2022\nfor values from ('2022-10-01') to ('2022-11-01');\n\ncreate table traffic_violations_p_2022_nov\npartition of traffic_violations_p_2022\nfor values from ('2022-11-01') to ('2022-12-01');\n\ncreate table traffic_violations_p_2022_dec\npartition of traffic_violations_p_2022\nfor values from ('2022-12-01') to ('2023-01-01');\n<\/pre>\n
    Looking at psql’s output when we describe the partitioned table not very much changed, just the keyword “PARTITIONED” is showing up beside our new partition for 2022:<\/p>\n
    \npostgres=# d+ traffic_violations_p\n                                      Partitioned table \"public.traffic_violations_p\"\n         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description \n-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------\n seqid                   | text                   |           |          |         | extended |              | \n date_of_stop            | date                   |           |          |         | plain    |              | \n time_of_stop            | time without time zone |           |          |         | plain    |              | \n agency                  | text                   |           |          |         | extended |              | \n subagency               | text                   |           |          |         | extended |              | \n description             | text                   |           |          |         | extended |              | \n location                | text                   |           |          |         | extended |              | \n latitude                | numeric                |           |          |         | main     |              | \n longitude               | numeric                |           |          |         | main     |              | \n accident                | text                   |           |          |         | extended |              | \n belts                   | boolean                |           |          |         | plain    |              | \n personal_injury         | boolean                |           |          |         | plain    |              | \n property_damage         | boolean                |           |          |         | plain    |              | \n fatal                   | boolean                |           |          |         | plain    |              | \n commercial_license      | boolean                |           |          |         | plain    |              | \n hazmat                  | boolean                |           |          |         | plain    |              | \n commercial_vehicle      | boolean                |           |          |         | plain    |              | \n alcohol                 | boolean                |           |          |         | plain    |              | \n workzone                | boolean                |           |          |         | plain    |              | \n state                   | text                   |           |          |         | extended |              | \n vehicletype             | text                   |           |          |         | extended |              | \n year                    | smallint               |           |          |         | plain    |              | \n make                    | text                   |           |          |         | extended |              | \n model                   | text                   |           |          |         | extended |              | \n color                   | text                   |           |          |         | extended |              | \n violation_type          | text                   |           |          |         | extended |              | \n charge                  | text                   |           |          |         | extended |              | \n article                 | text                   |           |          |         | extended |              | \n contributed_to_accident | boolean                |           |          |         | plain    |              | \n race                    | text                   |           |          |         | extended |              | \n gender                  | text                   |           |          |         | extended |              | \n driver_city             | text                   |           |          |         | extended |              | \n driver_state            | text                   |           |          |         | extended |              | \n dl_state                | text                   |           |          |         | extended |              | \n arrest_type             | text                   |           |          |         | extended |              | \n geolocation             | point                  |           |          |         | plain    |              | \n council_districts       | smallint               |           |          |         | plain    |              | \n councils                | smallint               |           |          |         | plain    |              | \n communities             | smallint               |           |          |         | plain    |              | \n zip_codes               | smallint               |           |          |         | plain    |              | \n municipalities          | smallint               |           |          |         | plain    |              | \nPartition key: RANGE (date_of_stop)\nPartitions: traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),\n            traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),\n            traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),\n            traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),\n            traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),\n            traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),\n            traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),\n            traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),\n            traffic_violations_p_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'),\n            traffic_violations_p_2022 FOR VALUES FROM ('2022-01-01') TO ('2023-01-01'), PARTITIONED,\n            traffic_violations_p_default DEFAULT\n<\/pre>\n
    The is where the new functions in PostgreSQL 12<\/a> become very handy:<\/p>\n
    \npostgres=# select * from pg_partition_tree('traffic_violations_p');\n             relid             |        parentrelid        | isleaf | level \n-------------------------------+---------------------------+--------+-------\n traffic_violations_p          |                           | f      |     0\n traffic_violations_p_default  | traffic_violations_p      | t      |     1\n traffic_violations_p_2013     | traffic_violations_p      | t      |     1\n traffic_violations_p_2014     | traffic_violations_p      | t      |     1\n traffic_violations_p_2015     | traffic_violations_p      | t      |     1\n traffic_violations_p_2016     | traffic_violations_p      | t      |     1\n traffic_violations_p_2017     | traffic_violations_p      | t      |     1\n traffic_violations_p_2018     | traffic_violations_p      | t      |     1\n traffic_violations_p_2019     | traffic_violations_p      | t      |     1\n traffic_violations_p_2020     | traffic_violations_p      | t      |     1\n traffic_violations_p_2021     | traffic_violations_p      | t      |     1\n traffic_violations_p_2022     | traffic_violations_p      | f      |     1\n traffic_violations_p_2022_jan | traffic_violations_p_2022 | t      |     2\n traffic_violations_p_2022_feb | traffic_violations_p_2022 | t      |     2\n traffic_violations_p_2022_mar | traffic_violations_p_2022 | t      |     2\n traffic_violations_p_2022_apr | traffic_violations_p_2022 | t      |     2\n traffic_violations_p_2022_may | traffic_violations_p_2022 | t      |     2\n traffic_violations_p_2022_jun | traffic_violations_p_2022 | t      |     2\n traffic_violations_p_2022_jul | traffic_violations_p_2022 | t      |     2\n traffic_violations_p_2022_aug | traffic_violations_p_2022 | t      |     2\n traffic_violations_p_2022_sep | traffic_violations_p_2022 | t      |     2\n traffic_violations_p_2022_oct | traffic_violations_p_2022 | t      |     2\n traffic_violations_p_2022_nov | traffic_violations_p_2022 | t      |     2\n traffic_violations_p_2022_dec | traffic_violations_p_2022 | t      |     2\n<\/pre>\n
    To verify if data is routed correctly to the sub partitions let’s add some data for 2022:<\/p>\n
    \ninsert into traffic_violations_p (date_of_stop)\n       select * from generate_series ( date('01-01-2022')\n                                     , date('12-31-2022')\n                                     , interval '1 day' );\n<\/pre>\n
    If we did the partitioning correctly we should see data in the new partitions:<\/p>\n
    \npostgres=# select count(*) from traffic_violations_p_2022_nov;\n count \n-------\n    30\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_2022_dec;\n count \n-------\n    31\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_2022_feb;\n count \n-------\n    28\n(1 row)\n<\/pre>\n
    Here we go. Of course you could go even further and sub-partition the monthly partitions further by day or week. You can also partition by list and then sub-partition the  list partitions by range. Or partition by range and then sub-partition by list, e.g.:<\/p>\n
    \npostgres=# create table traffic_violations_p_list_dummy partition of traffic_violations_p_list for values in ('dummy') partition by range (date_of_stop);\nCREATE TABLE\npostgres=# create table traffic_violations_p_list_dummy_2019 partition of traffic_violations_p_list_dummy for values from ('2022-01-01') to ('2023-01-01');\nCREATE TABLE\npostgres=# insert into traffic_violations_p_list (seqid, violation_type , date_of_stop) values (-1,'dummy',date('2022-12-01'));\nINSERT 0 1\npostgres=# select date_of_stop,violation_type from traffic_violations_p_list_dummy_2019;\n date_of_stop | violation_type \n--------------+----------------\n 2022-12-01   | dummy\n(1 row)\n<\/pre>\n
    That’s it for sub-partitioning. In the final post we will look at some corner cases with partitioning in PostgreSQL.<\/p>\n","protected":false},"excerpt":{"rendered":"
    We are slowly coming to the end of this little series about partitioning in PostgreSQL. In the last post we had a look at indexing and constraints and today we will have a look at sub partitioning. Sub partitioning means you go one step further and partition the partitions as well. Although it is not […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-12487","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nPostgreSQL partitioning (8): Sub-partitioning - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL partitioning (8): Sub-partitioning\" \/>\n<meta property=\"og:description\" content=\"We are slowly coming to the end of this little series about partitioning in PostgreSQL. In the last post we had a look at indexing and constraints and today we will have a look at sub partitioning. Sub partitioning means you go one step further and partition the partitions as well. Although it is not […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-06-18T07:19:29+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-8-sub-partitioning\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-8-sub-partitioning\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL partitioning (8): Sub-partitioning\",\"datePublished\":\"2019-06-18T07:19:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-8-sub-partitioning\\\/\"},\"wordCount\":351,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-8-sub-partitioning\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-8-sub-partitioning\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-8-sub-partitioning\\\/\",\"name\":\"PostgreSQL partitioning (8): Sub-partitioning - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2019-06-18T07:19:29+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-8-sub-partitioning\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-8-sub-partitioning\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-8-sub-partitioning\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL partitioning (8): Sub-partitioning\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL partitioning (8): Sub-partitioning - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL partitioning (8): Sub-partitioning","og_description":"We are slowly coming to the end of this little series about partitioning in PostgreSQL. In the last post we had a look at indexing and constraints and today we will have a look at sub partitioning. Sub partitioning means you go one step further and partition the partitions as well. Although it is not […]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/","og_site_name":"dbi Blog","article_published_time":"2019-06-18T07:19:29+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL partitioning (8): Sub-partitioning","datePublished":"2019-06-18T07:19:29+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/"},"wordCount":351,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/","name":"PostgreSQL partitioning (8): Sub-partitioning - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-06-18T07:19:29+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-8-sub-partitioning\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL partitioning (8): Sub-partitioning"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12487","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=12487"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12487\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12487"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12487"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12487"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12487"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}