배열 인덱싱: GIN-과 BTREE-인덱스 만들기

상상해봐, 너한테 수백만 개의 레코드가 있는 테이블이 있고, 그 중 한 컬럼이 배열을 저장하고 있어. 예를 들어, products라는 테이블이 있고, 각 상품이 여러 카테고리에 속할 수 있다고 해보자:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    categories TEXT[] -- 상품 카테고리 저장용 문자열 배열
);

예를 들어, electronics 카테고리에 속한 모든 상품을 찾고 싶다고 해보자. 그냥 @> 연산자를 써서 찾으면 테이블 전체를 다 스캔하게 될 수도 있어:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

전체 스캔(Seq Scan)은 진짜 느려. 특히 테이블이 엄청 크면 더더욱. 이럴 때 인덱스가 등장해서 훨씬 빠른 검색으로 바꿔주는 거지.

배열용 인덱스 타입

PostgreSQL에서는 배열에 쓸 수 있는 대표적인 인덱스 타입이 두 개 있어:

1. GIN (Generalized Inverted Index) — 배열에서 요소를 빠르게 찾거나 교집합을 검사할 때 완전 좋아.
2. BTREE (Binary Tree) — 배열을 정확하게 비교할 때 등등 다른 연산에 적합해.

각각 좀 더 자세히 볼게.

1. GIN 인덱스: 빛의 속도

GIN (Generalized Inverted Index)은 이런 연산자랑 찰떡이야:

• @> (배열이 요소나 다른 배열을 포함하는지),
• <@ (배열이 다른 배열에 포함되는지),
• && (배열끼리 교집합이 있는지).

우리 categories 컬럼에 GIN 인덱스를 만드는 방법은 이래:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

인덱스 만들고 나면 쿼리 속도가 확 빨라질 거야. 예를 들어, 이런 쿼리:

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

이제 GIN 인덱스를 쓸 수 있어.

재밌는 사실: GIN 인덱스는 인버티드 리스트(역방향 목록) 원리로 동작해 — 어떤 요소(예: 문자열)가 어떤 레코드에 있는지 저장하는 거지. 책에서 주제별로 페이지 번호 찾는 색인 같은 느낌이야. 편하지?

2. BTREE 인덱스: 순서가 중요할 때

BTREE (Binary Tree)는 대부분의 데이터베이스에서 표준 인덱스야. 배열을 정확하게 비교해야 할 때, 예를 들면:

• 배열이 같은지 =,
• 배열 요소 순서대로 비교(>, <).

배열에 BTREE 인덱스 만드는 방법은 이래:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

BTREE 인덱스를 쓸 수 있는 쿼리 예시:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

근데 BTREE 인덱스는 @>나 <@ 같은 연산자에는 안 맞아. 그런 건 GIN이 더 좋아.

인덱스 활용 예시

이제 이론이랑 실전 합쳐서 몇 가지 예시를 볼게.

1. 배열 교집합 검색

예를 들어, electronics랑 smartphones 카테고리 둘 다 관련된 상품을 찾고 싶으면 && (배열 교집합) 연산자를 써:

SELECT *
FROM products
WHERE categories && ARRAY['electronics', 'smartphones'];

이럴 때 아까 만든 GIN 인덱스가 딱이야:

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

이 인덱스 덕분에 쿼리가 인버티드 리스트를 써서 훨씬 빨라져.

2. 배열이 같은지 비교

만약 딱 electronics랑 gadgets (이 순서로) 카테고리에만 속한 상품을 찾고 싶으면 BTREE 인덱스가 더 좋아:

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

이렇게 인덱스 만들어줘:

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

인덱스 성능

인덱스는 쿼리 속도를 올려주지만, 단점도 있어. 예를 들면:

• 인덱스 생성은 시간도 들고 리소스도 먹어. 테이블이 엄청 크면 인덱스 만드는 데 오래 걸릴 수도 있어.
• 테이블 업데이트. 새로운 행을 넣거나 기존 데이터를 바꿀 때마다 인덱스도 같이 갱신돼. 그래서 INSERT나 UPDATE가 느려질 수 있어.

그래도 대부분의 경우, 쿼리 속도 향상이 이런 단점보다 훨씬 이득이야.

뭐 쓸까: GIN vs BTREE?

어떤 인덱스를 써야 할지 헷갈리면, 이 표 참고해봐: