리고니

INDEX 생성 시 CONCURRENTLY 옵션

그냥 일반 CREATE INDEX 하면 테이블에 LOCK 발생CONCURRENTLY 사용하면 LOCK 없이 인덱스 생성 가능, DROP 도 가능하지만 테이블 전체를 쭉 읽고 그 사이에 변경된 데이터에 대한 변경사항에 대한 적용을 위해 한 번 더 읽는다.그래서 순간 I/O 가 증가디스크 I/O 가 처리하는 동안 해당 프로세스는 잠을 잔다.(완료되기를 대기)

Hash Join

1. 선행 테이블에 조건을 만족하는 행을 찾아서 조인 키(컬럼) 기준으로 hash 알고리즘을 적용하여 hash 테이블 생성2. 후행 테이블도 동일하게 생성하는데 선행 테이블의 해쉬값과 맞는지(등가 =) 찾음3. 이후 JOIN 수행항상 HASH 값이 같아야 조인이 수행되므로 동등 조인에서만 사용가능선행 테이블의 결과값(ROWS)이 작아야 유리CPU,메모리 작업 위주 데이터 처리해쉬 함수를 이용하여 조인을 수행하기 때문에 '='로 수행하는 조인에서만 사용가능 합니다.

Sort Merge Join

1. 선행 테이블에 조건을 만족하는 행을 찾는다 후행 테이블도 동일하게2. PGA 공간에 조인컬럼을 기준으로 정렬을 수행3. 이후 두 테이블을 JOIN 수행해당 조인은 정렬할 데이터가 많은경우 TEMP 영역을 사용하기 때문에 대량 조인 작업에서는 Hash Join이 유리INDEX 있으면 자동정렬대부분 해시조인인 보다 느린 성능을 보이나, 아래와 같은 상황에서는 소트머지 조인이 유용하다.First 테이블에 소트연산을 대체할 인덱스가 있을 때조인할 First 집합이 이미 정렬되어 있을 때조인 조건식이 등차(=)조건이 아닐 때두 테이블의 사이즈가 비슷한경우에 유리하며, 사이즈 차이가 큰 경우에는 불리하고, 비효율적인 방법이다.

NL(Nested Loop) Join

NL 조인은 조인컬럼에 인덱스를 이용하는 방식, INDEX SCAN(RANDOM I/O) 사용1. 선행 테이블을 순차적으로 읽어 조건 만족하는 행 찾음, 만족하지 않으면 필터링2. 그 조인키를 가지고 후행 테이블에서 접근하여 조인키를 찾는다 조인 시도(inner[driven] 조인키에 인덱스가 없으면 풀스캔)3. 후행 테이블에 조인 키가 없으면 필터링4. 있으면 추출 버퍼에 넣음5. 반복 수행중첩 FOR 문 이라고 보면됨.선행 테이블의 처리 범위(ROW) 가 성능을 결정, 양이 많지 않으면 인덱스 없이 Full Scan해도 한번만 하면 됨.후행테이블에 대해서는 반드시 인덱스가 존재해야 NL 조인이 가능하다.랜덤 엑세스 위주라 대용량 처리 시 불리함,유니크 인덱스를 이용하여 소량 테이블 조인시 유리

배치 I/O

인덱스를 통한 Table Random Access는 Single Block I/O으로 작동하기에 엄청난 부하를 유발하는데, 심지어 버퍼 캐시 경유 비용 + 래치 경합까지 발생할 수 있어 로우가 많아 질수록 각종 Wait Event가 발생해 성능이 급격히 저하되었다.-> 실행계획 : TABLE ACCESS BY INDEX ROWID BATCHED-> 위 성능 개선을 위해 12c 부터 batch I/O 추가BATCH I/O1. 인덱스를 통해 버퍼 캐시에서 찾지 못하면 DISK I/O(TABLE SCAN) 했던 SINGLE I/O 방식이 아니라2. DISK I/O 를 잠시 미루고 일정량 모은 뒤 일괄 처리 방식* ROWID 로 버퍼 캐시 조회 -> 실패 시 ROWID 저장* 위 과정 반복하여 ROWID 일정량..

