[PostgreSQL] Partial Index

Partial 인덱스는 PostgreSQL에서 제공하는 강력한 인덱스의 확장기능 중 하나다.

인덱스에 대해서 특정 조건을 부여해서, 조건에 부합하는 데이터만 인덱스에 포함되도록 하는 것이다.
그래서 부분(Partial) 인덱스다.

Create Index 인덱스명 on 테이블명(컬럼...) where 조건식

저 조건식에 포함되는 Query는 인덱스에 걸려서 잘 인덱싱되고, 포함되지 않는 데이터는 인덱싱이 되지 않는다.

false인 데이터는 애초부터 인덱스 트리에 저장을 하지도 않고, 그렇기 때문에 SELECT 쿼리 플랜 도중에 조건에 걸려있지 않다면 인덱스 스캔을 시도하지도 않는 것이다.

이 기능의 장점은, 조정하기에 따라서 인덱스 저장공간을 더 아낄 수 있고, Update 시의 인덱스 수정 비용도 줄일 수 있다는 것이다.
근데 아마 이 이유만으로 쓰는 사람은 몇 없을 것이다.

응용: Partial Unique Index

사실 이 기능의 가장 유용한 응용 방법은, 조건에 따른 선택적 Unique를 설정하는 것이다.

예를 들어, 이메일을 유일키로 잡는 계정 정보 테이블이 있다고 가정해보겠다.

Create Table "user" (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(200) NOT NULL,
    name VARCHAR(100) NOT NULL,
    leftAt TIMESTAMPTZ -- 탈퇴 시점
);

INSERT INTO "user" (email, password, name, leftAt) VALUES
('asdf@example.com', 'q1w2e3r4', 'John', NULL),
('qert@example.com', 'q1w2e3r4', 'Tom', NULL),
('foo.bar@example.com', 'q1w2e3r4', 'Anna', NULL),
('buldak@example.com', 'q1w2e3r4', 'Alice Four', NULL),
('gang@example.com', 'q1w2e3r4', 'jonathan', NULL),
('kimchi@example.com', 'q1w2e3r4', 'kim', NULL),
('do@example.com', 'q1w2e3r4', 'doe', NULL);

뻔한 구성이다.
이메일이 있고, 탈퇴 시점을 기록하는 leftat 필드도 있다.
저게 null이 아니면 탈퇴한 회원인 것이다.

여기에 대해서 이메일 제약을 건다면, 가장 단순하면서도 강력한 방법은 Unique 인덱스를 꽂아버리는 것이다.

Create Unique Index email_unique on "user"(email);

INSERT INTO "user" (email, password, name, leftAt) VALUES
('asdf@example.com', 'q1w2e3r4', 'Other People', NULL);

서버에다가 직접 구현하면 동시성 문제로 유일성이 깨질 확률도 높고, 강력한 일관성을 보장하려면 결국에는 단일 락이 필요하다.
근데 RDB 자체가 강력한 원자성을 보장하는 단일 스토리지이니, 유일성 보장에 이만큼 효율적이고 완벽한 솔루션은 거의 없다고 봐도 될 것이다.

하지만 좀 응용을 하다보면 문제가 생길 수 있다.

update "user"
set leftAt = NOW()
where email = 'asdf@example.com';

예를 들면, 탈퇴한 회원이 있을때, 동일한 이메일로 재가입을 하고 싶을 수도 있는 것이다.
그럼 어떻게 해야할까?
email+leftat을 묶어서 Unique를 만든다고 해도, null이 포함되면 Unique 제약에서 벗어나기 때문에 요구사항을 만족할 수 없다.

여기에 대해서 가장 명쾌한 구현 방법은 Partial Unique 인덱스로 부분적인 제약을 가하는 것이다.
예를 들면, 이런 식이다.

Drop Index email_unique;
Create Unique Index email_unique on "user"(email) where leftAt IS NULL;

이러면 leftAt IS NULL을 만족하는 데이터에 대해서만 email 유일성을 보장하도록 인덱스가 구성된다.

INSERT INTO "user" (email, password, name, leftAt) VALUES
('asdf@example.com', 'q1w2e3r4', 'Other People', NULL);

그러면 leftat=null이 아닌 데이터는 유일성 제약에서 벗어나고

또 넣으려고 하면 의도대로 잘 막힐 것이다.

인덱싱 고려

partial index는 실제로 부분적으로만 인덱스가 생성되는만큼, 그 부분 조건에 일치하지 않으면 인덱스를 타지 않는다.

한번 실험해보자.

일단 데이터가 몇개 이상은 되어야 인덱스로 플랜을 잡기 떄문에, 쓰레기 데이터를 적당히 넣어준다.

Insert Into "user"(email, password, name, leftAt)
select t.n::text, 'auto generated', 'auto', null from (
  select generate_series(1, 10000) as n
) as t;

그리고 그냥 email로만 조건을 걸거나

leftAt IS NOT NULL을 걸면 인덱스를 절대 타지 않는다. 없기 때문이다.

인덱스를 타려면 무조건 Partial Index에서 정의된 조건이 명시되어야만 한다.

그래서 Partial Unique Index로 유일성을 구현한다고 하더라도, 실질적으로 인덱싱에 사용할 실제 전체 범위의 인덱스는 따로 마련해주는 편이 좋다.

참조
https://medium.com/little-programming-joys/unique-partial-indexes-with-postgresql-86e137905c12