Tmax OpenSQL Documentation

PostgresSQL Version 은 Major version과 Minor version으로 구성됩니다.

예를 들어, 11.14 version 은 Major version이 11 , Minor version 14 로 구성됩니다.

PostgreSQL 은 동일한 Major version을 가진 Minor version과 호환됩니다.

예를 들어, 10.1 version 은 10.0 및 10.6 버전과 호환됩니다.

호환되는 버전 간에 업데이트 하려면 서버가 다운된 상태에서 실행 파일을 교체하고 서버를 다시 시작하면 됩니다.

데이터 디렉토리는 변경되지 않은 상태로 유지됩니다.

메이저 버전의 경우 내부 데이터 저장 형식이 변경될 수 있으므로 업그레이드가 복잡합니다.

PostgreSQL 를 배우고 실습에 사용할 수 있는 샘플 데이터베이스 생성 방법을 알아보겠습니다.

DVD rental 데이터베이스는 DVD 대여점의 비즈니스 프로세스를 나타냅니다.

아래와 같은 오브젝트를 포함하고 있습니다.

아래 zip 파일을 통해 PostgreSQL DVD rental Database 를 다운로드 할 수 있습니다.

다운로드 받은 파일을 서버로 옮긴 후 아래와 같이 수행하여 적용합니다.

PostgreSQL의 데이터베이스 클러스터는 하나의 PostgreSQL 서버가 관리하는 데이터베이스들의 모음입니다.

데이터베이스 클러스터를 새로 생성하면 앞으로 데이터를 저장하고 관리하기 위한 Base 디렉토리가 생성되며, PGDATA 환경변수에 Base 디렉토리의 경로가 저장됩니다. 앞으로는 Base 디렉토리의 경로를 PGDATA라 지칭하겠습니다. Base 디렉토리에는 다음과 같은 파일들과 디렉토리들이 기본적으로 생성됩니다.

PostgreSQL의 데이터베이스는 오브젝트(aka. SQL 오브젝트, 데이터베이스 오브젝트)들을 관리하기 위한 최상위 계층(Top-most hierarchy)입니다.

PostgreSQL의 데이터베이스는 PostgreSQL의 명령행 도구(Command Line Tool)인 psql에서 “\l” 명령어를 통해 조회할 수 있습니다. 설치 직후, 데이터베이스 목록을 조회해보면, postgres, template0, template1 데이터베이스를 포함하고 있음을 확인 할 수 있습니다.

각각의 데이터베이스는 $PGDATA/base 디렉토리에 저장되어 있습니다. 하단의 예시는 초기 상태의 디렉토리입니다. 하단의 예시의 디렉토리들은 순서대로 각각 postgres, template0, template1 데이터베이스에 대한 디렉토리입니다. 또한 psql에서 조회한 데이터베이스 목록과 맵핑되는 것을 확인할 수 있습니다.

이번 장에서는 PostgreSQL의 구조에 대해 설명하겠습니다. PostgreSQL은 클라이언트-서버 모델을 사용하고 있습니다. 클라이언트는 사용자 어플리케이션과 같은 PostgreSQL을 사용하는 외부 프로그램을 의미합니다. 일반적으로 PostgreSQL(PostgreSQL 서버)은 하나의 데이터베이스 클러스터를 운영하는 프로세스 집합을 의미하며 이는 클라이언트-서버 모델에서 서버를 의미합니다. 해당 문서에서는 PostgreSQL 서버에 대해서 설명하겠습다.

PostgreSQL 서버의 프로세스 구조에 대해 논의하기 전, 실제 PostgreSQL에서 어떤 프로세스들이 동작하는지 다음 명령어를 수행하여 직접 확인해보겠습니다.

본 문서에서는 위 결과와 공식 문서의 내용을 종합하여 PostgreSQL의 프로세스를 다음과 같이 3가지로 분류하고자 합니다.

Postgres 프로세스는 PostgreSQL의 데이터베이스 서버입니다.

Postgres 프로세스는 PostgreSQL 서버 시작 시, 최초로 생성되어 다른 백엔드 프로세스와 백그라운드 프로세스를 생성합니다. 일례로 Postgres 프로세스는 클라이언트로부터 커넥션 생성 요청을 받으면, 백엔드 프로세스를 새로 시작하여 새로운 커넥션에 할당합니다. 또한 PostgreSQL 필요 시(PostgreSQL 서버 시작 포함) 또는 다른 프로세스로부터 요청을 받은 경우 필요한 백그라운드 프로세스를 생성합니다.

