In today’s world, the volume of data
Often, the bottleneck in IT infrastructure that causes users to wait and businesses to lose customers is the inefficient performance of the database. As a result, companies invest billions of dollars each year in maintaining and optimizing them. Over the past five years, the database management systems market has more than doubled, growing from
With over 10 years of experience in the field of manufacturing analytics, where the speed of data processing from real-time IIoT sensors directly impacts production efficiency, I’ve seen firsthand how critical this is. In this article, I’ll share my observations on the most common issues that slow down query processing and how to optimize database performance to provide instant results to customers.
Challenges in working with large databases
A typical drilling platform with 80,000 IIoT sensors
- Unoptimized database structure. The lack of indexes, inaccurate data types, fragmentation, or redundant records can all slow down queries and overload the disk and memory. It’s essential to regularly audit the database schema based on actual load.
- Insufficient computational resources. Working with billions of records requires ample computational and system resources (CPU, RAM, IOPS). If resources are lacking, the system will struggle during peak loads. Solutions such as autoscaling in the cloud or serverless engines that scale automatically can help.
- Poor data quality. This leads to unnecessary checks during queries, which slow down the system. It’s better to handle these processes in the ETL/ELT layers or streaming pipelines so that queries only work with “clean” tables.
- Scaling issues. As data volume grows, many parallel queries can cause delays due to locking or input/output bandwidth limitations. It’s important to configure query pools, job queues, concurrency control, and limits on heavy ad hoc queries—especially in OLTP systems.
- Bottlenecks in queries. Even in a well-structured database, suboptimal SQL queries can decrease performance. Run EXPLAIN analyzes, use CTEs or window functions instead of nested subqueries, and cache repetitive aggregations.
When the tool becomes part of the problem
The issues listed above can be resolved by optimizing queries, improving the architecture, or scaling the system. However, if you've chosen the wrong type of database, these efforts will be in vain.
I'll explain using the example of the Waites product. We work with data from industrial sensors that record vibration, temperature, and a dozen other parameters every second as part of equipment operation. These are classic time series data, continuously flowing and time-stamped. Initially, we used a file-based storage approach organized in a folder structure. However, as the data volume grew, this became inefficient: each query required scanning large amounts of files and took up to 10 seconds. For a client monitoring how a piece of equipment is performing, that’s too long.
To speed up processing, we initially switched to InfluxDB and later moved to TimescaleDB, a PostgreSQL extension optimized for time series data. It scales well and allows for data compression and archiving. The result was an 80% increase in performance and a reduction in query time to 2 seconds.
Choose a database based on the type of data you're working with and the tasks the system needs to accomplish:
-
Relational databases (SQL) are best suited for structured datasets with clearly defined relationships, such as: client – order – invoice. They are commonly used in financial and insurance sectors, manufacturing and logistics, as well as CRM and HR platforms. Systems like MySQL, PostgreSQL, Oracle, or MS SQL work well with stable data models.
-
NoSQL databases (document, key-value, graph) are designed for flexible or loosely structured data (JSON documents, relationship graphs) and scale horizontally without complex schemas. They are used in media services, social networks, and marketing or analytical platforms. MongoDB, Cassandra, or DynamoDB quickly write and read large volumes of diverse data, but they are not optimized for high-frequency time series data.
-
Time series databases are optimized for data where each entry has a timestamp—“when” is just as important as “what.” These DBMSs are often used in industries that collect data from IIoT sensors, in FinTech, telecom, and network monitoring. InfluxDB, TimescaleDB, or OpenTSDB can record and aggregate billions of data points per day without delays.
In real architectures, it’s not necessary to rely on a single database. On the contrary, combining several DBMSs for different types of data allows performance to be maintained.
How to maintain stable data processing speed
For a database to work efficiently even with billions of records, in addition to choosing the right DBMS type, you need to follow several principles:
- Optimize queries. Tools like EXPLAIN (for SQL databases) help visualize how a query is executed and identify bottlenecks. For example, adding an index to frequently queried columns can speed up data retrieval by orders of magnitude.
- Balance indexing. While indexes speed up searches, an excess of them can slow down data writing. It's crucial to strike a balance between read speed and data update efficiency.
- Plan for scalability. Data volumes often grow faster than available resources. For example, in industrial settings, during peak periods such as holidays when all equipment is operating at full capacity, data volumes can increase exponentially. Cloud solutions like Amazon Aurora Serverless enable automatic scaling to handle the load.
- Monitor performance. Use tools like Datadog or New Relic to detect slow queries, CPU overload, or memory shortages before users experience issues.
When working with billions of records, the key to maintaining stable performance lies in continuous architecture improvement. A well-chosen database, query optimization, and readiness for scaling form the foundation of an efficient system. This ensures that your data works for the client, rather than just accumulating in storage.