PG18 Hacktober:31 Days of New Features – Harnessing OLD and NEW Row Values with RETURNING Clause

Welcome to the Day13 of the PG18 Hacktober !!

PostgreSQL 18 introduces a powerful enhancement to DML operations: the ability to directly return both “OLD” and “NEW” values from modified rows across INSERT, UPDATE, DELETE, and MERGE statements. This feature streamlines audit logging, change tracking, and complex application logic by capturing before-and-after states within a single query.

Pre PostgreSQL 18

Prior to PostgreSQL 18, the RETURNING clause could only provide the resulting values of a DML statement; to obtain what a value was before an update, developers needed either a separate SELECT or a trigger workaround.

PostgreSQL 18

Now, with special aliases OLD and NEW, you can retrieve both previous and current values directly within the DML statements such as insert, delete, update and merge.

Behaviour for Different DML Operations

DML Type	Output (OLD / NEW)
INSERT	OLD = NULL / NEW = inserted values
UPDATE	OLD = pre-update / NEW = post-update
DELETE	OLD = deleted values / NEW = NULL
MERGE	OLD = pre-change (when matched) / NEW = post-change

Let’s check it out for each DML operation

#Create a table 
postgres=# CREATE TABLE employees (
emp_id TEXT PRIMARY KEY,
name TEXT,
department TEXT
);
CREATE TABLE

#Check for Insert
postgres=# INSERT INTO employees(emp_id, name, department)
VALUES ('e001', 'Alice', 'HR')
RETURNING OLD.emp_id AS prev_emp_id, OLD.name AS prev_name, OLD.department AS prev_dept,
         NEW.emp_id AS new_emp_id, NEW.name AS new_name, NEW.department AS new_dept;
-[ RECORD 1 ]------
prev_emp_id |
prev_name   |
prev_dept   |
new_emp_id  | e001
new_name    | Alice
new_dept    | HR

INSERT 0 1

#Check for Update 
postgres=# UPDATE employees
SET department = 'Finance'
WHERE emp_id = 'e001'
RETURNING OLD.emp_id AS prev_emp_id, OLD.name AS prev_name, OLD.department AS prev_dept,
         NEW.emp_id AS new_emp_id, NEW.name AS new_name, NEW.department AS new_dept;
-[ RECORD 1 ]--------
prev_emp_id | e001
prev_name   | Alice
prev_dept   | HR
new_emp_id  | e001
new_name    | Alice
new_dept    | Finance

UPDATE 1

#Check for merge
postgres=# MERGE INTO employees e
USING (VALUES ('e001', 'Alice', 'Legal'), ('e002', 'Bob', 'IT')) AS v(emp_id, name, department)
ON e.emp_id = v.emp_id
WHEN MATCHED THEN
  UPDATE SET name = v.name, department = v.department
WHEN NOT MATCHED THEN
  INSERT (emp_id, name, department) VALUES (v.emp_id, v.name, v.department)
RETURNING OLD.emp_id AS prev_emp_id, OLD.name AS prev_name, OLD.department AS prev_dept,
         NEW.emp_id AS new_emp_id, NEW.name AS new_name, NEW.department AS new_dept;
-[ RECORD 1 ]--------
prev_emp_id | e001
prev_name   | Alice
prev_dept   | Finance
new_emp_id  | e001
new_name    | Alice
new_dept    | Legal
-[ RECORD 2 ]--------
prev_emp_id |
prev_name   |
prev_dept   |
new_emp_id  | e002
new_name    | Bob
new_dept    | IT

MERGE 2

#Check for Delete
postgres=# DELETE FROM employees
WHERE emp_id = 'e001'
RETURNING OLD.emp_id AS prev_emp_id, OLD.name AS prev_name, OLD.department AS prev_dept,
         NEW.emp_id AS new_emp_id, NEW.name AS new_name, NEW.department AS new_dept;
-[ RECORD 1 ]------
prev_emp_id | e001
prev_name   | Alice
prev_dept   | Legal
new_emp_id  |
new_name    |
new_dept    |

DELETE 1

Key insights:

• For INSERT, OLD values are NULL and NEW values reflect the newly inserted employee.
• For UPDATE, both OLD and NEW display the previous and current states of the employee row.
• For DELETE, OLD shows the deleted employee, while NEW is NULL.
• For MERGE, UPDATE actions show both OLD and NEW; INSERT actions have NULL for OLD and values for NEW.

INSERT on conflicts

