Burt.K

Awesome Discovery

[PostgreSQL] 제2부. SQL 언어 - 5장. 데이터 정의

작성일 — 2025년 1월 29일

Table of Contents

이 장에서는 데이터를 저장할 데이터베이스 구조를 어떻게 만드는지 다룬다. 관계형 데이터베이스에서 원시 데이터는 테이블에 저장된다. 따라서 이 장의 대부분은 테이블을 생성하고 수정하는 방법, 그리고 테이블에 어떤 데이터를 저장할지 제어하는 기능에 대해 설명한다. 이후 테이블을 스키마로 어떻게 조직화하는지, 그리고 테이블에 권한을 어떻게 할당하는지 논의한다. 마지막으로 상속, 테이블 분할, 뷰, 함수, 트리거와 같이 데이터 저장에 영향을 미치는 다른 기능들도 간략히 살펴본다.

5.1. 테이블 기초

관계형 데이터베이스의 테이블은 종이에 그린 표와 매우 유사하다. 행과 열로 구성되며, 열의 개수와 순서는 고정되어 있고 각 열에는 이름이 있다. 행의 개수는 가변적이며 특정 시점에 저장된 데이터의 양을 반영한다. SQL은 테이블 내 행의 순서를 보장하지 않는다. 명시적으로 정렬을 요청하지 않는 한, 테이블을 읽을 때 행은 임의의 순서로 나타난다. 이에 대한 자세한 내용은 7장에서 다룬다. 또한 SQL은 행에 고유 식별자를 할당하지 않기 때문에, 하나의 테이블에 완전히 동일한 행이 여러 개 존재할 수 있다. 이는 SQL의 수학적 모델에서 비롯된 특성이지만, 일반적으로 바람직하지 않다. 이 문제를 해결하는 방법은 이 장의 뒷부분에서 다룬다.

각 열은 데이터 타입을 가진다. 데이터 타입은 열에 할당할 수 있는 값의 범위를 제한하고, 저장된 데이터의 의미를 정의하여 계산에 활용할 수 있게 한다. 예를 들어, 숫자 타입으로 선언된 열은 임의의 문자열을 받아들이지 않으며, 이 열에 저장된 데이터로 수학적 계산을 수행할 수 있다. 반면, 문자열 타입으로 선언된 열은 거의 모든 종류의 데이터를 받아들이지만 수학적 계산에는 적합하지 않다. 대신 문자열 연결과 같은 다른 작업을 수행할 수 있다.

PostgreSQL은 다양한 응용 프로그램에 적합한 상당한 수의 내장 데이터 타입을 포함한다. 사용자가 직접 데이터 타입을 정의할 수도 있다. 대부분의 내장 데이터 타입은 이름과 의미가 명확하여, 자세한 설명은 8장으로 미룬다. 자주 사용되는 데이터 타입에는 정수를 위한 integer, 소수점이 있는 숫자를 위한 numeric, 문자열을 위한 text, 날짜를 위한 date, 시간 값을 위한 time, 날짜와 시간을 모두 포함하는 값을 위한 timestamp가 있다.

테이블을 생성하려면 적절하게 명명된 CREATE TABLE 명령을 사용한다. 이 명령에서는 최소한 새 테이블의 이름, 열의 이름, 각 열의 데이터 타입을 지정해야 한다. 예를 들면:

CREATE TABLE my_first_table (
    first_column text,
    second_column integer
);

이 코드는 두 개의 열을 가진 my_first_table이라는 테이블을 생성한다. 첫 번째 열의 이름은 first_column이고 text 데이터 타입을 가지며, 두 번째 열의 이름은 second_column이고 integer 타입을 가진다. 테이블과 열의 이름은 4.1.1절에서 설명한 식별자 문법을 따른다. 타입 이름도 일반적으로 식별자이지만, 예외도 있다. 열 목록은 쉼표로 구분되며 괄호로 둘러싸인다.

물론, 앞의 예제는 매우 인위적이다. 일반적으로는 테이블과 열에 저장할 데이터의 성격을 알 수 있는 이름을 부여한다. 다음은 더 현실적인 예제이다:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);

(numeric 타입은 화폐 금액에 일반적으로 사용되는 소수점 이하 값을 저장할 수 있다.)

팁

여러 개의 관련 테이블을 생성할 때는 테이블과 컬럼의 이름을 일관된 규칙으로 짓는 것이 좋다. 예를 들어, 테이블 이름을 단수형으로 할지 복수형으로 할지 선택할 수 있는데, 이는 데이터베이스 설계자마다 선호하는 방식이 다르다.

테이블이 포함할 수 있는 컬럼 수에는 제한이 있다. 컬럼의 데이터 타입에 따라 250개에서 1600개까지 다양하다. 그러나 이렇게 많은 수의 컬럼을 가진 테이블을 정의하는 것은 매우 특이한 경우이며, 대부분 좋지 않은 설계가 될 수 있다.

더 이상 필요하지 않은 테이블은 DROP TABLE 명령어로 삭제할 수 있다. 예제는 다음과 같다:

DROP TABLE my_first_table;
DROP TABLE products;

존재하지 않는 테이블을 삭제하려고 하면 오류가 발생한다. 하지만 SQL 스크립트 파일에서는 테이블의 존재 여부와 관계없이 스크립트가 동작할 수 있도록, 테이블을 생성하기 전에 먼저 삭제를 시도하는 것이 일반적이다. 이때 발생하는 오류 메시지는 무시한다. (원한다면 표준 SQL은 아니지만 DROP TABLE IF EXISTS 구문을 사용하여 오류 메시지를 피할 수 있다.)

이미 존재하는 테이블을 수정해야 한다면, 이 장의 뒷부분에 있는 5.7절을 참고하면 된다.

지금까지 설명한 내용들로 완전히 동작하는 테이블을 만들 수 있다. 이 장의 나머지 부분에서는 데이터 무결성, 보안, 편의성을 보장하기 위해 테이블 정의에 추가할 수 있는 기능들을 다룬다. 지금 바로 테이블에 데이터를 채우고 싶다면 6장으로 건너뛰고 이 장의 나머지 부분은 나중에 읽어도 된다.

5.2. 기본값 설정

컬럼에 기본값을 지정할 수 있다. 새로운 행을 생성할 때 일부 컬럼에 값을 명시하지 않으면, 해당 컬럼은 지정된 기본값으로 채워진다. 데이터 조작 명령을 사용할 때 특정 컬럼을 기본값으로 설정하도록 명시적으로 요청할 수도 있다. 이때 기본값이 무엇인지 알 필요는 없다. (데이터 조작 명령에 대한 자세한 내용은 6장에서 다룬다.)

기본값을 명시적으로 선언하지 않으면 null 값이 기본값이 된다. null 값은 알 수 없는 데이터를 나타내는 것으로 간주할 수 있으므로 이는 대부분의 경우 합리적이다.

테이블 정의에서 기본값은 컬럼 데이터 타입 뒤에 표시한다. 예를 들면 다음과 같다:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric DEFAULT 9.99
);

기본값은 표현식일 수도 있는데, 이는 테이블이 생성될 때가 아니라 기본값이 삽입될 때마다 평가된다. 대표적인 예로 timestamp 컬럼에 CURRENT_TIMESTAMP를 기본값으로 설정하면 행이 삽입되는 시점의 시간이 기록된다. 또 다른 일반적인 예는 각 행마다 ‘일련번호’를 생성하는 것이다. PostgreSQL에서는 보통 다음과 같이 구현한다:

CREATE TABLE products (
    product_no integer DEFAULT nextval('products_product_no_seq'),
    ...
);

여기서 nextval() 함수는 시퀀스 객체로부터 연속된 값을 제공한다 (9.17절 참조). 이런 패턴은 매우 자주 사용되므로 다음과 같은 특별한 단축 문법을 제공한다:

CREATE TABLE products (
    product_no SERIAL,
    ...
);

SERIAL 단축 문법에 대한 자세한 내용은 8.1.4절에서 다룬다.

5.3. Identity 컬럼

Identity 컬럼은 내부 시퀀스를 통해 자동으로 값을 생성하는 특별한 컬럼이다. 주로 키 값을 생성하는 데 사용한다.

Identity 컬럼을 생성하려면 CREATE TABLE 문에서 GENERATED ... AS IDENTITY 구문을 사용한다. 예를 들면 다음과 같다:

CREATE TABLE people (
    id bigint GENERATED ALWAYS AS IDENTITY,
    ...,
);

또는 다음과 같이 작성할 수도 있다:

CREATE TABLE people (
    id bigint GENERATED BY DEFAULT AS IDENTITY,
    ...,
);

자세한 내용은 CREATE TABLE을 참고한다.

Identity 컬럼이 있는 테이블에 INSERT 명령을 실행할 때 Identity 컬럼의 값을 명시적으로 지정하지 않으면, 내부 시퀀스가 자동으로 값을 생성한다. 예를 들어, 위의 정의와 적절한 추가 컬럼이 있다고 가정하면 다음과 같이 작성할 수 있다:

INSERT INTO people (name, address) VALUES ('A', 'foo');
INSERT INTO people (name, address) VALUES ('B', 'bar');

이 경우 id 컬럼의 값은 1부터 시작하여 자동으로 생성되며, 결과는 다음과 같다:

 id | name | address
----+------+---------
  1 | A    | foo
  2 | B    | bar

시퀀스가 생성한 값을 명시적으로 요청하려면 값 대신 DEFAULT 키워드를 사용할 수 있다:

INSERT INTO people (id, name, address) VALUES (DEFAULT, 'C', 'baz');

마찬가지로 UPDATE 명령에서도 DEFAULT 키워드를 사용할 수 있다.

이처럼 Identity 컬럼은 기본값이 있는 컬럼과 비슷하게 동작한다.

컬럼 정의의 ALWAYS와 BY DEFAULT 구문은 INSERT와 UPDATE 명령에서 사용자가 명시적으로 지정한 값을 어떻게 처리할지 결정한다. INSERT 명령에서 ALWAYS를 선택하면, OVERRIDING SYSTEM VALUE를 지정한 경우에만 사용자가 지정한 값을 허용한다. BY DEFAULT를 선택하면 사용자가 지정한 값이 우선한다. 따라서 BY DEFAULT는 기본값과 더 유사하게 동작하며, 명시적인 값으로 기본값을 덮어쓸 수 있다. 반면 ALWAYS는 실수로 명시적인 값을 삽입하는 것을 방지하는 데 더 효과적이다.

Identity 컬럼의 데이터 타입은 시퀀스가 지원하는 데이터 타입 중 하나여야 한다. (자세한 내용은 CREATE SEQUENCE를 참고한다.) Identity 컬럼을 생성할 때 관련 시퀀스의 속성을 지정하거나(CREATE TABLE 참고), 나중에 변경할 수 있다(ALTER TABLE 참고).

Identity 컬럼은 자동으로 NOT NULL 속성이 부여된다. 하지만 Identity 컬럼이 유일성을 보장하는 것은 아니다. (일반적으로 시퀀스는 유일한 값을 반환하지만, 시퀀스를 초기화하거나 위에서 설명한 대로 수동으로 값을 삽입할 수 있다.) 유일성을 강제하려면 PRIMARY KEY나 UNIQUE 제약조건을 사용해야 한다.

테이블 상속 계층 구조에서 자식 테이블의 Identity 컬럼과 그 속성은 부모 테이블과 독립적이다. 자식 테이블은 부모 테이블의 Identity 컬럼이나 속성을 자동으로 상속하지 않는다. INSERT나 UPDATE 중에는 명령문에서 지정한 테이블의 해당 컬럼이 Identity 컬럼인 경우에만 Identity 컬럼으로 처리되며, 해당하는 Identity 속성이 적용된다.

파티션은 파티션된 테이블로부터 Identity 컬럼을 상속한다. 파티션은 자체적인 Identity 컬럼을 가질 수 없다. 특정 Identity 컬럼의 속성은 파티션 계층 구조의 모든 파티션에서 일관성을 유지한다.

5.4. 생성된 컬럼

생성된 컬럼은 다른 컬럼들로부터 계산되는 특별한 컬럼이다. 테이블에서 뷰가 하는 역할을 컬럼 수준에서 수행한다고 볼 수 있다. 생성된 컬럼은 저장형과 가상형 두 가지로 나뉜다. 저장형 생성 컬럼은 데이터를 쓸 때(삽입 또는 갱신) 계산되며 일반 컬럼처럼 저장 공간을 차지한다. 가상형 생성 컬럼은 저장 공간을 차지하지 않으며 읽을 때 계산된다. 따라서 가상형 생성 컬럼은 뷰와 비슷하고, 저장형 생성 컬럼은 구체화된 뷰와 비슷하다(단, 항상 자동으로 갱신된다는 차이가 있다). PostgreSQL은 현재 저장형 생성 컬럼만 구현하고 있다.

생성된 컬럼을 만들려면 CREATE TABLE에서 GENERATED ALWAYS AS 구문을 사용한다. 예를 들면 다음과 같다:

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

저장형 생성 컬럼을 선택하려면 STORED 키워드를 반드시 지정해야 한다. 자세한 내용은 CREATE TABLE을 참조한다.

생성된 컬럼은 직접 쓸 수 없다. INSERT나 UPDATE 명령에서 생성된 컬럼에 값을 지정할 수 없지만, DEFAULT 키워드는 사용할 수 있다.

기본값이 있는 컬럼과 생성된 컬럼의 차이점을 살펴보자: - 컬럼 기본값은 다른 값이 제공되지 않았을 때 행이 처음 삽입될 때 한 번만 계산된다. - 생성된 컬럼은 행이 변경될 때마다 갱신되며 값을 직접 지정할 수 없다. - 컬럼 기본값은 테이블의 다른 컬럼을 참조할 수 없다. - 생성 표현식은 일반적으로 다른 컬럼을 참조한다. - 컬럼 기본값은 random()이나 현재 시간을 참조하는 함수와 같은 휘발성 함수를 사용할 수 있다. - 생성된 컬럼은 휘발성 함수를 사용할 수 없다.

생성된 컬럼과 이를 포함하는 테이블의 정의에는 다음과 같은 제약사항이 적용된다:

생성된 컬럼 사용 시 다음 사항도 고려해야 한다:

5.5. 제약조건

데이터 타입은 테이블에 저장할 수 있는 데이터의 종류를 제한하는 방법이다. 하지만 많은 애플리케이션에서 데이터 타입만으로는 충분한 제약을 걸기 어렵다. 예를 들어, 상품 가격을 담는 컬럼은 양수 값만 받아들여야 하는데, 양수만 허용하는 표준 데이터 타입은 존재하지 않는다. 또 다른 문제는 특정 컬럼의 데이터를 다른 컬럼이나 행과 연관지어 제약을 걸어야 하는 경우다. 예를 들어, 상품 정보를 담은 테이블에서는 각 상품 번호당 단 하나의 행만 존재해야 한다.

이러한 요구사항을 충족하기 위해 SQL은 컬럼과 테이블에 제약조건을 정의할 수 있게 한다. 제약조건을 사용하면 테이블의 데이터를 원하는 만큼 세밀하게 제어할 수 있다. 사용자가 제약조건에 위배되는 데이터를 컬럼에 저장하려고 시도하면 오류가 발생한다. 이는 기본값 정의에서 가져온 값에도 동일하게 적용된다.

5.5.1. 체크 제약조건(Check Constraints)

체크 제약조건은 가장 일반적인 형태의 제약조건이다. 특정 컬럼의 값이 불리언(참/거짓) 표현식을 만족하도록 지정할 수 있다. 예를 들어, 상품 가격이 양수여야 한다는 조건을 다음과 같이 설정할 수 있다:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

위 예시에서 볼 수 있듯이, 제약조건은 기본값 정의와 마찬가지로 데이터 타입 뒤에 위치한다. 기본값과 제약조건은 순서에 관계없이 나열할 수 있다. 체크 제약조건은 CHECK 키워드와 괄호 안의 표현식으로 구성된다. 제약조건 표현식은 해당 컬럼을 포함해야 의미가 있다.

제약조건에 이름을 부여할 수도 있다. 이는 오류 메시지를 명확하게 하고, 나중에 제약조건을 수정할 때 참조하기 쉽게 만든다. 구문은 다음과 같다:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

이름이 있는 제약조건을 지정하려면 CONSTRAINT 키워드 다음에 식별자를 쓰고 제약조건 정의를 작성한다. (이름을 따로 지정하지 않으면 시스템이 자동으로 이름을 생성한다.)

체크 제약조건은 여러 컬럼을 참조할 수도 있다. 예를 들어, 정상 가격과 할인 가격을 저장하면서 할인 가격이 정상 가격보다 낮아야 한다는 조건을 설정하고 싶다면:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

처음 두 제약조건은 이미 살펴본 형태와 같다. 세 번째 제약조건은 새로운 구문을 사용한다. 특정 컬럼에 연결되지 않고 쉼표로 구분된 컬럼 목록에 독립적인 항목으로 나타난다. 컬럼 정의와 이러한 제약조건 정의는 순서에 관계없이 나열할 수 있다.

앞의 두 제약조건은 컬럼 제약조건이고, 세 번째는 테이블 제약조건이다. 컬럼 제약조건은 테이블 제약조건으로 작성할 수 있지만, 그 반대는 항상 가능하지 않다. 컬럼 제약조건은 해당 컬럼만 참조해야 하기 때문이다. (PostgreSQL은 이 규칙을 강제하지 않지만, 다른 데이터베이스 시스템과의 호환성을 위해 이 규칙을 따르는 것이 좋다.) 위의 예제는 다음과 같이 작성할 수도 있다:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

