커스텀 필드 iso8601 값으로 정렬된 토픽을 가져오는 방법?

개발
1

안녕하세요,

SQL/Active Record 쿼리만으로 해결하는 법이 잘 떠오르지 않는 이상한 쿼리를 하나 발견했습니다.

커스텀 필드를 가진 토픽들이 있고, ‘importedSortDate’ 커스텀 필드의 값을 기준으로 데이터를 정렬해야 합니다.

초기 코드
Topic.where(‘category_id in (?)’, [cat_id])
.where(‘closed = ?’, false)
.order(‘created_at desc’)
.limit(10)

이 쿼리를 어떻게 수정하거나 추가해야 메모리에 이 10개의 토픽만, 더 이상 로드하지 않고 가져올 수 있을까요?

커스텀 필드 테이블 템플릿이 name 컬럼과 value 컬럼을 가진 구조라 다소 특이해서, 이를 모두 데이터베이스 쿼리로 처리하는 방법에 대해 막막합니다.

메모리에 더 많은 데이터를 로드하는 문제의 핵심은, 메모리에 토픽 하나를 로드할 때마다 카테고리, 사용자, 게시물 조회(fetch)가 추가로 수행된다는 점입니다.

카테고리당 1,000~2,000개의 토픽을 다루고 있으며, 조회해야 할 카테고리는 4개입니다.
이 조회는 모든 사용자가 수행하며, 일회성 작업이 아닙니다.

현재까지 제가 생각한 해결 방법은 2가지입니다 (첫 번째는 제 기준으로는 충분히 빠르지 않다고 느껴집니다):

  1. 관심 있는 모든 토픽의 ID만 한 번의 조회로 가져온 후, 그 정보를 'importedSortDate’의 값과 어떻게든 조인(join)하고, 메모리에서 정렬한 다음, 동일한 순서대로 ID를 다시 조회합니다 (정확히 어떻게 구현해야 할지 잘 모르겠습니다).
  2. 커스텀 필드 테이블과 조인하는 쿼리를 만들어(몇 가지 예시를 봤지만 name과 value 컬럼이 고정된 값인 경우였습니다), ‘name’ === ‘importedSortDate’ 조건과 value 컬럼(iso8601 문자열)을 기준으로 전체를 정렬합니다.

이를 어떻게 구현할 수 있을까요?

P.S. 일부 토픽에는 importedSortDate 커스텀 필드에 값이 없을 수 있습니다. 이 경우 해당 토픽을 가져와 원하는 위치에 배치하면 되므로, 제가 처리할 수 있는 엣지 케이스입니다. 값이 없는 경우 첫 번째 항목으로 표시되어야 합니다.

P.P.S. 이후 이 쿼리를 페이지네이션 쿼리로 사용하려고 합니다. 페이지네이션 로직은 이미 구현되어 있으므로, 토픽을 가져오는 메인 쿼리만 변경하면 됩니다.

2

limit(10)이 이미 그렇게 처리할 것입니다 :slight_smile:

이런 방식으로 처리할 수 있습니다: (하지만 테스트는 하지 않았습니다)

Topic.where("category_id in (?)", [cat_id])
         .where("closed = ?", false)
         .joins("LEFT JOIN topic_custom_fields import_tcf ON import_tcf.topic_id = topic.id AND topic_custom_fields.name = 'importedSortDate'")
         .order("TIMESTAMP import_tcf.value")
         .order("created_at desc")
         .limit(10)

문제는 포스트그레SQL에 날짜 문자열을 타임스탬프로 캐스팅하도록 요청하는 것이 매우 비효율적이라는 점입니다. 모든 주제에 대해 먼저 변환을 수행한 후, 첫 번째 10개를 선택해야 하기 때문입니다.

여기서 몇 가지 옵션이 생각납니다 - ISO8601 대신 날짜를 "epoch 이후 초"로 저장할 수 있습니다. 그러면 포스트그레SQL이 정렬하는 것이 훨씬 쉬워집니다. 또는 데이터베이스 마이그레이션을 사용하여 topic_custom_fields 테이블에 TIMESTAMP value 인덱스를 추가할 수도 있습니다.

3

아…

흠, 그러면 iso8601 대신 에포시 이후의 초 단위를 저장해야 한다는 거네요. 그래야 문자열 비교가 정확히 작동하고 정렬도 제대로 되니까요.

import_tcf 값을 가져올 수 없을 것 같아서 너무 걱정하느라, 원시 SQL을 사용해야 한다는 걸 완전히 잊어버렸네요…

4

음 … 여기까지 왔습니다:

 topics = Topic.where("topics.category_id in (?)", [7])
    .where("topics.closed = ?", false)
    .joins("LEFT JOIN topic_custom_fields custom_fields ON custom_fields.topic_id = topics.id AND custom_fields.name = '#{Constants::TOPIC_SORT_DATE}'")
    .order("coalesce(cast(custom_fields.value as timestamp), topics.created_at) desc") # @falco에게 감사드립니다
  # .limit(10)

  array = topics.to_a.map do |t|
    next { id: t.id, createdAt: t.created_at, sortDate: t.custom_fields[Constants::TOPIC_SORT_DATE] }
  end
  puts array

