Cascading PostgreSQL function calls - Pass by Reference vs Value

Summary

The question revolves around passing by value vs pass by reference in PostgreSQL function calls, particularly when dealing with large datasets such as jsonb or multiple arrays. The concern is about the efficiency of passing these values through multiple function calls, up to 4-5 levels deep, for calculations on a large number of rows (40-50K).

Root Cause

The root cause of the inefficiency in this scenario is:

Passing by value: When large datasets are passed to functions, they are duplicated at each call level, leading to increased memory usage and potential performance issues.
Deep function calls: The complexity of calculations requiring multiple levels of function calls exacerbates the problem of passing by value.

Why This Happens in Real Systems

This issue occurs in real systems due to:

Complex business logic: The need for intricate calculations that cannot be efficiently incorporated into a single function or query.
Data complexity: Handling large, complex datasets such as jsonb or multiple large arrays.
Scalability: Systems designed to handle a large volume of data and transactions, where efficiency in data processing is crucial.

Real-World Impact

The real-world impact of this issue includes:

Performance degradation: Increased memory usage and computation time due to the duplication of large datasets at each function call level.
Scalability limitations: The system’s ability to handle a large number of transactions or users may be compromised due to the inefficiency in data passing and processing.

Example or Code

CREATE OR REPLACE FUNCTION main_function(data jsonb)
RETURNS integer AS $$
DECLARE
    score integer;
BEGIN
    score := func1(data);
    RETURN score;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION func1(data jsonb)
RETURNS integer AS $$
DECLARE
    score1 integer;
    score2 integer;
BEGIN
    score1 := func1_1(data);
    score2 := func1_2(data);
    RETURN score1 + score2;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION func1_1(data jsonb)
RETURNS integer AS $$
BEGIN
    -- Calculation using data
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION func1_2(data jsonb)
RETURNS integer AS $$
BEGIN
    -- Calculation using data
    RETURN 2;
END;
$$ LANGUAGE plpgsql;

How Senior Engineers Fix It

Senior engineers address this issue by:

Optimizing function calls: Minimizing the number of function calls and the depth of the call stack.
Using efficient data types: Selecting data types that are optimized for the specific use case, reducing the overhead of data passing.
Implementing pass by reference: When possible, using mechanisms that allow for pass by reference, reducing data duplication.
Caching intermediate results: Storing the results of expensive function calls to avoid redundant calculations.

Why Juniors Miss It

Junior engineers might overlook this issue due to:

Lack of experience: Limited exposure to large-scale, complex systems where such inefficiencies become significant.
Insufficient understanding of data passing mechanisms: Not fully grasping the implications of pass by value vs pass by reference in PostgreSQL.
Focus on functionality over performance: Prioritizing getting the system to work over optimizing its performance, leading to potential scalability issues down the line.

Cascading PostgreSQL function calls – Pass by Reference vs Value