PostgreSQL hstore로 문자열 속성 검색하기

소개

hstore는 PostgreSQL에서 키-값 형태의 반정형 데이터를 손쉽게 다루도록 설계된 확장이다. 컬럼 하나에 여러 속성을 저장하면서도 개별 키를 빠르게 조회할 수 있어 메타데이터나 속성 집합 저장에 유용하다. 이 글에서는 초보자도 이해할 수 있도록 hstore의 기본 사용법과 문자열 속성 검색 방법, 인덱스 전략을 정리한다.

hstore 기본 개념

hstore란 무엇인가

hstore는 문자열 키와 문자열 값을 저장하는 자료형이다. 각 로우는 여러 키-값 쌍을 포함할 수 있으며, SQL 연산자를 통해 키 존재 여부나 특정 값 조회가 가능하다. 스키마를 자주 변경하지 않는 상황에서 유연한 속성 저장소 역할을 한다.

장단점 요약

장점: 스키마 유연성, 간단한 키/값 조회, 적은 테이블 조인
단점: 정규화된 컬럼에 비해 타입 제약과 무결성 검사 제한, 복잡한 쿼리에서 성능 저하 가능

설치와 테이블 생성

확장 설치

먼저 hstore 확장을 설치해야 한다. 슈퍼유저 권한으로 한번만 실행하면 된다.

CREATE EXTENSION IF NOT EXISTS hstore;

테이블 예시

간단한 예로 products 테이블을 만들고 attributes 열을 hstore로 선언한다.

CREATE TABLE products (
  id serial PRIMARY KEY,
  name text,
  attributes hstore
);

INSERT INTO products (name, attributes) VALUES
  ('T-shirt', 'color=>"red",size=>"M"'),
  ('Mug', 'material=>"ceramic",capacity=>"300ml"');

키와 값 조회 방법

키로 값 조회

특정 키의 값을 가져오려면 -> 연산자를 사용한다. 값은 텍스트로 반환된다.

SELECT name, attributes -> 'color' AS color
FROM products
WHERE attributes -> 'color' IS NOT NULL;

키 존재 여부 확인

키 존재 여부는 ? 연산자로 확인한다. 여러 키를 한 번에 검사하려면 ?| 또는 ?& 를 사용한다.

-- 특정 키가 있는 로우
SELECT * FROM products WHERE attributes ? 'size';

-- 키 중 하나라도 있으면
SELECT * FROM products WHERE attributes ?| array['size','capacity'];

-- 모든 키가 있어야 함
SELECT * FROM products WHERE attributes ?& array['color','size'];

키-값 포함 관계

한쪽이 다른쪽을 포함하는지 검사하려면 포함 연산자(@>)를 사용한다. 오른쪽은 hstore 리터럴이다.

-- color=>"red" 속성을 포함하는 로우
SELECT * FROM products WHERE attributes @> 'color=>"red"';

문자열 패턴 검색

값에 대한 LIKE/ILIKE 검색

값 자체에 패턴 매칭을 하려면 값 추출 후 LIKE 또는 ILIKE를 사용한다. 대소문자를 무시하려면 ILIKE를 쓴다.

-- 값에 'cer'를 포함하는 로우
SELECT * FROM products
WHERE attributes -> 'material' ILIKE '%cer%';

모든 값에서 패턴 찾기

모든 키의 값에 대해 패턴을 찾을 때는 hstore_to_array 또는 hstore(hstore_each?) 같은 함수를 조합해 사용할 수 있다. 단순한 방법으로는 hstore를 텍스트로 캐스트해 검색하는 방법이 있다.

-- hstore 전체를 텍스트로 변환해 패턴 검색
SELECT * FROM products
WHERE attributes::text ILIKE '%red%';

인덱스 전략과 성능

기본 GIN 인덱스

키 존재 여부나 포함 연산자(@>) 같은 검색에는 GIN 인덱스가 효과적이다. 기본 연산자에 적합한 hstore_ops를 사용한다.

CREATE INDEX idx_products_attributes_gin ON products USING GIN (attributes);
-- 또는 명시적으로
CREATE INDEX idx_products_attributes_gin_ops ON products USING GIN (attributes hstore_ops);

문자열 패턴 검색을 위한 인덱스

LIKE/ILIKE와 같은 패턴 검색은 기본 hstore GIN 인덱스로는 최적화되지 않는다. 이 경우 트라이그램 인덱스를 활용한다. hstore를 텍스트로 캐스트한 표현식에 gin_trgm_ops를 적용하면 패턴 검색 성능을 개선할 수 있다.

-- trigram 인덱스 생성 (pg_trgm 확장 필요)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_attributes_trgm ON products USING GIN ((attributes::text) gin_trgm_ops);

-- 이후 ILIKE 쿼리가 인덱스를 활용할 가능성 증가
SELECT * FROM products WHERE attributes::text ILIKE '%cer%';

성능 고려사항

키 존재성 체크는 기본 GIN 인덱스로 빠름.
문자열 패턴 검색은 트라이그램 인덱스를 권장하지만 인덱스 크기가 커질 수 있음.
자주 조회되는 키는 별도 정규화 컬럼으로 분리하는 편이 더 효율적일 수 있음.

실무 적용 팁

hstore postgres 사용법을 도입할 때는 데이터 특성을 먼저 파악한다. 읽기 중심의 키 존재 체크가 대부분이라면 hstore가 유리하다. 반면 특정 값으로 자주 정렬하거나 집계한다면 정규화가 더 적합하다. 키값 검색 hstore postgres 시나리오별로 GIN 인덱스와 트라이그램 인덱스를 조합하면 성능과 유연성의 균형을 맞출 수 있다.

마무리

hstore는 간단한 키-값 저장과 빠른 키 존재 검색에 강점을 가진 도구다. hstore 인덱스 postgres 측면에서 GIN 인덱스는 기본 선택이며, 문자열 패턴 검색은 트라이그램 인덱스를 고려한다. 적용 전 쿼리 패턴과 데이터 분포를 검토하면 효율적인 설계가 가능하다.

목차

소개