Performance Impact of Exception Logic in PostgreSQL Stored Procedures

Summary: This article examines the performance impact of exception handling logic in PostgreSQL stored procedures on overall database performance.

Table of Contents

Test Preparation

As we know, using exception logic in PostgreSQL stored procedures requires subtransactions. We first create a simple function that increases the nesting depth of exception logic based on the specified depth parameter:

CREATE OR REPLACE FUNCTION generate_subtrans_load(p_id int, depth int)
RETURNS void AS $$
BEGIN
    IF depth > 0 THEN
        BEGIN
            PERFORM generate_subtrans_load(p_id, depth - 1);
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
    ELSE
        INSERT INTO penalty_test VALUES (p_id, 'Deep Stack');
    END IF;
END;
$$ LANGUAGE plpgsql;

Next, we use a simple stored procedure to call the above function with exception logic in a loop for testing:

CREATE OR REPLACE PROCEDURE subtrans_test(depth integer)
AS $$
DECLARE
    -- Timing Variables
    v_start_ts  timestamp;
    v_time      interval;

    -- Row Verification
    v_rows   int;

    -- WAL Variables
    v_start_lsn pg_lsn;
	v_end_lsn  pg_lsn;
    v_wal    numeric;

    -- XID Variables
    v_start_xid xid8;
    v_end_xid  xid8;
    v_xid    bigint;

    i int;
    v_target_rows int := 5000;
BEGIN
    DROP TABLE IF EXISTS penalty_test;
    -- Explicitly preserve rows so COMMIT doesn't empty the table
    CREATE TEMP TABLE penalty_test (id int, val text) ON COMMIT PRESERVE ROWS;

    v_start_xid := pg_current_xact_id();
    COMMIT;

    v_start_ts  := clock_timestamp();
    v_start_lsn := pg_current_wal_insert_lsn();

    FOR i IN 1..v_target_rows LOOP
        PERFORM generate_subtrans_load(i, depth);
    END LOOP;

    COMMIT;
    v_end_lsn := pg_current_wal_insert_lsn();
    v_end_xid := pg_current_xact_id();
    v_time    := clock_timestamp() - v_start_ts;

    SELECT count(*) INTO v_rows FROM penalty_test;
    v_wal := v_end_lsn - v_start_lsn;
    v_xid := (v_end_xid::text::bigint - v_start_xid::text::bigint - 1);

    RAISE NOTICE 'Target Rows: %, Depth: %', v_target_rows, depth;
    RAISE NOTICE 'Execution Time & Verification: % (Rows: %)', v_time, v_rows;
    RAISE NOTICE 'Disk Usage (WAL Written): % bytes', v_wal;
    RAISE NOTICE 'Transaction ID Consumption: % XIDs', v_xid;
END $$ LANGUAGE plpgsql;

Performance Testing of Exception Logic in Stored Procedures

We test the following 4 different scenarios:

• No exception (depth 0): Simple insert that inserts a number of rows in a loop with no exception logic.
• Single exception (depth 1): The same insert loop but with an exception block that fires after every insert
• 48-level exception (depth 48): A function call that arbitrarily creates 48 subtransactions (well below the 64 limit) and then completes the same insert.
• 128-level exception (depth 128): A function call that arbitrarily creates 128 subtransactions (well above the 64 limit) and then completes the same insert.

Calling the stored procedure subtrans_test in PostgreSQL with depth = 0 yields the following results:

CALL subtrans_test(0);
NOTICE:  Target Rows: 5000, Depth: 0
NOTICE:  Execution Time & Verification: 00:00:00.02833 (Rows: 5000)
NOTICE:  Disk Usage (WAL Written): 40 bytes
NOTICE:  Transaction ID Consumption: 1 XIDs

We call the stored procedure subtrans_test in PostgreSQL with depth set to 0, 1, 48, and 128 sequentially. The same tests are run in Redrock Postgres, with results collected below:

The test results show that when executing stored procedures in PostgreSQL, transaction ID consumption equals loop count × exception nesting depth, and the generated WAL volume scales accordingly.

Conclusion

From the tests above, using more exception handling logic in stored procedures in PostgreSQL consumes more transaction IDs and generates more WAL data.

In contrast, Redrock Postgres implements subtransactions based on undo log record positions, where subtransactions act only as temporary markers during transaction execution. Subtransactions share the same transaction ID as their parent transaction block and do not require separate transaction ID allocation. As a result, the volume of WAL generated remains stable.