PostgreSQL

PostgreSQL 은 데이터를 저장해 두고 질의로 꺼내 쓰는 데이터베이스 서버입니다. 클라이언트가 붙으면 서버가 그 연결마다 프로세스를 하나 띄웁니다. 자료형이나 함수는 쓰는 사람이 새로 더할 수 있습니다. 라이선스가 자유로워 누구나 무료로 쓰고 고치고 배포할 수 있습니다.

상세

PostgreSQL 은 ORDBMS(Object-Relational Database Management System, 객체-관계형 데이터베이스 관리 시스템)입니다. 캘리포니아 대학교 버클리 캠퍼스 컴퓨터 과학과에서 개발한 POSTGRES 4.2 판을 바탕으로 합니다. 공식 문서는 PostgreSQL 을 그 버클리 코드의 오픈소스 후손이라고 적습니다. POSTGRES 가 앞서 개척한 개념 여럿이 한참 뒤에야 일부 상용 데이터베이스 시스템에 들어왔다고도 적습니다.

데이터를 파일이나 애플리케이션 메모리에만 두면 프로세스가 죽을 때 함께 사라지고, 여러 프로그램이 동시에 손대면 값이 뒤섞입니다. PostgreSQL 같은 관계형 데이터베이스 서버는 이 두 문제를 서버 쪽에서 미리 풀어 둔 것이라, 구조화된 데이터를 SQL(Structured Query Language) 로 저장하고 질의하면서 여러 연결이 동시에 안전하게 손대야 할 때 고릅니다. 장애가 나면 알아서 승격까지 해 주길 바란다면 이야기가 다릅니다 — 그 자리는 아래 「포기한 것」이 답합니다.

문서는 PostgreSQL 이 SQL 표준의 상당 부분을 지원한다고 적습니다. 공식 문서의 준수 범위를 다루는 절(SQL Conformance)은 스스로 한정을 답니다. 그 절이 담은 것은 완전한 준수 선언이 아닙니다. 사용자에게 합리적이고 쓸모 있는 만큼의 주요 주제를 제시한 것이라고 밝힙니다.

확장 지점은 쓰는 사람 쪽으로 열려 있습니다. 자료형, 함수, 연산자, 집계 함수, 인덱스 방법, 절차 언어를 새로 더할 수 있습니다. 라이선스도 자유롭습니다. 사적 용도든 상업 용도든 학술 용도든 가리지 않고 누구나 무료로 쓰고 고치고 배포할 수 있습니다.

클라이언트와 서버

문서는 이 구조를 클라이언트/서버 응용의 전형이라고 적습니다. 클라이언트와 서버는 다른 호스트에 있어도 됩니다. 그때는 TCP/IP(Transmission Control Protocol/Internet Protocol) 네트워크 연결로 통신합니다. 문서는 이 점을 염두에 두라고 덧붙입니다 — 클라이언트와 서버가 다른 호스트에 있으면, 클라이언트 쪽에서 접근되는 파일이 서버 쪽 파일 시스템에서는 아예 접근되지 않거나, 접근되더라도 다른 파일 이름으로만 접근될 수 있기 때문입니다.

서버는 여러 클라이언트 연결을 동시에 다룹니다. 그 방법이 포크(fork, 연결마다 새 프로세스를 하나 통째로 시작하는 연산)입니다. 연결마다 새 프로세스를 하나 시작합니다. 그 시점부터 클라이언트와 새 서버 프로세스는 원래 postgres 프로세스 — 곧 감독 서버 프로세스입니다 — 의 개입 없이 통신합니다. 그 감독 서버 프로세스는 언제나 떠서 클라이언트 연결을 기다립니다. 클라이언트와 거기 딸린 서버 프로세스는 왔다가 갑니다.

flowchart TD
    C1["클라이언트 1"] -->|처음 연결| S["감독 서버 프로세스"]
    C2["클라이언트 2"] -->|처음 연결| S
    S --> P1["서버 프로세스 1"]
    S --> P2["서버 프로세스 2"]
    C1 -.->|이후 직접 통신| P1
    C2 -.->|이후 직접 통신| P2

포기한 것

연결마다 스레드

서버는 연결을 스레드로 받지 않습니다. 연결마다 새 프로세스를 포크합니다. 포크가 끝나면 클라이언트와 그 서버 프로세스는 원래 postgres 프로세스의 개입 없이 둘이서 통신합니다.

값은 연결 수를 정하는 손잡이에 붙어 있습니다. 문서는 PostgreSQL 이 어떤 자원의 크기를 max_connections 값에 직접 근거해 정한다고 적습니다. 이 값을 올리면 공유 메모리를 포함해 그 자원들의 할당이 늘어납니다. 연결 하나를 늘리는 일이 프로세스 하나와 그만큼의 자원 할당을 늘리는 일이 됩니다.

