[postgresql + prisma] nullable 값을 포함한 unique index는 partial index를 통해 구현해보자!

postgresql과 Prisma의 조합으로 compound key unique index 사용을 고민할 때 마주할 수 있는 문제와 이를 partial index를 통해 해결하는 방법을 공유하고자 합니다.

배경

특정 도메인에 대한 ERD를 구현하는 과정에서 특정 테이블에 대해 2개의 컬럼과 한 개의 nullable 컬럼의 조합으로 unique index를 만들기로 했습니다. 이 과정에서 deletedAt 컬럼(삭제된 Datetime을 기록하는 컬럼)을 nullable로 갖는 soft-delete 방법 역시 사용하기로 했습니다. 2개의 컬럼값과 deletedAt 컬럼이 null값을 갖는 행을 유효한 행으로 보고, deletedAt이 null값이 아닌 행들은 삭제된 데이터로 취급합니다.

왜 soft-delete를 사용했나?

위 index 적용의 대상이 되는 테이블은 ManyToMany 관계테이블로써 관계된 테이블들을 M:N으로 연결해줍니다. 해당 테이블은 데이터의 생성과 삭제가 잦을 것 같습니다. 이런 상황에서 데이터가 물리적으로 삭제되는 것보다 물리적인 데이터는 유지한 채 필드를 수정하는 식이 성능적으로 좋을 것이라고 판단했습니다. 또한 히스토리가 된 soft-delete 데이터들을 다양한 데이터 통계에도 사용하고자 했습니다.

unique index 생성

배경에서 언급했던 unique index를 아래와 같이 schema.prisma에 작성했습니다. 해당 코드를 기반으로 prisma 명령어를 통해 migration 파일 역시 생성할 수 있었습니다. migration 파일을 기반으로 postgresql db에 index를 생성했습니다.

// schema.prisma

model DiscountAndMember {
	/* (생략) */
    discountId Int
    memberId Int
    deletedAt Datetime?
    
    // prisma에서는 아래와 같은 방법으로 unique index를 생성할 수 있습니다.
    @@unique([discountId, memberId, deletedAt])
}

...

// migrations.sql

CREATE UNIQUE INDEX "DiscountAndMember_discountId_memberId_deletedAt_key" 
ON "DiscountAndMember"("discountId", "memberId", "deletedAt");

unique key를 사용하여 update 구문 수행하기

위 과정을 통해 생성한 unique index를 사용하여 update 구문을 수행하고자 했습니다. prisma는 update와 updateMany의 where input option이 다릅니다. 이전에 이와 관련하여 작성한 게시물이 있어 공유합니다. 요점은 update의 where 조건에 사용할 수 있는 키들은 반드시 unique 해야합니다. prisma는 unique index를 생성하게 되면 그에 맞게 unique input type을 node_modules > .prisma > index.d.ts 에 만들어 둡니다. 고로 앞서 만들어둔 unique input을 사용하여 다음과 같이 사용할 수 있기를 원했습니다. 그러나 아래와 같이 deletedAt 부분에서 컴파일 에러가 발생했습니다. 이 부분을 확인하기 위해 index.d.ts 에서 'discount_memberId_deletedAt'의 타입 정의를 확인해봤습니다.

 

 

...

