PostgreSQL 16~18 NOT NULL 추가: 긴 검증과 잠금을 나누는 순서

PostgreSQL 16~18에서 기존 컬럼을 NOT NULL로 바꿀 때는 CHECK (컬럼 IS NOT NULL) NOT VALID를 먼저 추가하고, 별도로 검증한 뒤 SET NOT NULL로 전환하면 마지막 단계의 전체 테이블 검사를 생략할 수 있어요. 기존 데이터는 VALIDATE CONSTRAINT 단계에서 검사해요. 제약 추가와 마지막 전환에는 여전히 강한 잠금이 필요합니다.

이미 쓰고 있는 일반 테이블에서 NULL을 허용하던 컬럼을 필수값으로 바꿀 때 적용하는 절차예요. NULL을 보내는 애플리케이션을 먼저 고치고, 기존 NULL을 어떤 값으로 보정할지 정해야 합니다. 파티션·상속 테이블이나 도메인 제약이 얽힌 컬럼은 아래 예제를 그대로 적용하지 마세요.

어떤 조건에서 긴 잠금을 줄일 수 있나요?

공식 16 문서와 17 문서는 검증된 CHECK가 NULL 부재를 증명하면 SET NOT NULL의 스캔을 건너뛴다고 설명해요. 이때 같은 ALTER TABLE 명령에서 그 CHECK를 삭제하면 안 된다는 조건도 명시되어 있습니다.

PostgreSQL 18은 NOT NULL 제약 자체를 NOT VALID로 추가하고 나중에 검증하는 경로도 지원해요. 다만 여기서는 16~18에 공통으로 적용할 수 있는 CHECK 경로 한 가지를 끝까지 사용합니다. 18 전용 경로를 선택한다면 18의 ALTER TABLE 문법을 따르고 두 절차를 섞지 마세요.

검증을 나누면 무엇이 달라지나요?

컬럼 속성을 바꾸기 전에 PostgreSQL은 기존 행이 새 규칙을 만족하는지 알아야 해요. 이미 NULL이 없다는 사전 조회 결과는 사람이 본 한 시점의 결과일 뿐입니다. 그 뒤 다른 연결이 NULL을 넣을 수 있으므로 조회만 했다고 데이터베이스가 제약 검사를 생략하지는 않아요.

CHECK를 NOT VALID로 추가하면 기존 데이터에 대한 검사는 미루되 이후 INSERT·UPDATE에는 규칙을 적용합니다. 기존 위반 행을 보정한 다음 VALIDATE CONSTRAINT로 과거 데이터까지 확인하면, 마지막 속성 변경은 앞서 끝낸 검증 결과를 이용해요. NOT VALID와 검증 단계의 동작에 따른 흐름입니다.

“NOT VALID면 기존 NULL 행은 어떤 UPDATE든 허용된다”는 해석은 위험해요. 예를 들어 연락처가 NULL인 행의 다른 컬럼만 수정해도, 수정 후 행이 CHECK를 위반하면 실패합니다. 새 가입 화면뿐 아니라 오래된 행을 수정하는 배치와 관리 화면까지 먼저 확인해야 하는 이유예요.

단계별 잠금과 다음 진행 조건

검증에 쓰는 SHARE UPDATE EXCLUSIVE는 일반 SELECT와 INSERT·UPDATE·DELETE의 테이블 잠금과 충돌하지 않아요. 하지만 VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY 등과는 충돌할 수 있습니다. 공식 잠금 충돌표를 기준으로 같은 테이블의 유지보수 일정을 겹치지 않게 잡으세요. 데이터 검사의 I/O 부하도 남습니다.

작은 테스트 테이블에서 상태 변화를 먼저 확인해요

아래 SQL은 테스트용 스키마를 만들 수 있는 계정으로 실행하는 재현 예제예요. 이 글을 작성하며 실제 PostgreSQL 서버에서 실행한 결과는 아니며, 출력과 실패는 공식 동작에 따른 예상입니다. 운영 데이터베이스에 접속해 예제를 실행하지 마세요. 각 코드 묶음은 앞 단계가 성공한 것을 확인한 뒤 순서대로 실행합니다.

BEGIN;
CREATE SCHEMA nn_lab;
CREATE TABLE nn_lab.customer_contact (
    customer_id bigint PRIMARY KEY,
    contact_code text,
    note text
);
INSERT INTO nn_lab.customer_contact
    (customer_id, contact_code, note)
VALUES
    (1, 'C001', 'existing'),
    (2, NULL, 'needs correction'),
    (3, 'C003', 'existing');
COMMIT;

SELECT customer_id, contact_code, note
FROM nn_lab.customer_contact
ORDER BY customer_id;

실행 전: NULL 행과 컬럼 상태를 따로 읽어요

SELECT current_setting('server_version') AS server_version,
       current_database() AS database_name,
       current_user AS execution_role;

SELECT customer_id
FROM nn_lab.customer_contact
WHERE contact_code IS NULL
ORDER BY customer_id
LIMIT 20;

