This is an AI translated post.
Physical Data Modeling
- Writing language: Korean
- •
-
Base country: All countries
- •
- Information Technology
0000-00-00 00:00:00
Select Language
Summarized by durumis AI
- If logical data modeling is the task of creating tables suitable for relational databases, physical data modeling is the task of creating tables for actual use, taking into account storage space efficiency and performance optimization.
- Performance can be improved by utilizing slow queries, indexes, and caches, and if performance issues persist, denormalization, which changes the database structure, can be considered.
- Normalization sacrifices read performance for write convenience, but denormalization can lead to performance degradation, so it is important to identify the problem accurately and make a careful decision.
If logical data modeling was about creating the ideal tables for a relational database, physical data modeling involves transforming those ideal tables into practical ones, taking into account efficient storage utilization, object partitioning design, optimal index design, and other performance enhancement and optimization considerations.
Finding slow queries that cause bottlenecks in service operations varies depending on the type of DBMS, and you need to search for them using the keywordSlow Query.
Things to Consider for Performance
Finding Slow Queries
- Slow queries are queries that fail to execute within a certain time frame when the DBMS receives a request.
- Identify slow queries that cause bottlenecks, determine the root of the problem, and resolve them.
Index
- Indexes dramatically enhance read performance on rows.
- However, they reduce write performance and require more storage space.
Cache
- A way to reduce database load at the application level.
- It stores and responds to requests with the same input in the storage space.
If performance issues persist even after employing these methods, proceed with a process called denormalization or inverse normalization.
This involves modifying the structure of the table.
Trade-off of Normalization
Normalization is akin to sacrificing read performance for the convenience of write operations. When normalization is performed, you need to write queries to join data from multiple tables.
However, normalization doesn't always degrade performance, so it's crucial to accurately identify and review the issue before proceeding with denormalization.
The link below presents a valuable article discussingDenormalization.
Denormalization, Inverse Normalization
It's essential to understand that denormalization should be carried out after normalization. Starting with a non-normalized table is not recommended.
This knowledge isn't immediately necessary, so we'll simply take note of it for now.
Table Denormalization Techniques
Source - DataOnAir - Denormalization and Performance
Column Denormalization
Source DataOnAir - Denormalization and Performance
Relationship Denormalization
Source - DataOnAir - Denormalization and Performance