또는 이렇게도 가능하다:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);

이는 개발자의 선호도에 따라 선택할 수 있다.

테이블 제약조건에도 컬럼 제약조건과 같은 방식으로 이름을 지정할 수 있다:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

주의할 점은 체크 제약조건은 검사 표현식이 참이거나 null 값일 때 만족된다는 것이다. 대부분의 표현식은 피연산자 중 하나가 null이면 null을 반환하므로, 제약조건이 걸린 컬럼에 null 값이 들어가는 것을 막지 못한다. 컬럼에 null 값을 허용하지 않으려면 다음 절에서 설명할 not-null 제약조건을 사용해야 한다.

참고사항

PostgreSQL의 CHECK 제약조건은 검사 대상이 되는 새로운 행이나 갱신된 행 이외의 다른 테이블 데이터를 참조할 수 없다. 이러한 규칙을 위반하는 CHECK 제약조건이 간단한 테스트에서는 정상 작동하는 것처럼 보일 수 있다. 하지만 관련된 다른 행들이 나중에 변경되어 제약조건이 거짓이 되는 상황을 막을 수 없다. 이는 데이터베이스 덤프와 복원 과정에서 오류를 일으킬 수 있다. 전체 데이터베이스가 제약조건과 일관성을 유지하더라도, 데이터 행들이 제약조건을 만족하는 순서대로 로드되지 않으면 복원 작업이 실패할 수 있다. 따라서 행 간 또는 테이블 간 제약을 표현할 때는 가능한 UNIQUE, EXCLUDE, FOREIGN KEY 제약조건을 사용하는 것이 바람직하다.

만약 지속적인 일관성 보장이 아닌, 행 삽입 시 다른 행과의 일회성 검사가 필요하다면 트리거를 사용하여 구현할 수 있다. 이 방식은 pg_dump가 데이터 복원이 완료된 후에 트리거를 다시 설치하기 때문에, 덤프/복원 과정에서 검사가 실행되지 않아 앞서 언급한 문제를 피할 수 있다.

참고사항

PostgreSQL은 CHECK 제약조건이 불변(immutable)하다고 가정한다. 즉, 동일한 입력 행에 대해 항상 같은 결과를 반환할 것으로 간주한다. 이러한 가정 때문에 PostgreSQL은 행을 삽입하거나 갱신할 때만 CHECK 제약조건을 검사하고, 다른 시점에는 검사하지 않는다. (앞서 언급한 다른 테이블 데이터 참조에 대한 경고는 실제로 이 제한사항의 특수한 경우다.)

이 가정을 위반하는 대표적인 예시는 CHECK 표현식에서 사용자 정의 함수를 참조하고, 이후 해당 함수의 동작을 변경하는 경우다. PostgreSQL은 이러한 변경을 막지 않지만, 테이블에 있는 행들이 CHECK 제약조건을 위반하게 되었는지 감지하지 못한다. 이는 이후 데이터베이스 덤프와 복원 과정에서 오류를 일으킬 수 있다. 이러한 상황에 대한 권장 처리 방법은 다음과 같다:

  1. ALTER TABLE을 사용하여 제약조건을 삭제한다
  2. 함수 정의를 수정한다
  3. 제약조건을 다시 추가한다

이렇게 하면 모든 테이블 행에 대해 제약조건을 다시 검사할 수 있다.

5.5.2. NOT NULL 제약조건

NOT NULL 제약조건은 컬럼이 NULL 값을 가질 수 없도록 지정한다. 아래는 기본적인 문법 예제이다:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

NOT NULL 제약조건은 항상 컬럼 제약조건으로 작성한다. NOT NULL 제약조건은 CHECK (*컬럼명* IS NOT NULL) 형태의 검사 제약조건을 만드는 것과 기능적으로 동일하다. 하지만 PostgreSQL에서는 명시적인 NOT NULL 제약조건을 사용하는 것이 더 효율적이다. 다만 이 방식으로 생성한 NOT NULL 제약조건에는 명시적인 이름을 지정할 수 없다는 단점이 있다.

하나의 컬럼에 여러 제약조건을 지정할 수 있다. 제약조건을 순서대로 나열하면 된다:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

제약조건의 작성 순서는 중요하지 않다. 이 순서가 제약조건의 검사 순서를 결정하지는 않는다.

NOT NULL 제약조건에는 NULL이라는 반대 개념의 제약조건이 있다. NULL 제약조건은 해당 컬럼이 반드시 NULL 값을 가져야 한다는 의미가 아니다. 이는 무의미한 제약이 될 것이다. 대신 컬럼이 NULL 값을 가질 수 있다는 기본 동작을 명시적으로 선택하는 것이다. NULL 제약조건은 SQL 표준에 포함되어 있지 않으므로 이식 가능한 애플리케이션에서는 사용하지 않는 것이 좋다. PostgreSQL은 다른 데이터베이스 시스템과의 호환성을 위해 이 기능을 추가했다. 일부 사용자는 스크립트 파일에서 제약조건을 쉽게 전환할 수 있다는 점 때문에 이 기능을 선호한다. 예를 들어 다음과 같이 시작할 수 있다:

CREATE TABLE products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
);

이후 원하는 위치에 NOT 키워드를 추가하면 된다.

팁

대부분의 데이터베이스 설계에서 컬럼은 NOT NULL로 지정하는 것이 바람직하다.

5.5.3. 고유 제약조건 (Unique Constraints)

고유 제약조건은 테이블의 특정 컬럼 또는 컬럼 그룹이 모든 행에서 유일한 값을 가지도록 보장한다. 고유 제약조건의 문법은 다음과 같다:

컬럼 제약조건으로 작성하는 경우:

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

테이블 제약조건으로 작성하는 경우:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no)
);

여러 컬럼에 대한 고유 제약조건을 정의하려면, 테이블 제약조건으로 작성하고 컬럼명을 쉼표로 구분한다:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

이는 지정된 컬럼들의 값 조합이 전체 테이블에서 유일해야 함을 의미한다. 단, 개별 컬럼은 반드시 유일할 필요는 없으며, 일반적으로도 유일하지 않다.

다음과 같이 고유 제약조건에 사용자 정의 이름을 부여할 수 있다:

CREATE TABLE products (
    product_no integer CONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);

고유 제약조건을 추가하면 해당 컬럼 또는 컬럼 그룹에 대해 자동으로 고유 B-트리 인덱스가 생성된다. 일부 행에만 적용되는 고유성 제한은 고유 제약조건으로 작성할 수 없지만, 고유 부분 인덱스(partial index)를 생성하여 이러한 제한을 구현할 수 있다.

일반적으로 고유 제약조건은 제약조건에 포함된 모든 컬럼의 값이 동일한 행이 둘 이상 있을 때 위반된다. 기본적으로 이 비교에서 두 개의 NULL 값은 동일하지 않은 것으로 간주된다. 따라서 고유 제약조건이 있더라도 제약된 컬럼 중 하나 이상에 NULL 값을 포함하는 중복 행을 저장할 수 있다. 이러한 동작은 다음과 같이 NULLS NOT DISTINCT 절을 추가하여 변경할 수 있다:

CREATE TABLE products (
    product_no integer UNIQUE NULLS NOT DISTINCT,
    name text,
    price numeric
);

또는:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE NULLS NOT DISTINCT (product_no)
);

기본 동작은 NULLS DISTINCT를 사용하여 명시적으로 지정할 수 있다. SQL 표준에 따르면 고유 제약조건에서 NULL 처리는 구현에 따라 다르며, 다른 구현에서는 다른 동작을 보일 수 있다. 따라서 이식 가능한 애플리케이션을 개발할 때는 이 점에 주의해야 한다.

5.5.4. 기본 키 제약조건

기본 키 제약조건은 테이블의 각 행을 고유하게 식별할 수 있는 컬럼 또는 컬럼 그룹을 지정한다. 기본 키로 지정된 값은 반드시 고유해야 하며 NULL 값을 포함할 수 없다. 아래 두 테이블 정의는 동일한 데이터 구조를 생성한다:

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

기본 키는 여러 컬럼을 포함할 수 있다. 기본 키의 문법은 고유 제약조건과 유사하다:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

기본 키를 추가하면 다음과 같은 작업이 자동으로 수행된다: 1. 기본 키로 지정된 컬럼 또는 컬럼 그룹에 대해 고유 B-트리 인덱스가 생성된다 2. 해당 컬럼들은 자동으로 NOT NULL 속성이 부여된다

한 테이블에는 단 하나의 기본 키만 존재할 수 있다. 고유하면서 NULL이 아닌 제약조건은 여러 개 만들 수 있지만, 이는 기본 키와 기능적으로 유사하더라도 실제 기본 키로 지정할 수 있는 것은 하나뿐이다. 관계형 데이터베이스 이론에 따르면 모든 테이블은 기본 키를 가져야 한다. PostgreSQL은 이 규칙을 강제하지 않지만, 이 규칙을 따르는 것이 좋다.

기본 키는 문서화 목적과 클라이언트 애플리케이션 개발에 매우 유용하다. 예를 들어, GUI 애플리케이션에서 행 값을 수정할 때 각 행을 고유하게 식별하기 위해 기본 키 정보가 필요하다. 데이터베이스 시스템도 선언된 기본 키를 다양한 방식으로 활용한다. 대표적인 예로, 다른 테이블에서 외래 키로 참조할 때 기본 키가 기본 대상 컬럼이 된다.

5.5.5. 외래 키 제약조건

외래 키 제약조건은 한 테이블의 컬럼(또는 컬럼 그룹)이 다른 테이블의 행에 있는 값과 일치해야 한다고 지정한다. 이를 통해 두 관련 테이블 간의 *참조 무결성*을 유지한다.

지금까지 여러 번 사용했던 제품 테이블을 예로 들어보자:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

이제 이 제품들의 주문을 저장하는 테이블이 있다고 가정한다. 주문 테이블에는 실제로 존재하는 제품의 주문만 포함되도록 보장하고 싶다. 이를 위해 주문 테이블에 제품 테이블을 참조하는 외래 키 제약조건을 정의한다:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

이제 제품 테이블에 없는 product_no 값으로는 주문을 생성할 수 없다(NULL 값은 제외).

이런 상황에서 주문 테이블을 참조하는 테이블이라 하고, 제품 테이블을 참조되는 테이블이라고 한다. 마찬가지로 참조하는 컬럼과 참조되는 컬럼도 있다.

위 명령은 다음과 같이 줄여서 작성할 수도 있다:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

컬럼 목록을 생략하면 참조되는 테이블의 기본 키가 참조되는 컬럼으로 사용된다.

일반적인 방식으로 외래 키 제약조건에 이름을 지정할 수 있다.

외래 키는 여러 컬럼의 그룹을 제약하고 참조할 수도 있다. 이 경우 테이블 제약조건 형식으로 작성해야 한다. 다음은 인위적인 문법 예제다:

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

제약되는 컬럼의 수와 타입은 참조되는 컬럼의 수와 타입과 일치해야 한다.

때로는 외래 키 제약조건의 “다른 테이블”이 동일한 테이블인 것이 유용할 수 있다. 이를 자체 참조 외래 키라고 한다. 예를 들어, 테이블의 행으로 트리 구조의 노드를 표현하고 싶다면 다음과 같이 작성할 수 있다:

CREATE TABLE tree (
    node_id integer PRIMARY KEY,
    parent_id integer REFERENCES tree,
    name text,
    ...
);

최상위 노드는 parent_id가 NULL이 되고, NULL이 아닌 parent_id 항목은 테이블의 유효한 행을 참조해야 한다.

하나의 테이블은 여러 개의 외래 키 제약조건을 가질 수 있다. 이는 테이블 간의 다대다 관계를 구현할 때 사용된다. 제품과 주문에 대한 테이블이 있고, 이제 하나의 주문에 여러 제품을 포함할 수 있게 하고 싶다면(위의 구조로는 불가능했다) 다음과 같은 테이블 구조를 사용할 수 있다:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

마지막 테이블에서 기본 키가 외래 키와 겹치는 것에 주목하자.

외래 키는 어떤 제품과도 관련이 없는 주문의 생성을 막는다. 하지만 주문이 참조하는 제품을 제품이 생성된 후에 삭제하려고 하면 어떻게 될까? SQL은 이런 상황도 처리할 수 있다. 직관적으로 다음과 같은 옵션이 있다:

이를 설명하기 위해 위의 다대다 관계 예제에서 다음과 같은 정책을 구현해보자: 누군가 아직 주문(order_items를 통해)에서 참조하고 있는 제품을 삭제하려고 할 때는 이를 금지한다. 누군가 주문을 삭제하면 주문 항목도 함께 삭제된다:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

삭제 제한과 연쇄 삭제는 가장 일반적인 두 가지 옵션이다. RESTRICT는 참조되는 행의 삭제를 방지한다. NO ACTION은 제약조건이 확인될 때 참조하는 행이 여전히 존재하면 오류를 발생시킨다. 이는 아무것도 지정하지 않았을 때의 기본 동작이다. (이 두 선택의 본질적인 차이점은 NO ACTION은 나중에 트랜잭션에서 확인을 연기할 수 있는 반면, RESTRICT는 그렇지 않다는 것이다.) CASCADE는 참조되는 행이 삭제될 때 이를 참조하는 행도 자동으로 삭제되도록 지정한다.

다른 두 가지 옵션으로 SET NULL과 SET DEFAULT가 있다. 이들은 참조되는 행이 삭제될 때 참조하는 행의 참조 컬럼을 각각 null이나 기본값으로 설정한다. 단, 이러한 옵션들이 제약조건을 무시하는 것은 아니다. 예를 들어, 어떤 동작이 SET DEFAULT를 지정하지만 기본값이 외래 키 제약조건을 만족하지 않는다면 작업은 실패한다.

적절한 ON DELETE 동작의 선택은 관련 테이블이 나타내는 객체의 종류에 따라 다르다. 참조하는 테이블이 참조되는 테이블이 나타내는 것의 구성 요소를 나타내고 독립적으로 존재할 수 없다면 CASCADE가 적절할 수 있다. 두 테이블이 독립적인 객체를 나타낸다면 RESTRICT나 NO ACTION이 더 적절하다. 이 경우 두 객체를 모두 삭제하려는 애플리케이션은 이를 명시적으로 처리하고 두 개의 삭제 명령을 실행해야 한다.

위의 예에서 주문 항목은 주문의 일부이므로 주문이 삭제될 때 자동으로 삭제되는 것이 편리하다. 하지만 제품과 주문은 서로 다른 것이므로, 제품을 삭제할 때 일부 주문 항목이 자동으로 삭제되는 것은 문제가 될 수 있다. SET NULL이나 SET DEFAULT 동작은 외래 키 관계가 선택적 정보를 나타내는 경우에 적절할 수 있다. 예를 들어, 제품 테이블에 제품 관리자에 대한 참조가 있고 제품 관리자 항목이 삭제된다면, 제품의 제품 관리자를 null이나 기본값으로 설정하는 것이 유용할 수 있다.

SET NULL과 SET DEFAULT 동작은 설정할 컬럼 목록을 지정할 수 있다. 일반적으로는 외래 키 제약조건의 모든 컬럼이 설정되지만, 일부 특별한 경우에는 일부 컬럼만 설정하는 것이 유용하다. 다음 예제를 살펴보자:

CREATE TABLE tenants (
    tenant_id integer PRIMARY KEY
);

CREATE TABLE users (
    tenant_id integer REFERENCES tenants ON DELETE CASCADE,
    user_id integer NOT NULL,
    PRIMARY KEY (tenant_id, user_id)
);

CREATE TABLE posts (
    tenant_id integer REFERENCES tenants ON DELETE CASCADE,
    post_id integer NOT NULL,
    author_id integer,
    PRIMARY KEY (tenant_id, post_id),
    FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL (author_id)
);

컬럼 지정이 없다면 외래 키는 tenant_id 컬럼도 null로 설정하려 할 것이지만, 이 컬럼은 여전히 기본 키의 일부로 필요하다.

ON DELETE와 유사하게 ON UPDATE도 있다. 이는 참조되는 컬럼이 변경(갱신)될 때 호출된다. 가능한 동작은 동일하지만, SET NULL과 SET DEFAULT에는 컬럼 목록을 지정할 수 없다. 이 경우 CASCADE는 참조되는 컬럼의 갱신된 값을 참조하는 행에 복사해야 한다는 의미다.

일반적으로 참조하는 행의 참조 컬럼 중 하나가 null이면 외래 키 제약조건을 만족할 필요가 없다. 외래 키 선언에 MATCH FULL을 추가하면, 참조하는 행은 모든 참조 컬럼이 null인 경우에만 제약조건을 피할 수 있다(따라서 null과 null이 아닌 값이 섞여 있으면 MATCH FULL 제약조건을 반드시 실패한다). 참조하는 행이 외래 키 제약조건을 피하지 못하게 하려면 참조하는 컬럼을 NOT NULL로 선언하면 된다.

외래 키는 기본 키이거나 고유 제약조건을 형성하거나 비부분 고유 인덱스의 컬럼인 컬럼들만 참조할 수 있다. 이는 참조되는 컬럼에 항상 참조하는 행이 일치하는지 효율적으로 찾을 수 있는 인덱스가 있다는 것을 의미한다. 참조되는 테이블에서 행을 삭제하거나 참조되는 컬럼을 갱신할 때는 이전 값과 일치하는 행을 찾기 위해 참조하는 테이블을 스캔해야 하므로, 참조하는 컬럼에도 인덱스를 만드는 것이 좋다. 이것이 항상 필요한 것은 아니고 인덱스를 만드는 방법도 여러 가지가 있기 때문에, 외래 키 제약조건을 선언한다고 해서 참조하는 컬럼에 자동으로 인덱스가 생성되지는 않는다.