SELECT a.attname, a.attnotnull
FROM pg_catalog.pg_attribute AS a
WHERE a.attrelid = 'nn_lab.customer_contact'::regclass
  AND a.attname = 'contact_code'
  AND a.attnum > 0
  AND NOT a.attisdropped;

운영에서는 처음부터 정확한 전체 NULL 개수를 반복 집계하기보다 위반 예시와 보정 근거를 먼저 찾는 편이 좋아요. 행 수준 보안으로 일부 데이터만 보이는 계정이라면 NULL 조회 결과를 전체 데이터의 증거로 삼지 마세요. 전체 정합성의 최종 판정은 제약 검증의 성공 여부로 확인합니다.

잠금이 걱정되는 테이블이라면 먼저 pg_blocking_pids로 실제 차단 세션을 찾는 순서로 긴 트랜잭션과 대기 관계를 확인하세요. 소유자를 확인하지 않은 세션 종료나 공유 서버의 전역 설정 변경은 이 절차에 포함하지 않습니다.

새 NULL 유입을 막고 기존 행을 보정해요

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '10s';
ALTER TABLE nn_lab.customer_contact
    ADD CONSTRAINT customer_contact_code_nn_check
    CHECK (contact_code IS NOT NULL) NOT VALID;
COMMIT;

SELECT c.conname, c.contype, c.convalidated,
       pg_get_constraintdef(c.oid) AS definition
FROM pg_catalog.pg_constraint AS c
WHERE c.conrelid = 'nn_lab.customer_contact'::regclass
  AND c.conname = 'customer_contact_code_nn_check';

이 시점에는 고객 2의 기존 NULL이 남아 있어도 제약 추가가 성공할 것으로 예상돼요. 그러나 그 행을 수정하면서 NULL을 그대로 두거나 새 NULL 행을 넣으면 CHECK 위반이 됩니다. 보정값은 단순히 오류를 없애기 위한 임의 문자열이 아니라 업무적으로 확인한 값이어야 해요.

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '10s';
UPDATE nn_lab.customer_contact
SET contact_code = 'C002'
WHERE customer_id = 2
  AND contact_code IS NULL
RETURNING customer_id, contact_code;
COMMIT;

대량 보정은 모든 NULL을 한 번에 바꾸는 UPDATE로 확대하지 마세요. 기본키 구간과 승인된 값 목록을 정하고, 처리한 범위·건수·변경 전 값을 남기면서 작은 트랜잭션으로 진행합니다. 재시도 때 IS NULL 조건은 이미 고친 값을 덮어쓰지 않도록 도와주지만, 다른 업무 변경과의 충돌까지 자동으로 해결해 주지는 않아요.

기존 데이터를 검증한 다음 속성을 바꿔요

검증은 전체 데이터를 읽는 단계이므로 테이블 크기와 저장장치의 읽기 여유를 살펴 실행 시점을 정하세요. 앞의 ADD 트랜잭션이 끝났는지도 확인합니다. 배포 도구가 파일 전체를 자동으로 하나의 트랜잭션에 넣는다면 작업을 나눈 취지가 사라질 수 있어요.

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '30s';
ALTER TABLE nn_lab.customer_contact
    VALIDATE CONSTRAINT customer_contact_code_nn_check;
COMMIT;

SELECT c.conname, c.convalidated
FROM pg_catalog.pg_constraint AS c
WHERE c.conrelid = 'nn_lab.customer_contact'::regclass
  AND c.conname = 'customer_contact_code_nn_check';

검증에 실패했다고 CHECK가 자동으로 없어지는 것은 아니에요. 앞 단계에서 커밋한 제약은 계속 새 쓰기에 적용됩니다. pg_constraint.convalidated로 검증 완료 여부를 확인하고, true를 확인하기 전에는 마지막 전환으로 넘어가지 마세요.

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '10s';
ALTER TABLE nn_lab.customer_contact
    ALTER COLUMN contact_code SET NOT NULL;
COMMIT;

SELECT a.attname, a.attnotnull
FROM pg_catalog.pg_attribute AS a
WHERE a.attrelid = 'nn_lab.customer_contact'::regclass
  AND a.attname = 'contact_code'
  AND a.attnum > 0
  AND NOT a.attisdropped;

PostgreSQL 18의 attnotnull은 미검증 NOT NULL 제약이 있어도 true일 수 있어요. 따라서 일반적인 점검에서는 이 값 하나만 보고 기존 행까지 검증됐다고 결론 내리지 않습니다. 이 예제는 CHECK 검증 성공과 SET NOT NULL의 성공·커밋을 함께 확인하는 경로예요.

마지막 확인과 보조 제약 정리

애플리케이션의 정상 입력·수정이 계속 성공하는지 확인한 뒤 보조 CHECK를 제거할 수 있어요. 제거에도 잠금이 필요하므로 급하게 이어서 실행할 이유는 없습니다. 아래는 이미 NOT NULL이 설정된 테스트 테이블에서만 진행합니다.

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '10s';
ALTER TABLE nn_lab.customer_contact
    DROP CONSTRAINT customer_contact_code_nn_check;
