관계형 데이터만으로 부족해지는 지점
지금까지는 고객, 상품, 주문처럼 구조가 비교적 분명한 데이터를 테이블과 열로 나누어 다루었다. 이 방식은 여전히 PostgreSQL 설계의 중심이다. 하지만 현대 애플리케이션에는 구조가 자주 바뀌거나 행마다 속성이 조금씩 다른 데이터도 많다.
예를 들어 다음과 같은 값이다.
사용자별 화면 설정
이벤트 로그의 부가 속성
외부 API에서 받은 원본 응답
상품별로 다른 상세 스펙
게시글 태그 목록
문서 본문 검색용 색인
이런 데이터를 모두 정규화된 테이블로만 풀면 열과 테이블이 지나치게 많아질 수 있다. 반대로 아무 기준 없이 JSON 하나에 모두 넣으면 제약 조건, 조인, 집계, 인덱스 설계가 어려워진다.
PostgreSQL의 장점은 둘 중 하나만 선택하게 하지 않는다는 데 있다. 핵심 업무 데이터는 관계형 모델로 단단하게 관리하고, 변동성이 큰 부가 데이터는 jsonb, 배열, 전문 검색 타입, 확장 기능으로 보완할 수 있다.
중요한 기준은 하나다. 자주 조인하고 검증하고 집계해야 하는 값은 열이나 별도 테이블로 두고, 구조가 유연해야 하는 부가 정보만 확장 타입에 맡긴다.
AI 생성 이미지
json과 jsonb: 대부분은 jsonb를 선택한다
PostgreSQL에는 JSON 데이터를 저장하는 타입이 두 가지 있다.
json: 입력된 JSON 텍스트를 비교적 원본 형태에 가깝게 저장한다.jsonb: JSON을 분해한 이진 형태로 저장한다.
실무에서는 대부분 jsonb를 사용한다. jsonb는 저장 시 약간의 변환 비용이 있지만, 조회와 비교, 인덱싱에 유리하다. 공백이나 키 순서처럼 JSON 문서의 표시 형태가 중요하거나 입력 원본을 그대로 보존해야 한다면 json을 검토할 수 있다. 그러나 사용자 설정, 이벤트 속성, 상품 스펙처럼 검색과 필터링이 필요한 데이터라면 jsonb가 기본 선택이다.
사용자 설정 테이블을 만들어 보자.
CREATE TABLE user_settings (
user_id bigint PRIMARY KEY REFERENCES customers(id),
settings jsonb NOT NULL DEFAULT '{}'::jsonb,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO user_settings (user_id, settings)
VALUES
(1, '{
"theme": "dark",
"language": "ko",
"notification": {
"email": true,
"sms": false
},
"marketing_opt_in": true
}'::jsonb);JSON 내부 값을 꺼낼 때는 연산자를 사용한다.
SELECT
user_id,
settings -> 'notification' AS notification_json,
settings ->> 'theme' AS theme_text,
settings #>> '{notification,email}' AS email_notification
FROM user_settings
WHERE user_id = 1;차이를 기억해야 한다.
->는 JSON 값을 반환한다.->>는 텍스트를 반환한다.#>는 경로를 따라 JSON 값을 반환한다.#>>는 경로를 따라 텍스트를 반환한다.
숫자나 날짜처럼 타입이 필요한 값은 꺼낸 뒤 형 변환해야 한다.
SELECT user_id
FROM user_settings
WHERE (settings ->> 'marketing_score')::integer >= 80;다만 이 방식은 해당 키가 없거나 숫자로 바꿀 수 없는 문자열이 들어 있으면 오류가 날 수 있다. 중요한 값이라면 JSON 안에 숨기지 말고 별도 열로 빼는 편이 안전하다.
jsonb 검색과 GIN 인덱스
jsonb의 강점은 포함 관계 검색이다. 예를 들어 마케팅 수신에 동의한 사용자를 찾으려면 다음처럼 쓸 수 있다.
SELECT user_id
FROM user_settings
WHERE settings @> '{"marketing_opt_in": true}'::jsonb;이벤트 로그에서도 같은 패턴을 쓸 수 있다.
CREATE TABLE app_events (
id bigserial PRIMARY KEY,
occurred_at timestamptz NOT NULL DEFAULT now(),
event_type text NOT NULL,
user_id bigint,
properties jsonb NOT NULL DEFAULT '{}'::jsonb
);
INSERT INTO app_events (event_type, user_id, properties)
VALUES
('product_viewed', 1, '{"product_id": 100, "device": "mobile", "campaign": "spring"}'),
('payment_failed', 2, '{"order_id": 9001, "reason": "card_declined"}');
SELECT id, occurred_at, user_id
FROM app_events
WHERE event_type = 'product_viewed'
AND properties @> '{"device": "mobile"}'::jsonb;데이터가 적을 때는 인덱스가 없어도 문제가 드러나지 않는다. 하지만 이벤트 로그처럼 계속 쌓이는 테이블에서는 jsonb 조건이 순차 스캔으로 이어질 수 있다. 이때 자주 쓰는 방식이 GIN 인덱스다. GIN은 하나의 열 안에 들어 있는 여러 키, 값, 토큰을 찾아가기 위한 인덱스 방식이다. jsonb, 배열, 전문 검색에서 공통으로 자주 등장한다.1
CREATE INDEX idx_app_events_properties_gin
ON app_events
USING gin (properties);이 인덱스는 @> 같은 포함 검색이나 키 존재 여부 검색에 도움이 된다.
SELECT id
FROM app_events
WHERE properties ? 'campaign';
SELECT id
FROM app_events
WHERE properties @> '{"reason": "card_declined"}'::jsonb;만약 @> 포함 검색만 집중적으로 사용한다면 jsonb_path_ops 연산자 클래스를 검토할 수 있다.
CREATE INDEX idx_app_events_properties_path_gin
ON app_events
USING gin (properties jsonb_path_ops);이 방식은 일반 GIN 인덱스보다 작고 포함 검색에 유리할 수 있지만, 모든 jsonb 연산자를 지원하지 않는다. 키 존재 여부 검색까지 자주 쓴다면 기본 GIN 인덱스가 더 적합할 수 있다.
반대로 JSON 내부의 특정 값을 정확히 비교하는 쿼리가 많다면 표현식 인덱스가 더 낫다.
CREATE INDEX idx_app_events_order_id
ON app_events ((properties ->> 'order_id'));
SELECT id
FROM app_events
WHERE properties ->> 'order_id' = '9001';판단 기준은 실행 계획이다. EXPLAIN (ANALYZE, BUFFERS)로 실제 쿼리가 어떤 인덱스를 쓰는지 확인해야 한다. JSONB 인덱스를 만들었다는 사실만으로 모든 JSON 조건이 빨라지는 것은 아니다.
JSONB에 넣을 값과 빼야 할 값
JSONB는 설계를 건너뛰기 위한 도구가 아니다. 다음 값은 JSONB에 넣어도 괜찮은 후보가 된다.
사용자별 UI 설정처럼 행마다 키가 조금씩 다른 값
이벤트 로그의 부가 속성
외부 API 응답 원본
상품군마다 구조가 다른 보조 스펙
반대로 다음 값은 별도 열이나 테이블로 두는 편이 좋다.
주문 금액, 주문 상태, 결제 상태처럼 업무 규칙의 중심이 되는 값
외래 키로 다른 테이블과 연결해야 하는 값
자주 GROUP BY, ORDER BY, JOIN에 사용되는 값
NOT NULL, UNIQUE, CHECK 같은 제약 조건이 필요한 값
예를 들어 orders 테이블의 status를 JSONB 안에 넣으면 결제 완료 주문만 조회하거나 상태별 주문 수를 집계할 때 매번 JSON 연산과 형 변환을 해야 한다. 또한 잘못된 상태값을 데이터베이스 제약 조건으로 막기도 어렵다.
JSONB는 변화가 많은 주변 정보를 담는 데 쓰고, 업무의 중심축은 관계형 열로 남겨야 한다.
배열 타입: 짧고 단순한 목록에만 사용한다
PostgreSQL은 배열 타입도 지원한다. 게시글 태그처럼 하나의 행에 짧은 값 목록을 붙이고 싶을 때 사용할 수 있다.
CREATE TABLE articles (
id bigserial PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
tags text[] NOT NULL DEFAULT '{}',
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO articles (title, body, tags)
VALUES
('PostgreSQL 인덱스 입문', 'B-tree와 GIN 인덱스를 설명합니다.', ARRAY['postgresql', 'index']),
('JSONB 사용법', '반정형 데이터를 다루는 방법입니다.', ARRAY['postgresql', 'jsonb']);배열 검색에는 다음 연산자가 자주 쓰인다.
-- postgresql 태그를 모두 포함하는 글
SELECT id, title
FROM articles
WHERE tags @> ARRAY['postgresql'];
-- jsonb 또는 search 태그 중 하나라도 겹치는 글
SELECT id, title
FROM articles
WHERE tags && ARRAY['jsonb', 'search'];
-- 배열 원소 중 하나와 비교
SELECT id, title
FROM articles
WHERE 'index' = ANY(tags);배열도 GIN 인덱스를 사용할 수 있다.
CREATE INDEX idx_articles_tags_gin
ON articles
USING gin (tags);이 인덱스는 주로 @>, && 같은 배열 연산자로 검색할 때 효과를 기대할 수 있다. = ANY(tags)는 읽기 쉬운 표현이지만, GIN 인덱스를 활용하는 조건으로는 tags @> ARRAY['index']처럼 배열 연산자로 쓰는 편이 더 분명하다. 결국 여기서도 실제 사용 여부는 실행 계획으로 확인해야 한다.
배열은 다음 조건을 만족할 때 적합하다.
목록 크기가 작고 크게 늘어나지 않는다.
원소 자체에 추가 속성이 없다.
원소별 생성일, 작성자, 정렬 순서 같은 정보를 관리하지 않는다.
목록을 다른 테이블과 외래 키로 연결할 필요가 없다.
태그가 단순 문자열이고 검색 보조 용도라면 배열이 편하다. 하지만 태그 이름을 별도 관리해야 하거나, 태그별 게시글 수를 자주 집계하거나, 태그에 표시 색상·설명·권한이 붙는다면 배열보다 연결 테이블이 낫다.
CREATE TABLE tags (
id bigserial PRIMARY KEY,
name text NOT NULL UNIQUE
);
CREATE TABLE article_tags (
article_id bigint NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
tag_id bigint NOT NULL REFERENCES tags(id),
PRIMARY KEY (article_id, tag_id)
);배열은 정규화를 대체하지 않는다. 짧은 목록을 한 행 안에서 간단히 다루고 싶을 때 선택하는 도구다.
LIKE 검색과 전문 검색의 차이
게시판이나 고객센터 문서에서 제목과 본문을 검색해야 한다고 해 보자. 가장 먼저 떠올리는 방법은 LIKE 또는 ILIKE다.
SELECT id, title
FROM articles
WHERE title ILIKE '%postgresql%'
OR body ILIKE '%postgresql%';이 방식은 단순 부분 문자열 검색에는 충분하다. 하지만 검색어가 앞뒤 %로 감싸이면 일반 B-tree 인덱스를 활용하기 어렵고, 결과의 관련도를 계산하지 못한다. 단어를 분해하거나 불필요한 단어를 제외하거나 제목의 일치를 본문보다 높게 평가하는 것도 어렵다.
PostgreSQL의 전문 검색은 텍스트를 검색용 토큰으로 바꾼 tsvector와 사용자의 검색 조건을 표현하는 tsquery를 비교한다. to_tsvector는 문서를 검색 가능한 형태로 바꾸고, to_tsquery, plainto_tsquery, websearch_to_tsquery는 검색어를 쿼리 형태로 바꾼다. 두 값은 @@ 연산자로 매칭한다.2
간단한 검색 열을 만들어 보자.
ALTER TABLE articles
ADD COLUMN search_document tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
setweight(to_tsvector('simple', coalesce(body, '')), 'B')
) STORED;
CREATE INDEX idx_articles_search_document_gin
ON articles
USING gin (search_document);검색은 다음처럼 실행한다.
SELECT
id,
title,
ts_rank(search_document, query) AS rank
FROM articles,
websearch_to_tsquery('simple', 'postgresql 인덱스') AS query
WHERE search_document @@ query
ORDER BY rank DESC, id DESC;여기서 setweight는 제목과 본문의 중요도를 다르게 주기 위해 사용했다. 제목에서 검색어가 발견된 문서를 본문에서만 발견된 문서보다 앞에 놓고 싶을 때 유용하다.
사용자 입력을 직접 받을 때는 to_tsquery보다 plainto_tsquery나 websearch_to_tsquery가 다루기 쉽다. to_tsquery는 postgresql & index처럼 연산자를 포함한 형식을 기대한다. 반면 websearch_to_tsquery는 일반 검색창에 가까운 입력을 받아 처리한다.
SELECT websearch_to_tsquery('simple', '"postgresql index" OR jsonb');주의할 점도 있다. PostgreSQL 내장 전문 검색은 언어별 설정에 따라 동작한다. 영어 문서에서는 어간 처리와 불용어 처리가 잘 맞는 경우가 많지만, 한국어 검색은 띄어쓰기와 형태소 분석 문제가 있어 요구사항에 따라 별도 확장이나 외부 검색 엔진을 검토해야 한다. 상품 코드, 기술 문서, 영문 로그, 제목 중심 검색처럼 요구가 단순한 경우에는 PostgreSQL 내장 전문 검색만으로도 충분한 출발점이 된다.
확장 기능은 PostgreSQL을 넓히는 공식 방식이다
PostgreSQL의 확장 기능은 데이터 타입, 함수, 인덱스 연산자, 통계 수집 기능 등을 데이터베이스에 추가하는 방식이다. 설치 가능한 확장은 다음처럼 확인할 수 있다.
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name IN ('pgcrypto', 'uuid-ossp', 'pg_stat_statements');현재 데이터베이스에 설치된 확장은 psql에서 다음 명령으로 확인할 수 있다.
dx확장은 보통 데이터베이스 단위로 설치한다.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";uuid-ossp처럼 이름에 하이픈이 들어간 확장은 큰따옴표로 감싸야 한다.
pgcrypto, uuid-ossp, pg_stat_statements
pgcrypto는 해시, HMAC, 비밀번호 해싱, 암호화 관련 함수를 제공한다. 대표적으로 해시 계산과 비밀번호 검증에 사용할 수 있고, PostgreSQL에서 무작위 UUID를 만들 때 자주 쓰는 gen_random_uuid()도 함께 알아 두면 좋다.3
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT gen_random_uuid();
SELECT encode(digest('hello', 'sha256'), 'hex');
SELECT crypt('new-password', gen_salt('bf')) AS password_hash;비밀번호 검증은 저장된 해시와 입력 비밀번호를 비교하는 방식이다.
SELECT password_hash = crypt('entered-password', password_hash) AS matched
FROM users
WHERE email = '[email protected]';다만 비밀번호 처리와 암호화 키 관리는 애플리케이션, 전송 구간 암호화, 접근 권한과 함께 설계해야 한다. 데이터베이스 함수 하나만으로 보안 설계가 완성되지는 않는다.
uuid-ossp는 표준 UUID 생성 알고리즘을 제공하는 확장이다. 일반적인 무작위 UUID가 필요하다면 PostgreSQL의 gen_random_uuid()로 충분한 경우가 많다. 반면 이름 기반 UUID v3/v5나 특수한 UUID 상수가 필요하다면 uuid-ossp를 검토할 수 있다. UUID v1은 시간과 장비 정보가 드러날 수 있으므로 공개 식별자로 쓸 때는 주의해야 한다.4
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();
SELECT uuid_generate_v5(uuid_ns_url(), 'https://example.com/products/100');pg_stat_statements는 실행된 SQL의 호출 횟수와 실행 시간 통계를 모아 주는 확장이다. 실행 계획으로 개별 쿼리의 병목을 읽는 방법을 익혔다면, 운영 환경에서는 먼저 어떤 쿼리가 시간을 많이 쓰는지 찾아야 한다. 이때 pg_stat_statements가 출발점이 된다.5
이 확장은 단순히 CREATE EXTENSION만으로 끝나지 않는다. 보통 서버 설정에 사전 로드가 필요하다.
shared_preload_libraries = 'pg_stat_statements'설정을 바꾼 뒤 PostgreSQL을 재시작하고 데이터베이스에서 확장을 설치한다.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;느린 쿼리 후보는 다음처럼 찾을 수 있다.
SELECT
calls,
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows,
left(query, 100) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;확장 기능은 편리하지만 설치 권한, 운영 정책, 백업·복구 환경을 함께 확인해야 한다. 개발 환경에서 만든 확장이 운영 데이터베이스에 없으면 마이그레이션이 실패할 수 있다. 그래서 확장 설치는 애플리케이션 배포 절차에 포함하고, CREATE EXTENSION IF NOT EXISTS를 명시적으로 관리하는 것이 좋다.
확장 타입을 사용할 때의 판단 기준
jsonb, 배열, 전문 검색, 확장 기능은 PostgreSQL을 더 넓게 사용할 수 있게 해 준다. 그러나 모든 문제를 한 열에 몰아넣는 방향으로 쓰면 오히려 유지보수가 어려워진다.
다음 기준으로 선택하면 실수가 줄어든다.
업무의 중심 데이터는 일반 열과 제약 조건으로 관리한다.
구조가 자주 바뀌는 부가 정보는
jsonb를 사용한다.짧고 단순한 값 목록은 배열을 검토한다.
원소에 속성이나 관계가 생기면 별도 테이블로 분리한다.
단순 부분 검색은
LIKE로 시작할 수 있지만, 문서 검색과 순위가 필요하면 전문 검색을 사용한다.GIN 인덱스는 내부 요소를 검색하는 데 유용하지만, 실제 효과는 실행 계획으로 확인한다.
확장은 데이터베이스 기능을 넓히는 공식 방법이지만, 설치와 운영 절차까지 함께 설계한다.
PostgreSQL은 관계형 데이터베이스이면서도 반정형 데이터와 검색 요구를 함께 처리할 수 있다. 핵심은 무엇을 테이블 구조로 고정하고, 무엇을 유연한 타입에 맡길지 구분하는 일이다.
참고
1PostgreSQL GIN Indexes: JSONB, Arrays & Full-Text Search
2PostgreSQL Documentation: Text Search Functions and Operators
3PostgreSQL Documentation: pgcrypto
이 노트는 보도·비평·교육·연구 목적으로 작성되었습니다. 저작권 문의가 있으시면 에서 알려주세요.