데이터를 갱신하고 삭제하는 것에 대한 더 자세한 정보는 6장에서 확인할 수 있다. 또한 [CREATE TABLE](https://www.postgresql.org

5.5.6. 제외 제약조건(Exclusion Constraints)

제외 제약조건은 지정한 컬럼이나 표현식에서 임의의 두 행을 비교할 때, 최소한 하나의 연산자 비교가 거짓(false)이나 널(null)을 반환하도록 보장한다. 문법은 다음과 같다:

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

자세한 내용은 CREATE TABLE ... CONSTRAINT ... EXCLUDE 문서를 참고한다.

제외 제약조건을 추가하면 제약조건 선언에서 지정한 타입의 인덱스가 자동으로 생성된다.

5.6. 시스템 컬럼

모든 테이블은 시스템이 자동으로 정의하는 여러 *시스템 컬럼*을 포함한다. 따라서 사용자가 정의하는 컬럼 이름으로 이러한 이름을 사용할 수 없다. (이는 해당 이름이 예약어인지 여부와는 별개의 제약사항이다. 이름을 따옴표로 감싸더라도 이러한 제약을 피할 수 없다.) 이러한 컬럼들에 대해 깊이 걱정할 필요는 없으며, 존재한다는 사실만 알고 있으면 충분하다.

tableoid # 해당 행을 포함하는 테이블의 OID이다. 이 컬럼은 특히 파티션 테이블(5.12절)이나 상속 계층(5.11절)에서 선택하는 쿼리에서 유용하다. 이 컬럼이 없다면 특정 행이 어떤 개별 테이블에서 왔는지 파악하기 어렵기 때문이다. tableoid는 pg_class의 oid 컬럼과 조인하여 테이블 이름을 얻을 수 있다.

xmin # 이 행 버전을 삽입한 트랜잭션의 식별자(트랜잭션 ID)이다. (행 버전은 행의 개별 상태를 의미한다. 동일한 논리적 행에 대한 각 업데이트는 새로운 행 버전을 생성한다.)

cmin # 삽입 트랜잭션 내의 커맨드 식별자(0부터 시작)이다.

xmax # 삭제 트랜잭션의 식별자(트랜잭션 ID)이다. 삭제되지 않은 행 버전의 경우 0이다. 보이는 행 버전에서 이 컬럼이 0이 아닐 수 있다. 이는 일반적으로 삭제 트랜잭션이 아직 커밋되지 않았거나 삭제 시도가 롤백되었음을 나타낸다.

cmax # 삭제 트랜잭션 내의 커맨드 식별자이다. 삭제되지 않은 경우 0이다.

ctid # 테이블 내 행 버전의 물리적 위치이다. ctid를 사용하면 행 버전을 매우 빠르게 찾을 수 있지만, 행이 업데이트되거나 VACUUM FULL에 의해 이동되면 행의 ctid가 변경된다. 따라서 ctid는 장기적인 행 식별자로는 적합하지 않다. 논리적 행을 식별하려면 기본 키를 사용해야 한다.

트랜잭션 식별자도 32비트 값이다. 오래 운영된 데이터베이스에서는 트랜잭션 ID가 순환할 수 있다. 적절한 유지보수 절차를 따른다면 이는 치명적인 문제가 되지 않는다. 자세한 내용은 24장을 참조한다. 그러나 장기적으로(10억 건 이상의 트랜잭션) 트랜잭션 ID의 고유성에 의존하는 것은 현명하지 않다.

커맨드 식별자 역시 32비트 값이다. 이로 인해 단일 트랜잭션 내에서 232(40억) SQL 커맨드라는 하드 제한이 생긴다. 실제로 이 제한은 문제가 되지 않는다. SQL 커맨드 수에 대한 제한이지 처리되는 행의 수에 대한 제한이 아니기 때문이다. 또한 데이터베이스 내용을 실제로 수정하는 커맨드만 커맨드 식별자를 소비한다.

5.7. 테이블 수정하기

테이블을 생성한 후 실수를 발견하거나 애플리케이션 요구사항이 변경되는 경우가 있다. 이때 테이블을 삭제하고 다시 생성하는 방법을 사용할 수 있다. 하지만 이미 데이터가 존재하거나 다른 데이터베이스 객체(예: 외래 키 제약조건)가 해당 테이블을 참조하고 있다면 이는 적절한 방법이 아니다. 이를 위해 PostgreSQL은 기존 테이블을 수정할 수 있는 다양한 커맨드를 제공한다. 여기서 주목할 점은 테이블의 데이터를 변경하는 것이 아니라, 테이블의 정의나 구조를 변경하는 것이다.

다음과 같은 작업을 수행할 수 있다:

이러한 모든 작업은 ALTER TABLE 커맨드를 사용하여 수행한다. 더 자세한 내용은 참조 페이지에서 확인할 수 있다.

5.7.1. 컬럼 추가하기

컬럼을 추가하려면 다음과 같은 커맨드를 사용한다:

ALTER TABLE products ADD COLUMN description text;

새로 추가한 컬럼은 지정한 기본값으로 초기화된다. DEFAULT 절을 지정하지 않으면 null 값이 기본값이 된다.

팁

PostgreSQL 11 버전부터 상수 기본값을 가진 컬럼을 추가할 때 ALTER TABLE 문을 실행하는 시점에 테이블의 모든 행을 즉시 갱신하지 않는다. 대신 다음에 해당 행에 접근할 때 기본값을 반환하며, 테이블이 다시 작성될 때 이 값을 적용한다. 이로 인해 대용량 테이블에서도 ALTER TABLE 명령을 매우 빠르게 실행할 수 있다.

그러나 기본값이 clock_timestamp()와 같은 변동성 함수인 경우, ALTER TABLE 실행 시점의 계산값으로 모든 행을 갱신해야 한다. 특히 나중에 대부분의 컬럼 값을 기본값이 아닌 다른 값으로 채울 계획이라면, 잠재적으로 긴 갱신 작업을 피하기 위해 다음과 같은 방법을 고려할 수 있다:

  1. 기본값 없이 컬럼을 추가한다
  2. UPDATE 문으로 원하는 값을 삽입한다
  3. 아래에서 설명하는 방법으로 필요한 기본값을 추가한다

다음과 같이 컬럼을 추가할 때 제약조건도 함께 정의할 수 있다:

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

실제로 CREATE TABLE에서 사용할 수 있는 모든 컬럼 설정 옵션을 여기서도 사용할 수 있다. 단, 기본값이 지정한 제약조건을 만족해야 한다. 그렇지 않으면 ADD 작업이 실패한다. 다른 방법으로는, 새로운 컬럼에 올바른 값을 채운 후에 제약조건을 추가할 수도 있다.

5.7.2. 컬럼 삭제하기

컬럼을 삭제하기 위해 다음과 같은 명령을 사용한다:

ALTER TABLE products DROP COLUMN description;

이 명령을 실행하면 해당 컬럼의 모든 데이터가 즉시 삭제된다. 또한 해당 컬럼과 관련된 테이블 제약 조건도 함께 삭제된다. 그러나 다른 테이블의 외래 키 제약 조건이 이 컬럼을 참조하고 있다면, PostgreSQL은 해당 제약 조건을 자동으로 삭제하지 않는다. 컬럼에 종속된 모든 요소를 함께 삭제하려면 CASCADE 옵션을 추가한다:

ALTER TABLE products DROP COLUMN description CASCADE;

이 메커니즘에 대한 자세한 설명은 5.15절 의존성 추적을 참조한다.

5.7.3. 제약 조건 추가하기

테이블에 제약 조건을 추가할 때는 테이블 제약 조건 문법을 사용한다. 아래 예제를 살펴보자:

ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

테이블 제약 조건으로 작성할 수 없는 NOT NULL 제약 조건을 추가하려면 다음 문법을 사용한다:

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

제약 조건을 추가하면 즉시 검사가 실행된다. 따라서 제약 조건을 추가하기 전에 테이블의 데이터가 해당 제약 조건을 만족하는지 확인해야 한다.

5.7.4. 제약 조건 제거하기

제약 조건을 제거하려면 먼저 제약 조건의 이름을 알아야 한다. 직접 이름을 지정했다면 쉽게 찾을 수 있다. 시스템이 자동으로 생성한 이름이라면 먼저 그 이름을 찾아야 한다. psql 명령어 \d tablename을 사용하면 도움이 된다. 다른 데이터베이스 도구들도 테이블 세부 정보를 확인하는 방법을 제공한다.

제약 조건을 제거하는 명령어는 다음과 같다:

ALTER TABLE products DROP CONSTRAINT some_name;

(시스템이 생성한 $2와 같은 제약 조건 이름을 사용할 때는 유효한 식별자로 만들기 위해 반드시 큰따옴표로 묶어야 한다.)

컬럼을 삭제할 때와 마찬가지로, 다른 객체가 의존하는 제약 조건을 삭제할 때는 CASCADE 옵션을 추가해야 한다. 예를 들어 외래 키 제약 조건은 참조하는 컬럼의 고유 키나 기본 키 제약 조건에 의존한다.

이 방식은 NOT NULL 제약 조건을 제외한 모든 제약 조건 타입에 동일하게 적용된다. NOT NULL 제약 조건을 제거하려면 다음 명령어를 사용한다:

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

(NOT NULL 제약 조건은 이름이 없다는 점을 기억하자.)

5.7.5. 컬럼의 기본값 변경하기

컬럼에 새로운 기본값을 지정하려면 다음과 같은 명령을 사용한다:

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

이 명령은 테이블의 기존 데이터에는 영향을 주지 않는다. 이후 실행되는 INSERT 명령에서 사용할 기본값만 변경한다.

기본값을 제거하려면 다음 명령을 사용한다:

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

이 명령은 기본값을 null로 설정하는 것과 동일한 효과를 가진다. 따라서 기존에 기본값이 정의되어 있지 않은 컬럼에 대해 이 명령을 실행해도 오류가 발생하지 않는다. 기본값은 암묵적으로 null 값을 가지기 때문이다.

5.7.6. 컬럼의 데이터 타입 변경하기

컬럼의 데이터 타입을 다른 형식으로 변경하려면 다음과 같은 명령을 사용한다:

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

이 명령은 기존 컬럼의 모든 데이터가 암시적 형변환으로 새로운 타입으로 변환 가능한 경우에만 성공적으로 실행된다. 더 복잡한 변환이 필요한 경우에는 USING 절을 추가하여 기존 값을 새로운 값으로 계산하는 방법을 지정할 수 있다.

PostgreSQL은 컬럼의 기본값과 해당 컬럼과 관련된 모든 제약조건도 새로운 타입으로 변환을 시도한다. 하지만 이러한 변환 과정에서 실패하거나 예상치 못한 결과가 발생할 수 있다. 따라서 컬럼의 타입을 변경하기 전에 관련 제약조건을 먼저 삭제하고, 타입 변경 후에 적절히 수정된 제약조건을 다시 추가하는 것이 좋다.

5.7.7. 컬럼 이름 변경하기

컬럼의 이름을 변경하려면 다음 명령을 사용한다:

ALTER TABLE products RENAME COLUMN product_no TO product_number;

5.7.8. 테이블 이름 변경하기

테이블의 이름을 변경하려면 다음 명령을 사용한다:

ALTER TABLE products RENAME TO items;

5.8. 권한 관리

객체를 생성하면 해당 객체의 소유자가 지정된다. 일반적으로 객체를 생성한 역할(role)이 소유자가 된다. 대부분의 객체는 초기 상태에서 소유자(또는 슈퍼유저)만이 객체를 사용할 수 있다. 다른 역할이 객체를 사용하려면 *권한*을 부여해야 한다.

권한에는 여러 종류가 있다: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE, SET, ALTER SYSTEM, MAINTAIN. 객체의 타입(테이블, 함수 등)에 따라 적용 가능한 권한이 다르다. 각 권한의 의미는 아래에서 자세히 설명한다. 이후 섹션과 장에서도 이러한 권한이 어떻게 사용되는지 살펴볼 것이다.

객체를 수정하거나 삭제할 수 있는 권한은 객체의 소유자에게 기본적으로 부여된다. 이 권한은 별도로 부여하거나 회수할 수 없다. (단, 다른 권한과 마찬가지로 소유 역할의 구성원이 이 권한을 상속받을 수 있다. 자세한 내용은 21.3절을 참조한다.)

객체의 소유자를 변경하려면 객체에 적합한 ALTER 명령을 사용한다. 예를 들어:

ALTER TABLE table_name OWNER TO new_owner;

슈퍼유저는 항상 이 작업을 수행할 수 있다. 일반 역할은 객체의 현재 소유자이거나 소유 역할의 권한을 상속받았으며, 새로운 소유자 역할로 SET ROLE을 할 수 있는 경우에만 이 작업을 수행할 수 있다.

권한을 부여하려면 GRANT 명령을 사용한다. 예를 들어, joe라는 역할이 있고 accounts라는 테이블이 있다면, joe에게 accounts 테이블을 업데이트할 수 있는 권한을 다음과 같이 부여할 수 있다:

GRANT UPDATE ON accounts TO joe;

특정 권한 대신 ALL을 사용하면 해당 객체 타입에 관련된 모든 권한을 부여한다.

특별한 “역할” 이름인 PUBLIC을 사용하면 시스템의 모든 역할에게 권한을 부여할 수 있다. 또한, 데이터베이스 사용자가 많은 경우 권한 관리를 돕기 위해 “그룹” 역할을 설정할 수 있다. 자세한 내용은 21장을 참조한다.

이전에 부여한 권한을 회수하려면 REVOKE 명령을 사용한다:

REVOKE ALL ON accounts FROM PUBLIC;

일반적으로 객체의 소유자(또는 슈퍼유저)만이 객체에 대한 권한을 부여하거나 회수할 수 있다. 그러나 “with grant option”을 사용하여 권한을 부여하면, 권한을 받은 역할이 다시 다른 역할에게 권한을 부여할 수 있다. 이후 grant option이 회수되면, 해당 역할로부터 권한을 받은 모든 역할(직접 또는 간접적으로)이 권한을 잃게 된다. 자세한 내용은 GRANT 및 REVOKE 참조 페이지를 확인한다.

객체의 소유자는 자신의 일반 권한을 회수할 수 있다. 예를 들어, 자신을 포함한 모든 사용자에게 테이블을 읽기 전용으로 만들 수 있다. 그러나 소유자는 항상 모든 grant option을 가지고 있는 것으로 간주되므로, 언제든지 자신의 권한을 다시 부여할 수 있다.

사용 가능한 권한은 다음과 같다:

SELECT

테이블, 뷰, 구체화된 뷰 등에서 모든 컬럼 또는 특정 컬럼을 SELECT할 수 있다. 또한 COPY TO를 사용할 수 있다. 이 권한은 UPDATE, DELETE, MERGE에서 기존 컬럼 값을 참조할 때도 필요하다. 시퀀스의 경우 currval 함수를 사용할 수 있다. 대용량 객체의 경우 객체를 읽을 수 있다.

INSERT

테이블, 뷰 등에 새로운 행을 INSERT할 수 있다. 특정 컬럼에 대해 부여할 수 있으며, 이 경우 INSERT 명령에서 해당 컬럼만 값을 할당할 수 있다(다른 컬럼은 기본값을 받는다). 또한 COPY FROM을 사용할 수 있다.

UPDATE

테이블, 뷰 등에서 모든 컬럼 또는 특정 컬럼을 UPDATE할 수 있다. (실제로는 대부분의 UPDATE 명령이 SELECT 권한도 필요하다. 업데이트할 행을 결정하거나 새로운 값을 계산하기 위해 테이블 컬럼을 참조해야 하기 때문이다.) SELECT ... FOR UPDATE와 SELECT ... FOR SHARE도 최소한 하나의 컬럼에 대해 이 권한이 필요하다. 시퀀스의 경우 nextval과 setval 함수를 사용할 수 있다. 대용량 객체의 경우 객체를 쓰거나 잘라낼 수 있다.

DELETE

테이블, 뷰 등에서 행을 DELETE할 수 있다. (실제로는 대부분의 DELETE 명령이 SELECT 권한도 필요하다. 삭제할 행을 결정하기 위해 테이블 컬럼을 참조해야 하기 때문이다.)

TRUNCATE

테이블을 TRUNCATE할 수 있다.

REFERENCES

테이블 또는 특정 컬럼을 참조하는 외래 키 제약 조건을 생성할 수 있다.

TRIGGER

테이블, 뷰 등에 트리거를 생성할 수 있다.

CREATE

데이터베이스의 경우, 데이터베이스 내에 새로운 스키마와 출판물을 생성할 수 있으며, 신뢰할 수 있는 확장 기능을 설치할 수 있다.

스키마의 경우, 스키마 내에 새로운 객체를 생성할 수 있다. 기존 객체의 이름을 변경하려면 객체를 소유하고 있어야 하며, 해당 스키마에 대해 이 권한이 있어야 한다.

테이블스페이스의 경우, 테이블스페이스 내에 테이블, 인덱스, 임시 파일을 생성할 수 있으며, 해당 테이블스페이스를 기본 테이블스페이스로 하는 데이터베이스를 생성할 수 있다.

이 권한을 회수해도 기존 객체의 존재나 위치는 변경되지 않는다.

CONNECT

데이터베이스에 연결할 수 있다. 이 권한은 연결 시작 시 확인된다(pg_hba.conf에 의해 부과된 제한 사항도 함께 확인된다).

TEMPORARY

데이터베이스를 사용하는 동안 임시 테이블을 생성할 수 있다.

EXECUTE

함수나 프로시저를 호출할 수 있다. 이 권한은 함수와 프로시저에만 적용된다.

USAGE

프로시저 언어의 경우, 해당 언어로 함수를 생성할 수 있다. 이 권한은 프로시저 언어에만 적용된다.

스키마의 경우, 스키마 내의 객체에 접근할 수 있다(객체 자체의 권한 요구 사항도 충족되어야 한다). 이 권한은 기본적으로 스키마 내의 객체를 “조회”할 수 있게 한다. 이 권한이 없어도 시스템 카탈로그를 쿼리하면 객체 이름을 볼 수 있다. 또한, 이 권한을 회수해도 기존 세션에서는 이전에 수행한 조회가 유효할 수 있으므로, 객체 접근을 완전히 차단하는 방법은 아니다.

시퀀스의 경우 currval과 nextval 함수를 사용할 수 있다.

타입과 도메인의 경우, 테이블, 함수 및 기타 스키마 객체를 생성할 때 해당 타입이나 도메인을 사용할 수 있다. (이 권한은 타입의 모든 “사용”을 제어하지는 않는다. 예를 들어, 쿼리에서 타입의 값을 사용하는 것은 제어하지 않는다. 이 권한은 주로 타입에 의존성을 생성할 수 있는 사용자를 제어하여, 소유자가 나중에 타입을 변경하는 것을 방지하는 데 목적이 있다.)

외부 데이터 래퍼의 경우, 해당 래퍼를 사용하여 새로운 서버를 생성할 수 있다.

외부 서버의 경우, 해당 서버를 사용하여 외부 테이블을 생성할 수 있다. 또한, 해당 서버와 관련된 사용자 매핑을 생성, 변경, 삭제할 수 있다.

SET

현재 세션 내에서 서버 구성 매개변수를 새로운 값으로 설정할 수 있다. (이 권한은 모든 매개변수에 대해 부여할 수 있지만, 일반적으로 슈퍼유저 권한이 필요한 매개변수에만 의미가 있다.)

ALTER SYSTEM

ALTER SYSTEM 명령을 사용하여 서버 구성 매개변수를 새로운 값으로 설정할 수 있다.

MAINTAIN

관계(relation)에 대해 VACUUM, ANALYZE, CLUSTER, REFRESH MATERIALIZED VIEW, REINDEX, LOCK TABLE을 수행할 수 있다.

다른 명령에 필요한 권한은 해당 명령의 참조 페이지에 나열되어 있다.

PostgreSQL은 특정 타입의 객체를 생성할 때 기본적으로 PUBLIC에게 권한을 부여한다. 하지만 테이블, 테이블 컬럼, 시퀀스, 외부 데이터 래퍼, 외부 서버, 대용량 객체, 스키마, 테이블스페이스, 설정 파라미터 등에 대해서는 PUBLIC에게 기본적으로 아무런 권한도 부여하지 않는다. 다른 타입의 객체에 대해 PUBLIC에게 기본적으로 부여하는 권한은 다음과 같다: 데이터베이스에 대해서는 CONNECT와 TEMPORARY(임시 테이블 생성) 권한, 함수와 프로시저에 대해서는 EXECUTE 권한, 언어와 데이터 타입(도메인 포함)에 대해서는 USAGE 권한이다. 물론 객체 소유자는 기본적으로 부여된 권한이나 명시적으로 부여된 권한을 REVOKE로 취소할 수 있다. (최대한 보안을 강화하려면 객체를 생성하는 트랜잭션 안에서 REVOKE를 실행하는 것이 좋다. 이렇게 하면 다른 사용자가 객체를 사용할 수 있는 짧은 시간 창을 없앨 수 있다.) 또한, ALTER DEFAULT PRIVILEGES 명령을 사용해 이러한 기본 권한 설정을 변경할 수 있다.

표 5.1은 ACL(Access Control List) 값에서 사용하는 권한 타입의 한 글자 약어를 보여준다. 아래에 나열된 psql 명령의 출력이나 시스템 카탈로그의 ACL 컬럼에서 이 약어를 볼 수 있다.

표 5.1. ACL 권한 약어

권한 약어 적용 가능한 객체 타입
SELECT r (“읽기”) 대용량 객체, 시퀀스, 테이블(및 테이블과 유사한 객체), 테이블 컬럼
INSERT a (“추가”) 테이블, 테이블 컬럼
UPDATE w (“쓰기”) 대용량 객체, 시퀀스, 테이블, 테이블 컬럼
DELETE d 테이블
TRUNCATE D 테이블
REFERENCES x 테이블, 테이블 컬럼
TRIGGER t 테이블
CREATE C 데이터베이스, 스키마, 테이블스페이스
CONNECT c 데이터베이스
TEMPORARY T 데이터베이스
EXECUTE X 함수, 프로시저
USAGE U 도메인, 외부 데이터 래퍼, 외부 서버, 언어, 스키마, 시퀀스, 타입
SET s 파라미터
ALTER SYSTEM A 파라미터
MAINTAIN m 테이블

표 5.2는 위에서 설명한 약어를 사용해 각 SQL 객체 타입에 사용 가능한 권한을 요약한다. 또한 각 객체 타입에 대한 권한 설정을 확인할 수 있는 psql 명령도 함께 보여준다.

표 5.2. 접근 권한 요약

객체 타입 모든 권한 기본 PUBLIC 권한 psql 명령
데이터베이스 CTc Tc l
도메인 U U dD+
함수 또는 프로시저 X X df+
외부 데이터 래퍼 U 없음 dew+
외부 서버 U 없음 des+
언어 U U dL+
대용량 객체 rw 없음 dl+
파라미터 sA 없음 dconfig+
스키마 UC 없음 dn+
시퀀스 rwU 없음 dp
테이블(및 테이블과 유사한 객체) arwdDxtm 없음 dp
테이블 컬럼 arwx 없음 dp
테이블스페이스 C 없음 db+
타입 U U dT+

특정 객체에 부여된 권한은 aclitem 항목의 목록으로 표시되며, 각 항목은 다음과 같은 형식을 가진다:

grantee=privilege-abbreviation[*].../grantor

각 aclitem은 특정 grantor가 부여한 한 grantee의 모든 권한을 나열한다. 특정 권한은 표 5.1의 한 글자 약어로 표현되며, 권한이 grant option과 함께 부여된 경우 *가 추가된다. 예를 들어, calvin=r*w/hobbes는 calvin이라는 역할이 hobbes라는 역할로부터 SELECT(r) 권한과 grant option(*)을 함께 부여받았으며, grant option 없이 UPDATE(w) 권한도 부여받았음을 나타낸다. 만약 calvin이 동일한 객체에 대해 다른 grantor로부터도 일부 권한을 부여받았다면, 이는 별도의 aclitem 항목으로 표시된다. aclitem에서 grantee 필드가 비어 있는 경우, 이는 PUBLIC을 의미한다.

예를 들어, 사용자 miriam이 mytable이라는 테이블을 생성하고 다음과 같은 명령을 실행했다고 가정하자:

GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;

이 경우 psql의 dp 명령은 다음과 같은 결과를 보여준다:

=> dp mytable
                                  Access privileges
 Schema |  Name   | Type  |   Access privileges    |   Column privileges   | Policies
--------+---------+-------+------------------------+-----------------------+----------
 public | mytable | table | miriam=arwdDxtm/miriam+| col1:                +|
        |         |       | =r/miriam             +|   miriam_rw=rw/miriam |
        |         |       | admin=arw/miriam       |                       |
(1 row)

특정 객체에 대해 “Access privileges” 컬럼이 비어 있는 경우, 이는 객체가 기본 권한을 가지고 있음을 의미한다(즉, 관련 시스템 카탈로그의 권한 항목이 null임). 기본 권한에는 항상 소유자의 모든 권한이 포함되며, 객체 타입에 따라 PUBLIC에게도 일부 권한이 포함될 수 있다. 객체에 대한 첫 번째 GRANT 또는 REVOKE는 기본 권한을 인스턴스화하고(예: miriam=arwdDxt/miriam), 그 후 지정된 요청에 따라 이를 수정한다. 마찬가지로, “Column privileges”에는 기본 권한이 아닌 권한이 부여된 컬럼만 표시된다. (참고: 여기서 “기본 권한”은 항상 객체 타입에 대한 내장된 기본 권한을 의미한다. ALTER DEFAULT PRIVILEGES 명령의 영향을 받은 객체는 항상 ALTER의 효과를 포함한 명시적인 권한 항목으로 표시된다.)

소유자의 암시적 grant option은 접근 권한 표시에 표시되지 않는다. *는 grant option이 명시적으로 누군가에게 부여된 경우에만 나타난다.

“Access privileges” 컬럼은 객체의 권한 항목이 null이 아니지만 비어 있는 경우 (none)을 표시한다. 이는 심지어 객체 소유자에게도 아무런 권한이 부여되지 않았음을 의미한다 — 매우 드문 상황이다. (이 경우에도 소유자는 여전히 암시적 grant option을 가지고 있으므로 자신의 권한을 다시 부여할 수 있지만, 현재는 아무런 권한도 가지고 있지 않다.)

5.9. 행 수준 보안 정책

테이블은 SQL 표준의 GRANT 명령을 통한 권한 시스템 외에도 행 수준 보안 정책(Row Security Policies)을 설정할 수 있다. 이 정책은 사용자별로 일반 쿼리가 반환하는 행이나 데이터 수정 명령으로 삽입, 갱신, 삭제할 수 있는 행을 제한한다. 이 기능을 행 수준 보안(Row-Level Security)이라고도 한다. 기본적으로 테이블에는 어떤 정책도 설정되어 있지 않아서, SQL 권한 시스템에 따라 테이블에 접근 권한이 있는 사용자는 모든 행에 동등하게 쿼리나 갱신을 수행할 수 있다.

테이블에서 행 수준 보안을 활성화하면(ALTER TABLE … ENABLE ROW LEVEL SECURITY), 행을 선택하거나 수정하는 모든 일반 접근은 행 수준 보안 정책의 허용을 받아야 한다. 단, 테이블 소유자는 일반적으로 행 수준 보안 정책의 영향을 받지 않는다. 테이블에 정책이 없다면 기본적으로 모든 접근을 거부하는 정책이 적용되어 어떤 행도 볼 수 없고 수정할 수 없다. TRUNCATE나 REFERENCES와 같이 테이블 전체에 적용되는 작업은 행 수준 보안의 영향을 받지 않는다.

행 보안 정책은 특정 명령이나 역할, 또는 둘 다에 대해 지정할 수 있다. 정책은 ALL 명령에 적용하거나 SELECT, INSERT, UPDATE, DELETE 각각에 대해 지정할 수 있다. 하나의 정책에 여러 역할을 할당할 수 있으며, 일반적인 역할 멤버십과 상속 규칙이 적용된다.

정책에 따라 어떤 행을 볼 수 있거나 수정할 수 있는지 지정하려면 불리언 결과를 반환하는 표현식이 필요하다. 이 표현식은 사용자의 쿼리에서 오는 조건이나 함수보다 먼저 각 행에 대해 평가된다. (유일한 예외는 정보를 누출하지 않는 것이 보장된 leakproof 함수다. 최적화 프로그램이 이러한 함수를 행 보안 검사보다 먼저 적용하도록 선택할 수 있다.) 표현식이 true를 반환하지 않는 행은 처리되지 않는다. 볼 수 있는 행과 수정할 수 있는 행을 독립적으로 제어하기 위해 별도의 표현식을 지정할 수 있다. 정책 표현식은 쿼리의 일부로 실행되며 쿼리를 실행하는 사용자의 권한으로 실행된다. 단, security-definer 함수를 사용하면 호출하는 사용자가 접근할 수 없는 데이터에도 접근할 수 있다.

수퍼유저와 BYPASSRLS 속성을 가진 역할은 테이블에 접근할 때 행 보안 시스템을 항상 우회한다. 테이블 소유자도 일반적으로 행 보안을 우회하지만, ALTER TABLE … FORCE ROW LEVEL SECURITY 명령으로 행 보안을 적용받도록 선택할 수 있다.

행 보안을 활성화하거나 비활성화하고 테이블에 정책을 추가하는 것은 오직 테이블 소유자만이 할 수 있는 권한이다.

정책은 CREATE POLICY 명령으로 생성하고, ALTER POLICY 명령으로 변경하며, DROP POLICY 명령으로 삭제한다. 특정 테이블의 행 보안을 활성화하거나 비활성화하려면 ALTER TABLE 명령을 사용한다.

각 정책은 이름을 가지며, 하나의 테이블에 여러 정책을 정의할 수 있다. 정책은 테이블별로 적용되므로, 각 테이블의 정책은 고유한 이름을 가져야 한다. 다만 서로 다른 테이블은 같은 이름의 정책을 가질 수 있다.

여러 정책이 하나의 쿼리에 적용될 때, 허용 정책(기본값)은 OR 연산자로, 제한 정책은 AND 연산자로 결합된다. 이는 특정 역할이 자신이 속한 모든 역할의 권한을 가지는 규칙과 유사하다. 허용 정책과 제한 정책에 대해서는 아래에서 자세히 설명한다.

간단한 예제로, account 테이블에 정책을 만들어 managers 역할의 구성원만 행에 접근할 수 있게 하고, 자신의 계정 행만 볼 수 있도록 설정하는 방법을 살펴보자:

CREATE TABLE accounts (manager text, company text, contact_email text);
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY account_managers ON accounts TO managers
    USING (manager = current_user);

위 정책은 USING 절과 동일한 WITH CHECK 절을 암묵적으로 포함한다. 이 제약 조건은 커맨드로 선택된 행과 수정된 행 모두에 적용된다. 따라서 관리자는 다른 관리자에 속한 기존 행에 대해 SELECT, UPDATE, DELETE 작업을 수행할 수 없으며, INSERT나 UPDATE를 통해 다른 관리자에 속한 행을 생성할 수도 없다.

역할을 지정하지 않거나 특수 사용자 이름인 PUBLIC을 사용하면, 해당 정책은 시스템의 모든 사용자에게 적용된다. 모든 사용자가 users 테이블에서 자신의 행만 접근할 수 있도록 하는 간단한 정책은 다음과 같다:

CREATE POLICY user_policy ON users
    USING (user_name = current_user);

이 정책은 앞의 예제와 비슷하게 동작한다.

테이블에 추가되는 행과 조회 가능한 행에 서로 다른 정책을 적용하려면 여러 정책을 조합할 수 있다. 다음 두 정책은 모든 사용자가 users 테이블의 모든 행을 볼 수 있지만, 자신의 행만 수정할 수 있도록 한다:

CREATE POLICY user_sel_policy ON users
    FOR SELECT
    USING (true);
CREATE POLICY user_mod_policy ON users
    USING (user_name = current_user);

SELECT 커맨드에서 이 두 정책은 OR 연산자로 결합되어 모든 행을 선택할 수 있다. 다른 커맨드 타입에서는 두 번째 정책만 적용되어 이전과 동일한 효과를 낸다.

행 보안은 ALTER TABLE 커맨드로 비활성화할 수도 있다. 행 보안을 비활성화해도 테이블에 정의된 정책은 삭제되지 않고 단순히 무시된다. 이 경우 표준 SQL 권한 시스템에 따라 테이블의 모든 행을 조회하고 수정할 수 있다.

다음은 실제 운영 환경에서 이 기능을 활용하는 방법을 보여주는 확장된 예제다. 여기서 passwd 테이블은 Unix 패스워드 파일을 모방한다:

-- Unix 패스워드 파일 기반의 간단한 예제
CREATE TABLE passwd (
  user_name             text UNIQUE NOT NULL,
  pwhash                text,
  uid                   int  PRIMARY KEY,
  gid                   int  NOT NULL,
  real_name             text NOT NULL,
  home_phone            text,
  extra_info            text,
  home_dir              text NOT NULL,
  shell                 text NOT NULL
);

CREATE ROLE admin;  -- 관리자
CREATE ROLE bob;    -- 일반 사용자
CREATE ROLE alice;  -- 일반 사용자

-- 테이블에 데이터 삽입
INSERT INTO passwd VALUES
  ('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT INTO passwd VALUES
  ('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT INTO passwd VALUES
  ('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');

-- 테이블에 행 수준 보안 활성화
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-- 정책 생성
-- 관리자는 모든 행을 보고 추가할 수 있음
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
-- 일반 사용자는 모든 행을 볼 수 있음
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
-- 일반 사용자는 자신의 기록만 갱신할 수 있으며,
-- 사용 가능한 셸이 제한됨
CREATE POLICY user_mod ON passwd FOR UPDATE
  USING (current_user = user_name)
  WITH CHECK (
    current_user = user_name AND
    shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
  );

-- 관리자에게 모든 일반 권한 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-- 일반 사용자는 공개 컬럼에 대한 조회 권한만 가짐
GRANT SELECT
  (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
  ON passwd TO public;
-- 일반 사용자는 특정 컬럼만 갱신할 수 있음
GRANT UPDATE
  (pwhash, real_name, home_phone, extra_info, shell)
  ON passwd TO public;

모든 보안 설정과 마찬가지로, 시스템이 의도한 대로 동작하는지 테스트하고 확인하는 것이 중요하다. 아래 예제는 앞서 설정한 권한 시스템이 올바르게 작동하는지 보여준다:

-- 관리자는 모든 행과 필드를 볼 수 있다
postgres=> set role admin;
SET
postgres=> table passwd;
 user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info | home_dir    |   shell
-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
 admin     | xxx    |   0 |   0 | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | xxx    |   1 |   1 | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | xxx    |   2 |   1 | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

-- Alice가 수행할 수 있는 작업 테스트
postgres=> set role alice;
SET
postgres=> table passwd;
ERROR:  permission denied for table passwd
postgres=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name |  home_phone  | extra_info | home_dir    |   shell
-----------+-----------+--------------+------------+-------------+-----------
 admin     | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

postgres=> update passwd set user_name = 'joe';
ERROR:  permission denied for table passwd
-- Alice는 자신의 real_name만 변경할 수 있고, 다른 사용자의 정보는 변경할 수 없다
postgres=> update passwd set real_name = 'Alice Doe';
UPDATE 1
postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
postgres=> update passwd set shell = '/bin/xx';
ERROR:  new row violates WITH CHECK OPTION for "passwd"
postgres=> delete from passwd;
ERROR:  permission denied for table passwd
postgres=> insert into passwd (user_name) values ('xxx');
ERROR:  permission denied for table passwd
-- Alice는 자신의 비밀번호를 변경할 수 있다. RLS는 다른 행의 갱신을 자동으로 차단한다
postgres=> update passwd set pwhash = 'abc';
UPDATE 1

지금까지 작성한 모든 정책은 허용 정책이었다. 허용 정책은 여러 정책이 적용될 때 “OR” 논리 연산자로 결합된다. 허용 정책만으로도 의도한 경우에만 행 접근을 허용하도록 구성할 수 있지만, 허용 정책과 제한 정책을 함께 사용하면 더 단순하게 구현할 수 있다. 제한 정책은 반드시 통과해야 하며 “AND” 논리 연산자로 결합된다. 앞의 예제를 확장하여, 관리자가 로컬 Unix 소켓을 통해 연결된 경우에만 passwd 테이블의 레코드에 접근할 수 있도록 제한 정책을 추가해보자:

CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin
    USING (pg_catalog.inet_client_addr() IS NULL);

이제 네트워크를 통해 연결한 관리자는 제한 정책으로 인해 어떤 레코드도 볼 수 없다:

=> SELECT current_user;
 current_user
--------------
 admin
(1 row)

=> select inet_client_addr();
 inet_client_addr
------------------
 127.0.0.1
(1 row)

=> TABLE passwd;
 user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell
-----------+--------+-----+-----+-----------+------------+------------+----------+-------
(0 rows)

=> UPDATE passwd set pwhash = NULL;
UPDATE 0

고유키나 기본 키 제약 조건, 외래 키 참조와 같은 참조 무결성 검사는 데이터 무결성 유지를 위해 행 수준 보안을 항상 우회한다. 따라서 스키마와 행 수준 정책을 개발할 때는 이러한 참조 무결성 검사를 통한 정보 유출의 “은닉 채널”을 방지하도록 주의해야 한다.

특정 상황에서는 행 수준 보안이 적용되지 않도록 해야 한다. 예를 들어 백업을 수행할 때 행 수준 보안으로 인해 일부 행이 백업에서 누락된다면 심각한 문제가 발생할 수 있다. 이런 경우에는 row_security 설정 매개변수를 off로 설정할 수 있다. 이 설정은 행 수준 보안을 우회하지 않고, 대신 정책에 의해 쿼리 결과가 필터링될 때 오류를 발생시킨다. 이를 통해 오류의 원인을 조사하고 수정할 수 있다.

앞의 예제에서 정책 표현식은 접근하거나 갱신할 행의 현재 값만 고려했다. 이는 가장 단순하고 성능이 좋은 경우다. 가능하다면 이런 방식으로 행 수준 보안 애플리케이션을 설계하는 것이 가장 좋다. 정책 결정을 위해 다른 행이나 테이블을 참조해야 할 경우, 정책 표현식에서 하위 SELECT 또는 SELECT를 포함하는 함수를 사용할 수 있다. 하지만 주의하지 않으면 이러한 접근이 정보 유출을 허용할 수 있는 경쟁 조건을 만들 수 있다. 다음 테이블 설계를 예로 살펴보자:

-- 권한 그룹 정의
CREATE TABLE groups (group_id int PRIMARY KEY,
                     group_name text NOT NULL);

INSERT INTO groups VALUES
  (1, 'low'),
  (2, 'medium'),
  (5, 'high');

GRANT ALL ON groups TO alice;  -- alice는 관리자다
GRANT SELECT ON groups TO public;

-- 사용자의 권한 수준 정의
CREATE TABLE users (user_name text PRIMARY KEY,
                    group_id int NOT NULL REFERENCES groups);

INSERT INTO users VALUES
  ('alice', 5),
  ('bob', 2),
  ('mallory', 2);

GRANT ALL ON users TO alice;
GRANT SELECT ON users TO public;

-- 보호할 정보를 담고 있는 테이블
CREATE TABLE information (info text,
                          group_id int NOT NULL REFERENCES groups);

INSERT INTO information VALUES
  ('barely secret', 1),
  ('slightly secret', 2),
  ('very secret', 5);

ALTER TABLE information ENABLE ROW LEVEL SECURITY;

-- 사용자의 보안 group_id가 해당 행의 group_id보다 크거나 같을 때
-- 행을 조회/갱신할 수 있다
CREATE POLICY fp_s ON information FOR SELECT
  USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
CREATE POLICY fp_u ON information FOR UPDATE
  USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));

-- information 테이블은 RLS로만 보호한다
GRANT ALL ON information TO public;

이제 alice가 “slightly secret” 정보를 변경하려고 하는 상황을 가정해보자. alice는 mallory가 새로운 내용을 볼 수 없기를 원하므로 다음과 같이 처리한다:

BEGIN;
UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
COMMIT;

언뜻 보기에 안전해 보인다. mallory가 “secret from mallory” 문자열을 볼 수 있는 시간대가 없어 보인다. 하지만 여기에는 경쟁 조건이 존재한다. 만약 mallory가 동시에 다음과 같은 쿼리를 실행한다면:

SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

그리고 mallory의 트랜잭션이 READ COMMITTED 모드라면, “secret from mallory”를 볼 수 있는 가능성이 있다. 이는 mallory의 트랜잭션이 alice의 트랜잭션 직후에 information 행에 도달할 때 발생한다. mallory의 트랜잭션은 alice의 트랜잭션이 커밋될 때까지 대기한 다음, FOR UPDATE 절 덕분에 갱신된 행 내용을 가져온다. 하지만 암묵적인 users 테이블의 SELECT는 갱신된 행을 가져오지 않는다. 이는 해당 하위 SELECT가 FOR UPDATE를 포함하지 않았기 때문이다. 대신 users 행은 쿼리 시작 시점의 스냅샷으로 읽힌다. 따라서 정책 표현식은 mallory의 이전 권한 수준을 테스트하게 되어 갱신된 행을 볼 수 있게 된다.

이 문제를 해결하는 방법은 여러 가지가 있다:

  1. 행 보안 정책의 하위 SELECT에서 SELECT ... FOR SHARE를 사용하는 간단한 방법이 있다. 하지만 이 경우 영향을 받는 사용자에게 참조된 테이블(users)에 대한 UPDATE 권한을 부여해야 한다. 이는 바람직하지 않을 수 있다. (다만 다른 행 보안 정책을 적용하여 실제로 이 권한을 행사하지 못하게 하거나, 하위 SELECT를 보안 정의자 함수에 포함시킬 수 있다.)

  2. 참조된 테이블에 대한 행 공유 잠금을 과도하게 사용하면 성능 문제가 발생할 수 있다. 특히 테이블 갱신이 빈번한 경우 이 문제가 두드러진다.

  3. 참조된 테이블의 갱신이 빈번하지 않다면, 갱신 시 ACCESS EXCLUSIVE 잠금을 사용하는 것이 실용적인 해결책이 될 수 있다. 이렇게 하면 동시 트랜잭션이 이전 행 값을 검사할 수 없다.

  4. 또는 참조된 테이블을 갱신한 후 커밋하고, 새로운 보안 상황에 의존하는 변경을 하기 전에 모든 동시 트랜잭션이 종료될 때까지 기다릴 수도 있다.

자세한 내용은 CREATE POLICY와 ALTER TABLE을 참조하기 바란다.

5.10. 스키마

PostgreSQL 데이터베이스 클러스터는 하나 이상의 이름이 지정된 데이터베이스를 포함한다. 역할(role)과 일부 다른 객체 유형은 전체 클러스터에서 공유된다. 클라이언트가 서버에 연결할 때는 연결 요청에서 지정한 단일 데이터베이스의 데이터만 접근할 수 있다.

참고사항

클러스터 사용자가 모든 데이터베이스에 접근할 수 있는 것은 아니다. 동일한 클러스터 내 두 개의 데이터베이스에서 joe와 같은 동일한 이름의 역할을 다르게 설정할 수 없다. 하지만 시스템 설정을 통해 joe가 특정 데이터베이스에만 접근하도록 제한할 수 있다.

하나의 데이터베이스는 여러 개의 이름이 있는 *스키마*를 포함한다. 각 스키마는 테이블을 비롯해 데이터 타입, 함수, 연산자 등 다양한 객체를 담고 있다. 동일한 스키마 안에서는 같은 타입의 객체가 동일한 이름을 가질 수 없다. 또한 테이블, 시퀀스, 인덱스, 뷰, 구체화된 뷰, 외부 테이블은 같은 네임스페이스를 공유하므로, 예를 들어 같은 스키마 내에서 인덱스와 테이블은 서로 다른 이름을 가져야 한다. 하지만 서로 다른 스키마에서는 같은 이름의 객체를 사용할 수 있다. 예를 들어 schema1과 myschema 모두 mytable이라는 이름의 테이블을 가질 수 있다. 데이터베이스와 달리 스키마는 엄격하게 분리되지 않는다. 사용자는 접근 권한만 있다면 연결된 데이터베이스 내 모든 스키마의 객체에 접근할 수 있다.

스키마를 사용하는 주요 이유는 다음과 같다:

스키마는 운영체제의 디렉터리와 비슷하지만, 중첩이 불가능하다는 차이점이 있다.

5.10.1. 스키마 생성하기

스키마를 생성하려면 CREATE SCHEMA 명령어를 사용한다. 원하는 이름으로 스키마를 만들 수 있다. 예를 들면 다음과 같다:

CREATE SCHEMA myschema;

스키마 내의 객체를 생성하거나 접근하려면 스키마 이름과 테이블 이름을 점으로 구분한 *정규화된 이름*을 사용한다:

schema.table

이 문법은 테이블 이름이 필요한 모든 곳에서 사용할 수 있다. 여기에는 이후 장에서 다룰 테이블 수정 명령어와 데이터 접근 명령어가 포함된다. (간단히 설명하기 위해 테이블만 언급했지만, 타입이나 함수와 같은 다른 종류의 명명된 객체에도 동일한 원칙이 적용된다.)

실제로는 더 일반적인 문법인 다음 형식도 사용할 수 있다:

database.schema.table

하지만 현재는 SQL 표준을 형식적으로 준수하기 위한 것일 뿐이다. 데이터베이스 이름을 작성한다면, 현재 연결된 데이터베이스와 동일해야 한다.

새로운 스키마에 테이블을 생성하려면 다음과 같이 작성한다:

CREATE TABLE myschema.mytable (
 ...
);

비어있는 스키마를 삭제하려면(스키마 내의 모든 객체가 이미 삭제된 경우) 다음 명령을 사용한다:

DROP SCHEMA myschema;

스키마와 그 안에 포함된 모든 객체를 함께 삭제하려면 다음 명령을 사용한다:

DROP SCHEMA myschema CASCADE;

이 기능의 일반적인 메커니즘에 대한 자세한 설명은 5.15절을 참조한다.

다른 사용자가 소유한 스키마를 생성해야 하는 경우가 많다(사용자의 활동을 잘 정의된 네임스페이스로 제한하는 방법 중 하나이므로). 이때는 다음과 같은 문법을 사용한다:

CREATE SCHEMA schema_name AUTHORIZATION user_name;

스키마 이름을 생략할 수도 있는데, 이 경우 스키마 이름은 사용자 이름과 동일하게 설정된다. 이 기능이 유용한 이유는 5.10.6절을 참조한다.

pg_로 시작하는 스키마 이름은 시스템용으로 예약되어 있으므로 사용자가 생성할 수 없다.

5.10.2. 퍼블릭 스키마

앞 절에서 스키마 이름을 지정하지 않고 테이블을 생성하는 방법을 살펴보았다. 스키마를 명시하지 않으면 모든 객체는 자동으로 “public”이라는 이름의 스키마에 저장된다. 새로 생성하는 모든 데이터베이스에는 이 public 스키마가 기본으로 포함되어 있다. 따라서 다음 두 SQL문은 동일한 결과를 만든다:

CREATE TABLE products ( ... );

그리고:

CREATE TABLE public.products ( ... );

5.10.3. 스키마 검색 경로

정규화된 이름을 매번 작성하는 것은 번거롭다. 또한 애플리케이션 코드에 특정 스키마 이름을 직접 지정하는 것은 바람직하지 않다. 따라서 테이블을 참조할 때는 주로 테이블 이름만으로 구성된 비정규화된 이름 을 사용한다. 시스템은 검색 경로 라는 스키마 목록을 참조하여 어떤 테이블을 의미하는지 판단한다. 검색 경로에서 처음 발견된 일치하는 테이블을 대상 테이블로 선택한다. 검색 경로에서 일치하는 테이블을 찾지 못하면, 데이터베이스의 다른 스키마에 동일한 이름의 테이블이 있더라도 오류가 발생한다.

서로 다른 스키마에 동일한 이름의 객체를 생성할 수 있다는 점은 특정 객체를 정확하게 참조하는 쿼리 작성을 복잡하게 만든다. 또한 이는 사용자가 의도적으로 또는 실수로 다른 사용자의 쿼리 동작을 변경할 수 있는 가능성을 열어준다. 비정규화된 이름이 쿼리와 PostgreSQL 내부에서 자주 사용되기 때문에, search_path에 스키마를 추가하면 해당 스키마에 대해 CREATE 권한을 가진 모든 사용자를 신뢰하게 된다. 일반적인 쿼리를 실행할 때, 검색 경로의 스키마에 객체를 생성할 수 있는 악의적인 사용자가 임의의 SQL 함수를 마치 사용자가 직접 실행한 것처럼 제어할 수 있다.

검색 경로에서 첫 번째로 지정된 스키마를 현재 스키마라고 한다. 이는 검색 시 가장 먼저 확인할 스키마일 뿐만 아니라, CREATE TABLE 명령에서 스키마 이름을 지정하지 않았을 때 새로운 테이블이 생성될 스키마이기도 하다.

현재 검색 경로를 확인하려면 다음 명령을 사용한다:

SHOW search_path;

기본 설정에서는 다음과 같은 결과가 출력된다:

 search_path
--------------
 "$user", public

첫 번째 요소는 현재 사용자 이름과 동일한 이름의 스키마를 검색하라는 의미다. 해당 스키마가 존재하지 않으면 이 항목은 무시된다. 두 번째 요소는 이미 살펴본 public 스키마를 가리킨다.

검색 경로에서 존재하는 첫 번째 스키마가 새로운 객체를 생성하는 기본 위치가 된다. 이것이 기본적으로 객체가 public 스키마에 생성되는 이유다. 스키마를 지정하지 않고 객체를 참조할 때(테이블 수정, 데이터 수정, 또는 쿼리 명령에서), 일치하는 객체를 찾을 때까지 검색 경로를 순회한다. 따라서 기본 설정에서는 비정규화된 접근이 public 스키마만을 참조할 수 있다.

새로운 스키마를 검색 경로에 추가하려면 다음과 같이 실행한다:

SET search_path TO myschema,public;

(여기서는 당장 필요하지 않으므로 $user를 생략했다.) 이제 스키마를 지정하지 않고도 테이블에 접근할 수 있다:

DROP TABLE mytable;

또한 myschema가 검색 경로의 첫 번째 요소이므로, 새로운 객체는 기본적으로 이 스키마에 생성된다.

다음과 같이 설정할 수도 있다:

SET search_path TO myschema;

이렇게 하면 명시적으로 지정하지 않는 한 public 스키마에 접근할 수 없게 된다. public 스키마는 기본적으로 존재한다는 점 외에는 특별한 점이 없다. 이 스키마도 삭제할 수 있다.

스키마 검색 경로를 조작하는 다른 방법은 9.27절을 참조한다.

검색 경로는 테이블 이름뿐만 아니라 데이터 타입 이름, 함수 이름, 연산자 이름에 대해서도 동일하게 작동한다. 데이터 타입과 함수 이름은 테이블 이름과 정확히 같은 방식으로 정규화할 수 있다. 표현식에서 정규화된 연산자 이름을 작성해야 할 경우, 구문 모호성을 피하기 위해 다음과 같은 특별한 규칙이 있다:

OPERATOR(schema.operator)

예를 들면 다음과 같다:

SELECT 3 OPERATOR(pg_catalog.+) 4;

실제로는 보통 이렇게 복잡한 구문을 작성하지 않도록 연산자에 대해 검색 경로를 사용한다.

스키마와 권한

기본적으로 사용자는 자신이 소유하지 않은 스키마의 객체에 접근할 수 없다. 다른 스키마의 객체에 접근하려면 해당 스키마의 소유자가 USAGE 권한을 부여해야 한다. public 스키마의 경우 기본적으로 모든 사용자가 이 권한을 가진다. 사용자가 스키마 내의 객체를 실제로 사용하려면 각 객체의 특성에 맞는 추가 권한이 필요할 수 있다.

사용자는 다른 사람의 스키마에서 객체를 생성할 수도 있다. 이를 위해서는 해당 스키마에 대한 CREATE 권한이 필요하다. PostgreSQL 14 이전 버전에서 업그레이드한 데이터베이스의 경우, 모든 사용자가 public 스키마에 대해 이 권한을 가진다. 일부 사용 패턴에서는 이 권한을 제거하는 것이 좋다:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

(첫 번째 “public”은 스키마를 의미하고, 두 번째 “PUBLIC”은 “모든 사용자”를 의미한다. 첫 번째는 식별자로 사용되고 두 번째는 키워드로 사용되기 때문에 대소문자가 다르다. 이는 4.1.1절의 가이드라인을 따른 것이다.)

5.10.5. 시스템 카탈로그 스키마

모든 데이터베이스는 public 스키마와 사용자가 생성한 스키마 외에도 pg_catalog 스키마를 포함한다. 이 pg_catalog 스키마는 시스템 테이블과 내장 데이터 타입, 함수, 연산자를 모두 담고 있다. pg_catalog는 항상 검색 경로의 일부로 동작한다. 검색 경로에 명시적으로 지정하지 않더라도 다른 스키마를 검색하기 전에 암묵적으로 먼저 검색한다. 이렇게 함으로써 내장 이름을 항상 찾을 수 있게 한다. 만약 사용자가 정의한 이름이 내장 이름보다 우선하기를 원한다면, pg_catalog를 검색 경로의 맨 끝에 명시적으로 배치할 수 있다.

시스템 테이블 이름은 모두 pg_로 시작하므로, 향후 버전에서 새로운 시스템 테이블이 추가될 때 이름 충돌이 발생하지 않도록 이러한 이름은 사용하지 않는 것이 좋다. (기본 검색 경로에서 테이블 이름을 한정자 없이 참조하면 시스템 테이블로 해석될 수 있다.) 시스템 테이블은 앞으로도 계속 pg_ 접두어 규칙을 따를 것이므로, 사용자가 pg_ 접두어를 피한다면 한정자 없는 사용자 테이블 이름과 충돌하는 일은 없을 것이다.

5.10.6. 스키마 활용 패턴

스키마는 데이터를 체계적으로 구성하는 다양한 방법을 제공한다. *안전한 스키마 활용 패턴*은 신뢰할 수 없는 사용자가 다른 사용자의 쿼리 동작을 변경하는 것을 방지한다. 데이터베이스가 안전한 스키마 활용 패턴을 사용하지 않을 경우, 데이터베이스를 안전하게 조회하려는 사용자는 각 세션 시작 시 보호 조치를 취해야 한다. 구체적으로, 각 세션을 시작할 때 search_path를 빈 문자열로 설정하거나 슈퍼유저가 아닌 사용자가 쓰기 권한을 가진 스키마를 search_path에서 제거해야 한다. 기본 설정으로 쉽게 구현할 수 있는 활용 패턴은 다음과 같다:

어떤 패턴을 사용하든 공유 애플리케이션(모든 사람이 사용할 테이블, 서드파티가 제공하는 추가 함수 등)을 설치할 때는 별도의 스키마에 넣는다. 다른 사용자가 접근할 수 있도록 적절한 권한을 부여해야 한다. 사용자는 선택에 따라 스키마 이름으로 이러한 추가 객체를 참조하거나 검색 경로에 추가 스키마를 포함할 수 있다.

5.10.7. 이식성

SQL 표준에는 동일한 스키마 내에서 서로 다른 사용자가 객체를 소유하는 개념이 존재하지 않는다. 더욱이 일부 구현체에서는 소유자의 이름과 다른 이름으로 스키마를 생성하는 것을 허용하지 않는다. 실제로 SQL 표준에 명시된 기본적인 스키마 지원만 구현한 데이터베이스 시스템에서는 스키마와 사용자의 개념이 거의 동일하다. 따라서 많은 사용자가 정규화된 이름을 실제로 *사용자_이름*.*테이블_이름*으로 구성된 것으로 간주한다. PostgreSQL에서도 모든 사용자에 대해 사용자별 스키마를 생성하면 이와 같이 동작한다.

또한 SQL 표준에서는 public 스키마라는 개념이 존재하지 않는다. 표준을 최대한 준수하려면 public 스키마를 사용하지 않아야 한다.

물론 일부 SQL 데이터베이스 시스템은 스키마를 전혀 구현하지 않거나, 제한적이나마 데이터베이스 간 접근을 허용하는 방식으로 네임스페이스를 지원할 수 있다. 이러한 시스템과 함께 작업해야 한다면, 스키마를 전혀 사용하지 않는 것이 최대한의 이식성을 확보하는 방법이다.

5.11. 테이블 상속

PostgreSQL은 테이블 상속 기능을 구현하여 데이터베이스 설계자에게 유용한 도구를 제공한다. (SQL:1999 이후 버전에서는 여기서 설명하는 기능과는 다른 타입 상속 기능을 정의한다.)

실제 예제를 통해 살펴보자. 도시 데이터 모델을 구축한다고 가정해보자. 각 주(state)는 여러 도시를 가지지만 주도(capital)는 하나만 있다. 특정 주의 주도를 빠르게 찾아야 할 때가 있다. 이런 경우 주도를 위한 테이블과 일반 도시를 위한 테이블, 이렇게 두 개의 테이블을 만들 수 있다. 하지만 주도인지 아닌지와 관계없이 도시에 대한 데이터를 조회하려면 어떻게 해야 할까? 이때 상속 기능이 도움이 된다. capitals 테이블이 cities 테이블을 상속하도록 다음과 같이 정의한다:

CREATE TABLE cities (
    name            text,
    population      float,
    elevation       int     -- 피트 단위
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);

이 경우 capitals 테이블은 부모 테이블인 cities의 모든 컬럼을 상속한다. 주도는 추가로 state 컬럼을 가져 어느 주의 주도인지 나타낸다.

PostgreSQL에서 테이블은 0개 이상의 다른 테이블을 상속할 수 있다. 쿼리는 테이블의 모든 행을 참조하거나, 테이블과 그 자식 테이블의 모든 행을 참조할 수 있다. 기본적으로는 후자의 동작을 수행한다. 예를 들어, 다음 쿼리는 고도가 500피트 이상인 모든 도시(주도 포함)의 이름을 찾는다:

SELECT name, elevation
    FROM cities
    WHERE elevation > 500;

PostgreSQL 튜토리얼의 샘플 데이터(2.1절 참조)를 사용하면 다음과 같은 결과가 나온다:

   name    | elevation
-----------+-----------
 Las Vegas |      2174
 Mariposa  |      1953
 Madison   |       845

반면 다음 쿼리는 고도가 500피트 이상인 일반 도시(주도 제외)만 찾는다:

SELECT name, elevation
    FROM ONLY cities
    WHERE elevation > 500;

   name    | elevation
-----------+-----------
 Las Vegas |      2174
 Mariposa  |      1953

여기서 ONLY 키워드는 쿼리가 cities 테이블에만 적용되고 상속 계층의 하위 테이블에는 적용되지 않음을 나타낸다. SELECT, UPDATE, DELETE 등 이미 설명한 많은 명령어가 ONLY 키워드를 지원한다.

테이블 이름 뒤에 *를 붙여 자식 테이블을 명시적으로 포함할 수도 있다:

SELECT name, elevation
    FROM cities*
    WHERE elevation > 500;

*를 쓰는 것은 필수가 아니다. 이 동작이 항상 기본값이기 때문이다. 하지만 이 문법은 기본값을 변경할 수 있었던 이전 버전과의 호환성을 위해 여전히 지원된다.

특정 행이 어느 테이블에서 왔는지 알고 싶을 때가 있다. 각 테이블에는 원본 테이블을 알려주는 tableoid라는 시스템 컬럼이 있다:

SELECT c.tableoid, c.name, c.elevation
FROM cities c
WHERE c.elevation > 500;

결과는 다음과 같다:

 tableoid |   name    | elevation
----------+-----------+-----------
   139793 | Las Vegas |      2174
   139793 | Mariposa  |      1953
   139798 | Madison   |       845

(이 예제를 직접 실행하면 다른 숫자 OID가 나올 것이다.) pg_class와 조인하면 실제 테이블 이름을 볼 수 있다:

SELECT p.relname, c.name, c.elevation
FROM cities c, pg_class p
WHERE c.elevation > 500 AND c.tableoid = p.oid;

결과:

 relname  |   name    | elevation
----------+-----------+-----------
 cities   | Las Vegas |      2174
 cities   | Mariposa  |      1953
 capitals | Madison   |       845

regclass 별칭 타입을 사용하면 테이블 OID를 심볼릭하게 출력하는 방법도 있다:

SELECT c.tableoid::regclass, c.name, c.elevation
FROM cities c
WHERE c.elevation > 500;

상속은 INSERT나 COPY 명령의 데이터를 상속 계층의 다른 테이블로 자동 전파하지 않는다. 예를 들어, 다음 INSERT 문은 실패한다:

INSERT INTO cities (name, population, elevation, state)
VALUES ('Albany', NULL, NULL, 'NY');

데이터가 어떻게든 capitals 테이블로 전달되기를 기대할 수 있지만, 그렇게 되지 않는다: INSERT는 항상 지정된 테이블에만 삽입한다. 규칙(rule)을 사용하여 삽입을 리디렉션할 수 있는 경우도 있다(39장 참조). 하지만 위 경우에는 도움이 되지 않는다. cities 테이블에 state 컬럼이 없어서 규칙이 적용되기 전에 명령이 거부되기 때문이다.

모든 검사 제약조건과 NOT NULL 제약조건은 NO INHERIT 절로 명시적으로 지정하지 않는 한 자식 테이블이 자동으로 상속한다. 다른 종류의 제약조건(UNIQUE, PRIMARY KEY, FOREIGN KEY)은 상속되지 않는다.

테이블은 둘 이상의 부모 테이블을 상속할 수 있다. 이 경우 부모 테이블들이 정의한 컬럼의 합집합을 가진다. 자식 테이블 정의에서 선언된 컬럼이 여기에 추가된다. 같은 컬럼 이름이 여러 부모 테이블에 나타나거나, 부모 테이블과 자식 테이블의 정의에 모두 나타나면 이 컬럼들은 “병합”되어 자식 테이블에 하나의 컬럼만 존재한다. 병합하려면 컬럼의 데이터 타입이 같아야 한다. 다르면 오류가 발생한다. 상속 가능한 검사 제약조건과 NOT NULL 제약조건도 비슷한 방식으로 병합된다. 예를 들어, 병합된 컬럼은 원본 컬럼 정의 중 하나라도 NOT NULL로 표시되어 있으면 NOT NULL로 표시된다. 검사 제약조건은 이름이 같으면 병합되며, 조건이 다르면 병합이 실패한다.

테이블 상속은 일반적으로 자식 테이블을 생성할 때 CREATE TABLE 문의 INHERITS 절을 사용하여 설정한다. 또는 이미 호환되는 방식으로 정의된 테이블에 ALTER TABLE의 INHERIT 변형을 사용하여 새로운 부모 관계를 추가할 수 있다. 이를 위해서는 새로운 자식 테이블이 부모의 컬럼과 같은 이름과 타입의 컬럼을 이미 포함하고 있어야 한다. 또한 부모와 같은 이름과 검사 표현식을 가진 검사 제약조건도 포함해야 한다. 마찬가지로 ALTER TABLE의 NO INHERIT 변형을 사용하여 자식에서 상속 연결을 제거할 수 있다. 이렇게 상속 연결을 동적으로 추가하고 제거하는 것은 테이블 파티셔닝(5.12절 참조)에 상속 관계를 사용할 때 유용할 수 있다.

나중에 자식이 될 호환되는 테이블을 만드는 편리한 방법은 CREATE TABLE의 LIKE 절을 사용하는 것이다. 이는 소스 테이블과 같은 컬럼을 가진 새 테이블을 만든다. 소스 테이블에 CHECK 제약조건이 정의되어 있다면, LIKE의 INCLUDING CONSTRAINTS 옵션을 지정해야 한다. 새로운 자식이 호환되려면 부모와 일치하는 제약조건이 있어야 하기 때문이다.

자식이 하나라도 남아있으면 부모 테이블을 삭제할 수 없다. 또한 부모 테이블에서 상속된 자식 테이블의 컬럼이나 검사 제약조건은 삭제하거나 변경할 수 없다. 테이블과 그 모든 자손을 제거하고 싶다면, CASCADE 옵션(5.15절 참조)을 사용하여 부모 테이블을 삭제하는 것이 쉬운 방법이다.

ALTER TABLE은 컬럼 데이터 정의와 검사 제약조건의 변경을 상속 계층 아래로 전파한다. 다른 테이블이 의존하는 컬럼을 삭제하는 것은 CASCADE 옵션을 사용할 때만 가능하다. ALTER TABLE은 CREATE TABLE 동안 적용되는 것과 같은 규칙을 따라 중복 컬럼을 병합하거나 거부한다.

상속된 쿼리는 부모 테이블에서만 접근 권한 검사를 수행한다. 예를 들어, cities 테이블에 UPDATE 권한을 부여하면 cities를 통해 접근할 때 capitals 테이블의 행도 업데이트할 수 있다는 권한이 함축된다. 이는 데이터가 (또한) 부모 테이블에 있다는 외관을 유지한다. 하지만 추가 권한 부여 없이는 capitals 테이블을 직접 업데이트할 수 없다. 비슷한 방식으로, 상속된 쿼리 중에 자식 테이블의 행에 부모 테이블의 행 보안 정책(5.9절 참조)이 적용된다. 자식 테이블의 정책은 쿼리에서 해당 테이블이 명시적으로 이름 지정된 경우에만 적용되며, 이 경우 부모 테이블에 연결된 정책은 무시된다.

외부 테이블(5.13절 참조)도 일반 테이블처럼 상속 계층의 부모나 자식 테이블이 될 수 있다. 외부 테이블이 상속 계층의 일부라면, 외부 테이블이 지원하지 않는 작업은 전체 계층에서도 지원되지 않는다.

5.11.1. 상속 기능 사용 시 주의사항

모든 SQL 명령이 상속 계층 구조에서 작동하는 것은 아니다. 데이터 조회, 수정, 스키마 변경에 사용하는 명령(SELECT, UPDATE, DELETE, ALTER TABLE의 대부분 변형)은 기본적으로 자식 테이블을 포함한다. 이때 ONLY 표기법을 사용하면 자식 테이블을 제외할 수 있다. 단, INSERT나 ALTER TABLE ... RENAME은 예외다. 데이터베이스 유지보수와 튜닝을 위한 명령(REINDEX, VACUUM)은 개별 물리 테이블에서만 작동하며 상속 계층 구조를 재귀적으로 처리하지 않는다. 각 명령의 구체적인 동작은 SQL 명령 참조 페이지에서 확인할 수 있다.

상속 기능의 가장 큰 제약은 인덱스(고유 제약 조건 포함)와 외래 키 제약 조건이 단일 테이블에만 적용되고 상속된 자식 테이블에는 적용되지 않는다는 점이다. 이는 외래 키 제약 조건의 참조하는 쪽과 참조되는 쪽 모두에 해당한다. 앞의 예제를 기준으로 설명하면 다음과 같다:

상속 계층 구조에서 구현되지 않은 일부 기능은 선언적 파티셔닝에서 구현되어 있다. 따라서 레거시 상속을 사용한 파티셔닝이 애플리케이션에 유용한지 신중하게 판단해야 한다.

5.12. 테이블 파티셔닝

PostgreSQL은 기본적인 테이블 파티셔닝을 지원한다. 이 장에서는 데이터베이스 설계 과정에서 파티셔닝을 구현하는 이유와 방법을 설명한다.

5.12.1. 파티셔닝 개요

파티셔닝은 논리적으로 하나의 큰 테이블을 여러 개의 작은 물리적 조각으로 나누는 기술이다. 파티셔닝은 다음과 같은 여러 이점을 제공한다:

이러한 이점은 테이블이 매우 클 때만 의미가 있다. 파티셔닝이 유용한 정확한 시점은 애플리케이션에 따라 다르지만, 일반적으로 테이블 크기가 데이터베이스 서버의 물리적 메모리를 초과할 때를 기준으로 삼는다.

PostgreSQL은 다음과 같은 파티셔닝 방식을 기본적으로 지원한다:

범위 파티셔닝 #

하나 또는 여러 개의 키 컬럼을 기준으로 “범위”를 정의하여 테이블을 분할하며, 서로 다른 파티션에 할당된 값의 범위는 겹치지 않는다. 예를 들어, 날짜 범위나 특정 비즈니스 객체의 식별자 범위로 파티션을 나눌 수 있다. 각 범위의 경계는 하한값은 포함하고 상한값은 제외한다. 예를 들어, 한 파티션의 범위가 1에서 10이고 다음 파티션의 범위가 10에서 20이라면, 값 10은 두 번째 파티션에 속한다.

리스트 파티셔닝 #

각 파티션에 포함될 키 값을 명시적으로 나열하여 테이블을 분할한다.

해시 파티셔닝 #

각 파티션에 대한 모듈러스와 나머지 값을 지정하여 테이블을 분할한다. 파티션 키의 해시 값을 지정된 모듈러스로 나눈 나머지가 지정된 값과 일치하는 행을 해당 파티션에 저장한다.

애플리케이션에서 위에서 설명하지 않은 다른 형태의 파티셔닝이 필요한 경우, 상속이나 UNION ALL 뷰와 같은 대체 방법을 사용할 수 있다. 이러한 방법은 유연성을 제공하지만 기본 제공되는 선언적 파티셔닝의 성능상 이점 일부를 얻을 수 없다.

5.12.2. 선언적 파티셔닝

PostgreSQL은 테이블을 여러 파티션으로 나누는 기능을 제공한다. 이렇게 분할된 테이블을 *파티션 테이블*이라고 부른다. 파티션 테이블을 선언할 때는 위에서 설명한 *파티셔닝 방식*과 함께 *파티션 키*로 사용할 컬럼이나 표현식 목록을 지정해야 한다.

파티션 테이블은 실제 저장 공간을 가지지 않는 ‘가상’ 테이블이다. 대신 실제 데이터는 파티션 테이블과 연결된 일반 테이블인 *파티션*에 저장된다. 각 파티션은 *파티션 경계*에 따라 정의된 데이터의 부분집합을 저장한다. 파티션 테이블에 삽입되는 모든 행은 파티션 키 컬럼의 값을 기준으로 적절한 파티션으로 자동 분류된다. 행의 파티션 키 값이 변경되면, 해당 행은 원래 파티션의 경계 조건을 더 이상 만족하지 않을 경우 다른 파티션으로 자동 이동한다.

파티션 자체를 다시 파티션 테이블로 정의할 수 있는데, 이를 *하위 파티셔닝*이라고 한다. 모든 파티션은 상위 파티션 테이블과 동일한 컬럼을 가져야 하지만, 각 파티션마다 고유한 인덱스, 제약조건, 기본값을 설정할 수 있다. 파티션 테이블과 파티션 생성에 대한 자세한 내용은 CREATE TABLE 문서를 참조한다.

일반 테이블을 파티션 테이블로 변경하거나 그 반대로 변경하는 것은 불가능하다. 하지만 기존의 일반 테이블이나 파티션 테이블을 다른 파티션 테이블의 파티션으로 추가하거나, 파티션을 파티션 테이블에서 분리하여 독립적인 테이블로 만들 수 있다. 이러한 기능은 많은 유지보수 작업을 단순화하고 가속화한다. ATTACH PARTITION과 DETACH PARTITION 하위 명령에 대한 자세한 내용은 ALTER TABLE 문서에서 확인할 수 있다.

파티션으로 외부 테이블을 사용할 수도 있다. 단, 외부 테이블의 내용이 파티셔닝 규칙을 만족하는지 확인하는 것은 사용자의 책임이므로 세심한 주의가 필요하다. 이 외에도 몇 가지 제약사항이 있다. 자세한 내용은 CREATE FOREIGN TABLE 문서를 참조한다.

5.12.2.1. 파티셔닝 예제

대규모 아이스크림 회사를 위한 데이터베이스를 구축하는 상황을 가정해보자. 이 회사는 매일 최고 기온과 각 지역의 아이스크림 판매량을 측정한다. 개념적으로 다음과 같은 테이블이 필요하다:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

대부분의 쿼리가 지난 주, 월, 분기의 데이터만 조회한다는 점을 알고 있다. 이 테이블의 주요 용도는 경영진을 위한 온라인 보고서 작성이기 때문이다. 오래된 데이터의 저장량을 줄이기 위해 최근 3년 치의 데이터만 보관하기로 결정했다. 매월 초에 가장 오래된 월의 데이터를 삭제할 것이다. 이러한 상황에서 파티셔닝을 활용하면 measurement 테이블의 다양한 요구사항을 효과적으로 충족할 수 있다.

선언적 파티셔닝을 사용하기 위해 다음 단계를 따른다:

  1. PARTITION BY 절을 지정하여 파티션 테이블로 measurement 테이블을 생성한다. 이 절에는 파티셔닝 방법(이 경우 RANGE)과 파티션 키로 사용할 컬럼 목록을 포함한다.

    CREATE TABLE measurement (
city_id         int not null,
logdate         date not null,
peaktemp        int,
unitsales       int
) PARTITION BY RANGE (logdate);

  2. 파티션을 생성한다. 각 파티션 정의는 부모의 파티셔닝 방식과 파티션 키에 해당하는 경계를 지정해야 한다. 새 파티션의 값이 하나 이상의 기존 파티션과 중복되는 경계를 지정하면 오류가 발생한다.

이렇게 생성된 파티션은 일반적인 PostgreSQL 테이블(또는 외부 테이블)과 동일하다. 각 파티션별로 테이블스페이스와 저장 매개변수를 별도로 지정할 수 있다.

이 예제에서는 매월 데이터를 삭제해야 하므로, 각 파티션이 한 달치 데이터를 저장해야 한다. 명령어는 다음과 같다:

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
    FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

CREATE TABLE measurement_y2007m12 PARTITION OF measurement
    FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
    TABLESPACE fasttablespace;

CREATE TABLE measurement_y2008m01 PARTITION OF measurement
    FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
    WITH (parallel_workers = 4)
    TABLESPACE fasttablespace;

(인접한 파티션은 경계값을 공유할 수 있다. 범위의 상한은 배타적 경계로 처리되기 때문이다.)

서브 파티셔닝을 구현하려면 개별 파티션을 생성하는 명령에서 PARTITION BY 절을 다시 지정한다. 예를 들면:

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
    PARTITION BY RANGE (peaktemp);

measurement_y2006m02의 파티션을 생성한 후, measurement에 삽입되는 데이터 중 measurement_y2006m02에 매핑되는 데이터(또는 파티션 제약조건을 만족하는 경우 measurement_y2006m02에 직접 삽입되는 데이터)는 peaktemp 컬럼을 기준으로 해당 파티션으로 추가로 리디렉션된다. 지정된 파티션 키는 부모의 파티션 키와 중복될 수 있지만, 서브 파티션의 경계를 지정할 때는 해당 데이터 세트가 파티션 자체의 경계가 허용하는 것의 부분집합이 되도록 주의해야 한다. 시스템은 이를 자동으로 확인하지 않는다.

기존 파티션에 매핑되지 않는 데이터를 부모 테이블에 삽입하면 오류가 발생한다. 적절한 파티션을 수동으로 추가해야 한다.

파티션의 경계 조건을 설명하는 테이블 제약조건을 수동으로 생성할 필요는 없다. 이러한 제약조건은 자동으로 생성된다.

  1. 파티션된 테이블의 키 컬럼에 인덱스를 생성하고, 필요한 다른 인덱스도 생성한다. (키 인덱스가 반드시 필요한 것은 아니지만, 대부분의 시나리오에서 도움이 된다.) 이렇게 하면 각 파티션에 자동으로 일치하는 인덱스가 생성되며, 나중에 생성하거나 연결하는 파티션도 이러한 인덱스를 갖게 된다. 파티션된 테이블에 선언된 인덱스나 고유 제약조건은 파티션된 테이블과 같은 방식으로 “가상”이다. 실제 데이터는 개별 파티션 테이블의 자식 인덱스에 있다.

    CREATE INDEX ON measurement (logdate);

  2. postgresql.conf에서 enable_partition_pruning 설정 매개변수가 비활성화되지 않았는지 확인한다. 비활성화된 경우 쿼리가 원하는 대로 최적화되지 않는다.

위 예제에서는 매월 새로운 파티션을 생성해야 하므로, 필요한 DDL을 자동으로 생성하는 스크립트를 작성하는 것이 현명할 것이다.

5.12.2.2. 파티션 유지보수

테이블을 처음 정의할 때 설정한 파티션 구조는 대개 고정적이지 않다. 오래된 데이터가 있는 파티션을 제거하고 새로운 데이터를 위한 파티션을 주기적으로 추가하는 것이 일반적이다. 파티셔닝의 가장 큰 장점 중 하나는 대량의 데이터를 물리적으로 이동시키는 대신, 파티션 구조를 조작하여 이러한 작업을 거의 즉각적으로 수행할 수 있다는 점이다.

오래된 데이터를 제거하는 가장 간단한 방법은 더 이상 필요하지 않은 파티션을 삭제하는 것이다:

DROP TABLE measurement_y2006m02;

이 명령은 각 레코드를 개별적으로 삭제할 필요 없이 수백만 개의 레코드를 매우 빠르게 삭제할 수 있다. 단, 이 명령을 실행하려면 상위 테이블에 대한 ACCESS EXCLUSIVE 잠금이 필요하다.

또 다른 방법으로는 파티션을 파티션드 테이블에서 분리하되, 독립적인 테이블로 접근할 수 있게 유지하는 것이다. 이 방법은 두 가지 형태가 있다:

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY;

이러한 방식을 사용하면 데이터를 삭제하기 전에 추가 작업을 수행할 수 있다. 예를 들어, COPY, pg_dump 또는 유사한 도구를 사용하여 데이터를 백업하기에 좋은 시점이 될 수 있다. 또한 데이터를 더 작은 형식으로 집계하거나, 다른 데이터 조작을 수행하거나, 보고서를 실행하기에도 적절한 시점이 될 수 있다. 첫 번째 형태의 명령은 상위 테이블에 대한 ACCESS EXCLUSIVE 잠금이 필요하다. 두 번째 형태처럼 CONCURRENTLY 한정자를 추가하면 상위 테이블에 대해 SHARE UPDATE EXCLUSIVE 잠금만 필요하지만, 자세한 제약 사항은 ALTER TABLE ... DETACH PARTITION을 참조하기 바란다.

마찬가지로 새로운 데이터를 처리하기 위한 새 파티션을 추가할 수 있다. 앞서 원본 파티션을 생성했던 것처럼 파티션드 테이블에 빈 파티션을 생성할 수 있다:

CREATE TABLE measurement_y2008m02 PARTITION OF measurement
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
    TABLESPACE fasttablespace;

새 파티션을 생성하는 대신, 파티션 구조와 별개로 새 테이블을 생성한 후 나중에 파티션으로 연결하는 것이 더 편리할 때가 있다. 이 방법을 사용하면 새로운 데이터를 파티션드 테이블에 표시하기 전에 로드, 검사, 변환할 수 있다. 또한 ATTACH PARTITION 작업은 CREATE TABLE ... PARTITION OF가 필요로 하는 ACCESS EXCLUSIVE 잠금 대신 SHARE UPDATE EXCLUSIVE 잠금만 필요하므로 파티션드 테이블의 동시 작업에 더 친화적이다. 자세한 내용은 ALTER TABLE ... ATTACH PARTITION을 참조하기 바란다. 상위 테이블의 정의를 번거롭게 반복하지 않도록 CREATE TABLE ... LIKE 옵션을 사용하면 도움이 될 수 있다. 예를 들면:

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
  TABLESPACE fasttablespace;

ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );

copy measurement_y2008m02 from 'measurement_y2008m02'
-- 필요한 경우 다른 데이터 준비 작업 수행

ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );

ATTACH PARTITION 명령을 실행할 때는 해당 파티션에 대한 ACCESS EXCLUSIVE 잠금을 유지하면서 파티션 제약 조건을 검증하기 위해 테이블을 스캔한다. 위에서 보여준 것처럼, 이 스캔을 피하기 위해 테이블을 연결하기 전에 예상되는 파티션 제약 조건과 일치하는 CHECK 제약 조건을 생성하는 것이 좋다. ATTACH PARTITION이 완료되면 이제 중복된 CHECK 제약 조건을 삭제하는 것이 좋다. 연결하려는 테이블이 파티션드 테이블인 경우, 적절한 CHECK 제약 조건을 만나거나 리프 파티션에 도달할 때까지 각 하위 파티션이 재귀적으로 잠기고 스캔된다.

마찬가지로, 파티션드 테이블에 DEFAULT 파티션이 있는 경우, 연결할 파티션의 제약 조건을 제외하는 CHECK 제약 조건을 생성하는 것이 좋다. 이렇게 하지 않으면 DEFAULT 파티션에 연결하려는 파티션에 위치해야 할 레코드가 없는지 확인하기 위해 스캔이 수행된다. 이 작업은 DEFAULT 파티션에 대한 ACCESS EXCLUSIVE 잠금을 유지하면서 수행된다. DEFAULT 파티션이 파티션드 테이블인 경우, 위에서 언급한 것처럼 연결하려는 테이블과 동일한 방식으로 각 파티션이 재귀적으로 확인된다.

