[DB] PostgreSQL ⇒ OLTP ..? OLAP ..? - (Citus+ cstore_fdw)

나는 RedShift는 OLAP DB로 알고 있고 PostgreSQL이 기반이 되어서 당연히 PostgreSQL이 OLAP DB라고 생각했다.
그런데 왜 OLTP 라고 하는걸까…?

1. PostgreSQL..?

1. PostgreSQL은 오픈소스 객체-관계형 데이터베이스 관리 시스템 (ORDBMS)이다.
2. PostgreSQL은 OLTP 워크로드에 적합한 인기 있고 강력한 오픈 소스 관계형 DBMS 이다.

---

2. PostgreSQL의 기본 철학 및 특징

1. ACID 보장

2. SQL 표준 준수

   ⇒ ANSI SQL 표준을 대부분 따름

3. 확장성

4. 객체 지향 요소 지원

5. 다양한 확장 모듈

   ⇒ PostGIS, TimescaleDB, Citus, FDW 등등…

---

3. PostgreSQL은 기본적으로 OLTP에 최적화되어 있다.

• 빠른 트랜잭션 처리
• 강력한 동시성 제어
• 정형화된 업무 데이터 처리에 강함
• 다양한 트랜잭션 오류 복구 기법 지원

⇒ MVCC, ACID, 다양한 인덱싱 및 트랜잭션 처리 기능을 통해 빠른 실시간 처리에 강점을 가짐

---

4-1. PostgreSQL의 OLAP 의 한계

• PostgreSQL은 기본적으로 행 기반(Row-oriented) 저장 구조 ⇒ 분석 쿼리 시 읽어야할 데이터가 많아져 성능 저하

4-2. 기존 한계 극복

• [비정규화] : 별도로 나누었던 테이블을 하나로 합쳐서 빠른 조회

• [인덱스 최적화] : 쿼리에서 자주 쓰이는 컬럼에 적절한 인덱스를 생성

  • B-Tree 외에 GIN, GiST 같은 특수 인덱스를 활용해 복잡한 검색 가속화 → 더 알아봐야함.

• [Materialized View 활용] : 자주 쓰이는 복잡한 쿼리 결과를 미리 저장해서 조회 시 빠르게 응답

  ⇒ 정기적으로 REFRESH MATERIALIZED VIEW 명령으로 최신화
  

• [Window Function] : OLAP 분석에 필요한 누적합, 순위, 이동평균 등 복잡한 분석 함수를 SQL에서 직접 처리 가능

• [파티셔닝-Partitioning] : 대용량 테이블을 날짜, 지역 등 기준으로 나누어 쿼리 범위를 좁혀 처리 속도 개선

  ⇒ PostgreSQL은 RANGE, LIST, HASH 파티셔닝 지원

  1. RANGE 파티셔닝 (범위 파티셔닝) ⇒ 범위

  특정 컬럼 값의 범위를 기준으로 데이터를 나누는 방식

  CREATE TABLE sales (
      id serial,
      sale_date date,
      amount numeric
  ) PARTITION BY RANGE (sale_date);
  
  CREATE TABLE sales_2024_q1 PARTITION OF sales
      FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
  
  CREATE TABLE sales_2024_q2 PARTITION OF sales
      FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

  2. LIST 파티셔닝 (목록 파티셔닝) ⇒ 특정 값

  특정 컬럼의 값 목록을 기준으로 데이터를 나누는 방식

  CREATE TABLE users (
      id serial,
      region text,
      name text
  ) PARTITION BY LIST (region);
  
  CREATE TABLE users_asia PARTITION OF users FOR VALUES IN ('KR', 'JP', 'CN');
  CREATE TABLE users_europe PARTITION OF users FOR VALUES IN ('FR', 'DE', 'UK');
  

  3. HASH 파티셔닝 (해시 파티셔닝) ⇒ 컬럼 값 → Hash 함수

  컬럼 값을 해시 함수로 분산시켜 균등하게 데이터를 나누는 방식

  ex) FOR VALUES WITH (MODULUS 4, REMAINDER x)
  ⇒ 해시 함수의 결과를 4로 나눈 나머지(remainder) 값이 각각 0, 1, 2, 3인 경우를 의미합니다.

  CREATE TABLE logs (
      id serial,
      user_id int,
      log_data text
  ) PARTITION BY HASH (user_id);
  
  CREATE TABLE logs_part_0 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 0);
  CREATE TABLE logs_part_1 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 1);
  CREATE TABLE logs_part_2 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 2);
  CREATE TABLE logs_part_3 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 3);

---

5. 이외 방법

5-1. PostgreSQL 14 이후 Columnar Storage 기능 (columnar) - cstore_fdw

PostgreSQL 14 부터 USING columnar 구문으로 열 저장 테이블을 만들 수 있음.

내용

