1. Outer Table / Inner Table 개념

• Outer Table: Nested Loop 조인에서 바깥 루프를 도는 테이블. 먼저 처리됨.
• Inner Table: 바깥에서 선택된 row에 대해 반복적으로 접근하는 테이블.
• EXPLAIN 실행계획에서 먼저 나오는 쪽이 outer, 후순위가 inner로 보면 됨.

2. 실행계획 분석 예시 1 (Nested Loop)

Nested Loop
  -> Seq Scan on t1      -- outer
  -> Index Scan on t2    -- inner

• t1이 먼저 실행되며 outer, 각 row에 대해 t2를 index scan
• loop 수를 보면 t1은 loop=1, t2는 loop=101 등으로 확인 가능

3. Buffer 정보에서 shared read / written 의미

• shared read: 디스크에서 shared buffer로 로딩된 블록 수
• written: buffer 공간이 부족해서 dirty buffer가 디스크로 flush된 수
• 단순 읽기만 해도 쓰기가 발생할 수 있음 → dirty buffer 교체 때문

4. Materialize 노드

• 작은 inner table 결과를 메모리에 저장 (work_mem) 해 두고 outer loop에서 재사용
• 조건: 작은 테이블, work_mem 내 적재 가능
• 조인 순서에 영향: Materialize가 가능하면 작은 테이블을 inner로 둬서 reuse 유도

실험 결과

• Materialize ON: t2 outer, t1 inner → 효율적
• Materialize OFF: t1 outer, t2 inner → Index Scan 없이 반복 Full Scan, 성능 저하

5. Memoize + Nested Loop Join

개념 요약

• Memoize는 파라미터화된 Index Scan의 결과를 key 기반으로 캐시해서 재사용
• 조건: inner 쿼리가 WHERE t.col = outer.col 형태일 때

실행계획 예시

Nested Loop
  -> Seq Scan on bizpl_sale_sum b    -- outer
  -> Memoize
      -> Index Scan on product a     -- inner

• b.prod_id 값이 반복되므로 product 테이블에 대한 결과를 Memoize 노드가 캐시
• 73,200개 row 중 200번만 Index Scan → 나머지는 캐시 재활용

6. Memoize 통계 해석

1. 인덱스 듀플리케이션 (Index Duplication)

• 동일한 인덱스 키 값이 여러 개 존재할 경우, PostgreSQL은 해당 키 값을 인덱스에 한 번만 저장하고, 그 키에 해당하는 여러 TID(Tuple ID) 를 연결해서 관리한다.
• 이는 인덱스 크기를 줄이기 위한 설계이며, 키 값을 매번 저장하는 오라클 방식과 대비된다.
• 유니크 인덱스에서도 MVCC 때문에 일시적으로 같은 키 값을 가진 dead row + live row 가 존재할 수 있으며, 이 경우에도 키는 하나만 저장되고 TID만 여러 개가 유지된다.

  위 개념을 이해하기 위해서는 다 같이 PostgreSQL의 MVCC 구조를 배워보는 시간을 가져보면 좋을듯

2. Oracle과의 인덱스 구조 비교

3. MVCC와 인덱스 설계

• PostgreSQL은 MVCC (Multi-Version Concurrency Control) 을 사용하여, 하나의 행이 여러 버전(TID)을 가질 수 있음.
• VACUUM 이전까지는 같은 키 값을 가진 데드/라이브 로우가 공존하므로 인덱스도 여러 TID를 유지해야 함.
• 이 구조에 따라 PostgreSQL 인덱스는 중복 키 값을 하나로 통합해 저장하고, 다수의 TID를 매핑함.

4. table에 대해 vacuum을 수행하면 index의 dead TID도 같이 정리 될까?

• chatgpt 답변 :
  • VACUUM은 테이블의 데드 튜플 제거뿐 아니라 인덱스 내 데드 TID도 함께 제거한다.
  • VACUUM FULL은 테이블 및 인덱스를 완전히 재작성한다.

5. REINDEX의 필요성

• REINDEX는 인덱스만 별도로 재작성하는 명령어.
• VACUUM FULL이 전체 인덱스를 재생성하긴 하지만, 다음의 경우에는 REINDEX가 더 적합:
  • 인덱스 손상 시 (corruption)
  • 특정 인덱스에만 bloat가 집중된 경우
  • 전체 테이블에 대한 vacuum full 수행이 어려운 경우(테이블에 대한 exclusive lock, 모든 index에 대한 재생성 등과 같은 이유로 인해)

6. 인덱스의 EXACT vs LOSSY 모드?

• EXACT 모드: TID까지 정확히 포함 → 테이블 접근 없이 처리 가능
• LOSSY(LOGICAL) 모드: 페이지 단위 정보만 포함 → Heap Scan 단계에서 조건 재검증 필요 (Recheck)

7. Index Only Scan의 두 가지 조건

1. Index Coverage: 인덱스만으로 필요한 컬럼을 모두 제공할 수 있어야 함
2. Visibility Map (VM):
   • 각 테이블 페이지가 모든 튜플이 visible함을 기록한 비트맵
     • dead tuple 없이 모든 live 튜플만으로 구성되어 있을 때

test_ddl.sql

📚 학습 범위: 『PostgreSQL 튜닝 기술 상』 83페이지까지

1. Gather 노드 당 병렬 실행 수란?

• PostgreSQL의 병렬 쿼리에서 Gather 노드는 병렬 워커들의 결과를 모으는 역할을 한다.
• max_parallel_workers_per_gather는 한 Gather 노드가 사용할 수 있는 병렬 워커의 수를 의미함.
• OLTP 환경에서는 병렬 실행의 오버헤드가 더 클 수 있어 0으로 설정하면 throughput이 좋아질 수 있음.

2. random_page_cost는 왜 SSD에서 낮춰야 하나?

• random_page_cost는 랜덤 블록 접근 비용을 추정하는 파라미터로, 기본값은 4.0 (HDD 기준).
• PostgreSQL 옵티마이저는 이 값을 기반으로 Index Scan 사용 여부를 결정함.
• SSD는 랜덤 I/O 속도가 빠르기 때문에 이 값을 1.1 ~ 1.5 정도로 낮추는 것이 적절.
• 값이 낮을수록 Index Scan이 더 자주 선택됨.

3. Index Scan이 랜덤 I/O인 이유

