[MCP&A2A] 07. 데이터베이스 설계

게시 2024/12/13

By BLUEBUG

23 분읽는 시간

PostgreSQL + pgvector 기반 데이터 계층

엔터프라이즈 AI 시스템의 데이터베이스는 다음 요구사항을 충족해야 합니다:

✅ 벡터 유사도 검색: 임베딩 기반 의미 검색
✅ 전문 검색(Full-text Search): BM25 키워드 검색
✅ 멀티테넌시: 테넌트 간 완벽한 데이터 격리
✅ 확장성: 수백만 문서 처리
✅ 트랜잭션: ACID 보장
✅ 감사 추적: 모든 작업 로깅

PostgreSQL 16 선택 이유

왜 PostgreSQL인가?

PostgreSQL 16 장점:
├── pgvector 확장: 네이티브 벡터 검색
├── RLS (Row-Level Security): DB 레벨 멀티테넌시
├── GIN/GiST 인덱스: 전문 검색 최적화
├── JSONB: 유연한 메타데이터 저장
├── 성숙한 생태계: 풍부한 도구와 라이브러리
└── 오픈소스: 라이선스 비용 없음

대안과 비교

특성	PostgreSQL + pgvector	Pinecone	Weaviate	Elasticsearch
벡터 검색	✅ pgvector	✅ 전문	✅ 전문	✅ dense_vector
전문 검색	✅ 네이티브	❌ 없음	✅ BM25	✅ 강력함
트랜잭션	✅ ACID	❌ 없음	⚠️ 제한적	❌ 없음
RLS	✅ 네이티브	❌ 앱 레벨	❌ 앱 레벨	❌ 앱 레벨
비용	무료	$$$	$$	$
복잡도	낮음	낮음	중간	중간
운영	익숙함	관리형	자체 운영	자체 운영

결론: PostgreSQL은 단일 시스템에서 모든 요구사항을 충족하며, 운영 복잡도를 최소화합니다.

핵심 테이블 스키마

1. documents 테이블

문서와 임베딩을 저장하는 핵심 테이블:

  
-- pgvector 확장 활성화 (최초 1회)
CREATE EXTENSION IF NOT EXISTS vector;

-- documents 테이블 생성
CREATE TABLE documents (
    -- 기본 필드
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    
    -- 문서 내용
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    source TEXT,                    -- 문서 출처 (URL, 파일명 등)
    
    -- 벡터 임베딩
    embedding vector(1536),         -- OpenAI ada-002: 1536차원
                                    -- NULL 허용: 임베딩 생성 전 문서 저장 가능
    
    -- 메타데이터
    metadata JSONB DEFAULT '{}',    -- 유연한 추가 정보
                                    -- 예: {"author": "...", "tags": [...]}
    
    -- 타임스탬프
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- 제약조건
    CONSTRAINT documents_title_not_empty CHECK (length(trim(title)) > 0),
    CONSTRAINT documents_content_not_empty CHECK (length(trim(content)) > 0)
);

-- 설명 (COMMENT)
COMMENT ON TABLE documents IS '멀티테넌트 문서 저장소 (벡터 + 전문 검색)';
COMMENT ON COLUMN documents.embedding IS 'OpenAI ada-002 임베딩 (1536차원), NULL 허용';
COMMENT ON COLUMN documents.metadata IS 'JSONB 형식의 유연한 메타데이터';

2. Row-Level Security (RLS) 정책

데이터베이스 레벨에서 테넌트 격리를 강제합니다:

  
-- 1. RLS 활성화
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- 2. 기본 정책: 모든 접근 차단
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

-- 3. 테넌트 격리 정책
CREATE POLICY tenant_isolation_policy ON documents
    FOR ALL                              -- SELECT, INSERT, UPDATE, DELETE 모두
    USING (
        tenant_id = current_setting('app.current_tenant_id', true)::uuid
    )
    WITH CHECK (
        tenant_id = current_setting('app.current_tenant_id', true)::uuid
    );

-- 4. 관리자 전체 접근 정책 (선택사항)
CREATE POLICY admin_full_access_policy ON documents
    FOR ALL
    USING (
        current_setting('app.current_user_role', true) = 'admin'
    );

