Big business problems start with the few seconds a customer spends waiting on a company’s website. A user clicks «Search» or «Place Order» and instead of an instant result, they see a loading animation. During that time, they could be moving on to a competitor. A website loading delay of even 100 milliseconds can reduce conversion rates by 7%.
Big business problems start with the few seconds a customer spends waiting on a company’s website. A user clicks «Search» or «Place Order» and instead of an instant result, they see a loading animation. During that time, they could be moving on to a competitor. A website loading delay of even 100 milliseconds can reduce conversion rates by 7%.
Next is a direct speech by Illia Smoliienko, CEO Europe at Waites.
I have been working in the manufacturing analytics space for almost 10 years, where the speed of processing real-time data from IIoT sensors is critical to manufacturing efficiency. In this column, I will share my observations: what issues most often slow down query processing and how to optimize database performance so that customers receive instant results.
Database performance is most often slowed down by errors in system architecture and maintenance. Here are the main ones:
Bottlenecks in SQL queries. Suboptimal SQL queries reduce performance. Use EXPLAIN analyses to see exactly how the database executes them. Replace nested subqueries with CTEs or window functions, cache repeated aggregations. A single line of code can speed up a query by dozens of times.
Unoptimized database structure. If indexes are created without taking into account queries, data types are chosen incorrectly, or there are redundant relationships, the database starts to lag. We have seen cases where a simple update of the table schema reduced the response time by three times. It is important to periodically audit the structure: what users are actually asking for, how often the data changes, and whether there are duplicate fields.
Poor data quality. Dirty, duplicated, or incorrect data creates an avalanche of checks that slow down every query. It is better to clean the information at the ETL/ELT stage or through streaming pipelines. Queries should only work with already checked tables — then both the speed and accuracy of the results will be stable.
Lack of computing resources. Even the best query optimization won’t help if the server can’t handle the load. When thousands of transactions are running simultaneously, CPU and memory quickly become bottlenecks. If your database is hosted in the cloud, you should enable autoscaling — this allows you to automatically increase resources during peak loads and reduce them when traffic drops.
Scaling issues. As data volumes grow, the number of parallel queries also grows. If the system does not have queues, connection pools, and limits on «heavy» queries, it may simply freeze. In transactional (OLTP) databases, this is critical: a delay of even half a second can cause a failure in the entire business logic.
To make your database run fast even with billions of records, you need to follow several principles:
Waites works with data from industrial sensors that record vibration, temperature, and dozens of other parameters in the operation of equipment every second. This is classic time series data that comes in constantly and has a time reference. At the initial stage, we used a file-based storage approach, organized in the form of a folder structure. But as the volume of data increased, this became inefficient: each request required scanning large volumes of files and took up to 10 seconds. If the client is monitoring how some equipment behaves, this is a long time for him.
To speed up processing, we first switched to InfluxDB and then to TimescaleDB, a PostgreSQL extension optimized for time series. It scales well and allows for data compression and archiving. The result was an 80% performance increase and query times down to 2 seconds.
It would seem that it was just a technical update, but it restored user trust and allowed the business to scale.
A fast database is a competitive advantage. When you’re working with billions of records, the key to consistent speed is to continually improve your architecture. The right database, query control, and scalability are the foundation of a productive system. This way, your data will work for the client, not just sit in storage.
He specializes in developing solutions in the field of predictive maintenance of industrial equipment and the Industrial Internet of Things (IIoT) and has over a decade of practical experience in this field. For the Waites platform, he and his team built an ecosystem of 12 integrated client services from scratch and led the implementation of IIoT solutions for equipment condition monitoring at global companies, including DHL, Michelin, Nike, Nestlé and Tesla.
