---
title: Index Partitioning
description: Partition ParadeDB indexes to accelerate filter and JOIN queries
canonical: https://www.paradedb.com/docs/reference/indexing/partition-by
---

<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
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
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`](/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`](/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
-- 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](/operate/performance-tuning/reads#adjusting-target-segment-count). Runtime planner settings for partitioned joins are listed in the [Configuration Reference](/reference/configuration#query-planning).