따봉 구걸남's 블로그

Postgresql 에서 많이 사용하는 파라미터와 설명을 정리해 보았다. 

postgresql 은 오라클과 마찬가지로 시퀀스를 사용할수 있다.

세부적으로는 owned by, serial, generated 등 생소한 구문들이 조금 있다.

1. 문법

CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]

2. owned by

OWNED BY는 시퀀스를 특정 테이블의 특정 컬럼과 명시적으로 연결 

이렇게 연결된 테이블을 삭제할 때 해당 시퀀스도 자동으로 삭제됨.

3. serial

SERIAL은 PostgreSQL에서 자동 증가 컬럼을 쉽게 만들기 위한 편의 타입
SERIAL을 지정하면, 내부적으로 시퀀스를 생성하고,
 해당 컬럼의 기본값을 nextval('시퀀스명')으로 지정하며, 시퀀스와 컬럼을 OWNED BY로 연결한다.

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  name VARCHAR(20)
);

이렇게 생성하면 아래의 작업이 내부적으로 이루어진다. 

- 시퀀스 생성
- 컬럼 기본값 지정
- OWNED BY 설정


3. generated

GENERATED ... AS IDENTITY는 ANSI SQL 표준에 맞춘 자동 증가 컬럼 정의 방식 이다.

PostgreSQL 10 이상에서 지원하며, 마이그레이션이나 표준 호환성이 중요할 때 사용한다. 

 

1) ALWAYS: 값을 직접 입력하면 오류 발생 (무조건 자동 증가)

2) BY DEFAULT: 값을 직접 입력할 수도 있고, 입력하지 않으면 자동 증가


CREATE TABLE users (
  user_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(20)
);

 
1. PG 의 MVCC (multi version concurrent control) 구현

오라클은 읽기 일관성을 위한 MVCC 을 undo 세그먼트에 cr 블록을 생성한다. 

postgresql 은 구조상 undo 세그먼트라는 것을 가지고 있지 않아 데이터파일 내에 변경전 값을 저장하게 된다

이것을 dead tuple 이라고 부른다. 시간이 지나 어느곳에도 참조하지 않는 데드튜플은 vacuum 이 정리한다.

* IO 단위인 page 에 변경전 튜플과 현재튜플이 같이 저장되어 효율적이지 않은 단점

 
2. PG 의 트랜잭션ID (XID) 특성

데이터의 선후관계 확인을 위해 오라클은 SCN 이라는 것을 사용하는데, postgresql 에서는 Transaction ID 를 사용한다. 

xid 는 unsigned 32 bit 정수로 40억 개 (2^32) 까지 사용 가능하다. 이것을 다쓰면 한바퀴 돌아 순환하는 구조이다. 

튜플은 생성시점의 xid (xmin), 삭제시점의 xid (xmax) 를 가지게 된다. 아래와 같은 구조를 가지게 된다. 

간단하게 그림으로 표현해본다면..

최초 xid 101 시점에 튜플이 생성되고, 이후 튜플이 업데이트 되면 원본의 xmax 가 103 으로 바뀌고

새롭게 생성된 튜플의 xmin 도 103 이 되고, 업데이트 된 값 'BBB' 가 저장된다. 

xid 102 시점에 조회를 하던 세션은 xmin 값이 자신보다 작은 101 을 가진 튜플의 값을 조회 ('AAA') 한다.

3. XID wraparound 

transaction ID 는 무한하지 않기 때문에 40억을 넘어가면 다시 돌게 되어있다. 

한번 순환한 xid 는 과거 시점 xid 가 자신의 xid 보다 클 수 밖에 없어, 그대로 사용하게 되면 이전 시점 데이터를 볼수가 없게 된다. 

그래서 xid 값을 frozen 처리를 하게 되고 이것은  "이 튜플은 아주 오래된 것이므로, 모든 트랜잭션에서 볼 수 있다" 라고 판단하게 된다.

 
4. vacuum 명령 

데이터 변경이 많은 테이블은 dead tuple 이 많이 발생하게 되어 많은 공간과 IO 의 비효율이 발생 할 수 있다. 

이를 정리해주는 vacuum 작업을 주기적으로 돌려야 한다. (물론 xid 의 frozen 작업도 vacuum 이 처리한다.)

실제 live tuple 에 비해 변경 작업이 많아 dead tuple 이 많이 생성된 경우, 테이블의 전체 크기가 커지게 되는데

이를 bloating 되었다고 표현하며, 주기적으로 관찰할 필요가 있다. 

* Dead Tuple, Live Tuple 확인

-- 1. 통계수집기 활용 방식 : 빠름, 근사치 제공. (vacuum 이후 수치) 
SELECT c.relname AS table_name,
       pg_stat_get_live_tuples(c.oid) AS live_tuple,
       pg_stat_get_dead_tuples(c.oid) AS dead_tuple
FROM pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
WHERE pg_stat_get_live_tuples(c.oid) > 0
  AND c.relname NOT LIKE 'pg_%'
ORDER BY dead_tuple DESC;

-- 1. 통계수집기 활용 방식
  select relname, n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup 
 from pg_stat_all_tables 
 where relname = 't1' ;

-- 2. pgstattuple 함수를 통한 통계 : 실시간, 정확

 select relname, table_len, tuple_percent, dead_tuple_percent,free_percent
 from( select relname,(pgstattuple(oid)).*
       from pg_class 
       where relkind = 'r' ) a 
 where table_len >= 1048576  -- 100kb
 order by dead_tuple_percent desc limit 10;

 select avail,count(*)
 from pg_freespace('t1') 
 group by avail ;
 
 

-- VACUUM 실행 예시
VACUUM;                    -- 전체 테이블에 대해 간단히 실행
VACUUM FULL;           -- 전체 테이블 공간 회수 = 재구축 (배타적 잠금 주의) 
* pg_repack : 온라인 재구축. 짧은 시간 락

VACUUM ANALYZE;        -- VACUUM 후 통계 정보 갱신
VACUUM [테이블명];         -- 특정 테이블에 대해 실행