Time-series data is widely used in the oil and gas industry throughout production, transportation, processing, and sales. A large number of devices, sensors, and monitoring systems continuously generate time-series data to support production monitoring, scheduling optimization, and fault warning. A service provider in the oil and gas sector had been using Oracle Database to store time-series data for a large oilfield, but as the volume of business data grew, the system gradually began to experience performance bottlenecks in data processing and querying.
One Size Doesn’t Fit All
Although Oracle is well known for its stability and performance, it is not well-suited for storing massive amounts of time-series data, becoming less efficient and more expensive as the scale of the datasets increase. This is a fundamental issue caused by the architecture of relational databases and can be resolved only by migrating to a purpose-built time-series database.
As the company’s business systems continued to expand, it was forced to deploy dozens of Oracle instances, leading to high operational expenses, not to mention rising storage costs. Time-series databases have a natural advantage in handling massive volumes of time-series data, with ultra-high compression ratios that significantly reduce storage overhead. Their distributed architecture not only allows for flexible scaling of compute resources to support new business needs, but also enables easy expansion of storage nodes to meet the demands of ever-growing datasets.
Product Selection
The company compared several alternatives but chose TDengine for the following reasons:
- TDengine supports essential features such as clustering and high availability.
- TDengine’s innovative storage engine significantly boosts ingestion, query, and storage efficiency.
- TDengine supports standard SQL syntax, requiring minimal changes to existing application code.
- TDengine is in active development, with stable release cycles and continuous feature enhancements.
- TDengine’s core code is fully open source.
Migrating from Oracle Database
The company processed real-time data from more than 70 types of devices and oil wells. In the past, each type of device had a large dedicated table in Oracle, with all data from similar devices stored together. The field types were mostly NUMBER, DATE, and VARCHAR2. After migrating to TDengine, a supertable was created for each device type, and the data types were adjusted to the more specific TIMESTAMP, INT, DOUBLE, FLOAT, VARCHAR, and NCHAR.
The device ID was specified as the first tag in each supertable to identify specific devices or wells. Within each supertable, multiple subtables were be created with the same data structure as the supertable. This optimized design has significantly improved performance in both data ingestion and querying.
Benefits of TDengine
Since going live with TDengine, the system has remained stable, delivering significant improvements in performance, storage efficiency, and operational management. Throughout the migration and integration process, the TDengine team provided ongoing professional assistance to ensure the successful implementation of the project.
Performance Gains
- Ingestion: The data in this project included 2 billion records generated by 50,000 oil wells.
- Total ingestion time: 2,355.57 seconds
- Average ingestion speed: 849,121.05 records per second or 140,954,094.3 points per second
- Average write frequency: 208.25 ms
- Maximum write interval: 3000.03 ms
- Minimum write interval: 11.64 ms
- Query: The 2 billion ingested records, divided among 50,000 subtables, were retrieved using different queries.
- Output one day’s data from one device to a CSV file: average response time 2.64 seconds
- Return one day’s data from one subtable filtering by tag: average response time 0.29 seconds
- Perform aggregations COUNT, MIN, MAX, and AVG on data from one device: average response time 1.41 ms
- Perform same aggregations on all devices within a specified time range grouped by tag: average response time 2.29 seconds
- Perform same aggregations grouped by time window and tag:
- 1 hour time window: average response time 9.76 ms
- 1 day time window: average response time 8.72 ms
- Return latest metric from all devices: 0.311 seconds
High Compression and Efficient Storage
After migrating several large tables from Oracle, storage usage dropped significantly, with the compressed data reduced to less than 10% of the original data size, as shown in the system output below.
While more than 40 Oracle databases were required to support the company’s operations, now business units can meet demand by running only a single TDengine cluster each. TDengine also supports tiered storage, which has effectively improved write performance: multiple directories are mounted at level 0 to enhance I/O throughput. Expanding disk capacity on a server and adding nodes to scale the cluster are both straightforward processes in TDengine.
TDengine’s Data Subscription
TDengine provides data subscription and consumption interfaces similar to Kafka, allowing users to define topics within the system. In many scenarios, TDengine’s data subscription component can replace third-party applications, which greatly simplifies the system architecture and reduces maintenance costs.
In the company’s data quality monitoring application, data subscription was used to check whether reported data points fell within a reasonable range, enabling real-time data validation and alerts. TDengine’s subscription interface also enabled real-time data synchronization across multiple clusters, enabling read-write separation and further enhancing the stability of the company’s production environment.
In addition, data subscription assisted in the verification of data ingestion accuracy by detecting duplicate or out-of-order data entries. After optimization and adjustments, the system achieved further improvements in both read/write performance and storage efficiency.
Conclusion
By migrating from Oracle to TDengine, this oil & gas service provider significantly reduced storage costs, improved query performance, and simplified system maintenance—all without major changes to their applications. The successful transition not only streamlined their time-series data processing but also laid a solid foundation for scaling future data workloads, earning high satisfaction from both technical and operational teams.
