postgreSQL 주요 기능

# postgreSQL 주요 기능

------------------

0. Transaction Isolation 개념

- session1 : read A > update A to A1

- session2 : read A > ...................... > read A

- session2 가 읽은 A 는 A 일까 A1 일까?

- **3가지 상황 을 처리하기 위한 isolation level**

          

- dirty reads : session2 가 session1 이 commit 하기 전부터 A1 를 읽어옴

- nonrepeatable reads : session2 가 session1 이 commit 해도 기존에 읽은 A 가 있다면 A 를 읽어옴

- phantom reads : session2 가 session1 이 A 데이터를 변경하지 못한다 (shared lock 적용)

  - oracle 도 default 로 read commited level 을 지원한다

    - [참고URL](https://support.microsoft.com/ko-kr/kb/601430)

    - 추가 : oracle transaction 은 statement 단위로 롤백이 되지만, postgreSQL 은 transaction 전체가 롤백 된다

1. MVCC (Multi Version Concurrency Control)

- 만약 하나의 record 에 대해 각각의 세션이 데이터 변경, 삭제 등의 동작을 했다면, 세션1(A), 세션2(A'), 세션3(A'') 는 존재해야 한다

- oracle 방식 (우월한 방식이다)

- 세션1 이 select 중 세션2가 업데이트할 시,

- A 를 다른 공간(undo)에 업데이트 하고 A' 를 원본 데이터로 저장한다

- 세션1에게는 다른 공간의 A를 읽어온다

- undo 영역에 대한 가비지 컬랙터가 존재하며, 부담이 없다

- 대신 rollback 이 느리다

    - postgreSQL 방식

    - 세션1 이 select 중 세션2가 업데이트할 시,

- A' 를 같은 테이블에 저장 append 한다

- 세션1에게는 A' 의 데이터 입력 시간 을 확인하고 skip 한다

- 각 레코드 마다 트랜잭션에 대한 created, expired 컬럼을 가지게 된다

- xmin , xmax : transaction 의 시작,끝

- cmin , cmax : transcation 내의 query 에 매겨지는 순서

- 각 레코드에 불필요한 overhead(각각 32bit)가 발생하게 된다

- 2 ~ 40억 의 값이 부여된다

- 현재 트랜잭션 ID 를 기준으로 +20억 까지는 미래, -20억 까지는 과거

- 그림

- transaction 관리 table 을 두고, transaction 의 상태 (commit, rollback, progress ...) 를 관리한다

- A 를 지우기 위한 가비지 컬랙터가 필요하다, 원본 테이블에 대한 컬랙터의 동작으로 어렵다

- OID 

- table 생성시, with OID 옵션을 추가하면 생긴다

- OID 를 별로도 저장하지 않는다?

- 내부 관리용으로 사용된다

2. Vacuuming

- 쓰레기 데이터를 정리하여 쾌적하게 청소하라는 명령 으로 "디스크 조각 모음" 이라고 생각하면 된다

- Update나 Delete 한다고 해서 해당영역이 자동으로 재사용되거나 사라지지 않고 이러한 오래된 영역을 재사용하거나 정리해주는 명령어가 Vacuum 이다

- postgresql.conf 파일내의 AUTOVACUUM PARAMETERS 관련 옵션을 지정

- default on : 오프시키면 안된다

- table 단위로 on/off 가 가능하다

- log_autovaccum_min_duration (default -1) : 

- freeze_max_age : 2억번 이상의 transaction 이 발생했을 때

- max_wokers (default 3) : 

- naptime (default 1 min) : worker 가 계속 일하는 것이 아니라 쉬는 시간을 갖는다

- threshold : 50 개가 변경이 되면 vacuum 해라

- analyze : 통계 정보가 변경되는 간격

    - http://blog.gaerae.com/2015/09/postgresql-vacuum-fsm.html

3. reindexing

    - 작업 소요 시간의 요인

- indexes 의 수

- indexes 의 크기

- 동작 중의 서버 load

    - reindexing 하는 이유

    - index 가 "bloated" 상태가 되었을때 == 많이 비어있거나 거의 비어있는 상태 일때

    - 대체할 fillfactor 와 같은 parameter 가 존재할 때

필펙터는 인덱스가 생성될 때 인덱스 페이지를 채우는 비율 인덱스가 처음 생성될때만 적용되고 운영중에는 유지되지 않기 때문에 데이타 수정이 잦은 DB라면 의미 없는 값이 된다 데이타 수정이 거의없는 DB라면 필펙터를 높여서 읽기 성능을 높일수 있겠지만 데이타 수정이 잦아지면 페이지 스플릿의 증가로 쓰기 성능이 나빠질수 있다

   - CONCURRENTLY 옵션이 실패하여 "invalid" index 로 남겨졌을 때

이 옵션은 index 생성시에 어떠한 lock 없이 동작하게끔 한다

4. clustering

- clustering 하면 지정된 index 에 의해 순서대로 정렬한다 (하는 중에는 table lock 이 걸린다)

- 반복적으로 적용되어 처리해주는 것이 아니라 명령 실행 이후 데이터는 무정렬 상태로 출력된다

5. backup

- logical backup

- partial backup and recovery (table 단위)

- can validate all data files

- logical backup 의 방법 : sql commands 를 통해

            - pg_dump 유틸리티 를 통해 다양한 포멧의 형태로 백업이 가능하다

                - 주요 사용옵션 : -Fp, -Fd (테이블별 병렬처리로 덤프), -Fc

            - pg_dumall 유틸리티 를 통해 클러스터 전체에 대한 백업을 받는다, 자주 사용하지 않는 기능이다

            - pg_restore 유틸리티 를 통해 복구가 가능하다

    - physical backup

    - 디렉토리 구조 전체의 데이터 파일을 통째로 복사한다

    - type

    - Cold backup : db 멈춘상태에서 백업

    - Continuous Archiving == Pointing In Time Recovery (PITR):

    - checkpoint 가 발생하기 전에 transaction log 를 모아두는 archive log 공간으로 복사

    - 추가적인 WAL log 나 transaction log 를 적용하여 시점 이후의 데이터를 복원 가능하다

    - setting

wal_level=archive    archive_mode=on

                - 실행 : cp -i %p from_path/%f </dev/null

6. Extensions

- 다양한 plugin 을 지원한다

- 특징

- package manager 가 있다

- 표준화된 개발환경 (PGXS) 을 지원한다

- 간단한 설치 및 관리

- http://pgxn.org

- 대표적인 extension

- pgcrypto : 암호화 / 복호화 용도

- pg_buffercache : 메모리의 버퍼 상태를 모니터링 하는 용도

- pg_freespacemp : FSM 정보를 제공 하는 용도

- pageinspect : 블럭에 대한 덤프 기능

- pg_prewarm : 디스크의 내용을 메모리에 올려둔다

- pg_stat_statements : 조회한 쿼리 정보 조회 기능

- memory 공간에 대한 추가적인 설정이 필요하다

- pg_repack : 논란의 요지가 있으나 유용하다

- online 중에 clustering, vacuum full 기능을 lock 없이 사용하도록 지원