-- 정책 확인
SELECT 
    schemaname, 
    tablename, 
    policyname, 
    permissive, 
    roles, 
    cmd, 
    qual 
FROM pg_policies 
WHERE tablename = 'documents';

RLS 작동 원리

  
-- 세션 시작 시 테넌트 컨텍스트 설정
BEGIN;
SET LOCAL app.current_tenant_id = 'tenant-uuid-123';

-- 이후 모든 쿼리는 자동으로 필터링됨
SELECT * FROM documents;  
-- 실제 실행: 
-- SELECT * FROM documents WHERE tenant_id = 'tenant-uuid-123';

INSERT INTO documents (tenant_id, title, content) 
VALUES ('tenant-uuid-123', '제목', '내용');
-- WITH CHECK 정책으로 검증됨

-- 다른 테넌트 데이터 접근 시도
INSERT INTO documents (tenant_id, title, content) 
VALUES ('tenant-uuid-999', '제목', '내용');
-- ❌ ERROR: new row violates row-level security policy

COMMIT;

3. 인덱스 전략

성능을 위한 최적화된 인덱스:

  
-- 1. 테넌트 기반 쿼리 최적화
CREATE INDEX idx_documents_tenant_id 
    ON documents(tenant_id);

-- 2. 테넌트 + 생성일시 복합 인덱스 (최신순 조회)
CREATE INDEX idx_documents_tenant_created 
    ON documents(tenant_id, created_at DESC);

-- 3. 벡터 유사도 검색 인덱스 (IVFFlat)
CREATE INDEX idx_documents_embedding 
    ON documents 
    USING ivfflat (embedding vector_cosine_ops) 
    WITH (lists = 100);
-- lists: 클러스터 수 (일반적으로 rows/1000)
-- 100K 문서 → lists=100

-- 4. 전문 검색 인덱스 (GIN)
CREATE INDEX idx_documents_content_fts 
    ON documents 
    USING gin(to_tsvector('english', content));

-- 5. 제목 전문 검색 인덱스
CREATE INDEX idx_documents_title_fts 
    ON documents 
    USING gin(to_tsvector('english', title));

-- 6. JSONB 메타데이터 인덱스 (GIN)
CREATE INDEX idx_documents_metadata 
    ON documents 
    USING gin(metadata);

-- 7. 소스별 검색 (NULL 포함)
CREATE INDEX idx_documents_source 
    ON documents(source) 
    WHERE source IS NOT NULL;

인덱스 성능 영향

100K 문서 기준 벤치마크:

인덱스 없음:
- SELECT (tenant filter): ~2000ms
- Vector search: ~5000ms
- Full-text search: ~3000ms

인덱스 있음:
- SELECT (tenant filter): ~5ms (400배 빠름)
- Vector search: ~45ms (111배 빠름)
- Full-text search: ~15ms (200배 빠름)

4. 트리거 (updated_at 자동 갱신)

  
-- 트리거 함수 정의
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 트리거 생성
CREATE TRIGGER update_documents_updated_at
    BEFORE UPDATE ON documents
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

지원 테이블

1. audit_logs 테이블

모든 작업을 추적하여 컴플라이언스 충족:

  
CREATE TABLE audit_logs (
    -- 기본 필드
    id BIGSERIAL PRIMARY KEY,
    
    -- 타임스탬프 (파티션 키로 사용)
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- 주체 (누가)
    tenant_id UUID NOT NULL,
    user_id TEXT NOT NULL,
    
    -- 행위 (무엇을)
    action TEXT NOT NULL,           -- 'search', 'insert', 'update', 'delete'
    resource TEXT,                  -- 'documents', 'tools/hybrid_search'
    resource_id UUID,               -- 문서 ID 등
    
    -- 상세 정보
    details JSONB DEFAULT '{}',     -- 쿼리 파라미터, 결과 개수 등
    
    -- 결과
    success BOOLEAN NOT NULL,
    error_message TEXT,
    
    -- 네트워크
    ip_address INET,
    user_agent TEXT,
    
    -- 성능
    duration_ms INTEGER             -- 작업 소요 시간 (밀리초)
);

-- 인덱스
CREATE INDEX idx_audit_logs_timestamp 
    ON audit_logs(timestamp DESC);

