마이그레이션만으로 재구축할 수 없는 DB 구하기

경험을 토대로 AI를 활용하여 작성한 글 입니다.

취업 준비 플래너 앱의 코드 감사에서 나온 지적 중 이번에 처리한 건 두 가지다. 하나는 "마이그레이션이 0001_quiz.sql부터 시작한다"는 것, 다른 하나는 "PK/UNIQUE 외 인덱스가 하나도 없다"는 것.

첫 번째가 왜 문제냐면, 이 프로젝트의 핵심 테이블들(profiles, tasks, ddays, job_postings)은 마이그레이션이 아니라 Supabase SQL Editor에 schema.sql을 붙여넣어서 만들어졌기 때문이다. 마이그레이션 히스토리는 그 위에 나중에 얹은 quiz 기능(0001)부터 시작한다. 지금 DB는 잘 돌아간다. 하지만 스테이징 환경을 하나 만들려고 하면? supabase db push로는 절반짜리 스키마만 생긴다. 나머지는 "그때 그 SQL을 순서대로 잘 붙여넣는" 수동 의식에 의존한다. 재현 가능한 배포가 아니다.

백필의 딜레마: 이미 적용된 마이그레이션을 어떻게 추가하나

해법 자체는 알려져 있다. 히스토리 맨 앞에 베이스 마이그레이션(0000_base_schema.sql)을 만들어 넣으면 된다. 문제는 운영 DB에는 그 내용이 이미 적용돼 있다는 것. 그냥 push하면 CREATE TABLE public.profiles 가 "already exists"로 터진다.

Supabase CLI에는 이 상황을 위한 명령이 있다.

supabase migration repair --status applied 0000

이건 SQL을 실행하지 않고 원격의 마이그레이션 히스토리 테이블에 "0000은 적용됨"이라는 기록만 남긴다. 그러면:

기존(운영) DB: 0000은 히스토리에만 존재, 실행되지 않음
신규 환경: 히스토리가 없으니 0000부터 순서대로 전부 실행됨

"적용된 상태"와 "적용됐다는 기록"이 별개라는 걸 이용하는 건데, 뒤집어 말하면 기록이 실제 상태와 일치한다는 보장은 전적으로 내 책임이 된다. 0000의 내용이 운영 DB의 실제 상태와 다르면, 신규 환경과 운영 환경은 조용히 달라진다.

Docker가 없어서 생긴 우회로, 그리고 뜻밖의 발견

그래서 0000을 쓰기 전에 원격 DB의 실제 상태를 확인하려 했는데, 여기서 막혔다. supabase db dump도 db diff도 셰도 DB를 만들기 위해 Docker를 요구하는데, 작업 환경(WSL2)에 Docker가 없었다.

우회로는 Supabase Management API였다. 대시보드 SQL Editor가 쓰는 것과 같은 엔드포인트로 임의 쿼리를 날릴 수 있다.

curl -X POST "https://api.supabase.com/v1/projects/<ref>/database/query" \
  -H "Authorization: Bearer $(cat ~/.supabase/access-token)" \
  -d '{"query": "select tablename, indexname from pg_indexes where schemaname = $$public$$"}'

이걸로 information_schema.columns, pg_indexes, pg_policies, pg_get_viewdef()를 차례로 훑어서 schema.sql과 전부 대조했다. 컬럼, 뷰 정의, RLS 정책 개수, 함수까지 일치 — 백필해도 안전하다는 확신이 생겼다.

그런데 테이블 목록에서 낯선 이름이 하나 나왔다. template_applications. schema.sql에도, 어떤 마이그레이션에도 없는 테이블이다. 지금 코드가 쓰는 건 task_template_applications고, 과거 마이그레이션(0004)이 "잘못된 스키마의 기존 테이블"을 DROP하고 재생성한 이력이 있는 걸 보면, 그 이전 실험의 잔재가 이름이 달라서 DROP을 피해 살아남은 것이다. 행 2개를 품고 조용히 2년째 있었다.