옛 행 버전의 즉시 삭제

PostgreSQL 에서 행 하나를 UPDATE 하거나 DELETE 해도 그 행의 옛 버전이 즉시 사라지지 않습니다. 문서는 이것이 다중 버전 동시성 제어(Multiversion Concurrency Control, MVCC)의 이득을 얻는 데 필요한 방식이라고 적습니다. 다른 트랜잭션에 아직 보일 가능성이 있는 행 버전은 지워지면 안 되기 때문입니다.

포기한 자리는 그 뒤에 옵니다. 낡거나 삭제된 행 버전은 언젠가 어느 트랜잭션에도 쓸모없어집니다. 그때 그 공간은 새 행이 재사용하도록 회수돼야 합니다. 디스크 요구량이 끝없이 늘어나는 것을 막으려면 그렇습니다. 그 회수를 하는 명령이 VACUUM(영어 동사 vacuum, 청소하다)입니다.

옛 행 버전은 이 네 자리를 거칩니다.

stateDiagram-v2
    최신버전: 최신 버전
    옛버전: 옛 버전 · 유지 중
    쓸모없음: 쓸모없음 · 회수 대상
    회수됨: 회수됨 · 공간 재사용 가능

    [*] --> 최신버전
    최신버전 --> 옛버전: UPDATE 또는 DELETE
    옛버전 --> 쓸모없음: 어느 트랜잭션에도 더는 안 보임
    쓸모없음 --> 회수됨: VACUUM 실행
    회수됨 --> [*]: 새 행이 그 공간을 씀

옛 버전이 "옛 버전" 자리에 머무는 동안은 다른 트랜잭션이 여전히 그 버전을 읽고 있을 수 있다는 뜻입니다. 그 트랜잭션들이 다 끝나야 다음 자리로 넘어갑니다.

회수가 밀리면 값이 경고로 드러납니다. 트랜잭션 ID 는 한정된 범위 안에서 차례로 매겨지는 값이라 다 쓰면 처음 값으로 되돌아갑니다. 이것이 되돌기입니다. 되돌기가 일어나면 오래전에 끝난 트랜잭션의 ID 가 방금 시작한 트랜잭션보다 나중 번호처럼 보이게 되어, 이미 커밋(commit, 트랜잭션이 낸 변경을 확정하는 것)된 데이터가 없던 것처럼 뒤바뀔 수 있습니다.

자동 청소(서버가 자동으로 돌리는 청소 실행기 데몬. 값은 아래 「운영」이 다룹니다)가 어떤 이유로 옛 트랜잭션 ID 를 테이블에서 치우지 못하면, 데이터베이스의 가장 오래된 트랜잭션 ID 가 되돌기 지점에서 4000만 트랜잭션까지 다가왔을 때 시스템이 경고를 뿜기 시작합니다.

WARNING:  database "mydb" must be vacuumed within 39985967 transactions
HINT:  To avoid XID assignment failures, execute a database-wide VACUUM in that database.

경고를 무시하면 다음 자리가 정지입니다. 되돌기까지 남은 트랜잭션이 300만 미만이 되면 시스템은 새 트랜잭션 ID 를 할당하는 명령을 아예 거부합니다.

ERROR:  database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "mydb"
HINT:  Execute a database-wide VACUUM in that database.

수동 VACUUM 이 이 문제를 풀 것으로 문서는 적습니다. 다만 그 VACUUM 은 슈퍼유저(모든 데이터베이스 객체에 제한 없이 접근할 수 있는 관리자 권한 계정)가 돌려야 합니다. 그러지 않으면 시스템 카탈로그(테이블·열 같은 객체 정보를 담아 두는 내부 테이블들)를 처리하지 못합니다. 시스템 카탈로그를 처리하지 못하면 데이터베이스의 datfrozenxid(그 데이터베이스에서 가장 오래된 트랜잭션 ID 를 담아 두는 값 — 앞서 본 되돌기 경고가 보던 바로 그 값입니다)를 앞으로 밀지 못합니다. 「민다」는 이 값을 더 최근 트랜잭션 ID 로 갱신해, 되돌기 지점까지 남은 여유를 다시 늘리는 것을 뜻합니다.

트랜잭션 ID 되돌기를 막는 쪽에서 보면 시스템은 이 세 자리를 오갑니다.

stateDiagram-v2
    정상운영: 정상 운영
    경고: 경고를 뿜음
    거부: 신규 트랜잭션 ID 할당 거부

    [*] --> 정상운영
    정상운영 --> 경고: 되돌기까지 4000만 트랜잭션 미만
    경고 --> 거부: 되돌기까지 300만 트랜잭션 미만
    거부 --> 정상운영: VACUUM 이 옛 XID 를 치움

