Volatility classification in PostgreSQL

PostgreSQL database systems often handle complex queries involving user-defined functions that process data and return results. These functions can range from simple calculations to complex data manipulations, each with different behavioral patterns in terms of their output consistency and side effects.

The unpredictability of these functions poses challenges for query optimization and caching. To tackle this, PostgreSQL employs volatility classification, categorizing functions based on their behavior and return value consistency across executions. By labeling functions as IMMUTABLE, STABLE, or VOLATILE, you help the query planner optimize performance by determining when results can be cached and reused or recalculated.

This classification system serves as a vital tool for database optimization, allowing PostgreSQL to balance between performance optimization and result accuracy. Understanding and implementing volatility classification correctly is essential for PostgreSQL developers, because improper use can lead to significant side effects impacting overall database performance.

By properly classifying function volatility, you can achieve several benefits, including:

• Informed decisions about result caching by the query planner
• Improved query performance through optimized execution plans
• Reduced unnecessary function recalculations
• Consistent and predictable query results
• Better resource utilization

To achieve optimal performance, label your functions with the strictest applicable volatility category (IMMUTABLE, STABLE, or VOLATILE). In this post, we discuss different ways you can use volatility classification with functions in PostgreSQL and provide best practices to help you keep your database optimized and develop efficient and reliable database applications.

Understanding volatility classification

PostgreSQL implements a volatility classification system that categorizes functions into three distinct levels, as detailed in the following table.

-- String Functions
length()
upper()
lower()
substr()
replace()
trim()
-- Mathematical Functions
abs()
round()
ceiling()
floor()
mod()
power()

-- Current Transaction Functions
current_user
current_schema
session_user
current_timestamp
-- Aggregate Functions
--(when used with non-volatile inputs)
count()
sum()
avg()
min()
max()

-- Time/Date Functions
timeofday()
clock_timestamp()
-- Random Functions
random()
gen_random_uuid()
-- Sequence Functions
nextval()
currval()
lastval()

Let’s understand each classification in detail through some practical examples.

Immutable

An IMMUTABLE function must not contain any statements that modify the database state and always returns the same result when given the same arguments. The following code snippet is an example of an IMMUTABLE function because its output depends solely on input parameters, with no reliance on database state or external factors, providing consistent results across executions:

CREATE FUNCTION add_numbers(a integer, b integer) 
RETURNS integer AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

IMMUTABLE functions must not contain any DML, DDL, or TCL commands. If you declare a function or procedure as IMMUTABLE, which modifies the database state, then the function or procedure will be created, but the function reports an error when invoked. For example, see the following code:

-- Create immutable function that contains DDL statements
CREATE OR REPLACE FUNCTION fn_create_tab_i()
RETURNS void
LANGUAGE plpgsql IMMUTABLE
AS
$$
BEGIN
  CREATE TABLE test_i(id integer, name varchar(20));
END $$;

We get the following output:

CREATE FUNCTION

If you run the function, you get the following error:

SELECT fn_create_tab_i();
ERROR:  CREATE TABLE is not allowed in a non-volatile function
CONTEXT:  SQL statement "CREATE TABLE test_i(id integer, name varchar(20))"
PL/pgSQL function fn_create_tab_i() line 3 at SQL statement

Stable

Similar to IMMUTABLE functions, a STABLE function must also not contain any statements that modify the database state. The difference is that STABLE functions return consistent results within a single table scan but might vary across transactions. STABLE functions must not contain any DML, DDL, or TCL, commands, but they can contain SELECT statements that query database tables. Because the database table state can change between transactions, STABLE function results might vary for different transactions.

Let’s explore an example to understand STABLE function behavior:

-- create the users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100),
    role VARCHAR(50)
);
-- Insert sample data
INSERT INTO users (username, role) VALUES
    ('john_doe', 'ADMIN'),
    ('jane_smith', 'USER'),
    ('bob_wilson', 'MANAGER'),
    ('alice_brown', 'USER');
-- create the function that references this table
CREATE OR REPLACE FUNCTION get_user_role(user_id integer) 
RETURNS text AS $$
BEGIN
    RETURN (SELECT role FROM users WHERE id = user_id);
END;
$$ LANGUAGE plpgsql STABLE;

Let’s test the code and check the function output:

-- Demonstrate STABLE behavior
-- Check initial role
SELECT get_user_role(1);  -- Returns 'ADMIN'
    
-- Update role
UPDATE users SET role = 'USER' WHERE id = 1;
    
