PostgreSQL 설계 시 알아두면 좋을 개념들
현재 진행 중인 사이드 프로젝트에서는 한 번 사용해보고 싶다는 팀원의 제안으로 PostgreSQL을 사용하고 있다. 그동안 MySQL로 주로 작업하다보니, PostgreSQL을 사용하면서 헷갈리거나(datetime, timestamp 등) 새롭게 알게 된 개념이 있었다. 그 중 기억에 남는 몇 가지를 정리한다.
logical name vs physical name
사용자와 개발자가 커뮤니케이션할 때 사용하는 이름의 종류라고 보면 된다.
- 논리명(logical name): 유저 인터페이스에 보여지는 이름, 사용자가 이해하기 쉬운 이름 ex) 유저, 쪽지
- 물리명(physical name): 테이블 선언시 사용하는 데이터베이스의 실제 이름, 개발자가 이해하기 쉬운 이름 ex) USER, DIRECT_MESSAGES
datetime vs timestamp
PostgreSQL에서는 날짜와 시간을 저장하는데 timestamp타입을 사용한다. timestamp는 time zone 포함 여부를 설정할 수 있다.
- TIMESTAMP WITHOUT TIME ZONE: 시간대 정보를 포함하지 않고 날짜와 시간을 저장한다. 특정 시간대에서 어떤 이벤트가 발생했는지가 중요하지 않는 경우, 적합하다.
- TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ): 시간대 정보를 포함(UTC 기준)해 저장한다. 조회 할 때, 사용자의 세션 시간대에 맞게 변환되어 글로벌 시스템에 적합하다.
# TIMESTAMP WITHOUT TIME ZONE
2024–08–27 12:34:56
# TIMESTAMP WITH TIME ZONE(UTC 기준)
2024–08–27 12:34:56+00SERIAL type
PK를 지정할 때 PostgreSQL에서는 auto increment가 포함된 Sequence 타입을 지원한다. PostgreSQL 10 이전에는 SERIAL 타입을 주로 사용하여 시퀀스를 생성했다.
PostgreSQL 10에서 표준 SQL에 맞추기 위해 IDENTITY 컬럼이 도입되었다. 두 방식 모두 내부적으로 시퀀스를 생성하지만 IDENTITY 컬럼이 좀 더 명시적이고 관리가 용이하다.
- GENERATED ALWAYS AS IDENTITY: 수동으로 값을 삽입할 수 없고(PostgreSQL이 자동으로 관리) 기본적으로 auto increment가 활성화된다.
- GENERATED BY DEFAULT AS IDENTITY: 수동으로 값을 삽입할 수 있고, default 값이 존재하지 않을 때만 auto increment가 활성화된다.
JSON vs JSONB
JSON 타입은 입력 데이터의 복사본을 그대로 저장하여 처리할 때마다 parsing과정이 필요하다. 공백과 키 순서를 그대로 유지하므로 키 순서를 유지해야 하는 경우 적절하다.
JSONB 타입은 데이터를 바이너리 형식으로 저장하여 입력 시 약간 느리지만 처리할 때는 더 빠르다. GIN(Generalized Inverted Index) 인덱스를 사용하여 빠른 검색과 필터링을 제공하므로 빈번한 검색이나 수정이 필요한 경우 적합하다.
int vs bigint
- int(4바이트, -2,147,483,648 ~ 2,147,483,647): 일반적인 애플리케이션에서 주로 사용한다.
- bigint(8바이트, -9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807): 대규모 애플리케이션이나 확장성 있는 시스템, 트랜잭션 로그같은 데이터를 저장할 때 사용한다.
성능 차이는 비슷하지만, 크기가 큰 데이터를 다루는 bigint의 경우 성능 최적화가 필요할 수 있다.
