Keep the Current and Previous Values in the Same YugabyteDB Row

Three-Part Series: Tracking Data Changes in YugabyteDB

This tip is Part 1 of a three-part series exploring different ways to track data changes in YugabyteDB.

We will begin with the simplest requirement:

Retaining the current value of a column and the value that immediately preceded it.

This approach works well when only a few columns need to be tracked, only one previous value is required, and the values should remain directly available in the operational row.

For this lightweight use case, you can store the current and previous values in separate columns and update both in a single YSQL statement… without introducing a trigger, history table, or external CDC pipeline.

The Basic Pattern

Consider the following table:

				
					CREATE TABLE customer_account (
    account_id              BIGINT PRIMARY KEY,
    account_status          TEXT NOT NULL,
    previous_account_status TEXT,
    email_address           TEXT,
    previous_email_address  TEXT,
    changed_by              TEXT,
    changed_at              TIMESTAMPTZ
);

The table stores both the current and previous values for the columns being tracked:

Current Column	Previous-Value Column
`account_status`	`previous_account_status`
`email_address`	`previous_email_address`

Insert an initial account:

				
					INSERT INTO customer_account (
    account_id,
    account_status,
    email_address
)
VALUES (
    1001,
    'Pending',
    'old-address@example.com'
);

Verify the row:

				
					SELECT *
FROM customer_account
WHERE account_id = 1001;

Output:

				
					.account_id | account_status | previous_account_status |      email_address       | previous_email_address | changed_by | changed_at
------------+----------------+-------------------------+--------------------------+------------------------+------------+------------
       1001 | Pending        |                         | old-address@example.com  |                        |            |

Because this is the first version of the row, the previous-value columns are NULL.

Update the Current and Previous Values Together

Suppose the account is activated and its email address is changed.

Run the following single UPDATE:

				
					UPDATE customer_account
SET previous_account_status = account_status,
    account_status          = 'Active',
    previous_email_address  = email_address,
    email_address           = 'new-address@example.com',
    changed_by              = 'tom_admin',
    changed_at              = clock_timestamp()
WHERE account_id = 1001
RETURNING
    account_id,
    account_status,
    previous_account_status,
    email_address,
    previous_email_address,
    changed_by,
    changed_at;

The result shows both the new values and the values that existed immediately before the update:

				
					.account_id | account_status | previous_account_status |      email_address       | previous_email_address  | changed_by   |          changed_at
------------+----------------+-------------------------+--------------------------+-------------------------+--------------+-------------------------------
       1001 | Active         | Pending                 | new-address@example.com  | old-address@example.com | sarah_admin  | 2026-07-17 12:42:09.327891+00

Why This Works

The important part of the statement is:

				
					SET
previous_account_status = account_status,
account_status          = 'Active'

The expression assigned to previous_account_status reads the original value of account_status from the row being updated.

Therefore:

				
					previous_account_status = Pending
account_status          = Active

The same behavior applies to the email columns:

				
					previous_email_address = email_address,
email_address          = 'new-address@example.com'

The assignments do not run sequentially from top to bottom. The expressions on the right side of the assignments use the row values that existed before the update.

For example, reversing the order does not change the result:

				
					UPDATE customer_account
SET account_status          = 'Active',
    previous_account_status = account_status
WHERE account_id = 1001;

previous_account_status still receives the original value of account_status, not 'Active'.

Updating Only One Tracked Column

You do not need to update every previous-value column each time.

For example, the following statement changes only the account status:

				
					UPDATE customer_account
SET previous_account_status = account_status,
    account_status          = 'Suspended',
    changed_by              = 'mike_admin',
    changed_at              = clock_timestamp()
WHERE account_id = 1001
RETURNING
    account_status,
    previous_account_status,
    email_address,
    previous_email_address;

Result:

				
					.account_status | previous_account_status |      email_address       | previous_email_address
----------------+-------------------------+--------------------------+-------------------------
 Suspended      | Active                  | new-address@example.com  | old-address@example.com

The email columns remain unchanged because they were not included in the SET clause.

Why Use a Single UPDATE?

An application could first retrieve the current value and then issue a separate update:

				
					SELECT account_status
