TypeORM - 수백만건의 데이터 0.1초만에 조회하기

물론 제목은 어그로다...

 

기존데이터

데이터 수	100만건	100만건
조회 시간	10분이상	0.1초
조회 방식	like	tsvector

1. tsvector란?

like & like 연산자가 db를 풀 스캔하고 있을때 너무나 시간이 오래 소요된다. 이를 해결하기 위해서 postgresql 에서 제공하는 데이터 유형으로, 텍스트 검색을 위한 벡터 데이터를 저장하는 방식을 말한다.

이 데이터는 주로 텍스트 문서의 내용을 토큰화 하고, 효율적으로 검색할 수 있게 인덱싱을 하는데 사용된다. 각 토큰은 문서에서의 위치와 옵션으로 가중치도 포함이 가능해진다.

풀 텍스트 검색에서는 문서의 텍스트를 어휘적 분석을 거쳐 중요 토큰으로 나누고 이 토큰들을 TSVector로 변환합니다.

특히 배열이나 JSON 같은 복잡한 구조의 데이터에 효과적이며, 풀 텍스트 검색에서는 TSVector 유형에 대한 인덱스로 많이 사용됩니다.

예제 문장
The quick brown fox
"the" "quick" "brown" "fox"
네 단어로 분해되고, 벡터 형태로 저장됨 

2. GIN 인덱스

Postgresql에서 복합적인 데이터 값들에 대해 빠른 검색을 제공하는 인덱스 유형으로 TSVector 유형에 대한 인덱스로 많이 사용됩니다.

GIN 인덱스는 각 키 (예: 단어) 에 대해 어느 문서에 등장하는지를 매핑하는 역 인덱스의 형태를 가지고 있고, 이 인덱스는 텍스트 검색 쿼리가 실행될때, 매우 빠른 검색 성능을 제공하여, 대용량의 텍스트 데이터베이스에서도 빠르게 원한는 결과를 찾을 수 있게 도와줍니다.

3. 예제코드

예를들어 현재 url 이 아래와 같다고 해보자

{{server}}/notices?pageNo=1&pageSize=100&order=postedDate.asc&keyword=missile&productServiceCode=1420

이와 같은 url 을 통해서 온다고 했을때

현재 controller 의 코드는 다음과 같다.

public getNotices = async (req: Request, res: Response, next: NextFunction): Promise<void> => {
    try {
        const pagination = getPaginationParam(req);
        const response = new PagedResponseDto<NoticeDto>()
        const filteringOptions = this.getFilteringOptionsParam(req)
        const orderingOptions = getOrderingOptionParam(req, 'postedDate');
        response.totalItems = await this.service.countAllNotices(filteringOptions);
        response.pageNo = pagination.pageNo;
        response.pageSize = pagination.pageSize;
        response.totalPages = Math.ceil(response.totalItems / pagination.pageSize);
        const item  = await this.service
              .findAllNotices(filteringOptions, orderingOptions, pagination.pageNo, pagination.pageSize)
              .then(notices => notices.map(notice => NoticeDto.fromEntity(notice)));
        response.items =item
        res.status(200).json(response)
    } catch (e) {
       next(e)
    }
  }

여기에 pagination 코드도 있고, orderingOptions 들도 있지만 그건 뒤로 하고, 먼저 첫번째 service code를 확인해보자

...
public async countAllNotices(filteringOptions: FilteringNoticeOptions): Promise<number> {
    const {where, parameters} = this.buildWhere(filteringOptions);
    const repository = getRepository(NoticeEntity);
    console.log(where, parameters)
    return await getRepository(NoticeEntity)
    .createQueryBuilder("notice")
    .where(
        where, parameters
    )
    .getCount();
}
...
private buildWhere(filteringOptions: FilteringNoticeOptions) {
    const conditions = [];
    if (filteringOptions.naicsCode) {
      conditions.push(`"naics_code" = :naicsCode`);
    }
    if (filteringOptions.productServiceCode) {
      conditions.push(`"classification_code" = :productServiceCode`);
    }
    if (filteringOptions.solNumber) {
      conditions.push(`"sol_number" = :solNumber`);
    }
    if (filteringOptions.keyword) {
      const keywordFormatted = filteringOptions.keyword.split(' ').join(' | '); // 공백을 OR 연산자로 대체
  
      const titleCondition = `to_tsvector('english', "title") @@ to_tsquery('english', :keyword)`;
      const summaryCondition = `meaningful = 1 AND to_tsvector('english', "summary") @@ to_tsquery('english', :keyword)`;
  
      conditions.push(`(${titleCondition} OR ${summaryCondition})`);
    }
    return {
      where: conditions.length > 0 ? conditions.join(' AND ') : 'TRUE',
      parameters: {
        naicsCode: filteringOptions.naicsCode,
        productServiceCode: filteringOptions.productServiceCode,
        solNumber: filteringOptions.solNumber,
        keyword: filteringOptions.keyword ? filteringOptions.keyword.split(' ').join(' | ') : undefined
      }
    };
  }
...

buildwhere에서 url 에서 받은 keyword, productServiceCode, solNumber… 등등을 받아온다.

conditions 이라는 배열을 만들고
각 조건들이 부합하면 추가한다.

keyword 는 사용자가 space를 누르는 경우가 있을 수 있으므로 그 경우에 대비하여 코드를 설정한다.

그리고 마지막으로 where 절에서 조건이 하나라도 일치한다면 query 용 문장을 AND 를 통해서 만들어준다.

이후 해당하는 값들의 파라미터를 동적으로 할당해 주면서 TSQuery 를 통해서 빠르게 서칭을 진행 할 수 있다.