{"id":12481,"date":"2019-06-03T06:00:01","date_gmt":"2019-06-03T04:00:01","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-2-range-partitioning\/"},"modified":"2019-06-03T06:00:01","modified_gmt":"2019-06-03T04:00:01","slug":"postgresql-partitioning-2-range-partitioning","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-2-range-partitioning\/","title":{"rendered":"PostgreSQL partitioning (2): Range partitioning"},"content":{"rendered":"

Now that the data set is ready<\/a> we will look at the first partitioning strategy: Range partitioning. Usually range partitioning is used to partition a table by days, months or years although you can partition by other data types as well. Time\/date based range partitioning is probably the most common use case so we will partition our traffic violation data by year and later on sub partition that month.<\/p>\n

<\/p>\n

Going back to the materialized we created in the first post, this is the structure:<\/p>\n

\npostgres=# d mv_traffic_violations \n                 Materialized view \"public.mv_traffic_violations\"\n         Column          |          Type          | Collation | Nullable | Default \n-------------------------+------------------------+-----------+----------+---------\n seqid                   | text                   |           |          | \n date_of_stop            | date                   |           |          | \n time_of_stop            | time without time zone |           |          | \n agency                  | text                   |           |          | \n subagency               | text                   |           |          | \n description             | text                   |           |          | \n location                | text                   |           |          | \n latitude                | numeric                |           |          | \n longitude               | numeric                |           |          | \n accident                | text                   |           |          | \n belts                   | boolean                |           |          | \n personal_injury         | boolean                |           |          | \n property_damage         | boolean                |           |          | \n fatal                   | boolean                |           |          | \n commercial_license      | boolean                |           |          | \n hazmat                  | boolean                |           |          | \n commercial_vehicle      | boolean                |           |          | \n alcohol                 | boolean                |           |          | \n workzone                | boolean                |           |          | \n state                   | text                   |           |          | \n vehicletype             | text                   |           |          | \n year                    | smallint               |           |          | \n make                    | text                   |           |          | \n model                   | text                   |           |          | \n color                   | text                   |           |          | \n violation_type          | text                   |           |          | \n charge                  | text                   |           |          | \n article                 | text                   |           |          | \n contributed_to_accident | boolean                |           |          | \n race                    | text                   |           |          | \n gender                  | text                   |           |          | \n driver_city             | text                   |           |          | \n driver_state            | text                   |           |          | \n dl_state                | text                   |           |          | \n arrest_type             | text                   |           |          | \n geolocation             | point                  |           |          | \n council_districts       | smallint               |           |          | \n councils                | smallint               |           |          | \n communities             | smallint               |           |          | \n zip_codes               | smallint               |           |          | \n municipalities          | smallint               |           |          | \n<\/pre>\n
Lets assume our most common queries against the data set restrict the data for one or more years. Using partitioned tables we can range partition on the “date_of_stop” column and break down the large data set into smaller chunks per year. How would that work in PostgreSQL?<\/p>\n
Currently we have this min and max values for the “date_of_stop” column:<\/p>\n
\npostgres=# select min(date_of_stop), max(date_of_stop) from mv_traffic_violations;\n    min     |    max     \n------------+------------\n 2012-01-01 | 2019-06-01\n(1 row)\n<\/pre>\n
If we want to partition that by year we need at least 8 partitions (2012 to 2019) and maybe already add a partition for 2010 so we are on the safe side when a more recent data set brings data for next year. The first step is to create a partitioned table:<\/p>\n
\ncreate table traffic_violations_p\n( seqid text\n, date_of_stop date not null\n, time_of_stop time\n, agency text\n, subagency text\n, description text \n, location text\n, latitude numeric\n, longitude numeric\n, accident text\n, belts boolean\n, personal_injury boolean \n, property_damage boolean\n, fatal boolean\n, commercial_license boolean\n, hazmat boolean\n, commercial_vehicle boolean\n, alcohol boolean\n, workzone boolean\n, state text\n, vehicletype text \n, year smallint\n, make text\n, model text\n, color text\n, violation_type text \n, charge text\n, article text\n, contributed_to_accident boolean\n, race text\n, gender text\n, driver_city text\n, driver_state text\n, dl_state text\n, arrest_type text\n, geolocation point\n, council_districts smallint \n, councils smallint\n, communities smallint\n, zip_codes smallint\n, municipalities smallint)\npartition by range (date_of_stop);\n<\/pre>\n
Now we have a partitioned table with no partitions:<\/p>\n
\npostgres=# d traffic_violations_p\n                  Partitioned table \"public.traffic_violations_p\"\n         Column          |          Type          | Collation | Nullable | Default \n-------------------------+------------------------+-----------+----------+---------\n seqid                   | text                   |           |          | \n date_of_stop            | date                   |           | not null | \n time_of_stop            | time without time zone |           |          | \n agency                  | text                   |           |          | \n subagency               | text                   |           |          | \n description             | text                   |           |          | \n location                | text                   |           |          | \n latitude                | numeric                |           |          | \n longitude               | numeric                |           |          | \n accident                | text                   |           |          | \n belts                   | boolean                |           |          | \n personal_injury         | boolean                |           |          | \n property_damage         | boolean                |           |          | \n fatal                   | boolean                |           |          | \n commercial_license      | boolean                |           |          | \n hazmat                  | boolean                |           |          | \n commercial_vehicle      | boolean                |           |          | \n alcohol                 | boolean                |           |          | \n workzone                | boolean                |           |          | \n state                   | text                   |           |          | \n vehicletype             | text                   |           |          | \n year                    | smallint               |           |          | \n make                    | text                   |           |          | \n model                   | text                   |           |          | \n color                   | text                   |           |          | \n violation_type          | text                   |           |          | \n charge                  | text                   |           |          | \n article                 | text                   |           |          | \n contributed_to_accident | boolean                |           |          | \n race                    | text                   |           |          | \n gender                  | text                   |           |          | \n driver_city             | text                   |           |          | \n driver_state            | text                   |           |          | \n dl_state                | text                   |           |          | \n arrest_type             | text                   |           |          | \n geolocation             | point                  |           |          | \n council_districts       | smallint               |           |          | \n councils                | smallint               |           |          | \n communities             | smallint               |           |          | \n zip_codes               | smallint               |           |          | \n municipalities          | smallint               |           |          | \nPartition key: RANGE (date_of_stop)\nNumber of partitions: 0\n<\/pre>\n
If we try to insert data without any partitions this will obviously fail:<\/p>\n
\npostgres=# insert into traffic_violations_p (date_of_Stop) values ( now() );\npsql: ERROR:  no partition of relation \"traffic_violations_p\" found for row\nDETAIL:  Partition key of the failing row contains (date_of_stop) = (2019-05-28).\n<\/pre>\n
We need partitions and this is how you can add partitions to that partitioned table:<\/p>\n
\ncreate table traffic_violations_p_2012\npartition of traffic_violations_p\nfor values from ('2012-01-01') to ('2012-12-31');\n\ncreate table traffic_violations_p_2013\npartition of traffic_violations_p\nfor values from ('2013-01-01') to ('2013-12-31');\n\ncreate table traffic_violations_p_2014\npartition of traffic_violations_p\nfor values from ('2014-01-01') to ('2014-12-31');\n\ncreate table traffic_violations_p_2015\npartition of traffic_violations_p\nfor values from ('2015-01-01') to ('2015-12-31');\n\ncreate table traffic_violations_p_2016\npartition of traffic_violations_p\nfor values from ('2016-01-01') to ('2016-12-31');\n\ncreate table traffic_violations_p_2017\npartition of traffic_violations_p\nfor values from ('2017-01-01') to ('2017-12-31');\n\ncreate table traffic_violations_p_2018\npartition of traffic_violations_p\nfor values from ('2018-01-01') to ('2018-12-31');\n\ncreate table traffic_violations_p_2019\npartition of traffic_violations_p\nfor values from ('2019-01-01') to ('2019-12-31');\n\ncreate table traffic_violations_p_2020\npartition of traffic_violations_p\nfor values from ('2020-01-01') to ('2020-12-31');\n<\/pre>\n
We now have a partition for each year from 2012 to 2020:<\/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_2012 FOR VALUES FROM ('2012-01-01') TO ('2012-12-31'),\n            traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2013-12-31'),\n            traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2014-12-31'),\n            traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2015-12-31'),\n            traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2016-12-31'),\n            traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2017-12-31'),\n            traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2018-12-31'),\n            traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2019-12-31'),\n            traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2020-12-31')\n<\/pre>\n
Doing the same insert as before now succeeds because we have a partition where the row can go to:<\/p>\n
\npostgres=# insert into traffic_violations_p (date_of_Stop) values ( now() );\nINSERT 0 1\npostgres=# delete from traffic_violations_p;\nDELETE 1\npostgres=# \n<\/pre>\n
What still would fail is an insert with a date before 2012 or after 2020:<\/p>\n
\npostgres=# insert into traffic_violations_p (date_of_Stop) values ( now() + interval '2 years' );\npsql: ERROR:  no partition of relation \"traffic_violations_p\" found for row\nDETAIL:  Partition key of the failing row contains (date_of_stop) = (2021-05-28).\n<\/pre>\n
To avoid that we can create a “default” partition:<\/p>\n
\nCREATE TABLE traffic_violations_p_default\n    PARTITION OF traffic_violations_p DEFAULT;\n<\/pre>\n
A default partition will hold all the rows that do not match any of the existing partition definitions:<\/p>\n
\npostgres=# select (date_of_stop) from traffic_violations_p_default;\n date_of_stop \n--------------\n 2021-05-28\n(1 row)\npostgres=# delete from traffic_violations_p;\nDELETE 1\n<\/pre>\n
As our partitioned table setup is now complete we can load the data:<\/p>\n
\npostgres=# insert into traffic_violations_p select * from mv_traffic_violations;\nINSERT 0 1528078\n<\/pre>\n
All rows successfully loaded so we can check the counts for each partition:<\/p>\n
\npostgres=# select count(*) from traffic_violations_p_2012;\n count  \n--------\n 150989\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_2013;\n count  \n--------\n 189946\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_2014;\n count  \n--------\n 223248\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_2015;\n count  \n--------\n 234468\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_2016;\n count  \n--------\n 219227\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_2017;\n count  \n--------\n 198879\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_2018;\n count  \n--------\n 217911\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_2019;\n count \n-------\n 89823\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_2020;\n count \n-------\n     0\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_default;\n count \n-------\n  3588\n(1 row)\n<\/pre>\n
Why do we have so many rows in the default partition? We checked the min and max values and there should be no row before 2012 and after 2019. But:<\/p>\n
\npostgres=# select distinct date_of_stop from traffic_violations_p_default order by 1;\n date_of_stop \n--------------\n 2012-12-31\n 2013-12-31\n 2014-12-31\n 2015-12-31\n 2016-12-31\n 2017-12-31\n 2018-12-31\n(7 rows)\n<\/pre>\n
The issue it, that the upper bound is exclusive so we did not correctly setup the partitions. The correct way of doing it is:<\/p>\n
\ndrop table traffic_violations_p_2012, traffic_violations_p_2013, traffic_violations_p_2014, traffic_violations_p_2015, traffic_violations_p_2016, traffic_violations_p_2017, traffic_violations_p_2018, traffic_violations_p_2019, traffic_violations_p_2020;\n\ncreate table traffic_violations_p_2012\npartition of traffic_violations_p\nfor values from ('2012-01-01') to ('2013-01-01');\n\ncreate table traffic_violations_p_2013\npartition of traffic_violations_p\nfor values from ('2013-01-01') to ('2014-01-01');\n\ncreate table traffic_violations_p_2014\npartition of traffic_violations_p\nfor values from ('2014-01-01') to ('2015-01-01');\n\ncreate table traffic_violations_p_2015\npartition of traffic_violations_p\nfor values from ('2015-01-01') to ('2016-01-01');\n\ncreate table traffic_violations_p_2016\npartition of traffic_violations_p\nfor values from ('2016-01-01') to ('2017-01-01');\n\ncreate table traffic_violations_p_2017\npartition of traffic_violations_p\nfor values from ('2017-01-01') to ('2018-01-01');\n\ncreate table traffic_violations_p_2018\npartition of traffic_violations_p\nfor values from ('2018-01-01') to ('2019-01-01');\n\ncreate table traffic_violations_p_2019\npartition of traffic_violations_p\nfor values from ('2019-01-01') to ('2020-01-01');\n\ncreate table traffic_violations_p_2020\npartition of traffic_violations_p\nfor values from ('2020-01-01') to ('2021-01-01');\n\ndelete from traffic_violations_p;\n<\/pre>\n
Once the partitions are set up as required the data is correctly stored:<\/p>\n
\npostgres=# insert into traffic_violations_p select * from mv_traffic_violations;\nINSERT 0 1528078\npostgres=# select count(*) from traffic_violations_p_default ;\n count \n-------\n     0\n(1 row)\n<\/pre>\n
Instead of having one big table containing all the data we now have 10 partitions splitting the table in smaller chunks. Why does that matter? This is the topic of another post after we had a look at list and hash partitioning in the next posts.<\/p>\n","protected":false},"excerpt":{"rendered":"
Now that the data set is ready we will look at the first partitioning strategy: Range partitioning. Usually range partitioning is used to partition a table by days, months or years although you can partition by other data types as well. Time\/date based range partitioning is probably the most common use case so we will […]<\/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-12481","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nPostgreSQL partitioning (2): Range 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-2-range-partitioning\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL partitioning (2): Range partitioning\" \/>\n<meta property=\"og:description\" content=\"Now that the data set is ready we will look at the first partitioning strategy: Range partitioning. Usually range partitioning is used to partition a table by days, months or years although you can partition by other data types as well. Time\/date based range partitioning is probably the most common use case so we will […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-2-range-partitioning\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-06-03T04:00:01+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=\"9 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-2-range-partitioning\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-2-range-partitioning\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL partitioning (2): Range partitioning\",\"datePublished\":\"2019-06-03T04:00:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-2-range-partitioning\\\/\"},\"wordCount\":442,\"commentCount\":1,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-2-range-partitioning\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-2-range-partitioning\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-2-range-partitioning\\\/\",\"name\":\"PostgreSQL partitioning (2): Range partitioning - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2019-06-03T04:00:01+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-2-range-partitioning\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-2-range-partitioning\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-partitioning-2-range-partitioning\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL partitioning (2): Range 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 (2): Range 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-2-range-partitioning\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL partitioning (2): Range partitioning","og_description":"Now that the data set is ready we will look at the first partitioning strategy: Range partitioning. Usually range partitioning is used to partition a table by days, months or years although you can partition by other data types as well. Time\/date based range partitioning is probably the most common use case so we will […]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-2-range-partitioning\/","og_site_name":"dbi Blog","article_published_time":"2019-06-03T04:00:01+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-2-range-partitioning\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-2-range-partitioning\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL partitioning (2): Range partitioning","datePublished":"2019-06-03T04:00:01+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-2-range-partitioning\/"},"wordCount":442,"commentCount":1,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-2-range-partitioning\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-2-range-partitioning\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-2-range-partitioning\/","name":"PostgreSQL partitioning (2): Range partitioning - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-06-03T04:00:01+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-2-range-partitioning\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-2-range-partitioning\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-2-range-partitioning\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL partitioning (2): Range 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\/12481","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=12481"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12481\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12481"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12481"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12481"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12481"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}