IMMUTABLE PARALLEL SAFE vs the Rest: How Function Attributes Unlock (or Block) PostgreSQL Parallel Query

Volatility (VOLATILE / STABLE / IMMUTABLE) tells the planner what it can assume about a function's result. Parallel safety (PARALLEL SAFE / PARALLEL RESTRICTED / PARALLEL UNSAFE) tells it something completely different: whether the function is safe to run concurrently, in a background worker process. These are two independent axes, and conflating them is one of the most common reasons a query silently loses its parallel plan.

A Function That Gets Called a Lot

Consider a small, pure calculation used across dozens of reports — say, converting two dates into an age in months:

CREATE FUNCTION age_in_months(birth_date date, ref_date date)
RETURNS integer
LANGUAGE sql
AS $$
  SELECT (EXTRACT(YEAR FROM age(ref_date, birth_date)) * 12
        + EXTRACT(MONTH FROM age(ref_date, birth_date)))::integer
$$;

Without any attributes, this function defaults to VOLATILE and PARALLEL UNSAFE — the most conservative, most restrictive combination PostgreSQL can assume. That default is almost always wrong for a function like this one, and it quietly costs you every time it is called against a large table.

Fixing the Volatility First

The function takes two dates and always returns the same result for the same two dates — no table reads, no session state, no side effects. That is the textbook definition of IMMUTABLE:

ALTER FUNCTION age_in_months(date, date) IMMUTABLE;

IMMUTABLE unlocks constant folding (if both arguments are literals, PostgreSQL computes the result once at plan time) and makes the function eligible for use in a functional index. But it says nothing about parallel workers — that is a separate declaration.

PARALLEL SAFE: A Different Question Entirely

When PostgreSQL considers a parallel plan, it splits the work across background worker processes, each with its own memory and its own connection to shared buffers — but they do not share the same backend-local state as the main process. A function is PARALLEL SAFE only if it can run correctly inside one of those workers: no writes, no use of backend-local sequences or cursors, no reliance on data that only exists in the leader's session. The three categories are:

• PARALLEL UNSAFE (the default) — the function cannot run in a parallel worker at all. If a query needs to call this function, PostgreSQL disables parallelism for the entire query, not just the part that touches the function.
• PARALLEL RESTRICTED — the function can run in a worker, but only the leader process is allowed to actually execute it (e.g., functions that access temporary tables or client-connection state). The query can still go parallel, but this particular function call is pinned to the leader.
• PARALLEL SAFE — the function can run in any worker, with no restrictions. This is what you want for pure, read-only, side-effect-free logic.

For a pure calculation like age_in_months, the correct declaration is:

ALTER FUNCTION age_in_months(date, date) IMMUTABLE PARALLEL SAFE;

Note the two attributes stacked in one statement — they are independent flags, and you generally want to set both correctly at once rather than fixing one and forgetting the other.

Why the Default Silently Hurts You

Here is the part that catches people off guard: PostgreSQL does not throw an error or a warning when a query calls a PARALLEL UNSAFE function. It just quietly falls back to a serial plan. Compare the two:

-- Function left at defaults: VOLATILE, PARALLEL UNSAFE
EXPLAIN SELECT id, age_in_months(birth_date, CURRENT_DATE)
FROM animals;
-- Seq Scan on animals  (no Gather, no workers — forced serial)

-- Function marked IMMUTABLE PARALLEL SAFE
EXPLAIN SELECT id, age_in_months(birth_date, CURRENT_DATE)
FROM animals;
-- Gather  (workers planned: 2)
--   ->  Parallel Seq Scan on animals

Same table, same query, same data — the only difference is the function's declared attributes. On a large table, that missing Gather node is the difference between a scan that uses one CPU core and one that uses four or eight. Nothing in the query itself hints that this is the cause; you have to check \df+ age_in_months or the query's EXPLAIN plan to notice.

Volatility and Parallel Safety Interact, But Are Not the Same

They are set independently, but there is a practical relationship: a function that is genuinely VOLATILE (has side effects, or depends on execution order) is almost never safe to mark PARALLEL SAFE, because parallel workers do not guarantee any particular execution order or count of invocations. In practice:

• IMMUTABLE functions — usually safe to also mark PARALLEL SAFE, since they touch no session or database state.
• STABLE functions that only SELECT from tables — usually safe to mark PARALLEL SAFE too, as long as they do not touch temp tables, sequences, or backend-local settings.
• VOLATILE functions with side effects (writes, nextval(), notifying, logging) — must stay PARALLEL UNSAFE. Marking these PARALLEL SAFE is not an optimization, it is a correctness bug: multiple workers could execute the function concurrently in an order you never intended.

A Checklist Before You ALTER FUNCTION

• Does it read only, with a deterministic result for the same input? → IMMUTABLE.
• Does it read only, but the result can change between statements (e.g., depends on now() or table contents)? → STABLE.
• Does it write, use sequences, or depend on session/connection state? → leave it VOLATILE and PARALLEL UNSAFE. Do not force parallelism here.
• Is it read-only and does not touch temp tables, cursors, or client state? → add PARALLEL SAFE.
• Is it read-only but touches something worker-local like a temp table? → PARALLEL RESTRICTED, not PARALLEL SAFE.

Confirming It Actually Worked

Check the function's declared attributes directly:

SELECT proname, provolatile, proparallel
FROM pg_proc
WHERE proname = 'age_in_months';
-- provolatile: 'i' (immutable)
-- proparallel: 's' (safe)

Then confirm the plan actually changes: run EXPLAIN on the calling query before and after the ALTER FUNCTION, on a table large enough that PostgreSQL would consider parallelism in the first place (small tables never trigger a parallel plan regardless of function attributes — the planner's cost model has a minimum table-size threshold).

Why This Matters at Scale

A single mislabeled helper function used in a handful of admin queries is a rounding error. The same function called from a report that scans millions of rows, once per row, is a query that runs four to eight times slower than it needs to — silently, forever, until someone happens to check EXPLAIN and notices the missing Gather node. PG Monitoring tracks each query's execution plan over time, so when a function's declared attributes change (or were never set correctly) and a query that used to run in parallel drops back to serial, it surfaces as a latency regression on that specific query fingerprint — not a mystery you have to rediscover from scratch.