CREATE INDEX idx_audit_logs_tenant_timestamp 
    ON audit_logs(tenant_id, timestamp DESC);

CREATE INDEX idx_audit_logs_user_timestamp 
    ON audit_logs(user_id, timestamp DESC);

CREATE INDEX idx_audit_logs_action 
    ON audit_logs(action);

-- 파티셔닝 (선택사항, 대용량 시스템용)
-- 월별 파티션 예제
CREATE TABLE audit_logs_2024_12 PARTITION OF audit_logs
    FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');

audit_logs 사용 예제

  
-- 검색 작업 로깅
INSERT INTO audit_logs (
    tenant_id, user_id, action, resource, 
    details, success, duration_ms
) VALUES (
    'tenant-uuid-123',
    'user@example.com',
    'search',
    'documents',
    jsonb_build_object(
        'query', 'machine learning',
        'limit', 10,
        'results_count', 7
    ),
    true,
    45
);

-- 최근 1시간 실패한 작업 조회
SELECT 
    timestamp,
    user_id,
    action,
    resource,
    error_message
FROM audit_logs
WHERE success = false
  AND timestamp > NOW() - INTERVAL '1 hour'
ORDER BY timestamp DESC;

-- 테넌트별 일일 사용량 통계
SELECT 
    tenant_id,
    DATE(timestamp) as date,
    COUNT(*) as total_operations,
    SUM(CASE WHEN success THEN 1 ELSE 0 END) as successful,
    AVG(duration_ms) as avg_duration_ms
FROM audit_logs
WHERE timestamp > NOW() - INTERVAL '7 days'
GROUP BY tenant_id, DATE(timestamp)
ORDER BY date DESC, total_operations DESC;

2. cost_tracking 테이블

토큰 사용량과 비용 추적:

  
CREATE TABLE cost_tracking (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- 주체
    tenant_id UUID NOT NULL,
    user_id TEXT NOT NULL,
    
    -- 모델 정보
    model_name TEXT NOT NULL,       -- 'gpt-4', 'claude-3', 'llama3.2'
    provider TEXT NOT NULL,         -- 'openai', 'anthropic', 'ollama'
    
    -- 토큰 사용량
    input_tokens INTEGER NOT NULL,
    output_tokens INTEGER NOT NULL,
    total_tokens INTEGER GENERATED ALWAYS AS (input_tokens + output_tokens) STORED,
    
    -- 비용 (USD)
    input_cost DECIMAL(10, 6) NOT NULL,
    output_cost DECIMAL(10, 6) NOT NULL,
    total_cost DECIMAL(10, 6) GENERATED ALWAYS AS (input_cost + output_cost) STORED,
    
    -- 컨텍스트
    operation TEXT,                 -- 'search', 'generate', 'chat'
    metadata JSONB DEFAULT '{}'
);

-- 인덱스
CREATE INDEX idx_cost_tracking_timestamp 
    ON cost_tracking(timestamp DESC);

CREATE INDEX idx_cost_tracking_tenant_timestamp 
    ON cost_tracking(tenant_id, timestamp DESC);

CREATE INDEX idx_cost_tracking_user_timestamp 
    ON cost_tracking(user_id, timestamp DESC);

-- 월별 파티셔닝
CREATE TABLE cost_tracking_2024_12 PARTITION OF cost_tracking
    FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');

cost_tracking 사용 예제

  
-- 비용 기록
INSERT INTO cost_tracking (
    tenant_id, user_id, model_name, provider,
    input_tokens, output_tokens, input_cost, output_cost,
    operation
) VALUES (
    'tenant-uuid-123',
    'user@example.com',
    'gpt-4',
    'openai',
    500,
    300,
    0.015,  -- $0.03/1K tokens
    0.009,  -- $0.03/1K tokens
    'search'
);

-- 월별 테넌트 비용
SELECT 
    tenant_id,
    DATE_TRUNC('month', timestamp) as month,
    SUM(total_cost) as total_cost,
    SUM(total_tokens) as total_tokens,
    COUNT(*) as operations
FROM cost_tracking
WHERE timestamp >= DATE_TRUNC('month', NOW()) - INTERVAL '3 months'
GROUP BY tenant_id, month
ORDER BY month DESC, total_cost DESC;

