[PostgreSQL] 스키마 안 바꾸고 유연한 속성 저장 — hstore vs JSONB vs intarray 태그

설치·접속: PostgreSQL 설치와 접속

부제: 상품마다 부가 스펙이 제각각이고 태그도 붙는데, 속성 하나 늘 때마다 ALTER TABLE 하기는 싫을 때

"유연한 속성"이라고 무조건 JSONB부터 꺼내는 경우가 많다. 실제로는 데이터 모양에 따라 셋이 갈린다. 평면 key-value는 hstore, 태그 집합은 intarray, 중첩 구조는 JSONB. 상품 테이블 하나에 셋을 다 얹어 놓고 언제 무엇이 이기는지 본다.

CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS intarray;

CREATE TABLE prod (
    id     serial PRIMARY KEY,
    name   text,
    specs  hstore,   -- 평면 key-value (색상/램/무게…)
    tags   int[],    -- 태그 ID 집합
    detail jsonb     -- 중첩 구조 (cpu.cores, ports[]…)
);
INSERT INTO prod(name, specs, tags, detail) VALUES
 ('Laptop A', 'color=>silver, ram=>16GB, weight=>1.2kg', ARRAY[1,2,3],
   '{"cpu":{"brand":"Intel","cores":8},"ports":["USB-C","HDMI"]}'),
 ('Laptop B', 'color=>black,  ram=>32GB',                 ARRAY[2,3,5],
   '{"cpu":{"brand":"AMD","cores":16},"ports":["USB-C"]}'),
 ('Phone C',  'color=>black,  storage=>256GB, waterproof=>yes', ARRAY[3,4],
   '{"cpu":{"brand":"Apple","cores":6},"ports":["Lightning"]}'),
 ('Tablet D', 'color=>silver, storage=>128GB',            ARRAY[1,4,5],
   '{"cpu":{"brand":"Apple","cores":8},"ports":["USB-C"]}');

CREATE INDEX prod_specs_gin  ON prod USING GIN (specs);
CREATE INDEX prod_tags_gin   ON prod USING GIN (tags gin__int_ops);
CREATE INDEX prod_detail_gin ON prod USING GIN (detail jsonb_path_ops);

평면 key-value → hstore

상품마다 다른 부가 스펙(색상·램·방수 여부…)처럼 한 겹짜리 key-value에는 hstore가 가장 가볍다. 포함(@>)·키 존재(?, ?&, ?|) 연산자를 GIN 인덱스가 받는다.

-- specs 에 color=black 을 포함하는 상품 (없는 키는 NULL로)
SELECT name, specs->'ram' AS ram FROM prod WHERE specs @> 'color=>black';

   name   | ram  
----------+------
 Laptop B | 32GB
 Phone C  | 
(2 rows)

Phone C는 검정색이지만 ram 키 자체가 없어 ram 컬럼이 비어 나온다. hstore의 핵심은 "이 속성을 아예 가진 상품"을 키 존재 연산자로 거를 수 있다는 것이다.

-- waterproof 키를 가진 상품만
SELECT name FROM prod WHERE specs ? 'waterproof';
-- storage 와 color 키를 둘 다 가진 상품
SELECT name, specs->'storage' AS storage FROM prod WHERE specs ?& ARRAY['storage','color'];

  name   
---------
 Phone C
(1 row)

   name   | storage 
----------+---------
 Phone C  | 256GB
 Tablet D | 128GB
(2 rows)

태그 집합 → intarray

태그는 "정수 ID의 집합"이다. 기본 int[]도 되지만 intarray 확장은 포함(@>)·겹침(&&)에 더해 불리언 질의(query_int)와 전용 GIN op class(gin__int_ops)를 준다. AND/OR가 섞인 태그 필터를 인덱스로 처리하는 게 강점이다.

-- 태그 2 AND 3 을 모두 가진 상품
SELECT name, tags FROM prod WHERE tags @> ARRAY[2,3];
-- 태그 4 또는 5 중 하나라도 겹치는 상품
SELECT name, tags FROM prod WHERE tags && ARRAY[4,5];
-- 불리언 질의: 1 AND (4 OR 2)
SELECT name, tags FROM prod WHERE tags @@ '1 & (4 | 2)';

-- @> ARRAY[2,3]
   name   |  tags   
----------+---------
 Laptop A | {1,2,3}
 Laptop B | {2,3,5}

-- && ARRAY[4,5]
   name   |  tags   
----------+---------
 Laptop B | {2,3,5}
 Phone C  | {3,4}
 Tablet D | {1,4,5}

-- @@ '1 & (4 | 2)'
   name   |  tags   
----------+---------
 Laptop A | {1,2,3}
 Tablet D | {1,4,5}

