PostgreSQL 안티패턴: 레지스트리 탐색

오늘날 SQL에는 복잡한 사례와 정교한 알고리즘이 없습니다. Captain Obvious 수준에서는 모든 것이 매우 간단해집니다. 해봅시다. 이벤트 레지스트리 보기 시간별로 정렬했습니다.

즉, 데이터베이스에 기호가 있습니다. events, 그리고 그녀는 밭을 갖고 있어요 ts - 이러한 기록을 순서대로 표시하려는 정확한 시간:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

거기에는 XNUMX개의 레코드가 없을 것이 분명하므로 어떤 형태의 레코드가 필요합니다. 페이지 탐색.

#0. “나는 우리 엄마의 학살자예요”

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

거의 농담이 아닙니다. 드물지만 야생에서 발견됩니다. 때로는 ORM으로 작업한 후 SQL을 사용하여 "직접" 작업으로 전환하는 것이 어려울 수 있습니다.

하지만 더 일반적이고 덜 분명한 문제로 넘어가겠습니다.

#1. 오프셋

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

숫자 26은 어디에서 왔습니까? 이는 한 화면을 채우는 대략적인 항목 수입니다. 보다 정확하게는 25개의 표시된 레코드에 1을 더한 것입니다. 이는 샘플에 최소한 다른 항목이 더 있으므로 계속 진행하는 것이 합리적이라는 신호입니다.

물론 이 값은 요청 본문에 "꿰어질" 수 없으며 매개변수를 통해 전달됩니다. 그러나 이 경우 PostgreSQL 스케줄러는 상대적으로 적은 수의 레코드가 있어야 한다는 지식에 의존할 수 없으며 쉽게 비효율적인 계획을 선택할 것입니다.

그리고 애플리케이션 인터페이스에서 레지스트리 보기는 시각적 "페이지" 간 전환으로 구현되므로 오랫동안 아무도 의심스러운 점을 눈치채지 못합니다. 편의성을 위해 UI/UX가 인터페이스를 "끝없는 스크롤"로 다시 만들기로 결정한 바로 그 순간까지 말입니다. 즉, 모든 레지스트리 항목은 사용자가 위아래로 스크롤할 수 있는 단일 목록에 그려집니다.

그래서 다음 테스트에서 당신은 잡혔습니다. 기록의 복제 레지스트리에서. 이유는 테이블에 일반 인덱스가 있기 때문입니다. (ts), 귀하의 쿼리는 무엇에 의존합니까?

정확히는 당신이 그 점을 고려하지 않았기 때문입니다 ts 고유 키가 아닙니다. 이 테이블에. 사실, 그리고 그 값은 고유하지 않습니다, 실제 조건의 "시간"과 마찬가지로, 동일한 키 값을 정렬하는 프레임워크 내에서 최종 순서가 다르기 때문에 인접한 두 쿼리의 동일한 레코드가 페이지에서 페이지로 쉽게 "점프"됩니다.

사실 여기에는 훨씬 더 눈에 띄기 어려운 두 번째 문제가 숨겨져 있습니다. 일부 항목은 표시되지 않습니다 조금도! 결국 "중복" 기록이 다른 사람의 자리를 차지하게 된 것입니다. 아름다운 사진과 함께 자세한 설명을 보실 수 있습니다 여기를 읽으십시오..

인덱스 확장

교활한 개발자는 인덱스 키를 고유하게 만들어야 하며 가장 쉬운 방법은 분명히 고유한 필드로 인덱스 키를 확장하는 것입니다. PK는 다음과 같은 경우에 적합합니다.

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

그리고 요청은 다음과 같이 변경됩니다.

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. "커서"로 전환

얼마 후 DBA가 귀하에게 와서 귀하의 요청에 "기뻐합니다" 그들은 OFFSET 규칙으로 서버를 지옥처럼 로드합니다., 일반적으로 다음으로 전환해야 할 때입니다. 표시된 마지막 값에서 탐색. 쿼리가 다시 변경되었습니다.

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

오기 전까지 안도의 한숨을 쉬셨는데...

#삼. 인덱스 청소

어느 날 당신의 DBA가 읽었기 때문입니다. 비효율적인 인덱스 찾기에 대한 기사 그리고 그걸 깨달았다 "최신이 아님" 타임스탬프가 좋지 않음. 그리고 나는 다시 당신에게 왔습니다. 이제 그 지수는 여전히 다시 (ts DESC).

그러나 페이지 사이에 레코드를 "점프"하는 초기 문제는 어떻게 처리해야 할까요?.. 그리고 모든 것이 간단합니다. 레코드 수가 고정되지 않은 블록을 선택해야 합니다!

일반적으로 "정확히 26"이 아니라 "26 이상"을 읽는 것을 누가 금지합니까? 예를 들어, 다음 블록에는 다음이 있습니다. 명백히 다른 의미를 지닌 기록 ts - 그러면 블록 사이의 기록을 "점프"하는 데 문제가 없을 것입니다!

이를 달성하는 방법은 다음과 같습니다.

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

여기서 무슨 일이 일어나고 있는 걸까요?

1. 25개의 레코드를 "아래로" 단계화하고 "경계" 값을 얻습니다. ts.
2. 이미 아무것도 없으면 NULL 값을 다음으로 바꿉니다. -infinity.
3. 받은 값 사이에서 전체 값 세그먼트를 뺍니다. ts 인터페이스에서 전달된 $1 매개변수(이전의 "마지막" 렌더링된 값)입니다.
4. 26개 미만의 레코드가 포함된 블록이 반환되면 이것이 마지막 블록입니다.

아니면 같은 사진:

왜냐면 지금 우리는 샘플에는 특정 "시작"이 없습니다., 그러면 이 요청을 반대 방향으로 "확장"하고 "참조 지점"에서 양방향(아래쪽과 위쪽 모두)으로 데이터 블록의 동적 로드를 구현하는 것을 방해하는 것은 없습니다.

참고 :

1. 예, 이 경우 인덱스에 두 번 액세스하지만 모든 것이 "순전히 인덱스 기준"입니다. 따라서 하위 쿼리의 결과는 다음과 같습니다. 하나의 추가 인덱스 전용 스캔에.
2. 이 기술은 값이 있을 때만 사용할 수 있다는 것은 매우 분명합니다. ts 우연히만 건너갈 수 있고, 그 중 많지 않습니다. 일반적인 사례가 "00:00:00.000에 레코드 백만 개"인 경우에는 이렇게 하면 안 됩니다. 내 말은, 그런 일이 일어나도록 놔둬서는 안 된다는 것입니다. 하지만 이런 일이 발생하면 확장 인덱스가 있는 옵션을 사용하세요.

출처 : habr.com