🐘 PostgreSQL 기본 Ⅷ - 적시적소에 알맞은 테이블 디자인

📙 『실용 SQL』을 읽고 정리한 글입니다.

네이밍 컨벤션 따르기

• 카멜 케이스, 파스칼 케이스, 스네이크 케이스 등 어떤 규칙을 선호하든 스타일을 선택하고 일관되게 적용하는 것이 중요하다.
• 무엇보다 속한 조직의 스타일 가이드나 컨벤션에 따르는 것이 가장 중요하다.

인용 식별자로 대소문자 혼합 사용하기

CREATE TABLE "Customers" (
	-- ...
)

• PostgreSQL은 대소문자에 관계 없이 식별자를 소문자로 처리한다.
• 만약 대소문자를 혼합하여 사용해야 한다면 큰따옴표로 감싸야 한다.

인용 식별자의 함정

• 인용 식별자를 사용하면 공백을 포함하여 예약어 등 허용되지 않았던 문자도 허용하게 된다.
• 그러나 이 문제에는 큰 함정이 존재한다.
• 모든 사용자는 이후의 모든 열 참조에 큰따옴표를 제공해야 한다.
• 가령 큰따옴표를 생략하면 허용되지 않았던 문자에 대해 SQL 오류가 발생한다.
• 가장 신뢰할 수 있는 방법은 _를 사용하여 애초에 스네이크 케이스를 선택하는 것이다.

이름 지정 식별자 가이드라인

1. 스네이크 케이스 사용
2. 이름을 이해하기 쉽게 짓고 암호 같은 약어 사용 지양
3. 테이블 이름으로는 복수형 사용
   • CSV 파일의 이름을 보존하기 위해 해당 파일을 테이블 이름으로 사용하는 경우는 예외 상황으로 간주한다.
4. 길이를 신경 쓰기
   • 데이터베이스 응용 프로그램에 따라 식별자 이름에 허용되는 길이 수가 다르다.
   • 다른 데이터베이스에서 재사용될 수 있는 코드를 작성하는 경우 더 짧은 식별자 이름이 권장된다.
5. 테이블을 복사할 때 나중에 관리하는데 도움되는 이름을 사용하기
   • 한 가지 방법은 YYYY_MM_DD 형식의 날짜를 테이블 이름에 추가하는 것이다.
   • 이 방식은 테이블 이름이 날짜 순서로 정렬되기 때문에 권장된다.

제약 조건으로 열 값 제어하기

• 제약 조건을 사용하면 품질이 좋지 않은 데이터로 인해 분석이 부정확하거나 불완전한 가비지 인, 가비지 아웃 현상을 피할 수 있다.
• 다음은 SQL의 제약 조건 유형이다.

1. CHECK: 제공된 부울 표현식이 true로 평가되는 행만 허용
2. UNIQUE: 열 또는 열 그룹의 값이 테이블의 각 행에서 고유한지 확인
3. NOT NULL: 열의 NULL 값을 방지

기본 키: 자연 키 vs 인조 키

• 기본 키는 SQL 제약 조건으로 키를 구성하는 열에 다음 규칙 두 가지를 적용한다.

1. 각 값은 행마다 고유해야 한다.
2. 열에 값이 누락된 부분이 있어서는 안 된다.

기존 열을 자연 키로 사용하기

• 자연 키는 테이블에 있는 열 중에서 기본 키의 기준을 충족하는 열 또는 열의 조합을 사용한다.
• 즉 해당 열의 값은 모든 행에 대해 고유하고 비어있지 않아야 한다.
• 열의 값은 제약 조건을 위반하지 않는다면 얼마든지 변경할 수 있다.

인조 키를 위한 열 생성

• 인조 키는 임의의 값을 부여한다.
• 테이블에 자연 기본 키를 생성할 수 있는 데이터가 없을 때 사용한다.
• 일부 개발자는 UUID를 선호하는데, 용량 면에서 bigint에 비해 비효율적이므로 유의해야 한다.

키 유형 별 장단점

자연 키

• 데이터가 이미 테이블에 있으므로, 키 생성을 위한 열을 추가할 필요가 없다.
• 자연 키 자체로도 의미가 있으므로, 쿼리 시 테이블을 조인할 필요성이 줄어든다.
• 데이터가 키 요구 사항을 위반하는 방식으로 변경되면 테이블 설정을 변경해야 함

인조 키

• 그 자체로 의미가 없고, 해당 값이 테이블의 데이터와 독립적이기 때문에 나중에 데이터가 변경되더라도 키 구조에 제한을 받지 않는다.
• 키 값은 고유해야 한다.
• 인조 키에 대한 열을 추가하려면 공간이 더 필요하다.

정리

• 가장 이상적인 테이블이라면, 테이블의 고유한 코드와 같이 자연 키로 사용할 수 있는 열이 하나 이상 있어야 한다.
• 그러나 현실 세계에는 늘 한계가 있고, 테이블 구조를 재고할 수 없는 경우 인조 키를 사용해야 한다.

단일 열 기본 키 생성

-- 열 제약 조건
CREATE TABLE natural_key_example (
	license_id text CONSTRAINT license_key PRIMARY KEY,
	first_name text,
	last_name text
);

-- 테이블 제약 조건
CREATE TABLE natural_key_example (
	license_id text,
	first_name text,
	last_name text,
	CONSTRAINT license_key PRIMARY KEY (license_id)
);

• 열 제약 조건을 설정할 때는 CONSTRAINT 키워드를 생략해도 간단하게 기본 키를 설정할 수 있다.
• 이 경우 PostgreSQL은 자체적으로 테이블 이름 뒤에 _pkey를 붙여 기본 키의 이름을 지정한다.
• 만약 둘 이상의 열을 사용하여 기본 키를 만들려면 테이블 제약 조건 구문을 사용해야 한다.

복합 기본 키 생성

CREATE TABLE natural_key_composite_example (
student_id text,
school_day date,
present boolean,
CONSTRAINT student_key PRIMARY KEY (student_id, school_day)
);

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "student_key"
  Detail: Key (student_id, school_day)=(775, 2022-01-23) already exists.

• 중복된 복합 키가 포함된 행을 삽입하면 위 에러가 발생한다.

자동 증가 인조 키 생성

CREATE TABLE surrogate_key_example (
	order_number bigint GENERATED ALWAYS AS IDENTITY,
	product_name text,
	order_time timestamp with time zone,
	CONSTRAINT order_number_key PRIMARY KEY (order_number)
);

INSERT INTO surrogate_key_example (product_name, order_time)
VALUES ('Beachball Polish', '2020-03-15 09:21-07'),
	('Wrinkle De-Atomizer', '2017-05-22 14:00-07'),
	('Flux Capacitor', '1985-10-26 01:18:00-07');

SELECT * FROM surrogate_key_example;

• 위 코드는 IDENTITY 구문을 사용하여 order_number라는 자동 증가 bigint 타입 열을 선언한 후, 해당 열을 기본 키로 설정한다.

order_number	product_name	order_time
1	Beachball Polish	2020-03-16 01:21:00.000 +0900
2	Wrinkle De-Atomizer	2017-05-23 06:00:00.000 +0900
3	Flux Capacitor	1985-10-26 17:18:00.000 +0900

• 테이블에 데이터를 삽입할 때는 order_number 열에는 값을 지정하지 않아도 괜찮다.
• 데이터베이스가 자동으로 기존 ID 값 중 가장 큰 값으로 자동으로 생성해준다.
• GENERATED ALWAYS AS IDENTITY 키워드를 통해 사용자가 설정을 무시하고 값을 억지로 삽입하는 행위를 방지하는 것이 중요하다.

INSERT INTO surrogate_key_example
OVERRIDING SYSTEM VALUE
VALUES (4, 'Chicken Coop', '2021-09-03 10:33-07');

ALTER TABLE surrogate_key_example ALTER COLUMN order_number RESTART WITH 5;

INSERT INTO surrogate_key_example (product_name, order_time)
VALUES ('Aloe Plant', '2020-03-15 10:09-07');

• OVERRIDING SYSTEM VALUE 키워드를 통해 IDENTITY 수동 삽입을 허용할 수 있다.
• 가령 실수로 삭제된 행을 삽입하는 경우 수동 입력을 허용할 수도 있다.
• 삽입 후에는 시퀀스를 재시작하도록 설정하여 다음 ID 값을 설정할 수 있다.
• 자주 처리할 작업은 아니지만 필요할 때가 있으니 기억해두어야 한다.

외래 키

CREATE TABLE licenses (
	license_id text,
	first_name text,
	last_name text,
	CONSTRAINT licenses_key PRIMARY KEY (license_id)
);

CREATE TABLE registrations (
	registration_id text,
	registration_date timestamp with time zone,
	license_id text REFERENCES licenses (license_id),
	CONSTRAINT registration_key PRIMARY KEY (registration_id, license_id)
);

INSERT INTO licenses (license_id, first_name, last_name)
VALUES ('T229901', 'Steve', 'Rothery');

INSERT INTO registrations (registration_id, registration_date, license_id)
VALUES ('A203391', '2022-03-17', 'T229901');