부분범위 처리

PostgreSQL에서 “부분 범위 처리”를 잘 하려면WHERE + ORDER BY + LIMIT를 인덱스 구조와 일치시키는 방식 사용 유도그러면 상위 N개만 읽고 스캔을 멈출 수 있어 성능 향상-> 테이블 전체를 읽지 않고, 인덱스 풀 스캔도 하지않고, 인덱스 자체로 정렬되어 있기 때문에 정렬된 엔트리를 위에서 순차적으로 20건만 스캔하고 끝남Oracle 은 자동으로 범위처리가 된다고함 ;;인덱스가 없거나, ORDER BY랑 안 맞는 경우 모든 row 스캔 후 정렬이 필요한 컬럼 기준으로 전체 정렬-> 이러면 최종 출력 수 는 소량이지만 작업은 전체 대상임

다중 컬럼 인덱스(복합 인덱스)

선두 컬럼부터 연속해서 조건/정렬을 사용해야 효율적선두 컬럼을 건너뛰고 그 뒤에 있는 컬럼만으로는 인덱스를 제대로 못탐선두 컬럼에는 =(동등 조건), 뒤에는 범위 조건(LIKE, BETWEEN) 이 베스트조건절에 선두 컬럼이 없으면, 옵티마이저가 TABLE FULL SCAN 혹은 INDEX FULL SCAN(데이터가 적으면) 으로 유도한다인덱스 컬럼에 함수/표현식 씌워서 인덱스를 못 타는 경우 많음인덱스 컬럼을 WHERE , ORDER BY, GROUP BY 에 사용 되어야 함그리고 인덱스에 포함되지 않는 컬럼이 들어오면(SELECT) TABLE HEAP 스캔 발생

세션 관련 SQL세션 수select count(*) from pg_stat_activity ;Active 세션 정보SELECT datname, usename, state, queryFROM pg_stat_activityWHERE state = 'active';idle 세션 정보SELECT datname, usename, state, queryFROM pg_stat_activityWHERE state = 'idle';특정 사용자 세션 조회SELECT datname, usename, state, queryFROM pg_stat_activityWHERE username = '';현재 실행 중인 쿼리 중단SELECT pg_cancel_backend(PID);현재 세션을 강제 종료SELECT pg_terminat..

PostgreSQL의 psql 명령줄 도구에서 사용할 수 있는 백슬래시(\) 명령어들의 전체 목록에 대한 설명입니다.각 명령은 다양한 작업을 손쉽게 수행할 수 있게 도와주는 유용한 도구일 것 같습니다몇 가지 자주 쓰이는 걸 요약하자면:🔍 정보 조회 관련\d → 테이블, 뷰, 시퀀스 등 목록 보기\dt → 테이블만 보기\dv → 뷰만 보기\di → 인덱스 목록 보기\df → 함수 목록 보기\l → 데이터베이스 목록 보기\du → 역할(사용자) 목록 보기\x → Expanded 모드 전환 (복잡한 결과 보기 좋음)🧪 쿼리 실행 관련\g → 현재 쿼리 실행\gx → Expanded 모드로 쿼리 실행\gexec → 쿼리 결과를 다시 쿼리로 실행\gset → 쿼리 결과를 변수로 저장🧰 버퍼 & 파일\e → ..

데이터베이스 계정 또는 패스워드 정책 수립시PostgreSQL은 기본적인 설정 기능을 제공하지 않기 때문에 추가 설정이 필요합니다.PostgreSQL 패스워드 정책 관련 모듈은 크게 passwordcheck 및 credcheck 가 있습니다.아래에서는 각 모듈들에 대한 설치과정과 기능 및 장단점을 비교하여 설명합니다.01. 설치과정(1) PasswordCheck1. 해당 모듈은 사용자 비밀번호 정책에 대한 기능을 가짐2. PostgreSQL 설치 시 기본적으로 해당 라이브러리를 제공1. shared_preload_libraries = 'passwordcheck' 추가2. 서버 재기동 (2) CredCheck1. 해당 모듈은 사용자 계정 관련 정책을 포함하여 passwordcheck 보다 정책이 다양함2...