SaaS 앱을 위한 PostgreSQL 인덱스: 필터, 검색, JSONB, 배열 같은 실제 쿼리 형태에 따라 B-tree, GIN, GiST 중 무엇을 선택할지 안내합니다.

인덱스는 PostgreSQL이 행을 찾는 방식을 바꿉니다. 인덱스가 없으면 데이터베이스는 종종 테이블의 대부분을 읽어야 하고(순차 스캔), 대부분을 버립니다. 적절한 인덱스가 있으면 필요한 행으로 곧바로 이동(인덱스 조회)해서 필요한 것만 가져올 수 있습니다.
SaaS에서는 이 차이를 일찍 느끼게 됩니다. 일상적인 화면이 쿼리를 많이 발생시키기 때문입니다. 한 번의 클릭이 목록 페이지, 전체 개수, 대시보드 카드 몇 개, 검색 상자를 모두 읽게 할 수 있습니다. 테이블이 수천 건에서 수백만 건으로 커지면 이전에 즉각적이던 같은 쿼리가 느려지기 시작합니다.
전형적인 예는 상태와 날짜로 필터링하고 최신순으로 정렬하며 페이지네이션하는 주문 페이지입니다. PostgreSQL이 지난 30일간의 결제된 주문을 찾으려고 전체 orders 테이블을 스캔해야 한다면, 각 페이지 로드마다 불필요한 작업이 발생합니다. 좋은 인덱스는 필요한 데이터 조각으로 빠르게 뛰어들게 합니다.
인덱스는 무료가 아닙니다. 특정 쿼리에 대해 빠른 읽기를 제공하는 대신 쓰기(INSERT/UPDATE/DELETE)가 느려지고 저장공간을 더 쓰며 캐시 부담도 증가합니다. 그래서 인덱스 종류에서 출발할 게 아니라 실제 쿼리 패턴에서 출발해야 합니다.
간단한 규칙: 인덱스는 그것이 가속할 구체적이고 빈번한 쿼리를 가리킬 수 있을 때만 추가하세요. Koder.ai 같은 챗 기반 빌더로 화면을 만들면 목록 페이지와 대시보드 뒤에 어떤 SQL이 실행되는지 캡처해서 인덱스 위시리스트로 쓰기 좋습니다.
대부분의 인덱스 혼란은 기능(JSON, 검색, 배열)으로 생각하는 대신 쿼리 형태(WHERE 절이 무슨 일을 하는가, 결과를 어떻게 정렬하길 바라는가)를 기준으로 생각하면 사라집니다.
일반적인 비교 연산과 정렬이 중요할 때 B-tree를 사용하세요. 동등(equality), 범위(range), 조인에 가장 잘 맞는 작업 마차(workhorse)입니다.
예시 패턴: tenant_id = ?, status = 'active', created_at >= ?, users.id = orders.user_id로 조인하거나 ORDER BY created_at DESC로 “최신순”을 표시할 때.
GIN(Generalized Inverted Index)은 한 컬럼에 많은 멤버가 들어 있고 “이것을 포함하나?”를 묻는 경우에 적합합니다. JSONB 키, 배열 요소, 전체 텍스트 벡터에서 흔히 사용됩니다.
예시 패턴: JSONB에서 metadata @> '{"plan":"pro"}', tags @> ARRAY['urgent'], to_tsvector(body) @@ plainto_tsquery('reset password') 같은 경우.
GiST(Generalized Search Tree)는 값이 범위나 모양처럼 행동할 때, 즉 거리나 겹침(overlap)을 묻는 질문에 맞습니다. 범위 타입, 지오메트릭 데이터, “가장 가까운” 스타일 검색에 자주 쓰입니다.
실용적인 선택법:
인덱스는 읽기를 빠르게 하지만 쓰기 시간과 디스크 비용을 발생시킵니다. SaaS에서는 이벤트, 세션, 활동 로그처럼 핫한 테이블에서 이 트레이드오프가 가장 중요합니다.
대부분의 SaaS 목록 화면은 동일한 형태를 갖습니다: 테넌트 경계, 몇 가지 필터, 예측 가능한 정렬. 이런 경우 B-tree가 기본 선택이며 유지 비용도 보통 가장 저렴합니다.
일반적인 패턴은 WHERE tenant_id = ?에 status = ?, user_id = ?, created_at >= ? 같은 필터가 붙습니다. 복합 B-tree 인덱스에서는 동등 필터(=)를 먼저 두고 그다음 정렬에 쓰이는 컬럼을 둡니다.
실무에서 잘 먹히는 규칙:
tenant_id로 시작하세요.= 필터를 둡니다(대개 status, user_id).ORDER BY 컬럼을 둡니다(주로 created_at 또는 id).INCLUDE를 사용해 인덱스 키는 좁게 유지하세요.현실적인 예: 상태로 필터하고 최신순으로 보여주는 Tickets 페이지.
-- Query
SELECT id, status, created_at, title
FROM tickets
WHERE tenant_id = $1
AND status = $2
ORDER BY created_at DESC
LIMIT 50;
-- Index
CREATE INDEX tickets_tenant_status_created_at_idx
ON tickets (tenant_id, status, created_at DESC)
INCLUDE (title);
이 인덱스는 필터와 정렬을 모두 지원하므로 Postgres가 큰 결과 집합을 정렬하지 않아도 됩니다. INCLUDE (title)는 목록 페이지가 접근해야 하는 테이블 페이지 수를 줄이는 데 도움을 주면서 인덱스 키를 필터와 정렬에 집중시킵니다.
시간 범위에 대한 같은 아이디어:
SELECT id, created_at
FROM events
WHERE tenant_id = $1
AND created_at >= $2
AND created_at < $3
ORDER BY created_at DESC
LIMIT 100;
CREATE INDEX events_tenant_created_at_idx
ON events (tenant_id, created_at DESC);
페이지네이션은 많은 SaaS 앱이 느려지는 지점입니다. OFFSET 페이지네이션(OFFSET 50000)은 데이터베이스가 많은 행을 지나가게 합니다. 키셋 페이지네이션은 마지막으로 본 정렬 키를 사용해 항상 빠르게 유지됩니다:
SELECT id, created_at
FROM tickets
WHERE tenant_id = $1
AND created_at < $2
ORDER BY created_at DESC
LIMIT 50;
올바른 B-tree 인덱스가 있다면 테이블이 커져도 이 쿼리는 빠릅니다.
대부분의 SaaS 앱은 멀티테넌트입니다. 모든 쿼리가 하나의 테넌트 안에서 작동해야 합니다. 인덱스에 tenant_id가 없으면 Postgres가 여전히 빠르게 행을 찾을 수 있지만 종종 필요 이상의 인덱스 엔트리를 스캔하게 됩니다. 테넌트 인식 인덱스는 각 테넌트의 데이터를 인덱스 안에 군집화시켜 일상적인 화면이 빠르고 예측 가능하게 유지되도록 합니다.
간단한 규칙: 쿼리가 항상 테넌트로 스코핑된다면 인덱스의 첫 번째 칼럼으로 tenant_id를 두세요. 그다음 가장 자주 필터링하거나 정렬하는 컬럼을 추가합니다.
효과 큰 지루한(그러나 유용한) 인덱스 예:
(tenant_id, created_at) — 최근 항목 목록과 커서 페이지네이션(tenant_id, status) — 상태 필터(Open, Paid, Failed)(tenant_id, user_id) — 특정 사용자가 소유한 항목 스크린(tenant_id, updated_at) — 최근 변경된 관리 뷰(tenant_id, external_id) — 웹훅이나 임포트에서의 조회과도한 인덱싱은 약간 다른 화면마다 새 인덱스를 추가할 때 발생합니다. 새 인덱스를 만들기 전에 기존 복합 인덱스가 필요한 왼쪽 칼럼을 이미 커버하는지 확인하세요. 예를 들어 (tenant_id, created_at)가 있다면 보통 (tenant_id, created_at, id)를 또 만들 필요는 없습니다(정말 그 뒤에 id로 필터링하지 않는 한).
부분 인덱스는 대부분의 행이 관련 없을 때 크기와 쓰기 비용을 줄여줍니다. 예: deleted_at IS NULL이거나 status = 'active'인 경우만 인덱싱하세요.
모든 추가 인덱스는 쓰기를 무겁게 만듭니다. 삽입은 각 인덱스를 업데이트해야 하고, 업데이트는 한 컬럼을 바꾸더라도 여러 인덱스를 건드릴 수 있습니다. Koder.ai 같은 빠른 반복으로 앱을 만들 때 핫한 테이블의 인덱스는 사용자가 매일 실제로 쓰는 몇 가지 쿼리 형태에 집중하세요.
JSONB는 기능 플래그, 사용자 속성, 테넌트별 설정 같은 유연한 추가 필드를 저장할 때 편리합니다. 문제는 서로 다른 JSONB 연산자가 다르게 동작하므로 어떤 인덱스가 좋은지는 쿼리 방식에 달려 있다는 점입니다.
중요한 두 가지 형태:
@>를 써서 “이 JSON이 이 키-값 쌍을 포함하나?”-> / ->>로 특정 필드 값을 꺼내 비교하는 경우@>로 필터하는 일이 잦다면 JSONB 컬럼에 GIN 인덱스를 두는 것이 보통 효과적입니다.
-- Query shape: containment
SELECT id
FROM accounts
WHERE tenant_id = $1
AND metadata @> '{"region":"eu","plan":"pro"}';
-- Index
CREATE INDEX accounts_metadata_gin
ON accounts
USING GIN (metadata);
JSON 구조가 예측 가능하고 최상위 키에 대한 @>만 주로 쓴다면 jsonb_path_ops가 더 작고 빠를 수 있지만 지원하는 연산자가 더 적습니다.
UI가 한 필드(plan 등)를 반복적으로 필터링한다면 그 필드를 추출해서 인덱싱하는 것이 넓은 GIN보다 더 빠르고 경제적일 때가 많습니다.
SELECT id
FROM accounts
WHERE tenant_id = $1
AND metadata->> 'plan' = 'pro';
CREATE INDEX accounts_plan_expr
ON accounts ((metadata->> 'plan'));
실용 규칙: JSONB는 유연하고 드물게 필터되는 속성에 남겨두고, 안정적이고 자주 쓰이는 필드(plan, status, created_at)는 실제 컬럼으로 올리는 것을 고려하세요. 빠르게 생성된 앱이라면 어떤 필터가 매 페이지 나타나는지 보고 스키마를 조금 조정하는 것이 보통 쉽습니다.
예: { "tags": ["beta","finance"], "region": "us" } 같은 JSONB를 저장한다면, 여러 속성 묶음으로 필터할 때는 GIN을 쓰고, 목록 뷰를 주로 좌우하는 몇몇 키(plan, region)는 표현식 인덱스로 만드는 것이 좋습니다.
배열은 저장과 읽기가 쉽기 때문에 유혹적입니다. users.roles text[]나 projects.labels text[] 같은 컬럼은 주로 “이 행이 이 값을 포함하나?”라는 질문을 던질 때 잘 작동합니다. 바로 이런 경우가 GIN 인덱스가 도움을 줍니다.
GIN은 배열을 개별 항목으로 분해해 해당 항목을 포함하는 행을 빠르게 찾을 수 있게 합니다.
배열 쿼리 형태로 GIN이 효과적인 경우:
@> (array contains)&& (any items overlap)= ANY(...), 그러나 @>가 더 예측 가능합니다역할별로 필터링하는 예:
-- Find users who have the "admin" role
SELECT id, email
FROM users
WHERE roles @> ARRAY['admin'];
CREATE INDEX users_roles_gin ON users USING GIN (roles);
두 레이블을 모두 포함하는 프로젝트 필터 예:
SELECT id, name
FROM projects
WHERE labels @> ARRAY['billing', 'urgent'];
CREATE INDEX projects_labels_gin ON projects USING GIN (labels);
사람들이 놀라는 점: 배열을 문자열로 바꿔서 array_to_string(labels, ',') 같은 후처리를 하고 LIKE를 쓰면 GIN 인덱스가 도움이 되지 않습니다. 또한 라벨 내부의 “시작 문자열”이나 퍼지 매칭을 원하면 텍스트 검색 쪽으로 가야 합니다.
배열은 자주 변경되거나 항목별 메타데이터(누가, 언제, 왜 추가했는지)가 필요하거나 항목별 분석이 필요해지면 관리하기 어려워집니다. 그때는 project_labels(project_id, label) 같은 조인 테이블로 정규화하는 편이 검증, 쿼리, 진화 측면에서 보통 더 쉽습니다.
검색 상자에는 두 가지 패턴이 자주 등장합니다: 주제 기반 전체 텍스트 검색과 부분 일치/오타 허용 퍼지 매칭. 올바른 인덱스는 “즉각 응답”과 “사용자 10k에서 타임아웃” 사이를 가르는 차이입니다.
사용자가 실제 단어로 검색하고 관련도 순으로 결과를 보여주고 싶을 때 전체 텍스트 검색을 사용하세요. 보통 tsvector를(생성된 칼럼으로) 저장하고 GIN으로 인덱싱합니다. @@와 tsquery로 검색합니다.
-- Tickets: full-text search on subject + body
ALTER TABLE tickets
ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
to_tsvector('simple', coalesce(subject,'') || ' ' || coalesce(body,''))
) STORED;
CREATE INDEX tickets_search_vec_gin
ON tickets USING GIN (search_vec);
-- Query
SELECT id, subject
FROM tickets
WHERE search_vec @@ plainto_tsquery('simple', 'invoice failed');
-- Customers: fuzzy name search using trigrams
CREATE INDEX customers_name_trgm
ON customers USING GIN (name gin_trgm_ops);
SELECT id, name
FROM customers
WHERE name ILIKE '%jon smth%';
벡터에 무엇을 저장할지: 실제로 검색하는 필드만 넣으세요. 모든 필드(notes, internal logs 등)를 다 넣으면 인덱스 크기와 쓰기 비용을 더 많이 내야 합니다.
이름, 이메일, 짧은 구절을 검색하고 부분 일치나 오타 허용이 필요하면 삼그램 유사도가 좋습니다. 삼그램은 ILIKE '%term%'와 유사성 연산자에 유용합니다. GIN은 “매칭하는가?” 조회에 보통 빠르고, GiST는 유사도로 정렬하는 경우 더 적합할 수 있습니다.
간단한 규칙:
tsvector를 사용하세요.주의할 점:
ILIKE '%abc'는 스캔을 강제합니다(트리거램 없으면).검색 화면을 빠르게 제공하려면 인덱스를 기능의 일부로 생각하세요: 검색 UX와 인덱스 선택은 함께 설계되어야 합니다.
앱이 실행하는 정확한 쿼리부터 시작하세요. "느린 화면"은 보통 WHERE와 ORDER BY가 있는 하나의 SQL 문입니다. 로그나 ORM 디버그 출력, 쿼리 캡처에서 정확한 SQL을 복사하세요.
현실 앱에서 통하는 워크플로:
EXPLAIN (ANALYZE, BUFFERS)를 실행하세요.=, >=, LIKE, @>, @@)에 주목하세요.EXPLAIN (ANALYZE, BUFFERS)를 실행하세요.구체적 예. Customers 페이지가 테넌트와 상태로 필터하고 최신순으로 정렬하며 페이지네이션한다고 합시다:
SELECT id, created_at, email
FROM customers
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT 50;
EXPLAIN이 순차 스캔과 정렬을 보여준다면 필터와 정렬에 맞춘 B-tree 인덱스가 보통 문제를 해결합니다:
CREATE INDEX ON customers (tenant_id, status, created_at DESC);
느린 부분이 metadata @> '{"plan":"pro"}' 같은 JSONB 필터라면 GIN이 가리키는 신호입니다. 전체 텍스트 검색이라면 to_tsvector(...) @@ plainto_tsquery(...)도 GIN 기반 검색 인덱스를 가리킵니다. 겹침(overlap)이나 근접 연산자 집합이면 GiST가 적합한 경우가 많습니다.
인덱스를 추가한 뒤에는 트레이드오프를 측정하세요. 인덱스 크기, 삽입/업데이트 시간, 상위 느린 쿼리들에 도움이 되었는지 확인하세요. Koder.ai로 빠르게 개발하는 프로젝트라면 이 재검토가 사용되지 않는 인덱스가 쌓이는 것을 막아줍니다.
인덱스 문제의 대부분은 B-tree vs GIN vs GiST 중 하나를 고르는 문제가 아닙니다. 앱이 테이블을 쿼리하는 방식과 맞지 않는 인덱스를 만드는 것이 문제입니다.
가장 해를 끼치는 실수들:
tenant_id와 created_at로 시작하는데 인덱스가 created_at로 시작하면 플래너가 인덱스를 건너뛸 수 있습니다.status, is_active, 불리언 같은 컬럼 단독 인덱스는 매칭되는 행이 많아 거의 도움이 되지 않습니다. 선택적 컬럼과 결합하거나 건너뛰세요.ANALYZE가 최근에 실행되지 않으면 플래너가 잘못된 계획을 선택할 수 있습니다.구체적 예: Invoices 화면이 tenant_id와 status로 필터한 뒤 created_at DESC로 정렬한다면 status만 인덱싱한 것으로는 거의 도움이 안 됩니다. tenant_id -> status -> created_at 순서의 복합 인덱스가 더 적합합니다. 이 한 가지 변경으로 세 개의 별도 인덱스를 추가하는 것보다 낫습니다.
모든 인덱스는 비용을 내야 합니다. 실제 쿼리에서 가치를 증명해야 하며 이론만으로 추가하지 마세요.
인덱스 변경은 배포하기 쉽지만 쓰기 비용을 늘리고 바쁜 테이블을 잠글 수 있어 되돌리기 귀찮을 수 있습니다. 병합 전에 작은 릴리스처럼 다루세요.
무엇을 최적화할지 결정하세요. 로그나 모니터링에서 자주 실행되는 쿼리와 지연 시간이 큰 쿼리 두 가지를 뽑으세요. 각 쿼리에 대해 필터 컬럼, 정렬 순서, 조인, 사용된 연산자(=, range, IN, ILIKE, JSONB 연산자, 배열 포함)를 적으세요. 추측을 막고 올바른 인덱스 타입을 고르는 데 도움됩니다.
사전 배포 체크리스트:
인덱스 추가 후 실제 플랜으로 개선되었는지 확인하세요. 같은 쿼리로 EXPLAIN (ANALYZE, BUFFERS)를 전/후로 실행해 비교하세요. 그리고 하루 정도 프로덕션을 관찰하세요:
Koder.ai로 개발한다면 변경 옆에 생성된 SQL을 보관해 인덱스가 실제 앱이 실행하는 쿼리와 일치하도록 하세요.
관리자 화면을 떠올려보세요: 테넌트 스코프의 Users 목록, 몇 가지 필터, 마지막 활동 기준 정렬, 검색 상자. 인덱스가 이론에서 벗어나 실제로 시간을 절약해 주는 지점입니다.
보통 보게 되는 세 가지 쿼리 형태:
-- 1) List page with tenant + status filter + sort
SELECT id, email, last_active_at
FROM users
WHERE tenant_id = $1 AND status = $2
ORDER BY last_active_at DESC
LIMIT 50;
-- 2) Search box (full-text)
SELECT id, email
FROM users
WHERE tenant_id = $1
AND to_tsvector('simple', coalesce(name,'') || ' ' || coalesce(email,'')) @@ plainto_tsquery($2)
ORDER BY last_active_at DESC
LIMIT 50;
-- 3) Filter on JSON metadata (plan, flags)
SELECT id
FROM users
WHERE tenant_id = $1
AND metadata @> '{"plan":"pro"}'::jsonb;
이 화면을 위한 작지만 의도적인 인덱스 세트:
(tenant_id, status, last_active_at DESC).tsvector 칼럼에 대한 GIN 인덱스.@>를 자주 쓴다면 GIN (metadata), 주로 한 키만 필터링하면 ((metadata->> 'plan')) 같은 표현식 B-tree.혼합된 요구는 정상입니다. 한 페이지에서 필터+검색+JSON이 동시에 필요하면 모든 것을 하나의 거대한 인덱스로 억지로 넣지 마세요. 정렬/페이지네이션은 B-tree로 두고, 비싼 부분(보통 GIN)을 하나만 추가하세요.
다음 단계: 느린 화면 하나를 골라 상위 2-3개 쿼리 형태를 적고, 각 인덱스를 목적(필터, 정렬, 검색, JSON)에 따라 검토하세요. 인덱스가 실제 쿼리와 명확하게 일치하지 않으면 계획에서 제외하세요. Koder.ai로 빠르게 반복하는 경우, 화면을 추가할 때마다 이 검토를 하면 인덱스 스프로울을 막을 수 있습니다.
인덱스는 PostgreSQL이 테이블의 대부분을 읽지 않고도 일치하는 행을 찾도록 도와줍니다. 목록, 대시보드, 검색 같은 일반적인 SaaS 화면에서 올바른 인덱스는 느린 순차 스캔을 빠른 조회로 바꿔 테이블이 커져도 성능을 유지하게 합니다.
대부분의 앱 쿼리에는 B-tree를 먼저 고려하세요. B-tree는 = 필터, 범위, 조인, 정렬에 가장 적합합니다. 포함/멤버십(JSONB, 배열)이나 전체 텍스트 검색이 주요 패턴이면 GIN을, 겹침(overlap)이나 거리/가장 가까운(match) 같은 문제라면 GiST를 고려하세요.
=로 필터링하는 컬럼을 먼저 두고, 마지막에 정렬에 쓰는 컬럼을 둡니다. 이 순서는 플래너가 인덱스를 효율적으로 읽고 별도 정렬 없이 결과를 반환하게 돕습니다.
쿼리가 항상 tenant_id로 스코핑된다면 인덱스에 tenant_id를 처음에 넣는 것이 좋습니다. 이렇게 하면 인덱스 안에서 각 테넌트의 데이터가 모여서 일상적인 목록 화면에서 읽어야 할 데이터 양을 줄여줍니다.
INCLUDE는 인덱스 키를 넓히지 않고도 목록 페이지의 추가 표시 컬럼을 인덱스에 포함시켜 인덱스 전용 읽기(index-only scan)를 가능하게 합니다. 필터와 정렬에 필요한 몇 개 컬럼 외에 화면에 표시되는 몇 가지 필드를 더 읽어야 할 때 유용합니다.
부분 인덱스는 관심 있는 행의 일부(예: deleted_at IS NULL, status = 'active')에만 인덱스를 만들 때 유용합니다. 인덱스 크기와 쓰기 비용을 줄일 수 있어 빈번한 삽입/업데이트가 있는 핫 테이블에서 특히 좋습니다.
JSONB에 대해 @> 같은 포함 연산을 자주 쓰면 해당 컬럼에 GIN 인덱스를 두는 것이 보편적으로 효과적입니다. 반면 특정 키(예: plan)를 자주 필터링하면 (metadata->> 'plan') 같은 표현식 인덱스(B-tree)가 더 작고 빠를 수 있습니다.
배열의 주된 질문이 “이 배열에 X가 포함되어 있는가?”라면 GIN이 적합합니다 (@> 또는 && 연산자). 다만 항목별 메타데이터가 필요하거나 항목별 분석/자주 변경이 발생하면 조인 테이블(예: project_labels)에 정규화하는 편이 관리나 확장성에서 더 낫습니다.
전체 텍스트 검색은 tsvector를 생성(종종 생성된 칼럼)하고 GIN으로 인덱싱한 뒤 @@로 검색하는 방식이 표준입니다. 이름·이메일 같은 부분 일치나 오타 허용이 필요하면 삼그램(trigram) 인덱스(보통 GIN)가 적합합니다.
애플리케이션이 실제로 실행하는 정확한 SQL을 복사해 EXPLAIN (ANALYZE, BUFFERS)로 병목을 찾아보세요. 스캔·정렬·비용을 유발하는 연산자를 확인한 뒤 그 연산자와 정렬에 맞는 가장 작은 인덱스를 추가하고, 다시 EXPLAIN으로 개선 여부를 검증하는 방식이 반복 가능한 워크플로입니다.