-- Check role again in same transaction
SELECT get_user_role(1);  -- Returns 'USER'

Now let’s understand why the function is defined as STABLE and not IMMUTABLE. IMMUTABLE would be incorrect for the following reasons:

• Data dependency – The function depends on table data that can change SELECT role FROM users WHERE id = user_id. Because of this, the same input doesn’t guarantee you will get the same output across transactions. Table data can be modified by other transactions. Even with the same input user_id, the function could return different results if the underlying role data changes. This violates the fundamental principle of IMMUTABLE functions, which must return the same output for the same input every time.
• Transaction context – Within the same transaction, results are reused as long as there is no database state change. This is appropriate because we want to see consistent data within our transaction. However, using IMMUTABLE would be incorrect because it would cache the result permanently. IMMUTABLE caching could lead to stale data being returned even after the role has been updated in the table.

See the following code:

-- Within same transaction, results are consistent
BEGIN;
    SELECT get_user_role(1);  -- Returns 'ADMIN'
    SELECT get_user_role(1);  -- Still returns 'ADMIN'
    -- Results cached within transaction
COMMIT;

Volatile

A VOLATILE function can do anything, including modifying the database. It can return different results on successive calls with the same input arguments. The optimizer makes no assumptions about the behavior of such functions. When a function is labeled as VOLATILE, the optimizer treats its results as unpredictable and potentially changing with each call. To provide accuracy, the optimizer will rerun the function every time it’s encountered in a query, rather than relying on cached or precomputed results. See the following code:

-- Create the employees table
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);
-- Insert data into the employees table
INSERT INTO employees (employee_id, employee_name, department, salary)
VALUES
    (101, 'John Doe', 'IT', 50000.00),
    (102, 'Jane Smith', 'HR', 60000.00),
    (103, 'Michael Johnson', 'Finance', 70000.00),
    (104, 'Emily Brown', 'Marketing', 55000.00),
    (105, 'William Taylor', 'Operations', 65000.00);
-- Create a function to insert employee data if the employee does not exist
CREATE OR REPLACE FUNCTION fnc_insert_emp_data(i_emp_id int, i_emp_name text, i_department text, i_salary numeric)
RETURNS void AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM employees WHERE employee_id = i_emp_id) THEN
        INSERT INTO employees (employee_id, employee_name, department, salary)
        VALUES (i_emp_id, i_emp_name, i_department, i_salary);
        RAISE NOTICE 'Employee data inserted successfully.';
    END IF;
END; 
$$ LANGUAGE plpgsql VOLATILE;
-- Call the function with a non-existing employeeID
SELECT fnc_insert_emp_data(200, 'Allen Tie', 'IT', 55555.00);
NOTICE:  Employee data inserted successfully.
 fnc_insert_emp_data
---------------------
(1 row)

In this example, fnc_insert_emp_data is VOLATILE because the function alters the database state if the employee doesn’t exist and the optimizer will run this function every time it’s called.

Function volatility in transactions

In PostgreSQL, you can start a transaction using BEGIN. Anything within the transaction follows the ACID property (Atomicity, Consistency, Isolation, Durability). Although functions and procedures have different volatility categories, their behavior might change with respect to the transaction. Let’s dive deeper into each function volatility type with examples.

IMMUTABLE functions always return the same result for the same input, regardless of whether they’re in a transaction or not:

-- IMMUTABLE functions return the same result
BEGIN; -- Transaction start
BEGIN
SELECT lower('PostgreSQL');
   lower
------------
 postgresql
(1 row)
-- Sleep for 1 sec to show same result when invoked second time
SELECT pg_sleep(1);
 pg_sleep
----------
(1 row)
-- Same output as previous
SELECT lower('PostgreSQL');
   lower
------------
 postgresql
(1 row)
ROLLBACK; -- Transaction end
ROLLBACK

STABLE functions, like the function now(), maintain consistency within a transaction but might change between transactions. Let’s explore the behavior of STABLE functions within transactions in PostgreSQL:

-- STABLE functions return the same result within transaction unless there is a change in database state.
BEGIN; -- Transaction start
BEGIN
show timezone;
   TimeZone
--------------
 Asia/Kolkata
(1 row)
-- Displays the current timestamp
SELECT now();
               now
----------------------------------
 2025-05-22 10:27:33.613453+05:30
(1 row)
-- Sleep for 1 sec to show same result when invoked second time
SELECT pg_sleep(1);
 pg_sleep
----------
(1 row)
-- Same output as previous as no database state change
SELECT now();
               now