• Index Scan은 인덱스를 통해 얻은 TID를 이용해 테이블(heap)의 각 블록을 개별적으로 읽는다.
• 이 블록들이 물리적으로 흩어져 있어 랜덤 I/O가 발생함.
• 반면, Seq Scan은 테이블을 순차적으로 읽기 때문에 순차 I/O 처리.

4. HDD vs SSD: 랜덤 I/O 성능 차이

• HDD는 물리적 이동이 필요해 랜덤 I/O가 느림.
• SSD는 메모리 기반 구조라 랜덤/순차 모두 빠름.

5. work_mem의 적용 범위

• work_mem은 쿼리 전체가 아닌, 각 정렬/해시 노드마다 개별 적용되는 메모리 제한값.
• 예: work_mem = 100MB, Sort 노드 10개 → 최대 1GB 사용 가능.
• 병렬 쿼리인 경우 워커마다 work_mem 별도 적용됨.
• 너무 크게 설정 시 다수의 쿼리 동시 실행 시 메모리 부족 가능성 있음.

6. EXISTS 서브쿼리에서는 왜 startup cost가 더 중요할까?

• EXISTS는 서브쿼리 결과가 존재하는지만 확인하고, row 하나만 찾으면 바로 종료됨.
• 그래서 전체 비용(total cost)보다 첫 결과를 얻기까지 드는 비용(startup cost)이 훨씬 더 중요함.
• 옵티마이저는 startup cost가 낮은 쿼리 계획을 선호하여 빠르게 True/False를 판단하려 함.

7. loops의 의미는?

• loops는 해당 쿼리 계획 노드가 실제 실행된 횟수를 나타냄.
• loops = 1: 한 번만 실행됨 (예: 전체 테이블 1회 스캔)
• loops = N: 외부 쿼리로 인해 N번 반복 실행됨 (예: Nested Loop Join에서 내부 테이블)
• 실제 총 row 수는 loops × rows로 계산됨.

예:

Index Scan on orders (actual time=0.02..0.05 rows=3 loops=10)
→ 총 3 × 10 = 30 rows 반환

8. EXPLAIN에서 generic plan과 바인드 변수의 관계

• 바인드 변수는 SQL문을 재사용하면서 값만 나중에 전달하는 방식.
• PostgreSQL은 바인드 변수가 있을 경우, 처음엔 custom plan을 만들고, 일정 횟수 이후 generic plan으로 전환할지 판단함.
• Custom Plan: 바인드 값에 따라 실행 계획이 매번 달라짐
• Generic Plan: 바인드 값과 관계없이 하나의 고정된 계획을 반복 사용
• 특정 바인드 값에 따라 성능 차이가 클 경우, generic plan이 비효율적일 수 있음.

✅ 참고 파라미터 정리

# tibble 관련 패키지
sudo R CMD INSTALL /data1/staging/R_lib/dplyr/tibble/pillar/utf8_1.2.5.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/dplyr/tibble/pillar_1.10.2.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/dplyr/tibble/pkgconfig_2.0.3.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/dplyr/tibble/magrittr_2.0.3.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/dplyr/tibble/fansi_1.0.6.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/dplyr/tibble_3.2.1.tar.gz

# tidyselect 관련 패키지
sudo R CMD INSTALL /data1/staging/R_lib/dplyr/tidyselect/withr_3.0.2.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/dplyr/tidyselect_1.2.1.tar.gz

# 기타 의존 패키지
sudo R CMD INSTALL /data1/staging/R_lib/dplyr/R6_2.6.1.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/dplyr/generics_0.1.3.tar.gz

# dplyr 설치
sudo R CMD INSTALL /data1/staging/R_lib/dplyr/dplyr_1.1.4.tar.gz

✅ dplyr 설치 확인 (R 콘솔)

library(dplyr)
cat("dplyr version:", as.character(packageVersion("dplyr")), "\n")

출력 예:

dplyr version: 1.1.4

🧪 PostgreSQL PL/R 테스트 함수

CREATE OR REPLACE FUNCTION dplyr_test() RETURNS text AS $$
  library(dplyr)

  df <- data.frame(
    category = c("A", "B", "A", "B", "A"),
    value = c(10, 20, 15, 25, 30)
  )

  result <- df %>%
    group_by(category) %>%
    summarise(total = sum(value), .groups = "drop")

  result <- as.data.frame(result)

  paste(capture.output(print(result)), collapse = "\n")
$$ LANGUAGE plr;

🧾 실행 결과

SELECT dplyr_test();

 category total
 1        A    55
 2        B    45
(1 row)

1. 파일 준비

🌐 필요한 R 라이브러리 다운로드

CRAN에서 필요한 패키지를 수동 다운로드
🔗 https://cran.r-project.org/web/packages/available_packages_by_name.html

📁 다운로드 파일 업로드 확인

[postgres@pgprimary01 R_lib]$ ls | grep reshape2
reshape2_1.4.4.tar.gz

2. reshape2 및 종속 패키지 설치

📦 reshape2 의존 패키지 설치

sudo R CMD INSTALL /data1/staging/R_lib/Rcpp_1.0.14.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/plyr_1.8.9.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/stringr_1.5.1.tar.gz # 밑에서 의존성 해결 필요

📦 stringr 의존 패키지 설치

sudo R CMD INSTALL /data1/staging/R_lib/stringi_1.8.7.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/cli_3.6.5.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/glue_1.8.0.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/magrittr_2.0.3.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/rlang_1.1.6.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/lifecycle_1.0.4.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/vctrs_0.6.5.tar.gz
sudo R CMD INSTALL /data1/staging/R_lib/stringr_1.5.1.tar.gz

📦 reshape2 설치

sudo R CMD INSTALL /data1/staging/R_lib/reshape2_1.4.4.tar.gz

2. reshape2 설치 확인 (R 콘솔)

library(reshape2)
cat("reshape2 version:", as.character(packageVersion("reshape2")), "\n")

reshape2 version: 1.4.4

3. PostgreSQL 내 PL/R 함수 테스트

CREATE OR REPLACE FUNCTION reshape2_test() RETURNS text AS $$
  library(reshape2)
  df <- data.frame(
    id = c(1,1,2,2),
    time = c("morning","evening","morning","evening"),
    value = c(10,20,30,40)
  )
  result <- dcast(df, id ~ time)
  paste(capture.output(print(result)), collapse = "\n")
$$ LANGUAGE plr;

SELECT reshape2_test();

 id evening morning
 1      20      10
 2      40      30

