[막힘없이 postgresql] 내용 정리(mysql과 비교하며)

1. PostgreSQL 소개 

PostgreSQL은 강력한 오픈소스 객체-관계형 데이터베이스 시스템입니다. MySQL이 주로 관계형 데이터베이스(RDBMS)에 초점을 맞추는 것과 달리, PostgreSQL은 객체 지향 및 관계형 기능을 모두 지원하여 더 넓은 범주의 데이터 타입을 처리할 수 있는 유연성을 제공합니다.

2. PostgreSQL 아키텍처

2.1 주요 프로세스

MySQL이 mysqld 단일 프로세스 아래 여러 스레드로 동작하는 것과 유사하게, PostgreSQL도 Postmaster 프로세스를 중심으로 여러 백그라운드 프로세스와 클라이언트 연결을 처리하는 프로세스로 구성됩니다.

• Postmaster 프로세스: MySQL의 mysqld와 같이, PostgreSQL 인스턴스가 시작될 때 가장 먼저 기동되는 메인 프로세스입니다. 여러 백그라운드 프로세스와 클라이언트의 요청을 처리하는 Backend 프로세스를 생성하고 관리합니다.
• Backend 프로세스: MySQL에서 클라이언트 연결마다 생성되는 스레드(또는 프로세스)와 유사하게, Postmaster에 의해 생성되며 클라이언트와 1:1 관계를 유지합니다. 클라이언트가 요청한 쿼리를 수행하고 결과를 전송하는 역할을 합니다. 기본적으로 동시 접속 가능한 Backend 프로세스 수는 100개이며, max_connections 파라미터로 변경할 수 있습니다.
• Background 프로세스: MySQL의 InnoDB Purge, Buffer Pool I/O Thread, Redo Log Writer 등과 유사하게, PostgreSQL 서버의 안정적인 운영을 위한 다양한 백그라운드 작업을 수행합니다.
  • 필수 프로세스: background writer (Shared Buffer의 Dirty 페이지를 디스크에 주기적으로 기록), WAL writer (WAL을 디스크에 기록).
  • 기타 프로세스: Checkpointer (특정 간격으로 모든 Dirty 페이지와 트랜잭션 로그를 디스크에 기록), Autovacuum launcher (Autovacuum 작업 수행 및 관리), Statistics collector (DB 사용 통계 수집), Archiver (WAL 파일 아카이빙), logger (오류 메시지 기록) 등이 있습니다.

💡mysql은 클라이언트 별로 스레드를 할당하는데 Postgre는 클라이언트 별로 프로세스를 할당하는 이유는? 프로세스 할당하는 데 더 비용이 크지 않나?

설계 철학의 차이

MySQL (스레드 모델):
  철학: 빠르고 가볍게, 효율적으로.
  🚀방식: 클라이언트마다 스레드를 할당합니다.
  장점: 스레드 생성 비용이 저렴해 연결 설정이 빠르고, 자원 소모가 적어 일반적인 웹 애플리케이션에 적합합니다.
  단점: 모든 스레드가 같은 프로세스 메모리를 공유하기 때문에, 하나의 스레드에 치명적인 오류가 발생하면 전체 서버가 불안정해             지거나 중단될 위험이 있습니다.

PostgreSQL (프로세스 모델):
  철학: 안정성과 신뢰성을 최우선으로.
  🛡️방식: 클라이언트마다 프로세스를 할당합니다.
  장점: 운영체제가 제공하는 강력한 프로세스 격리 기능을 활용합니다. 하나의 프로세스에 오류가 발생하더라도 다른 프로세스나              전체 서버에 영향을 주지 않아 매우 안정적입니다.
  단점: 프로세스 생성 비용이 스레드보다 비싸고 메모리 소모가 많습니다. 이 때문에 동시 접속이 매우 많은 환경에서는 연결 풀링(Connection Pooling)과 같은 기술이 필수적입니다. 

2.2 메모리 구조

MySQL의 메모리 구조와 마찬가지로, PostgreSQL도 공유 메모리와 로컬 메모리로 나눌 수 있습니다.

• 공유 메모리 (Shared Memory): MySQL의 InnoDB Buffer Pool과 유사하게, 모든 프로세스가 공유하는 영역입니다.
  • Shared Buffer: MySQL의 InnoDB Buffer Pool과 가장 유사한 개념으로, 데이터 변경 사항을 페이지 단위로 처리하고 물리적인 I/O를 줄여 데이터에 빠르게 접근하기 위한 캐시 영역입니다. MySQL의 InnoDB Buffer Pool이 시스템 메모리의 상당 부분을 할당하는 것과 달리, PostgreSQL은 Shared Buffer를 시스템 메모리의 약 25% 정도로 다소 작게 설정하도록 설계되어 있습니다. 이는 Shared Buffer와 OS 캐시를 함께 사용하여 데이터에 빠르게 접근하도록 최적화된 설계 방식입니다.
  • WAL Buffer (Write-Ahead Logging Buffer): MySQL의 Redo Log Buffer와 유사하게, 데이터 변경 사항이 디스크의 WAL 세그먼트 파일에 기록되기 전에 임시로 저장되는 영역입니다. 복구 시 데이터를 재구성하는 데 사용됩니다.
  • Clog Buffer (Commit Log Buffer): 트랜잭션의 커밋 상태(In_progress, Committed, Aborted)를 기록하는 로그 파일인 Clog의 버퍼입니다. MySQL의 트랜잭션 상태 관리와 유사하지만, 별도 파라미터 없이 데이터베이스 엔진에 의해 자동으로 관리됩니다.
  • 락 스페이스 (Lock Space): Shared Buffer에서 락 관련 내용을 보관하는 영역으로, 모든 유형의 락 정보를 저장하여 트랜잭션의 순차성과 데이터의 일관성 및 무결성을 유지합니다. MySQL도 내부적으로 다양한 락을 사용하지만, PostgreSQL은 이 공간을 명시적으로 관리합니다.
  • Other Buffers: 통계 정보, Two Phase Commit, Checkpoint, Autovacuum 등 다양한 백그라운드 프로세스를 위한 메모리 영역입니다.
