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

# partition

> Generate hash partition SQL statements

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

```bash theme={null}
pgtofu partition generate [flags]
```

## Flags

| Flag        | Short | Description                          | Default  |
| ----------- | ----- | ------------------------------------ | -------- |
| `--table`   |       | Table name (required)                |          |
| `--schema`  |       | Schema name                          | `public` |
| `--modulus` |       | Number of partitions (MODULUS value) | `16`     |
| `--output`  | `-o`  | Output file path (`-` for stdout)    | stdout   |
| `--format`  |       | Output format: `sql` or `list`       | `sql`    |
| `--help`    | `-h`  | Help for partition                   |          |

## Examples

### Basic Partition Generation

```bash theme={null}
# Generate 16 partitions for a table
pgtofu partition generate --table orders --modulus 16
```

Output:

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

```bash theme={null}
pgtofu partition generate \
  --table questions \
  --schema assessment \
  --modulus 16
```

### Write to File

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

### List Format

Get just the partition names (useful for scripts):

```bash theme={null}
pgtofu partition generate --table orders --modulus 16 --format list
```

Output:

```
orders_p0
orders_p1
orders_p2
...
orders_p15
```

### Docker

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

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

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

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

| Table    | Modulus | Partition Names                                    |
| -------- | ------- | -------------------------------------------------- |
| `orders` | 4       | `orders_p0`, `orders_p1`, `orders_p2`, `orders_p3` |
| `events` | 8       | `events_p0` through `events_p7`                    |
| `logs`   | 16      | `logs_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 Case                     | Recommended 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              |

<Warning>
  Choosing a modulus larger than 1024 will display a warning. Very high partition counts can negatively impact query planning and DDL operations.
</Warning>

## Generated SQL Format

The generated SQL follows PostgreSQL's declarative partition syntax:

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

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

```bash theme={null}
# Tenant data distributed by tenant ID
pgtofu partition generate --table tenant_data --modulus 16
```

### Analytics Tables

```bash theme={null}
# Event data for analytics
pgtofu partition generate \
  --table events \
  --schema analytics \
  --modulus 128
```

## Troubleshooting

<AccordionGroup>
  <Accordion title="Partitions not detected by diff">
    Ensure the partition file is in the `--desired` directory path and has a `.sql` extension.
  </Accordion>

  <Accordion title="Parent table not found">
    The parent table must be defined with `PARTITION BY HASH (column)` in your schema files before pgtofu can validate partitions.
  </Accordion>

  <Accordion title="Too many partitions warning">
    If you see a warning about high partition counts (>1024), consider whether you really need that many partitions. High counts can impact performance.
  </Accordion>
</AccordionGroup>

## See Also

* [Partitioning Feature](/features/partitioning) - Complete partitioning documentation
* [PostgreSQL Features](/features/postgresql) - All supported PostgreSQL features
* [`generate`](/cli/generate) - Generate migrations including partition changes