dev.syw

EXPLAIN으로 쿼리를 분석하고 인덱스·커넥션·캐싱 전략으로 대규모 트래픽에 대응한다.

성능 튜닝과 인덱싱, 확장성 설계

애플리케이션이 성장할수록 가장 먼저 드러나는 문제는 쿼리 지연입니다. 입문편에서 테이블을 만들고 기본 CRUD를 익혔다면, 이제는 그 쿼리들이 실제로 어떻게 실행되는지 들여다보고, 병목을 찾아 제거하는 단계로 나아가야 합니다. 이 레슨은 PostgreSQL의 실행 계획 분석부터 인덱스 설계, 커넥션 풀링, N+1 회피, 캐싱까지 Supabase에서 성능을 체계적으로 관리하는 방법을 다룹니다.

학습 목표

  • EXPLAIN ANALYZE 출력을 읽고 느린 쿼리의 원인을 진단할 수 있다.
  • B-tree, GIN, partial, composite 인덱스의 차이를 이해하고 상황에 맞게 선택할 수 있다.
  • Supavisor(PgBouncer) 커넥션 풀링의 동작 방식과 transaction vs session 모드의 차이를 설명할 수 있다.
  • N+1 문제를 탐지하고 임베딩 쿼리·RPC로 라운드트립을 줄일 수 있다.
  • Materialized View와 캐싱 전략으로 읽기 부하를 분산할 수 있다.

EXPLAIN ANALYZE로 실행 계획 읽기

PostgreSQL이 쿼리를 처리할 때 옵티마이저는 가능한 여러 실행 경로 중 비용이 가장 낮은 것을 선택합니다. EXPLAIN ANALYZE는 그 계획을 텍스트로 보여주는 동시에 실제로 쿼리를 실행해 측정치를 함께 출력합니다.

-- Supabase SQL Editor 또는 psql에서 실행
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email;

출력에서 주목해야 할 항목은 다음과 같습니다.

항목의미
Seq Scan테이블 전체를 순차 스캔 — 인덱스가 없거나 옵티마이저가 유리하다고 판단
Index Scan인덱스를 통해 행을 찾은 뒤 힙(heap)에서 실제 행을 읽음
Index Only Scan힙 접근 없이 인덱스만으로 결과 반환 — 가장 빠름
cost=X..Y예상 비용(시작..완료). 단위는 임의적이며 상대 비교에 사용
actual time=A..B실제 소요 시간(ms). 예상과 크게 다르면 통계가 오래된 것
rows=N예상 행 수. 실제와 큰 차이가 있으면 ANALYZE 재실행 필요
Buffers: hit=X read=Y캐시 히트 vs 디스크 읽기. read가 많으면 I/O 병목

⚠️ 주의 EXPLAIN ANALYZE는 쿼리를 실제 실행합니다. DELETE, UPDATE에 사용할 때는 트랜잭션으로 감싸고 롤백하세요.

-- DML 분석 시 반드시 트랜잭션 사용
BEGIN;
EXPLAIN ANALYZE
DELETE FROM logs WHERE created_at < NOW() - INTERVAL '90 days';
ROLLBACK;

출력이 복잡할 때는 FORMAT JSON으로 받아 explain.dalibo.com 같은 시각화 도구에 붙여넣으면 트리 형태로 확인하기 쉽습니다.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM products WHERE category = 'electronics' AND price < 50000;

인덱스 종류와 선택 기준

인덱스를 무작정 많이 만들면 쓰기 성능이 저하됩니다. 각 인덱스 유형의 특성을 파악해 필요한 곳에만 정확히 배치하는 것이 핵심입니다.

B-tree 인덱스 (기본값)

PostgreSQL의 기본 인덱스 유형으로, 등치(=), 범위(<, >, BETWEEN), 정렬(ORDER BY) 연산에 효과적입니다.

-- ✅ 단순 컬럼 인덱스
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- ✅ 정렬 방향을 명시해 ORDER BY 최적화
CREATE INDEX idx_posts_created_desc ON posts(created_at DESC);

Composite(복합) 인덱스

