Hash partitioning for distribution in PostgreSQL

Introduction

Hash partitioning helps split a big table into smaller parts based on a hash function. This makes data easier to manage and faster to find.

When you have a large table and want to spread data evenly across partitions.

When you want to improve query speed by searching only relevant partitions.

When you want to balance data storage and workload across multiple disks or servers.

When your data does not have natural ranges but you want even distribution.

When you want to simplify maintenance by working on smaller chunks of data.

Syntax

PostgreSQL

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...
) PARTITION BY HASH (column_name);

CREATE TABLE partition_name PARTITION OF table_name
  FOR VALUES WITH (MODULUS n, REMAINDER r);

The main table is created with PARTITION BY HASH on a chosen column.

Each partition is created with FOR VALUES WITH (MODULUS n, REMAINDER r) where n is total partitions and r is the partition number starting from 0.

Examples

This creates a main table users partitioned by hashing the id column.

PostgreSQL

CREATE TABLE users (
  id INT,
  name TEXT
) PARTITION BY HASH (id);

This creates the first partition for users where hash(id) % 4 = 0.

PostgreSQL

CREATE TABLE users_part_0 PARTITION OF users
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);

This creates the second partition for users where hash(id) % 4 = 1.

PostgreSQL

CREATE TABLE users_part_1 PARTITION OF users
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);

Sample Program

This example creates an employees table partitioned by hashing emp_id into 3 parts. It inserts 5 employees and selects all rows ordered by emp_id.

PostgreSQL

CREATE TABLE employees (
  emp_id INT,
  emp_name TEXT
) PARTITION BY HASH (emp_id);

CREATE TABLE employees_part_0 PARTITION OF employees
  FOR VALUES WITH (MODULUS 3, REMAINDER 0);

CREATE TABLE employees_part_1 PARTITION OF employees
  FOR VALUES WITH (MODULUS 3, REMAINDER 1);

CREATE TABLE employees_part_2 PARTITION OF employees
  FOR VALUES WITH (MODULUS 3, REMAINDER 2);

INSERT INTO employees (emp_id, emp_name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie'),
  (4, 'Diana'),
  (5, 'Evan');

SELECT * FROM employees ORDER BY emp_id;

Important Notes

Hash partitioning evenly distributes rows but does not guarantee order.

Choose the number of partitions (modulus) based on your data size and hardware.

Queries filtering on the partition key can be faster because only relevant partitions are scanned.

Summary

Hash partitioning splits data into parts using a hash function on a column.

It helps balance data and improve query speed by limiting search to certain partitions.

Each partition is defined by modulus (total parts) and remainder (partition number).