Skip to main content
Background Image
  1. PostgreSQL Posts/

Function Volatility Classification Levels

·644 words·4 mins· ·
Ruohang Feng
Author
Ruohang Feng
Pigsty Founder, @Vonng
Table of Contents

PostgreSQL functions have three volatility levels by default. Proper use can significantly improve performance.

Core Differences
#

  • VOLATILE: Has side effects, cannot be optimized.
  • STABLE: Executes database queries.
  • IMMUTABLE: Pure function, execution results may be pre-evaluated and cached during planning.

When to Use?
#

  • VOLATILE: Any writes, any side effects, needs to see changes made by external commands, or calls any VOLATILE function
  • STABLE: Has database queries but no writes, or function results depend on configuration parameters (e.g., timezone)
  • IMMUTABLE: Pure function.

Detailed Explanation
#

Each function carries a volatility level. Possible values include VOLATILE, STABLE, and IMMUTABLE. If no volatility level is specified when creating a function, it defaults to VOLATILE. Volatility is the function’s promise to the optimizer:

  • VOLATILE functions can do anything, including modifying database state. They may return different results on consecutive calls even with the same arguments. The optimizer won’t optimize away such functions; they are re-evaluated every time they’re called.
  • STABLE functions cannot modify database state, and guarantee that given the same arguments within a single statement, they will return the same result. Therefore, the optimizer can optimize multiple calls with the same parameters into a single call. STABLE functions are allowed in index scan conditions, but VOLATILE functions are not. (In an index scan, comparison values are evaluated only once, not once per row, so VOLATILE functions cannot be used in index scan conditions).
  • IMMUTABLE functions cannot modify database state and guarantee that given inputs will always return the same result at any time. This classification allows the optimizer to pre-compute the function when it’s called with constant parameters in a query. For example, a query like SELECT ... WHERE x = 2 + 2 can be simplified to SELECT ... WHERE x = 4 because the underlying function of the integer addition operator is marked as IMMUTABLE.

Difference Between STABLE and IMMUTABLE
#

Call Count Optimization
#

Take this function as an example, which simply returns the constant 2:

CREATE OR REPLACE FUNCTION return2() RETURNS INTEGER AS
$$
BEGIN
RAISE NOTICE 'INVOKED';
RETURN 2;
END;
$$ LANGUAGE PLPGSQL STABLE;

When using the STABLE tag, it actually calls 10 times, but when using the IMMUTABLE tag, it’s optimized to a single call.

vonng=# select return2() from generate_series(1,10);
NOTICE:  INVOKED
NOTICE:  INVOKED
NOTICE:  INVOKED
NOTICE:  INVOKED
NOTICE:  INVOKED
NOTICE:  INVOKED
NOTICE:  INVOKED
NOTICE:  INVOKED
NOTICE:  INVOKED
NOTICE:  INVOKED
 return2
---------
       2
       2
       2
       2
       2
       2
       2
       2
       2
       2
(10 rows)

Here we change the function tag to IMMUTABLE:

CREATE OR REPLACE FUNCTION return2() RETURNS INTEGER AS
$$
BEGIN
RAISE NOTICE 'INVOKED';
RETURN 2;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

Running the same query again, this time the function is called only once:

vonng=# select return2() from generate_series(1,10);
NOTICE:  INVOKED
 return2
---------
       2
       2
       2
       2
       2
       2
       2
       2
       2
       2
(10 rows)

Execution Plan Caching
#

The second example concerns function calls in index conditions. Suppose we have a table containing integers from 1 to 1000:

create table demo as select * from generate_series(1,1000) as id;
create index idx_id on demo(id);

Now create an IMMUTABLE function mymax:

CREATE OR REPLACE FUNCTION mymax(int, int)
RETURNS int
AS $$
BEGIN
     RETURN CASE WHEN $1 > $2 THEN $1 ELSE $2 END;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;

We’ll find that when we use this function directly in index conditions, the index condition in the execution plan is directly evaluated, cached, and solidified as id=2:

vonng=# EXPLAIN SELECT * FROM demo WHERE id = mymax(1,2);
                               QUERY PLAN
------------------------------------------------------------------------
 Index Only Scan using idx_id on demo  (cost=0.28..2.29 rows=1 width=4)
   Index Cond: (id = 2)
(2 rows)

But if we change it to a STABLE function, the result becomes runtime evaluation:

vonng=# EXPLAIN SELECT * FROM demo WHERE id = mymax(1,2);
                               QUERY PLAN
------------------------------------------------------------------------
 Index Only Scan using idx_id on demo  (cost=0.53..2.54 rows=1 width=4)
   Index Cond: (id = mymax(1, 2))
(2 rows)

Related

Distinct On: Remove Duplicate Data
·557 words·3 mins
Use Distinct On extension clause to quickly find records with maximum/minimum values within groups
Implementing Mutual Exclusion Constraints with Exclude
·854 words·5 mins
Exclude constraint is a PostgreSQL extension that can implement more advanced and sophisticated database constraints.
Implementing Cache Synchronization with Go and PostgreSQL
·1234 words·6 mins
Cleverly utilizing PostgreSQL’s Notify feature, you can conveniently notify applications of metadata changes and implement trigger-based logical replication.
Auditing Data Changes with Triggers
·477 words·3 mins
Sometimes we want to record important metadata changes for audit purposes. PostgreSQL triggers can conveniently solve this need automatically.
UUID Properties, Principles and Applications
·1683 words·8 mins
UUID properties, principles and applications, and how to manipulate UUIDs using PostgreSQL stored procedures.
PostgreSQL Routine Maintenance
·130 words·1 min
Cars need oil changes, databases need maintenance. For PG, three important maintenance tasks: backup, repack, vacuum