• 로컬 메모리 (Local Memory): MySQL의 sort_buffer_size, join_buffer_size, tmp_table_size 등과 같이, 개별 Backend 프로세스(세션)가 쿼리를 수행하기 위해 사용하는 전용 메모리 영역입니다.
  • work_mem: Order by, Group by, DISTINCT, 조인 작업 등 정렬 및 해시 연산 시 사용되는 메모리 공간입니다. 이 공간이 부족하면 MySQL의 tmp_table_size처럼 임시 파일을 사용하게 됩니다.
  • maintenance_work_mem: VACUUM, 인덱스 생성, 테이블 변경 등 유지보수 작업에 사용되는 메모리 공간입니다. MySQL의 경우 유사한 전역 설정은 없지만, 작업 유형에 따라 내부적으로 메모리를 사용합니다.
  • temp_buffers: 세션 단위로 임시 테이블에 접근하기 위해 사용하는 메모리 공간입니다.

2.3 PostgreSQL 논리 구조

PostgreSQL의 논리 구조는 MySQL의 논리 구조와 유사하지만, 일부 개념에서 차이가 있습니다.

• 클러스터 (Cluster): PostgreSQL의 클러스터는 하나 이상의 데이터베이스, Role(사용자), 테이블스페이스의 집합을 의미하는 논리적인 개념입니다. 하나의 PostgreSQL 인스턴스는 하나의 클러스터만을 관리하며, 한 번에 여러 데이터베이스에 서비스를 제공할 수 있습니다. 이는 MySQL 서버 인스턴스가 여러 데이터베이스를 포함하는 것과 유사합니다.
• 데이터베이스 (Database): 클러스터 내의 데이터베이스는 MySQL의 데이터베이스와 동일한 개념입니다. PostgreSQL은 initdb() 수행 시 Template0, Template1, Postgres 3개의 데이터베이스를 기본 생성합니다. 특히 Template1은 새로운 데이터베이스 생성 시 템플릿으로 사용될 수 있으며, Template0은 수정 불가능한 초기 상태의 템플릿입니다.
• 스키마 (Schema): MySQL에서는 DATABASE와 SCHEMA가 동일한 개념으로 사용되지만, PostgreSQL에서는 스키마가 오브젝트(테이블, 뷰 등)의 집합을 의미하며, 하나의 데이터베이스는 다수의 스키마를 소유할 수 있습니다. 이는 Oracle의 스키마 개념과 유사하며, MySQL의 DATABASE 내 TABLE을 직접 생성하는 방식과 다릅니다. 테이블 생성 시 스키마를 지정하지 않으면 Public 스키마로 생성됩니다.
• 롤 (Role): PostgreSQL의 Role은 MySQL의 User와 유사하게, 데이터베이스 관련 권한들의 집합입니다. Login Permission을 부여하면 일반 사용자 계정으로 사용 가능하며, 개별 데이터베이스가 아닌 클러스터 레벨에서 공통으로 사용됩니다.
• 오브젝트 (Object): 테이블, 인덱스, 뷰, 시퀀스, 함수, 프로시저, 트리거 등 데이터를 저장하거나 참조하는 데 사용되는 데이터 구조를 포함합니다. MySQL의 오브젝트 개념과 유사합니다.
  • 튜플 (Tuple): 테이블의 레코드(row)가 물리적으로 저장된 형태이며, 데이터를 저장하는 최소 단위입니다. MVCC 적용에 따라 라이브 튜플과 데드 튜플이 존재할 수 있습니다. MySQL의 InnoDB 레코드와 유사하지만, 데드 튜플의 명시적 관리가 다릅니다.
  • 페이지 (Page): 디스크 I/O가 발생하는 최소 단위이며 고정 길이의 데이터 블록입니다. MySQL의 InnoDB 페이지(기본 16KB)와 유사하게, PostgreSQL은 기본 8KB 페이지 크기를 사용합니다.
• 테이블스페이스 (Tablespace): MySQL의 InnoDB 테이블스페이스(예: ibdata, .ibd 파일)와 유사하게, 오브젝트가 파일 형태로 저장되는 물리적 위치를 나타냅니다. PostgreSQL은 pg_default, pg_global 두 개의 기본 테이블스페이스와 사용자 정의 테이블스페이스를 제공합니다. 성능 최적화를 위해 특정 오브젝트를 고성능 디스크(SSD)에 배치하는 데 활용할 수 있습니다.

2.4 PostgreSQL 물리 구조

PostgreSQL의 물리 구조는 디렉토리와 파일 및 포크(Fork)로 구성됩니다. MySQL의 데이터 디렉토리(datadir)와 유사한 구조를 가집니다.

