[Project] 프로젝트 삽질기6 (feat PostgreSQL 버전)

들어가며

사이드 프로젝트에서 푸시 알림을 활용한 서비스를 개발하고 있습니다. 그 과정에서 생각하고 배웠던 점들을 하나씩 작성하고자 합니다. 먼저 푸시 알림을 구성하기 위한 로직을 작성하면서, AWS RDS를 활용하고 있습니다. 프로젝트를 진행하면서, DB의 버전에 대한 깊은 생각 없이, 무료로 이용할 수 있는 프리티어 버전을 이용했습니다. 하지만 그 과정에서 버전에 대한 문제가 발생했습니다. 이 글은 제가 겪었던 DB 버전 문제는 무엇이고, 문제를 어떻게 해결했는지에 대해 정리하고자 작성된 글입니다.   

 

 

 

 

 

 

---

 

 

 

 

 

 

 

 

 

PostgreSQL RDS 버전 문제

얼마전 AWS에서 메일 한 통을 받았습니다. 그 내용은 다음과 같았습니다.

 

 

Amazon Relational Database Service (Amazon RDS) 는 PostgreSQL 데이터베이스 (DB) 메이저 버전 9.6의 수명 종료 프로세스를 연장합니다. 아마존 RDS 고객은 2022년 3월 31일까지 계속해서 새 PostgreSQL 9.6 인스턴스를 생성할 수 있으며, 아마존 RDS는 2022년 4월 26일부터 PostgreSQL 9.6 인스턴스를 자동으로 업그레이드합니다. 이 알림은 Amazon Aurora 데이터베이스 클러스터에는 적용되지 않습니다.

