dev.syw

Supabase의 심장인 Postgres의 내부 구조를 이해하고, 정규화·관계·고급 타입으로 견고한 스키마를 설계한다.

Postgres 깊이 파고들기와 고급 데이터 모델링

입문편에서 테이블을 생성하고 기본 CRUD를 익혔다면, 이제는 Supabase가 어떤 구조 위에서 동작하는지 내부를 들여다볼 차례입니다. Supabase는 결국 Postgres를 감싼 플랫폼이기 때문에, Postgres가 데이터를 어떻게 조직하는지 이해하면 쿼리 성능, 보안 정책, API 동작 방식 모두가 명확해집니다.

이 레슨에서는 스키마 구조부터 시작해 고급 컬럼 타입, 제약과 트리거, plpgsql 함수, 마이그레이션 전략, 그리고 뷰와 Materialized View까지 실무에서 반드시 필요한 설계 기법을 다룹니다. 코드 예제는 모두 Supabase SQL 에디터 또는 psql에서 바로 실행할 수 있습니다.

학습 목표

  • Supabase 프로젝트의 스키마 구조(public, auth, storage)와 각 역할을 설명할 수 있다.
  • 외래키, 다대다 조인 테이블, 참조 무결성 옵션(ON DELETE, ON UPDATE)을 올바르게 설계할 수 있다.
  • jsonb, enum, array, generated column, 도메인 타입 등 고급 컬럼 타입을 목적에 맞게 선택할 수 있다.
  • CHECK 제약, 트리거, plpgsql 함수로 비즈니스 규칙을 데이터베이스 레벨에서 강제할 수 있다.
  • 마이그레이션 기반 스키마 관리 흐름과 뷰·Materialized View의 활용법을 이해한다.

Supabase의 스키마 구조 이해하기

Postgres에서 스키마(schema)는 테이블·함수·타입 등의 네임스페이스입니다. Supabase 프로젝트를 생성하면 기본적으로 세 개의 중요한 스키마가 존재합니다.

스키마역할직접 수정 여부
public애플리케이션 테이블과 함수자유롭게 수정 가능
auth사용자 계정, 세션, OTP 등 인증 데이터Supabase 관리 — 직접 수정 금지
storage오브젝트 버킷 메타데이터Supabase 관리 — 직접 수정 금지

PostgREST는 기본적으로 public 스키마만 API로 노출합니다. auth.users 테이블을 애플리케이션에서 참조해야 할 때는 직접 조인하는 대신 public 스키마에 별도 프로필 테이블을 만들어 외래키로 연결하는 패턴이 표준입니다.

-- ✅ 올바른 패턴: auth.users를 직접 수정하지 않고 프로필 테이블로 확장
CREATE TABLE public.profiles (
  id          uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  display_name text NOT NULL,
  avatar_url  text,
  updated_at  timestamptz DEFAULT now()
);

-- auth.users 행이 삭제되면 profiles도 자동 삭제(CASCADE)

⚠️ 주의 auth.users에 컬럼을 직접 추가하거나 트리거를 잘못 달면 Supabase 인증 흐름이 깨질 수 있습니다. 항상 public 스키마에 보조 테이블을 두는 방식을 사용하세요.

스키마를 추가로 만드는 경우

멀티테넌트 앱이나 내부 관리 API를 공개 API와 분리하고 싶을 때 스키마를 추가로 생성합니다. Supabase 대시보드의 API Settings > Exposed schemas에서 원하는 스키마를 PostgREST에 노출할 수 있습니다.

CREATE SCHEMA internal;

-- internal 스키마의 테이블은 기본적으로 API에 노출되지 않음
CREATE TABLE internal.audit_log (
  id         bigserial PRIMARY KEY,
  actor_id   uuid,
  action     text,
  created_at timestamptz DEFAULT now()
);

관계 모델링 심화: 외래키와 참조 무결성

입문편에서 기본 외래키를 만들었다면, 이제는 ON DELETE / ON UPDATE 동작 옵션이 실제 서비스에서 어떤 의미를 갖는지 이해해야 합니다.