INSERT INTO registrations (registration_id, registration_date, license_id)
VALUES ('A75772', '2022-03-17', 'T000001');

• 외래 키 제약 조건을 설정했기 때문에, registrations 테이블에 데이터를 삽입할 때는 license_id 값이 licenses 테이블에 존재하는 값인지 확인한다.
• 그렇기 때문에 위 코드 중 세 번째 INSERT 구문은 다음 오류를 발생시킨다.

SQL Error [23503]: ERROR: insert or update on table "registrations" violates foreign key constraint "registrations_license_id_fkey"
  Detail: Key (license_id)=(T000001) is not present in table "licenses".

• 위 오류 메시지는 데이터를 깨끗하게 유지하고 있음을 나타내기도 하는 반면, 다음과 같은 몇 가지 실질적인 의미를 나타낸다.

1. 외래 키는 데이터 삽입 순서에 영향을 준다.
2. 데이터를 삭제할 때는 그 반대로 적용된다.

• 2번에 대한 부연 설명을 하자면, 참조 무결성을 유지하기 위해 외래 키 제약 조건은 registrations 테이블에서 관련 행을 제거하기 전에 licenses 테이블의 행을 제거하지 못하도록 한다.
• 이런 경우 CASCADE 키워드를 사용하면 된다.

CASCADE로 관련 행 자동 삭제하기

CREATE TABLE registrations (
	registration_id text,
	registration_date timestamp with time zone,
	license_id text REFERENCES licenses (license_id) ON DELETE CASCADE,
	CONSTRAINT registration_key PRIMARY KEY (registration_id, license_id)
);

• 테이블을 위 코드와 같이 생성할 경우 licenses 테이블의 행을 삭제할 경우 관련 테이블의 모든 관련 행이 함께 삭제된다.
• registrations 테이블에 고립된 데이터가 남지 않도록 하여 데이터 무결성을 유지한다.

CHECK 제약 조건

CREATE TABLE check_constraint_example (
	user_id bigint GENERATED ALWAYS AS IDENTITY,
	user_role text,
	salary numeric(10,2),
	CONSTRAINT user_id_key PRIMARY KEY (user_id),
	CONSTRAINT check_role_in_list CHECK (user_role IN('Admin', 'Staff')),
	CONSTRAINT check_salary_not_below_zero CHECK (salary >= 0)
);

-- 아래 두 명령어는 실행에 실패
INSERT INTO check_constraint_example (user_role)
VALUES ('admin');

INSERT INTO check_constraint_example (salary)
VALUES (-10000);

• CHECK 제약 조건은 열에 추가된 데이터가 논리적 테스트로 지정한 예상 기준을 충족하는지 여부를 평가한다.
• 기준이 충족하지 않으면 데이터베이스에서 오류를 반환한다.

개발자는 검사 로직이 데이터베이스에 있는지, 응용 프로그램에 있는지, 또는 둘 다에 속하는지 토론할 수 있다. 데이터베이스 검사의 한 가지 장점은 응용 프로그램이 변경된 후에도, 그 데이터베이스를 사용하는 새로운 응용 프로그램이나 사용자가 직접 데이터베이스에 접근하는 경우에도 데이터베이스가 무결성을 유지한다는 점이다.

UNIQUE 제약 조건

CREATE TABLE unique_constraint_example (
	contact_id bigint GENERATED ALWAYS AS IDENTITY,
	first_name text,
	last_name text,
	email text,
	CONSTRAINT contact_id_key PRIMARY KEY (contact_id),
	CONSTRAINT email_unique UNIQUE (email)
);

INSERT INTO unique_constraint_example (first_name, last_name, email)
VALUES ('Samantha', 'Lee', 'slee@example.org');

INSERT INTO unique_constraint_example (first_name, last_name, email)
VALUES ('Betty', 'Diaz', 'bdiaz@example.org');

INSERT INTO unique_constraint_example (first_name, last_name, email)
VALUES ('Sasha', 'Lee', 'slee@example.org');

• 기본 키와 동일하게 보일 수도 있지만, UNIQUE 제약 조건은 NULL 값을 허용한다.
• 동일한 이메일이 포함된 행을 삽입하려고 하면 다음 오류가 발생한다.

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "email_unique"
  Detail: Key (email)=(slee@example.org) already exists.

NOT NULL 제약 조건

CREATE TABLE not_null_example (
	student_id bigint GENERATED ALWAYS AS IDENTITY,
	first_name text NOT NULL,
	last_name text NOT NULL,
	CONSTRAINT student_id_key PRIMARY KEY (student_id)
);

