Database Design Mistakes I Made So You Don't Have To \u2014 TXT1.ai

3년 전, 나는 블랙 프라이데이에 우리 스타트업의 데이터베이스가 오전 2시 47분에 멈춰버리는 것을 목격했다. 우리는 50,000명의 동시 사용자, 200만 달러 규모의 보류 중인 거래, 그리고 45초가 걸리는 제품 가용성 쿼리를 가지고 있었다. 우리의 CTO는 슬랙에 소리쳤고, 투자자들은 전화하고 있었다. 나는 6개월 전에 설계했던 스키마를 바라보며 내가 한 모든 "영리한" 결정이 지금은 약 1분에 8,000달러의 손실 비용으로 이어지고 있음을 깨달았다.

나는 마커스 첸이고, 지난 12년간 데이터베이스 아키텍트로 일해왔으며, 열악한 SaaS 스타트업부터 포춘 500 대기업까지 다양한 고객과 협력해왔다. 나는 5억 건의 일일 거래를 처리하는 시스템의 스키마를 설계하고, 200ms를 줄인 쿼리를 최적화했으며, 예, 책에 나오는 모든 데이터베이스 설계 실수를 거의 다 저질렀다. 그 블랙 프라이데이 사건? 그것은 나에게 데이터베이스 설계에 대해 내 컴퓨터 과학 학위 전체보다 더 많은 것을 가르쳐주었다.

오늘 나는 TXT1.ai의 수석 데이터베이스 아키텍트로, 여기서는 AI 기반 커뮤니케이션 플랫폼을 통해 매년 30억 개 이상의 문자 메시지를 처리하고 있다. 하지만 나는 실패를 통해 이 자리에 올랐고, 나는 여러분이 오전 2시의 패닉 공격과 화난 투자자 전화를 건너뛰게 하도록 내가 배운 비싼 교훈을 공유하고 싶다.

"UUID를 어디에나 사용할 것" 재앙

내가 $40,000의 실수라고 부르는 것부터 시작하겠다. 2019년에, 나는 중간 규모의 전자 상거래 회사를 위한 고객 관계 관리 시스템을 설계하고 있었다. UUID가 기본 키를 처리하는 "현대" 방식이라는 블로그 포스트를 막 읽은 직후였다. 더 이상 자동 증가 정수도, 더 이상 순차 ID 노출도 없으며, 분산 시스템에 완벽했다. 나는 설득당했다.

그래서 나는 시스템의 모든 기본 키를 UUID로 만들었다. 사용자 테이블? UUID. 주문 테이블? UUID. 주문 라인 항목? 맞아요—UUID. 나는 천재가 된 기분이었다. 스키마는 깔끔해 보였고, 순차 ID의 취약점도 없었으며, 필요할 경우 클라이언트 측에서 ID를 생성할 수 있었다. 뭘 잘못할 수 있을까?

모든 것이 잘못되었다. 정말 모든 것이 잘못되었다.

6개월이 지나자, 우리의 데이터베이스 크기는 180GB 정도 되어야 할 때 340GB로 불어났다. 쿼리 성능은 매주 저하되고 있었고, 우리의 인덱스 크기는 어마어마했다—주문 테이블 인덱스만 해도 12GB였다. 50ms 걸려야 할 주문과 라인 항목 간의 조인도 800ms가 걸리고 있었다. 데이터베이스는 디스크 I/O에 막대한 양의 시간을 소모하고 있었으며, 우리의 AWS RDS 비용은 거의 두 배로 증가했다.

내가 힘들게 배운 것은 이렇다: UUID는 128비트(16바이트)로, 4바이트 정수 또는 8바이트 bigint와 비교될 때, 기본 키에 대해 4배의 저장공간을 차지한다. 하지만 진짜 문제는 저장공간이 아니라 인덱스 단편화다. UUID는 랜덤하다, 즉 모든 삽입이 B-tree 인덱스에서 랜덤 쓰기를 유발한다. 순차 정수의 경우, 새로운 행은 인덱스의 끝에 추가된다. UUID의 경우, 데이터베이스는 인덱스 구조를 지속적으로 재균형 잡아야 한다.

우리는 그 영향을 측정했다: 정수 ID로 100,000행을 삽입하는 데 8초가 걸렸다. UUID의 경우 동일한 작업에 34초가 걸렸다. 이는 기본 키 선택만으로 이루어진 4.25배의 성능 패널티다. 하루에 50,000건의 주문을 처리할 때, 이 수치는 빠르게 더해진다.

수정하는 데 3주간의 개발 시간이 소요되었고, 유지 관리 창 중에 세심하게 조정된 마이그레이션이 필요했다. 우리는 고용량 테이블에 대해 bigint 기본 키로 이동하고, 분산 시스템에서 전 세계적으로 고유한 식별자가 정말로 필요한 테이블에만 UUID를 유지하기로 했다—이는 47개 테이블 중 정확히 두 개로 드러났다.

내 새로운 규칙: 특정하고 문서화된 이유가 없다면 기본 키에 대해 자동 증분 정수 또는 bigint를 사용하라. "더 현대적으로 보인다"는 문서화된 이유가 아니다.

미숙한 정규화: 제3 정규형이 적이 될 때

