Skip to main content
As a general rule of thumb, the performance of expensive search queries can be greatly improved if they are able to access more parallel Postgres workers and more shared buffer memory.

​
Increasing Parallel Workers

There are three settings that control how many parallel workers ultimately get assigned to a query. First, max_worker_processes is a global limit for the number of workers. Next, max_parallel_workers is a subset of max_worker_processes, and sets the limit for workers used in parallel queries. Finally, max_parallel_workers_per_gather limits how many workers a single query can receive.
postgresql.conf
In the above example, the maximum number of workers that a single query can receive is set to 4. The max_parallel_workers pool is set to 64, which means that 16 queries can execute simultaneously with 4 workers each. Finally, max_worker_processes is set to 72 to give headroom for other workers like autovacuum and replication. This is a good starting point:
postgresql.conf
We recommend experimenting with different settings, as the best configuration depends on the underlying hardware, query patterns, and volume of data.
If all max_parallel_workers are in use, Postgres will still execute additional queries, but those queries will run without parallelism. This means that queries do not fail, but they may run slower due to lack of parallelism.

​
Increasing Shared Buffers

shared_buffers controls how much memory is available to the Postgres buffer cache. We recommend starting with 25% of total memory for shared_buffers and no more than 40% of total memory.
postgresql.conf
The pg_prewarm extension can be used to load the ParadeDB index into the buffer cache after Postgres restarts. A higher shared_buffers value allows more of the index to be stored in the buffer cache.

​
Configuring Autovacuum

ParadeDB relies on Postgres’ visibility map to confirm that matching index entries are visible without reading the underlying heap pages:
  • Aggregates (ParadeDB Aggregate Scan) skip MVCC checks for entire segments or bitmap windows when their heap blocks are marked all-visible, avoiding per-row ctid lookups and heap fetches.
  • Joins (ParadeDB Join Scan) and covering scans (ParadeDB Base Scan) confirm visibility directly from the visibility map and skip or defer ctid fast-field reads and heap page access for all-visible blocks.
  • Top-K and heap-fetching scans (ParadeDB Base Scan) avoid stepping over dead tuples left behind by unvacuumed updates and deletes.
When a table experiences writes (INSERT, UPDATE, or DELETE), Postgres clears the all-visible bit in the visibility map for every modified 8 KB heap page until VACUUM runs. Because the visibility map tracks pages rather than individual rows, updating even a small percentage of rows scattered across a large table can mark the majority of its heap pages as not all-visible long before Postgres’ default autovacuum_vacuum_scale_factor (0.2, or 20% of table rows) triggers a VACUUM. Similarly, newly inserted pages after a bulk load are not marked all-visible until VACUUM runs.

​
Diagnosing Visibility Map Staleness

When a scan encounters a significantly stale visibility map (by default, at least paradedb.visibility_map_warning_threshold = 10000 non-all-visible heap blocks across the table and at least 50% of scanned blocks not all-visible), ParadeDB emits a once-per-query WARNING (controlled by paradedb.planner_warnings). You can also inspect EXPLAIN ANALYZE output to see how much heap visibility work a query performed:
  • On ParadeDB Aggregate Scan, check the Visibility section (Segments Skipped vs Segments Checked, and Blocks Requiring Checks vs Blocks Total).
  • On ParadeDB Base Scan, check Heap Fetches (and Invisible Tuples under EXPLAIN (ANALYZE, VERBOSE)).

​
Tuning Autovacuum

Postgres can be configured to automatically vacuum a table when a certain number of rows have been modified or inserted. Autovacuum settings can be set globally in postgresql.conf or for a specific table:
There are several autovacuum settings, but the important ones to note are:
  1. autovacuum_vacuum_scale_factor triggers an autovacuum if a certain percentage of rows in a table have been updated or deleted.
  2. autovacuum_vacuum_threshold triggers an autovacuum if an absolute number of rows have been updated or deleted.
  3. autovacuum_vacuum_insert_scale_factor and autovacuum_vacuum_insert_threshold trigger an autovacuum after a percentage or absolute number of rows have been inserted, keeping the visibility map up to date for insert-heavy workloads.
  4. autovacuum_naptime ensures that vacuum does not run too frequently.
Setting autovacuum_vacuum_scale_factor to 0 and autovacuum_vacuum_threshold to 100000 triggers an autovacuum for every 100000 row updates. As a general rule of thumb, we recommend autovacuuming at least once every 100000 single-row updates, and running a manual VACUUM immediately after large bulk loads. We also generally recommend vacuum_index_cleanup = on for frequently updated tables with a ParadeDB index. By default, Postgres can skip removing dead index entries when fewer than 2% of heap pages contain dead entries. ParadeDB needs cleanup well below this threshold for optimal query performance. This setting forces dead index entry removal during vacuum:
This must be applied per table. It is not a database-wide setting.

​
Adjusting Target Segment Count

By default, CREATE INDEX/REINDEX targets max(4, max_parallel_workers_per_gather) * max(1, len(partition_by)) segments, where len(partition_by) is the number of partitioning columns. Without partition_by, the multiplier is 1. This can be changed using the target_segment_count index option or the paradedb.global_target_segment_count setting. Explicit targets are not multiplied by the number of partitioning columns.
This property is attached to the index so that during REINDEX, the same value will be used. It can be changed with ALTER INDEX, like so:
However, a REINDEX is required to rebalance the index to that segment count. For optimal performance, the segment count should be at least the number of parallel workers that a query can receive, which is controlled by max_parallel_workers_per_gather. The default target follows this setting. After increasing it, use REINDEX to rebuild existing indexes with the new target.
Actual segment counts may vary depending on parallel builder distribution, memory limits, and table size.When partitioning an index with partition_by, we recommend setting target_segment_count to 2–4X max_parallel_workers_per_gather (or CPU count). Creating more segments allows ParadeDB to skip more non-matching data during filtered queries and better align partition ranges across tables during parallel joins.