• OS: Red Hat Enterprise Linux 8.10
• DB: PostgreSQL 16
• R: 4.5.0-3.el8 (EPEL)
• PL/R 버전: 8.4.7

1. R 설치

💡 사전 준비

R을 설치하기 위해선 여러 dependency를 직접 다운로드해 설치가 필요하다. (유료 구독을 쓰는 사람들은 논외)

📦 수동 설치한 패키지 목록

# R 설치를 위한 dependency
openblas-0.3.15-6.el8.x86_64.rpm

# openblas-0.3.15-6.el8.x86_64.rpm 설치를 위한 dependency list
openblas-devel-0.3.15-6.el8.x86_64.rpm
openblas-openmp-0.3.15-6.el8.x86_64.rpm
openblas-openmp64-0.3.15-6.el8.x86_64.rpm
openblas-openmp64_-0.3.15-6.el8.x86_64.rpm
openblas-serial64-0.3.15-6.el8.x86_64.rpm
openblas-serial64_-0.3.15-6.el8.x86_64.rpm
openblas-threads64-0.3.15-6.el8.x86_64.rpm
openblas-threads64_-0.3.15-6.el8.x86_64.rpm

위 패키지들은 일반 repo에서는 구할 수 없어 수동으로 rpm을 다운받아 설치 진행

2. PL/R 설치

📥 PL/R .rpm 다운로드

wget https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8-x86_64/plr_16-8.4.7-1PGDG.rhel8.x86_64.rpm

📂 서버에 업로드 및 설치

# 업로드 확인
ls | grep plr
# 출력 예시:
plr_16-8.4.7-1PGDG.rhel8.x86_64.rpm

# 설치
sudo dnf install ./plr_16-8.4.7-1PGDG.rhel8.x86_64.rpm

3. PostgreSQL에서 PL/R 확장 사용

🔍 확장 가능한지 확인

SELECT * FROM pg_available_extensions WHERE name = 'plr';

 name | default_version | installed_version |                            comment
------+-----------------+-------------------+----------------------------------------------------------------
 plr  | 8.4.7           |                   | load R interpreter and execute R script from within a database

✅ 확장 생성 및 버전 확인

CREATE EXTENSION plr;
SELECT plr_version();

 plr_version
-------------
 8.4

4. 마무리

✅ 간단한 테스트 함수

CREATE OR REPLACE FUNCTION r_now() RETURNS text AS $$
  sprintf("R time is now: %s", Sys.time())
$$ LANGUAGE plr

SELECT r_now();
                   r_now
-------------------------------------------
 R time is now: 2025-05-05 06:00:52.607928
(1 row)

PostgreSQL 내부에서 직접 R 코드를 실행

FastAPI 사용을 위한 환경 구성 절차

✅ 실험 목적

• AI 에이전트가 자연어로 PostgreSQL에 질문을 던지는 구조를 실험
• FastAPI를 이용해서 간단한 REST API 서버를 구성함으로써, LLM과 DB 사이의 연동 흐름을 손쉽게 체험하고 테스트
• 궁극적으로는 MCP와의 차이를 확인하기 위함

사전 작업 (root 권한)

• 먼저 psycopg2 패키지 빌드를 위해 Python 개발 헤더와 C 컴파일러를 설치

  dnf install -y python3-devel gcc

[이하 작업은 postgres 유저로 진행]

1. 실험용 디렉토리 만들기

mkdir /data1/api_pg_test
cd /data1/api_pg_test

2. Python 가상환경(venv) 생성

• 프롬프트 앞에 (venv)가 붙으면 가상환경이 활성화된 상태

  python3 -m venv venv
  source venv/bin/activate

3. 필요한 패키지 설치

• FastAPI 사용을 위한 패키지 설치

  pip install fastapi uvicorn psycopg2

4. FastAPI 서버 코드 작성

vi api_server.py

• python code

  from fastapi import FastAPI
  from pydantic import BaseModel
  import psycopg2
  import re
  from typing import Any
  

app = FastAPI()

PostgreSQL 연결 설정

DB_CONFIG = { "host": "localhost", "port": 5432, "user": "postgres", "password": "yourpassword", "dbname": "yourdb" }

class PGRequest(BaseModel): input: str

class PGResponse(BaseModel): output: Any

간단한 프롬프트 → 쿼리 매핑 함수

def prompt_to_query(prompt: str) -> str: if "고객" in prompt and "주문" in prompt: # 고객 ID 추출 (예: "고객 2" → 2) match = re.search(r"고객\s*(\d+)", prompt) if match: customer_id = match.group(1) return f"SELECT * FROM orders WHERE customer_id = {customer_id} ORDER BY order_date DESC LIMIT 5;" else: return "SELECT '고객 ID를 찾을 수 없습니다.' AS message;" else: return "SELECT '알 수 없는 요청입니다.' AS message;"

@app.post("/mcp/query", response_model=PGResponse) async def handle_query(req: PGRequest): prompt = req.input query = prompt_to_query(prompt)

try:
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor()
    cur.execute(query)
    rows = cur.fetchall()
    colnames = [desc[0] for desc in cur.description]
    result = [dict(zip(colnames, row)) for row in rows]
    cur.close()
    conn.close()
except Exception as e:
    return PGResponse(output={"error": str(e)})

return PGResponse(output=result)

@app.get("/") def root(): return {"status": "API PostgreSQL 서버 실행 중 🚀"}

### 5. FastAPI 서버 실행 

* 로컬 접속만 허용하는 경우

uvicorn api_server:app --reload

Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)

* 외부 접속 허용하는 경우 (서버 IP 바인딩)
- 0.0.0.0으로 설정하면 모든 IP 대역에서 접속 허용 (실험용일 땐 편리하지만, 운영 환경에서는 위험할 수 있음)

uvicorn api_server:app --host 192.x.x.x --port 8000 --reload

### 6. 로컬 PC에서 접속 테스트

http://192.x.x.x:8000/
* 아래 메세지가 뜨면 정상
{"status":"API PostgreSQL 서버 실행 중 🚀"}

### 7. 조회 테스트

* Swagger UI를 통해 API 자연어 질의 테스트

- Request

{ "input": "고객 2의 주문 내역 보여줘" }

- Response

