Skip to main content
The partition command generates SQL statements for hash-partitioned tables. This is useful when you need to create a large number of partition tables following PostgreSQL’s declarative partitioning syntax.

​
Usage

pgtofu partition generate [flags]

​
Flags

FlagShortDescriptionDefault
--tableTable name (required)
--schemaSchema namepublic
--modulusNumber of partitions (MODULUS value)16
--output-oOutput file path (- for stdout)stdout
--formatOutput format: sql or listsql
--help-hHelp for partition

​
Examples

​
Basic Partition Generation

# Generate 16 partitions for a table
pgtofu partition generate --table orders --modulus 16
Output: 
-- 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);

​
Custom Schema

pgtofu partition generate \
  --table questions \
  --schema assessment \
  --modulus 16

​
Write to File

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

​
List Format

Get just the partition names (useful for scripts): 
pgtofu partition generate --table orders --modulus 16 --format list
Output: 
orders_p0
orders_p1
orders_p2
...
orders_p15

​
Docker

docker run --rm \
  -v "$(pwd):/workspace" \
  -w /workspace \
  accented/pgtofu:latest partition generate \
  --table orders \
  --modulus 16 \
  --output schema/tables/orders_partitions.sql

​
Workflow

​
1. Create Parent Table

First, define your parent table with PARTITION BY HASH in your schema files: 
-- schema/tables/orders.sql
CREATE TABLE orders (
    id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    total NUMERIC(12, 2) NOT NULL
) PARTITION BY HASH (id);

​
2. Generate Partitions

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

​
3. Include in Schema

The generated partition file is automatically discovered when you run pgtofu diff or pgtofu generate: 
pgtofu diff \
  --current current-schema.json \
  --desired ./schema

​
4. Version Control

Commit the partition file to version control. This ensures consistent partition definitions across environments.

​
Partition Naming Convention

Partitions are named using the pattern {tablename}_p{remainder}:
TableModulusPartition Names
orders4orders_p0, orders_p1, orders_p2, orders_p3
events8events_p0 through events_p7
logs16logs_p0 through logs_p15

​
Partition Validation

When pgtofu parses schema files, it automatically validates partition completeness:
  • All REMAINDER values (0 to MODULUS-1) must be present
  • Partition count must match the MODULUS
  • Warnings are displayed for missing or duplicate partitions
Example warning: 
Warning: Hash partition validation for public.orders:
  Missing remainders: 5, 12
  Expected 16 partitions, found 14

​
Modulus Recommendations

Use CaseRecommended Modulus
Small tables (under 1M rows)4-8
Medium tables (1-100M rows)16-32
Large tables (100M+ rows)32-64
Very large tables (1B+ rows)64-128
Choosing a modulus larger than 1024 will display a warning. Very high partition counts can negatively impact query planning and DDL operations.

​
Generated SQL Format

The generated SQL follows PostgreSQL’s declarative partition syntax: 
CREATE TABLE schema.tablename_pN PARTITION OF schema.tablename
    FOR VALUES WITH (MODULUS M, REMAINDER N);
This syntax is fully compatible with pgtofu’s parser and will be correctly detected in subsequent diff and generate operations.

​
Use Cases

​
High-Volume Transactional Tables

# E-commerce orders distributed by order ID
pgtofu partition generate --table orders --modulus 32

# User activity logs
pgtofu partition generate --table activity_logs --modulus 64

​
Multi-Tenant Applications

# Tenant data distributed by tenant ID
pgtofu partition generate --table tenant_data --modulus 16

​
Analytics Tables

# Event data for analytics
pgtofu partition generate \
  --table events \
  --schema analytics \
  --modulus 128

​
Troubleshooting

Ensure the partition file is in the --desired directory path and has a .sql extension.
The parent table must be defined with PARTITION BY HASH (column) in your schema files before pgtofu can validate partitions.
If you see a warning about high partition counts (>1024), consider whether you really need that many partitions. High counts can impact performance.

​
See Also