PostgreSQL 실전

# PostgreSQL 실전 가이드

## 학습 목표
강력한 오픈소스 관계형 데이터베이스 PostgreSQL을 마스터합니다.

## 1. PostgreSQL이란?
가장 진보된 오픈소스 관계형 데이터베이스입니다.
- ACID 완벽 지원
- JSON/JSONB 네이티브 지원
- 강력한 확장성
- 풍부한 데이터 타입

## 2. 설치 및 접속
Docker:
docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=password postgres

접속:
psql -U postgres -h localhost
psql -d mydb -U myuser

기본 명령:
\l - 데이터베이스 목록
\c mydb - 데이터베이스 전환
\dt - 테이블 목록
\d users - 테이블 구조
\q - 종료

## 3. 데이터베이스 관리
CREATE DATABASE mydb;
DROP DATABASE mydb;
CREATE USER myuser WITH PASSWORD "password";
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

## 4. 테이블 생성
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
age INTEGER CHECK (age >= 0),
balance DECIMAL(10,2) DEFAULT 0,
metadata JSONB,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
title VARCHAR(255) NOT NULL,
content TEXT,
tags TEXT[],
published_at TIMESTAMP
);

## 5. 기본 CRUD
INSERT:
INSERT INTO users (name, email, age) VALUES ("Kim", "kim@test.com", 25);
INSERT INTO users (name, email) VALUES ("Lee", "lee@test.com") RETURNING id;

SELECT:
SELECT * FROM users;
SELECT name, email FROM users WHERE age >= 20;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;

UPDATE:
UPDATE users SET age = 26 WHERE name = "Kim";
UPDATE users SET age = age + 1 RETURNING *;

DELETE:
DELETE FROM users WHERE id = 1;

## 6. JSON/JSONB 활용
저장:
INSERT INTO users (name, metadata) VALUES ("Kim",
{"address": {"city": "Seoul"}, "tags": ["vip", "premium"]}
);

조회:
SELECT metadata->"address"->"city" FROM users;
SELECT metadata->>"address" FROM users;
SELECT * FROM users WHERE metadata @> {"tags": ["vip"]};
SELECT * FROM users WHERE metadata ? "address";

수정:
UPDATE users SET metadata = jsonb_set(metadata, "{address,zip}", "12345");

## 7. 배열 타입
저장:
INSERT INTO posts (title, tags) VALUES ("Hello", ARRAY["tech", "news"]);

조회:
SELECT * FROM posts WHERE "tech" = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY["tech"];
SELECT unnest(tags) FROM posts;

## 8. 인덱스
기본 인덱스:
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

복합 인덱스:
CREATE INDEX idx_posts_user_date ON posts(user_id, published_at DESC);

JSONB 인덱스:
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

부분 인덱스:
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

## 9. JOIN
INNER JOIN:
SELECT u.name, p.title FROM users u INNER JOIN posts p ON u.id = p.user_id;

LEFT JOIN:
SELECT u.name, COUNT(p.id) FROM users u LEFT JOIN posts p ON u.id = p.user_id GROUP BY u.id;

## 10. CTE와 윈도우 함수
CTE:
WITH active_users AS (
SELECT * FROM users WHERE last_login > NOW() - INTERVAL "30 days"
)
SELECT * FROM active_users WHERE age > 20;

윈도우 함수:
SELECT name, age, RANK() OVER (ORDER BY age DESC) FROM users;
SELECT name, age, AVG(age) OVER () as avg_age FROM users;
SELECT name, created_at, LAG(created_at) OVER (ORDER BY created_at) as prev FROM users;

## 11. 트랜잭션
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 에러 시
ROLLBACK;

격리 수준:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

## 12. 백업 및 복원
백업:
pg_dump -U postgres mydb > backup.sql
pg_dump -U postgres -Fc mydb > backup.dump

복원:
psql -U postgres mydb < backup.sql
pg_restore -U postgres -d mydb backup.dump

## 추천 학습 순서
1. 기본 SQL 문법
2. JSON/배열 타입 활용
3. 인덱스 전략
4. 윈도우 함수
5. 성능 최적화