Skip to main content

Parallelism tuning

PR-04 expanded pocketPG's tuning surface to cover the cluster-wide parallelism controls that sit around max_parallel_workers_per_gather.

Parameters covered​

  • max_parallel_workers_per_gather
  • max_parallel_workers
  • max_worker_processes
  • max_parallel_maintenance_workers

These recommendations are related but not interchangeable:

  • max_parallel_workers_per_gather controls how many workers one query can request.
  • max_parallel_workers caps how many parallel query workers the cluster can run in total.
  • max_worker_processes is the hard background-worker budget that parallel workers, maintenance workers, and logical-replication workers all share.
  • max_parallel_maintenance_workers controls how much worker headroom heavy maintenance tasks can use.

What pocketPG uses​

  • Observed workload shape — OLTP vs mixed vs OLAP from pg_stat_statements
  • Active parallel worker usage — current parallel worker activity from pg_stat_activity
  • Background-worker budget — max_worker_processes
  • Logical slot count — to avoid recommending parallel ceilings that the global worker budget cannot support
  • Database size — to decide whether heavier maintenance operations justify more parallel maintenance headroom
  • Provider CPU metadata when available — cloud instance shape narrows ceilings when provider credentials are attached

Recommendation boundaries​

info

PostgreSQL does not expose host CPU count from SQL alone. pocketPG does not infer CPU count from max_worker_processes.

Without provider metadata, pocketPG treats PostgreSQL worker settings as a budget constraint, not a hardware detector. That keeps the recommendations honest:

  • raise max_parallel_workers_per_gather only when workload shape supports parallel query
  • raise max_parallel_workers only when the cluster-wide ceiling is tighter than the per-gather setting implies
  • raise max_worker_processes only when the proposed query, maintenance, and replication budgets would otherwise collide

Maintenance parallelism​

max_parallel_maintenance_workers is framed separately from query parallelism. The recommendation is biased toward larger databases, where index builds and reindex operations benefit more from parallel maintenance workers during maintenance windows.

The app does not assume every system should push maintenance parallelism high. Smaller databases often do not justify the extra worker budget.