PostgreSQL 외래키와 트랜잭션 성능 최적화

서론: 왜 외래키와 트랜잭션을 함께 봐야 하는가

외래키는 데이터 무결성을 보장한다. 그런데 무결성 검사 때문에 쓰기 작업(INSERT, UPDATE, DELETE)에서 추가 비용이 발생한다. 특히 트랜잭션이 많고 대량 데이터가 오갈 때 외래키 관련 잠금(lock)과 인덱스 스캔이 병목으로 작동할 수 있다. 따라서 외래키 설계와 트랜잭션 전략을 함께 최적화하는 것이 중요하다.

기본 원리: 외래키 검사와 인덱스, 잠금

외래키 검사 과정

INSERT/UPDATE 시 자식 테이블의 값이 부모 테이블에 존재하는지 확인한다. DELETE 시 부모키가 참조되는 경우 제약조건에 따라 차단 또는 CASCADE 동작이 발생한다. 이 확인은 해당 열에 적절한 인덱스가 있어야 효율적이다.

인덱스의 역할

외래키 열에 인덱스가 없으면 참조 체크가 전체 테이블 스캔으로 이어질 수 있다. 따라서 외래키 컬럼에는 적절한 인덱스를 생성해 조회 비용을 낮춰야 한다. 외래키 인덱스 postgres 환경에서는 특히 조인과 삭제 시 큰 효과를 본다.

잠금과 동시성

참조 무결성 검사는 부모·자식 테이블에 잠금을 유발한다. 트랜잭션이 길어지면 잠금 경쟁이 심화되어 대기 시간이 늘어난다. 이 때문에 트랜잭션 경계 관리와 배치 전략이 중요하다.

외래키 인덱스 postgres: 권장 패턴

외래키 컬럼에 단일 칼럼 인덱스를 만드는 것이 기본이다. 복합 외래키의 경우 각 조인 조건과 쿼리 패턴을 고려해 복합 인덱스 또는 별도의 단일 인덱스를 생성한다.

예제: 외래키와 인덱스 생성

-- 부모 테이블
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name TEXT
);

-- 자식 테이블
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES users(id),
  created_at TIMESTAMP
);

-- 외래키 컬럼에 인덱스가 자동으로 생성되지 않으므로 명시적으로 생성
CREATE INDEX idx_orders_user_id ON orders(user_id);

트랜잭션 외래키 최적화 전략

1) 단축 트랜잭션 경계

트랜잭션을 가능한 짧게 유지하면 잠금 유지 시간이 줄어든다. 읽기 전용 작업과 쓰기 작업을 분리하고, 필요한 최소한의 로직만 트랜잭션 안에 넣는다.

2) 배치 처리와 대량 삭제

대량 삭제나 업데이트는 한 번에 처리하지 말고 배치로 나누어 실행한다. 작은 단위로 커밋하면 잠금 지속 시간을 줄이고 WAL 및 리소스 소비를 분산시킬 수 있다.

예제: 배치 삭제

-- 한 번에 많은 행 삭제 대신 배치 반복
DO $$
DECLARE
  batch_size INT := 1000;
BEGIN
  LOOP
    WITH del AS (
      DELETE FROM orders
      WHERE created_at < now() - interval '90 days'
      RETURNING 1
    )
    SELECT count(*) INTO STRICT batch_size FROM del;
    EXIT WHEN batch_size = 0;
    PERFORM pg_sleep(0.1); -- 잠깐 쉬어주기
  END LOOP;
END$$;

3) 제약조건 지연(DEFERRABLE)

복잡한 트랜잭션에서 부모·자식 간 일시적 위반이 발생할 수 있다. DEFERRABLE INITIALLY DEFERRED 옵션을 사용하면 트랜잭션 종료 시점에 제약을 검사해 불필요한 중간 체크를 줄인다. 다만 전체 검사 시 비용은 유사하거나 더 클 수 있으므로 사용 시 검토가 필요하다.

4) 외래키 제거 고려

어떤 경우에는 애플리케이션 레벨에서 무결성을 관리하는 편이 성능상 유리할 수 있다. 다만 이 접근은 데이터 일관성 리스크를 수반하므로 신중한 설계와 모니터링이 필요하다.

5) 파티셔닝과 참조

대규모 테이블은 파티셔닝으로 관리하면 인덱스 및 IO 부담을 분산할 수 있다. 파티셔닝을 적용할 때 외래키가 파티션 경계에 미치는 영향을 고려해야 하며, 경우에 따라 부모-자식 모두 파티셔닝을 맞추는 것이 유리하다.

실전 체크리스트

• 외래키 컬럼에 적절한 인덱스가 있는지 확인
• 긴 트랜잭션을 줄이고 트랜잭션 경계를 명확히 함
• 대량 작업은 배치로 처리해 잠금 시간 분산
• 복잡한 트랜잭션은 DEFERRABLE 옵션 검토
• 모니터링으로 lock, wait, deadlock 지표 관찰
• 필요 시 파티셔닝이나 애플리케이션 레벨 무결성 검토

모니터링과 진단 도구

PostgreSQL의 pg_stat_activity, pg_locks, EXPLAIN(ANALYZE)로 쿼리 비용과 잠금 상황을 확인한다. slow query 로그와 autovacuum 상태도 병목 원인 진단에 유용하다. 주기적으로 인덱스 통계를 수집하고 필요하면 REINDEX나 VACUUM을 수행한다.

결론

foreign key 성능 postgres 문제는 단일 설정으로 해결되지 않는다. 외래키 인덱스 postgres를 포함한 인덱스 설계, 트랜잭션 경계 단축, 배치 처리, DEFERRABLE 옵션 등 여러 기법을 조합해야 한다. 우선 인덱스와 트랜잭션 길이를 점검하고, 모니터링 데이터를 바탕으로 우선순위를 두고 개선을 진행하면 실무에서 안정적인 성능을 얻을 수 있다.