PostgreSQL Parallel Query: Configuration, JIT, and Tuning for Analytics

# postgresql.conf
max_parallel_workers_per_gather = 4   # workers per query node
max_parallel_workers = 8              # total parallel workers system-wide
max_worker_processes = 16             # total background workers
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB

EXPLAIN (ANALYZE, BUFFERS)
SELECT region, sum(amount)
FROM orders
GROUP BY region;

-- Look for:
-- Gather (cost=... rows=... width=...)
--   Workers Planned: 4
--   Workers Launched: 4
--   -> Partial HashAggregate
--        -> Parallel Seq Scan on orders

SET max_parallel_workers_per_gather = 4;
SET parallel_tuple_cost = 0;
SET parallel_setup_cost = 0;
SET min_parallel_table_scan_size = 0;

-- Parallel query is not always faster (small tables, OLTP)
SET max_parallel_workers_per_gather = 0;

-- Or per table
ALTER TABLE small_lookup_table SET (parallel_workers = 0);

CREATE OR REPLACE FUNCTION calculate_discount(price NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE PARALLEL SAFE
AS $$
  SELECT price * 0.9;
$$;

-- Enable JIT for long-running analytical queries
SET jit = on;
SET jit_above_cost = 100000;     -- only for expensive queries
SET jit_inline_above_cost = 500000;
SET jit_optimize_above_cost = 500000;