SQL의 CTE, 잘 쓰면 약이고 못 쓰면 독이에요

들어가며

SQL을 쓰다 보면 한 번쯤은 CTE라는 걸 만나게 돼요. CTE는 Common Table Expression의 약자인데요, 쉽게 말하면 쿼리 안에서 임시로 이름을 붙여 쓸 수 있는 서브쿼리예요. WITH 키워드로 시작하는 그 문법이 바로 CTE거든요. 복잡한 쿼리를 읽기 좋게 쪼개주니까 한번 맛을 들이면 자꾸 쓰게 되는데, 문제는 CTE를 "언제 쓰면 좋고, 언제 쓰면 안 되는지"를 제대로 구분하지 않으면 오히려 성능을 망칠 수 있다는 거예요.

좋은 CTE는 이런 거예요

가독성을 높이는 CTE가 가장 좋은 활용법이에요. 서브쿼리가 3~4단계로 중첩되면 나중에 코드를 읽는 사람(내일의 나 포함)이 정말 괴로워지거든요. 이럴 때 CTE로 각 단계에 의미 있는 이름을 붙이면 마치 코드를 변수에 담아 쓰는 것처럼 깔끔해져요.

예를 들어 "지난 30일간 활성 사용자 중 결제한 사람의 평균 결제 금액"을 구한다고 해볼게요. 서브쿼리로 쓰면 안쪽부터 바깥쪽으로 읽어야 해서 머리가 아픈데, CTE로 쓰면 active_users → paying_users → avg_payment 이런 식으로 위에서 아래로 자연스럽게 읽을 수 있어요.

재사용이 필요한 경우도 좋은 CTE예요. 같은 서브쿼리를 쿼리 안에서 두 번 이상 참조해야 할 때, 서브쿼리를 복붙하면 유지보수 지옥이 되거든요. CTE에 한 번 정의하고 여러 번 참조하면 코드 중복도 줄고 의도도 명확해져요.

재귀 CTE도 아주 유용한 케이스예요. 조직도처럼 트리 구조 데이터를 탐색하거나, 날짜 시리즈를 생성할 때 재귀 CTE가 없으면 애플리케이션 코드에서 반복문을 돌려야 하는데, 재귀 CTE 한 방이면 DB에서 바로 처리할 수 있거든요.

나쁜 CTE는 이런 거예요

가장 흔한 실수는 CTE를 쓰면 결과가 캐싱될 거라고 착각하는 거예요. 많은 분들이 CTE에 한 번 정의하면 그 결과가 임시 테이블처럼 저장되어서 여러 번 참조해도 한 번만 실행될 거라고 생각하는데요, 데이터베이스 엔진에 따라 다르지만 대부분의 경우 CTE는 참조할 때마다 다시 실행돼요. PostgreSQL은 12버전부터 옵티마이저가 CTE를 인라인(펼치기) 할 수 있게 됐고, MySQL은 원래부터 인라인 처리를 하거든요.

이게 뭐냐면, CTE를 쓴다고 해서 자동으로 성능이 좋아지는 건 아니라는 뜻이에요. 오히려 옵티마이저가 CTE 경계를 넘어서 최적화하지 못하는 경우, 직접 서브쿼리나 조인으로 작성한 것보다 느려질 수도 있어요.

불필요하게 CTE를 남발하는 것도 안 좋은 패턴이에요. 단순한 조인 하나면 될 걸 CTE 3개로 쪼개놓으면 오히려 읽기가 더 어려워져요. CTE는 복잡성을 관리하는 도구이지, 모든 쿼리에 적용해야 하는 스타일 가이드가 아니거든요. 간단한 쿼리에는 그냥 간단하게 쓰는 게 최고예요.

또 하나, CTE 안에서 무거운 연산을 넣어놓고 여러 번 참조하는 패턴은 정말 위험해요. 위에서 말했듯 매번 재실행될 수 있으니까요. 이런 경우에는 차라리 임시 테이블(TEMPORARY TABLE)을 명시적으로 만들어서 결과를 저장하는 게 훨씬 나아요.

데이터베이스별 차이를 알아두세요

CTE 동작 방식이 DB마다 꽤 달라요. PostgreSQL은 11버전까지 CTE를 무조건 "optimization fence"로 취급했어요. 이게 뭐냐면 CTE 안쪽과 바깥쪽을 따로 최적화한다는 뜻인데, 12버전부터는 MATERIALIZED / NOT MATERIALIZED 키워드로 직접 제어할 수 있게 됐어요. MySQL 8.0은 CTE를 지원하기 시작했지만, 비재귀 CTE는 항상 인라인으로 처리해요. SQL Server는 CTE를 뷰처럼 취급해서 옵티마이저가 자유롭게 쿼리를 재작성하거든요.

실무에서는 자기가 쓰는 DB의 CTE 처리 방식을 EXPLAIN이나 EXPLAIN ANALYZE로 꼭 확인해보는 게 중요해요. 같은 쿼리라도 DB에 따라 실행 계획이 완전히 달라질 수 있거든요.

한국 개발자에게 주는 시사점

국내 프로젝트에서 가장 많이 쓰는 MySQL과 PostgreSQL 모두 CTE를 지원하니까, 적극적으로 활용해볼 만해요. 다만 "CTE가 성능을 올려주겠지"라는 막연한 기대보다는, 가독성과 유지보수성을 높이는 도구로 접근하는 게 맞아요. 성능 최적화가 목적이라면 반드시 실행 계획을 확인하고, 필요하면 임시 테이블이나 서브쿼리로 전환하는 유연함도 갖추는 게 좋아요.

특히 레거시 프로젝트에서 복잡한 리포트 쿼리를 유지보수할 때, CTE로 리팩토링하면 팀원들의 코드 리뷰 부담을 크게 줄일 수 있어요. 다만 리팩토링 전후로 실행 계획이 달라지지 않았는지는 꼭 체크하세요.

마무리

CTE는 "읽기 좋은 쿼리"를 만드는 도구이지, 마법의 성능 부스터가 아니에요. 좋은 CTE는 쿼리의 의도를 명확하게 드러내주고, 나쁜 CTE는 불필요한 복잡성과 성능 저하를 가져와요. 여러분은 평소 CTE를 어떤 기준으로 쓰고 계신가요? 혹시 CTE 때문에 성능 이슈를 겪은 경험이 있다면 공유해주세요!