Deterministic and Nondeterministic Functions in SQL: Understanding Volatility Categories and Their Impact on Query Planning, Caching, and Index Usage

Introduction

You've written what looks like a perfectly reasonable query. It uses an index. Your EXPLAIN output shows a clean index scan. You ship it to production, and then — two weeks later — you notice reports that should return consistent numbers are fluctuating between runs, or a function that should have been called once is being called thousands of times, or worst of all, an index is silently being ignored when your function appears in a WHERE clause.

These are the kinds of bugs that don't announce themselves. They hide in the gap between what you think a function does and what the query planner understands about how that function behaves. The distinction the database makes is called function volatility — a formal classification system that determines whether a function always returns the same output for the same input, might return different values on different calls, or could even have side effects. Getting this wrong means you're either leaving serious performance on the table, introducing subtle correctness bugs, or both simultaneously.

By the end of this lesson, you'll have a deep, practical understanding of how SQL databases categorize function behavior, why those categories exist, what the planner does with that information, and how to apply this knowledge when writing or optimizing queries. This isn't just about avoiding mistakes — it's about making deliberate architectural decisions that scale.

What you'll learn:

• The three volatility categories in SQL (IMMUTABLE, STABLE, VOLATILE) and the semantic contract each one represents
• How the query planner uses volatility to decide when to cache function results, fold them into constants, or evaluate them per-row
• Why nondeterministic functions in WHERE, ORDER BY, and index expressions can destroy performance or produce incorrect results
• How to audit existing functions and fix miscategorized ones safely
• Advanced patterns: exploiting volatility for performance, writing deterministic wrappers, and what happens when you lie to the optimizer

---

Prerequisites

This lesson assumes you're comfortable with:

• SQL query execution and reading EXPLAIN/EXPLAIN ANALYZE output (if you need a refresher, see Query Profiling and Statistics in SQL: Using EXPLAIN ANALYZE, Buffer Metrics, and Row Estimates to Diagnose Slow Queries)
• Writing and reading user-defined functions in at least one major RDBMS
• A working understanding of indexes and when they're used (covered in Indexing Fundamentals for Query Performance: How to Choose, Create, and Evaluate Indexes on Real Tables)
• Basic understanding of query optimization concepts

Examples in this lesson use PostgreSQL syntax primarily, with notes for SQL Server and MySQL behavior where they diverge significantly. The conceptual framework applies universally.

---

The Core Problem: Functions Are Black Boxes (Until You Classify Them)

From a query optimizer's perspective, a function call is a promise. The optimizer needs to decide: Can I call this function once and reuse the result? Can I use this function's output to filter rows before scanning the table? Can I precompute this during plan time?

Without any additional information, the planner has to assume the worst — that every function call might behave differently than the last, might read from external state, might have side effects. This assumption is safe but slow.

The volatility system exists to let you (and the database itself, for built-in functions) communicate stronger guarantees to the planner. Think of it as a contract:

Volatility	Contract	Planner Behavior
IMMUTABLE	Same inputs → same output, forever, with no reference to anything outside the inputs	Can be constant-folded, used in index expressions, aggressively cached
STABLE	Same inputs → same output within a single query, but not necessarily across queries	Can be called once per query and reused; cannot be used in index expressions
VOLATILE	No guarantees whatsoever	Called once per row; never cached; cannot be used in index expressions

Let's build this understanding from the ground up.

---

IMMUTABLE: The Strongest Contract

An IMMUTABLE function is a pure mathematical function in the computer science sense. Given the same arguments, it will always return the same result — not just today, but in ten years on a different machine with a different database state. It cannot query the database, cannot read configuration settings, and cannot call any function that isn't itself IMMUTABLE.

What qualifies as IMMUTABLE?

The classic examples are arithmetic and mathematical operations:

-- Perfectly IMMUTABLE: output depends only on inputs
CREATE OR REPLACE FUNCTION calculate_compound_interest(
    principal NUMERIC,
    annual_rate NUMERIC,
    years INTEGER
)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT principal * POWER(1 + annual_rate, years);
$$;

String manipulation that doesn't depend on locale settings, type conversion functions, and hash functions also qualify. PostgreSQL's built-in lower(), upper(), length(), md5(), and arithmetic operators are all IMMUTABLE.

Constant folding: the planner's superpower

When an IMMUTABLE function is called with literal arguments (not column references), the optimizer can evaluate it at plan time and replace the function call with the resulting constant. This is called constant folding, and it's a significant optimization.

Consider this query on a large transactions table:

