[Spring boot, PostgreSQL] 키워드 기반 게시물 검색 쿼리 및 구현 상세 분석

by흰담비팸 • • 2 min read
0

키워드 기반 게시물 검색 쿼리 및 구현 상세 분석




1. 서론

현대적인 웹 애플리케이션에서는 키워드 기반 검색이 필수적인 기능으로 자리 잡고 있습니다. 특히, 소셜 미디어나 게시판과 같은 플랫폼에서는 다양한 데이터를 효과적으로 검색하고 정렬하는 기능이 사용자 경험의 핵심입니다. 이번 글에서는 PostgreSQL의 ts_rank 및 to_tsvector 기능을 활용한 키워드 기반 게시물 검색 쿼리를 분석하고, Spring Data JPA를 활용하여 이를 구현한 방식을 살펴보겠습니다. 제공된 코드는 게시물 제목, 내용, 해시태그, 댓글, 좋아요 수 등을 종합적으로 고려하여 가장 적합한 결과를 반환하도록 설계되었습니다.

2. 주요 기능 및 쿼리 분석

2.1. 쿼리 목적

해당 쿼리는 다음과 같은 요구사항을 충족합니다:

  1. 다중 키워드 기반 검색: 제목, 내용, 해시태그에서 키워드를 검색.
  2. 사용자 정보 포함: 작성자의 닉네임 및 프로필 이미지.
  3. 종합적인 정렬 기준:
    • ts_rank를 기반으로 한 키워드 관련성.
    • 좋아요(likeCount), 조회수(viewCount), 댓글 수(commentCount).
  4. 페이징 처리: Spring Data JPA의 Pageable을 활용한 효율적 데이터 반환.

2.2. 주요 SQL 요소

2.2.1. CTE(Common Table Expression)

WITH keyword_search AS (
    SELECT DISTINCT
        p.id as id,
        p.user_id as userId,
        p.title,
        p.content,
        p.created_at as createdAt,
        u.nickname,
        u.profile_image_url as profileImageUrl,
        p.share_count as shareCount,
        p.view_count as viewCount,
        COUNT(DISTINCT pl.id) as likeCount,
        COUNT(DISTINCT c.id) as commentCount,
        ts_rank(
            setweight(to_tsvector('simple', COALESCE(p.title,'')), 'A') ||
            setweight(to_tsvector('simple', COALESCE(p.content,'')), 'B') ||
            setweight(to_tsvector('simple', string_agg(COALESCE(h.tag_name,''), ' ')), 'C'),
            to_tsquery('simple', :searchQuery)
        ) as rank
    FROM posts p
    INNER JOIN users u ON p.user_id = u.id
    LEFT JOIN post_likes pl ON p.id = pl.post_id
    LEFT JOIN comments c ON p.id = c.post_id
    LEFT JOIN post_hashtags h ON p.id = h.post_id
    WHERE p.status = :activeStatus
    AND p.visibility = :publicVisibility
    AND (
        p.title ILIKE ANY(SELECT CONCAT('%', TRIM(k), '%') FROM unnest(:keywords) AS k)
        OR p.content ILIKE ANY(SELECT CONCAT('%', TRIM(k), '%') FROM unnest(:keywords) AS k)
        OR h.tag_name ILIKE ANY(SELECT CONCAT('%', TRIM(k), '%') FROM unnest(:keywords) AS k)
    )
    GROUP BY p.id, p.user_id, p.title, p.content, p.created_at, u.nickname, u.profile_image_url
)
2.2.1.1. 주요 작업
  • WITH keyword_search AS (...)는 검색 결과를 임시 테이블로 생성합니다.
  • 랭킹 계산:
    • setweight와 to_tsvector를 조합하여 가중치 기반 검색.
    • to_tsquery로 키워드 쿼리를 생성.
  • 관련 데이터 포함:
    • LEFT JOIN을 활용하여 좋아요, 댓글, 해시태그 데이터를 추가.
  • 필터링 조건:
    • ILIKE로 키워드 부분 검색.
    • 상태 및 공개 여부 조건 추가.

2.2.2. 최종 결과 반환

SELECT * FROM keyword_search
ORDER BY 
    rank DESC,
    likeCount DESC,
    viewCount DESC,
    commentCount DESC
LIMIT :#{#pageable.pageSize}
OFFSET :#{#pageable.offset}
2.2.2.1. 정렬 기준
  1. 랭킹 우선: ts_rank 점수로 관련성 정렬.
  2. 보조 정렬: 좋아요, 조회수, 댓글 수로 추가 정렬.
2.2.2.2. 페이징 처리
  • LIMIT와 OFFSET을 사용하여 Spring Data JPA의 Pageable을 처리.

3. PostgreSQL 쿼리 함수 설명

3.1. to_tsvector

  • 텍스트를 토큰화하여 검색 가능한 형태로 변환합니다.
  • 예: to_tsvector('simple', 'hello world') → 'hello':1 'world':2
  • 여러 필드를 결합하여 검색에 사용할 수 있습니다. 
    to_tsvector('simple', title || ' ' || content)