{ "output": [ { "id": 3, "customer_id": 2, "order_date": "2024-12-03", "amount": 20000 } ] } ```

• 추후 자세히 들여다 보면 좋을듯

https://docs.arenadata.io/en/blog/current/ADB/greenplum-vs-citus.html

https://docs.arenadata.io/en/blog/current/ADB/greenplum-vs-citus-2.html

Testing an HA solution is a time consuming process, with many variables. This is particularly true considering a cross-platform application. You need a trained system administrator or a consultant to do this work. It is not something we can cover in depth in the documentation.

That said, here are some pieces of your infrastructure you should be sure to test:

• Network (the network in front of your system as well as the NICs [physical or virtual] themselves)
• Disk IO
• file limits (nofile in Linux)
• RAM. Even if you have oomkiller turned off, the unavailability of RAM could cause issues.
• CPU
• Virtualization Contention (overcommitting the hypervisor)
• Any cgroup limitation (likely to be related to the above)
• kill -9 of any postgres process (except postmaster!). This is a decent simulation of a segfault.

One thing that you should not do is run kill -9 on a postmaster process. This is because doing so does not mimic any real life scenario. If you are concerned your infrastructure is insecure and an attacker could run kill -9, no amount of HA process is going to fix that. The attacker will simply kill the process again, or cause chaos in another way.

PostgreSQL HA

클라우드 아키텍처 센터 구글 클라우드에서는 가장 기본적인 수준에서 데이터 계층 HA는 다음으로 구성된다고 말한다.

• 기본 노드의 장애 발생 여부를 식별하는 메커니즘
• 복제본 노드가 기본 노드로 승격되는 경우 장애 조치를 수행하는 프로세스
• 애플리케이션 요청이 새 기본 노드에 도달할 수 있도록 쿼리 라우팅을 변경하는 프로세스
• 장애 조치 전 원래 용량의 기본 노드와 복제본 노드를 사용하여 원래 아키텍처로 대체할 수 있는 방법(선택사항)

PostgreSQL HA solution

위에서 말한 네 가지 사항을 준수하는 PostgreSQL HA 솔루션에 대해 기술한다. 각 솔루션 별 HA 아키텍처를 설명하고 장 단점을 기술하고자 한다.

• pg_auto_failover
• patroni + etcd
• repmgr
• pg_poolII

1. Pg_auto_failover

pg_auto_failover docs pg_auto_failover는 적극적으로 개발 중인 오픈소스(PostgreSQL 라이선스) PostgreSQL 확장 프로그램(extension)이다. 올바른 HA 구성을 위해서는 최소 3개의 노드가 필요하며, 각각 모니터 노드, 기본 노드, 보조 노드 역할을 수행한다.

구성 요소

• Monitor(관리 노드): 클러스터의 상태를 모니터링하고 자동 장애 조치를 수행하는 중앙 관리 노드
• Primary(주 노드): 애플리케이션이 읽기 및 쓰기 작업을 수행하는 기본 데이터베이스 노드
• Sencondary(스탠바이 노드): Primary 노드를 실시간으로 복제하여 장애 발생 시 즉시 승격 가능

PAF 아키텍처(single) PAF 아키텍처(multi)

장점

• 비교적 구성이 간단함
• auto FO 가능
• 수동 전환 가능
• 국내,외 reference가 많음 (N사 Cloud DB for PostgreSQL, VMware PostgreSQL 등)
• split-brain 상황을 회피할 수 있음(Monitor 노드로 인해)
• PostgreSQL 이외의 다른 외부 구성 요소에 의존하지 않음

단점

• 모니터 노드 장애시 FO 불가

2. repmgr

repmgr Docs repmgr 는 PostgreSQL 서버 클러스터에서 복제 및 장애 조치를 관리하기 위한 오픈 소스 도구 모음이다. PostgreSQL의 기본 제공(hot-standby) 기능을 향상시켜 스탠바이 서버를 설정하고, 복제를 모니터링하고, 장애 조치나 수동 스위치오버 작업과 같은 관리 작업을 수행할 수 있는 도구를 제공한다.

구성요소

• Witness(증인 서버):(필수는 아님)여러 개의 대기 서버가 있는 장애 조치 상황에서 새로운 기본 서버를 결정하는 데 도움
• Primary(주 서버): 애플리케이션이 읽기 및 쓰기 작업을 수행하는 기본 데이터베이스 노드
• Sencondary(스탠바이 서버): Primary 노드를 실시간으로 복제하여 장애 발생 시 즉시 승격 가능

장점

• open source
• Auto FO 가능
• 수동 전환 가능
• witness server를 활용하여, 일시적인 통신 장애로 인한 FO 상황을 회피하거나 split-brain 상황을 회피할 수 있음

단점

• 장애 이후, 수동 조치 필요
• split-brain 상황이 쉽게 발생할 여지가 있음
• FO 로직이 다른 솔루션에 비해 단순

3. Patroni

Patroni Docs Patroni는 PostgreSQL 데이터베이스에서 고가용성을 구현하기 위한 Python 기반 소프트웨어 템플릿이다. 템플릿 이기에, 다양한 환경에서 유연한 설정이 가능하며 복잡한 HA 시나리오를 지원한다. 완전한 HA 클러스터 구현을 위해서는 etcd,Consul,Zookeeper와 함께 사용이 필요하다.

구성요소

• patroni: HA cluster template
• DCS(Distributed Configuration Store): 클러스터의 상태를 저장하고 장애 감지 기능을 수행할 수 있는 분산 저장소(ex. etcd)
• load balancer:App에서 단일 엔드포인트를 통해 DB connection을 유지하도록 도와줌(ex. HAProxy)
• Postgres:Patroni를 사용하여 관리되는 PosgreSQL 인스턴스

Patroni 아키텍처 High-availability (HA) PostgreSQL Cluster with Patroni

장점

• REST API 제공
• 다양한 기능을 갖추고 있으며, 유연하게 적용 가능하다
• 다양한 고가용성 테스트 시나리오에서 매우 잘 수행된다

단점

• 비교적 구성 난이도가 높다
• 별도 솔루션과 함께 써야, HA 구성이 완벽해진다
• 별도 솔루션에 대한 대처 능력이 필요하다

PostgreSQL은 write 작업에 대해 어떻게 내부적으로 처리할까? WAL 파일은 무엇이고, 언제 데이터 파일로 반영될까?

위와 같은 궁금즘에서 시작된 질문에 대한 답을 풀어나가기 위해 PostgreSQL의 내부 동작을 단계적으로 살펴보았다.

1. Write 작업의 기본 흐름

1. 클라이언트의 데이터 변경 요청
   • INSERT, UPDATE, DELETE 등 데이터 변경 쿼리를 실행하면 PostgreSQL은 데이터를 처리할 준비를 합니다.

2. 데이터가 shared_buffer에 기록

• PostgreSQL은 데이터를 바로 디스크에 쓰지 않고 shared_buffer(메모리 공간)에 우선 기록합니다. 이는 디스크 I/O를 줄이고 성능을 향상시키기 위한 메커니즘입니다.

3. WAL에 변경 내용 기록

• 변경된 데이터는 shared_buffer에 기록되는 동시에 WAL(Write-Ahead Log)에도 기록됩니다.

• WAL은 트랜잭션의 변경 내용을 순차적으로 저장하여 장애 발생 시 복구를 가능하게 합니다.

4. 트랜잭션 Commit

• 클라이언트가 COMMIT을 실행하면 PostgreSQL은 WAL에 기록된 내용을 디스크에 플러시(flush)하여 트랜잭션이 안전하게 완료되었음을 보장합니다.

5. 데이터 파일 반영 (Checkpoint)

• PostgreSQL은 특정 시점에 Checkpoint를 실행하여 shared_buffer의 내용을 실제 디스크의 데이터 파일에 기록합니다.

[postgres@DESKTOP-OUB301V ~]$ aws s3 ls --profile iceberg_test s3://test-iceberg-parquet/iceberg-warehouse/default/sample_table/data/
2025-01-07 19:03:05        916 00000-0-121ba239-b336-4375-9b16-1a0d8030a8fe-0-00001.parquet
2025-01-07 19:22:36        909 00000-0-c67ceb11-8b9b-4100-8e3e-ad8db6d40cd6-0-00001.parquet
2025-01-07 19:03:05        923 00001-1-121ba239-b336-4375-9b16-1a0d8030a8fe-0-00001.parquet
2025-01-07 19:22:36        902 00001-1-c67ceb11-8b9b-4100-8e3e-ad8db6d40cd6-0-00001.parquet

2. Foreign wrapper & server 생성

-- CREATE WRAPPER
s3test=#  CREATE FOREIGN DATA WRAPPER iceberg_wrapper
HANDLER iceberg_fdw_handler
VALIDATOR iceberg_fdw_validator;
CREATE FOREIGN DATA WRAPPER

-- CREATE SERVER
s3test=# CREATE SERVER iceberg_server
FOREIGN DATA WRAPPER iceberg_wrapper;
CREATE SERVER

3. user mapping

s3test=# CREATE USER MAPPING FOR current_user
SERVER iceberg_server
OPTIONS (
  type 'S3',
  key_id '****', -- AWS Access Key ID
  secret '****', -- AWS Secret Access Key
  region 'ap-northeast-2'
);

4. Foreign table 생성

-- CREATE FOREIGN TABLE
s3test=# CREATE FOREIGN TABLE iceberg_test ()
SERVER iceberg_server
OPTIONS (files 's3://test-iceberg-parquet/iceberg-warehouse/default/sample_table');

-- SELECT FOREIGN TABLE
s3test=# select * from iceberg_test;
 id |  name   | timestamp
----+---------+------------
  4 | Dave    | 2023-01-04
  5 | Eve     | 2023-01-05
  1 | Alice   | 2023-01-01
  2 | Bob     | 2023-01-02
  3 | Charlie | 2023-01-03

 -- CTAS
 s3test=# CREATE TABLE icebergtb as select * From iceberg_test ;

s3test=# select * from icebergtb;
 id |  name   | timestamp
----+---------+------------
  4 | Dave    | 2023-01-04
  5 | Eve     | 2023-01-05
  1 | Alice   | 2023-01-01
  2 | Bob     | 2023-01-02
  3 | Charlie | 2023-01-03
(5 rows)

chatgpt 답변 기반으로, 데이터 저장소 분류에 따른 비교

🔹 1. 데이터 레이크 (Data Lake)

🔸 개념 비정형, 반정형, 정형 데이터를 모두 저장하는 대규모 저장소야. 데이터를 가공하지 않은 원본 상태(raw data)로 저장해. 마치 호수(lake)처럼 다양한 형태의 데이터가 섞여 있는 구조라고 생각하면 돼. S3, HDFS, Azure Data Lake 같은 기술이 사용돼. 🔸 특징 확장성: 대용량 데이터를 저렴한 비용으로 저장할 수 있어. 유연성: 스키마 없이 데이터를 저장하고, 나중에 필요할 때 처리해. (Schema-on-read 방식) 다양한 데이터: 로그 데이터, 이미지, 동영상, JSON 등 형태에 구애받지 않고 저장할 수 있어. 분산 처리: Spark, Hadoop 같은 분산 처리 시스템과 연동돼. 🔸 단점 데이터 혼잡성: 데이터가 가공되지 않고 쌓이기 때문에 데이터 늪(Data Swamp)이 될 위험이 있어. 느린 쿼리: 전체 데이터를 스캔해야 해서 분석 속도가 느릴 수 있어. 데이터 품질 관리 어려움: 원시 데이터가 많아서 일관성이 부족할 수 있어. 🔸 사용 사례 대규모 로그 데이터 수집, IoT 데이터 저장, 비디오/이미지 분석 등. Netflix, Uber, AWS 등에서 데이터 레이크를 운영해.

🔹 2. 데이터 웨어하우스 (Data Warehouse)

🔸 개념 구조화된 데이터를 저장하는 분석용 데이터 저장소야. 데이터는 가공 및 정제된 후 저장돼. 기존 데이터베이스와 비슷하지만, 대규모 분석 쿼리에 특화되어 있어. Snowflake, Redshift, BigQuery, Teradata 등이 대표적인 기술이야. 🔸 특징 스키마 고정: 데이터가 들어오기 전에 스키마가 정해져 있어. (Schema-on-write 방식) 빠른 쿼리 속도: 분석용으로 최적화돼 있어서 쿼리 속도가 빠르다. 정제된 데이터: 데이터가 정리되어 있어서 신뢰도가 높아. 비즈니스 분석 최적화: OLAP(Online Analytical Processing)에 특화되어 있어. 🔸 단점 비용이 비쌈: 대규모 데이터 저장 및 처리 비용이 크다. 유연성 부족: 새로운 데이터 유형을 다루기 어렵고, 스키마 변경이 제한적이야. 비정형 데이터 처리 어려움: 이미지, 영상 같은 비정형 데이터는 처리하기 힘들어. 🔸 사용 사례 비즈니스 인텔리전스(BI), 재무 분석, 리포팅, KPI 모니터링 등. 은행, 보험사, 대기업에서 많이 사용해.

🔹 3. 데이터 마트 (Data Mart)

🔸 개념 데이터 웨어하우스에서 특정 부서나 프로젝트에서 사용할 작은 단위의 데이터 저장소야. 데이터 웨어하우스가 전체 데이터를 저장한다면, 데이터 마트는 특정 주제에 맞게 가공된 데이터를 저장해. 🔸 특징 속도와 접근성: 특정 부서에서 필요한 데이터만 저장하니까 쿼리 속도가 빠르고 접근이 쉬워. 비용 절감: 전체 웨어하우스를 조회하는 것보다 비용이 적게 든다. 간편성: 특정 비즈니스 목표에 맞게 데이터를 쉽게 추출할 수 있어. 🔸 단점 데이터 일관성 문제: 각 부서별로 데이터 마트를 운영하다 보면 중복 데이터가 생길 수 있어. 범위 제한: 특정 부서나 프로젝트 중심으로 데이터를 저장하기 때문에 전체 데이터 흐름 파악이 어렵다. 🔸 사용 사례 마케팅, 영업, HR 등 부서별로 데이터 마트를 운영해. 예를 들어, 마케팅 부서에서는 고객 행동 데이터를 저장하고, 영업 부서는 판매 데이터를 따로 관리해.

🔹 차이점 요약

🔹 언제 어떤 걸 써야 할까?

데이터 레이크: 비정형 데이터를 많이 다루고, 머신러닝이나 대규모 데이터 수집이 필요한 경우. 데이터 웨어하우스: 비즈니스 인텔리전스(BI), 재무 분석, 리포팅 등 정형 데이터 중심의 분석이 필요한 경우. 데이터 마트: 특정 부서나 프로젝트에서 빠르게 데이터를 추출하고 분석하고 싶을 때.

1. pg_analytics 개요

pg_analytics는 PostgreSQL 확장으로, DuckDB의 강력한 OLAP(Online Analytical Processing) 기능을 PostgreSQL에 추가하는 방식이다. 이를 통해 PostgreSQL에서 Parquet, CSV, JSON 파일을 직접 읽고 분석할 수 있다. 특히, pg_analytics는 duckdb를 이용하여 S3 버킷에 저장된 데이터에 직접 접근하여 PostgreSQL에서 외부 테이블로 불러오는 기능을 제공한다.

pg_analytics : https://github.com/paradedb/pg_analytics duckdb : https://duckdb.org/docs/data/overview

2. pg_analytics 설치

OS : Rocky linux9 on wsl DB : PostgreSQL 16.1.2

2.1 rpm file download

• 운영 환경에 맞는 버젼으로 다운로드
• https://github.com/paradedb/pg_analytics/releases/tag/v0.2.4

2.2 rpm install

[root@DESKTOP-OUB301V postgresql]# dnf install ./pg_analytics_16-0.2.4-1PARADEDB.el9.x86_64.rpm

2.3 shared_preload_libraries 추가

[postgres@DESKTOP-OUB301V ~]$ cat /var/lib/pgsql/16/data/postgresql.conf  |grep 'shared_preload_libraries'
shared_preload_libraries = 'pg_analytics'       # (change requires restart)

[postgres@DESKTOP-OUB301V ~]$ sudo systemctl restart postgresql-16

2.4 PostgreSQL에서 extension 활성화

• TESTDB 생성 후 extension 활성화

  postgres=# create database s3test;
  CREATE DATABASE
  postgres=# \c s3test
  You are now connected to database "s3test" as user "postgres".
  s3test=# create extension pg_analytics ;
  CREATE EXTENSION

3. S3에 저장된 parquet 파일 읽기 테스트

• github 페이지내 절차 참고

3.1 wrapper & server 설정

-- S3 parquet test 
CREATE FOREIGN DATA WRAPPER parquet_wrapper HANDLER parquet_fdw_handler VALIDATOR parquet_fdw_validator;

-- Provide S3 credentials
CREATE SERVER parquet_server FOREIGN DATA WRAPPER parquet_wrapper;

3.2 foreign table 생성

• 해당 파일은 public access가 허용되어 있어 바로 접근 가능

-- Create foreign table with auto schema creation
CREATE FOREIGN TABLE trips ()
SERVER parquet_server
OPTIONS (files 's3://paradedb-benchmarks/yellow_tripdata_2024-01.parquet');

3.3 테이블 조회

• 건수가 적은편은 아니지만, 조회 성능이 좋지는 않은듯

s3test=# \d trips
                                    Foreign table "public.trips"
        Column         |            Type             | Collation | Nullable | Default | FDW options
-----------------------+-----------------------------+-----------+----------+---------+-------------
 vendorid              | integer                     |           |          |         |
 tpep_pickup_datetime  | timestamp without time zone |           |          |         |
 tpep_dropoff_datetime | timestamp without time zone |           |          |         |
 passenger_count       | bigint                      |           |          |         |
 trip_distance         | double precision            |           |          |         |
 ratecodeid            | bigint                      |           |          |         |
 store_and_fwd_flag    | character varying           |           |          |         |
 pulocationid          | integer                     |           |          |         |
 dolocationid          | integer                     |           |          |         |
 payment_type          | bigint                      |           |          |         |
 fare_amount           | double precision            |           |          |         |
 extra                 | double precision            |           |          |         |
 mta_tax               | double precision            |           |          |         |
 tip_amount            | double precision            |           |          |         |
 tolls_amount          | double precision            |           |          |         |
 improvement_surcharge | double precision            |           |          |         |
 total_amount          | double precision            |           |          |         |
 congestion_surcharge  | double precision            |           |          |         |
 airport_fee           | double precision            |           |          |         |
Server: parquet_server
FDW options: (files 's3://paradedb-benchmarks/yellow_tripdata_2024-01.parquet')

-- Success! Now you can query the remote Parquet file like a regular Postgres table
SELECT COUNT(*) FROM trips;
  count
---------
 2964624
(1 row)

-- 조회 성능 확인
s3test=# SELECT count(*) FROM trips;
  count
---------
 2964624
(1 row)

Time: 2942.286 ms (00:02.942)

s3test=# SELECT * FROM trips limit 1;
 vendorid | tpep_pickup_datetime | tpep_dropoff_datetime | passenger_count | trip_distance | ratecodeid | store_and_fwd_flag | pulocationid | dolocationid |
 payment_type | fare_amount | extra | mta_tax | tip_amount | tolls_amount | improvement_surcharge | total_amount | congestion_surcharge | airport_fee
----------+----------------------+-----------------------+-----------------+---------------+------------+--------------------+--------------+--------------+
--------------+-------------+-------+---------+------------+--------------+-----------------------+--------------+----------------------+-------------
        2 | 2024-01-01 00:57:55  | 2024-01-01 01:17:43   |               1 |          1.72 |          1 | N                  |          186 |           79 |
            2 |        17.7 |     1 |     0.5 |          0 |            0 |                     1 |         22.7 |                  2.5 |           0
(1 row)

Time: 53570.829 ms (00:53.571)

3.4 CTAS

s3test=# create table ctas_trips as select * from trips;
SELECT 2964624
Time: 33662.761 ms (00:33.663)

s3test=# select count(*) from ctas_trips ;
  count
---------
 2964624
(1 row)

Time: 447.115 ms

4. S3에 저장된 csv 파일 읽기 테스트

4.1 sample scv 생성

s3test=# create table csvtest (name text, num int);
CREATE TABLE
s3test=# insert into csvtest values ('jkjung',1);
INSERT 0 1
s3test=# insert into csvtest values ('kilee',2);
INSERT 0 1
s3test=# insert into csvtest values ('hmson',3);
INSERT 0 1
s3test=# copy csvtest to '/home/postgres/csvtest.csv';
COPY 3

4.2 s3 upload

[postgres@DESKTOP-OUB301V ~]$ aws s3 cp csvtest.csv s3://test-parquet-jkjung
[postgres@DESKTOP-OUB301V ~]$ aws s3 ls s3://test-parquet-jkjung
2025-01-06 21:24:55         25 csvtest.csv

4.3 wrapper & server 생성

CREATE FOREIGN DATA WRAPPER csv_wrapper
HANDLER csv_fdw_handler
VALIDATOR csv_fdw_validator;

CREATE SERVER csv_server
FOREIGN DATA WRAPPER csv_wrapper;

4.4 user mapping

• 내 S3는 public access를 차단했기에, user mapping을 통해 등록된 서버에 AWS IAM 키와 리전 정보를 매핑해주어야 함.

  CREATE USER MAPPING FOR postgres
  SERVER csv_server
  OPTIONS (
  type 'S3',
  key_id '****',
  secret '****',
  region 'ap-northeast-2'
  );

  4.5 foreign table 생성

  CREATE FOREIGN TABLE s3csvtest ()
  SERVER csv_server
  OPTIONS (files 's3://test-parquet-jkjung/csvtest.csv');

4.6 테이블 조회

s3test=# \d s3csvtest
                      Foreign table "public.s3csvtest"
 Column  |       Type        | Collation | Nullable | Default | FDW options
---------+-------------------+-----------+----------+---------+-------------
 column0 | character varying |           |          |         |
 column1 | bigint            |           |          |         |
Server: csv_server
FDW options: (files 's3://test-parquet-jkjung/csvtest.csv')

s3test=# select * from s3csvtest ;
 column0 | column1
---------+---------
 jkjung  |       1
 kilee   |       2
 hmson   |       3
(3 rows)

[wsl에서 버킷 업로드 테스트]

1. awscli 설치

[~]#  dnf install awscli

2. aws s3 연결

[postgres@DESKTOP-OUB301V ~]$ aws configure
AWS Access Key ID [None]: ****
AWS Secret Access Key [None]: ****
Default region name [None]: ap-northeast-2
Default output format [None]: text

[postgres@DESKTOP-OUB301V ~]$ aws s3 ls
2025-01-05 22:05:41 test-parquet-jkjung

3. upload test

[postgres@DESKTOP-OUB301V ~]$ echo "Hello, S3 from wsl!" > testfile.txt
 [postgres@DESKTOP-OUB301V ~]$ aws s3 cp testfile.txt s3://test-parquet-jkjung
 upload: ./testfile.txt to s3://test-parquet-jkjung/testfile.txt

• aws 콘솔에서 확인

4. download test

 [postgres@DESKTOP-OUB301V ~]$ aws s3 cp s3://test-parquet-jkjung/testfile.txt ./download_testfile.txt
 download: s3://test-parquet-jkjung/testfile.txt to ./download_testfile.txt
 [postgres@DESKTOP-OUB301V ~]$ cat download_testfile.txt
 Hello, S3 from wsl!

5. delete test

 [postgres@DESKTOP-OUB301V ~]$ aws s3 rm s3://test-parquet-jkjung/testfile.tx
t
delete: s3://test-parquet-jkjung/testfile.txt

[postgres@DESKTOP-OUB301V ~]$ aws s3 ls s3://test-parquet-jkjung
[postgres@DESKTOP-OUB301V ~]$

OS : Rocky linux 9.5 on wsl DB : PostgreSQL 16.1.2

1. postgres user 생성

useradd -G wheel postgres
[postgres@DESKTOP-OUB301V ~]$ sudo hostname
DESKTOP-OUB301V

2. 설치 파일 준비

https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-9-x86_64/

• wsl 경로로 파일 준비

  [root@DESKTOP-OUB301V postgresql]# cd /mnt/c/WSL/postgresql
  [root@DESKTOP-OUB301V postgresql]# ls
  postgresql16-16.1-2PGDG.rhel9.x86_64.rpm
  postgresql16-server-16.1-2PGDG.rhel9.x86_64.rpm
  postgresql16-contrib-16.1-2PGDG.rhel9.x86_64.rpm
  postgresql16-devel-16.1-2PGDG.rhel9.x86_64.rpm
  postgresql16-docs-16.1-2PGDG.rhel9.x86_64.rpm
  postgresql16-libs-16.1-2PGDG.rhel9.x86_64.rpm

  3. PostgreSQL16 install

  설치 순서: libs → postgresql → server → contrib → devel → docs
• devel의 경우 'perl-IPC-Run' 의존성 오류가 발생할 수 있음.
• 그럴 경우 'CRB' repo를 활성화 한후 설치 진행.
• repo 활성화가 불가한 폐쇄망일 경우 힘들게 구해야함 ``` dnf install ./postgresql16-libs-16.1-2PGDG.rhel9.x86_64.rpm dnf install ./postgresql16-16.1-2PGDG.rhel9.x86_64.rpm dnf install ./postgresql16-server-16.1-2PGDG.rhel9.x86_64.rpm dnf install ./postgresql16-contrib-16.1-2PGDG.rhel9.x86_64.rpm dnf install postgresql16-devel-16.1-2PGDG.rhel9.x86_64.rpm
• • dependency dnf config-manager --set-enabled crb [root@DESKTOP-OUB301V data]# dnf repolist |grep CRB crb Rocky Linux 9 - CRB

dnf install perl-IPC-Run dnf install ./postgresql16-devel-16.1-2PGDG.rhel9.x86_64.rpm

### 4. PostgreSQL initdb
#### 4.1 PostgreSQL DB 초기화
 /usr/pgsql-16/bin/postgresql-16-setup initdb
 * 오류 발생

System has not been booted with systemd as init system (PID 1). Can't operate. Failed to connect to bus: Host is down failed to find PGDATA setting in postgresql-16.service

-> WSL은 systemd 대신 init 시스템을 사용해 부팅하기 때문에, systemctl 명령어가 제대로 작동 x
https://learn.microsoft.com/ko-kr/windows/wsl/systemd
- 해결방법
WSL에서 systemd를 활성화 한 후, OS reboot

WSL에서 systemd 활성화

vi /etc/wsl.conf [boot] systemd=true

wsl 재시작

PS C:\Users> wsl --shutdown PS C:\Users> wsl -l -v NAME STATE VERSION

• Ubuntu Stopped 2 Rocky9 Stopped 2 PS C:\Users> wsl -d Rocky9

initdb

[root@DESKTOP-OUB301V Users]# /usr/pgsql-16/bin/postgresql-16-setup initdb Initializing database ... OK

### 3. PostgreSQL DB 활성화

[postgres@DESKTOP-OUB301V ~]$ sudo systemctl status postgresql-16 ○ postgresql-16.service - PostgreSQL 16 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-16.service; disable> Active: inactive (dead) Docs: https://www.postgresql.org/docs/16/static/