-- 모델별 비용 분석
SELECT 
    model_name,
    COUNT(*) as operations,
    SUM(total_tokens) as total_tokens,
    SUM(total_cost) as total_cost,
    AVG(total_cost) as avg_cost_per_operation
FROM cost_tracking
WHERE tenant_id = 'tenant-uuid-123'
  AND timestamp > NOW() - INTERVAL '30 days'
GROUP BY model_name
ORDER BY total_cost DESC;

-- 예산 초과 알림 체크
SELECT 
    tenant_id,
    SUM(total_cost) as monthly_cost,
    50.00 as budget,  -- $50 예산
    CASE 
        WHEN SUM(total_cost) > 50.00 THEN 'EXCEEDED'
        WHEN SUM(total_cost) > 45.00 THEN 'WARNING'
        ELSE 'OK'
    END as status
FROM cost_tracking
WHERE timestamp >= DATE_TRUNC('month', NOW())
GROUP BY tenant_id
HAVING SUM(total_cost) > 45.00;

3. tenants 테이블

테넌트 메타데이터 및 설정:

  
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- 기본 정보
    name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,      -- URL-friendly 이름
    
    -- 설정
    settings JSONB DEFAULT '{}',    -- 커스텀 설정
    
    -- 할당량
    max_documents INTEGER,          -- 최대 문서 수
    max_storage_mb INTEGER,         -- 최대 저장 용량 (MB)
    monthly_budget_usd DECIMAL(10, 2), -- 월 예산
    
    -- 상태
    status TEXT NOT NULL DEFAULT 'active', -- 'active', 'suspended', 'deleted'
    
    -- 타임스탬프
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 인덱스
CREATE INDEX idx_tenants_slug ON tenants(slug);
CREATE INDEX idx_tenants_status ON tenants(status);

-- 트리거
CREATE TRIGGER update_tenants_updated_at
    BEFORE UPDATE ON tenants
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

고급 기능

1. 벡터 유사도 함수

  
-- 코사인 유사도 (1 = 완전 동일, 0 = 무관)
CREATE OR REPLACE FUNCTION cosine_similarity(a vector, b vector)
RETURNS float AS $$
    SELECT 1 - (a <=> b);
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;

-- 유클리드 거리
CREATE OR REPLACE FUNCTION euclidean_distance(a vector, b vector)
RETURNS float AS $$
    SELECT a <-> b;
$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;

-- 사용 예제
SELECT 
    id,
    title,
    cosine_similarity(embedding, $1::vector) as similarity
FROM documents
WHERE tenant_id = $2
  AND embedding IS NOT NULL
ORDER BY embedding <=> $1::vector  -- 코사인 거리로 정렬
LIMIT 10;

2. 하이브리드 검색 함수

자주 사용하는 하이브리드 검색을 함수로 캡슐화:

  
CREATE OR REPLACE FUNCTION hybrid_search(
    p_tenant_id UUID,
    p_query TEXT,
    p_query_embedding vector,
    p_limit INTEGER DEFAULT 10,
    p_bm25_weight FLOAT DEFAULT 0.5,
    p_vector_weight FLOAT DEFAULT 0.5
)
RETURNS TABLE (
    id UUID,
    title TEXT,
    content TEXT,
    combined_score FLOAT,
    bm25_score FLOAT,
    vector_score FLOAT
) AS $$
BEGIN
    RETURN QUERY
    WITH bm25_results AS (
        SELECT 
            d.id,
            ts_rank_cd(
                to_tsvector('english', d.content),
                plainto_tsquery('english', p_query)
            ) AS bm25_score,
            ROW_NUMBER() OVER (
                ORDER BY ts_rank_cd(
                    to_tsvector('english', d.content),
                    plainto_tsquery('english', p_query)
                ) DESC
            ) AS bm25_rank
        FROM documents d
        WHERE d.tenant_id = p_tenant_id
          AND to_tsvector('english', d.content) @@ plainto_tsquery('english', p_query)
    ),
    vector_results AS (
        SELECT 
            d.id,
            1 - (d.embedding <=> p_query_embedding) AS vector_score,
            ROW_NUMBER() OVER (
                ORDER BY d.embedding <=> p_query_embedding
            ) AS vector_rank
        FROM documents d
        WHERE d.tenant_id = p_tenant_id
          AND d.embedding IS NOT NULL
    ),
    combined AS (
        SELECT 
            COALESCE(b.id, v.id) AS id,
            (
                COALESCE(1.0 / (60 + b.bm25_rank), 0) * p_bm25_weight +
                COALESCE(1.0 / (60 + v.vector_rank), 0) * p_vector_weight
            ) AS combined_score,
            COALESCE(b.bm25_score, 0) AS bm25_score,
            COALESCE(v.vector_score, 0) AS vector_score
        FROM bm25_results b
        FULL OUTER JOIN vector_results v ON b.id = v.id
    )
    SELECT 
        d.id,
        d.title,
        d.content,
        c.combined_score,
        c.bm25_score,
        c.vector_score
    FROM combined c
    JOIN documents d ON d.id = c.id
    ORDER BY c.combined_score DESC
    LIMIT p_limit;
