Posted inDatabase

Time Travel in SQL: Using Temporal Tables in MariaDB and PostgreSQL

Database tutorial - IT technology blog
Database tutorial - IT technology blog

The 2 AM Nightmare: Where Did the Data Go?

It’s 2:14 AM. My phone is vibrating off the nightstand with alerts from the production monitoring system. A junior developer just ran an UPDATE on the products table but forgot the WHERE clause. In a heartbeat, every price in our catalog dropped to $0.00. We have a massive seasonal sale starting in exactly four hours.

A full restore of our 500GB database would take at least three hours, and point-in-time recovery (PITR) is a logistical headache involving side instances and data migration. I needed a way to see the data as it existed five minutes ago, right there in the production shell. This is where Temporal Tables—also known as System-Versioned Tables—become a literal lifesaver.

While I’ve used everything from MySQL to MongoDB, MariaDB and PostgreSQL offer the cleanest ways to handle the SQL:2011 temporal standard. Forget manual ‘audit_logs’ or brittle triggers. These engines can track every row’s history automatically, giving you a built-in time machine for your data.

The Logic: Why Backups Aren’t Enough

Standard databases are ephemeral; they only care about the ‘now.’ When you update a row, the previous value is overwritten and lost. Temporal tables change the game by tracking the ‘period of validity’ for every single record. Every change gets a timestamp, allowing you to query the database at any specific point in history.

Why is this better than a standard audit log? Here are three practical reasons:

  • Instant Forensic Audits: You can pinpoint exactly who changed a customer’s shipping address three weeks ago without digging through text logs.
  • Zero-Downtime Recovery: You can ‘undo’ a catastrophic update by pulling the previous state directly into a subquery.
  • Accurate Trend Analysis: You can compare how your inventory levels fluctuated last quarter without building a complex data warehouse.

Getting Started: Setup and Installation

The implementation path depends on your choice of engine. MariaDB offers a native, ‘set and forget’ approach. PostgreSQL requires a bit more manual configuration but offers more flexibility.

MariaDB: Native System Versioning

MariaDB built system versioning into the core engine starting with version 10.3.4. There are no plugins to install and no extra extensions to manage. If your version is current, you already have the tools you need.

PostgreSQL: The Flexible Approach

PostgreSQL doesn’t have a single ‘on’ switch for temporal tables. Most teams, especially those on managed services like AWS RDS or Google Cloud SQL, use a combination of history schemas and triggers. This keeps your setup portable and avoids dependency on specific C-language extensions.

If you are working in a local environment and want the dedicated extension, you can install the server development packages:

# For Debian/Ubuntu users
sudo apt-get install postgresql-server-dev-all
# Build the extension from source or install via your package manager

Configuration: Enabling Time Travel

Setting up MariaDB

MariaDB makes this trivial. You simply append WITH SYSTEM VERSIONING to your table definition. Here is a production-ready products table:

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10, 2)
) WITH SYSTEM VERSIONING;

Need to add it to a table that’s already live? Just run an alter command:

ALTER TABLE products ADD SYSTEM VERSIONING;

MariaDB invisible columns—row_start and row_end—now manage the lifecycle of every row behind the scenes. They won’t clutter your SELECT * results, but they are always there.

Setting up PostgreSQL

In Postgres, we create a secondary history table to mirror the primary. This approach is great for performance because it keeps your ‘hot’ table small and moves old data to a separate storage area. Here is the standard trigger-based pattern:

-- The main table for current data
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    salary INT,
    valid_from TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    valid_to TIMESTAMP WITH TIME ZONE DEFAULT '9999-12-31'
);

-- The history table
CREATE TABLE employees_history (LIKE employees);

-- The logic to archive old rows
CREATE OR REPLACE FUNCTION versioning_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO employees_history SELECT OLD.*;
        NEW.valid_from = CURRENT_TIMESTAMP;
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO employees_history SELECT OLD.*;
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_versioning
BEFORE UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION versioning_trigger();

Querying the Past

This is where the investment pays off. Let’s look at how to solve that 2 AM crisis by looking back in time.

The MariaDB Way

MariaDB uses a very readable syntax. If the developer broke the database at 2:10 AM, we can see what the prices were at 2:05 AM with one line:

SELECT * FROM products 
FOR SYSTEM_TIME AS OF '2023-10-27 02:05:00';

To see every price change for a specific item (like product ID 42) to check for errors:

SELECT name, price, row_start, row_end 
FROM products FOR SYSTEM_TIME ALL 
WHERE id = 42 
ORDER BY row_start ASC;

The PostgreSQL Way

With the manual setup, we query both the current and history tables using a UNION. It’s slightly more verbose but very explicit:

-- What was employee 101's salary yesterday at noon?
SELECT * FROM employees_history 
WHERE id = 101 
AND '2023-10-26 12:00:00' BETWEEN valid_from AND valid_to
UNION ALL
SELECT * FROM employees 
WHERE id = 101 
AND '2023-10-26 12:00:00' BETWEEN valid_from AND valid_to;

Maintenance: Managing Growth

Temporal tables grow quickly. Every single UPDATE creates a new row in your history. If you have a high-velocity table, your storage will vanish. I once saw a logging table with versioning enabled eat through 1TB of NVMe storage in just seven days.

In MariaDB, you can prune old data easily:

-- Purge history older than 30 days
DELETE FROM products FOR SYSTEM_TIME BEFORE (NOW() - INTERVAL 30 DAY);

For PostgreSQL, set up a pg_cron job to move or delete rows from your _history tables. This keeps your primary database lean and your queries fast.

The Bottom Line

Temporal tables aren’t just a niche SQL feature; they are your insurance policy. MariaDB is the winner for simplicity and standard compliance. PostgreSQL wins for customization, allowing you to store history on cheaper volumes or different tablespaces. The next time a script goes rogue, don’t panic. Just query the past and go back to sleep.

Share:
Tags: