🐘 PostgreSQL 완전 가이드

-- 특정 테이블 수동 VACUUM (일반적으로는 autovacuum 데몬이 백그라운드 처리)
VACUUM ANALYZE employees;

-- 풀 테이블 락을 점유하고 사용되지 않는 디스크 공간까지 OS에 반환
VACUUM FULL employees;

-- CTE를 활용한 계층 구조/임시 집합 정의
WITH dept_salaries AS (
    SELECT dept_id, SUM(salary) AS total_sal
    FROM employees
    GROUP BY dept_id
)
SELECT d.dept_name, ds.total_sal
FROM departments d
INNER JOIN dept_salaries ds ON d.dept_id = ds.dept_id;

-- Window 함수를 활용하여 부서 내 급여 순위 산출
SELECT emp_name, dept_id, salary,
       RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS sal_rank
FROM employees;

-- 테이블에 jsonb 컬럼 추가
CREATE TABLE user_profiles (
    user_id SERIAL PRIMARY KEY,
    settings JSONB
);

-- jsonb 데이터 삽입
INSERT INTO user_profiles (settings) 
VALUES ('{"theme": "dark", "notifications": {"email": true, "push": false}}');

-- jsonb 내부 키 검색 (email notification이 true인 프로필 조회)
SELECT user_id 
FROM user_profiles 
WHERE settings -> 'notifications' ->> 'email' = 'true';

-- GIN 인덱스 생성 (JSONB 성능 극대화)
CREATE INDEX idx_user_settings ON user_profiles USING gin (settings);

-- pgvector 확장 기능 활성화
CREATE EXTENSION IF NOT EXISTS vector;

-- 1536차원 임베딩을 저장할 컬럼을 가진 테이블 생성
CREATE TABLE document_embeddings (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)
);

-- 코사인 유사도 거리 연산자(<=>)를 활용해 가장 유사한 문서 5개 검색
SELECT content, 1 - (embedding <=> '[0.015, -0.021, ...]') AS cosine_similarity
FROM document_embeddings
ORDER BY embedding <=> '[0.015, -0.021, ...]'
LIMIT 5;

-- 1. Unified Role 계정 관리
CREATE ROLE readonly_role; -- 로그인 권한이 없는 그룹/롤
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;

CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_pass'; -- 로그인 권한이 있는 사용자 역할
GRANT readonly_role TO app_user; -- app_user는 readonly_role의 모든 권한을 상속받음

-- 2. 미래에 생성되는 테이블에 자동 권한 지정 (ALTER DEFAULT PRIVILEGES)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role;

-- 3. 행 수준 보안 (Row-Level Security) 설정
-- 사용자별로 본인 데이터 행만 조회하게 설정합니다.
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;

-- RLS Policy 정책 설정 (세션 유저가 profile의 owner_name과 일치하는 행만 허용)
CREATE POLICY user_profile_isolation_policy ON user_profiles
    FOR ALL
    USING (owner_name = CURRENT_USER);

-- 4. 정책 활성/비활성 여부 조회
SELECT relname, relrowsecurity FROM pg_class WHERE relrowsecurity = true;

-- 1. 현재 접속된 데이터베이스의 인코딩 및 로케일 확인
SELECT 
    datname, 
    pg_encoding_to_char(encoding) AS encoding_name, 
    datcollate, 
    datctype 
FROM pg_database 
WHERE datname = current_database();

-- 2. 신규 데이터베이스 생성 시 인코딩 및 로케일 지정
-- UTF8: 이모지를 포함한 다국어 표준 인코딩
-- C / POSIX: 로케일 정렬을 시스템 규칙 대신 단순 바이트 정렬로 설정하여 인덱스 정렬 연산 및 LIKE 검색 성능 극대화
CREATE DATABASE global_db
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'C'
    LC_CTYPE = 'C';

-- 3. 특정 컬럼에 개별 정렬(Collation) 적용하기 (PostgreSQL은 컬럼별 Collation 오버라이딩 지원)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) COLLATE "C", -- 단순 바이트 비교 정렬 적용
    display_name VARCHAR(100) COLLATE "ko_KR.utf8" -- 한국어 규칙 기준 정렬 적용
);

-- 4. 현재 클라이언트 연결 세션의 인코딩 조회 및 강제 설정
SHOW client_encoding;
SET client_encoding = 'UTF8';