async updateDiscountAndMember(member: Member, discount: Discount) {
    await this.prismaClient.discountAndMember.update({
        where: {
            discountId_memberId_deletedAt: {
                discountId: discount.id,
                memberId: member.id,
                deletedAt: null // error why??
            }
        },
        ...
    }
}

 

앞서 얘기한대로 node_moduls > .prisma > client > index.d.ts 에 보면 아래와 같이 unique input에 대한 타입이 정해집니다.

// index.d.ts
...

export type DiscountAndMemberWhereUniqueInput = Prisma.AtLeast<{
    ...
    discountId_memberId_deletedAt?: DiscountAndMemberDiscountIdMemberIdCDeletedAtompoundUniqueInput
    ...
}, "id" | "discountId_memberId_deletedAt">

...

export type DiscountAndMemberDiscountIdMemberIdCDeletedAtompoundUniqueInput = {
    discountId: number
    memberId: number
    deletedAt: Date | string // null을 허용하지 않고 string을 허용하도록 타입이 잡혀있음을 볼 수 있다.
}

prisma에서 nullable한 필드를 unique index로 설정하는 것은 옳은 방법인가?

위 코드 예시와 같이 deletedAt은 Date | null 이 아닌 Date | string이었고 이에 따라 null value에 대해 컴파일 에러가 발생하게 되었습니다. 이 상황에 대해 2가지가 궁금했습니다.

1. postgresql에서 null을 포함한 unique index를 지원해주지 않기 때문에 이런 방법을 선택한 것인가?
2. 기본적으로 Date와 string은 결이 다른데 어떤 식의 사용을 염두에 두고 이런 타이핑을 대체로 지원해주는 것인지?

prisma 공식 repository에 다음과 같이 Q&A를 남겼습니다.

 

속 시원한 답변을 듣고 싶지만 이를 마냥 기다릴 수 없는 상황이었습니다. 앞서 얘기했던대로 우리 팀은 아직 2개의 컬럼과 deletedAt 컬럼조합의 unique index 전략을 사용하고 싶었습니다.

Prisma에서의 타입문제뿐만 아니라 실제 postgresql db에도 제대로 index가 동작하지 않는다?

prisma에 타입이 제대로 안 잡힌 건 prisma라는 라이브러리의 사정이라고 생각했습니다. 실제 postgresql db에는 unique index가 제대로 적용되어 2개의 컬럼과 deletedAt 값이 null인 행의 중복 생성을 시도할 경우 에러가 발생하길 바랬습니다. 하지만 보란듯이 중복된 행들은 생성되었습니다. prisma로 인한 혼란부터 unique index의 기대와 다른 동작까지.. 현 문제에 대한 탐색이 좀 더 필요했습니다. 

nullable 값을 unique index로 사용하기 위한 최적의 방법은 partial index!

이에 대한 해결책을 찾다가 postgresql 맞춤전략인 partial index를 동료가 제안해주었습니다.

partial index란?

공식사이트에서는 다음과 같이 설명하고 있습니다.

partial index는 테이블의 하위 집합에 대해 구축된 인덱스입니다. 하위 집합은 조건식으로 정의됩니다. 인덱스에는 조건식을 만족하는 테이블 행에 대한 항목만 포함됩니다. partial index는 특수 기능이지만 유용한 상황이 여러 가지 있습니다.

• 공통 값을 제외하기 위한 부분 인덱스 설정
• 관심 없는 값을 제외하기 위한 부분 인덱스 설정
• 부분 고유 인덱스 설정
• 분할을 대체하기 위해 부분 인덱스를 사용하지 마십시오

위 예시들 중 '부분 고유 인덱스 설정'은 nullable 필드를 unique index에 녹여 사용하기에 최적의 방법이었습니다.

적용방법

2개의 필드와 한 개의 구분값으로 partial index를 구성한 예시

위 공식사이트의 예시를 참고하여 우리 상황에 적용해보면 2개의 컬럼을 unique index로 잡으면서 where 조건에 deletedAt is null을 추가하면 조건이 완성되었습니다. 이렇게 수정해 본 schema와 migration파일 그리고 이를 기반으로 생성된 index.d.ts 내 타입들까지 아래와 같이 변경되었습니다.

// schema.prisma

model DiscountAndMember {
	/* (생략) */
    discountId Int
    memberId Int
    deletedAt Datetime?
    
    // partial index를 위해 deletedAt을 아래 unique index에서 제거
    @@unique([discountId, memberId])
}

...

// 위 prisma파일을 기반으로 생성된 migrations.sql

CREATE UNIQUE INDEX "DiscountAndMember_discountId_memberId_key" 
ON "DiscountAndMember"("discountId", "memberId") // deletedAt이 제거됨

// 이를 partial index로 각색한 migrations.sql

CREATE UNIQUE INDEX "DiscountAndMember_discountId_memberId_key" 
ON "DiscountAndMember"("discountId", "memberId") // deletedAt이 제거됨
WHERE "deletedAt" IS NULL; // 직접 추가

// index.d.ts
...

export type DiscountAndMemberWhereUniqueInput = Prisma.AtLeast<{
    ...
    // deletedAt이 제거 되었음
    discountId_memberId?: DiscountAndMemberDiscountIdMemberIdCompoundUniqueInput
    ...
}, "id" | "discountId_memberId">

...

export type DiscountAndMemberDiscountIdMemberIdCompoundUniqueInput = {
    discountId: number
    memberId: number
    // deletedAt 이 제거되었음
}

 

위 스크립트에 주석으로 언급한대로 prisma에서는 partial index에 대한 문법을 따로 제공해주고 있지 않기 때문에 직접 migration 파일을 수정해야한다는 번거로움이 있습니다. 

그래도 결국 위 노력을 거친 결과 postgresql db에 partial index가 성공적으로 생성됨을 확인했고, 이에 따라 2개의 컬럼과 deletedAt이 null 값인 행의 중복 생성을 시도했을 때 기대한대로 에러도 발생함을 확인했습니다.

정리

postgresql + prisma의 조합으로 DB를 다루시는 분들 중 nullable 한 필드를 포함한 unique index를 구성하려는 분들이라면 부족하지만 공유드린 시행착오를 통해 도움을 받으실 수 있을거라 생각합니다. postgresql db의 특수한 기능인 partial index는 다양한 쓰임새가 있기 때문에 위 방법말고도 다양하게 사용해봐야겠습니다.