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