Skip to main content

​
Overview

PostgreSQL supports three partitioning strategies:
StrategyUse CaseKey Distribution
HASHDistribute data evenlyHash of partition key
RANGETime-series, ordered dataValue ranges
LISTCategorical dataExplicit 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 SizeRecommended Modulus
< 1M rows4-8
1-100M rows16-32
100M-1B rows32-64
> 1B rows64-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

  1. Define parent table with PARTITION BY clause
  2. Generate or write partitions in separate SQL file
  3. Commit to version control
  4. 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

  • 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
  • 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)
  • Too few: partitions become too large
  • Too many: query planning overhead increases
  • Sweet spot: 10-100 partitions typically works well
  • 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

Ensure the partition file is in the --desired directory and uses .sql extension.
Check that all REMAINDER values from 0 to MODULUS-1 are present in your partition definitions.
Ensure your WHERE clause includes the partition key with a literal or parameter.

​
See Also