Postgres 19의 네이티브 temporal table 지원이 `WITHOUT OVERLAPS`, `FOR PORTION OF`, temporal foreign key를 통해 무엇을 바꾸는지 살펴봅니다.
최근 들어 데이터베이스 분야에 새로운 유형의 질문이 등장했다. 이 데이터는 지난 화요일에 어떤 모습이었을까? 아마도 연휴 세일이 시작되기 전 상품 가격일 수도 있고, 아무도 바라지 않았던 그 조직개편 이전에 어떤 직원이 어느 부서에 속했는지일 수도 있다. 전체 감사 트리거 시스템을 추가하지 않고서, 정확히 그 날짜의 변경 전후에 데이터가 어떤 모습이었는지 어떻게 알 수 있을까?
SQL:2011 표준은 10여 년 전에 이미 temporal table로 이 문제에 대한 적절한 해법을 공식화했다. 다른 데이터베이스 엔진들은 그 일부를 비교적 빠르게 도입했다. Postgres답게, Postgres는 시간을 들였다. 하지만 Postgres 19는 마침내 네이티브 temporal table 지원을 이 파티에 가져오며 — 기다린 보람이 충분했다.
무엇을 다루게 되는지 살펴보자.
반짝이는 새 기능으로 넘어가기 전에, 비교를 위해 낡고 오래된 접근 방식부터 보자. 시간이 지나며 상품 가격을 추적하고 싶다고 해보자. 그럴듯한 첫 시도는 아마 이렇게 생겼을 것이다:
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE products (
product_id INT NOT NULL,
product_name TEXT NOT NULL,
price NUMERIC(10,2) NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE NOT NULL,
CONSTRAINT no_time_travel CHECK (valid_from < valid_to)
);
충분히 단순하다. 상품이 있고, 가격이 있고, 그 가격이 유효한 날짜 범위가 있다. 안타깝게도 같은 상품에 대해 날짜 범위가 겹치는 두 행을 넣는 것을 막아주는 장치는 없다. 42번 상품이 같은 화요일에 $9.99 이면서 동시에$14.99일 수도 있다. 그 사실을 알게 된 회계 담당자는 아마 꽤나 할 말이 많을 것이다.
이 경우 Postgres의 전통적인 해답은 btree_gist 확장과 exclusion constraint다:
ALTER TABLE products
ADD CONSTRAINT no_overlapping_prices
EXCLUDE USING gist (
product_id WITH =,
daterange(valid_from, valid_to) WITH &&
);
이건 동작한다. 충돌하는 행을 넣으려 하면, Postgres가 잡아낸다:
INSERT INTO products VALUES (1, 'Widget', 9.99, '2025-01-01', '2025-07-01');
INSERT INTO products VALUES (1, 'Widget', 12.99, '2025-06-01', '2026-01-01');
ERROR: conflicting key value violates exclusion constraint
"no_overlapping_prices"
btree_gist를 사용해서 문제가 해결되었다! 그런데 뭐가 문제일까? 몇 가지가 있다:
모두가 BTREE와 일반적인 인덱싱은 알지만, GiST는 Postgres에 특화되어 있어서 이해하려면 경험이 필요하다. 게다가 선택적 확장이라는 점까지 더하면 더더욱 그렇다.
exclusion constraint 문법은 꽤 직관적이지 않다. 문서에는 나와 있지만, 그렇지 않다면 누가 이걸 표준적인 접근 방식이라고 떠올리겠는가.
테이블 자체에 temporal 인식이 내장되어 있지 않다.
기본적으로 Postgres는 이것이 temporal data라는 사실을 이해하지 못한다. 그저 컬럼들과 특이한 인덱스 유형을 사용하는 난해한 제약일 뿐이다. 시간 범위를 바꾸는 모든 업데이트는 행을 수동으로 쪼개고 다시 잇는 작업을 요구하며, 이는 temporal correctness의 전체 부담을 애플리케이션이 떠안는다는 뜻이다.
이건 최소한 중에서도 가장 최소한의 수준이고, 솔직히 우리는 더 잘할 수 있다.
Postgres에서 제대로 된 temporal 지원을 원한 것은 새로운 일이 아니다. SQL:2011 표준은 APPLICATION TIME period, WITHOUT OVERLAPS constraint, temporal DML을 위한 FOR PORTION OF 문법을 도입했다. 2011년은 아주 오래전이다.
Henrietta Dombrovskaya(친구들은 Hetti라고 부른다)는 Postgres 생태계에서 temporal data를 가장 이르게 옹호한 인물 중 하나였다. Chad Slaughter와 함께 그녀는 pg_bitemporal 확장을 개발했다. 이것은 PL/pgSQL을 사용해 Postgres 내부에서 완전히 bitemporal table을 관리하기 위한 프레임워크다. 그녀는 2015년부터 여러 컨퍼런스에서 이 개념을 발표하며, valid time(이 사실이 현실 세계에서 언제 참이었는가?)과 transaction time(데이터베이스가 이 사실을 언제 기록했는가?)을 동시에 추적하는 방법을 보여주었다.
이 구분은 중요하다. valid time은 “이 가격은 1월부터 6월까지 유효하다”라고 말한다. transaction time은 데이터베이스의 관점으로, “이 행은 3월 12일 오후 3시 47분에 삽입되었고 4월 3일 오전 9시 1분에 대체되었다”라고 말한다. 둘을 결합하면, “그 당시 우리가 알고 있던 바를 기준으로 지난 화요일에 우리는 가격이 얼마라고 생각했는가?” 같은 질문에 답할 수 있는 bitemporal table이 된다.
pg_bitemporal 접근 방식은 앞서 이야기한 동일한 EXCLUDE USING gist 메커니즘에 크게 의존했지만, 이를 두 배로 사용했다. 하나는 effective 범위(valid time)용이고 다른 하나는 asserted 범위(transaction time)용이다. 테이블 정의는 대략 이런 모습이었다:
CREATE TABLE bi_temporal.customers (
cust_nbr INTEGER,
cust_nm TEXT,
cust_type TEXT,
effective_range TSTZRANGE,
asserted_range TSTZRANGE,
row_created_at TIMESTAMPTZ,
EXCLUDE USING gist (
cust_nbr WITH =,
effective_range WITH &&,
asserted_range WITH &&
)
);
이는 단일 exclusion constraint로 강제되는 두 개의 temporal 차원이다. 이 확장은 또한 bitemporal insert, update, correction, inactivation, delete를 위한 함수들과, temporal reasoning을 위한 Allen's interval relationships 구현도 도입했다. 당시 Postgres가 제공하던 것 위에 쌓아 올린 장치로서는 엄청나게 많은 것이었다.
그리고 실제로 동작했다! 하지만 확장에는 한계가 있다. 쿼리 플래너가 temporal predicate를 어떻게 보는지 바꿀 수 없고, 엔진 수준에서 constraint 시스템과 통합할 수 없으며, 네이티브 DML 문법을 제공할 수도 없다. 그러려면 이 기능이 코어에 들어와야 했다.
이제 Postgres 19는 bi-temporal system의 application-time 절반을 수용한다. 전체 그림은 아니지만, 그래도 올바른 방향으로 가는 거대한 한 걸음이다.
이제 Postgres 19 방식으로 products 테이블을 다시 만들어보자. valid_from과 valid_to를 따로 두는 대신, 단일 range type 컬럼을 사용한다:
CREATE TABLE products (
product_id INT NOT NULL,
product_name TEXT NOT NULL,
price NUMERIC(10,2) NOT NULL,
valid_at DATERANGE NOT NULL,
PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS)
);
이게 전부다. btree_gist 확장이 필요 없다. exclusion constraint도 없다. 기본 키의 WITHOUT OVERLAPS 절은 product_id가 어떤 특정 시점에서든 고유해야 한다는 뜻이지만, valid_at 범위가 겹치지 않기만 하면 같은 상품이 여러 행을 가질 수 있다고 Postgres에 알려준다.
이건 예전 방식과 어떻게 비교될까? 나란히 놓고 보자. 옛 방식:
CREATE EXTENSION IF NOT EXISTS btree_gist;
-- 범위를 위한 두 개의 분리된 컬럼
valid_from DATE NOT NULL,
valid_to DATE NOT NULL,
-- constraint 안에서 수동으로 범위 구성
EXCLUDE USING gist (
product_id WITH =,
daterange(valid_from, valid_to) WITH &&
)
새 방식:
-- 단일 range 컬럼, 확장 없음
valid_at DATERANGE NOT NULL,
-- 기본 키에 내장된 temporal 인식
PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS)
더 깔끔하고, 더 표현력이 좋으며, Postgres가 우리가 무엇을 하는지 실제로 이해한다. 내부적으로 WITHOUT OVERLAPS constraint는 여전히 GiST 인덱스를 사용하고, 키 안의 non-temporal 컬럼을 위해서는 여전히 btree_gist가 필요하다. 하지만 Postgres는 constraint를 초기화할 때 그 의존성을 자동으로 처리한다. 아주 편리하다.
이 글의 나머지 부분에서 계속 사용할 데이터를 테이블에 넣어보자:
INSERT INTO products VALUES
(1, 'Widget', 9.99, '[2025-01-01, 2025-07-01)'),
(1, 'Widget', 12.99, '[2025-07-01, 2026-01-01)'),
(1, 'Widget', 11.99, '[2026-01-01, 2026-07-01)'),
(2, 'Gadget', 24.99, '[2025-01-01, 2025-04-01)'),
(2, 'Gadget', 22.99, '[2025-04-01, 2026-01-01)'),
(2, 'Gadget', 26.99, '[2026-01-01,)');
SELECT * FROM products ORDER BY product_id, valid_at;
product_id | product_name | price | valid_at
------------+--------------+-------+-------------------------
1 | Widget | 9.99 | [2025-01-01,2025-07-01)
1 | Widget | 12.99 | [2025-07-01,2026-01-01)
1 | Widget | 11.99 | [2026-01-01,2026-07-01)
2 | Gadget | 24.99 | [2025-01-01,2025-04-01)
2 | Gadget | 22.99 | [2025-04-01,2026-01-01)
2 | Gadget | 26.99 | [2026-01-01,)
range 표기법에 주목하자: [는 inclusive, )는 exclusive를 뜻한다. 따라서 [2025-01-01, 2025-07-01)는 1월 1일은 포함하지만 7월 1일은 포함하지 않는다. 마지막 Gadget 행은 열린 범위 [2026-01-01,)를 가지는데, 이는 현재 가격에 정의된 종료일이 없다는 뜻이다. 그리고 overlap 방지는 우리가 기대하는 그대로 작동한다:
-- 잘못된 범위를 추가해 보기
INSERT INTO products VALUES (1, 'Widget', 99.99, '[2025-03-01, 2025-01-01)');
ERROR: range lower bound must be less than or equal to range upper bound
LINE 1: INSERT INTO products VALUES (1, 'Widget', 99.99, '[2025-03-0...'
-- 겹치는 범위를 추가해 보기
INSERT INTO products VALUES (1, 'Widget', 99.99, '[2025-03-01, 2025-09-01)');
ERROR: conflicting key value violates exclusion constraint "products_pkey"
DETAIL: Key (product_id, valid_at)=(1, [2025-03-01,2025-09-01))
conflicts with existing key (product_id, valid_at)=(1, [2025-01-01,2025-07-01)).
가격 하나로 검증 두 개를 얻는 셈이다! 서로 분리된 두 컬럼 대신 range를 다루기 때문에 가능한 일이다.
이제 정말 재미있는 부분이다. Widget의 가격을 $10.99로 바꿔야 하는데, 2025년 3월부터 9월까지만 그렇다고 해보자. 예전 방식에서는 기존 행을 수동으로 조각내야 했다. 원래 행을 삭제하거나 업데이트하고, 새 가격 범위를 넣고, 변경하지 않은 부분에 대한 나머지 행도 넣어야 했다. 그중 하나라도 틀리면 타임라인에 빈틈이나 겹침이 생긴다.
Temporal table에서는 그냥 의도를 그대로 말하면 된다:
UPDATE products
FOR PORTION OF valid_at FROM '2025-03-01' TO '2025-09-01'
SET price = 10.99
WHERE product_id = 1;
이제 행들이 어떻게 보이는지 보자:
SELECT * FROM products WHERE product_id = 1 ORDER BY valid_at;
product_id | product_name | price | valid_at
------------+--------------+-------+-------------------------
1 | Widget | 9.99 | [2025-01-01,2025-03-01)
1 | Widget | 10.99 | [2025-03-01,2025-07-01)
1 | Widget | 10.99 | [2025-07-01,2025-09-01)
1 | Widget | 12.99 | [2025-09-01,2026-01-01)
1 | Widget | 11.99 | [2026-01-01,2026-07-01)
잠깐만... 여기서 무슨 일이 벌어진 걸까? 원래 Widget 행은 세 개였는데 이제는 다섯 개가 되었다!
음, Postgres는 $9.99에 대한 [2025-01-01, 2025-07-01) 범위의 튜플과 $12.99에 대한 [2024-07-01, 2025-01-01) 범위의 튜플을 보고, 새 행을 수용하기 위해 겹침을 조정해야 한다. 그 결과 여러 일이 일어난다:
Postgres는 기존 $9.99 행을 [2025-01-01, 2025-03-01) 범위만 덮도록 수정한다.
그다음 남은 [2025-03-01, 2024-07-01) 범위에 대해 $10.99 새 행을 추가한다.
이어서 기존 $12.99 행을 [2025-09-01, 2026-01-01) 범위만 덮도록 수정한다.
마지막으로 남은 [2025-07-01, 2025-09-01) 범위에 대해 또 다른 $10.99 새 행을 추가한다.
왜 합쳐진 [2024-03-01, 2024-09-01) 범위 하나가 아니라 $10.99 행이 두 개일까? FOR PORTION OF는 각 일치 행에 대해 독립적으로 작동하기 때문이다. 이후에 인접한 범위를 자동으로 합치지는 않는다. 최종 결과는 빈틈도 없고 겹침도 없으며, 순수한 exclusion logic만 사용했을 때는 얻지 못하던 것이다.
이 모든 힘이 단 하나의 UPDATE 문에 담겨 있다.
경계 사례는 어떨까? FOR PORTION OF 범위가 단일 기존 행 안에 완전히 들어가면, Postgres는 최대 두 개의 leftovers를 만든다. 하나는 앞부분, 하나는 뒷부분이다. 기존 경계와 정확히 맞아떨어지면 leftovers는 필요 없다. 그냥 알아서 동작한다.
흥미롭게도, 새로 도입된 temporal leftovers는 INSERT 권한을 요구하지 않는다. 새로운 정보를 추가하는 것이 아니라 기존 데이터를 보존하는 것이기 때문이다. 하지만 기존 INSERT trigger는 발생시킨다. 감사 로깅이나 SECURITY DEFINER trigger 함수에서는 이 점을 염두에 둘 필요가 있다.
FOR PORTION OF 절은 DELETE 문에서도 동작한다. 2025년 6월부터 10월 사이에 Gadget이 일시적으로 카탈로그에서 빠졌다고 해보자:
DELETE FROM products
FOR PORTION OF valid_at FROM '2025-06-01' TO '2025-10-01'
WHERE product_id = 2;
피해 상황을 확인해보자:
SELECT * FROM products WHERE product_id = 2 ORDER BY valid_at;
product_id | product_name | price | valid_at
------------+--------------+-------+-------------------------
2 | Gadget | 24.99 | [2025-01-01,2025-04-01)
2 | Gadget | 22.99 | [2025-04-01,2025-06-01)
2 | Gadget | 22.99 | [2025-10-01,2026-01-01)
2 | Gadget | 26.99 | [2026-01-01,)
이 삭제는 6월부터 10월까지의 구간을 도려냈다. 원래 [2025-04-01, 2026-01-01)를 덮고 있던 $22.99 행은 두 개의 leftovers로 쪼개졌다. 하나는 6월에 끝나고, 다른 하나는 10월에 시작한다. 빈틈 전후의 가격 데이터는 원래 값 그대로 보존된다. DELETE 때문에 행 수가 _증가_할 수 있다는 사실은 좀 직관에 안 맞지만, 실제로 그렇다.
어쨌든 temporal table 관리의 기반 메커니즘 덕분에 이 모든 것은 자동으로 처리된다. 이제 애플리케이션 계층에서 실수로 너무 많이 지우거나, 뒤에 고아 조각을 남길 위험이 없다.
Temporal table에 temporal foreign key가 없다면 완전하다고 할 수 없을 것이다. Postgres 19는 PERIOD 키워드를 사용해 이를 지원한다:
CREATE TABLE variants (
variant_id INT NOT NULL,
product_id INT NOT NULL,
variant_name TEXT NOT NULL,
valid_at DATERANGE NOT NULL,
PRIMARY KEY (variant_id, valid_at WITHOUT OVERLAPS),
FOREIGN KEY (product_id, PERIOD valid_at)
REFERENCES products (product_id, PERIOD valid_at)
);
PERIOD 키워드는 foreign key 자체가 temporal하다는 사실을 Postgres에 알려준다. 그 결과, 참조되는 상품은 variant의 valid_at 범위 전체 기간 동안 존재해야 한다. 시간 어딘가에 일치하는 상품 행이 하나 존재하는 것만으로는 충분하지 않다. 참조된 테이블에서 일치하는 모든 행의 조합이 참조하는 행의 period를 완전히 덮어야 한다.
상품의 알려진 타임라인을 넘어서는 variant를 만들려고 하면:
INSERT INTO variants VALUES
(100, 1, 'Widget Deluxe', '[2025-01-01, 2027-01-01)');
ERROR: insert or update on table "variants" violates foreign key constraint
"variants_product_id_valid_at_fkey"
DETAIL: Key (product_id, valid_at)=(1, [2025-01-01,2027-01-01))
is not present in table "products".
Widget은 가격이 2026년 중반까지만 정의되어 있으므로, 2027년까지 유효하다고 주장하는 variant는 거부된다. Postgres는 전체 temporal coverage를 검사하여, 부모 테이블의 일치 행들이 variant의 전체 유효 기간을 가로지르는지 확인했다.
여기에는 한 가지 중요한 제한이 있다. temporal foreign key는 referential action으로 NO ACTION만 지원한다. 즉 CASCADE, SET NULL, SET DEFAULT는 제외된다. 따라서 variant가 의존하는 product 행을 삭제하면 언제나 오류가 발생한다. temporal operation의 cascading 복잡성을 생각하면 이해할 만하지만, 현재로서는 애플리케이션이 이런 경우를 명시적으로 처리해야 한다는 뜻이기도 하다.
이제 우리는 overlap 방지, temporal DML, temporal foreign key를 갖춘 application-time temporal table을 얻었다. 그 밖에 무엇이 남았을까?
가장 큰 누락은 system time, 때로는 transaction time이라고도 불리는 것이다. 앞서 언급했듯 application time은 현실 세계에서 사실이 언제 참인지 추적하고, system time은 데이터베이스가 그 사실을 언제 알게 되었는지를 추적한다. Temporal table에 의존하는 시스템은 종종 둘 다 활용한다. 바로 이 영역을 pg_bitemporal 확장이 2015년부터 메워 왔다. trigger를 사용해 system time을 흉내 내는 것은 가능하지만, 다른 새 temporal 기능들처럼 엔진이 이를 투명하게 관리해주는 것과는 다르다.
temporal table documentation도 이를 직접 인정하며, system time은 네이티브하게 지원되지 않지만 에뮬레이션할 수 있다고 적고 있다. 이것이 Postgres 20이나 그 이후에 도착할지는 누구도 장담할 수 없지만, 기반 작업은 이미 마련되어 있다.
Temporal table은 꽤 오랫동안 부재 상태였다. EXCLUDE USING gist 접근 방식도 동작은 하지만, 비교해보면 거친 우회책이다. pg_bitemporal 같은 확장은 개념을 입증했고, 이 대화를 계속 이어가게 했다. 이제 우리는 그것이 필요 없게 되는 꿈에 한 걸음 더 가까워졌다.
GiST exclusion constraint를 사용하는 것보다도 훨씬 직관적인 접근 방식이다. 기본 키 안의 WITHOUT OVERLAPS는 올바른 SQL이 그래야 하듯 평이한 영어처럼 읽힌다. FOR PORTION OF는 하는 일을 정확히 말해준다. Temporal update와 delete 동안의 자동 행 분할은 잠재적 버그의 한 범주를 통째로 없애준다. Temporal timeline의 빈틈을 디버깅해본 적 있는 사람이라면 그것이 얼마나 가치 있는지 잘 알 것이다.
SQL:2011에서 Postgres 19까지 오는 길은 길었다. Hetti와 커뮤니티의 다른 이들은 이런 패턴이 필요하고 실용적이라는 점을 수년간 입증해왔다. 이제 그것들이 코어에 들어왔고, 앞으로는 더 좋아질 일만 남았다. 미래 릴리스에서 system-time 지원을 주시하라. Postgres가 bitemporal equation의 두 절반을 모두 갖추는 순간, 정말 무슨 일이든 가능해질 테니까.
지금으로서는 테스트 인스턴스에서 WITHOUT OVERLAPS와 FOR PORTION OF를 직접 실험해보자. 현재만큼 좋은 (temporal) 시간은 없으니까!