Physical Data Modeling

If logical data modeling was the process of creating ideal tables suitable for relational databases, physical data modeling involves creating those ideal tables into actual usable tables, and focuses on performance improvement and optimization, including efficient storage space planning, object partitioning design, and optimal index design.

The method of finding slow queries that cause bottlenecks during service operation varies depending on the type of DBMS, and you need to search using slow query as a keyword.

Considerations for Performance

Finding Slow Queries

• A slow query refers to a query that fails to execute within a certain time frame when the DBMS executes a requested query.
• Find slow queries causing bottlenecks, identify the problem, and resolve it.

Index

• Indexes dramatically improve read performance for rows.
• However, they degrade write performance and consume more storage space.

Cache

• A method to reduce the load on the database at the application level.
• It stores the response to a request with the same input in storage and responds using that stored response.

If the performance issue is not resolved with the above methods, a process called denormalization or inverse normalization is performed.

It involves surgically modifying the structure of the table.

Trade-off of Normalization

Normalization is like sacrificing read performance for the convenience of write operations. When normalization is applied, queries need to be written to join the data from multiple split tables.

However, normalization does not necessarily degrade performance, so it is essential to properly identify and examine the problem before proceeding with denormalization.

The link below is a good article that discusses denormalization, which we will cover later.

[DataOnAir - Normalization and Performance]

Denormalization

First, it's important to understand that denormalization should be performed after normalization. A non-normalized table is not always ideal from the beginning.

Since this knowledge is not immediately necessary right now, I'm going to just note it for now.

Table Denormalization Techniques

Column Denormalization

Relationship Denormalization