Parallel-Safe Function Design

In this lesson, you will learn how to design functions that allow the PostgreSQL query planner to execute your code across multiple CPU cores simultaneously.

The Parallelism Problem

When you run a SELECT statement, PostgreSQL may decide to use "parallel workers" to speed up the process. However, if your query calls a custom function, the planner must decide if that function is safe to run in a separate process. If a function is marked as UNSAFE or even RESTRICTED, the database is forced to abandon parallelism and run the entire query on a single CPU core, which can be a significant performance bottleneck.

Classification of Parallelism

PostgreSQL classifies functions into three safety levels:

1. PARALLEL SAFE: The function does not modify any database state and does not rely on backend-local state (like temporary tables or package variables). It can be safely executed in a parallel worker.
2. PARALLEL RESTRICTED: The function can be executed in parallel, but only in the leader process. It cannot be pushed down to worker processes. This is typical for functions that access temporary tables.
3. PARALLEL UNSAFE: The function is fundamentally incompatible with parallel queries.

Designing for Performance

To ensure your functions are PARALLEL SAFE, they must be strictly functional: given the same input, they should always return the same output without causing side effects.

Consider this example of a safe function:

CREATE OR REPLACE FUNCTION calculate_tax(amount NUMERIC) 
RETURNS NUMERIC AS $$
BEGIN
    RETURN amount * 0.08;
END;
$$ LANGUAGE plpgsql PARALLEL SAFE;

By explicitly declaring PARALLEL SAFE, you permit the planner to include this function in a parallelized query plan. If you omit the PARALLEL attribute, PostgreSQL defaults to UNSAFE, meaning your function will implicitly disable parallel execution for any query that uses it.

Best Practices

• Avoid Global State: Do not attempt to use shared memory or static variables that require coordination between processes.
• Keep it Read-Only: Modifying data inside a function using INSERT, UPDATE, or DELETE makes the function PARALLEL UNSAFE.
• Check the Docs: Always analyze your complex functions; if they don't modify the database or interact with the transaction state, marking them PARALLEL SAFE is an easy win for query performance.

Key Takeaway: Always explicitly define your function’s parallel safety level, as functions defaulting to PARALLEL UNSAFE will silently inhibit the database's ability to scale across multiple CPU cores.