Open your passport and you see a story told in stamps: where you’ve been, when you arrived, when you left. Each stamp doesn’t erase the previous ones - they accumulate, creating a complete travel history. Now imagine if your database tables worked the same way, keeping every version of every row like passport stamps. That’s temporal tables, and they’re about to make “what did this data look like last Tuesday?” as easy as flipping through passport pages.
The Overwriting Problem
Traditional database tables are like a passport that only shows your current location:
When you update Jane’s city to ‘Paris’:
- The database overwrites her previous city
- London? What London? You’re in Paris now.
- All history vanishes
Where were you before Paris? When did you leave London? How long were you in Berlin? Gone. Overwritten. Lost to time.
The Passport Approach
Temporal tables keep every “stamp”:
Traditional table shows:
- ID 123: Jane lives in Paris (that’s all we know)
Temporal table shows the complete journey:
- ID 123: Jane in London from Jan 1 to June 15, 2024
- ID 123: Jane in Berlin from June 15 to Sept 1, 2024
- ID 123: Jane in Paris from Sept 1, 2024 to present (shown as 9999-12-31)
Like passport stamps, each entry shows when you “arrived” and “departed” from that state.
Time Travel Queries
This diagram requires JavaScript.
Enable JavaScript in your browser to use this feature.
Now you can ask questions across time:
To find where Jane was on July 15th:
- Query the table asking for data “AS OF” July 15, 2024
- The database looks through the stamps
- Finds she was in Berlin during that period
- Returns: Berlin
To see Jane’s complete travel history:
- Query for ALL versions over time
- Order by the start date
- See every city she’s lived in chronologically
Types of Passport Stamps (Temporal Patterns)
Valid Time (When things actually happened):
- “Jane lived in Berlin from June to September”
- Real-world time when facts were true
Transaction Time (When we recorded it):
- “We updated Jane’s location on June 20th”
- Database recording timestamp
Bitemporal (Both):
- “Jane moved to Berlin on June 15th, but we didn’t update our records until June 20th”
- Complete audit trail
The Immigration Officer (System Implementation)
The system works like an immigration officer stamping passports:
- Find the current “open” stamp (where valid_to is 9999-12-31)
- “Close” it by setting valid_to to the current time
- Create a new stamp starting from current time
- Mark the new stamp as “open” (valid_to = 9999-12-31)
This process ensures:
- No gaps in history
- Clear transition points
- Always one “current” record
- Complete audit trail
Real-World Passport Scenarios
Price History: To find what a product cost last Christmas:
- Query the products table “AS OF” December 25, 2024
- The temporal table returns the price that was valid on that date
- No need to dig through audit logs or backups
Salary Progression: To see an employee’s complete salary history:
- Query for ALL temporal versions of their record
- Each row shows a salary and when it was effective
- Ordered chronologically to see progression over time
Compliance Audit: To find who had access to sensitive data in March:
- Query permissions table for the specific time range
- Returns all users who had access during ANY part of March
- Captures even temporary permission grants
The Stamp Collection Grows
Without management, your passport (table) becomes unwieldy:
Managing the growing stamp collection:
- Set a retention period (e.g., keep 12 months of history)
- Calculate the cutoff date
- Move old “stamps” to an archive table:
- Copy all records older than cutoff to archive
- Delete them from the main table
- Keep recent history for fast queries
- Archive remains available for historical research
This prevents the main table from growing infinitely while preserving history.
Advanced Passport Tricks
Retroactive Updates (Fixing stamp dates): When you discover Jane actually moved to Berlin on June 10th, not 15th:
- Find the Berlin record in the temporal table
- Update its valid_from date to the correct date
- The history now accurately reflects reality
Future Dating (Scheduled changes): To record that Jane will move to Tokyo next month:
- Insert a new record for Tokyo starting Feb 1, 2025
- Update the current Paris record to end on Feb 1, 2025
- The change automatically takes effect on the scheduled date
- Queries before Feb 1 show Paris, after show Tokyo
Gap Detection (Missing stamps): To find gaps in temporal history:
- Compare each record’s end time with the next record’s start time
- If they don’t match exactly, there’s a gap
- This helps identify missing data or system errors
- Ensures continuous timeline coverage
When Not to Use Passport Stamps
Temporal tables aren’t always the answer:
High-Frequency Updates: Sensor data changing every millisecond Huge Tables: Billions of rows with full history Simple Requirements: Just need last modified timestamp Performance Critical: Can’t afford the overhead
Database Support for Time Travel
SQL Server: SQL Server has built-in temporal table support:
- Define the time period columns (valid_from, valid_to)
- Enable SYSTEM_VERSIONING
- Database automatically manages history
- Transparent time-travel queries
PostgreSQL: PostgreSQL uses extensions for temporal support:
- Create a regular table first
- Use temporal_tables extension
- Creates a separate history table
- Triggers manage the versioning
Decision Rules
Use temporal tables when:
- Compliance requires historical audit trails
- You need to query “what was the state at time X”
- Business logic depends on when facts were true
- You’re building systems where “effective date” matters
Don’t use temporal tables when:
- Storage is severely constrained
- Write throughput is critical
- You only need simple “last modified” timestamps
- Data doesn’t naturally have temporal semantics
Temporal tables embrace a fundamental truth: Data doesn’t change - our knowledge of it does. Jane didn’t teleport from London to Paris. She moved, and we recorded it. The temporal model respects this reality by preserving the journey, not just the destination.
Give your data a passport, and let every change tell its story.