• 디렉토리 구조:
  • 데이터 디렉토리 영역: MySQL의 datadir과 유사하며, 실제 사용자 데이터가 저장되는 공간으로 Global, Base, pg_tblspc 디렉토리로 구분됩니다.
  • pg_wal: MySQL의 binlog 및 InnoDB redo log와 유사하게, 장애 발생 시 복구 역할을 수행하는 WAL 파일이 저장되는 곳입니다. Replication 구현에도 사용됩니다.
  • Log 디렉토리: MySQL의 에러 로그(error.log)와 유사하게, 데이터베이스 운영 중 발생하는 특이사항(데드 락, Vacuum 수행 결과 등)을 기록하는 공간입니다.
  • 환경설정 디렉토리: MySQL의 my.cnf와 유사하게, postgresql.conf 파일이 존재하여 데이터베이스의 환경 설정을 관리합니다.
• 포크 (Fork): PostgreSQL은 물리적 파일을 포크라고 부르며, 데이터를 여러 포크로 분할하여 관리합니다. 테이블 생성 시 일반적으로 3개의 파일(포크)이 존재합니다.
  • 메인 포크 (Main Fork): 테이블이나 인덱스의 실제 데이터가 저장되는 파일 시스템으로, MySQL의 .ibd 파일(테이블 데이터 파일)과 유사합니다. 파일 용량이 1GB까지 증가하면 새로운 세그먼트 파일이 생성됩니다.
  • 여유 공간 맵 (Free Space Map, FSM): MySQL의 InnoDB에서 페이지 내 여유 공간을 내부적으로 관리하는 것과 달리, PostgreSQL은 FSM 파일에 여유 공간 정보를 저장하여 새로운 데이터 입력 시 기존 페이지의 적절한 여유 공간을 빠르게 찾도록 돕습니다. 이는 디스크 공간 낭비를 줄이고 데이터 페이지 공간을 최적화하는 데 사용됩니다.
  • 가시성 맵 (Visibility Map, VM): 활성 트랜잭션들이 페이지 내 모든 튜플의 가시성 여부를 추적하는 데 사용됩니다. VM은 두 개의 비트 값(all_visible, all_frozen)을 가진 비트맵 배열로 구성되어, Vacuum 및 인덱스 스캔을 최적화하고, 불필요한 Vacuum 작업을 최소화하는 데 중요한 역할을 합니다. 이는 MySQL InnoDB의 MVCC에서 Read View를 통해 가시성을 판단하는 방식과 유사하지만, 물리적 파일로 가시성 정보를 관리하는 것이 특징입니다.

3. WAL (Write-Ahead Log) - 데이터 무결성 및 복구

MySQL의 redo log와 binary log의 역할을 통합한 것과 유사하게, PostgreSQL의 WAL (Write-Ahead Log)은 데이터 무결성과 일관성을 보장하는 핵심 메커니즘입니다.

• 작동 방식:
  • PostgreSQL: 데이터 변경은 먼저 Shared Buffer(메모리 캐시)에서 이루어지고, 동시에 변경 내역이 WAL 버퍼(메모리)에 기록됩니다. 이 로그는 즉시 WAL 세그먼트 파일(디스크)에 기록됩니다. 변경된 실제 데이터는 나중에 디스크에 저장됩니다. 이는 메모리 변경의 빠른 속도를 활용하면서도, 로그를 먼저 기록하여 시스템 장애 시 복구 가능성을 보장합니다.
  • MySQL: InnoDB 스토리지 엔진은 PostgreSQL의 WAL과 유사한 redo log를 사용합니다. 데이터 변경은 먼저 Buffer Pool(메모리 캐시)에서 이루어지고, 동시에 변경 내역이 redo log 버퍼(메모리)에 기록됩니다. 이 로그는 트랜잭션 커밋 시점에 디스크의 redo log 파일에 기록되어 데이터의 내구성을 보장합니다.
  두 데이터베이스 모두 메모리(버퍼)에서 먼저 데이터를 변경하고, 그 변경 내역을 디스크의 로그 파일에 먼저 기록하는 동일한 Write-Ahead Logging(WAL) 원칙에 따라 작동합니다. 이 방식은 빠른 성능과 데이터 무결성이라는 두 마리 토끼를 잡기 위한 핵심적인 설계입니다.
• 주요 장점:
  • 트랜잭션 COMMIT 시마다 변경된 데이터를 데이터 파일에 즉시 기록할 필요가 없습니다.
  • WAL에 쓰기 작업은 MySQL의 INSERT와 같이 단순 Append 형태로 순차 작성되어 쓰기 비용이 적습니다.
  • 온라인 백업(Online Backup) 및 PITR (Point-In-Time Recovery)을 지원합니다. MySQL의 바이너리 로그 기반 복구와 유사합니다.
• 체크포인트 (Checkpoint): MySQL의 checkpoint와 유사하게, 특정 시점 기준으로 데이터베이스의 모든 변경 사항이 데이터 파일에 물리적으로 디스크에 쓰인 상태를 의미합니다. 이는 시스템 안정성 유지 및 데이터 정합성 보장에 중요한 역할을 합니다. 체크포인트는 Redo 포인트를 생성하며, 이는 데이터베이스 복구 시 복구의 시작점이 됩니다.

4. MVCC (Multi Version Concurrency Control) 및 VACUUM

PostgreSQL의 MVCC 구현 방식은 MySQL의 InnoDB와 중요한 차이점을 가집니다.

4.1 MVCC (Multi Version Concurrency Control) ⭐️⭐️⭐️⭐️⭐️

• 개념: MySQL InnoDB와 마찬가지로, 다수의 동시다발적인 트랜잭션 처리에서 데이터 일관성을 보호하기 위해 다중 버전 동시성 제어 (MVCC)를 사용합니다. 트랜잭션이 시작되면 그 시점에 맞는 스냅샷 범위에 해당하는 데이터만 볼 수 있도록 허용합니다.
• MySQL InnoDB와의 차이:
  • PostgreSQL: 데이터가 변경되면 이전 버전의 튜플(레코드)을 페이지 안에 그대로 남겨두고 신규 데이터를 추가하는 방식 (MGA: Multi Generation Architecture)을 사용합니다. 이렇게 남겨진 이전 버전의 튜플을 데드 튜플 (Dead Tuple)이라고 부릅니다. 데드 튜플이 많아지면 테이블과 인덱스 크기가 증가하는 팽창 (Bloating) 현상이 발생합니다.
  • MySQL (InnoDB): 데이터 변경 시 기존 데이터를 신규 데이터로 변경하고, 이전 이미지는 Undo 세그먼트 (Undo Segment)에 저장합니다. 트랜잭션이 종료되면 Undo 공간은 재활용되므로, PostgreSQL처럼 명시적인 VACUUM 작업이 필요 없습니다.
• 트랜잭션 ID (XID): MySQL의 트랜잭션 ID와 유사하게, PostgreSQL은 각 트랜잭션에 32비트 정수 형태의 XID를 할당하여 튜플 버전 정보를 관리합니다. 튜플이 INSERT되면 xmin에 XID가, UPDATE되면 이전 버전 튜플의 xmax에 XID가 저장됩니다.
• XID 순환 구조와 Frozen XID: PostgreSQL의 32비트 XID는 약 43억 개(4,294,967,296)를 모두 소진하면 다시 처음으로 돌아가는 XID Wraparound 현상이 발생합니다. MySQL에서는 이러한 wraparound가 사용자에게 직접적인 문제로 인식되지 않도록 내부적으로 처리되지만, PostgreSQL에서는 튜플의 가시성이 훼손되는 심각한 상황을 방지하기 위해 오래된 XID를 Frozen XID (값 2)로 고정하여 Anti-Wraparound XID 과정을 통해 관리해야 합니다.

4.2 VACUUM 및 Autovacuum ⭐️⭐️

PostgreSQL의 MVCC 구현 방식(데드 튜플 유지)으로 인해, VACUUM은 MySQL에는 없는 PostgreSQL의 필수적인 유지보수 작업입니다. 한 번에 이전 값인 Frozen XID를 삭제하는 게 아니라 스케줄러를 통해 한 번에 삭제합니다.

• VACUUM의 목적: MySQL의 OPTIMIZE TABLE이 데이터 파일을 재정렬하여 공간을 확보하는 것과 다르게, PostgreSQL의 VACUUM은 다음과 같은 중요한 목적을 가집니다.
  • 데드 튜플 정리 및 공간 확보: 더 이상 참조하지 않는 오래된 데드 튜플을 제거하고, 힙 페이지의 공간 활용도를 높여 디스크 공간을 확보합니다.
  • FSM 및 VM 정보 업데이트: 정리된 페이지 정보를 Free Space Map (FSM)과 Visibility Map (VM)에 등록하여 튜플의 가시성을 보장하고 페이지 여유 공간을 확보합니다.
  • 통계 정보 업데이트: 쿼리 플래너가 참조하는 테이블 통계 정보를 업데이트합니다.
  • Frozen XID 적용: XID Wraparound를 방지하기 위해 오래된 트랜잭션 ID에 Anti-Wraparound XID (Frozen XID)를 적용합니다.
• Autovacuum: MySQL의 자동 통계 수집 및 InnoDB의 백그라운드 퍼지 스레드와 유사하게, PostgreSQL은 Autovacuum 프로세스를 통해 특정 임계치(Threshold)에 따라 VACUUM 작업을 자동으로 수행합니다. Autovacuum은 모든 테이블에 예외 없이 VACUUM이 수행되도록 하는 데 목적이 있습니다.
  • autovacuum_enabled 파라미터가 on이어야 동작하며, autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor 등의 임계값을 기준으로 VACUUM 수행 여부를 결정합니다.
  • XID Wraparound를 막기 위한 최후의 마지노선으로 autovacuum_freeze_max_age 파라미터가 존재하여, XID 수명이 오래된 테이블은 autovacuum의 on/off와 상관없이 강제로 VACUUM이 실행되도록 합니다.
• HOT (Heap Only Tuple) Update: MySQL InnoDB가 In-Place 업데이트(일부 컬럼만 변경 시)를 통해 Redo/Undo 로그를 최적화하는 것과 유사하게, PostgreSQL은 업데이트되는 컬럼이 인덱스 구성 컬럼에 포함되지 않을 경우, 인덱스 포인터 생성을 피하고 힙 페이지 내에서만 튜플 변경을 처리하는 HOT 업데이트 최적화 기능을 제공합니다. 이는 테이블과 인덱스의 팽창을 예방하고 VACUUM 부담을 줄이는 데 효과적입니다. Fillfactor 설정으로 페이지 내에 여유 공간을 설조절하여 HOT 업데이트 가능성을 높일 수 있습니다. 

이해를 돕기 위한 예시


일반적인 업데이트 (No HOT Update)
만약 인덱스에 포함된 컬럼을 업데이트하면, PostgreSQL은 HOT 업데이트를 사용할 수 없습니다.

- 인덱스 컬럼을 업데이트 UPDATE users SET id = 101 WHERE id = 100;

이 경우, PostgreSQL은 기존 튜플을 삭제된 상태로 표시하고, 새로운 튜플을 다른 위치에 생성합니다. 그리고 id 인덱스도 새로운 위치를 가리키도록 업데이트해야 합니다. 이 과정에서 테이블과 인덱스 모두 불필요하게 커지는 문제(bloat)가 발생하고, VACUUM이 해야 할 일이 많아집니다.