COMMIT;

SELECT customer_id, contact_code
FROM nn_lab.customer_contact
ORDER BY customer_id;

SELECT c.conname
FROM pg_catalog.pg_constraint AS c
WHERE c.conrelid = 'nn_lab.customer_contact'::regclass
  AND c.conname = 'customer_contact_code_nn_check';

시간 초과와 롤백은 어느 단계까지 되나요?

lock_timeout은 잠금을 얻으려고 기다리는 시간을 제한하고, statement_timeout은 문장 실행 시간을 제한해요. 잠금을 얻은 뒤 오래 스캔하는 문제를 lock_timeout만으로 막을 수는 없습니다. 공식 제한 시간 설명처럼 두 값을 구분하고, SET LOCAL로 현재 트랜잭션 안에서만 적용하세요.

ADD 뒤에 VALIDATE를 같은 BEGIN/COMMIT으로 묶으면 ADD가 얻은 ACCESS EXCLUSIVE 잠금이 검증 동안에도 유지될 수 있습니다. “검증은 약한 잠금”이라는 설명만 보고 안전하다고 판단하면 안 되는 지점이에요. 코드의 문장 경계와 실제 배포 도구의 트랜잭션 경계를 함께 확인해야 합니다.

검증과 동시에 같은 테이블에 인덱스를 만들 필요가 있다면 작업 순서를 먼저 분리하세요. 이미 동시 인덱스 생성이 진행되다 실패한 상황은 동시 인덱스 생성의 진행 상태와 실패 잔재를 구분하는 방법으로 확인할 수 있어요. NOT NULL 작업이 늦어진다는 이유로 인덱스를 삭제하거나 다른 작업을 강제 종료하지 않습니다.

보조 CHECK가 남아 있는 동안에는 DROP NOT NULL만 해도 NULL 입력이 계속 차단돼요. 반대로 NOT NULL만 남았다면 CHECK를 지우는 명령은 의미가 없겠죠. 변경 취소는 “어떤 규칙이 현재 남아 있나”를 조회한 뒤 결정합니다. 아래는 이 글의 전환이 완료된 일반 테스트 테이블을 다시 nullable로 돌리는 예시입니다.

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '10s';
ALTER TABLE nn_lab.customer_contact
    ALTER COLUMN contact_code DROP NOT NULL;
ALTER TABLE nn_lab.customer_contact
    DROP CONSTRAINT IF EXISTS customer_contact_code_nn_check;
COMMIT;

여기서 ROLLBACK은 이미 커밋된 예전 트랜잭션을 거슬러 취소하는 명령이 아니에요. PostgreSQL 트랜잭션 설명을 기준으로 현재 열린 변경과 완료된 변경을 분리해서 기록하세요. 접속이 끊겨 커밋 여부가 불명확하면 다시 실행하기 전에 카탈로그와 대상 행을 조회합니다.

배포 스크립트에서 놓치기 쉬운 세 가지

• CHECK (contact_code <> ”)로 대체하기: 빈 문자열과 NULL은 달라요. 일반 CHECK 식이 NULL로 평가되면 통과하므로 NULL 부재 증명에는 IS NOT NULL을 명확히 사용합니다.
• DEFAULT로 기존 NULL까지 고쳐졌다고 생각하기: 기본값 설정은 기존 행 보정 작업을 대신하지 않아요. 입력에서 컬럼을 생략하는 경우와 명시적으로 NULL을 보내는 경우도 구분하세요.
• 짧은 DDL을 무한 재시도하기: 데이터 검사가 생략돼도 잠금 대기는 남습니다. 실패한 트랜잭션을 정리하고 차단자·작업창·실제 제약 상태를 확인한 뒤 다시 시도하세요.

CHECK와 NULL의 관계는 공식 제약조건 설명에서 확인할 수 있어요. ALTER TABLE이 성공했다면 이제 실제 쓰기 동작을 확인할 차례예요. 기존 데이터 검증, NOT NULL 속성, 정상 쓰기 동작을 모두 확인하고 나서 보조 제약 정리 시점을 정하세요.

공식 출처

• PostgreSQL 16 ALTER TABLE
• PostgreSQL 17 ALTER TABLE
• PostgreSQL 18 ALTER TABLE
• PostgreSQL 17 잠금 충돌과 유지 기간
• PostgreSQL 17 CHECK와 NULL
• PostgreSQL 잠금·문장 제한 시간
• SET LOCAL의 적용 범위
• 트랜잭션과 ROLLBACK
• pg_constraint 검증 상태
• pg_attribute NOT NULL 표시

문서 확인일: 2026년 9월 12일. 예제는 일반 테스트 테이블을 전제로 하며 실제 서비스의 잠금 시간이나 실행 성능을 측정한 결과가 아닙니다.