[postgres@DESKTOP-OUB301V 16]$ sudo systemctl start postgresql-16

### 4. 접속 확인

[postgres@DESKTOP-OUB301V 16]$ psql -c 'select version();' version

PostgreSQL 16.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 202306 05 (Red Hat 11.4.1-2), 64-bit (1 row)

````

• 호환 가능한 rocky9으로 설치 참고 : https://m.blog.naver.com/islove8587/223435396324

1. rocky9.tar 준비

1.1 설치 파일 준비 https://docs.rockylinux.org/guides/interoperability/import_rocky_to_wsl/

1.2 7zip으로 압축풀기 tar.xz -> tar

2. wsl setting

2.1. WSL 설치 및 설정 PowerShell 관리자 권한으로 실행

 wsl --install or
 wsl --update

2.2 WSL version 변경

 wsl --set-default-version 2

2.3 wsl list 확인

 wsl --list --verbose

3. Rocky linux 이미지 WSL에 등록

3.1. 이미지 다운로드 경로로 이동

PS C:\WINDOWS\system32> cd C:\Users\jjk\Downloads\rocky_linux9
PS C:\Users\jjk\Downloads\rocky_linux9> ls

    디렉터리: C:\Users\jjk\Downloads\rocky_linux9


Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a----      2025-01-05   오후 4:40      240936960 Rocky-9-Container-Base.latest.x86_64.tar
-a----      2025-01-05   오후 4:40       52991368 Rocky-9-Container-Base.latest.x86_64.xz