HOT 업데이트 (Heap Only Tuple Update)
반면, 인덱스에 포함되지 않은 컬럼을 업데이트하면 HOT 업데이트가 가능합니다.

- 인덱스에 포함되지 않은 컬럼을 업데이트 UPDATE users SET login_count = 101 WHERE id = 100;

PostgreSQL은 login_count가 인덱스에 없음을 확인합니다. 가능하면 기존 튜플이 있는 동일한 힙 페이지에 새로운 튜플을 만듭니다. 이때, 튜플의 물리적 위치가 변하지 않았으므로 인덱스를 업데이트할 필요가 없습니다. 기존의 id 인덱스 포인터는 그대로 유효합니다. 결과적으로, HOT 업데이트는 인덱스가 불필요하게 커지는 것을 막아 인덱스 팽창(bloat)을 방지하고 VACUUM의 부담을 크게 줄여 성능을 향상시킵니다.

 

인덱스가 아닌 컬럼을 업데이트할 때 MySQL처럼 그 컬럼을 덮어쓰면 굳이 HOP update할 필요 없잖아?

postgreSQL은 MVCC(Multi-Version Concurrency Control)를 구현하는 방식 때문에 인덱스가 아닌 컬럼을 업데이트하더라도 덮어쓰기를 하지 못합니다.

MVCC와 튜플 버전 관리

PostgreSQL의 MVCC는 모든 데이터 변경마다 새로운 버전의 행을 만드는 방식으로 작동합니다.

• 동시성 보장: UPDATE가 발생하면, 새로운 트랜잭션 ID(XID)를 가진 새 튜플을 생성합니다. 기존 튜플은 '데드 튜플'로 남겨두는데, 이는 업데이트가 시작될 때 데이터를 읽기 시작한 다른 동시 트랜잭션들이 여전히 원래의 튜플을 볼 수 있도록 하기 위함입니다.
• 데이터 일관성: 만약 덮어쓰기를 한다면, 다른 트랜잭션이 읽고 있는 데이터가 갑자기 바뀌어 버리는 문제가 발생합니다. 이는 MVCC의 핵심 원칙인 읽기 일관성을 깨뜨립니다.

HOT 업데이트는 최선의 절충안

HOT 업데이트는 PostgreSQL의 MVCC 원칙을 지키면서도 성능을 최적화하기 위한 방법입니다.

1. 새로운 튜플 생성: MVCC 원칙에 따라 새로운 튜플을 만듭니다.
2. 인덱스 업데이트 회피: 하지만, 인덱스에 영향을 주지 않는 업데이트이므로, 불필요하게 인덱스를 건드리지 않고 동일한 페이지에 새 튜플을 만듭니다.

결론적으로, PostgreSQL은 덮어쓰기처럼 단순하게 처리할 수 있는 구조가 아닙니다. 이는 덮어쓰기 시 MVCC의 핵심인 읽기 일관성이 깨지기 때문입니다. HOT 업데이트는 이러한 제약을 극복하고, 새로운 튜플을 생성하면서도 인덱스 팽창과 VACUUM 부담을 최소화하는 해결책입니다.

 

5. 락 (LOCK) - 동시성 제어

MySQL이 테이블 락, 레코드 락 등 다양한 락을 제공하는 것과 유사하게, PostgreSQL도 데이터 일관성과 동시성을 보장하기 위해 다양한 락 모드를 제공합니다. PostgreSQL은 크게 객체 레벨 락 (Object Level Lock), 행 레벨 락 (Row Level Lock), 메모리 레벨 락 (Memory Level Lock) 세 가지 레벨의 락을 제공합니다.

• 객체 레벨 락 (Object Level Lock): MySQL의 테이블 락과 유사하게, 테이블, 인덱스, 시퀀스 등 객체에 대한 동시 변경을 보호하는 역할을 합니다. pg_locks 뷰를 통해 락 정보를 확인할 수 있습니다.
  • DDL (CREATE, DROP) 쿼리는 AccessExclusive 모드로 락을 획득하여 다른 세션의 접근을 차단합니다.
  • max_locks_per_transaction 파라미터는 트랜잭션당 획득할 수 있는 객체 락 수를 제한합니다. 파티션 테이블처럼 많은 객체에 락이 필요할 경우 MySQL과 마찬가지로 이 값을 늘려야 할 수 있습니다.
• 행 레벨 락 (Row Level Lock): MySQL의 InnoDB 레코드 락과 유사하게, 튜플(행) 수준에서 락을 구현합니다.
  • 구현 방식: 튜플의 xmin, xmax 플래그 정보를 사용하거나 SELECT ... FOR UPDATE와 같은 쿼리를 통해 명시적으로 락을 적용합니다.
  • 특징: 락 정보가 공유 메모리(lock space)가 아닌 페이지 내부의 튜플 버전에 저장되므로 락 개수에 거의 제한이 없습니다. MySQL처럼 락 대기 프로세스 정보를 바로 확인할 수 없지만, pg_locks 뷰를 통해 확인 가능합니다.
  • SELECT ... FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE 등 다양한 모드를 제공합니다.
  • 다중 트랜잭션 (Multitransactions): 공유 락 모드(FOR SHARE 등)는 MySQL과 유사하게 여러 트랜잭션이 동일 튜플에 대해 동시에 락을 소유할 수 있도록 허용합니다. 이때 Multixact ID를 사용하여 여러 락 트랜잭션 ID를 저장합니다.