하나의 데이터베이스 클러스터는 하나의 Postgres 프로세스에 의해 관리됩니다.

백엔드 프로세스는 자신에게 배정된 커넥션을 통해 요청된 쿼리 또는 명령을 수행하기 위한 일종의 워커 프로세스입니다. 해당 프로세스는 새로운 커넥션이 맺어질 때마다 Postgres 프로세스에 의해 생성(fork)되고, 커넥션이 끊어질 때 종료됩니다. 백엔드 프로세스는 커넥션과 1:1로 맵핑되며 커넥션으로 이어진 클라이언트와 TCP/IP를 기반으로 통신하게됩니다.

위 예시에서는 PID(Process ID)가 167, 6228인 프로세스가 백엔드 프로세스에 해당합니다. 부모 프로세스의 PID를 의미하는 PPID(Parent Process ID)가 1인 것으로 보아 각각의 백엔드 프로세스는 새로운 커넥션이 추가 될때마다 Postgres 프로세스에 의해 새로 생성되는 것을 확인할 수 있습니다.

PostgreSQL은 max_connections 파라미터로 최대 커넥션 갯수(Default: 100)를 조절할 수 있습니다.

사용자의 Query가 요구하는 결과물을 가공하는 데에는 여러 방법이 존재할 수 있습니다. 예를 들어, 조건문을 사용하여 테이블로부터 필터링 된 결과만 읽으려고 할 때 테이블의 모든 Row를 읽어서 필터링을 할 수도 있고 아니면 인덱스를 활용하여 필요한 Row만 읽어올 수도 있습니다. 둘 중 어떤 선택이 더 효율적일 지는 조건문의 성격에 따라, 데이터의 특성에 따라, 그리고 장비의 스펙에 따라 달라질 수 있습니다.

사용자 Query 수행을 최적화하기 위해 가능한 모든 수행 방법의 조합을 비교하고 최선의 실행 경로를 결정하는 것이 Optimizer가 맡은 임무입니다. PostgreSQL의 Optimizer는 Query 수행을 최적화하기 위해 비용 기반 최적화(Cost-based Optimization) 방법을 사용합니다. 비용이란 Query 수행에 필요한 자원을 수치화한 것이며 대표적인 예로 로우를 읽고 값 비교를 하는 데 발생하는 CPU 비용, 임의의 페이지(Page)를 읽을 때 발생하는 I/O 비용 등이 있습니다. Optimizer는 Query 수행이 가능한 모든 실행 경로에 대해 비용을 계산하고 제일 적은 비용을 갖는 경로를 실행 계획(Execution Plan) 형태로 수립하는 일을 합니다.

데이터베이스에 요구하는 Query 복잡도가 높아질수록 실행 계획을 수립하는 과정은 더 복잡해질 수밖에 없습니다. 따라서 해당 글에서는 실행 계획이 수립되는 과정의 전반적인 이해를 높이는 차원에서 간단한 Query에 대한 예로 설명하겠습니다. SQL 형태로 들어온 사용자 Query를 Parsing하면 그 결과는 Query Tree 의 형태로 Optimizer에게 전달됩니다.

Query Tree에는 Query에 포함된 컬럼이나 테이블과 같은 데이터 객체의 MetaData와 연산자, 기호와 같이 데이터 값을 얻기 위해 계산해야 하는 표현식이 상호관계에 알맞게 트리 형태로 저장되어 있습니다. Optimizer는 Query Tree에 있는 정보를 사용하여 결과값을 얻을 수 있는 실행 경로를 모두 구하고, 각각의 비용을 계산 및 비교하여 최적의 실행 계획을 선택합니다.

실행 계획 수립 과정은 크게 3개의 과정으로 나뉩니다.

각 과정에 대해 살펴보도록 하겠습니다.

실행 경로를 만들기에 앞서 Query Tree 내부에 미리 할 수 있거나 불필요한 표현식에 대해서 간소화를 수행합니다. 간소화 작업은 실행 경로의 변주 개수를 줄이는 역할을 하기 때문에 Query 최적화 작업 시간을 줄이고 메모리와 CPU 사용량도 줄여주는 효과를 가져옵니다.