3.2. to_tsquery

  • to_tsvector로 변환된 데이터와 매칭되는 검색 쿼리를 생성합니다.
  • 예: to_tsquery('simple', 'hello & world') → 'hello' AND 'world'

3.3. setweight

  • to_tsvector 결과에 가중치를 부여합니다.
  • 가중치 레벨:
    • 'A': 최고 우선순위 (예: 제목)
    • 'B': 중간 우선순위 (예: 내용)
    • 'C': 낮은 우선순위 (예: 해시태그)
  • 예: 
    setweight(to_tsvector('simple', COALESCE(title, '')), 'A')

3.4. string_agg

  • 여러 행의 값을 하나의 문자열로 결합합니다.
  • 예: 
    string_agg(tag_name, ' ')
    → 태그 이름을 공백으로 구분하여 결합.

3.5. ts_rank

  • to_tsvector와 to_tsquery의 매칭 점수를 계산하여 결과를 정렬합니다.
  • 예: ts_rank(vector, query)

3.6. ILIKE

  • 대소문자를 구분하지 않고 문자열 매칭을 수행합니다.
  • 예: ILIKE '%keyword%'

3.7. COALESCE

  • COALESCE() 함수는 NULL 값을 처리하는 데 사용됩니다. 첫 번째 인자가 NULL일 경우 두 번째 인자를 반환합니다.
  • 예: 
    COALESCE(p.title, '')
    → title 필드가 NULL일 경우 빈 문자열을 반환하여 to_tsvector 함수가 오류 없이 처리되도록 보장합니다.

3.8. unnest

  • PostgreSQL의 unnest() 함수는 배열을 개별 행으로 변환하는 데 사용됩니다. 배열 내의 각 요소를 독립적인 행으로 변환하여 SQL 쿼리에서 개별적으로 처리할 수 있도록 합니다.

활용 설명

  • 기능: 배열 타입 데이터를 개별 행으로 변환하여 쿼리에서 배열의 각 요소를 독립적으로 처리할 수 있습니다.
  • 장점: 키워드 검색과 같이 다중 요소를 조건으로 사용해야 하는 시나리오에서 매우 유용합니다.
  • 예시: 
    SELECT unnest(ARRAY['keyword1', 'keyword2', 'keyword3']);
    → 결과: 
    keyword1
keyword2
keyword3

활용 사례

  • 다중 키워드 검색: 키워드 배열을 unnest()로 변환하여 각 키워드를 개별 조건으로 활용.
  • 동적 조건 생성: 배열 데이터를 조건문에서 동적으로 처리.

응용 방법

  • 쿼리 작성자는 unnest()를 통해 배열 데이터를 손쉽게 행으로 분리하여 조건문에서 활용할 수 있습니다. 이를 통해 보다 유연하고 확장 가능한 SQL 조건을 작성할 수 있습니다.
  • 응용 예시: 
    SELECT * FROM posts
WHERE title ILIKE ANY (SELECT '%' || unnest(ARRAY['keyword1', 'keyword2', 'keyword3']) || '%');
    → 각 키워드가 제목에 포함된 모든 게시글을 반환.


문서에서 선택된 내용을 표로 다시 작성하며, 잘못된 텍스트를 정리했습니다. 새로운 표 형식을 아래와 같이 제시하겠습니다:

함수/구문 설명
WITH 공통 테이블 표현식(CTE)을 정의합니다. 여기서는 'keyword_search'라는 임시 결과 집합을 생성합니다.
DISTINCT 중복된 행을 제거합니다.
COUNT 지정된 조건을 만족하는 행의 수를 계산합니다.
COALESCE 첫 번째로 NULL이 아닌 인자를 반환합니다. NULL 값 처리에 사용됩니다.
to_tsvector 문자열을 tsvector 타입으로 변환합니다. 전문 검색에 사용됩니다.
setweight tsvector의 각 어휘소에 가중치를 할당합니다.
to_tsquery 검색어를 tsquery 타입으로 변환합니다.
ts_rank 검색 결과의 관련성 순위를 계산합니다.
string_agg 지정된 구분자로 문자열을 연결합니다.
INNER JOIN 두 테이블 간의 내부 조인을 수행합니다.
LEFT JOIN 왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하는 행을 반환합니다.
ILIKE 대소문자를 구분하지 않는 패턴 매칭을 수행합니다.
ANY 배열의 어느 값과도 일치하는지 확인합니다.
unnest 배열을 행으로 확장합니다.
GROUP BY 지정된 열을 기준으로 결과를 그룹화합니다.
ORDER BY 결과를 지정된 열을 기준으로 정렬합니다.
LIMIT 반환되는 행의 수를 제한합니다.
OFFSET 결과 집합에서 건너뛸 행의 수를 지정합니다.


4. Spring Data JPA 구현

4.1. 함수 정의

함수 주석

