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

The last partitioning strategy we will look at is: hash partitioning. If you missed the first posts in this series 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<\/ol>\n

    Usually hash partitioning is used when you do not have a natural way of partitioning your data or you want to evenly distribute the data based on hash.<\/p>\n

    <\/p>\n

    In PostgreSQL hash partitioning might seem a bit strange in the beginning because you define a modulus and a remainder and that defines where the data is going to. Having a look at our materialized view which contains our data set which columns might be a good candidate for that?<\/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
    A good candidate would be the councils column. When we setup the hash partitioning correctly the result would be 5 partitions:<\/p>\n
    \npostgres=# select distinct councils from mv_traffic_violations ;\n councils \n----------\n         \n        1\n        3\n        5\n        2\n        4\n(6 rows)\n<\/pre>\n
    The partitioned table becomes:<\/p>\n
    \ncreate table traffic_violations_p_hash\n( seqid text\n, date_of_stop date  \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\n)\npartition by hash (councils);\n<\/pre>\n
    As usual psql will report the partitioning strategy for the table as well:<\/p>\n
    \npostgres=# d traffic_violations_p_hash \n               Partitioned table \"public.traffic_violations_p_hash\"\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               |           |          | \nPartition key: HASH (councils)\nNumber of partitions: 0\n<\/pre>\n
    The setup for the hash partitions will be as follows:<\/p>\n
    \ncreate table traffic_violations_p_hash_p1\npartition of traffic_violations_p_hash\nfor values with (modulus 5, remainder 0);\n\ncreate table traffic_violations_p_hash_p2\npartition of traffic_violations_p_hash\nfor values with (modulus 5, remainder 1);\n\ncreate table traffic_violations_p_hash_p3\npartition of traffic_violations_p_hash\nfor values with (modulus 5, remainder 2);\n\ncreate table traffic_violations_p_hash_p4\npartition of traffic_violations_p_hash\nfor values with (modulus 5, remainder 3);\n\ncreate table traffic_violations_p_hash_p5\npartition of traffic_violations_p_hash\nfor values with (modulus 5, remainder 4);\n<\/pre>\n
    Hash partitioning can not have a default partition as that would not make any sense because of the modulus and the remainder. When you try to do that you will get an error:<\/p>\n
    \npostgres=# create table traffic_violations_p_hash_default\npostgres-# partition of traffic_violations_p_hash default;\npsql: ERROR:  a hash-partitioned table may not have a default partition\n<\/pre>\n
    The final setup of our hash partitioned table is:<\/p>\n
    \npostgres=# d+ traffic_violations_p_hash\n                                   Partitioned table \"public.traffic_violations_p_hash\"\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: HASH (councils)\nPartitions: traffic_violations_p_hash_p1 FOR VALUES WITH (modulus 5, remainder 0),\n            traffic_violations_p_hash_p2 FOR VALUES WITH (modulus 5, remainder 1),\n            traffic_violations_p_hash_p3 FOR VALUES WITH (modulus 5, remainder 2),\n            traffic_violations_p_hash_p4 FOR VALUES WITH (modulus 5, remainder 3),\n            traffic_violations_p_hash_p5 FOR VALUES WITH (modulus 5, remainder 4)\n<\/pre>\n
    Loading the data and checking the partitions:<\/p>\n
    \npostgres=# insert into traffic_violations_p_hash\npostgres-#        select * from mv_traffic_violations;\nINSERT 0 1528078\npostgres=# select count(*) from traffic_violations_p_hash_p1;\n count  \n--------\n 988085\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_hash_p2;\n count  \n--------\n 539993\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_hash_p3;\n count \n-------\n     0\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_hash_p4;\n count \n-------\n     0\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_hash_p5;\n count \n-------\n     0\n(1 row)\n<\/pre>\n
    Why do we have only data in the first and second partition? Looking more closely at the distribution of the councils in the materialized view:<\/p>\n
    \npostgres=# select distinct councils, count(*) from mv_traffic_violations group by councils;\n councils | count  \n----------+--------\n        1 | 231070\n        2 | 211759\n        3 | 328234\n        4 | 334142\n        5 | 308402\n          | 114471\n(6 rows)\n<\/pre>\n
    First of all we have a lot of rows which do not have a council. Null values in hash partitioned tables always go to the partition where the remainder is 0:<\/p>\n
    \npostgres=# truncate table traffic_violations_p_hash;\nTRUNCATE TABLE\npostgres=# insert into traffic_violations_p_hash (councils) select null from generate_series(1,100);\nINSERT 0 100\npostgres=# select count(*) from traffic_violations_p_hash_p1;\n count \n-------\n   100\n(1 row)\n<\/pre>\n
    Here is the reason for this behavior: “The calculated hash value for the null value will be zero, therefore, it will fall to the partition having remainder zero.”<\/a>.<\/p>\n
    This does still not explain why nothing is in the partitions 3 to 5. Lets do a few tests with our councils:<\/p>\n
    \npostgres=# truncate traffic_violations_p_hash;\nTRUNCATE TABLE\npostgres=# insert into traffic_violations_p_hash (councils) values (1),(2),(3),(4),(5);\nINSERT 0 5\npostgres=# select councils from traffic_violations_p_hash_p1;\n councils \n----------\n        1\n        4\n        5\n(3 rows)\n\npostgres=# select councils from traffic_violations_p_hash_p2;\n councils \n----------\n        2\n        3\n(2 rows)\n<\/pre>\n
    This confirms the behavior. Councils 1,4 and 5 go to the first partition while 2 and 3 go to the second one. The decision to hash partition the on the councils column clearly was not a good idea. Hash paritioning is great when you have many different values:<\/p>\n
    \npostgres=# truncate traffic_violations_p_hash;\nTRUNCATE TABLE\npostgres=# insert into traffic_violations_p_hash (councils) select * from generate_series(1,10000);\nINSERT 0 10000\npostgres=# select count(*) from traffic_violations_p_hash_p1;\n count \n-------\n  1969\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_hash_p2;\n count \n-------\n  2034\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_hash_p3;\n count \n-------\n  2058\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_hash_p4;\n count \n-------\n  1928\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_hash_p5;\n count \n-------\n  2011\n(1 row)\n<\/pre>\n
    This gives much better distribution of the data. A good candidate would be the seqid column:<\/p>\n
    \ndrop table traffic_violations_p_hash, traffic_violations_p_hash_p1, traffic_violations_p_hash_p2, traffic_violations_p_hash_p3, traffic_violations_p_hash_p4, traffic_violations_p_hash_p5;\ncreate table traffic_violations_p_hash\n( seqid text\n, date_of_stop date  \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\n)\npartition by hash (seqid);\n\ncreate table traffic_violations_p_hash_p1\npartition of traffic_violations_p_hash\nfor values with (modulus 5, remainder 0);\n\ncreate table traffic_violations_p_hash_p2\npartition of traffic_violations_p_hash\nfor values with (modulus 5, remainder 1);\n\ncreate table traffic_violations_p_hash_p3\npartition of traffic_violations_p_hash\nfor values with (modulus 5, remainder 2);\n\ncreate table traffic_violations_p_hash_p4\npartition of traffic_violations_p_hash\nfor values with (modulus 5, remainder 3);\n\ncreate table traffic_violations_p_hash_p5\npartition of traffic_violations_p_hash\nfor values with (modulus 5, remainder 4);\n<\/pre>\n
    Loading the data again and checking the partitions:<\/p>\n
    \npostgres=# insert into traffic_violations_p_hash\npostgres-#        select * from mv_traffic_violations;\nINSERT 0 1528078\npostgres=# select count(*) from traffic_violations_p_hash_p1;\n count  \n--------\n 305253\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_hash_p2;\n count  \n--------\n 304999\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_hash_p3;\n count  \n--------\n 305215\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_hash_p4;\n count  \n--------\n 305719\n(1 row)\n\npostgres=# select count(*) from traffic_violations_p_hash_p5;\n count  \n--------\n 306892\n(1 row)\n<\/pre>\n
    That was hash partitioning. In the next post we’ll look at partition pruning.<\/p>\n","protected":false},"excerpt":{"rendered":"
    The last partitioning strategy we will look at is: hash partitioning. If you missed the first posts in this series here they are: PostgreSQL partitioning (1): Preparing the data set PostgreSQL partitioning (2): Range partitioning PostgreSQL partitioning (3): List partitioning Usually hash partitioning is used when you do not have a natural way of partitioning […]<\/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-12483","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nPostgreSQL partitioning (4): Hash 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-4-hash-partitioning\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL partitioning (4): Hash partitioning\" \/>\n<meta property=\"og:description\" content=\"The last partitioning strategy we will look at is: hash partitioning. If you missed the first posts in this series here they are: PostgreSQL partitioning (1): Preparing the data set PostgreSQL partitioning (2): Range partitioning PostgreSQL partitioning (3): List partitioning Usually hash partitioning is used when you do not have a natural way of partitioning […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-06-04T04:00:03+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-4-hash-partitioning\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL partitioning (4): Hash partitioning\",\"datePublished\":\"2019-06-04T04:00:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/\"},\"wordCount\":400,\"commentCount\":5,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/\",\"name\":\"PostgreSQL partitioning (4): Hash partitioning - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2019-06-04T04:00:03+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL partitioning (4): Hash 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 (4): Hash 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-4-hash-partitioning\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL partitioning (4): Hash partitioning","og_description":"The last partitioning strategy we will look at is: hash partitioning. If you missed the first posts in this series here they are: PostgreSQL partitioning (1): Preparing the data set PostgreSQL partitioning (2): Range partitioning PostgreSQL partitioning (3): List partitioning Usually hash partitioning is used when you do not have a natural way of partitioning […]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/","og_site_name":"dbi Blog","article_published_time":"2019-06-04T04:00:03+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-4-hash-partitioning\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL partitioning (4): Hash partitioning","datePublished":"2019-06-04T04:00:03+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/"},"wordCount":400,"commentCount":5,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/","name":"PostgreSQL partitioning (4): Hash partitioning - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-06-04T04:00:03+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-partitioning-4-hash-partitioning\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL partitioning (4): Hash 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\/12483","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=12483"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12483\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12483"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12483"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12483"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12483"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}