• 메모리 레벨 락 (Memory Level Lock): MySQL의 래치(Latch)와 유사하게, PostgreSQL 내부 공유 메모리 구조, 데이터 버퍼 및 기타 내부 리소스에 대한 접근을 관리하는 내부 메커니즘입니다. 주로 PostgreSQL 서버 프로세스 내에서 관리되며, 최종 사용자가 직접 상호작용하는 경우는 드뭅니다.
  • Light-Weight Lock (LWLocks): 버퍼, 트랜잭션 테이블, 해시 테이블과 같은 공유 데이터 구조에 대한 접근을 동기화하는 데 사용됩니다.
  • Spinlocks: 매우 짧은 시간 동안 획득되는 가볍고 저렴한 락으로, MySQL의 스핀락과 유사하게 컨텍스트 스위칭 없이 루프를 회전하며 락 획득을 재시도합니다.
  • Buffer Pin Lock: 여러 프로세스가 동일 버퍼에 동시에 접근할 때, 버퍼 변경 작업의 일관성을 보장하기 위해 획득하는 락입니다.
• Dead Lock (교착 상태): MySQL과 마찬가지로, 두 개 이상의 트랜잭션이 서로 락을 홀더한 자원을 기다리면서 무한 대기 상태에 빠지는 현상입니다. PostgreSQL은 deadlock_timeout 파라미터를 통해 데드 락 상황을 주기적으로 체크하고, 감지 시 원인 트랜잭션 중 하나를 롤백 처리합니다.

6. SQL 실행 - 옵티마이저 및 실행 계획

SQL 성능 최적화는 모든 데이터베이스에서 중요하며, PostgreSQL 또한 MySQL과 유사하게 비용 기반 옵티마이저 (CBO: Cost Based Optimizer)를 사용합니다.

• PostgreSQL vs. MySQL SQL 처리 프로세스:
  • MySQL: 쿼리 캐시나 내부적인 준비된 문장 캐시(Prepared Statement)등을 활용하여 동일 SQL의 파싱 부담을 줄일 수 있습니다. MySQL 8.0 이후로는 쿼리 캐시가 제거되었지만, 준비된 문장 캐시를 효율적으로 사용하여 반복적인 쿼리 성능을 향상시킵니다.
  • PostgreSQL: MySQL의 쿼리 캐시처럼 쿼리 결과를 공유하는 전역 캐시는 존재하지 않습니다. 하지만, 준비된 문장 캐시를 통해 동일 SQL 문장을 여러 번 실행할 때 반복적인 파싱 및 계획 수립 과정을 피하고 이전에 생성된 실행 계획을 재사용할 수 있습니다. 대부분의 최신 애플리케이션 드라이버는 내부적으로 준비된 문장을 사용하므로, 반복적인 쿼리는 파싱 부담이 거의 없습니다.
• PREPARE STATEMENT: MySQL의 Prepared Statement와 유사하며, custom 모드(파라미터 값에 따라 최적화)와 generic 모드(생성된 실행 계획 재사용)를 제공합니다. PostgreSQL의 아키텍처는 클라이언트 연결마다 별도의 백엔드 프로세스를 할당하므로, Prepared Statement는 해당 프로세스 내에서만 유효하게 캐시됩니다. 이러한 설계는 안정성을 극대화하는 동시에, 전역 캐시의 부재로 인한 성능 저하를 보완하는 중요한 역할을 합니다.
• 실행 계획 : EXPLAIN [ANALYZE] <SQL> 명령어를 통해 실행 계획을 추출합니다. MySQL의 EXPLAIN과 유사하며, ANALYZE 옵션은 실제 SQL을 수행하고 성능 데이터를 실행 계획에 표시하여 정확한 분석에 도움을 줍니다.
  • Buffers: MySQL의 Handler_read_key, Handler_read_next 등과 유사하게, Buffers 섹션은 쿼리 노드를 수행하는 데 스캔한 블록 수(Shared hit, Shared Reads, Temp Read 등)를 보여주어 I/O 발생 여부를 파악하는 데 유용합니다.
• 통계 정보: MySQL의 ANALYZE TABLE과 유사하게, PostgreSQL도 ANALYZE 명령어를 통해 데이터베이스, 테이블, 컬럼 단위로 통계 정보를 수집하고 pg_statistic, pg_class 등의 시스템 카탈로그에 저장합니다. 이 정보는 CBO가 최적의 실행 계획을 수립하는 데 활용됩니다.
• 스캔 방법 : MySQL과 유사하게 Sequential Scan (Table Full Scan), Index Scan, Index Only Scan, Bitmap Index Scan 등을 제공합니다.
  • Index Only Scan: MySQL의 Using index (커버링 인덱스)와 유사하게, 테이블 블록에 접근하지 않고 인덱스 스캔만으로 데이터를 조회하는 방식입니다. PostgreSQL의 경우 이 스캔 방식은 테이블의 VM 비트열이 all_visible = true일 때만 사용 가능하다는 특징이 있습니다.
  • Bitmap Index Scan: 여러 인덱스를 사용하거나, 인덱스 키와 테이블 데이터의 정렬이 불일치, 여러 조건문 처리할 때 MySQL의 Block Nested-Loop Join (BNL)처럼 Bitmap을 생성하여 테이블 랜덤 I/O를 최소화하는 스캔 방식입니다. work_mem 크기에 따라 Exact 모드(레코드 위치 정보 저장)와 Lossy 모드(블록 정보만 저장)로 나뉘며, work_mem이 충분할 때 Exact 모드로 동작하여 성능이 향상됩니다.

이해를 돕기 위한 예시