SELECT *
FROM transactions
WHERE transaction_date >= DATE_TRUNC('month', TIMESTAMP '2024-01-15 00:00:00');

DATE_TRUNC in PostgreSQL is classified as IMMUTABLE when its argument is a TIMESTAMP (not a TIMESTAMPTZ — more on that distinction shortly). When called with a literal timestamp argument, the planner evaluates DATE_TRUNC('month', TIMESTAMP '2024-01-15 00:00:00') at plan time, substitutes the constant 2024-01-01 00:00:00, and then uses your index on transaction_date to do a clean range scan. The function essentially disappears from the execution plan.

You can verify this yourself:

EXPLAIN SELECT *
FROM transactions
WHERE transaction_date >= DATE_TRUNC('month', TIMESTAMP '2024-01-15 00:00:00');

You'll see the literal constant 2024-01-01 00:00:00 in the plan output — the function call has been folded away entirely.

IMMUTABLE functions and index expressions

The most powerful capability that IMMUTABLE unlocks is use in index expressions (also called functional indexes or expression indexes). These let you index the result of a function applied to a column, so that queries using that same function in their WHERE clause can use the index.

-- Create an index on the lowercase version of the email column
CREATE INDEX idx_customers_email_lower
ON customers (lower(email));

-- This query can now use the index
SELECT customer_id, name
FROM customers
WHERE lower(email) = lower('User@Example.COM');

This works only because lower() is IMMUTABLE. The database needs to be certain that the indexed value will never change unless the underlying column changes. If lower() were STABLE or VOLATILE, the index value might not match what lower(column) returns at query time, making the index unsafe to use for lookups.

Key insight: An expression index is essentially a precomputed, automatically-maintained column. The database maintains it the same way it maintains a regular column index: by recomputing lower(email) when the row is inserted or updated, and storing that result. For this to be valid, the function must be IMMUTABLE — otherwise the stored value might go stale between the insert and the query.

The timezone trap: IMMUTABLE vs STABLE for date functions

Here's a subtlety that causes real production bugs. In PostgreSQL, DATE_TRUNC has two overloads:

-- This overload takes TIMESTAMP (no timezone) — classified as IMMUTABLE
DATE_TRUNC('month', TIMESTAMP '2024-01-15')

-- This overload takes TIMESTAMPTZ (with timezone) — classified as STABLE
DATE_TRUNC('month', TIMESTAMPTZ '2024-01-15 00:00:00+05:30')

Why the difference? Because DATE_TRUNC on a TIMESTAMPTZ value depends on the session's timezone setting. Two sessions with different TimeZone parameters would get different results from the same call. This means it's not IMMUTABLE — the output depends on something beyond the function's inputs. It's STABLE (same within a query, but varies across queries that might use different session settings).

This has a practical consequence: you cannot create an expression index using DATE_TRUNC on a TIMESTAMPTZ column. PostgreSQL will refuse:

-- This WILL fail if event_time is TIMESTAMPTZ
CREATE INDEX idx_events_month
ON events (DATE_TRUNC('month', event_time));
-- ERROR: functions in index expression must be marked IMMUTABLE

The fix is to either cast to TIMESTAMP first (losing timezone awareness) or to store a pre-computed column. This is one of the more common index design frustrations for teams working with temporal data and time-series patterns.

---

STABLE: The Middle Ground

A STABLE function promises that within a single SQL statement, the same inputs will always produce the same output. But that output might vary across different queries in the same session, or across sessions.

What makes a function STABLE?

The canonical STABLE function reads from the database but doesn't modify it, and its output might vary based on session-level parameters or the current database state — but that state doesn't change mid-query.

-- STABLE: reads from the database, but returns the same value
-- for the same input within a single query execution
CREATE OR REPLACE FUNCTION get_customer_tier(p_customer_id INTEGER)
RETURNS TEXT
LANGUAGE sql
STABLE
AS $$
    SELECT tier FROM customer_tiers WHERE customer_id = p_customer_id;
$$;

Note: A function that does a database lookup like the one above is technically only STABLE if the data it reads doesn't change during query execution. In PostgreSQL, this is guaranteed by the transaction snapshot — within a single query, the view of data is consistent. Between queries, another transaction might have updated customer_tiers, so the result could differ.

PostgreSQL's NOW(), CURRENT_TIMESTAMP, and CURRENT_USER are STABLE. They return the same value throughout a query, but can change between queries. This is a commonly misunderstood point:

