Versioned Table
A versioned table is a database table that maintains a history of all changes, allowing access to data as it existed at any point in time. Each modification creates a new version or snapshot while preserving previous states, enabling time travel queries and audit capabilities.
How versioned tables work
Versioned tables track changes through a series of immutable snapshots, each representing the table's state at a specific point in time. This is achieved through:
- Version tracking - Each change creates a new version number
- Snapshot management - Previous versions remain accessible
- Metadata tracking - Changes are logged with timestamps and user information
Next generation time-series database
QuestDB is an open-source time-series database optimized for market and heavy industry data. Built from scratch in Java and C++, it offers high-throughput ingestion and fast SQL queries with time-series extensions.
Key features and capabilities
Snapshot isolation
Versioned tables provide snapshot isolation for readers, ensuring consistent views of data without blocking writers. This enables:
- Concurrent access to different versions
- Point-in-time recovery
- Reproducible analytics
Time travel queries
Users can query historical states using:
-- ⚠️ ANSI (requires QuestDB adaptation)SELECT * FROM my_table AT TIMESTAMP '2023-01-01 12:00:00';
This capability is particularly valuable for:
- Audit compliance
- Bug investigation
- Historical analysis
Next generation time-series database
QuestDB is an open-source time-series database optimized for market and heavy industry data. Built from scratch in Java and C++, it offers high-throughput ingestion and fast SQL queries with time-series extensions.
Implementation approaches
Copy-on-write
Copy-on-write maintains full copies of modified data for each version:
- Creates new physical copies for changed records
- Preserves original data intact
- Provides direct access to any version
Merge-on-read
Merge-on-read stores changes as deltas:
- Records modifications separately
- Reconstructs versions at query time
- Optimizes storage space
Applications in time-series data
In time-series contexts, versioned tables are particularly valuable for:
- Financial audit trails
- Industrial sensor data corrections
- Regulatory compliance reporting
The ability to access historical states helps organizations maintain data lineage and meet compliance requirements while enabling advanced analytics capabilities.
Best practices
-
Version retention policies
- Define clear lifecycle rules
- Balance history depth vs storage costs
- Align with compliance requirements
-
Performance optimization
- Index version metadata
- Optimize storage layout
- Consider compression strategies
-
Governance controls
- Implement version access controls
- Track version provenance
- Document version changes
Integration with modern data architectures
Versioned tables are fundamental to modern data architectures, particularly in:
- Data lakehouse implementations
- Table format specifications
- Compliance-focused data platforms
They provide the foundation for reproducible analytics and robust data governance strategies.