-- 아래 명령어는 실행에 실패
INSERT INTO not_null_example (first_name, last_name)
VALUES ('Sting', NULL);

제약 조건을 제거하거나 나중에 추가하기

-- 키 삭제
ALTER TABLE not_null_example DROP CONSTRAINT student_id_key;

-- 기본 키 추가
ALTER TABLE not_null_example ADD CONSTRAINT student_id_key PRIMARY KEY (student_id);

-- 열 수정
ALTER TABLE not_null_example ALTER COLUMN first_name DROP NOT NULL; -- 삭제
ALTER TABLE not_null_example ALTER COLUMN first_name SET NOT NULL;  -- 추가

• 대상 열 데이터가 제약 조건의 제한을 따르는 경우에만 기존 테이블에 제약 조건을 추가할 수 있다.
• 예를 들어 값이 중복되거나 비어 있는 열에는 기본 키 제약 조건을 추가할 수 없다.

인덱스로 쿼리 속도 향상시키기

• 책의 목차처럼, 테이블에서도 하나 이상의 열에 인덱스를 추가하여 쿼리 속도를 높일 수 있다.
• 데이터베이스는 데이터를 찾기 위해 각 행을 스캔하는 대신 인덱스를 바로 가기로 사용한다.
• 이는 사소한 것처럼 보이지만 SQL 데이터베이스에서 꽤 중요한 주제이다.

B-Tree: PostgreSQL의 기본 인덱스

• 이미 알게 모르게 여러 인덱스를 만들어 보았다.
• 가령 기본 키를 추가하거나, UNIQUE 제약 조건을 추가할 때마다 제약 조건에 포함된 열에 인덱스를 생성한다.
• PostgreSQL의 기본 인덱스 유형은 B-Tree 인덱스이다.
• 기본 키를 추가하거나, UNIQUE 제약 조건을 추가할 때, CREATE INDEX 문을 실행할 때 기본적으로 생성되는 유형이다.
• B-Tree는 균형 잡힌 트리의 약자로, 값 검색 시 원하는 데이터를 찾을 때까지 분기를 통해 트리의 맨 위에서부터 아래로 내려다보는 방식으로 데이터를 구성한다.
• B-Tree 인덱스는 부등호, 등호, BETWEEN과 같은 동등 및 범위 연산자를 사용하여 정렬하고 검색할 수 있는 데이터에 유용하다.
• WHERE chips LIKE 'Dorito%' 처럼 검색 문자열의 시작 부분 패턴에 와일드 카드가 없는 경우에도 LIKE와 함께 작동한다.
• PostgreSQL은 GIN, GiST를 비롯한 추가 인덱스 유형을 통합한다.
• 각각은 고유한 용도를 갖고 있는데, 후반부 장에서 다룰 예정이다.

CREATE TABLE new_york_addresses (
	longitude numeric(9,6),
	latitude numeric(9,6),
	street_number text,
	street text,
	unit text,
	postcode text,
	id integer CONSTRAINT new_york_key PRIMARY KEY
);

COPY new_york_addresses
FROM 'C:\YourDirectory\city_of_new_york.csv'
WITH (FORMAT CSV, HEADER);

• 인덱스를 확인하기 위해 우선 테이블을 생성하고 데이터를 가져와야 한다.

EXPLAIN으로 쿼리 성능 벤치마킹하기

• EXPLAIN은 PostgreSQL에서만 지원하는 것은 아니지만, 데이터베이스 별로 옵션 및 출력 포맷이 모두 다르다.
• 해당 명령을 사용하면 특정 데이터베이스 기준으로 쿼리에 대한 스캔 계획, 인덱스 사용 여부 등이 포함된다.
• ANALYZE 키워드를 추가하면 EXPLAIN이 쿼리를 수행하고 실제 실행 시간을 표시한다.

제어 실행 시간 기록하기

EXPLAIN ANALYZE SELECT * FROM new_york_addresses
WHERE street = 'BROADWAY';

EXPLAIN ANALYZE SELECT * FROM new_york_addresses
WHERE street = '52 STREET';

EXPLAIN ANALYZE SELECT * FROM new_york_addresses
WHERE street = 'ZWICKY AVENUE';

• 위 명령어는 데이터베이스에 쿼리를 실행하도록 지시하고 결과를 표시하는 대신 쿼리 프로세스 및 실행에 걸린 시간에 대한 통계를 표시한다.