"경고" 자리와 "거부" 자리 모두 자동 청소가 문제를 풀지 못한 채로 남아 있는 동안 이어집니다. 문서가 권하는 길은 슈퍼유저가 수동으로 VACUUM 을 돌리는 것입니다.

더티 읽기

표준 격리 수준(여러 트랜잭션이 동시에 돌 때 서로의 변경을 얼마나 보게 할지 정해 두는 등급) 네 가지를 전부 요청할 수는 있습니다. 그런데 내부에 실제로 구현된 격리 수준은 셋뿐입니다. PostgreSQL 의 Read Uncommitted 모드는 Read Committed 처럼 동작합니다. 표준에서 Read Uncommitted 가 허용하는 것이 더티 읽기 — 아직 커밋되지 않은 다른 트랜잭션의 값을 읽어 버리는 것 — 인데, PostgreSQL 은 이 값을 요청해도 내주지 않는 셈입니다.

문서가 이유를 답니다. 표준 격리 수준을 PostgreSQL 의 다중 버전 동시성 제어 구조에 대응시키는 유일하게 합당한 방법이기 때문입니다. 앞 소절에서 옛 행 버전을 남기기로 한 결정이 여기서 격리 수준 하나를 없앤 셈입니다.

장애 감지와 스키마 전파

PostgreSQL 은 프라이머리(현재 쓰기를 받는 서버)의 장애를 식별해서 스탠바이(그 프라이머리를 복제로 따라가며 대기하는 서버) 데이터베이스 서버에 알리는 시스템 소프트웨어를 제공하지 않습니다. 문서는 그런 도구가 여럿 존재한다고 적습니다. 그리고 그 도구들이 페일오버(장애 난 프라이머리 자리를 스탠바이가 넘겨받는 전환) 성공에 필요한 운영체제 기능과 잘 통합돼 있다고 적습니다. IP 주소 이전 같은 것입니다. 승격을 누가 언제 시킬지는 바깥에 남겨 둔 자리입니다.

논리 복제도 나르지 않는 것을 문서가 못 박습니다. 데이터베이스 스키마(테이블·뷰·인덱스 같은 객체를 담는 이름공간)와 데이터 정의어(Data Definition Language, DDL) 명령은 복제되지 않습니다. 초기 스키마는 pg_dump --schema-only 으로 손수 복사할 수 있습니다. 이후의 스키마 변경은 수동으로 맞춰 줘야 합니다.

시퀀스(부를 때마다 차례로 늘어나는 정수 값을 내주는 객체로, 자동 증가 번호를 만드는 데 씁니다) 데이터도 복제되지 않습니다. 그 시퀀스가 뒤를 받치는 serial(내부에 시퀀스를 만들어 기본값으로 쓰는 정수 열 표기)·identity 열(SQL 표준이 정한, 마찬가지로 시퀀스가 뒤를 받치는 자동 증가 열)의 데이터는 테이블의 일부로 복제됩니다. 다만 시퀀스 자체는 구독자(논리 복제에서 변경을 받아 적용하는 쪽 데이터베이스) 쪽에서 여전히 시작값을 가리킵니다.

예시

weather 테이블 질의

SELECT * FROM weather;

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      43 |      57 |    0 | 1994-11-29
 Hayward       |      37 |      54 |      | 1994-11-29
(3 rows)

튜토리얼이 싣는 출력 그대로입니다. 마지막 줄의 (3 rows) 가 돌려준 행 수입니다. Hayward 행의 prcp 칸은 비어 있습니다. 조건을 붙이면 같은 테이블에서 한 행만 나옵니다.

SELECT * FROM weather
    WHERE city = 'San Francisco' AND prcp > 0.0;

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
(1 row)

hstore 확장 켜기

CREATE EXTENSION hstore SCHEMA addons;

확장 문서가 스키마를 지정하는 예로 싣는 한 줄입니다. hstore 확장을 설치하면서 그 객체들을 addons 스키마 아래에 두라는 뜻입니다. 상세에서 본 확장 지점이 실제로 열리는 자리가 이 명령입니다.

Supabase

Supabase 공식 문서는 자기 제품을 이렇게 적습니다. 모든 Supabase 프로젝트가 Postgres 추상화가 아니라 온전한 Postgres 데이터베이스 하나를 통째로 받는다는 것입니다. PostgreSQL 을 안에 넣고 파는 시스템이 그 사실을 스스로 밝힌 문장입니다. 라이선스가 자유로워 PostgreSQL 을 그대로 가져다 상용 제품으로 되팔 수 있다는 것을, 실제로 되파는 회사의 문서로 보여 주는 예시입니다.

