PostgreSQL

1. GiST 인덱스란? Generalized Search Tree의 약자이며 B-tree와 같은 balanced search tree의 형태이다. B-tree인덱스는 정렬된 채로 비교&일치의 연산에 최적화된 채로 연결되어있다. 하지만 현대의 다양한 데이터 종류 (기하학적, 텍스트문서, 이미지 등)를 연산하는 데는 적합하지 않다. GiST 인덱스는 이러한 데이터 타입의 인덱싱을 위해 설계되었다. GiST 인덱스는 각 유형의 데이터를 Balanced tree 형태로 구성하게하고, tree에 접근하는 연산자를 정의해 준다. 각각 leaf node는 table row(TID)와 boolean 형태의 predicate를 가지고 있고 인덱스 데이터(key)는 이 predicate와 부합한다. 그 후는 일반적인 tree search처럼, 루트노드에서 시작하여, 어떤 child node로 진입할지를 결정한다. 그러다가 leaf node를 발견하면, 그 결과들을 반환한다. ...

1. Hash 인덱스란? 해쉬 인덱스의 기본 아이디어는, hash function을 통해 작은 숫자를 데이터와 조합하여 integer 형태의 해쉬값 (최대 2^32 = 4B)을 생성하고 해쉬값을 테이블 행 정보(TID)가 저장될 배열의 인덱스 값으로 사용하는 것이다. 이 배열의 각 요소를 해시 테이블 버킷(hash table bucket)이라고 한다. 데이터 조회 시, hash function을 통해 생성된 key가 포함된 bucket을 찾고, 그 bucket만 확인하면 실제 데이터의 위치를 바로 확인할 수 있다. 데이터의 크기에 상관없이 인덱스의 크기가 작고 검색이 빠르다. 1개의 데이터를 조회하는 시간은 O(1)로 빠르지만 해쉬 테이블 내의 값들은 정렬이 되어있지 않기 때문에 범위 비교나 부정형 비교가 포함된 조건에서는 인덱스를 사용할 수 없다. Hash function이 버킷 단위로 소스 값을 더 균일하게 분배할수록 효율이 좋다. ...

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 락은 여러 트랜잭션에서 다중으로 적용될 수 있다.) ...