Table Partitioning

Overview

PostgreSQL supports three partitioning strategies:

Strategy	Use Case	Key Distribution
HASH	Distribute data evenly	Hash of partition key
RANGE	Time-series, ordered data	Value ranges
LIST	Categorical data	Explicit value lists

Hash Partitioning

Creating a Hash-Partitioned Table

-- schema/tables/orders.sql
CREATE TABLE orders (
    id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    total NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY HASH (id);

Generating Partitions

Use the pgtofu partition generate command to create partition definitions:

pgtofu partition generate \
  --table orders \
  --modulus 16 \
  --output schema/tables/orders_partitions.sql

This generates:

-- schema/tables/orders_partitions.sql
-- Hash partitions for public.orders
-- Generated by pgtofu

CREATE TABLE public.orders_p0 PARTITION OF public.orders
    FOR VALUES WITH (MODULUS 16, REMAINDER 0);

CREATE TABLE public.orders_p1 PARTITION OF public.orders
    FOR VALUES WITH (MODULUS 16, REMAINDER 1);

-- ... continues for all 16 partitions

CREATE TABLE public.orders_p15 PARTITION OF public.orders
    FOR VALUES WITH (MODULUS 16, REMAINDER 15);

Choosing Modulus

Table Size	Recommended Modulus
< 1M rows	4-8
1-100M rows	16-32
100M-1B rows	32-64
> 1B rows	64-128

Choose your modulus carefully. Changing the number of partitions later requires recreating all partitions and migrating data.

Custom Schema

pgtofu partition generate \
  --table events \
  --schema analytics \
  --modulus 32 \
  --output schema/tables/events_partitions.sql

Range Partitioning

Time-Based Partitioning

-- schema/tables/logs.sql
CREATE TABLE logs (
    id BIGSERIAL,
    message TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- Create partitions for each month
CREATE TABLE logs_2024_01 PARTITION OF logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_2024_02 PARTITION OF logs
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE logs_2024_03 PARTITION OF logs
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- Default partition for out-of-range values
CREATE TABLE logs_default PARTITION OF logs DEFAULT;

Numeric Range Partitioning

-- schema/tables/products.sql
CREATE TABLE products (
    id BIGSERIAL,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
) PARTITION BY RANGE (price);

CREATE TABLE products_cheap PARTITION OF products
    FOR VALUES FROM (0) TO (100);

CREATE TABLE products_medium PARTITION OF products
    FOR VALUES FROM (100) TO (1000);

CREATE TABLE products_expensive PARTITION OF products
    FOR VALUES FROM (1000) TO (MAXVALUE);

List Partitioning

Categorical Data

-- schema/tables/customers.sql
CREATE TABLE customers (
    id BIGSERIAL,
    name TEXT NOT NULL,
    country TEXT NOT NULL
) PARTITION BY LIST (country);

CREATE TABLE customers_na PARTITION OF customers
    FOR VALUES IN ('US', 'CA', 'MX');

CREATE TABLE customers_eu PARTITION OF customers
    FOR VALUES IN ('UK', 'DE', 'FR', 'ES', 'IT');

CREATE TABLE customers_apac PARTITION OF customers
    FOR VALUES IN ('JP', 'CN', 'AU', 'KR', 'SG');

CREATE TABLE customers_other PARTITION OF customers DEFAULT;

Partition Management with pgtofu

Workflow

Define parent table with PARTITION BY clause
Generate or write partitions in separate SQL file
Commit to version control
Run pgtofu diff/generate as normal

# Initial setup
pgtofu partition generate --table orders --modulus 16 \
  --output schema/tables/orders_partitions.sql

# Add to git
git add schema/tables/orders.sql schema/tables/orders_partitions.sql
git commit -m "Add orders table with hash partitioning"

# Generate migrations
pgtofu generate --current current.json --desired ./schema

Partition Validation

pgtofu automatically validates hash partitions when parsing:

Checks all REMAINDER values (0 to MODULUS-1) are present
Warns about missing partitions
Warns about duplicate partitions

Example warning:

Warning: Hash partition validation for public.orders:
  Missing remainders: 5, 12
  Expected 16 partitions, found 14

Indexes on Partitioned Tables

Global Index (PostgreSQL 11+)

Indexes are inherited by all partitions:

-- Index on parent table
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Automatically created on each partition:
-- idx_orders_p0_user_id, idx_orders_p1_user_id, etc.

Partition-Specific Indexes

-- Index only on specific partition
CREATE INDEX idx_logs_2024_01_message ON logs_2024_01(message);

Constraints on Partitions

Primary Keys

-- Primary key must include partition key
CREATE TABLE orders (
    id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    PRIMARY KEY (id)  -- Works with HASH partitioning
) PARTITION BY HASH (id);

Foreign Keys

Foreign keys referencing partitioned tables require PostgreSQL 12+:

-- PostgreSQL 12+
CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id)
);