END;
$$ LANGUAGE plpgsql STABLE;

3. 통계 뷰

실시간 통계를 위한 materialized view:

  
CREATE MATERIALIZED VIEW tenant_statistics AS
SELECT 
    t.id as tenant_id,
    t.name as tenant_name,
    COUNT(d.id) as document_count,
    SUM(LENGTH(d.content)) / 1024 / 1024 as total_size_mb,
    COUNT(CASE WHEN d.embedding IS NOT NULL THEN 1 END) as embedded_documents,
    MAX(d.created_at) as last_document_created,
    (
        SELECT SUM(total_cost)
        FROM cost_tracking ct
        WHERE ct.tenant_id = t.id
          AND ct.timestamp >= DATE_TRUNC('month', NOW())
    ) as monthly_cost
FROM tenants t
LEFT JOIN documents d ON d.tenant_id = t.id
WHERE t.status = 'active'
GROUP BY t.id, t.name;

-- 인덱스
CREATE INDEX idx_tenant_statistics_tenant_id 
    ON tenant_statistics(tenant_id);

-- 정기 갱신 (매시간)
CREATE OR REPLACE FUNCTION refresh_tenant_statistics()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY tenant_statistics;
END;
$$ LANGUAGE plpgsql;

데이터베이스 초기화 스크립트

  
-- init.sql
-- PostgreSQL 16 + pgvector 초기화 스크립트

-- 확장 활성화
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;  -- 유사 문자열 검색

-- 함수: updated_at 자동 갱신
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- tenants 테이블
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    settings JSONB DEFAULT '{}',
    max_documents INTEGER,
    max_storage_mb INTEGER,
    monthly_budget_usd DECIMAL(10, 2),
    status TEXT NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- documents 테이블
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    source TEXT,
    embedding vector(1536),
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT documents_title_not_empty CHECK (length(trim(title)) > 0),
    CONSTRAINT documents_content_not_empty CHECK (length(trim(content)) > 0)
);

-- RLS 활성화
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_policy ON documents
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid)
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- 인덱스 생성
CREATE INDEX idx_documents_tenant_id ON documents(tenant_id);
CREATE INDEX idx_documents_tenant_created ON documents(tenant_id, created_at DESC);
CREATE INDEX idx_documents_embedding ON documents 
    USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
CREATE INDEX idx_documents_content_fts ON documents 
    USING gin(to_tsvector('english', content));
CREATE INDEX idx_documents_metadata ON documents USING gin(metadata);

-- 트리거 생성
CREATE TRIGGER update_documents_updated_at
    BEFORE UPDATE ON documents
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_tenants_updated_at
    BEFORE UPDATE ON tenants
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- audit_logs 테이블
CREATE TABLE audit_logs (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    tenant_id UUID NOT NULL,
    user_id TEXT NOT NULL,
    action TEXT NOT NULL,
    resource TEXT,
    resource_id UUID,
    details JSONB DEFAULT '{}',
    success BOOLEAN NOT NULL,
    error_message TEXT,
    ip_address INET,
    user_agent TEXT,
    duration_ms INTEGER
);