-- 게시글 ↔ 작성자 (1:N)
CREATE TABLE public.posts (
  id         bigserial PRIMARY KEY,
  author_id  uuid NOT NULL REFERENCES public.profiles(id)
               ON DELETE CASCADE    -- 프로필 삭제 시 게시글도 삭제
               ON UPDATE CASCADE,   -- 프로필 id 변경 시 자동 반영(실제로는 uuid라 거의 불필요)
  title      text NOT NULL,
  body       text,
  created_at timestamptz DEFAULT now()
);
옵션동작
CASCADE부모 행이 삭제/변경되면 자식 행도 함께 삭제/변경
SET NULL자식의 외래키 컬럼을 NULL로 설정
SET DEFAULT자식의 외래키 컬럼을 기본값으로 설정
RESTRICT자식이 존재하는 한 부모 삭제 불가
NO ACTION참조 무결성 위반 시 오류 발생(기본값). RESTRICT와 유사하나, 제약이 DEFERRABLE로 선언된 경우 검사를 트랜잭션 커밋 시점까지 미룰 수 있다는 점이 다르다

다대다 조인 테이블

태그, 권한, 팔로우 같은 다대다 관계는 조인 테이블(교차 테이블)로 표현합니다. 조인 테이블에는 복합 기본키 또는 별도 surrogate key를 사용할 수 있습니다.

CREATE TABLE public.tags (
  id   bigserial PRIMARY KEY,
  name text UNIQUE NOT NULL
);

-- posts ↔ tags 다대다
CREATE TABLE public.post_tags (
  post_id bigint REFERENCES public.posts(id) ON DELETE CASCADE,
  tag_id  bigint REFERENCES public.tags(id)  ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)  -- 복합 기본키로 중복 방지
);

💡 TIP 조인 테이블에 created_at이나 added_by 같은 메타데이터가 필요하다면 surrogate key(id bigserial PRIMARY KEY)를 추가하고 복합 UNIQUE 제약을 따로 거는 방식을 선택하세요.

고급 컬럼 타입 활용

jsonb — 반정형 데이터

json은 저장 시 원본 텍스트를 그대로 보존하지만, jsonb는 파싱된 이진 형태로 저장하여 인덱싱·연산자 지원이 훨씬 강력합니다. 구조가 자주 바뀌는 설정값이나 외부 API 응답을 저장할 때 적합합니다.

ALTER TABLE public.profiles
  ADD COLUMN settings jsonb DEFAULT '{}'::jsonb;

-- jsonb 경로 연산자
SELECT id, settings->>'theme' AS theme
FROM public.profiles
WHERE settings @> '{"notifications": true}'::jsonb;

-- GIN 인덱스로 jsonb 포함/키 연산 가속 (@>, ?, ?|, ?&)
CREATE INDEX idx_profiles_settings ON public.profiles USING GIN (settings);

⚠️ 주의 기본 GIN(jsonb_ops) 인덱스는 위 settings @> '{"notifications": true}'처럼 @> 포함 연산자나 ?/?|/?& 키 존재 연산자만 가속합니다. settings->>'theme' = '...'처럼 경로 추출 후 단순 비교하는 쿼리는 이 인덱스를 타지 못합니다. 포함 검색만 필요하다면 인덱스 크기가 더 작은 USING GIN (settings jsonb_path_ops)를, 특정 키 비교를 자주 한다면 CREATE INDEX ... ((settings->>'theme')) 같은 표현식 인덱스를 고려하세요.

⚠️ 주의 jsonb 안의 모든 값을 자주 검색한다면 정규화를 먼저 고려하세요. jsonb는 스키마가 정말 불확정적일 때만 사용하는 것이 원칙입니다.

enum — 유효 값 집합 강제

-- 타입 정의(DB 레벨 enum)
CREATE TYPE public.post_status AS ENUM ('draft', 'published', 'archived');

ALTER TABLE public.posts
  ADD COLUMN status public.post_status NOT NULL DEFAULT 'draft';

-- 나중에 값 추가(순서 지정 가능)
ALTER TYPE public.post_status ADD VALUE 'scheduled' BEFORE 'published';

⚠️ 주의 enum 값은 추가는 쉽지만 삭제나 이름 변경은 복잡합니다. 값이 자주 바뀔 것 같다면 text + CHECK 제약을 대신 쓰는 것을 고려하세요.

array — 단순 목록

소수의 스칼라 값을 리스트로 저장할 때 조인 테이블 대신 배열을 쓰면 쿼리가 단순해집니다. 단, 배열 원소로 JOIN하거나 원소별 집계가 필요하다면 정규화가 더 낫습니다.

ALTER TABLE public.posts
  ADD COLUMN media_urls text[] DEFAULT ARRAY[]::text[];

-- 원소 포함 여부 검색
SELECT * FROM public.posts
WHERE 'https://example.com/img.png' = ANY(media_urls);