앞서 언급했듯이, 파티션드 테이블에 인덱스를 생성하여 전체 계층 구조에 자동으로 적용되게 할 수 있다. 이는 기존 파티션뿐만 아니라 향후 파티션에도 인덱스가 적용되므로 매우 편리하다. 그러나 파티션드 테이블에 새 인덱스를 생성할 때의 한 가지 제한 사항은 CONCURRENTLY 한정자를 사용할 수 없다는 것이며, 이는 긴 잠금 시간으로 이어질 수 있다. 이를 피하기 위해 파티션드 테이블에 대해 CREATE INDEX ON ONLY를 사용할 수 있다. 이렇게 하면 새 인덱스가 무효로 표시되어 기존 파티션에 자동 적용되는 것을 방지한다. 대신 CONCURRENTLY를 사용하여 각 파티션에 개별적으로 인덱스를 생성한 다음, ALTER INDEX ... ATTACH PARTITION을 사용하여 상위 인덱스에 *연결*할 수 있다. 모든 파티션의 인덱스가 상위 인덱스에 연결되면 상위 인덱스가 자동으로 유효로 표시된다. 예시:

CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);

CREATE INDEX CONCURRENTLY measurement_usls_200602_idx
    ON measurement_y2006m02 (unitsales);
ALTER INDEX measurement_usls_idx
    ATTACH PARTITION measurement_usls_200602_idx;
...

이 기법은 UNIQUE와 PRIMARY KEY 제약 조건에도 사용할 수 있다. 제약 조건이 생성될 때 인덱스가 암시적으로 생성된다. 예시:

ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);

ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);
ALTER INDEX measurement_city_id_logdate_key
    ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;
...

5.12.2.3. 파티셔닝 제한사항

파티셔닝된 테이블에는 다음과 같은 제한사항이 있다:

각 파티션은 내부적으로 상속을 사용하여 파티셔닝된 테이블과 연결된다. 하지만 아래에서 설명하는 바와 같이, 선언적으로 파티셔닝된 테이블이나 그 파티션에서는 상속의 모든 일반적인 기능을 사용할 수 없다. 특히 파티션은 자신이 속한 파티셔닝된 테이블 이외의 부모를 가질 수 없으며, 테이블이 파티셔닝된 테이블과 일반 테이블 모두를 상속할 수도 없다. 이는 파티셔닝된 테이블과 그 파티션이 일반 테이블과 상속 계층을 공유하지 않는다는 것을 의미한다.

파티셔닝된 테이블과 그 파티션으로 구성된 파티션 계층도 상속 계층이므로, 5.11절에서 설명한 대로 tableoid와 모든 일반적인 상속 규칙이 적용된다. 다만 다음과 같은 예외가 있다:

