PostgreSQL에는 6가지의 인덱스 종류가 있다. 각각의 인덱스는 다양한 데이터 탐색을 위해 다른 알고리즘을 사용한다. 그중 가장 일반적으로 사용되고, 가장 먼저 도입된 알고리즘인 B-tree 인덱스에 대해 알아보자. 1. B-tree 인덱스란? ▪ 트리의 노드를 밸런스 있게 재정렬한 트리형태의 자료구조 ▪ B-tree는 Binary 가 아닌 Balanced의 약자 ▪ 컬럼의 기존 데이터를 변형하지 않음 ▪ 인덱스 구조체 내에서는 항상 정렬된 상태를 유지 2. B-tree 인덱스의 원리 ▪ B-tree 인덱스의 자료구조 형태 ...

1. 인덱스 컨트롤 1-1. 인덱스 조회 SELECT * FROM pg_indexes WHERE tablename = '{테이블명}'; -- 테이블명에 '' 필요 1-2. 인덱스 생성 -- 단일 인덱스 CREATE INDEX {인덱스명} ON {테이블명} USING btree({컬럼명}); -- 결합 인덱스 CREATE INDEX {인덱스명} ON {테이블명} USING btree({컬럼명1}, {컬럼명2}); -- 유니크 인덱스 CREATE UNIQUE INDEX {인덱스명} ON table_name ({컬럼명}); 1-3. 인덱스 삭제 DROP INDEX {인덱스명}; 1-4. 인덱스 사용 빈도 확인 SELECT schemaname, relname, indexrelname, idx_scan as idx_scan_cnt FROM pg_stat_user_indexes ORDER BY idx_scan; 1-5. 인덱스 손상 시 재인덱싱 REINDEX INDEX {인덱스명} REINDEX TABLE {테이블명} REINDEX DATABASE {데이터베이스명} ...

1. Lock 확인방법 SELECT PSAT.RELNAME, PL.LOCKTYPE, PL.PID, PL.MODE, PL.GRANTED FROM PG_LOCKS PL, PG_STAT_ALL_TABLES PSAT WHERE PL.RELATION = PSAT.RELID 2. Lock Kill 방법 SELECT PG_CANCEL_BACKEND([PID]) SELECT PG_TERMINATE_BACKEND([PID]) Lock 리스트에서 조회된 PID를 넣고 cancel, 혹은 terminate 시켜주면 된다. cancel은 해당 프로세스만을, terminate는 상위 프로세스들까지 종료시킨다. 3. Lock 이란? (Postgresql) Postgresql은 다양한 종류의 lock 기능을 제공한다. 애플리케이션 단에서 제어도 가능하지만, 대부분 기본적인 SQL 실행 시 적절한 락을 자동실행시켜 관련 테이블의 무결성 유지한다. 3-1. 테이블 단위 Lock 다음 락 들은 모두 테이블 단위의 락이며, 명칭과 상관없이 테이블 단위로 적용된다. 서로 다른 락이 충돌했을때의 상관관계에 의해 대기 상태로 돌입한다. (테이블 단위 락은 유형에 따라 서로 충돌여부가 다름) 한 테이블에는 2개의 트랜잭션이 동시에 락 적용 될 수 없다. (서로 충돌되지 않는 락은 여러 트랜잭션에 동시에 적용될 수 있다.) 특정 락은 self-conflicting 될 수 있다. (ex. access exclusive 락은 중첩불가 access share 락은 여러 트랜잭션에서 다중으로 적용될 수 있다.) ...

* 가장 보편적으로 쓰이는 간단한 history 저장 트리거 생성 예제 특정 테이블에 insert, update가 수행될 경우 무조건 내역에 “insert”를 하는 간단한 트리거 생성 예제이다. 1-1. 함수를 실행할 트리거 생성 create trigger trigger_save_history after insert or update on A for each row execute procedure trigger_insert(); 1-2. 실제 insert문이 실행되는 함수 CREATE OR REPLACE FUNCTION trigger_insert() returns trigger AS $$ DECLARE BEGIN insert into B (id, values, date) values (new.id, new.values, current_timestamp()); return NULL; END; $$ LANGUAGE 'plpgsql'; 하지만 특정 table에 insert, delete, update에 따라 서로 다른 테이블에 이력을 보관하거나, 기존 이력을 업데이트하는 등 ...

1. 문제상황 긴 텍스트에서 단순 like 조합 외 방법으로 유사 문자열 검색 (Ex. Susan loves hiking 을 “love hike” 이라는 키워드로 검색하고자 함) RDBMS에서 수천만 건의 데이터 처리 시 긴 문자열 검색 속도 향상 2. Full Text Search(전문검색)란? 게시물의 내용/제목 등 문장, 문서 전체에서 키워드를 검색하는 기능이다. 단순한 like, 비교연산자와 달리 각 단어의 Token화 및 정규화를 통해 긴 문장내에서의 유사 검색을 가능하게 한다. Postgresql 기본 인덱스인 b-tree인덱스로는 Like 와 같은 패턴 매칭 검색시 양쪽에 %%를 거는 경우는 인덱스를 타지 않지만, gin 인덱스를 사용하여 빠른 검색이 가능하다. ...