설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


총 게시물 85건, 최근 0 건
   

Database Design and Performance

글쓴이 : 모델광 날짜 : 2021-10-29 (금) 05:48 조회 : 82

When I am involved in a project as an SQL tuner, from time to time, there are queries that I cannot optimize. Below are several factors which prevent sql tuners or developers from writing performant queries.

* The query tuner or developer doesn't understand the business intent.

* The database design is not right for the usage of the data.


It is very tough to evaluate someone else's database design. If you do not have any exposure to the business specifics and the purpose of the new tables and views, there is not much you can do to determine whether the proposed design is optimal. The only thing you may be able to evaluate without knowing the business requirements is whether the database design is normalized. However, the fact that the table is normalized doesn't guarantee that the queries on the table can be performant.


Thus, query optimization starts with gathering requirements. To illustrate the argument, let's look at the following example: in this database, we need to store dealer ids, and we need to store the date on which each dealer wrote their signature. Two possible designs are shown below.


* Denormalized table design

CREATE TABLE dealer_m (

dealer_id                  int not null,

dealer_name            varchar(50) not null,

dealer_jumin_no       varchar(13) not null,

phone_no                 varchar(13),

email                       varchar(100),

declaration_sign_date date not null,

release_sign_date      date,

update_sign_date      date,

change_sign_date      date);

ALTER TABLE dealer_m ADD CONSTRAINT dealer_id_pk PRIMARY KEY (dealer_id);


* Normalized table design

CREATE TABLE dealer_m (

dealer_id               int not null,

dealer_name         varchar(50) not null,

dealer_jumin_no    varchar(13) not null,

phone_no              varchar(13),

email                    varchar(100));

ALTER TABLE dealer_m ADD CONSTRAINT dealer_id_pk PRIMARY KEY (dealer_id);


CREATE TABLE dealer_sign_m (

dealer_id              int not null,

sign_type_code     varchar(1) not null

sign_date              date          );

ALTER TABLE dealer_sign_m ADD CONSTRAINT dealer_sign_m_pk PRIMARY KEY (dealer_id, sign_type_code);

ALTER TABLE dealer_sign_m ADD CONSTRAINT dealer_sign_m_fk FOREIGN KEY (dealer_id) REFERENCES dealer_m(dealer_id);


Which of the two designs is the right one? It depends on the intended usage of the data. If the dates on which the dealer writes their signature are never used as search criteria and are selected as a part of a dealer_id, if UX has fields labeled with specific signature types, then a denormalized design is more appropriate.

However, if we want to search by the signature date regardless of type, having all the signature dates in a separate table will make the search more performant.

Also, there are chances that business requirements can be changed. Taking this into account the latter design is more desirable.

Lastly, we can evaluate the frequency of each use case and how critical response time is in each case.


Conclusion

Ideally, optimization begins at the time of gathering business requirements and designing the database. We should be aware of the database design and its implications on the query performance.


Addendum

If you think database design is some ancient thing that shouldn't be done anymore, you might be interested in Doctor Stonebraker's blog post here:


https://www.enterprisedb.com/blog/schema-later-considered-harmful


   

postgresdba.com