1. Why Migrate the Database?
In the early stages of our business, we used PostgreSQL as both an OLTP and analytical database, deployed and maintained on our own infrastructure. In day-to-day operations, however, maintaining high availability, scheduling regular backups, and handling failure recovery required significant engineering effort. The resulting operational costs and complexity were high, and occasional operational oversights introduced a certain level of risk to business continuity.
Given that TiDB Cloud, PingCAP's cloud database service, eliminates the need for on-premises deployment and maintenance while offering convenient usage and flexible, controllable pricing, we decided to migrate our PostgreSQL database to TiDB Cloud, with business continuity and stability as our top priorities. We also saw this migration as an opportunity to explore a more diversified data storage and analytics architecture that would better fit our business needs.
Our core business focuses on road traffic checkpoint data analytics. Based on massive volumes of vehicle passage records—including license plates, vehicle speeds, locations, and driving behavior characteristics—we provide data support for traffic management and safety oversight.
The core table, driving_data_jsonb, uses a JSONB + partitioned-table design. It processes approximately 5 million vehicle passage records per day, while a single partition stores around 150 million vehicle passage records (approximately 60 GB). It is the largest and most frequently queried table in our business.
Typical analytical scenarios include:
- Road congestion analysis: Aggregating traffic volume and average vehicle speed by time period and road segment to assess current road traffic conditions in real time
- Section speed-limit violation detection: Calculating a vehicle's average speed over a road section based on the travel time and distance between checkpoints, and identifying speeding violations
- Driving trajectory analysis: Reconstructing vehicle routes to analyze driving habits and patterns of abnormal behavior
- Continuous driving duration monitoring: Tracking the continuous driving time of individual vehicles and generating alerts based on traffic regulations, such as the requirement to take a mandatory break after four hours of continuous driving
These analytical queries involve large-scale data aggregation and multidimensional calculations, placing relatively high demands on the database's OLAP capabilities.
2. Migration Environment
2.1 Source Environment
| Item | Details |
|---|---|
| Database | PostgreSQL 18.2 |
| Deployment | Local virtual machine (test environment) |
| Database |
traffic (traffic data database) |
| Core table |
driving_data_jsonb (partitioned table, approximately 150 million rows / 60 GB per partition) |
| Network egress | 500 Mbps broadband connection with direct public Internet access |
2.2 Target Environment
| Item | Details |
|---|---|
| Platform | TiDB Cloud |
| Region |
cn-shanghai (Shanghai) |
| Storage consumption | Approximately 62.62 GiB |
2.3 Network Topology
The source environment connects directly to TiDB Cloud over the public Internet through a 500 Mbps broadband connection. No dedicated private connection or VPN was deployed.
3. Migration Tool Selection
During the migration, we primarily evaluated two commonly used data migration tools: DataX and SeaTunnel.
| DataX | SeaTunnel ✅ |
|---|---|
| • No version updates for a long time | • Active open-source project with timely iterative updates (currently using 2.3.13) |
| • Some plugins rely on Python 2.x helper scripts, leading to poor compatibility with modern tech stacks | • Rich variety of supported data sources with a mature connector ecosystem |
| • Limited extensibility with a relatively sparse connector ecosystem | • High community activity and fast response times to issues |
| • Supports generic JDBC connectors, fully compatible with PG (PostgreSQL) and TiDB |
Given our core requirements for timely synchronization, stability, and zero data loss, we ultimately selected SeaTunnel 2.3.13 as the migration tool.
4. Migrating Data with SeaTunnel
4.1 Migration Strategy: Full Load + Incremental Sync
Because the PG-CDC connector in SeaTunnel 2.3.13 had a known bug in the combined full-load + incremental synchronization mode (which we reported to the community and helped fix), we adopted a two-step approach: full-load initialization followed by CDC-based incremental synchronization.
Step 1: Perform a full data initialization using the JDBC PostgreSQL connector.
Step 2: Use the CDC incremental connector to synchronize data changes in real time.
4.2 Full-Load Configuration (JDBC Source → JDBC Sink)
The following is the core configuration used during the full-load phase:
env {
parallelism = 2
checkpoint.interval = 10000
pipeline.name = "PG_TO_TiDB_DRIVING"
flink.execution.checkpointing.mode = "EXACTLY_ONCE"
flink.execution.checkpointing.timeout = 600000
}
source {
Jdbc {
url = "jdbc:postgresql://<source_host>:5432/traffic"
driver = "org.postgresql.Driver"
user = "postgres"
password = "******"
query = "SELECT record_id, pass_time, plate_no, plate_color,
vehicle_type, speed, speed_limit, status,
longitude, latitude, speed_variance,
lane_change_count, raw_behavior_features,
create_time, update_time
FROM driving_data_jsonb"
column.filters = "update_time"
start.time = "2025-01-01 00:00:00"
fetch.size = 1000
parallelism = 2
}
}
sink {
Jdbc {
url = "jdbc:mysql://<tidb_cloud_host>:4000/traffic?sslMode=VERIFY_IDENTITY"
driver = "com.mysql.cj.jdbc.Driver"
user = "******"
password = "******"
database = "traffic"
table = "driving_data_jsonb"
generate_sink_sql = true
save_mode = "upsert"
unique_key = ["record_id"]
batch.size = 1000
batch.interval = 1000
parallelism = 2
}
}
4.3 Incremental Synchronization Configuration (Postgres-CDC Source → JDBC Sink)
Once the full load was completed, we started the CDC incremental connector to continuously synchronize changes from the source database in real time:
env {
execution.parallelism = 1
job.mode = "STREAMING"
checkpoint.interval = 5000
}
source {
Postgres-CDC {
username = "postgres"
password = "******"
database-names = ["traffic"]
schema-names = ["public"]
table-names = ["traffic.public.driving_data_jsonb"]
url = "jdbc:postgresql://<source_host>:5432/traffic"
decoding.plugin.name = "pgoutput"
slot.name = "final_slot"
startup.mode = "latest"
plugin_output = "out"
}
}
sink {
Jdbc {
url = "jdbc:mysql://<tidb_cloud_host>:4000/traffic?sslMode=VERIFY_IDENTITY"
driver = "com.mysql.cj.jdbc.Driver"
user = "******"
password = "******"
database = "traffic"
table = "driving_data_jsonb"
generate_sink_sql = true
save_mode = "upsert"
unique_key = ["record_id"]
batch.size = 1000
batch.interval = 1000
parallelism = 2
}
}
4.4 Key Configuration Details
The following table highlights the key configuration options used during full-load and incremental synchronization:
| Configuration | Description |
|---|---|
parallelism = 2 |
Sets the parallelism to 2 to match the available resources on the source side |
checkpoint.mode = EXACTLY_ONCE |
Provides exactly-once semantics to help ensure data consistency |
checkpoint.interval = 10000 |
Sets the checkpoint interval to 10 seconds, balancing performance and fault tolerance |
save_mode = upsert |
Writes data using Upsert based on unique_key to prevent duplicate records |
unique_key = ["record_id"] |
Uses record_id as the deduplication key |
sslMode = VERIFY_IDENTITY |
Uses a TLS-encrypted connection, as required by TiDB Cloud |
batch.size / batch.interval |
Writes 1,000 records per batch or flushes every 1 second, whichever comes first |
decoding.plugin.name = pgoutput |
Uses PostgreSQL's native logical decoding plugin for CDC incremental synchronization |
slot.name |
Specifies the logical replication slot to ensure incremental data is not lost |
startup.mode = latest |
Starts consuming incremental changes from the latest position to avoid duplicate synchronization |
4.5 Migration Results
| Metric | Result |
|---|---|
| Full-load duration | Approximately 12 hours per partition (the source was a virtual machine in a test environment) |
| Incremental synchronization latency | The migration proceeded smoothly with no noticeable latency |
| Data consistency | Every record contains the unique primary key record_id. The Sink uses upsert mode and deduplicates records based on the primary key, ensuring that data is neither lost nor duplicated. After migration, the final validation was performed by comparing row counts between the source and target |
| Schema compatibility | No data type mapping issues were encountered when migrating from PostgreSQL to TiDB |
Note: The migration first focused on the largest core table, driving_data_jsonb. The remaining tables contain significantly less data and will be synchronized as needed.
5. Challenges and Lessons Learned
5.1 PG-CDC Connector Bug
The PG-CDC connector in SeaTunnel 2.3.13 had a bug in the combined full-load + incremental synchronization mode, which prevented the workflow from completing successfully. We reported the issue to the community, worked with developers to identify and fix the problem, and ultimately avoided it by running the full-load and incremental synchronization stages separately.
Recommendation: When using SeaTunnel for PostgreSQL migration, we recommend adopting a two-step full-load + incremental synchronization strategy first, as it provides better stability. If you need to use the integrated mode, make sure the SeaTunnel version you are using contains the fix for this issue.
5.2 The Impact of Network Bandwidth
The source database was deployed on a local virtual machine and connected to TiDB Cloud over the public Internet through a 500 Mbps broadband connection. Synchronizing 60 GB of data from a single partition took approximately 12 hours during the full-load phase.
If the source database is deployed in the same cloud region or connected through a dedicated private connection, the synchronization time is expected to be significantly shorter.
6. Business Performance After Migration
The most noticeable improvement after migrating to TiDB Cloud was business performance in OLAP workloads. Statistical analysis and complex queries became noticeably more efficient, reducing query wait times for business users and improving the overall query experience.
Going forward, we plan to explore enabling TiFlash, the columnar storage engine, based on specific business scenarios. This will allow us to further optimize analytical query performance and make better use of TiDB's HTAP architecture for workloads that combine row- and column-oriented processing.
Note: We have not yet completed a detailed analysis of application performance after the migration. Specific query performance comparisons will be added in a future update.
7. Conclusion and Recommendations
Overall Assessment
Overall, the migration from PostgreSQL to TiDB Cloud achieved the expected results.
SeaTunnel performed reliably as the migration tool. Both the full-load and incremental synchronization stages were completed successfully, with no data loss observed. TiDB Cloud delivered noticeable performance improvements for OLAP analytical workloads while significantly reducing the operational burden of database management.
Migration Recommendations for Other PostgreSQL Users
- Tool selection: We recommend SeaTunnel for its rich connector ecosystem and active community. Its JDBC connector can quickly adapt to both PostgreSQL and TiDB.
- Migration strategy: We recommend using a two-step full-load + incremental synchronization strategy, which provides better stability than the integrated mode.
-
Data consistency: Be sure to enable the
EXACTLY_ONCEcheckpoint mode and useupsertwrites to prevent duplicate records. - Network planning: Network quality between the source environment and TiDB Cloud has a significant impact on full-load synchronization time. Where possible, prioritize deployment within the same cloud environment or use a dedicated private connection.
-
Secure connections: TiDB Cloud requires TLS. Configure
sslMode=VERIFY_IDENTITYon the Sink side to ensure encrypted data transmission.
Top comments (0)