How T‑TDSQL Implements a Full‑Temporal Data Model in MySQL

Full‑Temporal Data Model

The model introduces a dual‑temporal approach where each data item carries a state attribute (current, historical, transitional) and a time attribute (valid time and transaction time). Together they form the full‑state concept, enabling precise tracking of a data item's lifecycle.

Data Model Example

An employee‑salary table illustrates the model: operations op1‑op4 (inserting, updating, adjusting, and deleting records) demonstrate how rows transition among current, historical, and transitional states. The resulting full‑temporal view is shown in accompanying diagrams.

Historical State Storage

MVCC‑based databases such as MySQL/InnoDB and PostgreSQL manage multiple versions differently: MySQL stores historical versions in an Undo Log, while PostgreSQL links version tuples directly. Both use purge/vacuum processes to clean up old versions. To preserve valuable historical data, the design proposes a dedicated history table that mirrors the user table and adds an operation column (enum I/D/U).

Dump Timing

Historical versions are dumped to the history table when they are about to be removed from the Undo Log, ensuring no loss of data during cleanup.

Storage Format

Each tuple consists of system columns (e.g., Trx_id) and user‑defined columns. The history table stores the same user columns plus the operation type, and is prohibited from DML modifications to protect data integrity.

Storage Mode

Historical data can be stored in row‑oriented or column‑oriented formats. Column‑store options include MySQL’s native column store and external formats such as Parquet, RCFile, and ORC.

Dump Efficiency

For column‑store mode, an in‑memory transition area buffers row‑format data; once full, the data is compressed and reorganized into column format, improving read performance.

Historical State Visibility Judgment

A new algorithm determines whether a historical version is visible to a given snapshot, differing from traditional current‑state visibility checks. The algorithm evaluates snapshot start and stop times against the version's commit time and classifies the operation type (insert, update, delete) accordingly.

function HISTORY_VISIBILITY_JUDGEMENT (r_i, s_start, s_stop)
    opT = 0
    if s_start.createTime < r_i.commitTime < s_stop.createTime then
        if r_i.isDelete then
            opT = 3
        else
            if r_i.prev() then
                opT = 1
            else
                opT = 2
            end if
        end if
    else
        opT = 0
    end if
    return opT
end function