Gather  (cost=1000.00..15285.68 rows=3479 width=46) (actual time=19.869..436.569 rows=3336 loops=1)
	Workers Planned: 2
	Workers Launched: 2
	->  Parallel Seq Scan on new_york_addresses  (cost=0.00..13937.78 rows=1450 width=46) (actual time=2.471..404.527 rows=1112 loops=3)
		Filter: (street = 'BROADWAY'::text)
		Rows Removed by Filter: 312346
Planning Time: 11.520 ms
Execution Time: 437.598 ms

• 첫 번째 쿼리에 대한 실행 계획으로, 다음과 같이 해석할 수 있다.

1. street = "BROADWAY"인 행을 찾기 위해 데이터베이스가 테이블의 순차적 스캔을 수행함을 나타낸다.
   • 이는 전체 테이블 스캔의 동의어이다.
   • 각 행이 검색되고, 데이터베이스는 "BROADWAY"와 일치하지 않는 행을 결과에서 제거한다.
2. 실행에 걸리는 시간을 알 수 있다.
   • 이는 하드웨어 성능 및 다양한 요인에 따라 다르게 나타날 수 있다.
   • 테스트를 위해 쿼리를 각자 여러 번 실행하고, 각각 가장 빠른 시간을 기록해야 한다.
   • 서버에서 실행 중인 다른 프로세스나 앞선 쿼리의 결과 등의 원인으로 동일한 쿼리임에도 실행 시간은 실행할 때마다 약간씩 다르다.

인덱스 추가하기

CREATE INDEX street_idx ON new_york_addresses (street);

• 위 명령어로 인덱스를 추가하고, 다시 실행 계획을 보면 다음과 같다.

Bitmap Heap Scan on new_york_addresses  (cost=43.39..6785.34 rows=3479 width=46) (actual time=0.768..3.071 rows=3336 loops=1)
  Recheck Cond: (street = 'BROADWAY'::text)
  Heap Blocks: exact=2152
  ->  Bitmap Index Scan on street_idx  (cost=0.00..42.52 rows=3479 width=0) (actual time=0.376..0.377 rows=3336 loops=1)
        Index Cond: (street = 'BROADWAY'::text)
Planning Time: 0.087 ms
Execution Time: 3.235 ms

• 두 가지 큰 변화가 있다.

1. 데이터베이스가 각 행을 순차 스캔하는 대신 street_idx에서 인덱스 스캔을 사용하고 있음을 보여준다.
2. 쿼리 속도가 현저히 빨라졌다.

• 반복적인 쿼리를 사용한 데이터에서 답을 찾거나, 수천 명의 사용자를 위한 데이터베이스 시스템을 구축할 때 시간을 절약할 수 있다.
• 여러 인덱스 유형의 성능을 테스트하는 경우와 같이 테이블에서 인덱스를 제거해야 하는 경우 다음과 같이 하면 된다.

DROP INDEX street_idx;

인덱스를 사용할 때 생각할 점

• 인덱스가 성능 향상에 큰 장점을 갖고 있다는 것을 확인했다.
• 그렇다면 테이블의 모든 열에 인덱스를 추가해야 할까?
• 그것은 아니다.
• 인덱스를 추가하면 데이터베이스의 크기가 커지고, 데이터를 작성하는 데 유지 관리 비용이 든다.
• 다음은 인덱스를 언제 사용하면 좋을지 판단하기 위한 몇 가지 기준이다.

1. 사용 가능한 인덱스 종류와 특정 데이터 타입에서 사용할 인덱스에 대해 알아보려면 사용 중인 데이터베이스 관리자에 대한 문서를 읽어 보라.
   • 예를 들어 PostgreSQL에는 B-Tree 외에도 다섯 개의 인덱스 유형이 더 존재한다.
2. 테이블 조인에 사용할 열에 인덱스를 추가하는 것이 좋다.
   • 기본 키는 PostgreSQL에서 기본적으로 인덱싱되지만, 관련 테이블의 외래 키 열은 기본적으로 인덱싱되지 않으므로 인덱스를 추가하기 좋은 대상이다.
3. 외래 키에 대한 인덱스는 연쇄 삭제(CASCADING DELETE) 동안 값비싼 순차 스캔을 피하는 데 도움이 된다.
4. WHERE 절에 자주 사용되는 열에 인덱스를 추가하면 성능이 크게 향상된다.
5. EXPLAIN ANALYZE 키워드를 통해 다양한 구성에서 성능을 테스트하자.
   • 최적화는 과정이다.
   • 데이터베이스에서 인덱스를 사용하지 않고 기본 키 또는 기타 제약 조건을 백업하지 않는다면, 인덱스를 삭제하고 데이터베이스 크기를 줄이고 삽입, 업데이트, 삭제 속도를 높일 수 있다.