-- Within this single query, every call to NOW() returns the same timestamp
SELECT order_id,
       NOW() AS report_timestamp,  -- same value for all rows
       created_at,
       NOW() - created_at AS age   -- consistent calculation
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days';

The planner knows it only needs to evaluate NOW() once per query, not once per row. That's a meaningful optimization when you're scanning millions of rows.

What STABLE cannot do

STABLE functions cannot be used in index expressions. The index is a persistent data structure that exists independently of any particular query. There's no meaningful concept of "within a single query" for a persistent index — the index might be read by a query that was planned an hour ago and is only now executing, or it might be read by thousands of concurrent queries at once.

STABLE functions can take advantage of the planner evaluating them once per query, and they can be used in WHERE clauses (though they can't be pushed down to an index scan the way IMMUTABLE expressions can).

---

VOLATILE: The Dangerous Default

VOLATILE is the most restrictive — and the default when you don't specify anything. A VOLATILE function might return different results on every single call, even with identical inputs. It might have side effects. It might modify the database.

Functions that must be VOLATILE

Any function that:

• Modifies data (INSERT, UPDATE, DELETE)
• Uses sequences (nextval(), lastval())
• Calls RANDOM() or similar stochastic functions
• Reads from external sources (file system, network, in some extensions)
• Reads truly volatile state like CLOCK_TIMESTAMP() (wall clock time, not query start time)

CREATE OR REPLACE FUNCTION generate_order_id()
RETURNS TEXT
LANGUAGE sql
VOLATILE  -- correctly marked: uses sequences and random elements
AS $$
    SELECT 'ORD-' || LPAD(NEXTVAL('order_id_seq')::TEXT, 8, '0') ||
           '-' || UPPER(SUBSTR(MD5(RANDOM()::TEXT), 1, 6));
$$;

The per-row evaluation cost

When you call a VOLATILE function in a query, the planner must assume it needs to be called for every row the query touches. Not every row in the result — every row in the scan.

Consider this antipattern:

-- PROBLEMATIC: get_exchange_rate() is VOLATILE (makes network calls)
-- This will be called once per row in the orders table
SELECT
    order_id,
    amount_usd * get_exchange_rate('USD', 'EUR') AS amount_eur
FROM orders
WHERE order_date >= '2024-01-01';

If orders has 10 million rows and your filter leaves 50,000 after the WHERE clause, the planner still can't know that before scanning. And if get_exchange_rate() makes network calls or is just expensive, you've turned a query into a million slow function calls.

If the exchange rate is truly constant within a query context, this function should be STABLE. If it's constant for the entire session, a local variable or CTE is more appropriate:

-- BETTER: compute once, reference many times
WITH exchange_rate AS (
    SELECT get_exchange_rate('USD', 'EUR') AS rate
)
SELECT
    o.order_id,
    o.amount_usd * er.rate AS amount_eur
FROM orders o
CROSS JOIN exchange_rate er
WHERE o.order_date >= '2024-01-01';

This explicitly forces a single evaluation, regardless of what the function's volatility label says. This pattern is useful when you're working with a function you can't modify.

Tip: Using a Common Table Expression to pull a volatile expression to the top of the query is a reliable pattern for forcing single evaluation. The CTE materializes its result once, and subsequent references to it are just lookups into that materialized result set.

---

How the Planner Actually Uses Volatility Information

Understanding the planner's decision tree helps you predict and diagnose performance issues before they happen.

The volatility check in index usage

When the planner considers using an index for a WHERE clause predicate, it checks whether the expression in the predicate is indexable. For an expression index (a functional index), the predicate must use the exact same expression as the index, and that expression must be IMMUTABLE.

-- Setup: expression index on a JSON field extraction
CREATE INDEX idx_orders_metadata_region
ON orders ((metadata->>'region'));

-- The ->> operator on jsonb is IMMUTABLE, so this can use the index
SELECT * FROM orders WHERE metadata->>'region' = 'APAC';

If you tried to use a STABLE function in the same index definition, PostgreSQL would error at index creation time. But the more insidious failure mode is using a VOLATILE or STABLE function in a WHERE clause that should match a regular column index — because the function call prevents the index from being used.

-- orders.email is indexed
-- This CANNOT use the index on email:
SELECT * FROM orders WHERE lower(email) = 'user@example.com';

-- This CAN use the index (the index is on the column directly):
SELECT * FROM orders WHERE email = 'user@example.com';

-- To support case-insensitive lookups WITH an index, you need:
CREATE INDEX idx_orders_email_lower ON orders (lower(email));
SELECT * FROM orders WHERE lower(email) = 'user@example.com';

This is one of the most common SQL performance anti-patterns — wrapping an indexed column in a function call in the WHERE clause. The fix is an expression index, which only works because lower() is IMMUTABLE.

Volatile functions and parallel query

In PostgreSQL's parallel query execution model, the planner won't parallelize queries that contain VOLATILE function calls in certain positions, because the results might not be deterministic across workers. This is a significant performance consideration for analytical workloads on large tables.

-- Check if your function blocks parallelism
EXPLAIN (ANALYZE, VERBOSE)
SELECT COUNT(*), some_volatile_function(category)
FROM large_events_table
GROUP BY some_volatile_function(category);

If you see Workers Planned: 0 in a query that would normally benefit from parallelism, a volatile function in the grouping or filter clause is a likely culprit. Upgrading a correctly-behaving function from VOLATILE to STABLE can unlock parallel execution automatically.

Warning: Upgrading a function's volatility label doesn't change how the function behaves — it changes how the planner treats it. If you mark a truly volatile function as STABLE or IMMUTABLE to force parallelism, you're lying to the optimizer, and that can produce incorrect results. More on this in the "Lying to the Optimizer" section.

Join planning and function volatility

The planner uses volatility when deciding how to order operations in a join. A VOLATILE function on the outer side of a loop join will be evaluated once per row from the outer relation. A STABLE function will be evaluated once and reused. This can dramatically affect join performance:

-- Assume customer_segment_lookup() is a lookup function
-- The volatility label determines whether it's called 1M times or 1 time

SELECT o.order_id, o.amount,
       customer_segment_lookup(o.customer_id) AS segment
FROM orders o
WHERE o.order_date >= CURRENT_DATE - INTERVAL '90 days';

If customer_segment_lookup() is VOLATILE, the planner assumes it must be called once per row. If it's correctly labeled STABLE (reads from the database but is consistent within the query), the planner might batch these lookups or optimize them differently depending on the engine.

---

User-Defined Functions: Getting Volatility Right

When you write a user-defined function, you're responsible for declaring the correct volatility. The database doesn't verify your declaration — it trusts you. This trust can be exploited (dangerously) or lost (by being too conservative).

The cost of being too conservative

Declaring a function as VOLATILE when it's actually STABLE is the most common mistake. The behavior is correct but slow. You lose:

• Parallel query eligibility
• The opportunity for single-per-query evaluation
• Potential for the planner to optimize expressions containing the function

A practical audit of your functions

To find functions that might be miscategorized in PostgreSQL:

SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    CASE p.provolatile
        WHEN 'i' THEN 'IMMUTABLE'
        WHEN 's' THEN 'STABLE'
        WHEN 'v' THEN 'VOLATILE'
    END AS volatility,
    p.prosrc AS function_body
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND p.prokind = 'f'  -- only regular functions, not procedures
ORDER BY n.nspname, p.proname;

Review any VOLATILE functions whose bodies don't contain non-deterministic calls (sequences, RANDOM(), modifications, etc.). Those are candidates for promotion to STABLE or IMMUTABLE.

Testing volatility changes safely

Before changing a function's volatility category, you should have tests that validate its behavior. This is exactly the kind of thing that belongs in a SQL unit testing suite. At minimum, verify:

1. The function returns correct results for known inputs
2. Query plans that reference the function behave as expected after the change
3. Any expression indexes built on the function are still valid

-- Before changing volatility: capture plan for comparison
EXPLAIN (FORMAT JSON)
SELECT * FROM orders WHERE my_function(amount) > 100;

-- Change the function
CREATE OR REPLACE FUNCTION my_function(x NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
STABLE  -- was VOLATILE
AS $$
    SELECT x * (SELECT rate FROM config WHERE key = 'base_multiplier');
$$;

-- Verify the plan changed as expected
EXPLAIN (FORMAT JSON)
SELECT * FROM orders WHERE my_function(amount) > 100;

---

Lying to the Optimizer: Consequences of Incorrect Classification

This section deserves special attention because the consequences range from subtle data corruption to catastrophic failures.

Marking VOLATILE functions as IMMUTABLE

The most dangerous misclassification is marking a VOLATILE function as IMMUTABLE. Consider this example:

-- DANGEROUS: This function reads from a config table
-- but is incorrectly marked IMMUTABLE
CREATE OR REPLACE FUNCTION get_tax_rate(country_code TEXT)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE  -- WRONG: reads from database, will vary as data changes
AS $$
    SELECT rate FROM tax_rates WHERE country = country_code;
$$;

-- Create an expression index using this function
CREATE INDEX idx_orders_tax
ON orders (get_tax_rate(country_code));

When you first create this index, it stores the correct tax rates. But when the tax_rates table is updated, the index values won't be refreshed — because the database thinks the function's output can never change for a given input. Your index now contains stale, incorrect values, and queries using it will return wrong results silently.

Worse, because the function is IMMUTABLE, the planner might constant-fold it during planning, caching the result from plan time and never re-evaluating it even when the underlying data has changed.

Warning: Incorrect IMMUTABLE declarations on functions that read from the database are a form of silent data corruption. The query returns wrong results, not errors. These bugs can persist for a long time because they only manifest when the underlying data changes, and the incorrect results might not be obviously wrong.

Marking STABLE functions as IMMUTABLE

Less dangerous but still problematic is marking a STABLE function as IMMUTABLE when it depends on session parameters:

-- Incorrectly IMMUTABLE: output depends on session locale
CREATE OR REPLACE FUNCTION format_currency(amount NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE  -- WRONG: depends on lc_monetary setting
AS $$
BEGIN
    RETURN TO_CHAR(amount, 'FM$999,999,990.00');
END;
$$;

The planner might constant-fold this with a specific session's locale, then reuse the cached plan in a different session with different settings. The output format would be wrong.

The RANDOM() trap in IMMUTABLE functions

PostgreSQL won't stop you from calling RANDOM() inside an IMMUTABLE function — it trusts your declaration. This is a trap:

-- This compiles without error but is WRONG
CREATE OR REPLACE FUNCTION fake_immutable_random(seed TEXT)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE  -- lies: RANDOM() is VOLATILE
AS $$
    SELECT RANDOM();  -- completely ignores the seed parameter
$$;

If you call this function in a query that scans a million rows, the planner might evaluate it once (because it's "immutable") and use that single value for all rows. The result would look like your random function isn't working — because it isn't.

---

SQL Server and MySQL: Different Terminology, Same Concepts

SQL Server's approach

SQL Server uses the term deterministic vs nondeterministic rather than volatility categories, and the classification applies to built-in functions. You cannot explicitly set a determinism level for user-defined scalar functions (SQL Server infers it based on the function's contents).

For SQL Server user-defined functions (UDFs) to be considered deterministic, they must:

• Not reference any database tables (schema-bound or otherwise) that could change
• Not call nondeterministic built-in functions (GETDATE(), NEWID(), etc.)
• Have SCHEMABINDING enabled

-- SQL Server: deterministic scalar UDF that can be used in indexes
CREATE FUNCTION dbo.CleanPhoneNumber(@phone NVARCHAR(50))
RETURNS NVARCHAR(20)
WITH SCHEMABINDING  -- required for determinism
AS
BEGIN
    RETURN REPLACE(REPLACE(REPLACE(REPLACE(@phone, '-', ''), '(', ''), ')', ''), ' ', '')
END;

-- This can now be used in an index
CREATE INDEX idx_customers_phone_clean
ON dbo.Customers (dbo.CleanPhoneNumber(PhoneNumber));

The WITH SCHEMABINDING clause is critical — without it, SQL Server won't consider the function deterministic, even if it truly is.

MySQL's approach

MySQL doesn't expose volatility categories to user-defined functions directly. MySQL's optimizer uses a simpler heuristic: functions that are recognized as deterministic (either built-in deterministic functions or UDFs declared with the DETERMINISTIC keyword) can be optimized more aggressively.

-- MySQL: explicit DETERMINISTIC declaration
DELIMITER //
CREATE FUNCTION calculate_age(birth_date DATE)
RETURNS INT
DETERMINISTIC  -- explicit declaration
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
END //
DELIMITER ;

Wait — is this actually deterministic? Technically no, because CURDATE() returns different values on different days. MySQL's DETERMINISTIC keyword is closer to PostgreSQL's STABLE than its IMMUTABLE. The optimizer uses this hint to allow certain optimizations and to enable binary logging in some configurations, but it's less aggressive about constant-folding than PostgreSQL.

Note: In MySQL, the DETERMINISTIC keyword also affects binary log behavior. Non-deterministic functions can cause replication issues because the replica can't guarantee it would reproduce the same results by re-executing the statement. Marking a function DETERMINISTIC is both a performance declaration and a replication contract.

---

Advanced Patterns: Exploiting Volatility for Performance

Once you understand the system, you can use it deliberately to unlock specific optimizations.

Wrapping STABLE lookups for index compatibility

Suppose you have a function that does a lookup but you need it in an index expression context. One pattern is to cache the value in a generated column:

-- The function itself is STABLE, can't be indexed
-- Solution: store the computed value as a generated column

ALTER TABLE orders ADD COLUMN region_code TEXT
    GENERATED ALWAYS AS (metadata->>'region') STORED;

-- Generated stored columns use IMMUTABLE expressions
-- and can be indexed normally
CREATE INDEX idx_orders_region ON orders (region_code);

This moves the computation to write time (when rows are inserted/updated) rather than query time, achieving the indexability of an expression index without the IMMUTABLE constraint.

Using volatility to control caching in analytics queries

When you're building complex analytical queries — say, for cohort analysis or retention calculations — you can use volatility strategically to control when expensive calculations happen.

A common pattern is to wrap an expensive computation in a CTE (which materializes once) versus a subquery (which might be inlined and re-executed):

-- This might be re-evaluated multiple times if the optimizer inlines it
WITH active_users AS (
    SELECT user_id
    FROM users
    WHERE status = 'active' AND last_login >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    e.event_type,
    COUNT(*) AS event_count
FROM events e
JOIN active_users au ON e.user_id = au.user_id
WHERE e.event_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY e.event_type;

In PostgreSQL 12+, CTEs are inlined by default unless you add MATERIALIZED:

-- Force materialization: compute active_users exactly once
WITH active_users AS MATERIALIZED (
    SELECT user_id
    FROM users
    WHERE status = 'active' AND last_login >= CURRENT_DATE - INTERVAL '30 days'
)
...

This is conceptually similar to promoting a VOLATILE expression to STABLE — you're making an explicit declaration about reuse.

Partial indexes with STABLE predicates

PostgreSQL allows STABLE functions in partial index predicates (the WHERE clause of the index), though not in index expressions. This is occasionally useful:

-- This works: STABLE function in the WHERE clause of a partial index
-- (PostgreSQL evaluates this at index scan time, not build time)
CREATE INDEX idx_active_sessions
ON sessions (user_id, started_at)
WHERE session_type = 'web' AND expires_at > NOW();

Wait — NOW() is STABLE, not IMMUTABLE. Actually, PostgreSQL is smart enough to allow STABLE functions in index WHERE clauses because they're evaluated at query time, not stored in the index. The index structure still filters correctly at scan time.

---

Hands-On Exercise

Let's work through a realistic scenario. You're a data engineer at a logistics company with these tables:

CREATE TABLE shipments (
    shipment_id BIGINT PRIMARY KEY,
    origin_warehouse_id INTEGER NOT NULL,
    destination_zip TEXT NOT NULL,
    weight_kg NUMERIC(10, 3) NOT NULL,
    shipped_at TIMESTAMPTZ NOT NULL,
    status TEXT NOT NULL
);

CREATE TABLE warehouses (
    warehouse_id INTEGER PRIMARY KEY,
    region TEXT NOT NULL,
    country_code CHAR(2) NOT NULL
);

CREATE TABLE shipping_rates (
    country_code CHAR(2) NOT NULL,
    weight_class TEXT NOT NULL,  -- 'light', 'standard', 'heavy'
    rate_per_kg NUMERIC(8, 4) NOT NULL,
    effective_date DATE NOT NULL,
    PRIMARY KEY (country_code, weight_class, effective_date)
);

You've inherited these functions:

-- Function 1: weight classification
CREATE OR REPLACE FUNCTION classify_weight(weight_kg NUMERIC)
RETURNS TEXT
LANGUAGE sql
VOLATILE  -- is this right?
AS $$
    SELECT CASE
        WHEN weight_kg < 5 THEN 'light'
        WHEN weight_kg < 50 THEN 'standard'
        ELSE 'heavy'
    END;
$$;

-- Function 2: current shipping rate lookup
CREATE OR REPLACE FUNCTION get_shipping_rate(
    p_country_code CHAR(2),
    p_weight_kg NUMERIC
)
RETURNS NUMERIC
LANGUAGE sql
VOLATILE  -- is this right?
AS $$
    SELECT rate_per_kg
    FROM shipping_rates
    WHERE country_code = p_country_code
      AND weight_class = classify_weight(p_weight_kg)
      AND effective_date = (
          SELECT MAX(effective_date)
          FROM shipping_rates
          WHERE country_code = p_country_code
            AND effective_date <= CURRENT_DATE
      );
$$;

-- Function 3: estimated shipping cost
CREATE OR REPLACE FUNCTION estimate_cost(
    p_country_code CHAR(2),
    p_weight_kg NUMERIC
)
RETURNS NUMERIC
LANGUAGE sql
VOLATILE  -- is this right?
AS $$
    SELECT p_weight_kg * get_shipping_rate(p_country_code, p_weight_kg);
$$;

Exercise Tasks:

Task 1: Classify each function correctly. For each one, determine whether it should be IMMUTABLE, STABLE, or VOLATILE, and explain why.

Task 2: Rewrite classify_weight with the correct volatility and create an expression index that will accelerate queries like:

SELECT * FROM shipments WHERE classify_weight(weight_kg) = 'heavy';

Task 3: Write the query below and use EXPLAIN ANALYZE to compare performance before and after correcting the volatility of get_shipping_rate:

SELECT
    s.shipment_id,
    w.country_code,
    estimate_cost(w.country_code, s.weight_kg) AS estimated_cost
FROM shipments s
JOIN warehouses w ON s.origin_warehouse_id = w.warehouse_id
WHERE s.status = 'pending'
  AND s.shipped_at >= CURRENT_DATE - INTERVAL '7 days';

Solutions:

Task 1:

• classify_weight: Should be IMMUTABLE. It depends only on its input parameter and contains no database lookups, no sequence calls, no randomness. Pure math.
• get_shipping_rate: Should be STABLE. It reads from shipping_rates and uses CURRENT_DATE (which is STABLE). The result is consistent within a single query but may change between queries as shipping_rates is updated or dates change.
• estimate_cost: Should be STABLE. It calls get_shipping_rate() which is STABLE, and multiplies by a parameter. The stability propagates from the inner call.

Task 2:

-- Fix the volatility
CREATE OR REPLACE FUNCTION classify_weight(weight_kg NUMERIC)
RETURNS TEXT
LANGUAGE sql
IMMUTABLE  -- corrected
AS $$
    SELECT CASE
        WHEN weight_kg < 5 THEN 'light'
        WHEN weight_kg < 50 THEN 'standard'
        ELSE 'heavy'
    END;
$$;

-- Create expression index
CREATE INDEX idx_shipments_weight_class
ON shipments (classify_weight(weight_kg));

-- Now this query uses the index:
EXPLAIN ANALYZE
SELECT * FROM shipments WHERE classify_weight(weight_kg) = 'heavy';
-- You should see: Index Scan using idx_shipments_weight_class on shipments

Task 3: After correcting get_shipping_rate to STABLE, run EXPLAIN ANALYZE on the main query. You'll observe that the planner now evaluates the shipping rate lookup fewer times, and the query may become eligible for parallel execution.

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting that `VOLATILE` is the default

In PostgreSQL, if you create a function without specifying volatility, it's VOLATILE. Many developers write pure computation functions without realizing this costs them caching, indexability, and parallel query eligibility.

Fix: Make it a code review standard to always explicitly declare volatility, even when you intend VOLATILE. Explicit is better than default.

Mistake 2: Using `NOW()` vs `CLOCK_TIMESTAMP()`

In PostgreSQL, NOW() and CURRENT_TIMESTAMP return the transaction start time — they're STABLE. CLOCK_TIMESTAMP() returns the actual wall clock time at the moment of evaluation — it's VOLATILE. Using CLOCK_TIMESTAMP() in a WHERE clause on a large table means the timestamp slightly advances as rows are scanned, which is almost never what you want and kills caching.

-- STABLE: same timestamp for entire query
WHERE event_time > NOW() - INTERVAL '1 hour'

-- VOLATILE: timestamp changes per-row during scan (almost certainly wrong)
WHERE event_time > CLOCK_TIMESTAMP() - INTERVAL '1 hour'

Mistake 3: Functions with conditional logic across volatility boundaries

A function that sometimes does a database lookup and sometimes doesn't is still STABLE — the maximum volatility of any code path determines the function's overall volatility:

-- This is STABLE, not IMMUTABLE, because it can do a lookup
CREATE OR REPLACE FUNCTION get_tier_multiplier(tier TEXT)
RETURNS NUMERIC
LANGUAGE plpgsql
IMMUTABLE  -- WRONG: the ELSE branch does a lookup
AS $$
BEGIN
    IF tier = 'premium' THEN
        RETURN 2.0;  -- no lookup needed
    ELSE
        RETURN (SELECT multiplier FROM tier_config WHERE tier_name = tier);  -- lookup!
    END IF;
END;
$$;

Even though the lookup branch might rarely execute, the function must be classified by its worst-case behavior.

Mistake 4: Volatility of aggregate functions

User-defined aggregate functions have their own volatility considerations. The state transition function and the final function each have their own volatility, and the aggregate inherits the most volatile of the two. If you're building custom aggregates for statistical analysis, make sure both components are correctly classified.

Mistake 5: Assuming function inlining is free

SQL language functions (using LANGUAGE sql) are candidates for inlining by the PostgreSQL optimizer — the function body is substituted directly into the query, and the planner can optimize across the boundary. PL/pgSQL functions cannot be inlined. This means a STABLE SQL function might get better optimization treatment than an equivalent STABLE PL/pgSQL function. When performance matters, prefer LANGUAGE sql for simple lookup functions.

-- CAN be inlined and optimized through
CREATE FUNCTION get_region(p_id INTEGER) RETURNS TEXT LANGUAGE sql STABLE
AS $$ SELECT region FROM warehouses WHERE warehouse_id = p_id $$;

-- CANNOT be inlined — treated as a black box by the planner
CREATE FUNCTION get_region_plpgsql(p_id INTEGER) RETURNS TEXT LANGUAGE plpgsql STABLE
AS $$
BEGIN
    RETURN (SELECT region FROM warehouses WHERE warehouse_id = p_id);
END;
$$;

Tip: When you need to understand how a function is being treated in a query plan, add VERBOSE to your EXPLAIN output. You'll see whether the function is being inlined, evaluated once, or called per-row. This pairs well with the query profiling techniques covered here.

Mistake 6: Volatility and materialized views

Materialized views are refreshed explicitly and stored as a snapshot. They cannot contain VOLATILE function calls (or more precisely, PostgreSQL will allow it but warn you that auto-refresh can't be used). This is particularly relevant if you're using materialized views for caching complex aggregations. Any non-deterministic element in the view definition makes the cached result potentially stale from the moment it's written.

---

Security Implications of Volatility

Function volatility intersects with security in a subtle but important way. In PostgreSQL, functions can be declared with SECURITY DEFINER, meaning they run with the privileges of the function owner rather than the caller. Combining SECURITY DEFINER with IMMUTABLE or even STABLE creates an important consideration: the planner might evaluate the function at unexpected times or with unexpected context.

If an IMMUTABLE SECURITY DEFINER function is used in a query plan, the planner might evaluate it during query planning (for constant folding), not query execution. The security context during planning might differ from what you expect. This is an edge case, but it's relevant in multi-tenant environments or row-level security setups where the context of evaluation matters.

---

Summary & Next Steps

Function volatility is one of those topics that separates SQL practitioners who write queries that happen to work from those who understand why they work — and can confidently make performance and correctness decisions at scale.

Here's what we covered:

• IMMUTABLE functions depend only on their inputs, enable constant folding, can be used in expression indexes, and enable the most aggressive optimizer behavior. Reserve this for truly pure computations.
• STABLE functions return consistent results within a single query, can be evaluated once and cached per-query, but cannot participate in index expressions. Use this for database lookups and session-parameter-dependent calculations.
• VOLATILE functions make no guarantees and are called once per row. Reserve this for anything with side effects, randomness, or genuine non-determinism.
• Incorrect classification is a form of lying to the optimizer. Being too conservative (VOLATILE when STABLE) loses performance. Being too aggressive (IMMUTABLE when STABLE or VOLATILE) risks silent data corruption.
• Expression indexes unlock powerful query patterns but require IMMUTABLE functions — and this requirement is architecturally meaningful, not just an arbitrary restriction.
• PL/pgSQL vs SQL language affects inlining, with SQL-language functions receiving more aggressive optimization treatment.

Where to go next:

The volatility system interacts deeply with several advanced SQL topics. Understanding how the planner uses function metadata is foundational to query rewriting and optimizer hints. If you're building functions that use string manipulation, the volatility of the underlying string operations affects what you can declare — see SQL string functions and their behavior for the full picture.

For teams building production pipelines where these functions feed into ETL logic, understanding how volatility interacts with incremental load patterns and change data capture will help you avoid subtle consistency bugs when functions like CURRENT_DATE appear in watermark calculations.

Finally, if you're maintaining a library of user-defined functions, making volatility classification part of your SQL unit testing practice — specifically testing that function results are consistent within a query when declared STABLE, and consistent across queries when declared IMMUTABLE — is the only reliable way to catch misclassification before it causes production issues.