비트맵 인덱스 스캔(Bitmap Index Scan)은 여러 개의 인덱스를 효율적으로 결합하거나 복잡한 조건을 처리할 때 사용되는 고급 스캔 방식입니다. 핵심은 비트맵이라는 임시 데이터 구조를 활용하여 테이블 접근을 최소화하는 것입니다.

비트맵이란?
비트맵은 행(row)의 위치 정보를 1과 0으로 표현한 지도라고 생각하면 이해하기 쉽습니다. 예를 들어, 1000개의 행이 있는 테이블에서 1, 5, 20번 행이 특정 조건을 만족하면 비트맵은 100100...1...과 같이 해당 위치에 1을 표시합니다.


비트맵 인덱스 스캔 동작 방식
이 스캔 방식은 세 단계로 나뉩니다.
비트맵 생성 (Bitmap Creation): 먼저 쿼리에 사용된 개별 인덱스를 스캔하여 조건에 맞는 행들의 위치를 비트맵에 표시합니다. 예를 들어, (condition_A OR condition_B) 쿼리가 있다면, condition_A를 만족하는 모든 행의 위치를 비트맵에 1로 표시하고, 이어서 condition_B를 만족하는 행의 위치도 비트맵에 1로 표시합니다. 여러 인덱스의 비트맵을 생성한 후 OR는 비트맵을 합치고, AND는 비트맵을 교차하는 비트 연산을 수행합니다. 비트맵 스캔 (Bitmap Scan): 1단계에서 생성된 최종 비트맵을 사용하여 실제로 테이블의 데이터를 읽습니다. 비트맵을 순서대로 탐색하며 1로 표시된 위치의 행만 골라 읽어옵니다. 이렇게 하면 랜덤 I/O를 최소화하고 순차적으로 데이터를 읽을 수 있어 효율적입니다. 테이블 데이터 읽기: 선택된 행의 데이터만 메모리로 가져옵니다.


Exact 모드 vs. Lossy 모드
비트맵 인덱스 스캔의 성능은 PostgreSQL의 작업 메모리(work_mem) 크기에 따라 달라집니다.

Exact 모드: work_mem이 충분할 때 사용됩니다. 비트맵에 각 행의 정확한 위치(TID)를 저장합니다. 따라서 테이블 접근 시 정확히 필요한 행만 읽어오므로 가장 효율적입니다.
Lossy 모드: work_mem이 부족할 때 사용됩니다. 비트맵에 행이 속한 데이터 블록의 위치만 저장합니다. 이 경우 해당 블록을 메모리로 가져온 뒤, 블록 내의 모든 행을 확인하여 조건에 맞는 행을 다시 필터링해야 합니다. 이는 불필요한 행을 읽게 되어 성능이 저하될 수 있습니다.

예시: 복잡한 쿼리에서 성능 향상
만약 '30대이고(age 인덱스), 서울에 거주하는(city 인덱스)' 사람을 찾는다고 가정해 봅시다.

SELECT * FROM users WHERE age BETWEEN 30 AND 39 AND city = 'Seoul';

일반적인 인덱스 스캔: age 인덱스를 스캔하여 30대인 사람들을 찾습니다. 이 과정에서 수많은 행을 읽어야 하고, 각 행에 대해 city 조건을 확인해야 합니다. 이 작업이 비효율적일 수 있습니다. 비트맵 인덱스 스캔: age 인덱스를 스캔하여 30대인 모든 행의 위치를 비트맵1에 표시합니다. city 인덱스를 스캔하여 서울 거주자인 모든 행의 위치를 비트맵2에 표시합니다. 두 비트맵을 AND 연산으로 결합하여 두 조건을 모두 만족하는 행의 최종 위치를 가진 비트맵을 생성합니다. 이 최종 비트맵을 사용하여 정확히 필요한 행만 테이블에서 순차적으로 읽습니다. 이렇게 하면 불필요한 디스크 I/O를 크게 줄여 성능을 향상시킵니다.

• 조인 방법 (Join Method): PostgreSQL은 Nested Loop Join, Hash Join, Merge Join 세 가지 기본 조인 방식을 지원하며, 옵티마이저가 데이터 양에 따라 결정합니다. MySQL은 주로 Nested Loop 계열 조인을 사용하며, 최신 버전에서 Hash Join이 도입되었습니다.
  • Nested Loop Join (NL Join): MySQL의 NL Join과 유사하며, 선행 테이블의 결과 건수만큼 후행 테이블을 반복적으로 조인합니다. OLTP 환경에 적합합니다.
    • Materialize: 후행 테이블에 적절한 인덱스가 없을 때, 후행 테이블 조회 결과를 메모리(work_mem)에 임시 테이블로 적재하여 반복적인 디스크 I/O를 줄이는 최적화입니다.
    • Memoize Join: PostgreSQL 14에 도입된 기능으로, NL Join 시 조인 키 컬럼의 선택도가 높아 동일 결과값을 반복 조인할 때, 그 결과값을 메모리에 캐시하여 재사용함으로써 CPU와 I/O 연산을 최소화합니다. MySQL의 스칼라 서브쿼리 캐싱 효과와 유사합니다.
  • Hash Join: MySQL의 Hash Join과 유사하게, 양쪽 테이블 모두 대량의 데이터를 조인할 때 사용됩니다. Inner 테이블에 해시 함수를 적용하여 해시 테이블을 생성하고, Outer 테이블로 이 해시 테이블을 검색하여 조인합니다. work_mem 크기가 중요하며, 부족하면 temp tablespace를 사용하는 Two-Pass Hash Join이 수행될 수 있습니다.
  • Sort-Merge Join: 두 테이블을 조인 컬럼 기준으로 정렬한 후 병합하여 일치하는 행을 찾는 방식입니다. MySQL 8.0 이전 버전에서 Sort Merge Join은 Block Nested-Loop Join으로 대체되었습니다. PostgreSQL에서는 Quick sort, External sort, Top-N heapsort, Incremental sort 등 다양한 정렬 방식을 사용합니다.

