IoT, IIoT, smart grids, and related systems are generating far more time-series data than traditional enterprise databases were designed to handle. Relational databases often hit performance bottlenecks with high-frequency writes, long retention periods, and large-scale time-range queries. Migration to a real-time or time-series database such as TDengine TSDB requires careful planning across data modeling, tool selection, validation, risk control, and application cutover.
1. Technical differences
1.1 Data model comparison
Relational databases (MySQL, Oracle, etc.):
- Structure: Based on two-dimensional tables with row-oriented storage and entity-relationship modeling
- Indexes: General-purpose B+tree and hash indexes, optimized for multi-condition queries
- Transactions: Strict ACID compliance, guaranteeing data consistency and integrity
- Queries: Full standard SQL support including complex JOINs, subqueries, and window functions
Real-time databases / time-series databases such as TDengine TSDB:
- Structure: Columnar storage with a time-series data model organized around “timestamp + metric value”
- Indexes: Purpose-built indexes optimized for time-range scans, supporting efficient time-window queries
- Data organization: Supertable and Subtable architecture, classified by device or tag
- Query optimization: Built-in time-window aggregation, downsampling functions, and stream processing
1.2 Performance metrics comparison
| Metric Dimension | Traditional Relational Database | Real-Time Database (TDengine) | Performance Improvement |
|---|---|---|---|
| Write Throughput | 3,000-10,000 records/second | 500,000-1,000,000 records/second | 50-300x |
| Query Response Time | Seconds to minutes | Milliseconds to seconds | 10-100x |
| Storage Compression Ratio | No dedicated compression | 10-20x compression ratio | 80-95% storage savings |
| Concurrent Connections | Hundreds to thousands | Tens of thousands to hundreds of thousands | 10-100x |
| Scalability | Primarily vertical scaling | Horizontal distributed scaling | Linear scaling |
1.3 Use case comparison
Where relational databases excel:
- Business systems requiring strong transactional guarantees (financial transactions, inventory management)
- Complex multi-table join queries (ERP, CRM systems)
- Data structures that change frequently and need dynamic schema support
- High-frequency data updates with balanced read/write ratios
Where real-time databases excel:
- High-frequency data collection (industrial sensors, IoT devices)
- Time-series analysis (equipment state trends, energy consumption monitoring)
- Massive historical data storage and querying (2+ years of data retention)
- Real-time monitoring and alerting (millisecond-level response requirements)
2. Migration drivers
2.1 Business drivers
Performance bottlenecks: When daily writes per node exceed tens of millions of records, relational databases begin to show connection pool exhaustion, WAL log backlogs, and widening primary-replica replication lag. Query response times degrade from seconds to minutes, affecting business decision-making.
Storage cost pressure: Relational databases lack dedicated compression for high-frequency time-point data. For the same data volume, they can consume 3 to 5 times more storage space than a time-series database. Long-term data retention requirements (1 to 5 years) drive storage costs upward linearly.
Increasing operational complexity: As monitoring dimensions expand, teams find themselves manually maintaining hundreds of tables and their indexes. High-concurrency scenarios demand complex sharding and read/write splitting architectures.
Growing real-time requirements: Data query response requirements are shrinking from minutes to seconds, and in some cases to milliseconds. Applications increasingly demand real-time dashboards, instant alerts, and predictive maintenance.
2.2 Technical drivers
Write performance requirements: Relational databases typically cap at under 100,000 writes per second. Time-series scenarios can require millions of writes per second. TDengine TSDB has demonstrated write throughput of 850,000 data points per second with average latency under 10 milliseconds.
Query performance requirements: Time-range queries in relational databases require scanning large numbers of records, with performance degrading as data volume grows. TDengine uses time-based partition pruning and a vectorized query engine to deliver millisecond-level aggregation queries.
Scalability requirements: Vertical scaling of relational databases eventually hits physical limits, while distributed relational deployments add operational complexity. Time-series databases support horizontal sharding with automatic load balancing and online, non-disruptive scale-out.
2.3 Economic analysis
TCO comparison:
| Cost Item | Relational Database Solution | Real-Time Database Solution | Savings Ratio |
|---|---|---|---|
| Hardware Cost | High-end server cluster | Standard x86 server cluster | 40-60% |
| Storage Cost | Raw data storage | Compressed data storage | 55-75% |
| Operations Cost | Professional DBA team | Automated operations platform | 50-70% |
| Development Cost | Complex optimized SQL | Standard time-series SQL | 30-50% |
| Migration Cost | One-time investment | Incremental implementation | Controllable risk |
3. Implementation strategy and steps
3.1 Pre-migration assessment
- Business requirements analysis:
- Identify core time-series data scenarios (equipment monitoring, energy management, quality traceability)
- Define performance targets (write throughput, query latency, data retention period)
- Assess existing application dependencies (APIs, query patterns, reporting requirements)
- Technical evaluation:
- Use TDengine’s migration assessment tool to automatically scan the source database structure
- Generate a compatibility report identifying time-series data characteristics
- Produce data model optimization recommendations (tag column design, timestamp precision selection)
- Resource planning:
- Calculate storage capacity requirements (factoring in compression ratios)
- Plan cluster node scale based on write throughput and concurrent query needs
- Design the network architecture (separating the data collection layer from the application access layer)
3.2 Data model design
- Supertable design (example: power monitoring):
CREATE STABLE power_meters (
ts TIMESTAMP,
voltage FLOAT,
current FLOAT,
power FLOAT,
frequency FLOAT
) TAGS (
device_id BINARY(32),
location BINARY(64),
phase TINYINT,
voltage_level SMALLINT,
manufacturer BINARY(32)
);
- Data mapping strategy:
- 1:1 mapping: Source table maps directly to a Supertable, suitable for simple migration scenarios
- Multi-table consolidation: Multiple related source tables merge into a single Supertable, reducing join queries
- Data cleaning: Remove redundant fields, standardize measurement units, handle null values
- Tag design principles:
- Business dimension tags: Device type, installation location, department
- Technical dimension tags: Data source, collection frequency, quality level
- Management dimension tags: Responsible person, maintenance status, criticality level
3.3 Migration execution
- Parallel operation strategy:
- Dual-write mode: Critical business data is written to both old and new systems simultaneously
- Data validation: Sample-based comparison of data consistency and completeness by time window
- Gradual cutover: Migrate in batches by business module, with a defined observation period for each
- Tool selection:
| Migration Tool | Applicable Scenario | Advantages | Notes |
|---|---|---|---|
| taosX | Online real-time migration | Zero-code configuration; supports resumable transfer from breakpoints | Requires SQL mapping rules to be configured |
| DataX | Batch historical migration | Open-source tool; supports many data sources | Requires custom plugin development |
| CSV Intermediate Transfer | Table-structure-change migration | Flexible field mapping | Requires additional storage space |
| CDC Sync | Continuous data synchronization | Low-latency real-time sync | Requires the source database to support CDC functionality |
- Incremental migration steps:
Step 1: Data model preparation
CREATE DATABASE power_monitor;
USE power_monitor;
CREATE STABLE power_meters (...);
Step 2: Historical data migration
taosx run -f "taos://root:password@source_db:6030/power_data" \
-t "taos://root:password@target_db:6030/power_monitor" \
--start "2025-01-01T00:00:00Z" \
--end "2025-12-31T23:59:59Z" \
--batch-size 10000
Step 3: Real-time data synchronization
CREATE TRIGGER sync_to_tdengine
AFTER INSERT ON power_readings
FOR EACH ROW
BEGIN
<em>-- Call REST API or use message queue to synchronize to TDengine</em>
END;
Step 4: Application cutover
- Read-only cutover: Redirect application query requests to TDengine while writes stay on the original path
- Read-write cutover: Full switch to TDengine; the original database serves as a backup
- Dual-track operation: Old and new systems run in parallel with gradual traffic shifting
3.4 Validation and optimization
- Data consistency validation:
- Row count comparison: Verify that source and target database record counts match
- Spot-check validation: Randomly sample time-point data for field-by-field comparison
- Aggregate validation: Compare statistical metrics (sum, average, maximum)
- Performance benchmarking:
- Write performance: Test write throughput and latency under varying concurrency levels
- Query performance: Compare response times for typical business queries
- System resources: Monitor CPU, memory, and disk I/O utilization
- Continuous optimization:
- Query optimization: Analyze slow query logs, refine SQL statements and index usage
- Storage optimization: Adjust data retention policies, enable tiered storage
- Cluster optimization: Adjust node configuration and sharding strategy based on load
4. Key technical considerations
4.1 Data consistency guarantees
Consistency strategy during migration:
- Combined full + incremental: Migrate historical data in full first, then switch to incremental sync
- Transaction boundary alignment: Ensure business transactions remain complete throughout the migration
- Conflict detection and resolution: Design automated resolution mechanisms for data conflicts
Common issues and solutions:
| Issue Type | Cause Analysis | Solution |
|---|---|---|
| Duplicate Data | Migration task interrupted and restarted | Enable resumable transfer from breakpoints and record the migrated time range |
| Data Loss | Incremental sync delay | Increase the delay-duration configuration to ensure delayed writes are covered |
| Out-of-Order Sequence | Multi-threaded concurrent migration | Migrate sequentially by time range to avoid cross writes |
4.2 Data type mapping
Relational database to TDengine type mapping:
| Source Database Type | TDengine Type | Conversion Notes |
|---|---|---|
| DATETIME/TIMESTAMP | TIMESTAMP | Precision conversion (seconds -> milliseconds/microseconds) |
| INTEGER/BIGINT | INT/BIGINT | Ensure numeric range compatibility |
| DECIMAL/NUMERIC | FLOAT/DOUBLE | Assess precision loss |
| VARCHAR/TEXT | NCHAR/VARCHAR | Character set conversion and length limits |
| BOOLEAN | TINYINT | 0/1 mapping |
Special handling scenarios:
- Timestamp precision differences: MySQL second-level timestamps converted to TDengine millisecond-level
- NULL value handling: Define default fill strategies for null values
- Enumeration types: Convert to string or integer tags
4.3 Query compatibility adaptation
SQL syntax differences:
| Source Database SQL Feature | TDengine Corresponding Solution | Adaptation Notes |
|---|---|---|
| Complex JOIN | Avoid usage; switch to supertable queries | TDengine does not support multi-table JOIN |
| Stored Procedures | Implement in application-layer logic | Business logic needs to be refactored |
| Triggers | Replace with a stream processing engine | Use TDengine’s built-in stream processing capabilities |
| Window Functions | Partially supported; testing and validation required | Check compatibility for specific functions |
Query optimization recommendations:
- Time-range first: All queries must include a time condition
- Tag filtering: Use tag column filtering in place of regular column filtering
- Batch operations: Use batch writes and queries to improve performance
- Cache utilization: Configure query caching appropriately to reduce disk access
5. Risk assessment and mitigation
5.1 Technical risks
Data loss risk:
- Risk level: High
- Impact: Incomplete business data, affecting analysis and decision-making
- Mitigation: Implement dual-write mechanisms; establish data validation systems; retain source database backups
Performance shortfall risk:
- Risk level: Medium
- Impact: System response delays, degraded user experience
- Mitigation: Conduct thorough performance testing and stress validation; use progressive traffic cutover; prepare emergency rollback plans
Application compatibility risk:
- Risk level: Medium
- Impact: Application functionality issues, business disruption
- Mitigation: Comprehensive compatibility testing; build protocol compatibility middleware; migrate by module
5.2 Business risks
Business interruption during migration:
- Risk level: High
- Impact: Users unable to use the system normally
- Mitigation: Schedule migration during low-traffic periods; adopt online migration; prepare emergency communication channels
Data quality degradation:
- Risk level: Medium
- Impact: Inaccurate analysis results, flawed decision-making
- Mitigation: Establish a data quality monitoring system; compare data quality before and after migration; design data cleaning and repair workflows
5.3 Management risks
Insufficient team skills:
- Risk level: Medium
- Impact: Migration delays, frequent quality issues
- Mitigation: Organize targeted technical training and certification; bring in external experts; build an internal knowledge base
Loss of project control:
- Risk level: Medium
- Impact: Migration cost overruns, missed delivery deadlines
- Mitigation: Develop a detailed migration roadmap and timeline; establish cross-department coordination mechanisms; adopt agile project management
6. Case studies
6.1 Energy storage data management
Background: A global energy storage brand serving users worldwide. Initially using MySQL for device time-series data storage, the company hit performance bottlenecks as data scale grew.
Migration approach:
- Data model redesign: Created 3 Supertables (power, energy, input/output data)
- Historical data migration: Used taosX to migrate data in reverse chronological order
- Real-time synchronization: Configured an incremental dual-write mechanism
Results:
- Write performance improved by more than 8x (from 100,000 to 850,000 data points per second)
- Query response times dropped from seconds to milliseconds
- Storage costs reduced by over 55%
6.2 Energy storage time-series data processing
Background: A major energy storage equipment manufacturer with hundreds of thousands of deployed terminals worldwide. Originally used MongoDB for time-series data storage, facing low storage efficiency and poor write performance.
Migration results:
- Data compression ratio exceeded 10x
- Write performance improved by 18x
- Query performance improved by several times
- Reduced architectural complexity with zero-code data ingestion
6.3 Oil and petrochemical industry
Background: A software provider in the oil and petrochemical industry, managing over 40 Oracle database instances for time-series data. The goal was to migrate to a time-series database while maintaining business continuity.
Implementation strategy:
- Parallel operation: Set up a temporary cluster; new and old systems ran concurrently
- Incremental migration: Used taosX to migrate 6 TB of data per replica within one week
- Application adaptation: Preserved original table structures, adjusting only field type mappings
Economic benefits:
- Hardware costs reduced by 40-60%
- Operational costs reduced by 50-70%
- System performance improved by 10-100x
7. Best practices
7.1 Migration planning phase
- Assess thoroughly: Conduct a complete business requirements analysis and technical evaluation
- Pilot first: Validate with a non-critical business module before broader rollout
- Prepare contingency plans: Develop detailed risk response and rollback procedures
- Ready the team: Organize targeted training to build team competency
7.2 Execution phase
- Migrate incrementally: Execute migration by phase and module to reduce overall risk
- Monitor in real time: Establish comprehensive monitoring to catch issues early
- Validate data: Implement strict data consistency verification processes
- Communicate with users: Maintain transparency and provide advance notice of business impact
7.3 Post-migration optimization
- Tune performance: Continuously optimize query performance and adjust cluster configuration
- Optimize costs: Use compression and tiered storage to reduce TCO
- Build capabilities: Create an internal knowledge base and develop specialist talent
- Drive innovation: Explore new feature applications that increase business value
8. Recommended technical roadmap
Phase 1: Assessment and preparation (1 to 2 months)
- Technical activities: Existing system architecture analysis; data model compatibility assessment; migration tool selection and validation; performance benchmarking
- Deliverables: Detailed migration feasibility report; data model design proposal; migration tool configuration plan; performance benchmark report
Phase 2: Pilot validation (1 to 2 months)
- Technical activities: Pilot environment setup; historical data migration validation; real-time sync mechanism validation; application compatibility testing
- Deliverables: Pilot system live; migration validation report; risk and issue list; optimization recommendations
Phase 3: Full migration (3 to 6 months)
- Technical activities: Module-by-module migration execution; data consistency monitoring; performance tuning; risk contingency plan execution
- Deliverables: Production system live; performance acceptance report; user training materials; operations manual
Phase 4: Continuous optimization (ongoing)
- Technical activities: Performance monitoring and tuning; new feature adoption; cost optimization; capability building
- Deliverables: Quarterly performance reports; cost optimization proposals; technical competency matrix; business value assessment
9. Summary and outlook
Migrating from relational databases to real-time or time-series databases is a major architectural decision. It can improve performance, scalability, and cost efficiency, but only when the migration is planned around workload characteristics, data consistency, application compatibility, and operational risk.
As IoT, AI, and industrial digitization continue to expand, time-series data will become more central to operational systems. Teams should evaluate database architecture based on workload fit, migration cost, and long-term maintainability. TDengine TSDB is one option for building that foundation when high-frequency time-series workloads are the core requirement.
