실제 Postgres 스키마에서의 정규화 대 비정규화: 제가 수정한 것과 유지한 복사본

만약 제품을 소유하고 있다면, 그 밑바닥까지 알아야 합니다. 많은 제품에서 데이터베이스가 핵심 부분입니다. 모든 고객, 모든 메시지, 모든 결정이 실제로 존재하는 곳이니까요. 화면은 매달 바뀌지만, 데이터는 그대로 있습니다. 따라서 잘 구조화되어 있어야 하며, 위에 놓인 앱에 의해 사용 가능해야 합니다.

구조적인 측면에는 오래되고 잘 알려진 답이 있습니다: 정규형(normal forms), 그리고 무엇보다도 3NF입니다. 하지만 사용 가능한 측면에서는 교과서만으로는 충분하지 않습니다. 서버리스 모바일 앱은 휴대폰에서 데이터베이스에 직접 연결하며, 접근 규칙, 지연 시간(latency), 그리고 그 사이에 백엔드가 없다는 사실 모두가 교과서적인 방식을 무너뜨립니다. 의도적으로 몇 가지 차이를 만들어야 합니다.

제 앱인 Hearback은 제 게시물 댓글에 대한 답장을 초안합니다. 이 앱은 약 25개의 테이블을 가진 Postgres(Supabase)에서 실행됩니다. 저는 AI 코딩 에이전트를 사용해 사흘 만에 구축했습니다: 17번의 마이그레이션, 하나의 기능씩 순차적으로 추가했죠. 모든 기능은 작동했고, 테스트도 통과했습니다.

그러고 나서 기본으로 돌아가서 전체 스키마를 정규형과 비교 검토했습니다. 저는 블로그 요약을 사용하지 않았습니다. 정의한 논문들을 사용했습니다.

일반적인 에이전트 빌드가 제공하는 것

특별히 요청하지 않은 상태에서 에이전트를 이용해 만든 평범한 빌드는 3NF 데이터베이스를 생성하지 못합니다. 이 에이전트는 각 기능만 작동하게 만듭니다. 어떤 값이 행(row)에 있으면 유용하다고 판단하고 그곳에 넣으며, 스키마는 합리적인 단계마다 복사본을 늘려나갑니다.

제 기존 설정에서 감사 결과 발견된 것은 다음과 같습니다:

좋은 소식은, 라이브 데이터 상에서는 그중 어느 것도 아직 잘못된 적이 없었다는 것입니다. 아무것도 고장 나지 않았습니다. 단지 나중에 조용히 망가질 수 있는 22가지 방법만 있었고, 어떤 테스트도 그것을 잡아내지 못했을 겁니다.

또 다른 좋은 소식은: 제가 명시적으로 요청하자 같은 에이전트가 원본 자료를 가지고 감사까지 잘 수행했다는 것입니다. 데이터베이스 구조는 요청해야 하는 것입니다.

대부분의 중복은 사라졌습니다. 저는 의도적으로 한 복사본을 남겨두었습니다. 이 글은 두 가지에 관한 것입니다. 즉, 정규화(normalization)가 실제로 무엇을 제공하는지, 그리고 그것을 잃지 않으면서 어떻게 고의로 깨뜨릴 수 있는지입니다.

한 줄 요약된 이론

William Kent의 A Simple Guide to Five Normal Forms (1983)가 여전히 이 주제에 대한 가장 명확한 자료입니다. 그의 요약은 다음과 같습니다:

모든 비키(non-key) 필드는 키, 전체 키, 그리고 오직 키에 관한 사실만을 제공해야 한다.

• 1NF: 필드당 하나의 값. 검색할 수 있는 리스트가 없어야 합니다.
• 2NF:

수정 사항: 컬럼을 제거하고 아이템을 통해 사람 정보를 읽도록 변경했습니다. 카운트 값은 이제 트igger로 이동했으며, 아이템이 양도될 때마다 재계산됩니다.