-- GIN 인덱스로 배열 검색 가속
CREATE INDEX idx_posts_media ON public.posts USING GIN (media_urls);

generated column — 계산 컬럼

다른 컬럼에서 자동으로 계산되는 컬럼입니다. 항상 최신 값을 유지하며, 직접 INSERT/UPDATE할 수 없습니다.

ALTER TABLE public.posts
  ADD COLUMN body_length int GENERATED ALWAYS AS (char_length(body)) STORED;

-- 전문 검색(tsvector) 컬럼으로도 활용 가능
ALTER TABLE public.posts
  ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
      to_tsvector('simple', coalesce(title, '') || ' ' || coalesce(body, ''))
    ) STORED;

CREATE INDEX idx_posts_search ON public.posts USING GIN (search_vector);

⚠️ 주의 Postgres 표준 인스턴스에는 'korean'이라는 텍스트 검색 설정(text search configuration)이 기본 제공되지 않으므로 to_tsvector('korean', ...)는 text search configuration "korean" does not exist 오류로 실패합니다. 위 예제는 어간 추출 없이 토큰을 소문자로만 변환하는 'simple' 설정을 사용했습니다. 한국어는 형태소 분석이 필요하므로, 프로덕션에서는 pgroonga 같은 확장을 설치하고 별도 설정을 만들어 사용하세요(이 레슨 마지막 절 참고).

도메인 타입 — 재사용 가능한 제약 묶음

CREATE DOMAIN으로 기존 타입에 이름과 제약을 붙여 재사용할 수 있습니다.

