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

Popular posts from this blog

삼성전자 무선사업부 퇴사 후기

개발자 커리어로 해외 취업, 독일 이직 프로세스

코드리뷰에 대하여