Query Tree 간소화 작업은 다양한 부분에 대해 이뤄지는데 아래 간단한 예제들을 소개합니다.

Concurrency Control은 DBMS에서 특정 데이터에 대한 동시 접근을 제어하는 것을 의미합니다. Concurrency Control은 Multi-version Concurrency Control (MVCC), Strict Two-phase Locking (S2PL), Optimistic Concurrency Control (OCC) 등 여러 방식으로 이루어집니다. PostgreSQL은 MVCC 방식을 활용하여 Concurrency Control을 수행하고 있습니다.

MVCC는 각 데이터를 각 시점에 따라 여러 버전으로 관리하는 방식입니다. 따라서, 사용자에게 보여지는 데이터는 1개로 보여지지만 실제로 해당 데이터는 여러 버전의 데이터(레코드)로 관리되고 있습니다.

이번 장에서는 PostgreSQL에서 MVCC를 구현하는 방법을 설명하고 이를 기반으로 Isolation Level, Lost Update 등 DBMS에서 정의하는 Concurrency 이슈들이 실제로 어떻게 다루어지고 있는지 확인해볼 것입니다.

Reference. Bernstein, Philip A., and Nathan Goodman. "Concurrency control in distributed database systems." ACM Computing Surveys (CSUR) 13.2 (1981): 185-221.

대다수의 데이터베이스는 트랜잭션을 관리하고 있고 이를 위하여 트랜잭션 아이디라는 개념을 사용하고 있습니다. PostgreSQL도 예외 없이 트랜잭션 아이디를 사용하고 있습니다. 본 문단에서는 PostgreSQL에서 트랜잭션 아이디를 관리하는 구조에 대하여 설명하겠습니다.

PostgreSQL은 32-bit unsigned integer(1 ~ 2^32 -1)로 표현되어 약 42억 개의 트랜잭션 아이디를 가질 수 있고 1부터 시작하여 새로운 트랜잭션이 시작될 때 증가합니다(0은 invalid tx_id로 예약되어 있습니다). PostgreSQL은 이를 원형(Circular) 형태로 관리하고 있습니다.

눈치가 빠르다면 이 구조에서 다음과 같은 의문점을 가질 수 있습니다. “트랜잭션 아이디를 다 쓰고 한바퀴 돌면 어떡하지?” 이 개념은 Vacuum에서 설명할 것 입니다. 그때까지 이 의문을 마음에 품고 있길 바랍니다.

관계형 데이터베이스를 최초로 고안한 에드거 F. 커드는 Relation은 튜플(Tuple)들의 집합이라고 정의하였습니다. 일반적인 데이터베이스에서는 주로 Relation은 테이블(Table), 튜플은 행(Row)의 형태로 표현됩니다. 그리고 파일 시스템의 관점에서는 Relation은 파일, 튜플은 그 안에 기록된 레코드(Record)들에 대응합니다.

후술할 내용을 원활하게 이해하기 위해서는 우리가 원론적인 정의를 하는 이유에 대해 이해하고 넘어갈 필요가 있습니다. 왜냐하면 우리가 다룰 Concurrency Control은 우리가 익숙한 개념인 테이블과 행과 같은 데이터베이스 관점이 아닌 파일 시스템의 관점에서 이루어지기 때문입니다. (위 개념은 필수적으로 이해야하는 후술할 내용들의 기본 전제입니다. 이 문장이 이해되지 않는다면 Concurrency Control 항목을 다시 한번 읽는 것을 추천합니다.)

Reference. Codd, Edgar F. "A relational model of data for large shared data banks." Communications of the ACM 13.6 (1970): 377-387.

이번 장에서는 PostgreSQL에서 어떤 방식으로 데이터를 찾고 접근하는지에 대한 내용을 다루어보려고 합니다.

PostgreSQL 디자인 문서를 살펴보면 확장성을 특히 유념하고 설계되었음을 알 수 있습니다. 그 결과, PostgreSQL의 코어 시스템은 구현되어있는 Access Method를 호출할 뿐 실제 해당 내용에 대해서는 거의 알지 못하는 구조를 가지고 있습니다. 즉, Access Method는 상당한 독립성을 가지고 있어 여러 개발자가 쉽게 추가할 수 있도록 하는 것을 의도한 것입니다.