FROM customer_account
WHERE account_id = 1001;

UPDATE customer_account
SET account_status = 'Suspended'
WHERE account_id = 1001;

However, that requires multiple statements and leaves the application responsible for carrying the previous value between them.

The single-statement pattern keeps the value capture and update together:

				
					UPDATE customer_account
SET previous_account_status = account_status,
    account_status          = 'Suspended'
WHERE account_id = 1001;

Adding RETURNING also lets the application immediately retrieve the resulting row without issuing a separate SELECT.

A More Compact Example

Here is the pattern in its simplest form:

				
					CREATE TABLE update_me (
    id             INT PRIMARY KEY,
    current_value  TEXT,
    previous_value TEXT
);

INSERT INTO update_me (
    id,
    current_value
)
VALUES (
    1,
    'data1'
);

UPDATE update_me
SET previous_value = current_value,
    current_value  = 'data2'
WHERE id = 1;

SELECT *
FROM update_me;

Result:

				
					.id | current_value | previous_value
----+---------------+----------------
  1 | data2         | data1

Run another update:

				
					UPDATE update_me
SET previous_value = current_value,
    current_value  = 'data3'
WHERE id = 1;

SELECT *
FROM update_me;

Result:

				
					 id | current_value | previous_value
----+---------------+----------------
  1 | data3         | data2

The row always contains the current value and the value that immediately preceded it.

Important: This pattern retains only one previous value. After changing data2 to data3, the original data1 value is no longer available. Use an audit-history table or CDC when every historical version must be preserved.

When This Pattern Is a Good Fit

This approach works well when:

● Only a few columns need previous-value tracking.
● Only the most recent previous value matters.
● The values should be immediately available in the operational row.
● The application controls the update statements.
● You want to avoid introducing an audit table or trigger for a small requirement.

Possible use cases include:

● Displaying the previous account status.
● Showing a recently changed email address.
● Supporting a basic one-step undo operation.
● Comparing the current and last configuration values.
● Displaying the most recent administrative change.

Limitations

Only One Previous Version Is Preserved

Every update replaces the existing previous value.

This pattern cannot answer questions such as:

● What were all the values this column had during the past year?
● How many times was the value changed?
● Which users made each individual change?
● What was the value at a particular point in time?

Every Update Must Follow the Pattern

The application must remember to update both columns:

				
					SET
previous_account_status = account_status,
account_status          = 'Active'

The following update changes the current value but does not preserve the previous value:

				
					UPDATE customer_account
SET account_status = 'Active'
WHERE account_id = 1001;

If many applications, services, or administrators can update the table, relying on every writer to follow the convention may be risky.

The Table Becomes Wider

Each tracked column normally requires a corresponding previous-value column.

For example:

				
					account_status
previous_account_status

email_address
previous_email_address

phone_number
previous_phone_number

That remains manageable for a few important attributes, but it can become cumbersome when dozens of columns must be audited.

It Is Not a Complete Audit Trail

Although the row can store changed_by and changed_at, those columns describe only the most recent update.

Earlier users, timestamps, and values are overwritten by subsequent changes.

Final Takeaway

For lightweight requirements, storing the current and previous values in the same YugabyteDB row can be an elegant solution.

The key is to move the current value into the previous-value column as part of the same UPDATE:

				
					UPDATE customer_account
SET previous_account_status = account_status,
    account_status          = 'Active'
WHERE account_id = 1001;

This approach is simple, requires no trigger, and makes the immediately preceding value easy to retrieve.

However, it is intentionally limited to one level of history. When you need a durable record of every field change, including the user, old value, new value, and timestamp, a centralized JSONB audit table and reusable trigger provide a more complete solution.

That will be the focus of the next tip in this series.

Have Fun!

A few weeks ago, I visited one of my favorite customers at their office in Omaha. In a common area, they had a puzzle set out as a relaxing break from the difficulty of their work.

But this was no ordinary puzzle… almost every piece looked exactly the same! 😂

Apparently, they finished that one and have now moved on to this 1,000-piece pink puppy puzzle. Just looking at all those nearly identical pieces would drive me crazy! 🧩🐶