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

# Index Partitioning

> Partition ParadeDB indexes to accelerate filter and JOIN queries

<Warning>
  `partition_by` is currently a Beta feature under active development. Syntax
  and internal planner behaviors may change in future releases.
</Warning>

By default, a ParadeDB index does not partition rows across segments. By using the `partition_by`
option in `CREATE INDEX`, you can partition the index space along one or more columns:

```sql theme={null}
CREATE INDEX items_idx ON items
USING paradedb (id, tenant_id, description, created_at)
WITH (
  partition_by = 'tenant_id',
  target_segment_count = 16
);
```

Partitioning an index provides two key benefits:

* Accelerating search and filter queries
* Accelerating joins

You can also partition along multiple columns by specifying a comma-separated list:

```sql theme={null}
CREATE INDEX events_idx ON events
USING paradedb (id, organization_id, created_at, message)
WITH (
  partition_by = 'organization_id, created_at',
  target_segment_count = 32
);
```

<Note>
  Because ParadeDB partitions rows multi-dimensionally using a kd-tree, the
  order in which columns are specified in `partition_by` is not important.
</Note>

Because [`target_segment_count`](/docs/reference/indexing/create-index#index-partitioning-beta) controls the total number of segments, that segment budget is divided across all partition columns. For most workloads, partitioning on 1 or 2 high-selectivity columns gives the best balance between query performance and partitioning overhead.

<Note>
  Partition boundaries are established during `CREATE INDEX` or `REINDEX` and
  are not yet rebalanced as new data is inserted. Query performance against
  partitioned indexes will gradually decline as writes accumulate. Running
  `REINDEX` restores optimal partition boundaries. Incremental maintenance of
  partitioned indexes across ongoing writes is being actively worked on.
</Note>

***

## Column Selection Guidance

Choosing the right column(s) for `partition_by` depends on your query patterns and join requirements.

| Workload / Primary Pattern | Recommended `partition_by` Column | Expected Impact |
| :- | :- | :- |
| Multi-Tenant SaaS (queries filter by tenant) | Tenant ID (e.g. `tenant_id`, `org_id`) | Pruning: Queries filter on `tenant_id = ?`, bypassing non-matching segments entirely to reduce I/O and cache churn. |
| Time-Series / Event Logs (queries filter by recency) | Timestamp / Date (e.g. `created_at`, `event_time`) | Pruning: Range filters (`created_at >= ...`) skip older or non-matching segments. |
| Large-Scale Joins (e.g. `orders` JOIN `items`) | Equi-join key on both tables (e.g. `orders.item_id` and `items.id`) | Co-Partitioned Joins: Joins and downstream operations (aggregates, Top-K) execute worker-locally without inter-worker data exchange. |
| Multi-Tenant with Joins | Shared tenant key on both tables (e.g. `orders.tenant_id` and `items.tenant_id`) | Dual Benefit: Segment pruning for single-tenant searches and worker-local joins within a tenant. |
| Multiple Query Filters | Multiple columns (e.g. `tenant_id, created_at`) | Multi-Dimensional Pruning: Enables pruning on tenant equality, timestamp ranges, or both, with segment granularity divided among the columns. |

### Requirements for Index Partitioning

Columns specified in `partition_by` must meet the following constraints:

1. Single-valued only: Multi-valued types (such as arrays `TEXT[]`, `INT[]` and JSON/JSONB fields) cannot be used.
2. Columnar indexed: The column must be columnar indexed. Scalar types (integers, floats, booleans, dates, timestamps, UUIDs) are columnar indexed by default.
3. Tokenizer: If a text column is used in `partition_by`, it must use the [`literal`](/docs/reference/tokenizers/available-tokenizers/literal) tokenizer (e.g. `(tenant_code::pdb.literal)`).
4. Low-cardinality columns: Columns with few distinct values (booleans, enums) cap total partitions to their cardinality. To reach `target_segment_count`, pair them with Postgres's internal `ctid` column (e.g. `partition_by = 'is_active, ctid'`). Only use `ctid` in this specific case: it usually cannot prune on its own and should not be used alone or with high-cardinality columns.

***

## Performance Tuning

By default, `CREATE INDEX` sets `target_segment_count` equal to the number of CPUs on the system. When using `partition_by`, we recommend setting `target_segment_count` to 2–4X the CPU core count (or parallel worker count).

Over-partitioning creates narrower value ranges per segment, allowing queries to prune more non-matching data and helping the planner align partition boundaries across tables during parallel joins. However, creating too many segments increases per-segment scan and metadata overhead. Sizing `target_segment_count` to 2–4X CPU cores strikes a practical balance between pruning granularity and resource usage.

```sql theme={null}
-- Example: On an 8-core machine, over-partition by 4X (target_segment_count = 32)
CREATE INDEX users_idx ON users USING paradedb (id, name)
WITH (partition_by = 'id', target_segment_count = 32);

CREATE INDEX posts_idx ON posts USING paradedb (id, owner_user_id, title)
WITH (partition_by = 'owner_user_id', target_segment_count = 32);
```

For more details on tuning worker parallelism and segment counts, see [Read Throughput](/docs/operate/performance-tuning/reads#adjusting-target-segment-count). Runtime planner settings for partitioned joins are listed in the [Configuration Reference](/docs/reference/configuration#query-planning).