Combining with TimescaleDB

For time-series data, consider using TimescaleDB hypertables instead of native range partitioning:

-- Native PostgreSQL partitioning
CREATE TABLE events (...) PARTITION BY RANGE (created_at);

-- TimescaleDB (recommended for time-series)
CREATE TABLE events (...);
SELECT create_hypertable('events', 'created_at');

TimescaleDB offers:

Automatic chunk creation
Built-in compression
Continuous aggregates
Better query optimization

Best Practices

Choosing a Partitioning Strategy

HASH: Use when you need even data distribution and query by partition key
RANGE: Use for time-series data or ordered numeric ranges
LIST: Use for categorical data with known distinct values

Partition Key Selection

Choose columns frequently used in WHERE clauses
For HASH: column with high cardinality and frequent equality lookups
For RANGE: column with natural ordering (timestamps, sequential IDs)

Number of Partitions

Too few: partitions become too large
Too many: query planning overhead increases
Sweet spot: 10-100 partitions typically works well

Maintenance

Range partitions: create future partitions in advance
List partitions: add DEFAULT partition for unknown values
Hash partitions: modulus is fixed, plan for growth

Limitations

pgtofu Limitations

Only HASH partition generation is automated via CLI
RANGE and LIST partitions must be written manually
Changing partition strategy requires manual migration

PostgreSQL Limitations

Cannot partition an existing table (must create new and migrate)
Primary/unique keys must include partition key
Some foreign key constraints require PostgreSQL 12+

Troubleshooting

Partition not detected

Ensure the partition file is in the --desired directory and uses .sql extension.

Validation warnings

Check that all REMAINDER values from 0 to MODULUS-1 are present in your partition definitions.

Query not using partition pruning

Ensure your WHERE clause includes the partition key with a literal or parameter.

Getting Started

Core Concepts

Features

Workflows

Development

Table Partitioning

Overview

Hash Partitioning

Creating a Hash-Partitioned Table

Generating Partitions

Choosing Modulus

Custom Schema

Range Partitioning

Time-Based Partitioning

Numeric Range Partitioning

List Partitioning

Categorical Data

Partition Management with pgtofu

Workflow

Partition Validation

Indexes on Partitioned Tables

Global Index (PostgreSQL 11+)

Partition-Specific Indexes

Constraints on Partitions

Primary Keys

Foreign Keys

Combining with TimescaleDB

Best Practices

Limitations

pgtofu Limitations

PostgreSQL Limitations

Troubleshooting

See Also

​Overview

​Hash Partitioning

​Creating a Hash-Partitioned Table

​Generating Partitions

​Choosing Modulus

​Custom Schema

​Range Partitioning

​Time-Based Partitioning

​Numeric Range Partitioning

​List Partitioning

​Categorical Data

​Partition Management with pgtofu

​Workflow

​Partition Validation

​Indexes on Partitioned Tables

​Global Index (PostgreSQL 11+)

​Partition-Specific Indexes

​Constraints on Partitions

​Primary Keys

​Foreign Keys

​Combining with TimescaleDB

​Best Practices

​Limitations

​pgtofu Limitations

​PostgreSQL Limitations

​Troubleshooting

​See Also

Overview

Hash Partitioning

Creating a Hash-Partitioned Table

Generating Partitions

Choosing Modulus

Custom Schema

Range Partitioning

Time-Based Partitioning

Numeric Range Partitioning

List Partitioning

Categorical Data

Partition Management with pgtofu

Workflow

Partition Validation

Indexes on Partitioned Tables

Global Index (PostgreSQL 11+)

Partition-Specific Indexes

Constraints on Partitions

Primary Keys

Foreign Keys

Combining with TimescaleDB

Best Practices

Limitations

pgtofu Limitations

PostgreSQL Limitations

Troubleshooting

See Also