PostgreSQL는 전 세계적으로 널리 사용되는 데이터베이스 관리 시스템이지만, SQL을 실행할 때 내부적으로 어떤 프로세스가 움직이고 데이터가 메모나 디스크로 어떻게 읽고 쓰이는지를 직관적으로 파악하는 것은 쉽지 않습니다. 그런 PostgreSQL의 내부 구조를 시뮬레이션 시티(SimCity)와 같은 3D 도시로 시각화해 주는 웹 애플리케이션이 “PGSimCity”입니다. PGSimCity · How PostgreSQL Works, in 3D [https://nikolays.github.io/PGSimCity/](https://nikolays.github.io/PGSimCity/) GitHub - NikolayS/PGSimCity at pgsimcity-hud · GitHub [https://github.com/NikolayS/PGSimCity?ref=pgsimcity-hud](https://github.com/NikolayS/PGSimCity?ref=pgsimcity-hud) PGSimCity에 접속하면 다음과 같이 나타납니다. 먼저 흐름을 파악하기 위해 가이드 투어를 따라다닐 것입니다. “Start the tour”를 클릭합니다.
가이드 투어의 처음에는 PostgreSQL에 대한 연결이 어디에서 시작되는지가 나타납니다. 화면 중앙의 “The service address”가 연결의 진입구이며, 클라이언트로부터 TCP 연결이 Postmaster로 도착하고, 거기서 실제 처리를 담당하는 백엔드로 전달되는 흐름이 표현되어 있습니다. 오른쪽의 Inspector에는 “removed dead tuples”가 표시되어 있으며, 왼쪽 아래에서 뻗어나오는 클라이언트 연결 흐름과 Postmaster가 서버 측의 접수 창구로 그려져 있습니다.
그리고 PostgreSQL이 연결마다 백엔드 프로세스를 할당하는 방식입니다. 화면 앞에 펼쳐진 건물들이 각 백엔드를 나타내며, 연결이 증가할수록 전용 프로세스도 증가합니다. PostgreSQL에서는 대기 중인 연결이라도 메모나 프로세스 관리 비용이 발생하기 때문에, 연결 풀이 중요합니다.
SQL이 실행 계획으로 변환되는 “Query Lab”에서는 화면 상단에 SELECT 문이 표시되고, 하단에는 Parser, Rewriter, Planner, Executor에 해당되는 단계와 Seq Scan, Index Scan, Bitmap Heap Scan과 같은 후보들이 나열됩니다. SQL은 “어떤 데이터를 원하는지”를 지정하는 것으로, 실제로 어떤 경로로 읽는지는 PostgreSQL이 통계 정보를 기반으로 추정하여 결정합니다.
shared_buffers는 PostgreSQL의 공유 버퍼 캐시를 나타냅니다. 중앙의 흰색 타일 모양의 광장이 버퍼 풀이며, PostgreSQL은 디스크상의 데이터를 1행 단위가 아닌 8KiB 페이지 단위로 이곳에 읽어들입니다. 상단의 CACHE HIT가 높을수록 필요한 페이지가 이미 메모 상에 존재하고, 디스크 접근을 피하고 있다는 것을 의미합니다.
디스크상의 데이터 페이지의 내용입니다. 지하의 저장 영역에 있는 accounts 테이블은 8KiB 페이지의 모음으로 표현되어 있으며, 페이지 내에는 행 데이터와 빈 공간을 관리하는 정보를 포함합니다. 녹색의 경로가 디스크상의 페이지가 shared_buffers로 읽히는 흐름을 나타냅니다.
데이터 페이지보다 먼저 WAL(Write-Ahead Logging)로 변경 내용을 기록하는 방식입니다. UPDATE와 같이 WAL로 변경하면 PostgreSQL은 먼저 메모상의 페이지를 변경하고, 그 변경 내용을 WAL 버퍼에 기록합니다. 화면 중앙 하단의 원형 시설이 WAL 버퍼이며, 페이지 본체를 나중에 쓰더라도 먼저 WAL에 남아있으면 장애 발생 후 복구할 수 있습니다.
트랜잭션의 커밋에서는 변경된 데이터 페이지가 즉시 디스크에 쓰이는 것을 기다리는 대신, WAL 레코드가 안전하게 플래시되기를 기다립니다. 화면 오른쪽 상단에는 synchronous_commit을 끄는 상태도 표시되어 있으며, 대기 시간을 줄일 수 있지만, 장애 발생 시 직근의 커밋을 잃을 수 있다는 트레이드오프를 보여줍니다.
체크포인트에 의해 쓰기 부하가 집중되는 모습입니다. 왼쪽의 녹색 시설이 체크포인터이며, shared_buffers 내의 덜티 페이지를 모아서 스토리지에 씁니다. 상단에는 max_wal_size에 의해 체크포인트가 발생한 것이 표시되며, 체크포인트가 잦을수록 WAL과 데이터 페이지의 쓰기가 집중되어 지연이 스파이크 형태로 나타납니다.
PostgreSQL의 MVCC(Multi-Version Concurrency Control)에 의해, 업데이트된 오래된 행은 즉시 사라지지 않습니다. UPDATE는 기존 행을 그 자리에 덮어쓰는 대신, 새로운 행 버전을 생성하고, 오래된 행을 데드 튜플(dead tuple)으로 남깁니다. 아래 그림에서는 sessions 테이블에 데드 튜플이 축적되어 있으며, 읽기 및 쓰기를 막기 어렵게 하는 대신, 나중에 vacuum으로 청소해야 합니다.
autovacuum이 데드 튜플을 회수하는 모습입니다. autovacuum 워커는 통계 정보를 보고, 불필요한 행 버전이 증가한 테이블을 순회하며, 테이블이나 인덱스를 읽으면서 빈 영역을 재사용 가능하게 합니다. 다만, vacuum은 일반적으로 파일 크기를 직접 축소하는 것이 아니라, 테이블 내부의 빈 영역을 미래의 INSERT나 UPDATE에서 사용할 수 있도록 하는 처리입니다.
중앙의 빨간색 원으로 표시된 “XMIN HORIZON”은, 오래된 트랜잭션이 아직 참조할 수 있는 가능성이 있는 행 버전의 경계를 나타냅니다. 이 경계가 움직이지 않으면, autovacuum은 데드 튜플을 안전하게 삭제하지 못하고, 불필요한 행이 축적되어 테이블이 부풀어 오게 됩니다.
프라이머리(primary) 측에서 생성된 WAL(Write-Ahead Logging)은 walsender를 통해 스탠바이(standby)로 전송되며, 스탠바이 측의 walreceiver나 startup 프로세스가 받아서 재생합니다. 여기서 전송되는 것은 SQL 문 자체는 아니며, 데이터 페이지에 일어난 물리적인 변경의 기록이며, 스탠바이는 그 WAL을 순서대로 적용하여 프라이머리에 추종합니다.
화면에 나타나는 것은 전송됨, 쓰기됨, 플래시됨, 재생됨이라는 4개의 LSN(Log Sequence Number)에 해당하는 상태이며, 스탠바이에서 WAL을 받더라도 아직 재생하지 않으므로 쿼리 결과에는 반영되지 않습니다. 프라이머리가 보낸 위치와 스탠바이에서 실제로 적용한 위치의 차이가 화면 상단의 REPL LAG(Replica Lag)로 나타납니다.
마지막으로 PostgreSQL 내부의 일련의 처리 과정을 도시 전체로서 되돌아봅니다. 클라이언트 연결, Postmaster, 백엔드, Query Lab, shared_buffers, WAL(Write-Ahead Logging), 저장소, 유지 관리, 스탠바이 등이 같은 공간에 배치되어 지금까지 본 처리 과정이 하나의 흐름으로 연결되어 있음을 알 수 있습니다. PostgreSQL의 성능 문제는 이 중 캐시, WAL(Write-Ahead Logging), 체크포인트, vacuum, 레플리케이션 등의 특정 부분에서 대기나 병목 현상이 발생하는 것으로 인해 표면화됩니다.
원문 보기 | 출처: Gigazine