----------------------------------
 2025-05-22 10:27:33.613453+05:30
(1 row)
-- Alter database timezone
set timezone to 'CET';
SET
show timezone;
 TimeZone
----------
 CET
(1 row)
-- Different result within same transaction as database state is changed
SELECT now();
              now
-------------------------------
 2025-05-22 06:57:33.613453+02
(1 row)
ROLLBACK; -- Transaction end
ROLLBACK

By defining now() as STABLE, PostgreSQL acknowledges that its behavior can change depending on the context, even if the transaction or input remains the same. This distinction is important for optimization and caching purposes, because IMMUTABLE functions can be safely cached and reused, whereas STABLE and VOLATILE functions require more careful consideration.

Unlike IMMUTABLE and STABLE functions, VOLATILE functions are run every time, because they can return different results on each call. Understanding these distinctions is important for writing efficient and predictable database operations. For example, the built-in functions random() and clock_timestamp() are inherently non-deterministic, meaning their output will always vary, even when called within a transaction:

-- VOLATILE functions run on each function call
BEGIN; -- Transaction start
BEGIN
-- Displays the current timestamp
SELECT random();
       random
--------------------
 0.4286588847863162
(1 row)
-- Sleep for 1 sec to show different result when invoked second time
SELECT pg_sleep(1);
 pg_sleep
----------
(1 row)
-- Generates different result each time
SELECT random();
       random
--------------------
 0.5273124512071314
(1 row)
ROLLBACK; -- Transaction end
ROLLBACK

Sometimes, you might declare functions as IMMUTABLE or STABLE without realizing that some expressions or called functions are neither IMMUTABLE nor STABLE. For example, in the following code block, the get_timestamp() function is labeled as IMMUTABLE, indicating that its result depends only on the input parameters (none in this case):

-- Incorrect use of IMMUTABLE
CREATE OR REPLACE FUNCTION get_timestamp()
RETURNS timestamp
AS $$
BEGIN
    RETURN now();
END;
$$ LANGUAGE plpgsql IMMUTABLE;

On first look, the function code looks fine, but it actually returns the current timestamp, which depends on the TimeZone setting, a configuration parameter. The problem with this example is that it calls the built-in now() function, which is defined as STABLE in the PostgreSQL catalog. As a general rule, IMMUTABLE PostgreSQL functions must not invoke non-IMMUTABLE functions, because non-IMMUTABLE functions like STABLE or VOLATILE can change their values within transactions, which contradicts the IMMUTABLE definition.

In this example, if the TimeZone setting changes, the result of the get_timestamp function will also change. This violates the contract of an IMMUTABLE function, which promises consistent results for identical input parameters. See the following code:

-- Correct use of STABLE declaration as inside function now() is STABLE
CREATE OR REPLACE FUNCTION get_timestamp()
RETURNS timestamp
AS $$
BEGIN
    RETURN now();
END;
$$ LANGUAGE plpgsql STABLE;

By labeling the function as STABLE instead of IMMUTABLE, we acknowledge that its result depends on TimeZone settings. This way, PostgreSQL will recalculate the function result if the TimeZone setting changes, providing correctness and avoiding unexpected behavior.

Remember, when in doubt about whether a function should be IMMUTABLE, it’s safer to use STABLE or VOLATILE instead. Incorrectly marking a function as IMMUTABLE can lead to incorrect query results that are hard to debug.

Key considerations for determining function volatility

This section provides a comprehensive explanation of when to use IMMUTABLE, STABLE, and VOLATILE functions in PostgreSQL.

Define a function as IMMUTABLE for the following cases:

• String manipulations (like upper(), lower(), or substr())
• Mathematical calculations
• Pure computational operations
• Functions that only work with their input parameters
• Date/time format conversions with constant timestamp values (not current time)
• The function only invokes other IMMUTABLE functions

Define STABLE for the following cases:

• When the function results depend on database lookups or information that remains constant throughout a transaction
• Functions that read from the database but don’t modify data, typically SELECT statements
• Results should be consistent within the same transaction when called with the same arguments
• Invoked functions are either IMMUATBLE or STABLE functions inside the function body

Define VOLATILE for the following cases:

• When results can change within the same transaction
• Use for functions that modify database state or depend on external factors
• Results might vary even with the same inputs and within the same transaction, for example random()
• This is the default volatility category if none is specified

IMMUTABLE functions can be optimized most aggressively by PostgreSQL, whereas VOLATILE functions need to be reevaluated every time. STABLE functions provide a middle ground, allowing for some optimization while still depending on database state. The following section provides more clarity on function volatility optimization.