7. 트랜잭션 격리 수준 (Transaction Isolation Level)

PostgreSQL은 MySQL의 InnoDB와 마찬가지로 표준 SQL에서 정의하는 트랜잭션 격리 수준을 지원하지만, 일부 동작 방식에서 차이가 있습니다.

• Read Committed: PostgreSQL의 기본 격리 수준입니다. MySQL의 Read Committed와 유사하게, 한 트랜잭션 내에서 동일 쿼리를 여러 번 실행했을 때 다른 트랜잭션에 의해 커밋된 변경 사항이 반영되어 Non-Repeatable Read 및 Phantom Read 현상이 발생할 수 있습니다. PostgreSQL은 Dirty Read (커밋되지 않은 변경 사항 읽기)를 의도적으로 허용하지 않습니다. Read Uncommitted로 설정해도 Read Committed처럼 동작합니다.
• Repeatable Read: MySQL InnoDB의 기본 격리 수준과 동일하게, 트랜잭션 시작 이전에 이미 커밋된 데이터만 볼 수 있도록 보장합니다. 즉, 트랜잭션이 시작된 후 다른 트랜잭션에 의해 변경되어 커밋된 값은 보이지 않고, 처음에 조회한 값만 반복적으로 보여줍니다. Lost Update와 Write Skew와 같은 이상 현상은 여전히 발생할 수 있습니다.
• Serializable: 가장 높은 격리 수준으로, 트랜잭션이 직렬로 하나씩 처리되는 것과 같은 결과를 보장합니다. MySQL의 Serializable과 유사하게, 가능한 모든 이상 현상(Dirty Read, Non-Repeatable Read, Phantom Read, Lost Update)을 방지합니다. Write Skew와 같은 복잡한 이상 현상도 Serialization Failure 에러를 발생시켜 트랜잭션을 롤백 처리함으로써 방지합니다.

8. SQL 모니터링 도구

MySQL의 Performance Schema, Slow Query Log, SHOW STATUS 등과 유사하게, PostgreSQL도 SQL 성능 모니터링을 위한 도구를 제공합니다.

• pg_stat_statements: MySQL의 Performance Schema에서 쿼리 통계를 보는 것과 유사하게, 서버에 실행된 SQL 쿼리에 대한 실행 통계 정보를 확인할 수 있는 모듈입니다. 실행 횟수, 총 실행 시간, 블록 I/O 정보 등을 제공합니다.
• pg_stat_monitor: pg_stat_statements의 기능을 확장하여, 시간대별(Time Bucket) 성능 정보 정리 기능을 제공하며, SQL 수행 완료 여부, 클라이언트 정보(IP, 사용자명), SQL 오류 정보, 재귀 SQL 추적 등 더 상세하고 포괄적인 SQL 실행 이력 정보를 제공합니다.

---

+) 데이터베이스 선택 기준

 

MySQL 선택 기준

MySQL은 빠르고 가볍게 시작할 수 있는 웹 서비스에 최적화된 선택입니다.

• 단순한 구조에 강합니다. 벤치마크 결과에 따르면, PostgreSQL이 복잡한 분석 쿼리와 다중 테이블 조인에서 더 나은 성능을 보이고, MySQL은 단순한 SELECT 쿼리와 적은 JOIN이 포함된 작업에서 우수한 성능을 보입니다.
• 수평 확장에 효율적입니다. 읽기 요청이 압도적으로 많은 서비스에서는 수평 확장(Horizontal Scaling)이 중요합니다. MySQL은 리플리케이션(Replication) 기술이 매우 성숙해 읽기 부하를 여러 서버로 분산시키기 용이합니다.

PostgreSQL 선택 기준

PostgreSQL은 데이터의 신뢰성과 복잡한 기능이 요구되는 프로젝트에 최적화된 선택입니다.

• 데이터의 정합성이 중요할 때. 금융, 통신, 물류 시스템처럼 데이터의 정확성과 일관성이 최우선인 환경에서 PostgreSQL의 엄격한 ACID 준수와 MVCC는 큰 강점입니다.
• 복잡한 쿼리 및 데이터 분석에 유리합니다. JSONB, 지리정보(GIS) 등 다양한 데이터 타입을 지원하고, 고급 쿼리 플래너와 병렬 쿼리 처리 기능을 통해 복잡한 데이터 분석 및 리포트 생성에 뛰어난 성능을 보여줍니다.
• 뛰어난 확장성을 가집니다. '객체 관계형'이라는 특징 덕분에 사용자가 직접 새로운 데이터 타입이나 함수를 정의하여 시스템을 유연하게 확장할 수 있습니다.

최종 결론

MySQL PostgreSQL

핵심 강점	개발 및 운영의 용이성, 단순한 쿼리가 주일때 성능👍, 넓은 생태계	데이터 신뢰성, 복잡한 쿼리 처리 능력, 기능 확장성
주요 사용처	개인 웹사이트, 블로그, 소규모/중규모 커머스, 빠르게 시작하는 서비스	금융/통신 시스템, 빅데이터 분석, GIS/IoT 애플리케이션
선택 기준	빠른 개발 속도와 관리의 용이성이 최우선일 때	데이터의 정확성과 복잡한 비즈니스 로직이 중요할 때