Index Partitioning

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:

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:

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

Requirements for Index Partitioning

Columns specified in partition_by must meet the following constraints:

Single-valued only: Multi-valued types (such as arrays TEXT[], INT[] and JSON/JSONB fields) cannot be used.
Columnar indexed: The column must be columnar indexed. Scalar types (integers, floats, booleans, dates, timestamps, UUIDs) are columnar indexed by default.
Tokenizer: If a text column is used in partition_by, it must use the literal tokenizer (e.g. (tenant_code::pdb.literal)).
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.

-- 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. Runtime planner settings for partitioned joins are listed in the Configuration Reference.

Indexing

Full-Text / Lexical Search

Vector / Semantic Search

Hybrid Search

Aggregations

JOINs

Sharding

Cheatsheets

Index Partitioning

Column Selection Guidance

Requirements for Index Partitioning

Performance Tuning

​Column Selection Guidance

​Requirements for Index Partitioning

​Performance Tuning

Column Selection Guidance

Requirements for Index Partitioning

Performance Tuning