1 & (4 | 2)는 "태그 1을 가지면서 4 또는 2 중 하나를 가진" 상품이다. 이걸 조인 테이블로 풀면 서브쿼리와 GROUP BY HAVING 조합이 되는데, intarray는 한 줄이다. 데이터가 커지면 GIN 인덱스가 그대로 먹는다 — 20만 행에 태그를 심고 @> ARRAY[7,11]을 돌리면:

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF)
SELECT count(*) FROM prod_big WHERE tags @> ARRAY[7,11];

 Aggregate (actual time=1.643..1.643 rows=1 loops=1)
   ->  Bitmap Heap Scan on prod_big (actual rows=1006 loops=1)
         Recheck Cond: (tags @> '{7,11}'::integer[])
         Heap Blocks: exact=826
         ->  Bitmap Index Scan on prod_big_gin (actual rows=1006 loops=1)
               Index Cond: (tags @> '{7,11}'::integer[])

20만 행에서 1006행을 1.6ms에 뽑는다. 인덱스 없이 겹침 조건을 걸면 전건 스캔이다.

중첩 구조 → JSONB

cpu.brand, ports[]처럼 계층이 있는 데이터는 hstore로는 표현이 안 된다. 여기서 JSONB가 필요하다. 경로 접근(#>, #>>)과 포함(@>)을 쓴다.

-- cpu.cores 가 8 이상인 상품
SELECT name, detail #> '{cpu,brand}' AS brand, detail #> '{cpu,cores}' AS cores
  FROM prod WHERE (detail #>> '{cpu,cores}')::int >= 8;
-- ports 배열에 USB-C 를 포함하는 상품
SELECT name FROM prod WHERE detail @> '{"ports":["USB-C"]}';

   name   |  brand  | cores 
----------+---------+-------
 Laptop A | "Intel" | 8
 Laptop B | "AMD"   | 16
 Tablet D | "Apple" | 8

   name   
----------
 Laptop A
 Laptop B
 Tablet D

결론: 언제 무엇을 쓰나

데이터 모양	선택	이유
한 겹 key-value (색상·용량…)	hstore	가장 단순, 인덱스 가볍고 키 존재 질의(?)가 명확
정수 ID 태그 집합	intarray	@>/&&/불리언 질의 + GIN, 조인 테이블 없이 태그 필터
중첩·배열·이종 타입	JSONB	경로 접근·중첩·jsonpath. 나머지가 표현 못 하는 구조

hstore와 JSONB는 서로 변환된다. 평면으로 시작했다가 중첩이 필요해지면 hstore_to_jsonb로 넘기면 된다.

SELECT name, hstore_to_jsonb(specs) AS specs_json FROM prod WHERE id = 1;

   name   |                      specs_json                       
----------+-------------------------------------------------------
 Laptop A | {"ram": "16GB", "color": "silver", "weight": "1.2kg"}

과하게 JSONB로 다 담기보다, 모양에 맞는 도구를 고르면 인덱스도 질의도 단순해진다.

이렇게도 쓴다

hstore 키/값을 배열로 뽑거나 병합한다. 키 목록·값 목록을 한 번에, ||로 속성 추가. (조합: akeys/avals + 병합)

SELECT akeys(specs) AS keys, avals(specs) AS vals FROM prod WHERE id = 1;
-- keys: {ram,color,weight}   vals: {16GB,silver,1.2kg}
SELECT name, specs || 'discount=>10%'::hstore AS updated FROM prod WHERE id = 2;  -- 키 추가/병합

 

intarray로 태그 집합 연산을 한다. 정렬·중복제거·차집합을 배열에서 바로. (조합: intarray 함수)

SELECT sort(uniq(tags || ARRAY[3,3,9])) FROM prod WHERE id = 1;  -- 병합 후 중복제거+정렬
SELECT tags - ARRAY[2] AS without_2 FROM prod WHERE id = 1;      -- 특정 태그 제거

 

JSONB 내부를 부등호·범위로 필터한다. @>로는 등호만 되지만 jsonpath는 범위가 된다. (조합: jsonpath)

SELECT name FROM prod WHERE detail @? '$.cpu.cores ? (@ >= 8)';

 

hstore를 키·값 행으로 펼쳐 집계한다. "어떤 속성이 몇 개 상품에 쓰였나". (조합: each + GROUP BY)

SELECT key, count(*) FROM prod, each(specs) GROUP BY key ORDER BY count(*) DESC;

 

태그를 조인 테이블로 빼야 할 때. 태그 자체에 이름·색상 같은 속성이 붙거나, 태그별 집계가 잦으면 배열은 안티패턴이 된다. (경계: 정규화로 전환)

-- tag(id, name) + product_tag(product_id, tag_id) 로 분리하는 신호:
--   태그에 메타데이터가 붙는다 / 태그별 통계가 핵심 쿼리다 / 태그 이름을 자주 바꾼다