> ## Documentation Index
> Fetch the complete documentation index at: https://pgtofu.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Table Partitioning

> Declarative management of partitioned tables

## 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

```sql theme={null}
-- 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:

```bash theme={null}
pgtofu partition generate \
  --table orders \
  --modulus 16 \
  --output schema/tables/orders_partitions.sql
```

This generates:

```sql theme={null}
-- 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              |

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

### Custom Schema

```bash theme={null}
pgtofu partition generate \
  --table events \
  --schema analytics \
  --modulus 32 \
  --output schema/tables/events_partitions.sql
```

## Range Partitioning

### Time-Based Partitioning

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```bash theme={null}
# 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:

```sql theme={null}
-- 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

```sql theme={null}
-- Index only on specific partition
CREATE INDEX idx_logs_2024_01_message ON logs_2024_01(message);
```

## Constraints on Partitions

### Primary Keys

```sql theme={null}
-- 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+:

```sql theme={null}
-- 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:

```sql theme={null}
-- 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

<AccordionGroup>
  <Accordion title="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
  </Accordion>

  <Accordion title="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)
  </Accordion>

  <Accordion title="Number of Partitions">
    * Too few: partitions become too large
    * Too many: query planning overhead increases
    * Sweet spot: 10-100 partitions typically works well
  </Accordion>

  <Accordion title="Maintenance">
    * Range partitions: create future partitions in advance
    * List partitions: add DEFAULT partition for unknown values
    * Hash partitions: modulus is fixed, plan for growth
  </Accordion>
</AccordionGroup>

## 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

<AccordionGroup>
  <Accordion title="Partition not detected">
    Ensure the partition file is in the `--desired` directory and uses `.sql` extension.
  </Accordion>

  <Accordion title="Validation warnings">
    Check that all REMAINDER values from 0 to MODULUS-1 are present in your partition definitions.
  </Accordion>

  <Accordion title="Query not using partition pruning">
    Ensure your WHERE clause includes the partition key with a literal or parameter.
  </Accordion>
</AccordionGroup>

## See Also

* [`partition` command](/cli/partition) - CLI reference for partition generation
* [PostgreSQL Features](/features/postgresql) - All PostgreSQL support
* [TimescaleDB Features](/features/timescaledb) - Automatic time-based partitioning