5.12.3. 상속을 이용한 파티셔닝

기본으로 제공되는 선언적 파티셔닝은 대부분의 일반적인 사용 사례에 적합하지만, 더 유연한 접근이 필요한 상황도 있다. 테이블 상속을 활용하면 선언적 파티셔닝이 지원하지 않는 여러 기능을 구현할 수 있다. 테이블 상속의 장점은 다음과 같다:

5.12.3.1. 예제

이 예제는 앞서 설명한 선언적 파티셔닝과 동일한 파티셔닝 구조를 구축한다. 다음 단계를 따라 진행한다:

  1. 먼저 “루트” 테이블을 생성한다. 모든 “자식” 테이블이 이 테이블을 상속한다. 이 테이블은 실제 데이터를 포함하지 않는다. 모든 자식 테이블에 동일하게 적용하려는 경우가 아니라면 이 테이블에 검사 제약 조건을 정의하지 않는다. 인덱스나 고유 제약 조건을 정의하는 것도 의미가 없다. 예제에서는 루트 테이블로 measurement 테이블을 다음과 같이 정의한다:

    CREATE TABLE measurement (
city_id         int not null,
logdate         date not null,
peaktemp        int,
unitsales       int
);

  2. 루트 테이블을 상속하는 여러 “자식” 테이블을 생성한다. 일반적으로 이러한 테이블은 루트 테이블에서 상속받은 컬럼 외에 추가 컬럼을 정의하지 않는다. 선언적 파티셔닝과 마찬가지로, 이 테이블들은 모든 면에서 일반적인 PostgreSQL 테이블(또는 외부 테이블)이다.

    CREATE TABLE measurement_y2006m02 () INHERITS (measurement);
CREATE TABLE measurement_y2006m03 () INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 () INHERITS (measurement);
CREATE TABLE measurement_y2007m12 () INHERITS (measurement);
CREATE TABLE measurement_y2008m01 () INHERITS (measurement);

  3. 자식 테이블에 겹치지 않는 테이블 제약 조건을 추가하여 각 테이블에서 허용되는 키 값을 정의한다.

일반적인 예제는 다음과 같다:

CHECK ( x = 1 )
CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
CHECK ( outletID >= 100 AND outletID < 200 )