CREATE INDEX idx_audit_logs_timestamp ON audit_logs(timestamp DESC);
CREATE INDEX idx_audit_logs_tenant_timestamp ON audit_logs(tenant_id, timestamp DESC);

-- cost_tracking 테이블
CREATE TABLE cost_tracking (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    tenant_id UUID NOT NULL,
    user_id TEXT NOT NULL,
    model_name TEXT NOT NULL,
    provider TEXT NOT NULL,
    input_tokens INTEGER NOT NULL,
    output_tokens INTEGER NOT NULL,
    total_tokens INTEGER GENERATED ALWAYS AS (input_tokens + output_tokens) STORED,
    input_cost DECIMAL(10, 6) NOT NULL,
    output_cost DECIMAL(10, 6) NOT NULL,
    total_cost DECIMAL(10, 6) GENERATED ALWAYS AS (input_cost + output_cost) STORED,
    operation TEXT,
    metadata JSONB DEFAULT '{}'
);

CREATE INDEX idx_cost_tracking_timestamp ON cost_tracking(timestamp DESC);
CREATE INDEX idx_cost_tracking_tenant_timestamp ON cost_tracking(tenant_id, timestamp DESC);

-- 초기 데이터
INSERT INTO tenants (name, slug, settings, monthly_budget_usd)
VALUES 
    ('Demo Tenant', 'demo', '{"tier": "free"}'::jsonb, 50.00),
    ('Enterprise Tenant', 'enterprise', '{"tier": "enterprise"}'::jsonb, 500.00);

SELECT 'Database initialized successfully!' as status;

마이그레이션 전략

버전 관리 (golang-migrate)

  
# 마이그레이션 생성
migrate create -ext sql -dir migrations -seq create_documents

# 마이그레이션 실행
migrate -path migrations -database "postgres://user:pass@localhost/db" up

# 롤백
migrate -path migrations -database "postgres://user:pass@localhost/db" down 1

  
-- migrations/000001_create_documents.up.sql
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    embedding vector(1536),
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- migrations/000001_create_documents.down.sql
DROP TABLE IF EXISTS documents;

백업 및 복구

  
# 전체 백업
pg_dump -U mcp_user -d mcp_db -F c -f backup_$(date +%Y%m%d).dump

# 특정 테이블 백업
pg_dump -U mcp_user -d mcp_db -t documents -F c -f documents_backup.dump

# 복구
pg_restore -U mcp_user -d mcp_db -c backup_20241213.dump

# 지속적 백업 (WAL archiving)
# postgresql.conf
# wal_level = replica
# archive_mode = on
# archive_command = 'cp %p /backup/wal/%f'

성능 모니터링

  
-- 느린 쿼리 찾기
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 100  -- 100ms 이상
ORDER BY mean_exec_time DESC
LIMIT 20;

-- 인덱스 사용률
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- 테이블 크기
SELECT 
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 캐시 히트율 (95% 이상 권장)
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as cache_hit_ratio
FROM pg_statio_user_tables;

핵심 요약

테이블 구조

✅ documents: 문서 + 임베딩 + 메타데이터
✅ audit_logs: 감사 추적
✅ cost_tracking: 비용 모니터링
✅ tenants: 테넌트 관리

보안 기능

✅ RLS: 데이터베이스 레벨 격리
✅ 체크 제약: 데이터 무결성
✅ 감사 로깅: 모든 작업 추적

성능 최적화

✅ 벡터 인덱스: IVFFlat (100배 이상 빠름)
✅ 전문 검색 인덱스: GIN (200배 이상 빠름)
✅ 복합 인덱스: 자주 사용하는 조합
✅ 파티셔닝: 대용량 데이터

운영

✅ 마이그레이션: 버전 관리
✅ 백업: 정기 백업 + WAL
✅ 모니터링: 성능 지표 추적

참고 자료:

pgvector GitHub: https://github.com/pgvector/pgvector
PostgreSQL 공식 문서: https://www.postgresql.org/docs/16/
RLS 가이드: https://www.postgresql.org/docs/16/ddl-rowsecurity.html

작성일: 2024년 12월 13일

AI, MCP & A2A

AI MCP A2A Guide MCP-A2A-Guide Medium Claude.write