CREATE DOMAIN public.email_address AS text
  CHECK (VALUE ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$');

CREATE TABLE public.contact_requests (
  id    bigserial PRIMARY KEY,
  email public.email_address NOT NULL
);

제약(Constraint)과 트리거

CHECK 제약

비즈니스 규칙을 컬럼 단위 또는 테이블 단위로 강제합니다. 애플리케이션 코드보다 신뢰성이 높습니다.

ALTER TABLE public.posts
  ADD CONSTRAINT chk_title_length CHECK (char_length(title) BETWEEN 1 AND 200),
  ADD CONSTRAINT chk_published_requires_body
    CHECK (status <> 'published' OR (body IS NOT NULL AND char_length(body) > 0));

트리거 — 자동화된 부수 효과

트리거는 INSERT, UPDATE, DELETE 전후에 자동으로 함수를 실행합니다. updated_at 자동 갱신이 가장 흔한 사용 사례입니다.

-- 트리거 함수 정의
CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

-- posts 테이블에 트리거 연결
CREATE TRIGGER trg_posts_updated_at
  BEFORE UPDATE ON public.posts
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

트리거로 다른 테이블에 연쇄 작업을 수행할 수도 있습니다. 예를 들어, 사용자가 처음 가입할 때 auth.users에 행이 생성되면 public.profiles에 자동으로 프로필을 만드는 패턴은 Supabase에서 매우 흔합니다.

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = public AS $$
BEGIN
  INSERT INTO public.profiles (id, display_name)
  VALUES (NEW.id, COALESCE(NEW.raw_user_meta_data->>'full_name', 'Anonymous'));
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

⚠️ 주의 auth 스키마의 트리거는 인증 흐름 중에 실행됩니다. 함수 내부에서 오류가 발생하면 회원가입 자체가 실패할 수 있으므로, 예외 처리(EXCEPTION 블록)를 반드시 추가하세요.

plpgsql 함수로 비즈니스 규칙 강제하기

단순한 트리거 함수를 넘어서, 복잡한 비즈니스 로직을 데이터베이스 함수로 구현하면 네트워크 왕복 횟수를 줄이고 원자성을 보장할 수 있습니다. Supabase의 rpc() 메서드로 클라이언트에서 직접 호출할 수 있습니다.

-- 게시글 발행 함수: 본문 유효성 검사 + 상태 변경 + 발행 시각 기록
CREATE OR REPLACE FUNCTION public.publish_post(post_id bigint)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER AS $$
DECLARE
  v_post public.posts%ROWTYPE;
BEGIN
  SELECT * INTO v_post FROM public.posts WHERE id = post_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'post_not_found: id=%', post_id;
  END IF;

  IF v_post.author_id <> auth.uid() THEN
    RAISE EXCEPTION 'forbidden: only the author can publish';
  END IF;

  IF v_post.status = 'published' THEN
    RAISE EXCEPTION 'already_published';
  END IF;

  IF v_post.body IS NULL OR char_length(v_post.body) < 10 THEN
    RAISE EXCEPTION 'body_too_short: minimum 10 characters required';
  END IF;

  UPDATE public.posts
  SET status = 'published', updated_at = now()
  WHERE id = post_id;
END;
$$;

클라이언트에서 호출하는 방법은 아래와 같습니다.

const { error } = await supabase.rpc('publish_post', { post_id: 42 })

if (error) {
  // error.message 에 RAISE EXCEPTION 메시지가 담겨 있음
  console.error(error.message)
}

💡 TIP SECURITY DEFINER 함수는 함수 소유자의 권한으로 실행됩니다. RLS를 우회할 수 있으므로, 함수 내부에서 auth.uid()를 이용한 명시적 소유권 검사를 반드시 포함하세요.

마이그레이션 기반 스키마 관리

Supabase CLI는 supabase/migrations 디렉터리에 타임스탬프 기반 SQL 파일을 쌓아가는 방식으로 스키마를 버전 관리합니다.

# 새 마이그레이션 파일 생성
supabase migration new add_post_tags

# 로컬 DB에 적용
supabase db reset   # 로컬 DB를 드롭 후 재생성하여 처음부터 재적용(개발 환경, 기존 데이터가 전부 삭제되므로 주의)
supabase db push    # 원격 DB에 미적용 마이그레이션만 적용

생성된 파일(20240601120000_add_post_tags.sql)에 DDL을 작성합니다.

-- supabase/migrations/20240601120000_add_post_tags.sql

CREATE TABLE IF NOT EXISTS public.post_tags (
  post_id bigint NOT NULL REFERENCES public.posts(id) ON DELETE CASCADE,
  tag_id  bigint NOT NULL REFERENCES public.tags(id)  ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

COMMENT ON TABLE public.post_tags IS '게시글-태그 다대다 조인 테이블';

마이그레이션 작성 원칙

-- ✅ 멱등성 보장: IF NOT EXISTS / OR REPLACE 활용
CREATE TABLE IF NOT EXISTS public.tags ( ... );
CREATE OR REPLACE FUNCTION public.set_updated_at() ...;

-- ❌ 재실행 시 오류 발생
CREATE TABLE public.tags ( ... );
CREATE FUNCTION public.set_updated_at() ...;

💡 TIP 컬럼 삭제(DROP COLUMN)나 타입 변경은 기존 데이터와 API에 영향을 줍니다. 프로덕션에서는 먼저 새 컬럼을 추가하고, 데이터를 마이그레이션한 뒤, 이전 컬럼을 삭제하는 확장-마이그레이션-축소 3단계 전략을 권장합니다.

뷰와 Materialized View로 읽기 모델 분리하기

뷰(View)

뷰는 복잡한 쿼리에 이름을 붙여 재사용하는 가상 테이블입니다. 실제 데이터를 저장하지 않으며, PostgREST를 통해 API로 노출할 수 있습니다.

-- 게시글 + 작성자 + 태그 수를 합친 읽기 전용 뷰
CREATE OR REPLACE VIEW public.posts_with_meta AS
SELECT
  p.id,
  p.title,
  p.status,
  p.created_at,
  pr.display_name AS author_name,
  COUNT(pt.tag_id) AS tag_count
FROM public.posts p
JOIN public.profiles pr ON pr.id = p.author_id
LEFT JOIN public.post_tags pt ON pt.post_id = p.id
GROUP BY p.id, pr.display_name;

-- RLS는 뷰 기반 테이블에 적용되므로, 뷰 자체에도 RLS 정책 필요

⚠️ 주의 뷰는 기반 테이블의 RLS를 자동으로 따르지 않습니다. Supabase에서 뷰를 API로 노출할 때는 SECURITY INVOKER 뷰(기본값)인지 SECURITY DEFINER 뷰인지 명확히 하고, 별도 RLS 정책을 검토하세요.

Materialized View

Materialized View는 쿼리 결과를 실제 디스크에 저장합니다. 집계·조인이 무거운 대시보드 데이터에 적합하며, REFRESH로 수동 또는 스케줄 갱신합니다.

-- 태그별 게시글 수 집계 (무거운 쿼리를 미리 계산)
CREATE MATERIALIZED VIEW public.tag_post_counts AS
SELECT
  t.id,
  t.name,
  COUNT(pt.post_id) AS post_count
FROM public.tags t
LEFT JOIN public.post_tags pt ON pt.tag_id = t.id
GROUP BY t.id, t.name;

-- 유니크 인덱스가 있어야 CONCURRENTLY 새로고침 가능
CREATE UNIQUE INDEX ON public.tag_post_counts (id);

-- 데이터 새로고침 (테이블 잠금 없이)
REFRESH MATERIALIZED VIEW CONCURRENTLY public.tag_post_counts;
구분ViewMaterialized View
데이터 저장없음 (쿼리 실행)있음 (디스크에 캐시)
항상 최신예아니오 (REFRESH 필요)
인덱스 가능불가가능
적합한 용도단순 추상화, RLS 경계무거운 집계, 대시보드

Supabase에서 Materialized View를 주기적으로 갱신하려면 pg_cron 확장을 활성화하고 스케줄을 등록합니다.

-- pg_cron 확장 활성화 (대시보드 Extensions에서도 가능)
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 매 시간 정각에 새로고침
SELECT cron.schedule(
  'refresh-tag-counts',
  '0 * * * *',
  $$ REFRESH MATERIALIZED VIEW CONCURRENTLY public.tag_post_counts $$
);

요약

  • Supabase 프로젝트는 public(애플리케이션), auth(인증), storage(파일 메타) 스키마로 분리되어 있으며, auth 스키마는 직접 수정하지 않고 public.profiles로 확장한다.
  • 외래키의 ON DELETE CASCADE / SET NULL / RESTRICT 옵션은 데이터 무결성과 삭제 정책을 결정하며, 다대다 관계는 조인 테이블로 표현한다.
  • jsonb는 반정형 데이터에, enum은 고정 값 집합에, array는 단순 목록에, generated column은 파생 계산값에 각각 적합하다.
  • CHECK 제약과 트리거로 데이터베이스 레벨의 불변 규칙을 강제하고, plpgsql 함수로 복잡한 비즈니스 트랜잭션을 원자적으로 처리한다.
  • 마이그레이션은 타임스탬프 파일로 버전 관리하며, 프로덕션 스키마 변경에는 확장-마이그레이션-축소 3단계 전략을 사용한다.
  • 뷰는 쿼리 추상화와 RLS 경계 정의에, Materialized View는 무거운 집계 결과 캐싱에 활용하며, 후자는 REFRESH CONCURRENTLY로 무중단 갱신할 수 있다.

연습문제

  1. public.orders 테이블에 status 컬럼을 enum 타입으로 추가하되, 허용 값은 'pending', 'paid', 'shipped', 'cancelled'입니다. 이후 'refunded' 값을 'cancelled' 앞에 추가하는 마이그레이션 SQL을 작성하세요.

    힌트 CREATE TYPE ... AS ENUM, ALTER TYPE ... ADD VALUE ... BEFORE

  2. public.products 테이블에 price numeric NOT NULL과 discount_rate numeric DEFAULT 0 컬럼이 있습니다. 실제 판매 가격을 자동으로 계산하는 sale_price generated column을 추가하고, price와 discount_rate에 적절한 CHECK 제약을 함께 작성하세요.

    힌트 GENERATED ALWAYS AS (...) STORED, CHECK (price > 0), CHECK (discount_rate BETWEEN 0 AND 1)

  3. public.profiles 테이블에서 사용자가 삭제될 때 해당 사용자의 게시글을 삭제하는 대신 author_id를 고정된 "탈퇴 회원" UUID('00000000-0000-0000-0000-000000000000')로 변경하는 트리거와 함수를 작성하세요.

    힌트 BEFORE DELETE 트리거, UPDATE public.posts SET author_id = '...' WHERE author_id = OLD.id

  4. 게시글 제목과 본문을 합쳐 전문 검색 인덱스를 생성하고, 특정 키워드를 포함한 게시글을 검색하는 쿼리를 작성하세요. to_tsvector와 to_tsquery를 사용하고, 한국어 환경에서는 'simple' 설정을 사용합니다.

    힌트 search_vector generated column + GIN 인덱스, WHERE search_vector @@ to_tsquery('simple', '키워드')

💡 연습문제 풀이

불러오는 중…

함께 보면 좋은 자료

📑 치트시트

🛠 자주 나는 에러

🧰 개발자 도구

댓글 0

“Supabase 심화” 강좌에 대한 댓글입니다.

댓글을 작성하려면 로그인이 필요합니다.