Use the strictest volatility category to optimize functions

This section highlights the importance of understanding the performance differences between IMMUTABLE, STABLE, and VOLATILE functions in PostgreSQL, and how you can use this knowledge to optimize database performance and improve query efficiency. For best optimization results, you should label your functions with the strictest volatility category that is valid for them. This means you should use IMMUTABLE or STABLE wherever appropriate, rather than defaulting to VOLATILE. This is one of the optimization techniques that can boost functions performance in PostgreSQL.

Let’s examine this through a practical example:

-- Create a VOLATILE function that does some calculation
CREATE OR REPLACE FUNCTION calculation_volatile(x integer)
RETURNS numeric AS $$
BEGIN
    -- Simulate some mathematical calculation
    RETURN power(x, 3) + sqrt(x) + ln(x + 1);
END;
$$ LANGUAGE plpgsql VOLATILE;

Because this use case doesn’t depend on the database state or database configuration parameters, you could create the function as IMMUTABLE:

-- Create the function as IMMUTABLE
CREATE OR REPLACE FUNCTION calculation_immutable(x integer)
RETURNS numeric AS $$
BEGIN
    -- Same calculation
    RETURN power(x, 3) + sqrt(x) + ln(x + 1);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

You can verify the performance difference by running both functions many times in a row and comparing their runtimes:

SELECT count(calculation_immutable(100)) FROM generate_series(1,10000);
 count
-------
 10000
(1 row)
Time: 3.205 ms
SELECT count(calculation_volatile(100)) FROM generate_series(1,10000);
 count
-------
 10000
(1 row)
Time: 22.363 ms

The output shows that the IMMUTABLE function took 3 milliseconds, whereas the VOLATILE function took 22 milliseconds—an 86% improvement just by correctly classifying the volatility category.

This example demonstrates how IMMUTABLE functions can significantly improve performance.

During the planning phase, PostgreSQL recognizes the function is IMMUTABLE. It evaluates calculation_immutable(100) one time and caches the result value. During execution, PostgreSQL uses the cached value for all 1,000 rows. There are no repeated calculations; it returns the same pre-calculated value 1,000 times.

This distinction is relevant for large-scale applications and complex queries, where even slight improvements in runtime can have a significant impact on overall performance. Always analyze before you decide the volatility category of a function or procedure. Using the incorrect volatility category of a function in PostgreSQL can lead to stale data, meaning the system might cache and reuse outdated results instead of recalculating them when needed. This can cause inconsistencies, data accuracy issues, and potential business decisions based on outdated information. Let’s dive deeper into this in the next section.

Incorrect function volatility category side effects

In this section, we discuss how volatility classification might have unintended consequences or side effects when incorrectly used, and why it’s important to be aware of these potential risks.

PostgreSQL doesn’t issue warnings when an IMMUTABLE function executes a SELECT statement on a table. However, because IMMUTABLE functions assume unchanged data, modifying the table can lead to inconsistent results due to cached query plans. If a function is mistakenly marked as IMMUTABLE instead of STABLE, it might not reflect real-time data changes, leading to unreliable outputs in queries and reports. See the following code:

-- Create the employees table
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);
-- Insert data into the employees table
INSERT INTO employees (employee_id, employee_name, department, salary)
VALUES
    (101, 'John Doe', 'IT', 50000.00),
    (102, 'Jane Smith', 'HR', 60000.00),
    (103, 'Michael Johnson', 'Finance', 70000.00),
    (104, 'Emily Brown', 'Marketing', 55000.00),
    (105, 'William Taylor', 'Operations', 65000.00);
-- Function queries from database table and incorrectly classified to IMMUTABLE
CREATE OR REPLACE FUNCTION fnc_check_emp_existence_i(i_emp_id int)
RETURNS boolean AS $$
    SELECT EXISTS (
        SELECT 1 FROM employees WHERE employee_id = i_emp_id
    );
$$ LANGUAGE sql IMMUTABLE;
-- Same function classified as STABLE is correct
CREATE OR REPLACE FUNCTION fnc_check_emp_existence_s(i_emp_id int)
RETURNS boolean AS $$
    SELECT EXISTS (
        SELECT 1 FROM employees WHERE employee_id = i_emp_id
    );
$$ LANGUAGE sql STABLE;

Let’s create prepared statements and see how incorrect function labeling can produce unreliable outputs:

-- Create prepare statements for an employee that does not exists
prepare non_exists_i as select fnc_check_emp_existence_i(200);
prepare non_exists_s as select fnc_check_emp_existence_s(200);

