[PostgreSQL] Hash 인덱스의 원리 및 특징

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

• format_list_bulleted Postgresql
• · 2023. 9. 13.
• textsms

[PostgreSQL] B-tree 인덱스의 원리 및 특징

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

• format_list_bulleted Postgresql
• · 2023. 9. 12.
• textsms

[PostgreSQL] 인덱스(INDEX)개념 및 생성, 삭제, 분석, 설계 방법

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 id..

• format_list_bulleted Postgresql
• · 2023. 9. 12.
• textsms

Postgresql Lock이란? (조회 및 kill, Dead lock)

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 기능을 제공한다. 애플리케이션..

• format_list_bulleted Postgresql
• · 2023. 9. 11.
• textsms

[PostgreSQL] Trigger, Procedure, Function (history 관리하기)

* 가장 보편적으로 쓰이는 간단한 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, ne..

• format_list_bulleted Postgresql
• · 2023. 9. 8.
• textsms

Full Text Search를 활용한 데이터베이스 성능 향상

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

• format_list_bulleted Postgresql
• · 2023. 9. 7.
• textsms