{
  "version": 3,
  "savedAt": "2026-05-10T19:21:31.678Z",
  "fields": {
    "title": "System-versioned tables for MySQL (SQL:2011 temporal tables)",
    "status": "Draft",
    "roadmap-section": "Developer/DBA Experience",
    "contact-name": "Vinicius Grippa",
    "contact-email": "vgrippa@gmail.com",
    "company": "Readyset",
    "role": "Lead Database Engineer",
    "authors": "",
    "date": "2026-05-10",
    "target": "",
    "related": "QL:2011 standard, Part 2: \"System-versioned tables\" and \"Application-time period tables\"",
    "summary": "Add SQL:2011 system-versioned tables to MySQL: per-table opt-in via WITH SYSTEM VERSIONING, automatic maintenance of row_start and row_end timestamps on every modification, and SELECT ... FOR SYSTEM_TIME syntax to query historical state. The design adopts the MariaDB convention of storing history rows in the same table (optionally partitioned for retention management), so the user-visible surface is a single table that answers \"now\" or \"as of T\" depending on the query.\n\nUser / developer stories\n\nAs a compliance engineer, I want to answer \"what was customer X's address on 2024-03-12?\" without restoring a backup.\nAs an application developer, I want to debug \"why did this order show the wrong price last week?\" by selecting the row as of that timestamp.\nAs a DBA, I want to remove the brittle trigger-driven audit tables I maintain today and replace them with a single declarative clause.\nAs a platform engineer, I want SQL-standard portability so the same FOR SYSTEM_TIME AS OF query runs on MySQL, SQL Server, DB2, and MariaDB.",
    "stories": "As a compliance engineer, I want to answer \"what was customer X's address on 2024-03-12?\" without restoring a backup.\nAs an application developer, I want to debug \"why did this order show the wrong price last week?\" by selecting the row as of that timestamp.\nAs a DBA, I want to remove the brittle trigger-driven audit tables I maintain today and replace them with a single declarative clause.\nAs a platform engineer, I want SQL-standard portability so the same FOR SYSTEM_TIME AS OF query runs on MySQL, SQL Server, DB2, and MariaDB.",
    "scope-in": "New table option WITH SYSTEM VERSIONING (and WITHOUT SYSTEM VERSIONING to remove it).\nAutomatic management of row_start and row_end columns of type TIMESTAMP(6).\nSELECT ... FOR SYSTEM_TIME { AS OF | BETWEEN ... AND ... | FROM ... TO ... | ALL } syntax.\nDELETE HISTORY FROM t [BEFORE SYSTEM_TIME ...] for explicit retention management.\nOptional partitioning by SYSTEM_TIME for retention by time interval (matches MariaDB).\nReplication of system-versioned writes such that replicas see identical history.",
    "scope-out": "Application-time period tables (the second half of SQL:2011 temporal). Useful but separable; a follow-up FR.\nFlashback Database (Oracle-style server-wide point-in-time recovery). Different feature, different implementation; not this proposal.\nCross-database temporal joins or FOR SYSTEM_TIME on views, in v1.\nForeign-key referential checks against historical state (FK checks remain on the current snapshot).",
    "references": "SQL:2011 standard, Part 2, Clauses 4.15.7 and 7.4.\nMariaDB 10.3+ syntax serves as a working precedent for naming, behavior, and DDL surface.\nSQL Server temporal tables documentation for the alternative sibling-history-table model.",
    "functional": "The server MUST accept CREATE TABLE ... WITH SYSTEM VERSIONING and ALTER TABLE ... ADD SYSTEM VERSIONING / DROP SYSTEM VERSIONING.\nFor a system-versioned table, every INSERT MUST stamp row_start = NOW(6) and row_end = '9999-12-31 23:59:59.999999'.\nEvery UPDATE MUST close the existing row (row_end = NOW(6)) and insert a new current row (row_start = NOW(6), row_end = MAX).\nEvery DELETE MUST close the current row (row_end = NOW(6)) without physically removing it.\nSELECT ... FOR SYSTEM_TIME AS OF <ts> MUST return rows where row_start <= <ts> < row_end.\nSELECT ... FOR SYSTEM_TIME ALL MUST return all rows including history.\nSELECT without FOR SYSTEM_TIME MUST return only current rows (row_end = MAX), preserving today's semantics.\nDELETE HISTORY FROM t [BEFORE SYSTEM_TIME <ts>] MUST physically remove closed history rows older than <ts>.\nReplication MUST reproduce system-versioned writes on replicas with byte-identical row_start/row_end values.",
    "nonfunctional": "A system-versioned table SHOULD have INSERT/UPDATE/DELETE throughput within ~10% of the same table without versioning, for write-mostly workloads.\nSELECT without FOR SYSTEM_TIME MUST not be measurably slower than today's SELECT, given an index on row_end.\nHistory row storage cost MUST be predictable and bounded by user-managed retention (DELETE HISTORY or partitioning).\nALTER TABLE ... ADD SYSTEM VERSIONING on an existing populated table MUST be online (no exclusive lock for the duration of the alter).",
    "approach": "A system-versioned table carries two implicit TIMESTAMP(6) columns, row_start and row_end, maintained automatically by the server. History rows live in the same physical table; current rows are those with row_end = '9999-12-31 23:59:59.999999'. Queries without FOR SYSTEM_TIME are transparently rewritten to filter on row_end = MAX. Queries with FOR SYSTEM_TIME AS OF <ts> filter on row_start <= <ts> < row_end. Retention is user-managed via DELETE HISTORY or by partitioning the table on SYSTEM_TIME.\n\nThis proposal adopts the MariaDB same-table model rather than the SQL Server sibling-history-table model, because:\n\na single table simplifies backup, replication, and tooling;\nthe user-visible surface stays as one object;\npartitioning gives clean retention without a second table to manage;\nit matches the syntax of the closest existing implementation, easing migration.",
    "config-clauses": "-- Create a versioned table\nCREATE TABLE customers (\n  id   BIGINT PRIMARY KEY,\n  name VARCHAR(255),\n  tier ENUM('free','pro','enterprise')\n) WITH SYSTEM VERSIONING;\n\n-- Normal DML; history is maintained transparently\nINSERT INTO customers VALUES (1, 'Acme', 'free');\nUPDATE customers SET tier = 'pro' WHERE id = 1;\n\n-- Current state\nSELECT * FROM customers WHERE id = 1;\n-- -> 1, Acme, pro\n\n-- Historical state\nSELECT * FROM customers FOR SYSTEM_TIME AS OF '2026-05-09 12:00:00'\nWHERE id = 1;\n-- -> 1, Acme, free\n\n-- Full history\nSELECT row_start, row_end, * FROM customers FOR SYSTEM_TIME ALL\nWHERE id = 1;\n\n-- Retention\nDELETE HISTORY FROM customers BEFORE SYSTEM_TIME '2026-01-01';\nPartitioned-by-time variant for automated retention:\n\n\nCREATE TABLE customers (...)\nWITH SYSTEM VERSIONING\nPARTITION BY SYSTEM_TIME INTERVAL 1 MONTH (\n  PARTITION p_hist HISTORY,\n  PARTITION p_cur  CURRENT\n);",
    "sysvars": "system_versioning_alter_history (enum: ERROR, KEEP; default ERROR): controls whether ALTER TABLE against a versioned table is allowed when history exists.\nsystem_versioning_asof (session variable, default NULL): if set to a timestamp, all SELECT statements against versioned tables in this session implicitly carry FOR SYSTEM_TIME AS OF <value>. Useful for application-level point-in-time reads without rewriting queries. Optional; can be deferred.\nsystem_versioning_timestamp_precision (default 6): precision of row_start/row_end.",
    "utilities": "mysql shell --skip-history / --include-history (default: include history). Round-trip of a versioned table must reproduce the history on restore.",
    "udfs": "None",
    "statements": "None",
    "observability": "New INFORMATION_SCHEMA.SYSTEM_VERSIONED_TABLES view: schema, table, has-versioning flag, current row count, history row count, oldest history timestamp, total bytes in history.\nNew status variable System_versioning_history_bytes (per-table available via the view above).\nPerformance Schema instruments for the close-and-reinsert path under UPDATE so write amplification is measurable.",
    "procedure": "ALTER TABLE customers ADD SYSTEM VERSIONING;",
    "security": "Reading history requires SELECT on the table; no new privilege.\nDELETE HISTORY requires DELETE on the table (the data already requires DELETE to remove from current; removing from history is at least as sensitive).\nALTER TABLE ... DROP SYSTEM VERSIONING is the privilege-sensitive operation: it permanently destroys history. Requires ALTER + DELETE on the table and, optionally, a future DROP_HISTORY privilege if reviewers want finer separation.",
    "compatibility": "Tables without WITH SYSTEM VERSIONING are unaffected. Default behavior is unchanged.\nFor a versioned table, DELETE is logical: the row remains queryable via FOR SYSTEM_TIME. This is documented but is a behavior departure from non-versioned tables and must be called out prominently.\nmysqldump of a versioned table must round-trip history; the dump format gains a WITH SYSTEM VERSIONING clause on the CREATE TABLE and explicit row_start/row_end values on inserts.\nReplication: row events for versioned tables carry row_start/row_end values explicitly. Replicas apply these values verbatim (do not regenerate NOW(6) on the replica). This is essential for replica correctness and must be enforced.\nGroup replication: compatible; same row-event semantics.",
    "diagram": "+----------------------------+         +-----------------------------+\n|  Parser                    |         |  Optimizer                  |\n|  (FOR SYSTEM_TIME,         |         |  (rewrite SELECT to add     |\n|   WITH SYSTEM VERSIONING)  |         |   row_end = MAX or          |\n+-------------+--------------+         |   row_start..row_end range) |\n              |                        +--------------+--------------+\n              v                                       |\n+----------------------------+                        v\n|  DDL: table options &      |         +-----------------------------+\n|  data dictionary metadata  |         |  Executor                   |\n+-------------+--------------+         |  (transparent filter on     |\n              |                        |   row_start/row_end)        |\n              v                        +--------------+--------------+\n+----------------------------+                        |\n|  Handler API: versioned    |<-----------------------+\n|  row lifecycle             |\n|   INSERT: stamp start/end  |\n|   UPDATE: close + insert   |\n|   DELETE: close            |\n+-------------+--------------+\n              |\n              v\n+----------------------------+         +-----------------------------+\n|  InnoDB row format         |         |  Binlog row events          |\n|  (no on-disk change;       |         |  (carry explicit start/end) |\n|   row_start/row_end are    |         +-----------------------------+\n|   ordinary columns)        |\n+----------------------------+",
    "interfaces": "Handler flag: HA_VERSIONED_TABLE exposes versioning to the storage engine. InnoDB acknowledges the flag and treats row_start/row_end as ordinary columns; the row lifecycle (close-on-update, close-on-delete, insert-new-on-update) is implemented above the handler in the SQL layer, so all engines that support generated columns can in principle support versioning.\nRow events: Write_rows_log_event / Update_rows_log_event / Delete_rows_log_event for versioned tables include row_start/row_end in the row image. Replicas apply the values as-is.\nOptimizer rewrite: SELECT against a versioned table without FOR SYSTEM_TIME is rewritten to add WHERE row_end = MAX_TS; with AS OF <ts>, to WHERE row_start <= <ts> AND <ts> < row_end.\nIndex recommendation: a btree on (row_end) (or (pk, row_end)) is created automatically when versioning is enabled, to keep \"current rows only\" queries fast.",
    "implementation": "Parser/grammar: add WITH SYSTEM VERSIONING, FOR SYSTEM_TIME, DELETE HISTORY, PARTITION BY SYSTEM_TIME.\nData dictionary: persist the versioning flag on the table; persist auto-generated row_start/row_end columns.\nDDL paths: CREATE TABLE WITH SYSTEM VERSIONING, ALTER TABLE ADD/DROP SYSTEM VERSIONING, online alter for adding versioning.\nDML lifecycle: intercept INSERT/UPDATE/DELETE in the SQL layer for versioned tables; emit the close-and-reinsert sequence atomically within the same statement.\nOptimizer rewrite: automatic predicate injection for \"current only\" and FOR SYSTEM_TIME variants.\nReplication: ensure row events carry explicit row_start/row_end; replicas apply verbatim.\nRetention: DELETE HISTORY statement; partition-by-time pruning.\nTooling: mysqldump round-trip; INFORMATION_SCHEMA.SYSTEM_VERSIONED_TABLES.\nDocs & MTR tests (Section 6).",
    "qa-notes": "Basic lifecycle. Insert, update, delete, then verify current SELECT matches expected current state and FOR SYSTEM_TIME AS OF <past> matches expected historical state.\nBoundary times. AS OF <row_start> is inclusive; AS OF <row_end> is exclusive. Test both boundaries and the simultaneous-update case (two updates at the same NOW(6) value).\nConcurrent writers. Two transactions updating the same row at near-identical timestamps must produce a consistent history with strict ordering (row_starts monotonic per row).\nReplication parity. Source writes; replica must produce byte-identical history. Verify with checksums of (row_start, row_end, *) across replicas.\nBinlog replay. mysqlbinlog replay of versioned-table events must reproduce identical history, not regenerate NOW(6).\nBackup round-trip. mysqldump + restore must reproduce all history rows with original timestamps.\nSchema evolution. ALTER TABLE on a versioned table: adding a column, dropping a nullable column, renaming a column. The FR should pick a documented policy (recommendation: history rows show the new schema with NULL/defaults for added columns; dropped columns vanish from history too).\nRetention. DELETE HISTORY BEFORE SYSTEM_TIME <ts> removes only closed rows older than <ts>; active \"current\" rows are never affected. Partitioned-by-time variant: dropping a HISTORY partition removes its rows without scanning.\nPrivilege checks. DROP SYSTEM VERSIONING on a table with history requires the documented privileges and emits a clear error otherwise.\nPerformance. Microbenchmark INSERT/UPDATE/DELETE on a versioned vs non-versioned copy of the same schema; document the overhead and verify it falls within the ~10% non-functional requirement for write-mostly workloads.\nStorage growth. Long-running update-heavy workload (e.g. sysbench oltp_update_index for 1h) against a versioned table. Verify history size matches the expected 2 * update_count row growth and that partition-based retention prunes correctly.\nFailure injection. Crash mid-UPDATE on a versioned row. After recovery, history must be consistent: either the old row is still current (UPDATE rolled back) or the old row is closed and the new row is current (UPDATE committed). Never both, never neither."
  },
  "checks": [
    "SQL syntax or statements",
    "Configuration options or system variables",
    "Command-line options or utilities",
    "User-visible behavior",
    "Observability",
    "Protocol or replication behavior",
    "Upgrade / downgrade compatibility",
    "Performance or resource usage",
    "Files, persistence, or metadata formats",
    "APIs or internal interfaces",
    "Testing or QA coverage needs"
  ]
}