/**
 * <h3>키워드로 게시물 검색</h3>
 * <hr>
 * <p>- 상세 설명 -</p>
 * <p> 키워드로 게시물을 검색합니다. 키워드는 게시물의 제목, 내용, 해시태그에 포함된 키워드입니다.</p>
 * @since 2024-12-25
 * @param searchQuery 검색 키워드
 * @param keywords 검색 키워드 목록
 * @param activeStatus 게시물 상태
 * @param publicVisibility 게시물 공개 여부
 * @param pageable 페이지 요청
 * @return 게시물 목록
 */

함수 선언

@Query(value = "...", nativeQuery = true)
Page<PostSearchProjection> searchPostsByKeyword(
    @Param("searchQuery") String searchQuery,
    @Param("keywords") String[] keywords,
    @Param("activeStatus") Integer activeStatus,
    @Param("publicVisibility") Integer publicVisibility,
    Pageable pageable);

5. 성능 최적화

5.1. 인덱스 활용

  • GIN 인덱스를 활용하여 검색 성능을 크게 향상시켰습니다. 다음은 적용된 인덱스입니다:
CREATE INDEX idx_posts_search ON posts USING gin(to_tsvector('simple', title || ' ' || content));
CREATE INDEX idx_hashtags_search ON post_hashtags USING gin(to_tsvector('simple', tag_name));
5.1.1. 효과
  • posts 테이블의 title 및 content 필드에 대한 검색 속도가 크게 개선됩니다.
  • post_hashtags 테이블에서 tag_name을 검색할 때도 효율이 향상됩니다.
  • 인덱스는 to_tsvector를 활용한 Full-Text Search와 잘 결합되어 빠른 검색을 지원합니다.

5.2. JOIN 최적화

  • LEFT JOIN 대신 필요한 경우 INNER JOIN 사용.
  • GROUP BY 및 COUNT 작업 최소화.

5.3. 캐싱

5.3.1. Redis 캐싱의 도입

Redis는 인메모리 데이터 저장소로, 자주 사용되는 데이터나 결과를 캐싱함으로써 데이터베이스 요청 부하를 줄이고 성능을 크게 향상시킬 수 있습니다. 특히, 본 구현에서는 검색 쿼리의 결과를 캐싱하여 반복적인 키워드 검색 요청에서 빠른 응답을 제공합니다.

5.3.2. Redis 캐시 적용 방법

  • Spring Cache를 활용하여 Redis와 통합.
  • 키워드와 페이징 정보를 캐시 키로 설정.
@Transactional(readOnly = true)
@Cacheable(value = "postSearchCache", key = "#keywords.toString() + #pageable.pageNumber + #pageable.pageSize")
public Page<PostResponse> searchPosts(List<String> keywords, Pageable pageable) {
    if (keywords == null || keywords.isEmpty()) {
        return Page.empty(pageable);
    }

    // 정렬 조건을 제거한 새로운 Pageable 객체 생성
    Pageable pageableWithoutSort = PageRequest.of(
        pageable.getPageNumber(),
        pageable.getPageSize()
    );

    // 키워드를 하나의 검색 쿼리로 변환
    String searchQuery = keywords.stream()
        .map(keyword -> keyword.trim().replaceAll("\\s+", " & "))
        .collect(Collectors.joining(" | "));

    // 키워드를 배열로 변환
    String[] keywordsArray = keywords.toArray(new String[0]);

    // 단일 쿼리로 검색 수행
    Page<PostSearchProjection> searchResults = postRepository.searchPostsByKeyword(
        searchQuery,
        keywordsArray,
        PostStatus.POST_STATUS_PUBLISHED.getValue(),
        PostVisibility.POST_VISIBILITY_PUBLIC.getValue(),
        pageableWithoutSort
    );

    if (searchResults.isEmpty()) {
        return Page.empty(pageable);
    }

    return searchResults.map(this::convertToPostResponse);
}

5.3.3. 기대 효과

  • 동일한 키워드와 페이지 요청에 대해 Redis에서 즉시 응답하여 데이터베이스 부하를 감소.
  • 자주 사용되는 검색 요청에서 처리 속도를 크게 단축.
  • Redis의 TTL(Time-To-Live)을 설정하여 캐시된 데이터의 유효 기간 관리.

6. 결론

해당 구현은 다양한 요구 사항을 충족하면서도 PostgreSQL의 강력한 검색 기능을 효과적으로 활용하는 예입니다. 성능 최적화를 위해 인덱스 추가와 쿼리 튜닝을 통해 검색 속도를 크게 개선하였으며, Redis 캐싱을 도입하여 반복적인 요청에 대한 응답 성능을 향상시켰습니다. 이와 함께, Spring Data JPA의 페이징 기능과 Redis를 결합하여 높은 확장성을 제공하는 구조를 설계하였습니다. 이러한 최적화는 사용자 경험을 크게 향상시킬 수 있습니다.

4.94 / 169 rates
다음 이전