서로 다른 자식 테이블 간에 허용되는 키 값이 겹치지 않도록 제약 조건을 설정해야 한다. 다음과 같은 범위 제약 조건은 흔한 실수다:

CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )

이는 키 값 200이 어느 자식 테이블에 속하는지 명확하지 않으므로 잘못된 방식이다. 대신 범위를 다음과 같이 정의해야 한다:

CREATE TABLE measurement_y2006m02 (
    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2006m03 (
    CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);

...
CREATE TABLE measurement_y2007m11 (
    CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2007m12 (
    CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2008m01 (
    CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);

  1. 각 자식 테이블에 대해 키 컬럼에 인덱스를 생성하고, 필요한 다른 인덱스도 생성한다.

    CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

  2. 애플리케이션에서 INSERT INTO measurement ...와 같이 실행하면 데이터가 적절한 자식 테이블로 전달되도록 설정한다. 이는 루트 테이블에 적절한 트리거 함수를 연결하여 구현할 수 있다. 데이터를 가장 최근의 자식 테이블에만 추가한다면, 매우 간단한 트리거 함수를 사용할 수 있다:

    CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

함수를 생성한 후, 트리거 함수를 호출하는 트리거를 생성한다:

CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();

현재 자식 테이블에 항상 삽입되도록 하려면 매월 트리거 함수를 재정의해야 한다. 하지만 트리거 정의 자체는 갱신할 필요가 없다.

데이터를 삽입할 때 서버가 자동으로 적절한 자식 테이블을 찾아 행을 추가하도록 할 수도 있다. 이를 위해서는 더 복잡한 트리거 함수가 필요하다. 예를 들면:

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.logdate >= DATE '2006-02-01' AND
         NEW.logdate < DATE '2006-03-01' ) THEN
        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
            NEW.logdate < DATE '2006-04-01' ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
    ...
    ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
            NEW.logdate < DATE '2008-02-01' ) THEN
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION '날짜가 범위를 벗어났습니다. measurement_insert_trigger() 함수를 수정하세요!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

트리거 정의는 이전과 동일하다. 각 IF 테스트는 해당 자식 테이블의 CHECK 제약 조건과 정확히 일치해야 한다는 점에 유의한다.

이 함수는 단일 월 케이스보다 복잡하지만, 필요한 시점보다 미리 분기를 추가할 수 있으므로 자주 갱신할 필요가 없다.

참고

실제로는 대부분의 삽입이 최신 자식 테이블에 이루어진다면, 가장 최근의 자식을 먼저 검사하는 것이 좋을 수 있다. 이 예제에서는 단순성을 위해 다른 부분과 동일한 순서로 트리거 테스트를 보여주었다.

적절한 자식 테이블로 삽입을 리디렉션하는 또 다른 방법은 트리거 대신 루트 테이블에 규칙을 설정하는 것이다. 예를 들면:

CREATE RULE measurement_insert_y2006m02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
DO INSTEAD
    INSERT INTO measurement_y2006m02 VALUES (NEW.*);
...
CREATE RULE measurement_insert_y2008m01 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
DO INSTEAD
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);

규칙은 트리거보다 상당히 많은 오버헤드를 발생시키지만, 이 오버헤드는 행당이 아닌 쿼리당 한 번만 발생한다. 따라서 대량 삽입 상황에서는 이 방법이 유리할 수 있다. 하지만 대부분의 경우 트리거 방식이 더 나은 성능을 제공할 것이다.

COPY 명령은 규칙을 무시한다는 점에 주의해야 한다. COPY를 사용하여 데이터를 삽입하려면 루트 테이블이 아닌 올바른 자식 테이블에 직접 복사해야 한다. COPY는 트리거를 실행하므로 트리거 방식을 사용한다면 정상적으로 사용할 수 있다.

규칙 방식의 또 다른 단점은 규칙 세트가 삽입 날짜를 포함하지 않을 때 오류를 강제하는 간단한 방법이 없다는 것이다. 이 경우 데이터는 조용히 루트 테이블로 들어가버린다.

  1. postgresql.conf에서 constraint_exclusion 설정 매개변수가 비활성화되지 않았는지 확인한다. 그렇지 않으면 자식 테이블에 불필요하게 접근할 수 있다.

보다시피 복잡한 테이블 계층 구조를 위해서는 상당한 양의 DDL이 필요할 수 있다. 위 예제에서는 매월 새로운 자식 테이블을 생성해야 하므로, 필요한 DDL을 자동으로 생성하는 스크립트를 작성하는 것이 현명할 것이다.

5.12.3.2. 상속 파티셔닝 유지보수

오래된 데이터를 빠르게 제거하려면 더 이상 필요하지 않은 자식 테이블을 삭제하면 된다:

DROP TABLE measurement_y2006m02;

자식 테이블을 상속 계층 구조에서 제거하면서도 독립적인 테이블로 유지하고 싶다면 다음과 같이 실행한다:

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

새로운 데이터를 처리하기 위해 자식 테이블을 추가하려면, 앞서 생성했던 방식과 동일하게 빈 자식 테이블을 생성한다:

CREATE TABLE measurement_y2008m02 (
    CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
) INHERITS (measurement);

또는 테이블 계층 구조에 추가하기 전에 새 자식 테이블을 먼저 생성하고 데이터를 채울 수도 있다. 이 방식은 데이터를 부모 테이블의 쿼리에 노출하기 전에 로드, 검증, 변환할 수 있는 장점이 있다.

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
copy measurement_y2008m02 from 'measurement_y2008m02'
-- 필요한 경우 추가 데이터 준비 작업 수행
ALTER TABLE measurement_y2008m02 INHERIT measurement;

5.12.3.3. 주의사항

상속을 사용한 파티셔닝 구현 시 다음 사항에 주의한다:

루트 테이블만 처리한다.

5.12.4. 파티션 프루닝

파티션 프루닝(또는 파티션 가지치기)은 선언적 파티션 테이블의 성능을 향상시키는 쿼리 최적화 기법이다. 다음 예제를 통해 살펴보자:

SET enable_partition_pruning = on;                 -- 기본값
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

파티션 프루닝을 사용하지 않으면 위 쿼리는 measurement 테이블의 모든 파티션을 검색한다. 반면 파티션 프루닝을 활성화하면 플래너는 각 파티션의 정의를 검사하여 쿼리의 WHERE 절을 만족하는 행을 포함할 수 없다고 판단되는 파티션은 검색 대상에서 제외한다. 플래너가 이를 증명할 수 있다면 해당 파티션을 쿼리 계획에서 제거(프루닝)한다.

EXPLAIN 명령과 enable_partition_pruning 설정 매개변수를 사용하면 파티션이 제거된 계획과 그렇지 않은 계획의 차이를 확인할 수 있다. 이러한 테이블 구성에서 최적화되지 않은 일반적인 계획은 다음과 같다:

SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-------------------------------------------------------------------​----------------
 Aggregate  (cost=188.76..188.77 rows=1 width=8)
   ->  Append  (cost=0.00..181.05 rows=3085 width=0)
         ->  Seq Scan on measurement_y2006m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m11  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2007m12  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)

일부 또는 모든 파티션에서 전체 테이블 순차 스캔 대신 인덱스 스캔을 사용할 수 있다. 하지만 여기서 중요한 점은 이 쿼리에 대한 답을 얻기 위해 이전 파티션들을 전혀 스캔할 필요가 없다는 것이다. 파티션 프루닝을 활성화하면 동일한 결과를 훨씬 적은 비용으로 제공하는 계획을 얻을 수 있다:

SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-------------------------------------------------------------------​----------------
 Aggregate  (cost=37.75..37.76 rows=1 width=8)
   ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
         Filter: (logdate >= '2008-01-01'::date)

파티션 프루닝은 파티션 키에 의해 암시적으로 정의된 제약 조건에 의해서만 동작하며, 인덱스의 존재 여부와는 무관하다. 따라서 키 컬럼에 인덱스를 정의할 필요가 없다. 특정 파티션에 인덱스를 생성할지 여부는 해당 파티션을 검색하는 쿼리가 일반적으로 파티션의 큰 부분을 검색하는지 아니면 작은 부분만 검색하는지에 따라 결정된다. 후자의 경우에만 인덱스가 도움이 된다.

파티션 프루닝은 쿼리 계획 수립 시점뿐만 아니라 실행 중에도 수행할 수 있다. 이는 PREPARE 문에 정의된 매개변수나 서브쿼리에서 얻은 값, 또는 중첩 루프 조인의 내부에서 매개변수화된 값과 같이 쿼리 계획 수립 시점에 알 수 없는 값을 포함하는 절이 있을 때 더 많은 파티션을 제거할 수 있게 해준다. 실행 중 파티션 프루닝은 다음과 같은 시점에 수행될 수 있다:

파티션 프루닝은 enable_partition_pruning 설정을 통해 비활성화할 수 있다.

5.12.5. 파티셔닝과 제약 조건 제외

*제약 조건 제외(Constraint exclusion)*는 파티션 프루닝(partition pruning)과 유사한 쿼리 최적화 기법이다. 주로 레거시 상속 방식으로 구현된 파티셔닝에 사용하지만, 선언적 파티셔닝을 포함한 다른 용도로도 활용할 수 있다.

제약 조건 제외는 파티션 프루닝과 매우 비슷한 방식으로 동작한다. 다만 파티션 프루닝이 선언적 파티셔닝에서만 존재하는 파티션 경계를 사용하는 반면, 제약 조건 제외는 각 테이블의 CHECK 제약 조건을 활용한다는 점이 다르다. 또한 제약 조건 제외는 계획 시점에만 적용되며, 실행 시점에는 파티션 제거를 시도하지 않는다.

제약 조건 제외가 CHECK 제약 조건을 사용하는 특성 때문에 파티션 프루닝보다 속도가 느리다는 단점이 있다. 하지만 이는 장점이 될 수도 있다. 선언적 파티셔닝 테이블에서도 내부 파티션 경계 외에 제약 조건을 정의할 수 있기 때문에, 제약 조건 제외를 통해 쿼리 계획에서 추가 파티션을 제거할 수 있다.

constraint_exclusion의 기본값이자 권장값은 on이나 off가 아닌 중간 설정인 partition이다. 이 설정은 상속 파티셔닝 테이블에서 동작할 가능성이 높은 쿼리에만 이 기법을 적용한다. on 설정을 사용하면 단순한 쿼리를 포함한 모든 쿼리에서 플래너가 CHECK 제약 조건을 검사한다.

제약 조건 제외 사용 시 다음 사항을 주의해야 한다:

5.12.6. 선언적 파티셔닝의 모범 사례

테이블 파티셔닝 방식을 선택할 때는 신중한 결정이 필요하다. 잘못된 설계는 쿼리 계획과 실행 성능에 부정적인 영향을 미칠 수 있기 때문이다.

가장 중요한 설계 결정 중 하나는 데이터를 어떤 컬럼을 기준으로 파티셔닝할지 선택하는 것이다. 일반적으로 파티셔닝된 테이블에서 실행되는 쿼리의 WHERE 절에 가장 자주 등장하는 컬럼이나 컬럼 집합을 기준으로 파티셔닝하는 것이 최선이다. 파티션 경계 제약조건과 호환되는 WHERE 절은 불필요한 파티션을 제거하는 데 활용할 수 있다. 하지만 PRIMARY KEY나 UNIQUE 제약조건 때문에 다른 선택을 해야 할 수도 있다. 파티셔닝 전략을 계획할 때는 불필요한 데이터 제거도 고려해야 한다. 전체 파티션을 빠르게 분리할 수 있으므로, 한 번에 제거할 모든 데이터가 단일 파티션에 위치하도록 파티션 전략을 설계하면 유리하다.

테이블을 몇 개의 파티션으로 나눌지 결정하는 것도 매우 중요하다. 파티션 수가 너무 적으면 인덱스가 너무 커지고 데이터 지역성이 떨어져 캐시 적중률이 낮아질 수 있다. 반면 파티션을 너무 많이 나누면 다른 문제가 발생한다. 파티션이 너무 많으면 쿼리 계획 시간이 길어지고, 쿼리 계획과 실행 단계에서 메모리 사용량이 증가한다. 테이블 파티셔닝 방식을 선택할 때는 미래의 변화도 고려해야 한다. 예를 들어, 현재 소수의 대형 고객을 보유하고 있어서 고객별로 하나의 파티션을 사용하기로 했다면, 수년 후 다수의 소형 고객으로 상황이 바뀌었을 때의 영향도 생각해봐야 한다. 이런 경우에는 LIST 파티셔닝으로 고객 수 증가를 기대하기보다는 HASH 파티셔닝을 선택하고 적절한 파티션 수를 정하는 것이 더 나은 방법일 수 있다.

다른 파티션보다 더 커질 것으로 예상되는 파티션은 서브파티셔닝을 활용해 더 세분화할 수 있다. 또는 파티션 키에 여러 컬럼을 사용하는 범위 파티셔닝도 대안이 될 수 있다. 하지만 이러한 방법들은 쉽게 과도한 수의 파티션을 만들 수 있으므로 신중하게 접근해야 한다.

쿼리 계획과 실행 단계에서 파티셔닝에 따른 오버헤드를 고려하는 것이 중요하다. 쿼리 플래너는 일반적으로 수천 개 정도의 파티션 계층을 잘 처리할 수 있다. 단, 일반적인 쿼리가 대부분의 파티션을 제거하고 소수의 파티션만 남기는 경우에 한한다. 플래너가 파티션 제거를 수행한 후에도 많은 파티션이 남아있으면 계획 시간이 길어지고 메모리 사용량이 증가한다. 또한 많은 세션이 다수의 파티션에 접근할 경우 서버의 메모리 사용량이 시간이 지남에 따라 크게 증가할 수 있다. 이는 각 세션이 접근하는 파티션의 메타데이터를 로컬 메모리에 로드해야 하기 때문이다.

데이터 웨어하우스 형태의 워크로드에서는 OLTP 워크로드보다 더 많은 수의 파티션을 사용하는 것이 합리적일 수 있다. 데이터 웨어하우스에서는 대부분의 처리 시간이 쿼리 실행에 소요되므로 쿼리 계획 시간이 덜 중요하기 때문이다. 두 유형의 워크로드 모두에서 초기에 올바른 결정을 내리는 것이 중요하다. 대용량 데이터의 재파티셔닝은 매우 느린 작업이 될 수 있다. 의도한 워크로드를 시뮬레이션해보면 파티셔닝 전략을 최적화하는 데 도움이 된다. 더 많은 파티션이 더 적은 파티션보다 항상 좋다거나 그 반대라고 섣불리 가정해서는 안 된다.

5.13. 외부 데이터

PostgreSQL은 SQL/MED 명세의 일부를 구현하여 PostgreSQL 외부에 있는 데이터에 일반 SQL 쿼리로 접근할 수 있다. 이러한 데이터를 *외부 데이터*라고 한다. (이는 데이터베이스 내의 제약 조건 타입인 외래 키와는 다른 개념이다.)

외부 데이터는 *외부 데이터 래퍼(foreign data wrapper)*를 통해 접근한다. 외부 데이터 래퍼는 외부 데이터 소스와 통신할 수 있는 라이브러리로, 데이터 소스 연결과 데이터 획득 과정의 세부 사항을 추상화한다. contrib 모듈로 제공되는 외부 데이터 래퍼들이 있으며, 자세한 내용은 부록 F에서 확인할 수 있다. 다른 종류의 외부 데이터 래퍼는 서드파티 제품으로도 찾을 수 있다. 기존 외부 데이터 래퍼가 요구사항에 맞지 않는다면 직접 작성할 수도 있다. 자세한 내용은 57장을 참조한다.

외부 데이터에 접근하려면 먼저 외부 서버 객체를 생성해야 한다. 이 객체는 해당 외부 데이터 래퍼가 사용하는 옵션 집합에 따라 특정 외부 데이터 소스에 연결하는 방법을 정의한다. 그 다음 하나 이상의 *외부 테이블*을 생성해야 하는데, 이는 원격 데이터의 구조를 정의한다. 외부 테이블은 일반 테이블처럼 쿼리에서 사용할 수 있지만, PostgreSQL 서버에는 실제 저장 공간을 차지하지 않는다. 외부 테이블이 사용될 때마다 PostgreSQL은 외부 데이터 래퍼에게 외부 소스로부터 데이터를 가져오거나, 갱신 명령의 경우 외부 소스로 데이터를 전송하도록 요청한다.

원격 데이터에 접근하려면 외부 데이터 소스에 대한 인증이 필요할 수 있다. 이러한 정보는 *사용자 매핑*을 통해 제공할 수 있으며, 현재 PostgreSQL 역할을 기반으로 사용자 이름과 비밀번호와 같은 추가 데이터를 제공할 수 있다.

더 자세한 정보는 다음 문서를 참조한다: - 외부 데이터 래퍼 생성 - 서버 생성 - 사용자 매핑 생성 - 외부 테이블 생성 - 외부 스키마 가져오기

5.14. 기타 데이터베이스 객체

관계형 데이터베이스 구조에서 테이블은 데이터를 저장하는 핵심 객체다. 하지만 데이터베이스에는 테이블만 존재하는 것이 아니다. 데이터의 활용과 관리를 더욱 효율적이고 편리하게 만들기 위한 다양한 종류의 객체들이 있다. 이 장에서 자세히 다루지는 않지만, 어떤 것들이 있는지 알아두면 좋을 만한 객체들을 다음과 같이 정리했다:

이러한 주제들에 대한 자세한 내용은 5부: 서버 프로그래밍에서 확인할 수 있다.