운영

shared_buffers

shared_buffers 는 데이터베이스 서버가 공유 메모리 버퍼로 쓸 메모리 양을 정합니다. 기본값은 대개 128MB 입니다. 커널 설정이 그만큼을 못 받치면 더 작을 수 있습니다. initdb 때 정해집니다. 최솟값은 128kB 입니다. 다만 최솟값보다 상당히 높은 설정이 성능을 위해 대개 필요합니다. 이 파라미터는 서버 시작 때만 설정할 수 있습니다.

전용 데이터베이스 서버에 메모리가 1GB 이상 있으면 시스템 메모리의 25% 가 합리적인 시작값입니다. 더 큰 값이 효과적인 작업 부하도 있습니다. 다만 PostgreSQL 이 운영체제 캐시에도 의존하기 때문에, 메모리의 40% 를 넘겨 shared_buffers 에 할당하는 것이 더 적은 양보다 나을 가능성은 낮습니다.

max_connections

max_connections 는 데이터베이스 서버로의 동시 연결 최대 수를 정합니다. 기본값은 대개 100 연결입니다. 커널 설정이 못 받치면 더 작을 수 있습니다. 이 파라미터도 서버 시작 때만 설정할 수 있습니다.

스탠바이 서버를 굴릴 때는 이 값을 프라이머리와 같거나 그보다 높게 둬야 합니다. 그러지 않으면 스탠바이 서버에서 질의가 허용되지 않습니다.

자동 청소

autovacuum 은 서버가 자동 청소 실행기 데몬을 돌릴지를 정합니다. 기본은 켜짐입니다. 다만 track_counts 도 함께 켜져 있어야 자동 청소가 동작합니다. 이 파라미터는 postgresql.conf 파일이나 서버 명령줄에서만 설정할 수 있습니다. 테이블 저장 파라미터를 바꾸면 개별 테이블에서만 끌 수도 있습니다.

이 파라미터를 꺼도 시스템은 트랜잭션 ID 되돌기를 막기 위해 필요하면 자동 청소 프로세스를 띄웁니다. 아래 표의 튜플은 앞서 본 행 버전과 같은 것 — 테이블의 한 행입니다.

relfrozenxid 는 앞서 본 datfrozenxid 의 테이블 단위 버전입니다 — 그 테이블에서 가장 오래된 트랜잭션 ID 를 담습니다.

autovacuum_freeze_max_age 의 기본이 비교적 낮은 2억인 이유를 문서가 적습니다. VACUUM 은 트랜잭션 ID 되돌기를 막을 뿐 아니라, 트랜잭션마다 커밋됐는지 아닌지를 기록해 두는 pg_xact 하위 디렉터리의 옛 파일도 지울 수 있습니다. 이 값을 낮게 잡아 VACUUM 을 그만큼 자주 강제해야 pg_xact 파일도 그만큼 자주 지워져 디스크에 쌓이지 않습니다. 이 값은 서버 시작 때만 설정할 수 있습니다. 개별 테이블에서 낮추는 것은 테이블 저장 파라미터로 됩니다.

볼 자리

무엇이 아픈지는 통계 뷰에서 봅니다. 여기서 백엔드라 부르는 것은 앞서 본, 연결마다 뜬 서버 프로세스와 같은 것입니다.

n_dead_tup 이 앞 절의 옛 행 버전과 이어지는 자리입니다. 백엔드 하나가 오래 머무는지는 pg_stat_activity 의 state 와 query 가 보여줍니다.

관련 항목

PostgreSQL을 이루는 동시성·트랜잭션 구성 요소

다중 버전 동시성 제어 · 격리 수준 · 트랜잭션 처리 · 2단계 커밋 · VACUUM · 트랜잭션 ID 되돌기

PostgreSQL을 이루는 저장·색인 구성 요소

선행 기록 로그 · 체크포인트 · 아카이빙 · 복구 · TOAST(The Oversized-Attribute Storage Technique) · 파티셔닝 · B-tree · 해시 인덱스 · GiST(Generalized Search Tree) · SP-GiST(Space-Partitioned Generalized Search Tree) · GIN(Generalized Inverted Index) · BRIN(Block Range Index)

PostgreSQL을 이루는 복제·이중화 구성 요소

스트리밍 복제 · 복제 슬롯 · 캐스케이딩 복제 · 동기 복제 · 핫 스탠바이 · 페일오버 · 논리 복제

PostgreSQL 서버에 접속하거나 앞에 서는 도구

psql · pg_dump · 커넥션풀

PostgreSQL이 따르는 표준과 넓히는 방식

확장 · 외래 데이터 래퍼 · SQL 표준