출력 결과:

{:id=>25, :createdAt=>Thu, 14 May 2020 09:26:47 UTC +00:00, :sortDate=>nil}
{:id=>7017, :createdAt=>Tue, 06 Oct 2020 07:49:10 UTC +00:00, :sortDate=>"2011-01-02T22:00:00.000Z"}
{:id=>7016, :createdAt=>Tue, 06 Oct 2020 07:49:10 UTC +00:00, :sortDate=>"2011-01-02T22:00:00.000Z"}
{:id=>7058, :createdAt=>Tue, 06 Oct 2020 15:39:49 UTC +00:00, :sortDate=>"2010-02-02T00:00:00Z"}
{:id=>7008, :createdAt=>Tue, 06 Oct 2020 07:49:05 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7010, :createdAt=>Tue, 06 Oct 2020 07:49:06 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7011, :createdAt=>Tue, 06 Oct 2020 07:49:06 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7012, :createdAt=>Tue, 06 Oct 2020 07:49:07 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7013, :createdAt=>Tue, 06 Oct 2020 07:49:08 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7014, :createdAt=>Tue, 06 Oct 2020 07:49:08 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7015, :createdAt=>Tue, 06 Oct 2020 07:49:09 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7003, :createdAt=>Tue, 06 Oct 2020 07:49:01 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7004, :createdAt=>Tue, 06 Oct 2020 07:49:02 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7005, :createdAt=>Tue, 06 Oct 2020 07:49:03 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7006, :createdAt=>Tue, 06 Oct 2020 07:49:03 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7007, :createdAt=>Tue, 06 Oct 2020 07:49:04 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7009, :createdAt=>Tue, 06 Oct 2020 07:49:05 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7059, :createdAt=>Tue, 06 Oct 2020 15:49:16 UTC +00:00, :sortDate=>"2009-02-02T00:00:00Z"}
{:id=>7002, :createdAt=>Tue, 06 Oct 2020 07:49:01 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>7001, :createdAt=>Tue, 06 Oct 2020 07:49:00 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>6999, :createdAt=>Tue, 06 Oct 2020 07:48:59 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>6998, :createdAt=>Tue, 06 Oct 2020 07:48:58 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>7000, :createdAt=>Tue, 06 Oct 2020 07:49:00 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
5

아마도 모든 것을 타임스탬프로 변환하기 위해 스크립트를 실행해야 할 것 같습니다.

6

다음과 같이 변경하는 것이 좋습니다:

.order("COALESCE(CAST(custom_fields.value AS timestamp), custom_fields.value) DESC")

가독성을 높이기 위해서입니다.

sortDate 기준으로 정렬하고, 해당 값이 없으면 createdAt을 사용합니다. 모두 내림차순으로 정렬됩니다. 2020년이 2011년인 2등보다 “크기” 때문에 쿼리는 요청한 대로 데이터를 정확히 반환하고 있습니다.

null 값을 마지막에 배치하려면 다음이 필요합니다:

.order("CAST(custom_fields.value AS timestamp DESC NULLS LAST")
8

미안해 @Falco, 코드가 정상이었다는 걸 알아내고 최대한 빨리 수정했습니다.

수정:
코드를 다음과 같이 단순화했습니다:
.order(“coalesce(cast(custom_fields.value as timestamp), topics.created_at) desc”)

혹시 모를 상황을 대비해서요..

@david 타임스탬프로 변환하는 아이디어에 대해 말씀드리자면, 제 문제는 모든 카테고리가 동일한 주제 커스텀 필드 모델을 사용하지 않는다는 점입니다. 즉, 모든 주제에 대해 sort_date를 생성하거나, 현재 방식을 유지하고 sort_date || t.created_at으로만 정렬해야 한다는 뜻입니다.

타임스탬프의 경우, 전부 적용하거나 전혀 적용하지 않는 식이 되어야 합니다. :frowning:

9

@Falco @david ORDER BY에서 캐스팅 시 성능에 어떤 영향이 있는지 혹시 아시는 분이 있을까요?

이 스레드에서 작성자는 반환되는 항목 수가 증가하면 성능이 저하된다고 언급하고 있습니다. 저는 SQL 전문가가 아니기 때문에 이것이 사실인지 알 수 없습니다. (https://stackoverflow.com/a/491240/4020131)

그 이유는 char 데이터 타입의 경우 행을 문자열로 정렬하기 때문입니다.

ORDER BY CAST()를 사용하는 아이디어는 맞지만, 반환되는 결과 수가 증가할수록 이 방식의 성능은 저하됩니다.

해당 컬럼에 숫자 데이터만 있는 경우, 가장 좋은 방법은 적합한 숫자 데이터 타입을 찾아 컬럼을 변경하는 것입니다.

컬럼을 변경할 수 없고 성능 문제를 겪고 있다면, 정렬 순서 컬럼을 추가하고 그 컬럼에 값을 정수로 캐스팅하여 저장하는 것을 제안합니다 (null 값은 적절한 값으로 변환).

정렬 순서 컬럼에 인덱스를 만들고, 이상적으로는 CHAR 컬럼에 트리거를 추가하여 char 값의 삽입이나 업데이트가 정수 값의 업데이트를 트리거하도록 하는 것이 좋습니다.

그의 설명을 들어보니, 제가 10~20개의 주제만 원하는 경우, 데이터베이스에 얼마나 많은 주제가 있든 성능은 동일하게 유지된다고 이해했습니다.

저에게는 이것이 직관에 반하는 것입니다. 미리 캐스팅하지 않으면 어떻게 모든 주제를 정렬해서 10~20개를 가져올 수 있을까요?

또한 이 스레드 MSDN thread도 찾았지만, 제 경우, 즉 ORDER BY에서의 CAST에 어떻게 적용되는지 정확히 이해하지 못하겠습니다.

10

좋지 않습니다. 핫 패스에서 이 컬럼으로 쿼리를 실행할 계획이라면, 플러그인 마이그레이션에서 적절한 컬럼 타입과 인덱스를 가진 새 테이블을 추가하는 것이 나을 것입니다.

11

커스텀 필드 테이블의 조인, 즉 topic_id가 있는 항목만 검색하는 경우를 어떻게 생각하시나요? @Falco 이 부분도 성능에 영향을 미치지 않나요?

12

방금 생각해보니… 여기서 목표는 시간 순서대로 정렬하는 거죠? ISO8601 날짜의 YYYY-MM-DDTHH:MM:SS 형식을 보면, 실제로는 '알파벳 순서’로 정렬해도 시간 순서가 유지될 수 있을 것 같습니다.

캐스팅을 하지 않으면 postgres가 (name, value)에 있는 인덱스를 사용할 수 있을 것 같고, 훨씬 더 효율적일 것입니다.

13

그렇다면 create_at 부분은 어떻게 해야 할까요? 값이 있으면 sortDate를, 없으면 created_at을 사용하도록 하고 싶습니다.

14

잠깐, ISO8601은 원래 문자열 비교가 올바르게 되지 않나요? 그게 이 형식의 장점 중 하나가 아닌가요?

네, 맞습니다.

15

문제는 sortDate(내가 가지고 있는 값)와 created_at(내가 가지고 있지 않은 값)을 사용하여 크로스 정렬을 하되, 성능 저하 없이 하고 싶은 것입니다.

제 문제는 커스텀 필드의 값이 문자열(STRING)이고, created_at은 날짜(DATE) 타입이라는 점입니다.

16

아마도 이 방법을 시도해 볼 수 있습니다.
https://www.blendo.co/documents/queries-casting-postgresql/#:~:text=The%20TO_DATE%20function%20in%20PostgreSQL,to_timestamp(text%2C%20text).

17

방법을 하나 찾았지만, 캐스팅을 사용해야 합니다. 문자열을 날짜로 캐스팅하거나(sortDate가 커스텀 필드 값 문자열인 경우), created_at을 날짜에서 문자열로 변환하는 방식이죠.

더 적은 부담으로, DB 마이그레이션 스크립트와 같은 추가 작업이 필요하지 않은 해결책을 찾고 있었습니다.

데이터베이스 리베이스와 업그레이드 시점에 discourse가 커스텀 테이블을 어떻게 처리하는지 정확히 모르겠습니다.

18

향후 모든 항목에 sortDate 값을 채워 넣는 방식으로 이 문제를 다시 다룰 가능성이 큽니다. Topic 자체에서 설정할 수 있다면 더 나은 해결책이 될 텐데, 그렇게 하면 Discourse 업그레이드에 어떤 영향을 미칠지 다시 한번 잘 모르겠습니다.

커스텀 테이블에 대한 유용한 링크나 팁, 그리고 커스텀 테이블을 사용하는 경우 Discourse를 업데이트할 때 문제를 피하는 방법에 대해 조언을 주실 수 있을까요?

19

기존 테이블에 필드를 추가하거나 수정하는 것보다 커스텀 테이블을 사용하는 것이 훨씬 안전합니다. Discourse는 사용자의 테이블에 개입하지 않지만, 코어 변경 사항을 따라가기 위해 일부 작업을 수행해야 할 수 있습니다.

discourse-subscriptions와 discourse-calendar는 자체 테이블을 가진 공식 플러그인의 좋은 예시입니다.

20

@fzngagan 저는 3~4개월 전부터 Discourse를 사용하기 시작했고, Ruby도 함께 사용하고 있습니다 :slight_smile: "일부 작업을 수행한다"고 하셨을 때, 정확히 어떤 단계를 말씀하시는 건가요? 테이블이 존재하지 않는 경우 테이블을 생성하는 마이그레이션 스크립트를 작성하는 것과 같은 작업인가요? 작은 목록으로 도와주시면 문서화 시간을 크게 줄일 수 있을 것 같습니다 :expressionless:

미리 감사드립니다.

21

무언가 깨지는 것을 발견하게 되면(정기적으로 확인해야 합니다) 계속 고쳐야 합니다. 또한, 문제를 쉽게 찾을 수 있도록 단위 테스트를 작성할 수도 있습니다.