Parallel Query in PostgreSQL vs Oracle: What You Gain

Parallel Query: What You Gain Moving to PostgreSQL

In Oracle, parallelism is something you often ask for — a PARALLEL hint or a degree on the table. In modern PostgreSQL, parallel query is automatic: the planner spins up worker processes for big scans, joins, and aggregates whenever the cost model says it'll pay off — no hint required. The migration story here is mostly good news, with a few configuration knobs to know.

What PostgreSQL Parallelizes

Since 9.6 and steadily improved through every release, PostgreSQL parallelizes the operations that matter for analytics and large reports: parallel sequential scans, parallel index & index-only scans, parallel joins (nested loop, hash, merge), and parallel aggregates. A big GROUP BY over a 100M-row table fans out across workers and partial-aggregates in parallel — exactly the workload that used to need an Oracle hint.

EXPLAIN (ANALYZE)
SELECT region, SUM(amount) FROM sales GROUP BY region;

-- You'll see the parallel shape in the plan:
--   Finalize GroupAggregate
--     -> Gather Merge   (Workers Planned: 4)
--        -> Partial GroupAggregate
--           -> Parallel Seq Scan on sales

The Knobs That Control It

Parallelism is on by default, but three settings decide how aggressively it kicks in. The most common post-migration win is raising max_parallel_workers_per_gather from the default of 2 on a box with cores to spare.

-- Max workers a single Gather can use (default 2 — raise on big boxes)
ALTER SYSTEM SET max_parallel_workers_per_gather = 6;

-- Total parallel workers across the whole server
ALTER SYSTEM SET max_parallel_workers = 16;

-- Smallest table the planner will consider scanning in parallel (default 8MB)
ALTER SYSTEM SET min_parallel_table_scan_size = '16MB';
SELECT pg_reload_conf();

Why a Query Might Not Go Parallel

If you expected workers and the plan is serial, the usual reasons: the table is below min_parallel_table_scan_size; the query writes data (most parallel write paths are limited — INSERT … SELECT got parallel support only recently); it uses a construct marked parallel-unsafe (a VOLATILE function, certain CTEs); or the cost estimate simply says serial is cheaper. EXPLAIN with the worker counts tells you which.

Parallel Index Builds — a Real Migration Speedup

Beyond queries, PostgreSQL builds B-tree indexes in parallel — which matters enormously during the post-data step of a migration, where you create all indexes after the bulk load. Give it workers and memory and a large index build that took an hour can drop to minutes.

-- Parallel B-tree build (workers scale with maintenance_work_mem)
SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;
CREATE INDEX CONCURRENTLY idx_sales_region ON sales (region);