두 개 이상의 컬럼을 묶은 인덱스입니다. 컬럼 순서가 핵심으로, 선두 컬럼이 WHERE 절에 포함되어야 인덱스가 활용됩니다.

-- ✅ (user_id, status) 복합 — user_id만의 조건에도 사용 가능
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- ❌ 선두 컬럼(user_id) 없이 status만 조건으로 주면 이 인덱스 미사용
-- SELECT * FROM orders WHERE status = 'pending';

자주 조회하는 컬럼 조합을 분석해 카디널리티가 높은 컬럼(값이 더 다양한 것)을 앞에 두는 것이 일반적입니다.

Partial(부분) 인덱스

조건을 만족하는 행에만 인덱스를 생성합니다. 대부분의 쿼리가 특정 상태의 행만 다룰 때 인덱스 크기를 대폭 줄일 수 있습니다.

-- ✅ 미처리 주문만 인덱싱 — 전체의 5%만 해당한다면 인덱스가 20배 작아짐
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- ✅ 삭제되지 않은 게시글만 인덱싱
CREATE INDEX idx_posts_active ON posts(user_id, created_at)
WHERE deleted_at IS NULL;

💡 TIP 쿼리의 WHERE 절에 인덱스의 WHERE 조건이 그대로 포함되어야 옵티마이저가 partial index를 선택합니다.

GIN 인덱스

배열, JSONB, 전문 검색(tsvector)처럼 하나의 값 안에 여러 요소가 있는 타입에 적합합니다.

-- ✅ JSONB 컬럼 내 키로 검색
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);

-- 사용 예
SELECT * FROM products WHERE metadata @> '{"color": "red"}';

-- ✅ 배열 컬럼 포함 검색
CREATE INDEX idx_articles_tags ON articles USING GIN(tags);
SELECT * FROM articles WHERE tags @> ARRAY['supabase', 'postgres'];

-- ✅ 전문 검색
-- ⚠️ PostgreSQL/Supabase는 'korean' 텍스트 검색 구성을 기본 제공하지 않습니다.
--    그대로 쓰면 'text search configuration "korean" does not exist' 에러가 납니다.
--    한국어 형태소 분석은 별도 확장(pg_bigm, mecab 기반 등)이나 커스텀 configuration이 필요합니다.
--    아래는 기본 제공되는 'simple' 구성을 사용한 예입니다.
CREATE INDEX idx_posts_fts ON posts USING GIN(to_tsvector('simple', title || ' ' || body));
SELECT * FROM posts
WHERE to_tsvector('simple', title || ' ' || body) @@ plainto_tsquery('simple', '성능 튜닝');

인덱스 선택 기준 요약

유형적합한 상황
B-tree등치, 범위, 정렬. 대부분의 기본 케이스
Composite두 컬럼 이상 동시 조건. 선두 컬럼 카디널리티 우선
Partial특정 조건(status, deleted_at)으로 행이 필터링될 때
GINJSONB, 배열, 전문검색
-- 사용되지 않는 인덱스 확인 (운영 중 주기적으로 점검)
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;

커넥션 풀링: Supavisor와 transaction vs session 모드

서버리스 환경에서 PostgreSQL에 직접 연결하면 각 함수 호출마다 새 커넥션이 생성되어 금방 max_connections 한도에 도달합니다. Supabase는 Supavisor(PgBouncer 기반)를 기본 제공하며, 연결 문자열 포트로 구분합니다.

모드포트커넥션 유지사용 사례
Session mode5432클라이언트 연결 동안 유지긴 트랜잭션, SET LOCAL, 임시 테이블
Transaction mode6543트랜잭션 단위로 반환서버리스, 짧은 쿼리 다수
# Transaction mode (서버리스/Edge Functions 권장)
# 풀러 호스트는 지역별로 다르고 사용자명은 postgres.[ref] 형태입니다.
postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres?pgbouncer=true

# Session mode (긴 세션 필요 시) — 동일 풀러 호스트의 5432 포트 사용
postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres

# 실제 호스트/포트/사용자명은 대시보드의 Connection Pooling 탭 값을 그대로 사용하세요.

⚠️ 주의 Transaction mode에서는 SET 명령, prepared statements, advisory locks가 세션 간에 공유되지 않습니다. RLS의 set_config() 호출도 트랜잭션 범위에서만 유효합니다.

