| name | postgres-schema |
| description | PostgreSQL 스키마 설계 가이드. DDL 작성, 테이블 설계, 인덱스 전략, 마이그레이션 시 참조. "schema", "ddl", "table", "migration", "index", "constraint" 키워드로 트리거. |
PostgreSQL Schema Design Guide
Quick Reference
┌─────────────────────────────────────────────────────────────────┐
│ Schema Design Principles │
├─────────────────────────────────────────────────────────────────┤
│ │
│ 1. TEXT 기본 → VARCHAR 예외 (RFC 표준만) │
│ 2. TIMESTAMPTZ 필수 (타임존 보존) │
│ 3. ENUM 선택적 (고정 값 집합만) │
│ 4. Partial Index 활용 (NULL 제외) │
│ 5. CASCADE 명시적 설정 │
│ │
└─────────────────────────────────────────────────────────────────┘
Naming Conventions
| Type | Pattern | Example |
|---|
| Schema | 도메인 소문자 | chat, users, auth |
| Table | snake_case 복수형 | sessions, social_accounts |
| Column | snake_case | user_id, created_at |
| PK | id | id UUID PRIMARY KEY |
| FK Column | {entity}_id | session_id, user_id |
| Index | idx_{table}_{columns} | idx_sessions_user_updated |
| Constraint | {type}_{table}_{column} | fk_user, chk_role |
Column Type Strategy
1. TEXT 기본 원칙
content TEXT NOT NULL,
title TEXT NOT NULL,
description TEXT,
title VARCHAR(255) NOT NULL,
2. VARCHAR 예외 (RFC/표준 기반만)
email VARCHAR(320) NOT NULL,
phone_number VARCHAR(20) UNIQUE,
country_code CHAR(2),
currency_code CHAR(3),
language_code VARCHAR(5),
3. ENUM 사용 기준
role VARCHAR(10) NOT NULL CHECK (role IN ('user', 'assistant')),
type VARCHAR(20) NOT NULL CHECK (type IN ('text', 'image', 'generated_image')),
CREATE TYPE message_role AS ENUM ('user', 'assistant');
ENUM vs CHECK 비교:
| 방식 | 장점 | 단점 |
|---|
| CHECK | 마이그레이션 쉬움, 값 추가 간단 | 오타 가능성 |
| ENUM TYPE | 타입 안전성, 저장 공간 효율 | 값 추가 시 ALTER TYPE 필요 |
Timestamp Rules
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
created_at TIMESTAMP NOT NULL,
SQLAlchemy에서 자동 갱신:
updated_at: Mapped[datetime] = mapped_column(
TIMESTAMP(timezone=True),
server_default=func.now(),
onupdate=func.now(),
)
Constraint Patterns
Foreign Key
CONSTRAINT fk_session FOREIGN KEY (session_id)
REFERENCES chat.sessions(id) ON DELETE CASCADE,
CONSTRAINT fk_assignee FOREIGN KEY (assignee_id)
REFERENCES users.accounts(id) ON DELETE SET NULL,
CONSTRAINT fk_owner FOREIGN KEY (owner_id)
REFERENCES users.accounts(id) ON DELETE RESTRICT,
Check Constraints
CONSTRAINT chk_role CHECK (role IN ('user', 'assistant')),
CONSTRAINT chk_type CHECK (type IN ('text', 'image', 'generated_image')),
CONSTRAINT chk_message_count CHECK (message_count >= 0),
Unique Constraints
email VARCHAR(320) UNIQUE NOT NULL,
CONSTRAINT uq_social_provider UNIQUE (provider, provider_user_id),
Index Strategy
기본 인덱스
CREATE INDEX idx_sessions_user_updated
ON chat.sessions(user_id, updated_at DESC);
CREATE INDEX idx_messages_session_ts
ON chat.messages(session_id, timestamp DESC);
Partial Index (조건부 인덱스)
CREATE INDEX idx_accounts_nickname
ON users.accounts(nickname)
WHERE nickname IS NOT NULL;
CREATE INDEX idx_sessions_active
ON chat.sessions(user_id, updated_at DESC)
WHERE is_deleted = FALSE;
Soft Delete Pattern
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
deleted_at TIMESTAMPTZ,
CREATE INDEX idx_sessions_user_updated
ON chat.sessions(user_id, updated_at DESC)
WHERE is_deleted = FALSE;
Standard DDL Template
CREATE SCHEMA IF NOT EXISTS {schema_name};
CREATE TABLE {schema_name}.{table_name} (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
{parent}_id UUID NOT NULL,
{column} {TYPE} [NOT NULL] [DEFAULT value],
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
CONSTRAINT fk_{parent} FOREIGN KEY ({parent}_id)
REFERENCES {parent_schema}.{parent_table}(id) ON DELETE CASCADE,
CONSTRAINT chk_{column} CHECK ({column} IN ('val1', 'val2'))
);
CREATE INDEX idx_{table}_{columns}
ON {schema_name}.{table_name}({columns})
[WHERE condition];
COMMENT ON TABLE {schema_name}.{table_name} IS '테이블 설명';
COMMENT ON COLUMN {schema_name}.{table_name}.{column} IS '컬럼 설명';
Migration Structure
migrations/schemas/
├── 001_users_schema.sql # users 스키마
├── 002_auth_schema.sql # auth 스키마
├── 003_chat_schema.sql # chat 스키마 (신규)
└── README.md # 마이그레이션 가이드
Review Checklist
DDL 리뷰 시 확인할 항목:
Reference Files
Eco² Project Notes
