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

CREATE INDEX search_idx ON mock_items
USING paradedb (id, description, rating)
WITH (partition_by = 'rating', target_segment_count = 16);

import { indexing } from "@paradedb/drizzle-paradedb";

indexing
  .paradedbIndex("search_idx", {
    partitionBy: ["rating"],
    targetSegmentCount: 16,
  })
  .on(mockItems.id, mockItems.description, mockItems.rating);

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,
        ),
    )

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)

ActiveRecord::Base.connection.add_paradedb_index(
  :mock_items,
  fields: {
    id: {},
    description: {},
    rating: {},
  },
  name: :search_idx,
  index_options: { partition_by: ["rating"], target_segment_count: 16 }
)

modelBuilder.Entity<MockItem>()
    .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:

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.

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:

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

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.

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