Edge Functions에서 Prisma나 Drizzle을 사용할 때 커넥션 풀을 직접 관리해야 한다면 다음처럼 설정합니다.

// Edge Function에서 Prisma 사용 시
import { PrismaClient } from '@prisma/client'

// ✅ 전역 인스턴스 재사용 — 매 요청마다 new PrismaClient() 금지
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient }

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    datasources: {
      db: {
        url: process.env.DATABASE_URL, // 포트 6543 transaction mode URL
      },
    },
  })

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

Supabase 대시보드 → Settings > Database > Connection Pooling 에서 풀 크기(pool size)와 현재 연결 수를 확인하고 조정할 수 있습니다.

N+1 회피: 임베딩 쿼리와 RPC 활용

N+1 문제는 목록을 가져온 뒤 각 항목마다 추가 쿼리를 날리는 패턴입니다. 100개의 게시글을 가져오고 각각의 작성자 정보를 별도로 조회하면 101번의 왕복이 발생합니다.

PostgREST 임베딩으로 단일 요청 처리

// ❌ N+1 패턴
const { data: posts } = await supabase.from('posts').select('*')
for (const post of posts) {
  const { data: author } = await supabase
    .from('users')
    .select('*')
    .eq('id', post.user_id)
    .single()
  // post.author = author
}

