postgres-patterns

name	postgres-patterns
description	쿼리 최적화, 스키마 설계, 인덱싱 및 보안을 위한 PostgreSQL 데이터베이스 패턴. 일반적인 패턴, 인덱스 유형, 데이터 유형 및 안티 패턴 감지를 위한 빠른 참조 가이드. Supabase 모범 사례를 기반으로 합니다.
metadata	{"origin":"ECC","credit":"Supabase team (MIT License)"}

PostgreSQL 패턴 (PostgreSQL Patterns)

PostgreSQL 모범 사례에 대한 빠른 참조 가이드입니다. 상세한 안내는 database-reviewer 에이전트를 사용하세요.

활성화 시기

• SQL 쿼리 또는 마이그레이션 작성 시
• 데이터베이스 스키마 설계 시
• 느린 쿼리 트러블슈팅 시
• 행 단위 보안(RLS) 구현 시
• 커넥션 풀링(connection pooling) 설정 시

퀵 레퍼런스

인덱스 치트 시트 (Index Cheat Sheet)

쿼리 패턴	인덱스 유형	예시
WHERE col = value	B-tree (기본값)	CREATE INDEX idx ON t (col)
WHERE col > value	B-tree	CREATE INDEX idx ON t (col)
WHERE a = x AND b > y	복합(Composite)	CREATE INDEX idx ON t (a, b)
WHERE jsonb @> '{}'	GIN	CREATE INDEX idx ON t USING gin (col)
WHERE tsv @@ query	GIN	CREATE INDEX idx ON t USING gin (col)
시계열 범위(Ranges)	BRIN	CREATE INDEX idx ON t USING brin (col)

데이터 유형 퀵 레퍼런스

사용 사례	올바른 유형	피해야 할 유형
ID	bigint	int, random UUID
문자열	text	varchar(255)
타임스탬프	timestamptz	timestamp
금액	numeric(10,2)	float
플래그	boolean	varchar, int

일반적인 패턴

복합 인덱스 순서:

-- 등치(Equality) 컬럼을 먼저 배치하고, 범위(Range) 컬럼을 나중에 배치
CREATE INDEX idx ON orders (status, created_at);
-- 적용 대상: WHERE status = 'pending' AND created_at > '2024-01-01'

커버링 인덱스 (Covering Index):

CREATE INDEX idx ON users (email) INCLUDE (name, created_at);
-- SELECT email, name, created_at 조회 시 테이블 조회를 피함

부분 인덱스 (Partial Index):

CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;
-- 인덱스 크기를 줄이고 활성 사용자만 포함

RLS 정책 (최적화):

CREATE POLICY policy ON orders
  USING ((SELECT auth.uid()) = user_id);  -- SELECT로 감싸기!

UPSERT:

INSERT INTO settings (user_id, key, value)
VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value;

커서 페이지네이션 (Cursor Pagination):

SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;
-- O(1) 성능 (OFFSET 방식은 O(n))

큐 프로세싱 (Queue Processing):

UPDATE jobs SET status = 'processing'
WHERE id = (
  SELECT id FROM jobs WHERE status = 'pending'
  ORDER BY created_at LIMIT 1
  FOR UPDATE SKIP LOCKED
) RETURNING *;

안티 패턴 감지

-- 인덱스가 없는 외래 키 찾기
SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
  );

-- 느린 쿼리 찾기
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

-- 테이블 비대화(Bloat) 확인
SELECT relname, n_dead_tup, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

구성 템플릿 (Configuration Template)

-- 커넥션 제한 (RAM에 따라 조정)
ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET work_mem = '8MB';

-- 타임아웃
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET statement_timeout = '30s';

-- 모니터링
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 보안 기본값
REVOKE ALL ON SCHEMA public FROM public;

SELECT pg_reload_conf();

관련 항목

• 에이전트: database-reviewer - 전체 데이터베이스 리뷰 워크플로우
• 스킬: backend-patterns - API 및 백엔드 패턴
• 스킬: database-migrations - 안전한 스키마 변경

이 스킬을 사용하는 시점

• SQL 쿼리를 작성할 때
• 데이터베이스 스키마를 설계할 때
• 쿼리 성능을 최적화할 때
• 행 단위 보안(RLS)을 구현할 때
• 데이터베이스 문제를 해결할 때
• PostgreSQL 구성을 설정할 때

---

Supabase 에이전트 스킬 기반 (제공: Supabase 팀) (MIT License)