PostgreSQL 곳곳에서 강조하고 있는 내용인 만큼 우리는 PostgreSQL의 Data Access에 대해 이해하기 위해서는 Access Method에 대한 이해가 필요합니다.

일단, 다음 명령어를 통해 PostgreSQL에 기본으로 내장된 Access Method를 살펴봅시다. amtype을 보면 t (table), i (index) 타입의 Access Method가 기본적으로 존재함을 확인할 수 있습니다. 테이블과 인덱스의 개념은 본문에서 설명하지 않겠습니다.

이전 장들에서 우리는 Storage for Tables 즉, 실제로 데이터베이스의 테이블 등이 파일 시스템에서 어떻게 저장되는지 설명한 바 있습니다. Table Access Method는 어떻게 이에 접근하고 관리할지에 대한 내용을 정의하고 있습니다.

Index Access Method 또한 Table Access Method와 크게 다르지 않습니다. 대부분의 관계형 데이터베이스는 탐색 속도를 향상하기 위하여 인덱스라는 개념을 활용하고 있습니다. Index Access Method는 각 Index Type을 통해 어떻게 데이터에 접근하고 관리하는지에 대한 내용을 정의하고 있습니다.

데이터베이스는 쿼리 응답에 필요한 데이터를 찾기 위해 Scan이라는 과정을 거칩니다. PostgreSQL은 Access Method에 따라 Sequential Scan, Index Scan, Bitmap Scan 등의 전략을 사용합니다. 특정 쿼리에서 어떤 Scan 전략을 사용했는지는 EXPLAIN을 사용하여 쿼리를 수행했을 때, 플랜 노드의 형태로 다음과 같이 나타납니다.

필수 조정 파라미터

구성 파일 형식 ( repmgr.conf )

psql은 PostgreSQL에 대한 터미널 기반 프론트 엔드입니다. PostgreSQL 데이터베이스에 연결하여 대화식으로 쿼리를 입력하여 보낸 후, 쿼리 결과를 볼 수 있습니다. 스크립트를 작성하고 다양한 작업을 자동화할 수 있도록 많은 메타 명령어(meta-commands)와 shell-like 기능을 제공합니다.

백슬래시로 시작하는 psql에 입력하는 모든 것을 psql 자체에서 처리되는 psql 메타 명령이라고 합니다. psql 메타 명령어는 psql을 관리하는 데에 있어서 더 편리하게 해줍니다. 슬래시 또는 백슬래시 명령이라고도 합니다.

initdb는 새로운 PostgreSQL 데이터베이스 클러스터를 생성합니다.

데이터베이스 클러스터를 생성하게 되면 아래 내용들이 생성 됩니다.

pg_dumpall은 클러스터의 모든 PostgreSQL 데이터베이스를 하나의 스크립트 파일에 기록 (“덤프”) 하는 유틸리티입니다. 스크립트 파일에는 데이터베이스를 복원하기 위해 psql에 대한 입력으로 사용할 수 있는 SQL 커맨드가 포함되어 있습니다.

pg_dumpall은 클러스터의 각 데이터베이스에 대해 pg_dump를 호출하고, 또한 모든 데이터베이스의 공통 전역 개체(Global Object), 즉 데이터베이스의 role 및 테이블 스페이스, Config 파라미터에 대한 권한까지 덤프합니다. (pg_dump는 이러한 오브젝트들을 덤프하지 않습니다.)

pg_dumpall은 모든 데이터베이스에서 테이블을 읽기 때문에 완전한 덤프를 생성하려면 데이터베이스 수퍼유저로 연결해야 할 가능성이 큽니다. 또한 PostgreSQL 서버에 여러 번 연결하기 때문에 (데이터베이스당 한번 씩) 비밀번호 인증을 사용하게 되면 매번 비밀번호를 묻게 됩니다. 이럴 땐, ~/.pgpass파일을 생성하면 편리합니다!

pg_restore은 pg_dump가 만든 아카이브 백업 파일에서 PostgreSQL 데이터베이스를 복원하기 위해 사용하는 유틸리티입니다.

pg_restore은 두 가지 모드로 작동할 수 있습니다.