Online Transaction Processing (OLTP) systems are designed for high-speed transactions and efficient data management. However, one of their characteristics is that they do not retain historical changes by default. In this post, we will explore why this happens and provide an example to illustrate the concept.

OLTP Systems: Focused on Current Data

OLTP databases are optimized for real-time operations, such as inserting, updating, and deleting records. They ensure data consistency and integrity, but they do not inherently keep track of changes over time. Instead, they maintain only the most recent state of the data.

If an update occurs, the previous information is typically overwritten, meaning that historical values are lost unless explicitly stored using additional mechanisms.

Example: Customer Address Change

Let’s consider a banking system that stores customer addresses in a database. Initially, the customer has the following record:

Initial State

customer_id	name	address
101	John	123 Main St, NY

If the customer moves and updates their address, the OLTP system performs an update operation:

UPDATE customers
SET address = '456 Elm St, NY'
WHERE customer_id = 101;

After Update

customer_id	name	address
101	John	456 Elm St, NY

What Happened to the Old Address?

The original address (123 Main St, NY) is gone—it has been replaced with the new one (456 Elm St, NY). The OLTP system did not preserve any record of the previous value.

How to Retain Historical Data?

If an application needs to maintain historical data, there are several approaches to achieve this:

1. Audit Tables: Creating a separate table to log all changes with timestamps.
2. Triggers: Using database triggers to automatically record changes before an update.
3. Temporal Tables: Some databases (like SQL Server and PostgreSQL) provide built-in support for tracking historical changes.
4. Data Warehousing: Moving transactional data to an OLAP (Online Analytical Processing) system for historical analysis.

Example: Keeping Address History

Instead of overwriting the old address, we can create a history table:

CREATE TABLE customer_address_history (
    customer_id INT,
    address VARCHAR(255),
    valid_from DATE,
    valid_to DATE
);

Whenever an address changes, the previous one is archived:

UPDATE customer_address_history
SET valid_to = CURRENT_DATE
WHERE customer_id = 101 AND valid_to IS NULL;

INSERT INTO customer_address_history (customer_id, address, valid_from, valid_to)
VALUES (101, '456 Elm St, NY', CURRENT_DATE, NULL);

Now, the history is preserved:

customer_id	address	valid_from	valid_to
101	123 Main St, NY	2023-01-01	2024-02-01
101	456 Elm St, NY	2024-02-01	NULL

Conclusion

OLTP systems prioritize real-time data integrity and efficiency, but they do not automatically retain historical changes. If preserving historical data is required, additional mechanisms such as audit tables, triggers, or temporal tables must be implemented. By understanding how OLTP systems work, developers can design solutions that balance performance with historical data retention needs.