대학을 갓 졸업한 나는 정규화에 집착했다. 나는 모든 정규형을 암기하고, 코드를 잊어버린 꿈에서도 코딩할 수 있었으며, 제대로 정규화된 데이터베이스가 디자인의 정점이라고 믿었다. 그래서 나는 첫 번째 프로덕션 시스템인 콘텐츠 관리 플랫폼을 설계할 때 모든 것을 제3 정규형 이상으로 정규화했다.

"오늘날 당신이 내리는 모든 '영리한' 데이터베이스 결정은 6개월 뒤 오전 2시에 발생할 잠재적인 위기입니다. 당신이 갖게 될 시스템을 위해 설계하세요, 당신이 원하는 시스템이 아니라."

나는 사용자 테이블, 사용자_주소 테이블(사용자는 여러 주소를 가질 수 있기 때문에), 사용자_전화번호 테이블(여러 전화기!), 사용자_환경설정 테이블, 사용자_설정 테이블, 그리고 사용자_메타데이터 테이블을 가지고 있었다. 단일 사용자의 프로필을 로드하는 데에는 여섯 개의 테이블을 조인해야 했다. 나는 모든 것이 얼마나 "깨끗한"지에 대해 매우 자랑스러웠다.

그런데 우리가 출시하자, 전체 애플리케이션에서 가장 많이 접근되는 페이지인 사용자 프로필 페이지 로딩에 1.2초가 걸리고 있었다. 페이지 뷰마다 여섯 번의 조인을 수행하고 있었고, 10,000명의 일일 활성 사용자로서는 하루에 단지 프로필 뷰를 위해 60,000번의 조인이 필요했다. 데이터베이스 CPU는 항상 70%를 초과하고 있었다.

전화가 오는 계기가 있었고, 우리의 수석 개발자가 나를 한쪽에 세워놓고 쿼리 실행 계획을 보여주었다. "마커스," 그가 말했다, "우리는 사용자의 이름, 이메일 및 전화번호를 표시하기 위해 여섯 개의 테이블을 조인하고 있어. 이건 미친 짓이다." 그는 옳았다. 나는 이론적인 순수함을 최적화하는 바람에 실질적인 성능을 간과하고 있었다.

우리는 전략적으로 비정규화했다. 사용자의 기본 주소는 사용자 테이블로 돌아갔고, 그들의 기본 전화번호? 마찬가지였다. 추가 주소 및 전화번호에 대한 별도의 테이블은 유지했지만, 우리의 사용자 중 94%는 각각 하나씩만을 가졌다. 그 단일 변경으로 평균 프로필 페이지 쿼리 시간이 1.2초에서 180ms로 감소하는 85%의 개선을 이루었다.

내가 배운 것은 이렇다: 정규화는 도구지 종교가 아니다. 제3 정규형은 좋은 출발점이지만, 실제 성능은 종종 전략적 비정규화를 요구한다. 이제 나는 "80/20 비정규화 규칙"을 따르는데, 만약 80%의 쿼리가 여러 테이블에서 데이터를 필요로 한다면, 그 데이터는 아마도 하나의 테이블에 있어야 한다. 나는 실제 사용에 따라 쿼리 패턴을 측정하고 이론적인 순수함이 아니라 실제 사용량에 따라 비정규화한다.

비정규화를 해야 할 때를 아는 것이 중요하다. 읽기 많고 쓰기 적은 테이블이 완벽한 후보다. 사용자 프로필, 제품 카탈로그, 구성 데이터—이 모든 것이 비정규화하기에 좋은 곳이다. 쓰기량이 많은 트랜잭션 테이블은? 업데이트 이상을 피하기 위해 그런 테이블은 정규화를 유지하라.

NULL 악몽: 선택적이 불가능해질 때

나는 예전에는 NULL 가능한 열을 좋아했다. 그들은 매우 유연하고, 배려가 많은 것처럼 보였다. 사용자가 중간 이름을 가지고 있지 않을 수도 있나? NULL로 만들어라. 주문이 할인 코드를 가지고 있지 않을 수도 있나? NULL. 제품이 무게를 가지고 있지 않을 수도 있나? 당신이 아는 대로다.

특히 재앙적인 프로젝트 중 하나에서, 나는 전체 테이블의 약 60%의 열이 NULL 가능하게 디자인한 재고 관리 시스템을 만들었다. 그건 합리적인 것처럼 보였다—모든 필드가 항상 데이터를 가질 필요는 없지 않나? NULL이 "값 없음"을 확실히 전달하는데 기본값을 강요할 필요가 있을까?

문제는 즉시 시작되었다. 쿼리는 NULL 검사의 지뢰밭이 되었다. 무게가 없는 모든 제품을 찾고 싶다고? "WHERE weight IS NULL"이 작동할 것이라고 생각하겠지만, 무게가 명시적으로 0으로 설정된 제품은? 이제는 "WHERE weight IS NULL OR weight = 0"이 필요하다. 주문 총계를 합산하고 싶다면? COALESCE를 사용하는 것이 좋을 것이다. 왜냐하면 개별 값이 NULL인 경우 합계가 NULL을 반환할 수 있기 때문이다.