3.2 이미지 설치 wsl --import $설치경로 $설치파일

PS C:\Users\jjk\Downloads\rocky_linux9> wsl --import Rocky9 C:\WSL\Rocky9 C:\Users\jjk\Downloads\rocky_linux9\Rocky-9-Container-Base.latest.x86_64.tar
가져오기가 진행 중입니다. 이 작업은 몇 분 정도 걸릴 수 있습니다.
작업을 완료했습니다.

PS C:\Users\jjk\Downloads\rocky_linux9> wsl --list -v
  NAME      STATE           VERSION
* Ubuntu    Stopped         2
  Rocky9    Stopped         2

4. rocky9 실행

PS C:\Users> wsl -d Rocky9
[root@DESKTOP-OUB301V Users]#

(1) NVL 함수 NVL은 null 값을 다른 값으로 대체할 때 주로 활용된다. -실제 수행 예시

-- 테이블 생성
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(50),
    salary NUMBER
);

-- 데이터 insert
INSERT INTO employees VALUES (1, 'John Doe', 50000);
INSERT INTO employees VALUES (2, 'Jane Smith', NULL);
INSERT INTO employees VALUES (3, 'Bob Johnson', 60000);

-- NVL 함수 활용
SELECT employee_id, employee_name, NVL(salary, 0) AS modified_salary
FROM employees;

