Primary Key Uniqueness란?

• 테이블에서 하나의 레코드를 유일하게 지칭할 수 있는 필드(들)

  • 이를 CREATE TABLE 사용시 지정

• 관계형 데이터베이스 시스템이 Primary key의 값이 중복 존재하는 것을 막아줌

• 예 1) Users 테이블에서 email 필드

• 예 2) Products 테이블에서 product_id 필드

  CREATE TABLE products (
   product_id INT PRIMARY KEY,
   name VARCHAR(50),
   price decimal(7, 2)
  );
  

• 하나의 필드가 일반적이지만 다수의 필드를 사용할 수도 있음

  CREATE TABLE orders (
   order_id INT,
   product_id INT,
   PRIMARY KEY (order_id, product_id),
   FOREIGN KEY (product_id) REFERENCES products (product_id)
  );
  -- product 테이블을 참조
  
  

• Foreign key를 두게 되면 SQL 옵티마이저가 쿼리 최적화를 더 잘할 수 있게 된다.

빅데이터 기반 데이터 웨어하우스들은 Primary Key를 지켜주지 않음

• 정책적으로 Primary key를 기준으로 유일성 보장을 해주지 않음
  • 이를 보장하는 것은 데이터 인력의 책임
  • 각각 ETL, ELT를 수행할 때 보장을 해줘야한다.
• 빅데이터 기반 데이터 웨어하우스 ⇒ Primary key 유일성을 보장해주지 않는 이유는?

  • 모든 레코드들의 Pk를 B+Tree 형태로 메모리에 올려놓은 후 룩업을 해야한다

    • 레코드가 추가될 때마다 PK가 존재하고 있는가를 체크를 매번 해야한다.
    • 체크를 하기 위한 효율적인 자료구조를 만들어놔야할 확률이 높다
    • OLTP는 이런 pk uniqueness를 꼭 필요로 하지만, 일반적으로 데이터의 크기가 작기 때문에, pk uniqueness를 매번 확인하는 작업이 부담이 되지 않는다.

  • 이로 인해 보장하는데 메모리와 시간이 더 들기 때문에 대용량 데이터의 적재가 걸림돌이 된다

    CREATE TABLE keeyong.test (
    date date primary key,
    value bigint
    );
    
    INSERT INTO keeyong.test VALUES ('2023-05-10', 100);
    INSERT INTO keeyong.test VALUES ('2023-05-10', 150); 
    -- 두번째 작업이 성공함! 일반 RDB는 성공 못함
    -- pK를 지정한다고 유일성 보장 X
    

Primary Key 유지 방법 - SQL 기반 Upsert

Upsert란?

• Primary Key를 기준으로 존재하는 레코드라면 새 정보로 수정

• 존재하지 않는 레코드라면 새 레코드로 적재

• 보통 데이터 웨어하우스마다 UPSERT를 효율적으로 해주는 문법을 지원해줌

  • MySQL to Redshift DAG를 구현할 때 살펴볼 예정

• 아래에서는 Upsert를 row number를 통해 구현

• 데이터 웨어하우스들은 Primary Key를 지켜주지 않음