PostgreSQL:GIN
lastmod 2026-01-05created 2026-01-051 edits
A Generalized Inverted Index (GIN) is an index that performs well when the queries to be processed involve composite data.
What is a GIN index?
An index method in PostgreSQL for efficiently searching composite data types such as arrays, JSONB, and full-text search.
Key features
- Array element search: quickly finds specific values inside an array
- Containment search: optimized for operators such as
@>,<@,&& - Read performance: searches are very fast
- Write performance: slower than B-tree when inserting/updating data
Usage example
-- tags 배열 컬럼에 GIN 인덱스 생성
-- Create GIN index on tags array column
CREATE INDEX IF NOT EXISTS idx_proj_tags ON proj USING GIN(tags);
-- 특정 태그를 포함한 프로젝트 검색 (빠름!)
-- Search for projects containing specific tag (fast!)
SELECT * FROM proj WHERE tags @> ARRAY['python'];
-- 여러 태그 중 하나라도 포함
-- Contains any of multiple tags
SELECT * FROM proj WHERE tags && ARRAY['python', 'web'];
-- 태그가 정확히 포함되어 있는지
-- Check if tags are contained
SELECT * FROM proj WHERE ARRAY['python'] <@ tags;