Unlocking Efficiency: The Power of Denormalization
For a long time, normalizing data tables in relational databases has been regarded as a standard best practice. But what if an alternative approach could offer additional benefits? This piece delves into the merits of denormalizing relational tables, featuring a case study from the real world that demonstrates considerable enhancements.
The Norm of Normalization
When we were in the university, we learned how to store data in a database wisely. The principle we learned was to normalize data through at least three key forms of normalization: First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). These forms help us organize data efficiently, minimize redundancy, and ensure data integrity.
When Normal Isn’t Always Better
In the information systems industry, numerous unique challenges arise, and various factors must be considered when designing a database. One of the important variables is the three normal forms of data normalization. A database that follows the three normal forms can typically be well-equipped to handle a wide range of challenges.
Over time, data grows, and the demand for fast and stable database performance becomes essential. As the database continues to accumulate data, it becomes crucial to maintain it in top condition. To achieve this, several options can be implemented, such as database sharding, replication, and query optimization. One less popular option is denormalization, which might improve performance in certain situations. Database normalization can result in a complex structure that reduces performance and denormalization provides a way to address overly complex structures and enhance performance.
The Issues
The extranet team, experienced a issue when storing a huge amount of data regularly. The process of storing data often experiences timeouts, even more, sometimes it impacts the stability of the service. In response, the extranet team made it asynchronous and queued the data into a message broker which could maintain service stability. This solution worked well until we faced another issue, the Kafka lag steadily increased.
In addition, the extranet team also encountered another issue while retrieving the data. The query latency increased in line with the growing data in the database. We implemented some experiments and quick actions, such as reviewing the indexing tables and adjusting the query.
Last but not least, the database storage size was growing rapidly. Some temporary measures were taken, such as cutting old data as much as possible and limiting the incoming new data.
Denormalize the database
The extranet team realized that to obtain one set of data, it needed to query at least four tables. Almost all data processing consistently involves combining these four tables that contain hundreds of millions up to billions of rows. Rarely does the team process data using just one or two tables. When addressing the three issues mentioned above, there are the following conclusions:
a. Storing data: All pathways for saving data follow the same process, requiring insertion into at least four tables. Each query cannot run asynchronously as they are dependent on each other.
b. Retrieving data: When querying data, it almost always involves joining all those tables. Again, each table is large, containing hundreds of millions and even billions of rows.
c. Storage: Each set of data stored, results in multiple rows in several tables, causing the number of rows to grow rapidly.
From these three conclusions, the extranet team decided to refactore the database schema. However, several incoming questions come to light in the next step:
- Is it worth refactoring the database?
- What is the impact?
- What is the scale of the cost (in terms of time, manpower, risk, and data migration)?
The answer may vary for each team and under different conditions, but in the case of the extranet team, all the questions can be addressed. Furthermore, the extranet team has started discussing ways to minimize risks as effectively as possible. During the development process, the extranet team conducts performance testing asynchronously to ensure the results align with the expected calculations. For reversibility, each module has its toggle to use the newly denormalized tables. Below is an illustration of how the data is denormalized:
The explanation for the illustration above:
- merging four tables to be a single table
- removing unused columns (old_location_id, city_id)
- data level with a single field merging to 1 row, and put it as an array. See the temperature column with data level hotspot_number (after denormalization). Array 0 represents as hotspot_number 1 and so on)
- duplication occurs on column approval and email_blast (saving approval and email_blast will take more time)
The Results
a. Storing data: after denormalizing the table, it now only needs to be saved to one table. As a result, the SLA for inserting data is now under 10 minutes. Before denormalization, it takes a maximum of 3.8 hours (1.93 hours average), while after denormalization, it takes a maximum of 9.7 minutes (1.31 minutes average).
b. Retrieving data: By eliminating three join tables, a significant impact on the query has been achieved. Additionally, the total number of rows has been reduced. After applying denormalization, the maximum latency was reduced by five times, decreasing from approximately 1.5 seconds to around 300 milliseconds.
c. storage: Database storage usage was reduced by more than half. Before denormalization, the usage of database storage was 2.11 TiB and reduced to 965 TiB after deleting old tables.
d. Database operation: As a bonus, the reduction in complexity and query latency has brought a very significant impact on database operations. CPU usage fell to around 12% from 28%, and the number of database operations per minute decreased from around 4 million to under 1 million.
Conclusions
The success of the extranet team illustrates the potential benefits of denormalizing data tables, showcasing improvements in speed and efficiency. Denormalization can be a useful approach under specific conditions. Nonetheless, reorganizing the database requires considerable effort, and numerous factors must be carefully evaluated.