// ✅ 임베딩 쿼리 — 단 1번의 요청
const { data: posts } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    created_at,
    author:users(id, email, avatar_url),
    comments(id, body, created_at)
  `)
  .order('created_at', { ascending: false })
  .limit(20)

RPC(Postgres 함수)로 복잡한 로직 서버 집약

여러 테이블을 집계하거나 비즈니스 로직이 복잡한 경우, 클라이언트에서 여러 번 쿼리하는 대신 Postgres 함수로 캡슐화합니다.

-- 대시보드 요약 데이터를 한 번에 반환하는 함수
CREATE OR REPLACE FUNCTION get_dashboard_summary(p_user_id UUID)
RETURNS JSON
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  result JSON;
BEGIN
  SELECT json_build_object(
    'total_orders',    (SELECT COUNT(*) FROM orders WHERE user_id = p_user_id),
    'pending_orders',  (SELECT COUNT(*) FROM orders WHERE user_id = p_user_id AND status = 'pending'),
    'total_spent',     (SELECT COALESCE(SUM(amount), 0) FROM orders WHERE user_id = p_user_id AND status = 'completed'),
    'recent_orders',   (
      SELECT json_agg(row_to_json(o))
      FROM (
        SELECT id, amount, status, created_at
        FROM orders
        WHERE user_id = p_user_id
        ORDER BY created_at DESC
        LIMIT 5
      ) o
    )
  ) INTO result;

  RETURN result;
END;
$$;

// 클라이언트에서 단 1회 호출
const { data } = await supabase
  .rpc('get_dashboard_summary', { p_user_id: userId })

💡 TIP SECURITY DEFINER 함수는 함수 소유자 권한으로 실행됩니다. RLS를 우회할 수 있으므로 내부에서 직접 사용자 검증을 구현하거나, 반드시 필요한 경우에만 사용하세요.

캐싱 전략과 Materialized View

Materialized View로 집계 쿼리 사전 계산

무거운 집계 쿼리를 매번 실행하는 대신 결과를 물리적으로 저장하고 주기적으로 갱신합니다.

-- 카테고리별 통계를 사전 계산
CREATE MATERIALIZED VIEW product_category_stats AS
SELECT
  c.id          AS category_id,
  c.name        AS category_name,
  COUNT(p.id)   AS product_count,
  AVG(p.price)  AS avg_price,
  MIN(p.price)  AS min_price,
  MAX(p.price)  AS max_price
FROM categories c
LEFT JOIN products p ON p.category_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id, c.name;

-- Materialized View에도 인덱스 생성 가능
CREATE UNIQUE INDEX idx_mv_category_stats ON product_category_stats(category_id);

갱신은 수동 또는 스케줄로 트리거합니다.

-- 동시 갱신 (읽기를 막지 않음) — 운영 환경 권장
REFRESH MATERIALIZED VIEW CONCURRENTLY product_category_stats;

-- pg_cron으로 매시간 자동 갱신 (Supabase Database Extensions에서 pg_cron 활성화)
SELECT cron.schedule(
  'refresh-category-stats',
  '0 * * * *',  -- 매시간 정각
  'REFRESH MATERIALIZED VIEW CONCURRENTLY product_category_stats'
);

⚠️ 주의 CONCURRENTLY 옵션을 사용하려면 Materialized View에 UNIQUE 인덱스가 반드시 있어야 합니다.

애플리케이션 레벨 캐싱

Supabase Realtime이나 Edge Functions에서 Redis(Upstash) 또는 메모리 캐싱을 조합합니다.

// Edge Function에서 간단한 캐싱 패턴 (Upstash Redis)
import { Redis } from '@upstash/redis'

const redis = Redis.fromEnv()
const CACHE_TTL = 60 * 5 // 5분

export async function getCategoryStats() {
  const cacheKey = 'category_stats'

  // ✅ 캐시 우선 조회
  const cached = await redis.get(cacheKey)
  if (cached) return cached

  // 캐시 미스 — DB 조회
  const { data, error } = await supabase
    .from('product_category_stats') // materialized view
    .select('*')

  if (error) throw error

  // 결과 캐싱
  await redis.setex(cacheKey, CACHE_TTL, JSON.stringify(data))
  return data
}

읽기 부하 분산: Read Replica

Supabase Pro 플랜 이상에서는 Read Replica를 활성화할 수 있습니다. 쓰기는 primary로, 집계·분석·리포트 쿼리는 replica로 라우팅해 부하를 분산합니다.

// Read Replica 연결 (별도 URL 사용)
const supabaseRead = createClient(
  process.env.SUPABASE_REPLICA_URL!,  // replica 엔드포인트
  process.env.SUPABASE_ANON_KEY!
)

// ✅ 읽기 전용 쿼리는 replica로
// PostgREST에는 'COUNT(*)' 표기가 없습니다. 단순 행 개수는 count 옵션을 사용합니다.
const { count: orderCount } = await supabaseRead
  .from('orders')
  .select('*', { count: 'exact', head: true })

// 상태별 GROUP BY 집계가 목적이라면 Postgres 함수(RPC)로 처리합니다.
// CREATE FUNCTION orders_status_summary() RETURNS TABLE(status text, total bigint)
//   LANGUAGE sql AS $$ SELECT status, COUNT(*) FROM orders GROUP BY status $$;
const { data: report } = await supabaseRead
  .rpc('orders_status_summary')

// ✅ 쓰기는 primary로
const { data } = await supabase
  .from('orders')
  .insert({ user_id: userId, amount: 15000 })

Supabase 대시보드로 병목 모니터링

코드 변경 없이 현재 시스템 상태를 파악하는 가장 빠른 방법은 대시보드를 활용하는 것입니다.

Query Performance 탭

대시보드 → Database > Query Performance 에서 pg_stat_statements 기반으로 가장 느린 쿼리, 가장 많이 호출된 쿼리, 총 실행 시간이 긴 쿼리를 확인할 수 있습니다.

  • Mean Time 이 높은 쿼리: 실행 계획을 분석해 인덱스 추가 여부 판단
  • Calls 가 많은 쿼리: N+1 패턴이 아닌지 확인
  • Total Time 이 높은 쿼리: 빈도와 단건 비용의 곱 — 우선순위가 가장 높음
-- 직접 pg_stat_statements 조회 (상위 10개 느린 쿼리)
SELECT
  query,
  calls,
  ROUND(mean_exec_time::numeric, 2) AS mean_ms,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

로그 분석

대시보드 → Logs > Postgres 에서 실시간 로그를 필터링할 수 있습니다. slow query 또는 특정 테이블명으로 검색해 문제 쿼리를 빠르게 찾습니다.

-- slow query 임계값 설정 (500ms 이상 쿼리 기록)
-- ✅ 세션 단위 테스트: 단위 없는 정수는 밀리초로 해석됩니다.
SET log_min_duration_statement = 500;  -- 현재 세션에만 적용

-- ⚠️ 관리형 Supabase에서는 ALTER SYSTEM / pg_reload_conf()가 슈퍼유저 권한을 요구하므로
--    SQL Editor에서 실행하면 'permission denied to set parameter' 등으로 거부될 수 있습니다.
--    전역 설정 변경은 Supabase 대시보드 설정 경로나 지원을 통해 진행해야 합니다.
-- ALTER SYSTEM SET log_min_duration_statement = '500ms';
-- SELECT pg_reload_conf();

테이블 통계 현행화

옵티마이저의 예측이 실제와 크게 다를 때는 통계를 수동으로 갱신합니다.

-- 특정 테이블 통계 갱신
ANALYZE orders;

-- 전체 DB 통계 갱신 (대용량 테이블에서는 시간이 걸릴 수 있음)
ANALYZE VERBOSE;

💡 TIP Supabase는 autovacuum을 기본 활성화하지만, 대용량 bulk insert/delete 직후에는 수동 ANALYZE를 실행해 통계를 즉시 반영하는 것이 좋습니다.

요약

  • EXPLAIN (ANALYZE, BUFFERS) 로 실행 계획을 확인하고, Seq Scan, actual rows 불일치, 높은 read 버퍼를 성능 문제의 단서로 삼는다.
  • 인덱스 유형 선택: 기본 조회에는 B-tree, 특정 조건 행에는 partial, JSONB·배열에는 GIN, 다중 컬럼 조건에는 composite를 사용한다. 사용되지 않는 인덱스는 주기적으로 제거한다.
  • Supavisor transaction mode(포트 6543) 는 서버리스 환경에서 커넥션 고갈을 방지한다. SET, prepared statement, advisory lock은 session mode에서만 안정적으로 동작한다.
  • PostgREST 임베딩과 RPC로 클라이언트-서버 왕복 횟수를 최소화해 N+1 문제를 구조적으로 제거한다.
  • Materialized View + CONCURRENTLY 갱신 으로 무거운 집계를 사전 계산하고, pg_cron으로 자동화한다.
  • Query Performance 탭과 pg_stat_statements를 활용해 Total Time 기준으로 우선순위를 정하고 병목을 해소한다.

연습문제

  1. 다음 쿼리에 적합한 인덱스를 설계하세요. orders 테이블에서 특정 사용자의 status = 'pending' 주문을 created_at DESC 순으로 조회합니다. 전체 주문 중 pending 상태는 약 3%입니다.

    힌트 선두 컬럼 선택, partial index 활용 여부, 정렬 방향을 함께 고려하세요.

  2. 아래 TypeScript 코드에서 N+1 문제를 찾아내고 Supabase 클라이언트를 사용해 단일 요청으로 리팩터링하세요.

    const { data: comments } = await supabase.from('comments').select('*').eq('post_id', postId)
for (const comment of comments) {
  const { data: user } = await supabase.from('users').select('id, nickname, avatar_url').eq('id', comment.user_id).single()
  comment.author = user
}

    힌트 PostgREST의 Foreign Key 관계를 이용한 임베딩 쿼리를 사용하세요.

  3. monthly_revenue Materialized View를 생성하세요. orders 테이블에서 status = 'completed'인 주문을 월별(DATE_TRUNC('month', created_at))로 집계해 month, revenue, order_count 컬럼을 만들고, pg_cron으로 매일 새벽 3시에 갱신되도록 스케줄을 등록하세요.

    힌트 CONCURRENTLY 갱신을 위해 UNIQUE 인덱스가 필요합니다.

  4. pg_stat_statements를 이용해 지난 누적 기간 동안 평균 실행 시간이 100ms 이상이고 호출 횟수가 50회 이상인 쿼리를 total_exec_time 내림차순으로 조회하는 SQL을 작성하세요.

    힌트 mean_exec_time과 calls 컬럼을 WHERE 조건으로 사용하세요.

💡 연습문제 풀이

불러오는 중…

함께 보면 좋은 자료

📑 치트시트

🛠 자주 나는 에러

🧰 개발자 도구

댓글 0

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

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