• PostgreSQL 14 부터 USING columnar 옵션은 아직 실험적…
• 기본적으로 USING columnar 는 포함되어 있지 않음. ⇒ PostgreSQL 기본 버전에는 열 저장(Columnar Storage) 기능이 없음. 그래서 Columnar Storage를 구현하려면 외부 확장 모듈을 설치해야 함.
  
• 대표적인 Columnar Storage Extension이 cstore_fdw 이다.

사용 방법

1. Linux 기준 - cstore_fdw 설치

# PostgreSQL 개발 헤더와 빌드 도구 설치 (OS별 다름)
sudo apt-get install postgresql-server-dev-all build-essential

# cstore_fdw 클론 및 빌드
git clone https://github.com/citusdata/cstore_fdw.git
cd cstore_fdw
make
sudo make install

2. PostgreSQL에서 cstore_fdw 확장 활성화 및 사용

-- 확장 설치
CREATE EXTENSION cstore_fdw;

3. 외부 서버 정의

# 외부 데이터 여기 있어요라고 알려주는 역할
# cstore
CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;

• cstore_fdw 는 파일기반 FDW라서, 외부 DB주소는 없지만 구조상 이렇게 정의해야 PostgreSQL이 이해함. ⇒ 즉, FDW와 연결되는 “외부 데이터 위치”를 등록하는 작업

  PostgreSQL의 FDW?
  PostgreSQL이 외부 데이터 소스에 접근할 수 있게 해주는 기능

4. 외부 테이블 정의

-- 컬럼형 테이블 생성 (foreign table)
CREATE FOREIGN TABLE cstore_table (
    id int,
    data text,
    value numeric
) SERVER cstore_server OPTIONS(compression 'pglz');

• FOREIGN TABLE은 실제로는 PostgreSQL 내부에 존재하지 않고, 외부 파일에 연결된 테이블처럼 동작합니다.
• 위 예제에서는 cstore_fdw가 내부적으로 .cstore 파일 포맷을 이용해 컬럼 저장 방식으로 데이터를 관리합니다.

REF : https://m.blog.naver.com/geartec82/221987422533

5-2. PostgreSQL 기반 분산 확장 기능 (Citus) - 분산 OLTP/OLAP 처리

PostgreSQL을 수평 확장(sharding)하여 분산 환경에서 처리할 수 있게 도와주는 확장 모듈
OLTP + OLAP 모두 지원 가능하나, 대량 데이터 처리 및 분석 쿼리(OLAP)에 특히 강력함.

내용

• PostgreSQL은 기본적으로 단일 노드에서만 작동 → 대규모 데이터에서는 병목 발생
• Citus는 PostgreSQL을 분산 시스템으로 확장해줌
  • 테이블을 자동으로 Shard내고
  • 여러 Worker Node로 분산 처리 가능

⇒ 특히 OLAP 대용량 분석용으로 성능 강함.

사용 방법

1. Citus 설치

# PostgreSQL과 Citus repository 추가 및 설치
curl https://install.citusdata.com/community/deb.sh | sudo bash
sudo apt-get install postgresql-15-citus-11.2

2. 확장 기능 활성화

-- 마스터 노드에서 실행
CREATE EXTENSION citus;

3. Worker Node 등록

-- 마스터 노드에서 각 워커 노드 등록
SELECT * from master_add_node('worker-node-ip', 5432);

⇒ Worker Node 에도 PostgreSQL과 Citus가 설치되어 있어야 함.

[Worker Node Setup]

• postgresql.conf ⇒ Postgresql 설정

# 외부 접속 허용 (필수)
listen_addresses = '*'

# 필요시 포트 변경 (기본은 5432)
port = 5432

# 워커 간 통신을 위해 max_prepared_transactions 설정
max_prepared_transactions = 150

• pg_hba.conf ⇒ 인증 설정

워커 노드의 pg_hba.conf 파일에 마스터 노드의 IP가 접속 가능하도록 설정해주어야 합니다.

# 마스터 노드가 192.168.1.10일 경우
host    all             all             192.168.1.10/32        md5

• 사용자 및 비밀번호 동기화

마스터 노드에서 사용하는 DB 사용자명/비밀번호가 워커 노드에도 동일하게 생성되어 있어야 합니다.

-- 워커 노드에서 실행 (예: postgres 사용자)
CREATE USER postgres WITH PASSWORD 'yourpassword';

ALTER USER postgres WITH PASSWORD 'yourpassword';

• 포트 개방

4. 테이블 분산 선언

-- 일반 테이블 생성
CREATE TABLE orders (
    order_id bigint,
    customer_id bigint,
    amount numeric
);

-- 특정 컬럼 기준으로 분산 선언
SELECT create_distributed_table('orders', 'customer_id');

⇒ 열 단위 저장으로 OLAP 분석 속도 개선 가능

---

요약: OLTP → OLAP으로 확장 가능한 이유

💡이런 것들 때문에 가능하다..!

1. cstore_fdw
2. Citus
3. USING columnar
4. Partitioning
5. Parallel Query ⇒ 병렬 쿼리…? 요런게 있네용