The enhanced RETURNING clause is especially valuable in INSERT ... ON CONFLICT operations, where the OLD alias refers to the existing row causing the conflict. This capability enables updating existing records while capturing their original state within the same statement, simplifying audit and change tracking.

postgres=# select * from employees ;
-[ RECORD 1 ]----
emp_id     | e002
name       | Bob
department | IT

postgres=# INSERT INTO employees(emp_id, name, department)
VALUES ('e003', 'John', 'Finance')
ON CONFLICT (emp_id) DO UPDATE
SET name = EXCLUDED.name,
    department = EXCLUDED.department
RETURNING
  OLD.emp_id AS prev_emp_id, OLD.name AS prev_name, OLD.department AS prev_dept,
  NEW.emp_id AS new_emp_id, NEW.name AS new_name, NEW.department AS new_dept;
-[ RECORD 1 ]--------
prev_emp_id |
prev_name   |
prev_dept   |
new_emp_id  | e003
new_name    | John
new_dept    | Finance

INSERT 0 1
postgres=# select * from employees ;
-[ RECORD 1 ]-------
emp_id     | e002
name       | Bob
department | IT
-[ RECORD 2 ]-------
emp_id     | e003
name       | John
department | Finance

postgres=# INSERT INTO employees(emp_id, name, department)
VALUES ('e003', 'Robert', 'Finance')
ON CONFLICT (emp_id) DO UPDATE
SET name = EXCLUDED.name,
    department = EXCLUDED.department
RETURNING
  OLD.emp_id AS prev_emp_id, OLD.name AS prev_name, OLD.department AS prev_dept,
  NEW.emp_id AS new_emp_id, NEW.name AS new_name, NEW.department AS new_dept;
-[ RECORD 1 ]--------
prev_emp_id | e003
prev_name   | John
prev_dept   | Finance
new_emp_id  | e003
new_name    | Robert
new_dept    | Finance

INSERT 0 1

• When you inserted a new employee (emp_id = 'e003'), since there was no prior row, all OLD values were NULL and the NEW values reflected the inserted data.
• When you attempted to insert again with the same emp_id = 'e003', the conflict triggered the DO UPDATE action, updating the existing row. Here, the OLD values showed the prior data (John in name) and the NEW values showed the updated data (Robert in name).

Use Cases

• Audit Logging: Directly capture changes for history or compliance without triggers or extra queries.​
• Conflict Handling: In INSERT ON CONFLICT scenarios, you can easily differentiate fresh inserts versus updates using the presence (or absence) of OLD values.​
• Change Tracking: Application code that needs to reconcile before and after states can simplify its logic, reducing round-trips and performance bottlenecks.

Performance Implications

• Memory Usage: PostgreSQL must maintain both old and new row versions in memory during the DML operation. For very large batch sizes, this can increase peak memory consumption substantially, especially when returning many columns.​
• Processing Overhead: Constructing and materializing both OLD and NEW row values means more CPU work and potentially more I/O, which can slow down DML operations compared to returning only new or old values alone.​
• Network Bandwidth: Returning large volumes of data from massive batches increases network traffic between the database and client, which can become a bottleneck in distributed or high-latency environments.
• Planning and Execution: Large batch sizes with extensive RETURNING clauses may increase planning and execution times for the queries. Techniques like using optimized batch inserts (e.g., with UNNEST) can mitigate some overhead.​

Best Practices for Performance

• Batch Size Management: Break very large DML operations into smaller batches to reduce memory pressure and resource contention.
• Selective Returning: Return only the columns needed rather than using RETURNING OLD.*, NEW.* to control data volume and reduce overhead.
• Use Indexes: Proper indexing speeds up conflict detection and WHERE clause filtering, reducing the work needed during the DML.
• Consider Asynchronous Processing: For heavy audit or logging workloads, consider offloading change capture to asynchronous processes or logical decoding.
• Monitor and Tune: Monitor memory usage, query planning, execution times, and WAL activity to balance performance with the need for returning OLD and NEW data.

Summary

PostgreSQL 18 introduces a more powerful and flexible RETURNING clause, marking a major leap in how developers can track and utilize data changes.

You can now capture modified rows across multiple tables, CTEs, and even joined updates or deletes, all within a single SQL statement. This eliminates the need for separate queries to fetch affected data, significantly improving performance, auditing, and debugging workflows.

For developers and DBAs, this means real-time insight into data modifications, seamless integration with application logic, and simplified change tracking — making PostgreSQL 18’s RETURNING clause a key step toward smarter, more transparent database operations.

Stay tuned !!