수동으로 관리되던 DB는 이런 게 무섭다. 문서(schema.sql)를 아무리 봐도 존재를 알 수 없는 객체가 실제 DB에는 있다. 백필은 schema.sql을 복사하는 작업이 아니라 실제 DB를 검증하는 작업이라는 걸 이 테이블 하나가 증명해줬다. (삭제는 파괴적 작업이라 이번 PR에서는 손대지 않고 보고만 했다.)

백필 마이그레이션은 "현재"가 아니라 "그 시점"을 담는다

0000을 쓰면서 한 가지 원칙을 지켰다. 0000은 0001 직전 시점의 스키마여야 한다는 것. 현재의 schema.sql을 그대로 복사하면 안 된다.

profiles.day_start_time 컬럼은 0000에서 뺐다 — 0004가 ADD COLUMN으로 추가하기 때문
기록 뷰는 security_invoker 없이 만들었다 — 0006이 ALTER VIEW로 켜기 때문

이래야 0000→0008을 처음부터 재생했을 때 각 마이그레이션이 실제 히스토리 그대로 의미 있게 실행된다. 현재 상태를 0000에 다 넣어버리면 0004와 0006은 하는 일 없는 빈 껍데기가 되거나, 최악에는 충돌한다.

인덱스: 추측이 아니라 쿼리에서 역산

두 번째 문제로 넘어가서. 인덱스가 PK/UNIQUE뿐이라는 건 Management API로 재확인했고, 어떤 인덱스를 만들지는 코드에 있는 쿼리를 전수 조사해서 정했다. API 라우트와 페이지의 .eq(), .gte(), .order()를 전부 grep해 테이블별 접근 패턴을 뽑았다.

여기서 중요한 전제가 하나 있다. 이 앱은 모든 테이블에 RLS가 걸려 있어서 모든 쿼리에 user_id = auth.uid() 필터가 암묵적으로 붙는다. 코드에 안 보여도 실행 계획에는 있다. 그래서 복합 인덱스는 전부 user_id를 선두에 놨다.

-- 일간/주간(=), 월간(범위) 조회를 하나로 커버
CREATE INDEX tasks_user_scope_target_date_idx
  ON public.tasks (user_id, scope, target_date);
 
-- 완료 기록 조회 — 뷰의 WHERE 조건과 일치하는 부분 인덱스
CREATE INDEX tasks_user_completed_at_idx
  ON public.tasks (user_id, completed_at DESC)
  WHERE is_completed = TRUE;

두 번째 것처럼 조회 조건이 항상 is_completed = TRUE로 고정된 곳에는 부분 인덱스를 썼다. 미완료 행은 인덱스에 아예 들어가지 않으니 크기도 작고, 완료 기록 화면의 ORDER BY completed_at DESC까지 인덱스가 흡수한다.

반대로 "있으면 좋을 것 같은" 인덱스는 참았다. goals는 UNIQUE(user_id)가 이미 있고, quiz_histories는 UNIQUE(user_id, question_id)의 선두 컬럼이 조회를 커버한다. 인덱스는 공짜가 아니라(쓰기마다 비용) 쿼리가 증명하는 것만 넣는 게 맞다.

적용 후 EXPLAIN으로 실측했다.

Index Scan using tasks_user_scope_target_date_idx on tasks
  Index Cond: ((user_id = '...') AND (scope = 'daily') AND (target_date = '2026-07-13'))

세 조건이 전부 Index Cond로 흡수됐다. 지금은 데이터가 적어 체감 차이가 없지만, RLS 필터가 풀스캔으로 도는 미래를 하나 지웠다.

마무리

이번 작업의 교훈은 두 줄로 요약된다.

하나, 마이그레이션 히스토리는 DB의 재현 가능성 그 자체다. "지금 잘 돌아가는 DB"와 "다시 만들 수 있는 DB"는 다른 것이고, 그 간극은 평소에는 안 보이다가 스테이징이 필요한 날, 장애 복구가 필요한 날 청구서로 돌아온다.

둘, 수동 관리의 흔적은 문서가 아니라 DB에서 찾아야 한다. schema.sql은 "그랬어야 하는 상태"고, 진실은 pg_indexes와 information_schema에 있다. 고아 테이블 하나가 그 차이를 보여줬다.