---
title: Index Partitioning
description: Partition ParadeDB indexes to accelerate filter and JOIN queries
canonical: https://www.paradedb.com/docs/reference/indexing/partition-by
---
`partition_by` is currently a Beta feature under active development. Syntax
and internal planner behaviors may change in future releases.
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:
The following example uses the demo table’s `rating` field as a partition key. In your application, use the tenant or organization identifier that appears in your filters.
```sql SQL
CREATE INDEX search_idx ON mock_items
USING paradedb (id, description, rating)
WITH (partition_by = 'rating', target_segment_count = 16);
```
```ts Drizzle
import { indexing } from "@paradedb/drizzle-paradedb";
indexing
.paradedbIndex("search_idx", {
partitionBy: ["rating"],
targetSegmentCount: 16,
})
.on(mockItems.id, mockItems.description, mockItems.rating);
```
```python Django
from django.db import connection
from paradedb.indexes import ParadeDBIndex
with connection.schema_editor() as schema_editor:
schema_editor.add_index(
MockItem,
ParadeDBIndex(
fields={'id': {}, 'description': {}, 'rating': {}},
name="search_idx",
partition_by=['rating'],
target_segment_count=16,
),
)
```
```python SQLAlchemy
from sqlalchemy import Index
from paradedb.sqlalchemy import indexing
idx = Index(
"search_idx",
indexing.ParadeDBField(MockItem.id),
indexing.ParadeDBField(MockItem.description),
indexing.ParadeDBField(MockItem.rating),
postgresql_using="paradedb",
postgresql_with=dict(indexing.IndexOptions(
partition_by=['rating'], target_segment_count=16,
)),
)
with engine.begin() as conn:
idx.create(conn)
```
```ruby Rails
ActiveRecord::Base.connection.add_paradedb_index(
:mock_items,
fields: {
id: {},
description: {},
rating: {},
},
name: :search_idx,
index_options: { partition_by: ["rating"], target_segment_count: 16 }
)
```
```cs EF Core
modelBuilder.Entity()
.HasParadeDbIndex("search_idx", e => e.Id)
.HasField(e => e.Description)
.HasField(e => e.Rating)
.HasPartitionBy("rating")
.HasTargetSegmentCount(16);
```
When an index includes a vector field, `partition_by` defaults to that field
unless explicitly set.
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
);
```
Because ParadeDB partitions rows multi-dimensionally using a kd-tree, the
order in which columns are specified in `partition_by` is not important.
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.
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.
---
## 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. |
You don't need to add `ctid` to reach target_segment_count. When the listed columns can't split a partition any further, `CREATE INDEX` splits it by heap position. If you list `ctid`, it competes with the other columns as an equal, and the fallback doesn't apply.
### 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)`).
---
## Performance Tuning
When using `partition_by`, we recommend setting `target_segment_count` to 2–4X the parallel worker count. By default, `CREATE INDEX` sets `target_segment_count` equal to 2X `max_parallel_workers_per_gather`.
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).