이 수명 종료 일정에 앞서 PostgreSQL 9.6 인스턴스를 가능한 한 빨리 버전 12 이상으로 업그레이드하는 것을 권고합니다. PostgreSQL 커뮤니티는 2021년 11월 11일 PostgreSQL 9.6에 대한 지원을 중단했으며 [2, 더 이상 이 버전에 대한 버그 수정 또는 보안 패치를 생성하지 않고 있습니다. PostgreSQL 12에는 SQL/JSON 사양에 따른 JSON 경로 쿼리와 플러그 가능한 테이블 스토리지 인터페이스 [3] 등 주요 혁신 기술이 포함되어 있습니다. PostgreSQL 13은 VACUUM 명령을 사용하여 인덱스의 병렬 처리를 도입했으며 B 트리 인덱스에 의한 중복 데이터 처리를 개선했습니다 [4]. 두 버전 모두 이전 버전의 데이터베이스의 다양한 소프트웨어 버그에 대한 수많은 수정 사항이 포함되어 있습니다.

 

 

이 글이 뜻하는 바가 무엇인지 알 수 없었습니다. 그래서 AWS 계정을 통해 RDS 서비스에 들어가 보니, 아래와 같은 문구가 있었습니다.

 

 

 

 

간단하게, PostgreSQL 9.6 버전이 종료된다는 뜻이었습니다. 생각해보면, DB의 버전에 따라 지원되는 기능은 무엇이고, 현재 사용하고 있는 DB는 어떤 기능을 제공하는지에 대해 궁금했습니다. 이를 통해 처음으로 DB의 버전에 대해 생각해볼 수 있었습니다. 기능에 대해 다 알 순 없지만, 적어도 커버링 인덱스를 지원하는가, 지원하지 않는가에 대해서 버전별로 살펴봤습니다. 

 

 

 

 

출처 : PostgreSQL (Aurora) 10 vs 11 버전 성능 비교 (이동욱님 블로그)

 

 

 

 

PostgreSQL은 현재 14 버전까지 나왔습니다. 여기서 제가 사용하고 있는 버전이 9.6버전이었으니, 그 이상의 버전들은 어떤 기능들을 제공하는지에 대해 간략히 살펴봤습니다.

 

가장 눈에 보이는 것은, 커버링 인덱스의 지원 여부였습니다. 10버전의 경우 B-tree 커버링 인덱스를 지원하지 않았고, PK 인덱스를 우선적으로 지원하는 점이었습니다. 만약 PK 인덱스가 우선된다면, 옵티마이저가 잘못된 판단을 내려서, 쿼리 시간을 대폭 늘릴 수 있는 문제가 발생할 수 있습니다. 어떤 문제가 발생할 수 있는지 예시를 통해 알아보겠습니다. (아래의 예시는 이동욱님의 글을 참고했습니다. )

 

예를 들어 400만 건이 넘는 데이터에서 다음의 쿼리를 실행해서 원하는 데이터를 얻고 싶은 경우 보통 1~5초 정도 수행됩니다.

 

 

SELECT "vouchers"."id"
FROM "vouchers" AS "vouchers"
WHERE "course_id" in (select "id" from "courses" WHERE ("slug" = '코딩테스트-자바-실전' AND "deleted_at" IS NULL))
  AND "user_id" in (select "id" from "users" WHERE ("deleted_at" IS NULL))
  AND "deleted_at" IS NULL
ORDER BY "id" DESC
LIMIT 30 OFFSET 0;

 

 

여기서 ORDER BY를 제외하고 다시 수행하면, 0.015초만에 수행됩니다. ORDER BY가 시간을 왜 이렇게 잡아먹는지 살펴보면, 그 이유는 바로 정렬 후 필터링을 하기 때문입니다. 정렬을 먼저 하는 이유는 옵티마이저가 PK 인덱스를 기준으로 읽으면서 조건을 검증하는 것이 조건으로 먼저 걸러내고 후정렬 하는 것보다 빠르다고 판단했기 때문입니다. 이 문제를 해결하기 위해서는 옵티마이저가 잘 선택할 수 있도록 가이드가 필요합니다. 이를 위해 2가지 해결 방법이 있습니다.

 

 

1. 어느 인덱스를 선택할지 인덱스를 강제화한다.
2. 옵티마이저가 원하는 인덱스를 선택하도록 쿼리문을 개조한다.

 

일반적으로 인덱스를 강제하는 것은 조건문 변경 / 인덱스 변경 / 인덱스 추가 / DB 버전 변경으로 인한 인덱스 작동방식 변경등에 대해서 대처하기가 어렵습니다. 그래서 2번째 방법인 옵티마이저가 좋은 인덱스를 타도록 쿼리문을 개조하는 방식을 선택합니다. 여기서는 PK 인덱스의 운선순위를 낮추도록 쿼리를 변경합니다.

 

 

select "vouchers"."id"
FROM "vouchers" AS "vouchers"
WHERE "course_id" in (select "id" from "courses" WHERE ("slug" = '타입-파이썬' AND "deleted_at" IS NULL))
  AND "user_id" in (select "id" from "users" WHERE ("deleted_at" IS NULL))
  AND "deleted_at" IS NULL
ORDER BY "id" + 0 DESC
LIMIT 30 OFFSET 0;

 

 

+0을 추가해서 PK 인덱스를 사용하지 못하도록 설정했고, 이를 통해 Where 문의 인덱스를 선택하도록 변경했습니다. 이렇게 활용하면, 쿼리 시간을 대폭 단축시킬 수 있습니다. 

 

즉 10버전 이하를 활용하면, 커버링 인덱스를 활용하지 못한다는 것과 PK 인덱스를 우선적으로 지원한다는 것을 알 수 있었습니다. 하지만 11버전 이상으로 활용하면, 커버링 인덱스를 활용할 수 있으면서, PK 인덱스가 우선 적용되는 문제에 대해 해결할 수 있습니다. 만약 11버전 이상으로 업데이트 된다면 어떤 성능차이가 있는지에 대해 알아보고 싶으시다면, 인프런 CTO 이동욱님의 글을 참고해주세요.

 

이번 기회에 PostgreSQL의 버전 차이에 따라 얼마나 큰 성능차이를 낼 수 있는지에 대해 알 수 있었습니다. 

 

 

 

 

 

 

---

 

 

 

 

마치며

앞으로도 팀의 발전을 돕는 개발자가 되기 위해 노력하려 합니다. 팀에 필요한 부분이 무엇일지 고민하면서, 팀에 도움이 된다면, 열심히 공부해서 실무에 적용할 수 있는 개발자가 되기 위해 노력하고 싶습니다. 팀의 성장에 기여할 수 있는 개발자가 되겠습니다. 

 

 

 

 

 

 

---

 

 

 

 

 

 

 

 

참고 및 출처

PostgreSQL (Aurora) 10 vs 11 버전 성능 비교

PostgreSQL에서 Order By가 선적용되는 슬로우 쿼리 해결책