GIN(Generalized Inverted Index) 인덱스란 ? PostgreSQL에서 복합적인 데이터를 효율적으로 검색하기 위해 사용되는 특수한 인덱스 유형 주요 특징은 아래와 같다.
1. 복합 데이터 처리: 배열, JSON, hstore, tsvector 등 복합적인 데이터 타입에 적합 2. 키워드 기반 검색: 문서나 텍스트에서 특정 단어를 빠르게 찾을 수 있다. 3. 역인덱스 구조: 키워드나 토큰을 기반으로 데이터를 인덱싱 4. 전문 검색(Full-text search) 지원: 텍스트 검색 속도를 크게 향상 5. 다양한 연산자 지원: LIKE, ILIKE, ~, ~* 등 다양한 검색 연산자를 지원
GIN 인덱스는 특히 대량의 텍스트 데이터를 처리할 때 B-tree 인덱스보다 훨씬 효과적이며, 검색 성능을 크게 향상시킬 수 있다. 다만, 인덱스 생성과 업데이트에는 상대적으로 많은 시간이 소요될 수 있습니다
## 복합 데이터 처리
테스트 테이블을 만들고 GIN 인덱스 사용 전후의 플랜을 비교해보자
-- Create a table with a JSONB column CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(100), details JSONB );
-- Query using the GIN index to find products with specific attributes EXPLAIN ANALYZE SELECT name, details FROM products WHERE details @> '{"brand": "TechCo"}';
-- Query to find products with specific tags EXPLAIN ANALYZE SELECT name, details FROM products WHERE details->'tags' @> '"electronics"'::jsonb;
-- Create a GIN index on the JSONB column CREATE INDEX idx_product_details ON products USING GIN (details);
## 전문검색 처리
-- 문서 테이블 생성 CREATE TABLE documents ( id SERIAL PRIMARY KEY, content TEXT, content_tsv tsvector );
-- 함수 생성: 랜덤 문장 생성 CREATE FUNCTION random_sentence() RETURNS TEXT AS $$ DECLARE words TEXT[] := ARRAY['PostgreSQL', 'database', 'index', 'query', 'performance', 'GIN', 'B-tree', 'full-text', 'search', 'optimization', 'SQL', 'data', 'management', 'system', 'relational', 'ACID', 'transaction', 'concurrency', 'scalability', 'replication']; sentence TEXT := ''; word_count INT; BEGIN word_count := 5 + random() * 10; -- 5에서 15 단어 사이의 문장 생성 FOR i IN 1..word_count LOOP sentence := sentence || ' ' || words[1 + random() * (array_length(words, 1) - 1)]; END LOOP; RETURN trim(sentence); END; $$ LANGUAGE plpgsql;
-- 10,000개의 샘플 문서 생성 INSERT INTO documents (content, content_tsv) SELECT random_sentence(), to_tsvector('english', random_sentence()) FROM generate_series(1, 10000);
-- GIN 인덱스 생성 CREATE INDEX idx_gin_content ON documents USING gin(content_tsv);
-- 통계 업데이트 ANALYZE documents;
-- 검색 쿼리 실행 및 실행 계획 확인 EXPLAIN ANALYZE SELECT id, content FROM documents WHERE content_tsv @@ to_tsquery('english', 'postgresql & index');
pg_tables: 데이터베이스의 모든 테이블 정보 pg_views: 모든 뷰에 대한 정보 pg_indexes: 데이터베이스의 모든 인덱스 정보 pg_stats: 플래너(query planner)가 사용하는 통계 정보 pg_settings: 서버 설정 파라미터에 대한 정보 pg_roles: 데이터베이스 롤(사용자 및 그룹)에 대한 정보 pg_user: 데이터베이스 사용자 정보
추가적인 유용한 시스템 뷰
pg_locks: 현재 보유 중인 락(lock)에 대한 정보 pg_stat_activity: 현재 실행 중인 쿼리와 세션에 대한 정보 pg_prepared_statements: 준비된 구문(prepared statements)에 대한 정보 pg_available_extensions: 사용 가능한 확장(extensions)에 대한 정보 pg_cursors: 현재 열려있는 커서에 대한 정보 pg_file_settings: 설정 파일의 내용 요약을 제공 pg_matviews: 모든 물리화된 뷰(materialized views)에 대한 정보
# 테이블 권한 부여하기
select * from pg_tables where schemaname='public' and tablename like 'abc%' ;
select * from pg_views where schemaname='public' and tablename like 'abc%' ;
select * from information_schema.role_table_grants where grantee='USER1' and table_name like 'abc%'