Because employee_id = 200 doesn’t exist in Employee, the result for both preceding statements must be False. Let’s test and verify the output:

execute non_exists_i;
 fnc_check_emp_existence_i
---------------------------
 f
(1 row)
execute non_exists_s;
 fnc_check_emp_existence_s
---------------------------
 f
(1 row)

Now let’s change the database table state by inserting one row for employee_id=200 and see how the IMMUATBLE and STABLE functions behave:

-- Insert a new employee with ID 200
INSERT INTO employees VALUES (200, 'Allen Tie', 'IT', 5555.00);
-- Incorrect output as result was cached and shows same pre-calculated value
execute non_exists_i;
 fnc_check_emp_existence_i
---------------------------
 f
(1 row)
-- Correct output as STABLE functions get data from database table for each statement
execute non_exists_s;
 fnc_check_emp_existence_s
---------------------------
 t
(1 row)

This example shows why functions that depend on table data should be marked as STABLE rather than IMMUTABLE, because it affects both data accuracy and query performance. Therefore, using the correct volatility category for functions is critical for maintaining data accuracy and integrity, especially when using prepared statements in PostgreSQL.

PostgreSQL stores function metadata, including volatility categories, in system catalog tables, making it possible to query and verify a function’s volatility. You can check if a function is IMMUTABLE, STABLE, or VOLATILE. Let’s dive deeper into querying this information in next section.

Check the volatility classification of a function

You can use the system catalog pg_proc to check the volatility classification of a function in the column provolatile. The values are as follows:

• i indicates IMMUTABLE functions
• s indicates STABLE functions
• v indicates VOLATILE functions

See the following code:

SELECT proname, pronamespace::regnamespace, provolatile
FROM pg_proc
WHERE proname in ('translate', 'now', 'random', 'clock_timestamp') 
ORDER BY 3;
     proname     | pronamespace | provolatile
-----------------+--------------+-------------
 translate       | pg_catalog   | i
 now             | pg_catalog   | s
 clock_timestamp | pg_catalog   | v
 random          | pg_catalog   | v
(4 rows)

Alternatively, you can use the function pg_get_functiondef() to check the volatility of an individual function:

select pg_get_functiondef('pg_catalog.translate'::regproc)
                        pg_get_functiondef                         
-------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION pg_catalog.translate(text, text, text)+
  RETURNS text                                                    +
  LANGUAGE internal                                               +
  IMMUTABLE STRICT                                                +
 AS $function$translate$function$                                 +
(1 row)

You can also use the psql terminal to check function volatility classifications and other details:

\df+ clock_timestamp
                                                                                            List of functions
   Schema   |      Name       |     Result data type     | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language |  Internal name  |    Description
------------+-----------------+--------------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+-----------------+--------------------
 pg_catalog | clock_timestamp | timestamp with time zone |                     | func | volatile   | safe     | postgres | invoker  |                   | internal | clock_timestamp | current clock time
(1 row)

Best practices

The following are some best practices for using VOLATILE, IMMUTABLE, and STABLE functions in PostgreSQL:

• Choose the correct volatility category based on the function’s behavior and dependencies.
• A function must be labeled VOLATILE if its value can change within a single query or has side effects. Some built-in functions examples are random(), currval(), and timeofday().
• A function must be labeled IMMUTABLE if it depends only on its input parameters and doesn’t modify the external state or rely on volatile data. This provides predictability and consistency, making the function suitable for optimization and caching.
• A function must be labeled STABLE when it depends on configuration parameters or the query contains only SELECT statements (no DDL, DML, or TCL commands).
• If the function is declared IMMUTABLE, then the expression must not invoke any non-IMMUTABLE functions or operators.
• If the function is declared STABLE, then the expression must not invoke any VOLATILE functions or operators.
• When in doubt about whether a function should be IMMUTABLE, it’s safer to use STABLE or VOLATILE instead.

Conclusion

In this post, we discussed how properly categorizing functions as VOLATILE, IMMUTABLE, or STABLE allows PostgreSQL’s optimizer to make informed decisions, leading to more efficient query plans. VOLATILE functions offer freshness, STABLE functions balance predictability with flexibility, and IMMUTABLE functions enable the highest degree of optimization through result caching and precomputation. Understanding and using these categories effectively can greatly enhance the performance and scalability of PostgreSQL-based applications.

If you have any questions or feedback about these functions and how to implement them, leave a comment.

About the Authors