Efficient PostgreSQL Index 생성을 위한 몇가지 참고점
- B-Tree
- MySQL 과 마찬가지로, CREATE INDEX 를 하게 되면 B-Tree 를 사용한다.
- B-Tree 는 별도로 공부해야하지만, it tries to remain balanced. 가 핵심이다. 각 tree의 branch 들에 속한 값의 양들이 거의 동일하게된다.
- INDEX Case by case
select * from foo where bar = 1
select * from foo where bar = 2
- bar=2 인 경우가 굉장히 많으면, 오히려 안타게 된다. sequential scan 이 index scan 보다 훨씬 빠르기 때문이다.
- Partial Index
- Index 는 Index 인데, where 조건이 포함된 Index 이다
- Index size 를 줄임으로써 Index에 대한 효율성을 높일 수 있다
- 아래와 같은 경우, comments 중 flag 가 true 인 것들에 대해서만 created_at에 Index 를 걸 수 있다.
CREATE INDEX idx_comments_flagged_created_at ON comments(created_at) WHERE flag IS TRUE;
- Expression Index
- data 를 function 에 넣은 결과값에 대해 Index를 걸 수 있다.
- 예를들어, 사용자의 email 계정을 저장하는 column 이 있다고 할때, 사용자가 입력한대로 값은 저장하되 로그인등 인증시에는 아래처럼 소문자로 처리하고 싶으면,
-
WHERE lower(account) = '{lowercased_email}'
- Index 는 아래와 같이 걸수 있다.
CREATE INDEX idx_users_account_email ON users(lower(account));
- 혹은 Timestamp Column 에 대해 날짜에 대해서만 검색을 하는 경우,
-
WHERE date(posts.published_at) = date('2020-07-07')
- 다음과 같이 할 수도 있다.
CREATE INDEX idx_posts_day ON posts (date(published_at));
- Unique Index
- UK 는, data integrity 측면과 성능 측면에서 중요한 역할을 한다.
- UK 와 Unique Constraints 에 는 차이가 있는데, Unique Constraints 는 위의 Partial, Expression Index 를 사용할 수 없다.
- Multi-column Index
- Postgres query planner 는 기본적으로 Multiple single-column indexes 들을 잘 조합해서 사용하는 능력이 있다. multi-column query 시에 말이다.
- 따라서, query 조건에 들어오는 column 들에 대해 각각 single-column index 들만 잘 걸어두면, Postgres 에서 알아서 잘 선택해준다.
- 그럼에도 multi-column index 를 생성한다면, order 가 중요하므로 반드시 column 순서를 고려해야 하고, 항상 index 에는 cost 가 따른다는점을 생각해서 benchmark 테스트를 해야한다.
- 아래와 같은 index 가 있다고 ㅎ
CREATE INDEX idx_posts_like_dislike ON posts (like,dislike);
- 아래와 같은 두 경우는 index 를 사용한다
-
WHERE like=n and dislike=m;
-
WHERE like=n;
- 하지만 아래의 경우에는 사용되지 않는다.
-
WHERE dislike=m;
- 이 경우, column like 에만 따로 또 index 를 거는것은 중복에 해당한다.
- B-Trees and Index
- B-Tree Index 는 기본적으로 ascending order 이다.
- 하지만 descending order 로 indexing 을 할 수도 있으며, 다음과 같은 경우 이익을 얻을 수있다.
- 예를들어, post 들을 paging 할때는 최신 순으로 읽어오면서 paging 을 하는데, 이런 경우이다.
CREATE INDEX idx_posts_published_at_index ON posts(published_at DESC NULLS LAST);
- Necessarily
- Index 를 건다고 해서 row 의 모든 데이터가 전부 index 에 포함되지 않는다. 따라서 index 를 통해 query 가 매칭 된다 하더라도 disk block 을 찾아가서 row data 를 fetching 한다.
- 따라서 index 는 disk-lookup 을 줄일 수 있는 경우에 사용되어야 한다
- 예를들어, big table 에서 PK 를통해 접근하는 것은 sequential scanning 을 피할수 있다
- small table 같은 경우, 예를 들어 cities table 같은, city_name 으로 querying 을 한다 할지라도 index 가 불필요 할 수도 있다.
- (Random I/O 는 sequential scanning 보다 비싸니, 이런 경우는 index 를 타는게 overhead 라고 판단되는 것 같다. )
- 이런 경우 Postgres 는 Index 를 무시하고 sequential scan 을 단행할 수 있으며, 이경우 index 는 dead index 가 된다.
- index 는 결코 공짜가 아니므로 이런 관점에서 꼭 필요한지 따져봐야 한다.
- benchmarking 을 할때도, 실 데이터 혹은 실데이터와 유사한 양의 데이터를 가지고 테스트를해야 효과가 있다. 그저 row 몇백개, 천개 가지고는 원하는 indexing 결과를 보기가 힘들다.
- Table Lock
- MySQL에서도 경험한 적이 있는데, CREATE INDEX 는 table lock(writing) 을 동반하는 작업이므로, productive 환경에서는 유의해야한다. 서비스 down 이 발생할 수도 있다.
- Postgres 는 CREATE INDEX CONCURRENTLY 라는 옵션이 있는데, table lock 을 걸지 않고 작업이 가능한것 같다. 물론 훨씬 더 오래 걸린다.
- index 도 시간이 지남에 따라 fragmented, unoptimized 되는데, 특히나 빈번히 업데이트, 삭제되는 column에 걸린 경우 더 그렇다.
- 그런 경우 REINDEX 를 수행해야 하는데, 이 역시 table lock 이 걸리므로, 동일한 column 으로 concurrently 수행하고, old index 를 삭제하는 방식으로 할 수 있다.
Comments
Post a Comment