PostgreSQL Hash Partitioning — Internals & Practical Guide

Hash partitioning in PostgreSQL is useful when you want to evenly distribute rows across partitions based on a column such as customer_id, user_id, or uuid.

How Hash Partitioning Works Internally

When a row is inserted or queried, such as:

SELECT * FROM customer_data WHERE customer_id = 19;

target_partition = hash(customer_id) % MODULUS

PostgreSQL internally computes:

• hash(...) is a type-specific internal hash function (not a simple % operation)
• MODULUS is the number of partitions
• Each partition is assigned a REMAINDER value from 0 to MODULUS - 1
• A row will be routed to the partition whose REMAINDER matches the result of hash(value) % MODULUS

Step-by-Step: Creating Hash Partitions

1. Create a Partitioned Table

CREATE TABLE customer_data (
  id BIGSERIAL,
  customer_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT now(),
  data TEXT
) PARTITION BY HASH (customer_id);

2. Create Hash Partitions

CREATE TABLE customer_data_p0 PARTITION OF customer_data
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE customer_data_p1 PARTITION OF customer_data
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE customer_data_p2 PARTITION OF customer_data
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE customer_data_p3 PARTITION OF customer_data
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

PostgreSQL Hash Functions by Data Type

PostgreSQL uses internal hash functions depending on the column type:

Column Type Hash Function Used int hashint4() bigint hashint8() text, varchar hashtext() uuid hashuuid() timestamp hashtimestamp()

These functions are used during partition routing and query planning. While not designed for direct use in partition logic, they are deterministic and consistent.

Summary of Key Concepts

Important Notes

• PostgreSQL does not use customer_id % N; it uses an internal hash function.
• You do not need to compute or know the exact hash values manually.
• Partition routing is handled automatically once the partition layout is defined.