| EMPLOYEE_ID | EMPLOYEE_NAME | MODIFIED_SALARY |
|-------------|---------------|------------------|
| 1           | John Doe      | 50000            |
| 2           | Jane Smith    | 0                |
| 3           | Bob Johnson   | 60000            |

(2) 다른 DBMS에서 NVL 사용 여러 DBMS는 각자의 방식으로 NULL 대체를 위한 함수를 제공한다. 때문에, 본인이 사용하는 DBMS에 맞춰 적절한 용법으로 NULL 값을 대체해야 한다.

• SQL SERVER

  SELECT ISNULL(column_name, replacement_value) FROM table_name;
  

- MYSQL, Postgresql

SELECT COALESCE(column_name, replacement_value) FROM table_name;

(3) postgresql 에서 NVL 사용하기
다양한 DBMS를 접해본 사용자나, DBMS에 대해 어느정도 지식이 있는 사람들은 위와 같이 DBMS의 차이를 인지하고 적절한 함수를 대체하여 사용한다.
그러나, ORACLE만을 사용해본 사용자 혹은, Migration을 통해 어쩔 수 없이 타 DBMS로 넘어온 시스템의 경우 즉시 대체함수를 사용하기는 어려울 수 있다.
유능한 DBA라면 이러한 상황까지 고려해, 또 한가지의 대책안을 제시할 수 있어야 있다고 생각한다.

ex) pg_ctl stop -D /data/primary/gpseg4 -m fast

pg_ctl start 통해 DB 기동하나, mirror로 role이 변경된 경우 gprecoverseg 통하여 복구 필요