3NF: 자식들이 부모를 반복하는 경우

검색용 텍스트 청크는 문서의 kind, label 및 URL을 반복 저장하고 있었습니다. 문서의 모든 청크가 일치해야 했지만, 이를 강제하는 것은 아무것도 없었습니다. 저는 해당 컬럼들을 제거했습니다. 검색 함수는 이제 documents를 조인하며 이전과 정확히 동일한 형태의 결과를 반환합니다.

수동으로 작성된 파생 값

제가 가장 많은 것을 발견한 곳입니다. 각 케이스는 두 가지 수정 사항 중 하나를 적용받았습니다:

• 같은 행에 대한 함수 → 생성 컬럼(generated column). is_public은 단순히 label = 'quotable'을 두 번 저장하는 것이었습니다. 토큰 총합계는 옆에 있는 JSON 분해 값들의 합산이었습니다.

alter table drafts add column tokens_in int generated always as (
    (tokens->>'input')::int
    + coalesce((tokens->>'cache_read')::int, 0)
...

Postgres가 이를 계산하며, 아무도 잘못 쓸 수 없습니다. (값을 설정하려는 insert 문이 실패하므로, 먼저 작성자들을 업데이트하세요.)

• 다른 행들에 대한 집계 → 유일한 작성자인 트igger.

이것은 서버리스 모바일 부분입니다. 전화기와 데이터베이스 사이에 제가 만든 백엔드는 없습니다.

보안(Security). Supabase에서는 앱이 Postgres에 직접 연결하며, 행 레벨 보안(row-level security)이 각 사용자가 어떤 행을 볼지 결정합니다. 해당 행 자체를 확인하는 정책은 간단하고 저렴합니다:

create policy members_all on items for all
  using (is_member(project_id));

만약 이 컬럼이 없다면, 모든 정책은 부모 테이블을 통해 조인해야 하며, 모든 쿼리의 모든 행에 대해 그렇게 해야 합니다.

성능(Performance). 받은 편지함(inbox)과 벡터 검색 모두 먼저 프로젝트별로 필터링하며, 해당 컬럼에 인덱스를 사용합니다.

따라서 복사본은 유지됩니다. 잘못된 것은 복사본 자체가 아니라, 그것이 거짓말하는 것을 막는 장치가 없었다는 점입니다. 하나의 항목(item)이 프로젝트 A를 주장할 수 있지만 실제 작성자는 프로젝트 B에 살고 있을 수 있고, 정책은 그 주장을 신뢰합니다.

복사본을 유지하고 데이터베이스가 이를 보호하게 만들기

해결책은 합성 외래 키(composite foreign key)입니다. 부모 테이블이 쌍(pair)을 노출합니다:

alter table people add constraint people_id_project unique (id, project_id);

그리고 자식 테이블은 ID 하나만 참조하는 대신 이 쌍을 참조합니다:

alter table items
  drop constraint items_person_id_fkey,
  add constraint items_person_fkey
...

이제 items.project_id는 사람의 실제 프로젝트만을 가질 수 있습니다. RLS는 여전히 로컬 컬럼 하나를 확인하며, 그 컬럼은 이제 정확하다는 것이 보장됩니다.

같은 트릭은 테넌시(tenancy)보다 더 많은 것을 확인합니다:

• 항목의 계정은 해당 항목의 플랫폼에 있어야 합니다: (account_id, project_id, platform).
• 항목에 대한 언급(touch)은 해당 항목의 작성자와 함께 있어야 합니다: (item_id, project_id, person_id), 그리고 항목이 다른 사람에게 이동할 경우 따라가도록 on update cascade를 사용합니다.
• 삭제 시 null로 설정되어야 하는 Nullable 참조의 경우, Postgres 15+는 하나의 컬럼만 null로 설정하도록 허용합니다: on delete set null (theme_id). 그렇지 않으면 테넌트 컬럼까지 지워지게 됩니다.

제가 그대로 두고 그 이유를 기록한 것들

모든 중복 데이터가 버그인 것은 아닙니다. 저는 이들을 유지했고, 마이그레이션(migration)의 주석으로 그 이유를 작성했습니다:

• 스냅샷(Snapshots). 가져온 시점의 스레드는 복사본이 아니라 그 순간에 대한 사실입니다.
• 다른 컬럼들로부터 도출될 수 없는 링크를 때때로 담는 URL 컬럼.
• 삽입 전에 JavaScript에서 계산된 해시값(hash). Postgres의 생성된 컬럼은 공백을 다르게 정규화할 수 있습니다.

만약 복사본이 존재하는 이유를 설명할 수 없다면, 그것을 제거해야 한다는 신호입니다.

API 레이어를 잊지 마세요

복합 외래 키(Composite foreign keys)는 관계를 추가하며, 외래 키로부터 조인(join)을 추론하는 도구들은 혼란스러워할 수 있습니다. PostgREST (Supabase의 REST 레이어)는 외래 키에서 조인을 가져옵니다. 변경 후 plans와 plan_models 사이에 두 개의 관계가 있었습니다: 계획에 속한 모델들, 그리고 계획의 기본(default) 모델입니다. 이로 인해 일반적인 임베디드 선택(embedded select)이 모호해졌고, 저는 쿼리에서 제약 조건 이름을 지정했습니다:

plans?select=id,default_model,plan_models!plan_models_plan_fkey(model)

따라서 마이그레이션 후에는 앱과 함수가 사용하는 모든 임베디드 쿼리를 테스트해야 하며, SQL 테스트만으로는 충분하지 않습니다. 저는 로컬 Supabase (supabase start)에서 이 작업을 수행했고, 의도적으로 잘못된 행을 삽입하여 거부되는지 확인한 다음, 마이그레이션과 서버 코드를 함께 배포하고 엔드투엔드(end-to-end) 스위트를 실행했습니다.

균형점

정규화는 순수성 테스트가 아닙니다. 그것은 소유자인 여러분이 제품의 모든 사실(fact)이 어디에 존재하는지 아는 방법입니다. 앱의 실제 제약 조건들이 어디서 유연성을 발휘할지 결정합니다. 제가 최종적으로 확립한 작업 규칙은 다음과 같습니다:

1. 원래 정의를 사용하여 1NF, 2NF, 그리고 3NF에 대해 감사(Audit)를 수행합니다. 만약 에이전트가 여러분의 앱을 구축한다면, 명시적으로 이 감사를 요청해야 합니다. 스스로는 일어나지 않을 것입니다.
2. 각 중복 데이터에 대해 다음 중 하나를 선택합니다: 제거(remove), 계산(compute) (생성된 컬럼), 캐싱(cache) (유일한 작성자로서의 트리거), 또는 유지 및 강제 적용(keep and enforce) (복합 외래 키).
3. 어떤 제약 조건을 추가하기 전에 라이브 데이터에서 위반 횟수를 계산합니다.
4. 유지하는 모든 복사본 옆에 그 이유를 작성합니다.

기초를 기억하고, 그런 다음 의도적으로 깨뜨리되, 데이터베이스가 기준을 지키도록 하세요.

단계별 버전과 자체 스키마에서 실행할 수 있는 읽기 전용 검사까지 포함합니다:

• 데이터베이스 정규화 예시: 실제 Postgres 스키마에서의 1NF, 2NF 및 3NF 문제
• 정규화 대 비정규화: 의도적으로 유지한 복사본과 Postgres가 이를 보호하는 방법

혹시 의도적으로 어떤 데이터를 보존해 두셨나요? 그것이 가치가 있다고 느낀 이유와, 그 데이터가 신뢰를 유지하게 하는 요소에 대해 듣고 싶습니다.