PostgreSQL 인 딥다이브

gyumingim·2025년 6월 17일

깊이있게 배워보아요~

🥵 내용이 매우 방대합니다.

1. PostgeSQL

오픈소스로 개발되고 있는 RDBMS(관계형 데이터베이스)

장점

⭐⭐⭐무료⭐⭐⭐
거의 모든 SQL 기능 제공 (86% 제공, 오라클이 71%)
최다 ANSI SQL 표준 지원 (179개 중 170개, 95%)
객체 관계형 데이터베이스 관리 시스템(ORDBMS)
사용자 정의 가능
다양한 OS에서 사용 가능
최고수준의 ACID를 준수
다양한 데이터 포맷 (JSON, JSONB 지원...!)

단점

단순한 쿼리(CRUD)는 MySQL보다 느림

postgresql의 update는 값이 바뀌는 것이 아닌, 값을 delete한 후 insert하는 방식임

2. PostgreSQL 설치 / 기본 셋팅

[Database] PostgreSQL 입문 (with 생활코딩) - (1) 개념, 설치, 접속

이거 보면 에러 없이 설치할 수 있다

~~내가 보증함~~

쿼리 돌리려면 2편까지 봐야한다

3. 기초적인 CRUD

간단한 유저 정보 Table을 만들어서 CRUD를 배워보자

CREATE

id, title, body, created 로 구성된 테이블을 만들어봅시다

요소는 이름 데이터타입 제약조건 으로 구성되고 제약조건은 없어도 된다

CREATE TABLE public.topic (
  id serial NOT NULL, // 자동 입력
  title character varying(50) NOT NULL,
  body text,
  created timestamp with time zone NOT NULL DEFAULT now(), // 자동 입력
  PRIMARY KEY (id)
);

제약조건을 거는 방법은 2가지가 있다

바로 제약조건 걸기 id serial NOT NULL

마지막줄에 제약조건 걸기 PRIMARY KEY (id)

INSERT

테이블에 값을 INSERT 해봅시다
id랑 created는 기본으로 채워지는 값이다. 따로 채우기 ㄴㄴ

INSERT INTO table (title, body) VALUES('타이틀입니다1', '바디에용1');
INSERT INTO table (title, body) VALUES('타이틀입니다2', '바디에용2');

SELECT (READ)

INSERT한 걸 조회해봅시다.
조회할 땐 특정 컬럼만 조회할 수 있습니다. 전체도 가능

// 전체 조회
SELECT * FROM topic;

id	title	body	created
1	타이틀입니다1	바디에용1	2024-01-01 01:01:01
2	타이틀입니다2	바디에용2	2024-01-01 01:01:02

// 특정 컬럼 조회
SELECT title, body FROM topic;

title	body
타이틀입니다1	바디에용1
타이틀입니다2	바디에용2

UPDATE

기존 들어가있는 레코드를 수정할 수 있습니다

SET : 수정할 컬럼의 내용을 적습니다
WHERE : 수정할 레코드의 조건을 적습니다

UPDATE topic 
SET title = '저는 사실 타이틀이 아닙니다!!', body = '저도요!!' 
WHERE id = 2;

id 2의 내용 변화

title	body
저는 사실 타이틀이 아닙니다!!	저도요!!

DELETE

당연히 레코드를 삭제할 수도 있습니다

DELETE FROM topic WHERE id=1;

WHERE 조건에 걸린 레코드가 1개 이상이여도 다 삭제합니다

4. 기초적인 WHERE

이 장에서는 SQL에서 연산자가 무엇이 있는지 알려줍니다

예제를 실행하기 위한 테이블 생성

// products 테이블 생성
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category VARCHAR(100) NOT NULL
);
// products 테이블에 데이터 삽입
INSERT INTO products (product_name, price, category) VALUES
('Laptop Pro X', 1200.00, 'Electronics'),
('Mechanical Keyboard', 150.00, 'Electronics'),
('Wireless Mouse', 50.00, 'Accessories'),
('Smart TV 55"', 999.99, 'Electronics'),
('Coffee Maker', 80.50, 'Home Appliances'),
('Smartphone XL', 1100.00, 'Electronics');

비교 연산자, 논리 연산자

일반적인 프로그래밍 언어와 똑같이 비교·논리 연산자를 사용할 수 있다
괄호()도 사용할 수 있다

// 비교 연산자
= : 같다
!=, <> : 같지 않다
> : 보다 크다
< : 보다 작다
>= : 보다 크거나 같다
<= : 보다 작거나 같다

// 논리 연산자
AND : 모든 조건이 참일 때 참
OR : 하나 이상의 조건이 참일 때 참
NOT : 조건이 거짓일 때 참 (조건을 반전)

활용

// 가격이 1000 이상이고, 카테고리가 'Electronics'인 제품 조회
SELECT product_name, price, category
FROM products
WHERE price >= 1000 AND category = 'Electronics';

// 상태가 'pending' 또는 'processing'인 주문 조회
SELECT order_id, status
FROM orders
WHERE status = 'pending' OR status = 'processing';

범위 조건, 목록 조건, 패턴 매칭

이런 파트를 일일이 작성하니까 모던 JS 딥다이브가 생각난다
어떻게 사용하는지 외우지 않아도 된다. 이런 함수의 존재만 알면 된다

// 범위 조건
BETWEEN A AND B : A와 B 사이 (A와 B 포함)
NOT BETWEEN A AND B : A와 B 사이가 아닌 값

// 목록 조건
IN (value1, value2, ...) : 목록 내의 값 중 하나와 일치
NOT IN (value1, value2, ...) : 목록 내의 값 중 어느 것과도 일치하지 않음

// 패턴 매칭
LIKE 'pattern' : 패턴과 일치
ILIKE 'pattern' : 대소문자를 구분하지 않고 패턴과 일치 (PostgreSQL 전용)
NOT LIKE 'pattern' : 패턴과 일치하지 않음
NOT ILIKE 'pattern' : 대소문자를 구분하지 않고 패턴과 일치하지 않음

활용

// 2023년 1월 1일부터 2023년 12월 31일까지의 주문 조회
SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

// 상태가 'cancelled' 또는 'refunded'가 아닌 주문 조회
SELECT order_id, status
FROM orders
WHERE status NOT IN ('cancelled', 'refunded');

// 'Pro'로 시작하는 제품 이름 조회
SELECT product_name
FROM products
WHERE product_name LIKE 'Pro%';

// 'monitor'를 포함하는 제품 이름 조회 (대소문자 구분 없음)
SELECT product_name
FROM products
WHERE product_name ILIKE '%monitor%';

// 정확히 5글자로 이루어진 도시 이름 조회
SELECT city_name
FROM cities
WHERE city_name LIKE '_____'; -- 밑줄 5개

NULL 값 처리

값이 NULL이거나 NULL이 아닌 데이터를 찾아준다
백엔드에서 데이터를 처리하지 않기 떄문에 성능에 좋을 거 같다

IS NULL : 값이 NULL인지 확인
IS NOT NULL : 값이 NULL이 아닌지 확인

활용

-- 배송 주소가 아직 설정되지 않은 주문 조회
SELECT order_id, delivery_address
FROM orders
WHERE delivery_address IS NULL;

-- 이메일 주소가 있는 사용자 조회
SELECT username, email
FROM users
WHERE email IS NOT NULL;

서브쿼리, JOIN

서브쿼리를 사용해서
서브쿼리

-- 특정 카테고리의 제품을 주문한 고객의 정보 조회
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE c.customer_id IN (
    SELECT o.customer_id
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE p.category = 'Electronics'
);

-- 평균 가격보다 비싼 제품 조회
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

조인

-- 'Electronics' 카테고리의 제품을 포함하는 주문과 고객 정보 조회
SELECT o.order_id, c.customer_name, p.product_name, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.category = 'Electronics';

인덱스

인덱스 생성: WHERE 절에서 자주 사용되는 컬럼에 인덱스를 생성합니다.
SQL

CREATE INDEX idx_products_category ON products (category);
CREATE INDEX idx_users_email ON users (email);

다중 컬럼 인덱스: 여러 컬럼을 AND 조건으로 함께 필터링할 때 다중 컬럼 인덱스를 고려합니다.

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

날짜/시간 데이터 타입 활용

날짜 및 시간 데이터는 비교 연산, BETWEEN, 특정 날짜 함수와 함께 사용됩니다.

DATE : 날짜 (YYYY-MM-DD)
TIME : 시간 (HH:MI:SS)
TIMESTAMP : 날짜 및 시간 (YYYY-MM-DD HH:MI:SS)
TIMESTAMPTZ : 시간대 정보 포함 날짜 및 시간

활용

-- 오늘 이후 생성된 주문 조회
SELECT order_id, order_date
FROM orders
WHERE order_date >= CURRENT_DATE;

-- 특정 월에 생성된 주문 조회
SELECT order_id, order_date
FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 6 AND EXTRACT(YEAR FROM order_date) = 2024;

CASE 문과 WHERE 절

WHERE 절 내에서 CASE 문을 직접적으로 사용하여 복잡한 논리 조건을 구현하기는 어렵습니다. CASE 문은 주로 SELECT 절에서 값을 변환하거나, ORDER BY 절에서 정렬 순서를 동적으로 결정할 때 사용됩니다.

하지만 CASE 문으로 만들어진 결과를 서브쿼리나 CTE (Common Table Expression)로 만들어 외부 WHERE 절에서 필터링하는 방식은 가능합니다.

활용

-- CTE를 사용하여 조건에 따라 분류된 후 필터링
WITH categorized_products AS (
    SELECT
        product_id,
        product_name,
        price,
        CASE
            WHEN price >= 1000 THEN 'Expensive'
            WHEN price BETWEEN 500 AND 999 THEN 'Moderate'
            ELSE 'Cheap'
        END AS price_category
    FROM products
)
SELECT product_id, product_name, price
FROM categorized_products
WHERE price_category = 'Expensive';

배열 컬럼 (ARRAY) 조건

PostgreSQL은 배열 데이터 타입을 지원하며, 이에 대한 WHERE 절 필터링도 가능합니다.

ANY / ALL : 배열 내의 어떤 요소라도/모든 요소가 조건 만족
@> : 배열이 다른 배열을 포함하는지 확인 (contains)
<@ : 배열이 다른 배열에 포함되는지 확인 (is contained by)
&& : 배열이 공통 요소를 가지는지 확인 (overlap)

활용

-- tags 컬럼(text array)에 'electronics' 태그가 포함된 제품 조회
SELECT product_name, tags
FROM products
WHERE 'electronics' = ANY(tags);

-- tags 컬럼에 'sale'과 'new_arrival' 태그가 모두 포함된 제품 조회
SELECT product_name, tags
FROM products
WHERE tags @> ARRAY['sale', 'new_arrival']::text[];

-- tags 컬럼에 'sale' 또는 'clearance' 태그가 포함된 제품 조회
SELECT product_name, tags
FROM products
WHERE tags && ARRAY['sale', 'clearance']::text[];

5. 기초적인 ORDER BY

1. 기본 ORDER BY 절 (Basic ORDER BY Clause)

가장 기본적인 형태로, 하나 이상의 컬럼을 기준으로 결과 집합의 순서를 정렬합니다. 기본 정렬 순서는 오름차순(ASCending)이며, 내림차순(DESCending)으로 지정할 수도 있습니다.

예시:

-- 제품 이름을 오름차순으로 정렬
SELECT product_name, price
FROM products
ORDER BY product_name ASC; -- ASC는 생략 가능

-- 가격을 내림차순으로 정렬
SELECT product_name, price
FROM products
ORDER BY price DESC;

2. 여러 컬럼으로 정렬 (Sorting by Multiple Columns)

두 개 이상의 컬럼을 기준으로 정렬할 수 있습니다. 첫 번째 컬럼으로 정렬한 후, 같은 값을 가진 행들은 두 번째 컬럼으로 정렬하는 식입니다.

예시:

-- 카테고리를 오름차순으로 정렬하고, 같은 카테고리 내에서는 가격을 내림차순으로 정렬
SELECT category, product_name, price
FROM products
ORDER BY category ASC, price DESC;

-- 주문 날짜를 내림차순으로 정렬하고, 같은 날짜 내에서는 총 금액을 오름차순으로 정렬
SELECT order_date, total_amount, customer_id
FROM orders
ORDER BY order_date DESC, total_amount ASC;

3. 컬럼 번호로 정렬 (Sorting by Column Number)

SELECT 절에 명시된 컬럼의 순서(번호)를 사용하여 정렬할 수도 있습니다. 가독성을 위해 컬럼 이름을 사용하는 것을 권장하지만, 경우에 따라 유용할 수 있습니다.

예시:

-- 첫 번째 컬럼(product_name)을 오름차순, 두 번째 컬럼(price)을 내림차순으로 정렬
SELECT product_name, price, category
FROM products
ORDER BY 1 ASC, 2 DESC;

4. 표현식 또는 함수로 정렬 (Sorting by Expression or Function)

단순한 컬럼뿐만 아니라, 계산된 값이나 함수 결과에 따라 정렬할 수 있습니다. 이는 데이터를 특정 로직에 따라 유연하게 정렬할 때 유용합니다.

예시:

-- 제품 이름의 길이를 기준으로 오름차순 정렬
SELECT product_name, LENGTH(product_name) AS name_length
FROM products
ORDER BY LENGTH(product_name) ASC;

-- 주문 날짜의 월(Month)을 기준으로 정렬 (예: 1월, 2월, ...)
SELECT order_id, order_date
FROM orders
ORDER BY EXTRACT(MONTH FROM order_date) ASC;

-- (price * stock_quantity) 값을 기준으로 내림차순 정렬
SELECT product_name, price, stock_quantity, (price * stock_quantity) AS total_value
FROM products
ORDER BY (price * stock_quantity) DESC;

5. CASE 문을 이용한 동적 정렬 (Dynamic Sorting with CASE)

ORDER BY 절에서 CASE 문을 활용하면 특정 조건에 따라 정렬 순서를 동적으로 변경할 수 있습니다. 이는 실무에서 매우 강력하고 유연한 정렬 방식을 제공합니다.

활용 예시:

-- 제품 상태(status)에 따라 정렬 우선순위를 부여
-- 'New' > 'Sale' > 'Regular' 순서로 정렬하고, 그 외는 제품 이름으로 정렬
SELECT product_name, status, price
FROM products
ORDER BY
    CASE status
        WHEN 'New' THEN 1
        WHEN 'Sale' THEN 2
        WHEN 'Regular' THEN 3
        ELSE 4 -- 기타 상태는 낮은 우선순위
    END ASC,
    product_name ASC; -- 동일한 우선순위 내에서 제품 이름으로 2차 정렬

-- 재고 수량(stock_quantity)이 0인 제품을 가장 뒤로 보내고, 나머지는 가격 내림차순으로 정렬
SELECT product_name, stock_quantity, price
FROM products
ORDER BY
    CASE
        WHEN stock_quantity = 0 THEN 1 -- 재고 0은 가장 낮은 우선순위 (뒤로 보냄)
        ELSE 0 -- 나머지는 높은 우선순위
    END ASC,
    price DESC; -- 재고가 있는 제품들은 가격 내림차순

6. NULL 값의 정렬 순서 (NULLs Ordering)

PostgreSQL은 NULL 값을 ORDER BY 절에서 어떻게 처리할지 명시적으로 지정할 수 있는 옵션을 제공합니다.

NULLS FIRST : NULL 값을 가장 먼저 정렬
NULLS LAST : NULL 값을 가장 나중에 정렬 (기본 동작)

예시:

-- 이메일 주소(email)를 오름차순 정렬하되, NULL 값은 가장 먼저 나오도록
SELECT username, email
FROM users
ORDER BY email ASC NULLS FIRST;

-- 배송 주소(delivery_address)를 내림차순 정렬하되, NULL 값은 가장 나중에 나오도록
SELECT order_id, delivery_address
FROM orders
ORDER BY delivery_address DESC NULLS LAST; -- NULLS LAST는 DESC의 기본 동작

7. 인덱스 (Indexes) 활용 및 성능 고려

ORDER BY 절은 WHERE 절과 마찬가지로 쿼리 성능에 큰 영향을 미칩니다. 적절한 인덱스 사용은 정렬 작업의 효율성을 크게 높여줍니다.

인덱스 생성: ORDER BY 절에서 자주 사용되는 컬럼에 단일 컬럼 인덱스나 다중 컬럼 인덱스를 생성합니다.
```
CREATE INDEX idx_products_price ON products (price);
CREATE INDEX idx_orders_date_amount ON orders (order_date DESC, total_amount ASC);
```
팁: ORDER BY 절의 정렬 순서(ASC/DESC)에 맞춰 인덱스를 생성하면 더욱 효율적일 수 있습니다.
다중 컬럼 인덱스: ORDER BY 절에 여러 컬럼이 사용될 경우, 해당 컬럼들의 순서대로 다중 컬럼 인덱스를 생성하는 것이 유리합니다. 인덱스 컬럼의 순서가 중요합니다.
함수 사용 주의: ORDER BY 절에서 컬럼에 함수를 적용하면 일반적으로 인덱스를 활용하기 어렵습니다. (예: ORDER BY LENGTH(product_name)). 이런 경우, 필요하다면 함수 기반 인덱스(Functional Index)를 고려할 수 있습니다.
```
-- product_name 길이에 대한 함수 기반 인덱스 (PostgreSQL)
CREATE INDEX idx_products_name_length ON products (LENGTH(product_name));
```
EXPLAIN ANALYZE: 쿼리가 어떻게 실행되는지 분석하여 정렬 작업(Sort)이 발생하는지, 그리고 인덱스를 사용하는지 확인합니다. 대량의 데이터에서 Sort 작업이 자주 발생한다면 성능 병목일 수 있습니다.

8. `LIMIT` 및 `OFFSET`과 함께 사용

페이징(Pagination) 기능을 구현할 때 ORDER BY는 LIMIT (조회할 행의 수 제한) 및 OFFSET (건너뛸 행의 수)과 함께 거의 필수적으로 사용됩니다.

예시:

-- 가장 최신 주문 10개를 조회
SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 10;

-- 두 번째 페이지의 주문 10개 조회 (11번째부터 20번째 주문)
SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 10 OFFSET 10;

6. 기초적인 DATA TYPE

PostgreSQL 데이터 타입, 왜 중요할까?

데이터 타입은 데이터베이스 테이블의 각 컬럼이 어떤 종류의 데이터를 저장할지 정의하는 데 사용됩니다. 올바른 데이터 타입을 선택하는 것은 다음과 같은 이유로 매우 중요합니다:

데이터 무결성(Data Integrity): 잘못된 데이터가 저장되는 것을 방지합니다.
성능(Performance): 데이터 타입에 따라 저장 공간, 인덱싱 효율성, 쿼리 처리 속도가 달라집니다.
저장 공간 효율성(Storage Efficiency): 필요 이상으로 큰 데이터 타입을 사용하면 디스크 공간을 낭비하고 I/O 비용을 증가시킵니다.
기능성(Functionality): 각 데이터 타입에 맞는 함수와 연산자를 활용할 수 있습니다.

주요 PostgreSQL 데이터 타입 및 실무 활용

PostgreSQL은 매우 풍부한 데이터 타입을 제공합니다. 실무에서 자주 사용되는 주요 데이터 타입과 그 활용법, 그리고 고려사항을 알아보겠습니다.

1. 숫자형 (Numeric Types)

정수, 소수점 등 수치를 저장하는 데 사용됩니다. 크기와 정밀도에 따라 여러 종류가 있습니다.

SMALLINT: -32,768에서 32,767까지의 정수 (2바이트).
- 활용: 작은 범위의 ID, 상태 코드, 짧은 카운터 등
- 고려사항: 범위가 작으므로 신중하게 사용해야 합니다.
INTEGER (or INT): -2,147,483,648에서 2,147,483,647까지의 정수 (4바이트). 가장 일반적으로 사용되는 정수 타입입니다.
- 활용: 대부분의 ID, 수량, 일반적인 카운터 등
- 고려사항: 충분히 넓은 범위를 제공하지만, 아주 큰 값이 예상되면 다음 타입을 고려하세요.
BIGINT: -9,223,372,036,854,775,808에서 9,223,372,036,854,775,807까지의 정수 (8바이트).
- 활용: 대규모 시스템의 ID (ex. 사용자 수십억 명 이상), 매우 큰 카운터 등
- 고려사항: INTEGER보다 2배의 저장 공간을 사용하므로, 정말 필요할 때만 사용하세요.
SERIAL / BIGSERIAL: 자동으로 증가하는 정수 시퀀스를 생성합니다. 각각 INTEGER와 BIGINT 기반입니다. 주로 PRIMARY KEY에 사용됩니다.
- 활용: 테이블의 고유 식별자 (ID)
- 고려사항: IDENTITY 컬럼이 SQL 표준에 더 가깝지만, SERIAL은 PostgreSQL에서 널리 사용됩니다.
```
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY, -- INTEGER 기반 자동 증가
    username VARCHAR(50)
);

CREATE TABLE products (
    product_id BIGSERIAL PRIMARY KEY, -- BIGINT 기반 자동 증가
    product_name VARCHAR(100)
);
```
NUMERIC(p, s) (or DECIMAL(p, s)): 정확한 소수점 값을 저장합니다. p는 총 자릿수(precision), s는 소수점 이하 자릿수(scale)입니다. (예: NUMERIC(5, 2)는 999.99까지 저장)
- 활용: 금융 데이터, 화폐, 정밀한 계산이 필요한 수치 (예: 가격, 세금, 비율)
- 고려사항: 계산 비용이 FLOAT 계열보다 높지만, 정밀도 손실이 없어 정확성이 중요할 때 사용합니다.
REAL (4바이트) / DOUBLE PRECISION (8바이트): 근사치(부동 소수점) 숫자를 저장합니다.
- 활용: 과학 계산, 지리 좌표 (위도, 경도) 등 정밀도가 아주 중요하지 않은 대략적인 소수점 값
- 고려사항: 부동 소수점 오차가 발생할 수 있으므로, 정확한 계산이 필요한 화폐 등에 사용하면 안 됩니다.

2. 문자열형 (Character Types)

텍스트 데이터를 저장하는 데 사용됩니다.

VARCHAR(n): 가변 길이 문자열. 최대 n 글자까지 저장합니다. n을 지정하지 않으면 최대 길이가 시스템이 허용하는 가장 큰 값이 됩니다.
- 활용: 이름, 주소, 설명, 짧은 메시지 등 대부분의 텍스트 데이터
- 고려사항: n을 너무 크게 지정해도 공간 낭비는 적지만, 인덱스 생성 시 영향을 줄 수 있습니다. 적절한 최대 길이를 지정하는 것이 좋습니다.
CHAR(n): 고정 길이 문자열. 항상 n 글자를 저장하며, 짧은 문자열이 들어오면 공백으로 채워집니다.
- 활용: 매우 드물게 사용 (예: 2글자 국가 코드 CHAR(2))
- 고려사항: 대부분의 경우 VARCHAR가 더 효율적입니다.
TEXT: 가변 길이 문자열. 이론적으로 최대 무제한 길이입니다.
- 활용: 긴 설명, 블로그 본문, JSON/XML 문자열 등
- 고려사항: 매우 긴 문자열에 적합하지만, 너무 긴 텍스트에 대한 인덱싱은 성능에 영향을 줄 수 있습니다.

3. 날짜/시간형 (Date/Time Types)

날짜, 시간, 또는 둘 다를 저장하는 데 사용됩니다.

DATE: 날짜 (년, 월, 일)를 저장합니다. (예: 2024-06-17)
- 활용: 생년월일, 특정 이벤트 발생일
TIME: 시간 (시, 분, 초)을 저장합니다. (예: 13:52:30)
- 활용: 업무 시작 시간, 특정 작업 소요 시간
TIMESTAMP (or TIMESTAMP WITHOUT TIME ZONE): 날짜와 시간을 저장합니다. 시간대 정보는 포함되지 않습니다.
- 활용: 이벤트 발생 시각, 생성/수정 시각 (시간대 독립적)
- 고려사항: 서버 시간대 설정에 따라 다르게 해석될 수 있으므로, 여러 시간대에 걸쳐 서비스를 제공한다면 다음 타입을 고려하세요.
TIMESTAMPTZ (or TIMESTAMP WITH TIME ZONE): 날짜와 시간, 그리고 시간대 정보를 함께 저장합니다. 입력된 시간은 UTC(협정 세계시)로 변환되어 저장되며, 조회 시 세션 시간대에 맞춰 변환됩니다.
- 활용: 전 세계 사용자를 위한 서비스에서 생성/수정 시각, 로그 시간
- 고려사항: 대부분의 실무 애플리케이션에서 시간대 문제를 피하기 위해 가장 권장되는 타입입니다.
INTERVAL: 시간 간격을 저장합니다.
- 활용: 경과 시간, 기간 계산
- 예시: INTERVAL '1 day', INTERVAL '5 hours 30 minutes'

4. 불리언형 (Boolean Type)

참/거짓 값을 저장합니다.

BOOLEAN (or BOOL): TRUE, FALSE, NULL 값을 저장합니다.
- 활용: 플래그 (예: is_active, is_published), 조건 상태
- 고려사항: PostgreSQL은 TRUE/FALSE 외에도 t/f, y/n, 1/0 등 다양한 문자열 및 숫자 리터럴을 자동으로 BOOLEAN으로 변환해줍니다.

5. JSON/JSONB (JavaScript Object Notation)

비정형 데이터를 JSON 형식으로 저장합니다.

JSON: 입력된 JSON 문자열을 그대로 저장합니다.
JSONB: JSON 데이터를 이진(Binary) 형태로 파싱하여 저장합니다. 쿼리 성능이 훨씬 빠르고 인덱싱이 가능합니다.
- 활용: 유연한 스키마가 필요한 데이터 (예: 사용자 설정, 제품 속성, 로그 데이터)
- 고려사항:
  - 대부분의 경우 JSONB를 사용하는 것이 강력히 권장됩니다. JSON은 입력 시 유효성 검사만 하고 원본 텍스트를 저장하는 반면, JSONB는 파싱하여 저장하므로 쓰기 시 부하가 더 크지만 읽기 및 쿼리 시 성능 이점이 큽니다.
  - ->, ->>, #>, #>> 등의 연산자를 사용하여 JSON 데이터를 쿼리할 수 있습니다.
  - GIN 인덱스를 활용하여 JSONB 필드 내의 특정 키/값에 대한 검색 성능을 최적화할 수 있습니다.
```
CREATE INDEX idx_products_metadata ON products USING GIN (metadata jsonb_path_ops);
```

6. 배열형 (Array Types)

동일한 타입의 값들을 배열 형태로 저장합니다.

VARCHAR[], INT[], TEXT[] 등: 모든 기본 데이터 타입 뒤에 []를 붙여 배열 타입을 생성할 수 있습니다.
- 활용: 여러 개의 태그, 연락처 번호 목록, 관련 ID 목록
- 고려사항:
  - 정규화(Normalization) 원칙에 따라 별도의 조인 테이블을 사용하는 것이 일반적이지만, 간단한 목록이나 비정형적인 다대다 관계에서 유용합니다.
  - 배열 내 요소 검색을 위해 GIN 인덱스를 활용할 수 있습니다.
```
CREATE INDEX idx_products_tags ON products USING GIN (tags);
```
  - 앞서 WHERE 절 설명에서 언급했듯이 ANY, ALL, @>, && 등의 연산자로 쿼리합니다.

7. UUID (Universally Unique Identifier)

전역적으로 고유한 식별자를 저장합니다.

UUID: 128비트 UUID (예: a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11)
- 활용: 분산 환경에서 고유한 ID를 생성할 때, 여러 데이터베이스 간의 충돌 없는 ID가 필요할 때
- 고려사항: SERIAL과 달리 순차적이지 않아 인덱싱 성능에 영향을 줄 수 있지만, pgcrypto 또는 uuid-ossp 확장 기능을 사용하여 DB에서 직접 UUID를 생성할 수 있습니다.
```
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE sessions (
    session_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INTEGER
);
```

7. 기초적인 제약조건

PostgreSQL 제약 조건, 왜 중요할까?

제약 조건은 테이블에 저장되는 데이터의 유효성과 일관성을 강제하는 규칙입니다. 이는 데이터베이스의 데이터 무결성(Data Integrity)을 유지하고, 잘못된 데이터가 삽입되거나 업데이트되는 것을 방지하는 데 필수적인 요소입니다.

제약 조건의 이점은 다음과 같습니다:

데이터 품질 향상: 잘못된 데이터 입력으로 인한 오류를 줄여줍니다.
애플리케이션 로직 단순화: 데이터 유효성 검사를 데이터베이스 레벨에서 처리하므로 애플리케이션 코드의 복잡성을 줄일 수 있습니다.
성능 최적화: 특정 제약 조건(예: PRIMARY KEY, UNIQUE)은 내부적으로 인덱스를 생성하여 쿼리 성능을 향상시킵니다.
데이터 일관성 보장: 여러 애플리케이션이나 사용자가 같은 데이터베이스를 사용할 때도 일관된 규칙을 적용할 수 있습니다.

주요 PostgreSQL 제약 조건 및 실무 활용

PostgreSQL에서 실무적으로 가장 자주 사용되는 제약 조건들을 하나씩 자세히 알아보겠습니다.

1. `PRIMARY KEY` (기본 키)

정의: 테이블의 각 행을 고유하게 식별하는 하나 이상의 컬럼(또는 컬럼 집합)을 지정하는 제약 조건입니다. PRIMARY KEY는 다음과 같은 특징을 가집니다:

UNIQUE (고유성): 기본 키로 지정된 컬럼의 모든 값은 고유해야 합니다. 중복된 값을 허용하지 않습니다.
NOT NULL (비어있지 않음): 기본 키로 지정된 컬럼은 NULL 값을 가질 수 없습니다.
인덱스 자동 생성: PostgreSQL은 PRIMARY KEY를 생성할 때 자동으로 UNIQUE B-tree 인덱스를 생성합니다. 이는 해당 컬럼을 이용한 검색 및 정렬 성능을 크게 향상시킵니다.

실무 활용:

테이블의 고유 식별자: 거의 모든 테이블은 PRIMARY KEY를 가져야 합니다. 이는 해당 테이블의 행을 참조하거나 다른 테이블과 관계를 맺을 때 필수적입니다.

단일 컬럼 기본 키: 가장 일반적인 형태입니다. 주로 SERIAL 또는 BIGSERIAL (자동 증가 정수)과 함께 사용됩니다.

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY, -- user_id는 자동으로 증가하며 고유하고 NULL이 될 수 없음
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

복합 기본 키 (Composite Primary Key): 둘 이상의 컬럼 조합이 하나의 행을 고유하게 식별할 때 사용합니다. 예를 들어, 주문 내역 테이블에서 order_id와 product_id의 조합이 각 품목을 고유하게 식별할 수 있습니다.
```
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    -- order_id와 product_id의 조합이 각 항목을 고유하게 식별
    PRIMARY KEY (order_id, product_id)
);
```
팁: 복합 기본 키를 사용할 때는 컬럼의 순서가 인덱싱에 영향을 미칠 수 있으므로, 쿼리에서 가장 자주 사용되는 컬럼을 앞에 두는 것이 좋습니다.

고려사항:

변경 불가능성: PRIMARY KEY 값은 일반적으로 생성된 후 변경되지 않도록 설계하는 것이 좋습니다. 변경 시 다른 테이블과의 외래 키(Foreign Key) 관계에 복잡성을 초래할 수 있습니다.
UUID vs. SERIAL: 분산 시스템이나 오프라인 작업이 필요한 경우 UUID를 PRIMARY KEY로 사용하는 것을 고려할 수 있습니다. SERIAL은 순차적인 번호라 예측 가능하지만, UUID는 전역적으로 고유성을 보장합니다.

2. `NOT NULL` (널 아님)

정의: 해당 컬럼이 NULL 값(데이터 없음)을 포함할 수 없도록 강제하는 제약 조건입니다. 컬럼에 반드시 값이 존재해야 할 때 사용합니다.

실무 활용:

필수 정보: 사용자 이름, 이메일, 제품명, 주문 금액 등 애플리케이션의 핵심적인 비즈니스 로직에 필요한 정보는 NULL이 될 수 없도록 NOT NULL 제약 조건을 적용해야 합니다.

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL, -- 제품명은 반드시 입력되어야 함
    description TEXT, -- 설명은 선택 사항이므로 NULL 허용
    price NUMERIC(10, 2) NOT NULL, -- 가격은 반드시 입력되어야 함
    stock_quantity INT NOT NULL DEFAULT 0, -- 재고 수량은 반드시 입력되어야 하며, 기본값은 0
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

생성/수정 시간 스탬프: created_at, updated_at과 같은 시간 스탬프 컬럼은 보통 NOT NULL과 함께 DEFAULT CURRENT_TIMESTAMP와 같은 기본값을 설정하여 데이터 삽입 시 자동으로 값이 채워지도록 합니다.

고려사항:

기본값(Default Value)과의 조합: NOT NULL 컬럼에 명시적인 값을 제공하지 않고 데이터를 삽입할 경우 오류가 발생합니다. 이를 방지하기 위해 DEFAULT 절을 사용하여 기본값을 지정할 수 있습니다.
유연성 vs. 강제성: 모든 컬럼에 NOT NULL을 무분별하게 적용하기보다는, 해당 데이터가 비즈니스적으로 정말 필수적인지 신중하게 판단해야 합니다. 너무 많은 NOT NULL은 데이터 삽입/업데이트 시 유연성을 저해할 수 있습니다. NULL이 허용되어야 하는 컬럼은 명시적으로 NULL을 허용하는 것이 좋습니다.

3. `UNIQUE` (고유)

정의: 해당 컬럼 또는 컬럼 집합의 모든 값이 고유해야 함을 강제하는 제약 조건입니다. NULL 값은 여러 개 존재할 수 있습니다 (단, PostgreSQL 15부터는 기본적으로 하나만 허용).

실무 활용:

대체 키: PRIMARY KEY는 아니지만, 테이블 내에서 특정 컬럼의 값이 중복되지 않아야 할 때 사용합니다. 예를 들어, 사용자 테이블의 이메일 주소나 주민등록번호와 같이 고유해야 하는 정보에 적용합니다.

단일 컬럼 UNIQUE:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    ssn VARCHAR(20) UNIQUE, -- 사회 보장 번호는 고유해야 함
    username VARCHAR(50) NOT NULL UNIQUE, -- 사용자 이름은 고유해야 함
    email VARCHAR(100) UNIQUE, -- 이메일은 고유해야 함 (NULL은 여러 개 가능)
    phone_number VARCHAR(20) UNIQUE
);

복합 UNIQUE (Composite Unique Key): 여러 컬럼의 조합이 고유해야 할 때 사용합니다. 예를 들어, 웹사이트에서 게시판의 board_id와 slug(게시글 URL에 사용되는 짧은 이름)의 조합이 고유해야 할 때.

CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    board_id INT NOT NULL,
    slug VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    -- 특정 게시판 내에서 slug는 고유해야 함
    UNIQUE (board_id, slug)
);

고려사항:

UNIQUE 제약 조건도 내부적으로 Unique B-tree 인덱스를 생성합니다. 따라서 해당 컬럼에 대한 검색 성능을 향상시킵니다.
PRIMARY KEY는 암시적으로 UNIQUE와 NOT NULL을 포함합니다. UNIQUE는 NULL을 허용한다는 점에서 차이가 있습니다 (PostgreSQL 15 이전 버전에서는 여러 개의 NULL 허용, 15부터는 하나만 허용).
UNIQUE 제약 조건을 추가할 때, 이미 중복된 값이 존재한다면 에러가 발생합니다.

4. `CHECK` (체크)

정의: 컬럼에 삽입될 값 또는 컬럼의 조합이 특정 조건을 만족하는지 검사하는 제약 조건입니다. 사용자 정의 유효성 검사를 수행할 때 사용합니다.

실무 활용:

값의 범위 제한: 숫자형 컬럼의 값이 특정 범위 내에 있어야 할 때 (예: 나이는 0보다 커야 함, 가격은 양수여야 함).

CREATE TABLE products_with_check (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price > 0), -- 가격은 0보다 커야 함
    stock_quantity INT NOT NULL CHECK (stock_quantity >= 0), -- 재고는 0 이상이어야 함
    discount_rate NUMERIC(3, 2) CHECK (discount_rate >= 0 AND discount_rate <= 1) -- 할인율은 0에서 1 사이
);

조건부 값: 특정 컬럼의 값이 다른 컬럼의 값에 따라 달라질 때 (예: 배송 완료일은 주문일보다 늦어야 함).

CREATE TABLE orders_with_delivery (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    delivery_date DATE,
    -- 배송 완료일이 존재한다면 주문일보다 늦어야 함
    CHECK (delivery_date IS NULL OR delivery_date >= order_date)
);

문자열 패턴: 특정 문자열이 특정 패턴을 따라야 할 때 (정규식 사용).

CREATE TABLE users_with_email_check (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}$'), -- 간단한 이메일 형식 검사
    password VARCHAR(100) NOT NULL
);

고려사항:

CHECK 제약 조건은 데이터 삽입/업데이트 시 매번 조건을 검사하므로, 너무 복잡하거나 성능에 민감한 조건은 피하는 것이 좋습니다.
대량의 데이터가 있는 테이블에 CHECK 제약 조건을 추가할 때는 NOT VALID 옵션을 사용하여 기존 데이터에 대한 검증을 생략하고, 나중에 VALIDATE CONSTRAINT로 검증할 수 있습니다 (PostgreSQL 9.2+).
```
ALTER TABLE products_with_check ADD CONSTRAINT chk_price CHECK (price > 0) NOT VALID;
ALTER TABLE products_with_check VALIDATE CONSTRAINT chk_price;
```

5. `FOREIGN KEY` (외래 키)

정의: 한 테이블의 컬럼(또는 컬럼 집합)이 다른 테이블의 PRIMARY KEY 또는 UNIQUE 컬럼을 참조하도록 강제하는 제약 조건입니다. 테이블 간의 관계(Relational Integrity)를 설정하고 유지하는 데 사용됩니다.

실무 활용:

관계형 데이터베이스의 핵심: FOREIGN KEY는 PRIMARY KEY와 함께 관계형 데이터베이스의 가장 중요한 요소입니다. 부모 테이블의 데이터 없이는 자식 테이블에 데이터를 삽입할 수 없도록 합니다.

데이터 일관성 유지: 예를 들어, 존재하지 않는 사용자의 주문을 생성하거나, 존재하지 않는 제품을 주문 항목에 추가하는 것을 방지합니다.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL, -- orders 테이블의 customer_id는 customers 테이블의 customer_id를 참조
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
    -- 또는 FOREIGN KEY (customer_id) REFERENCES customers -- 컬럼명이 같으면 생략 가능
);

참조 무결성 동작 (ON DELETE, ON UPDATE): 부모 테이블의 데이터가 삭제되거나 업데이트될 때 자식 테이블의 데이터에 어떤 작업을 수행할지 지정할 수 있습니다.
- NO ACTION (기본값): 아무것도 하지 않음 (참조하는 행이 있으면 삭제/업데이트 방지)
- RESTRICT: NO ACTION과 유사, 즉시 오류 발생
- CASCADE: 부모 행이 삭제/업데이트되면 자식 행도 함께 삭제/업데이트
- SET NULL: 부모 행이 삭제/업데이트되면 자식 행의 외래 키 컬럼을 NULL로 설정 (단, 외래 키 컬럼은 NOT NULL이 아니어야 함)
- SET DEFAULT: 부모 행이 삭제/업데이트되면 자식 행의 외래 키 컬럼을 기본값으로 설정
```
CREATE TABLE products_fk (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL
);

CREATE TABLE order_items_fk (
    item_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE, -- 주문 삭제 시 해당 주문 항목도 함께 삭제
    FOREIGN KEY (product_id) REFERENCES products_fk (product_id) ON UPDATE CASCADE -- 제품 ID 변경 시 주문 항목의 제품 ID도 함께 변경
);
```

고려사항:

FOREIGN KEY는 참조하는 컬럼(부모 테이블의 키)에 인덱스가 있어야 효율적으로 작동합니다. PRIMARY KEY는 자동으로 인덱스가 생성되지만, UNIQUE 제약 조건 컬럼을 참조하는 경우에도 인덱스를 확인하는 것이 좋습니다.
FOREIGN KEY를 많이 사용하면 삽입/업데이트/삭제 작업 시 성능 오버헤드가 발생할 수 있습니다. 트랜잭션의 ACID 속성을 보장하기 위한 중요한 비용입니다.
순환 참조(Circular Reference)는 설계 시 주의해야 합니다. (A 테이블이 B 테이블을 참조하고, B 테이블이 다시 A 테이블을 참조하는 경우)

제약 조건 추가/제거 (ALTER TABLE)

테이블을 생성한 후에 제약 조건을 추가하거나 제거할 수도 있습니다.

PRIMARY KEY 추가/제거:

ALTER TABLE customers ADD CONSTRAINT pk_customer_id PRIMARY KEY (customer_id);
ALTER TABLE customers DROP CONSTRAINT pk_customer_id;

NOT NULL 추가/제거:

ALTER TABLE products ALTER COLUMN description SET NOT NULL;
ALTER TABLE products ALTER COLUMN description DROP NOT NULL;

UNIQUE 추가/제거:

ALTER TABLE employees ADD CONSTRAINT uq_employee_email UNIQUE (email);
ALTER TABLE employees DROP CONSTRAINT uq_employee_email;

CHECK 추가/제거:

ALTER TABLE products_with_check ADD CONSTRAINT chk_price_positive CHECK (price > 0);
ALTER TABLE products_with_check DROP CONSTRAINT chk_price_positive;

FOREIGN KEY 추가/제거:
```
ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers (customer_id);
ALTER TABLE orders DROP CONSTRAINT fk_customer_id;
```
팁: 모든 제약 조건에 CONSTRAINT constraint_name을 명시하여 이름을 지정하는 것이 좋습니다. 나중에 제거하거나 관리할 때 편리합니다.

8. 기초적인 함수

PostgreSQL 함수, 왜 중요할까?

함수는 SQL 쿼리 내에서 특정 작업을 수행하는 재사용 가능한 코드 블록입니다. PostgreSQL은 수많은 내장 함수를 제공하며, 필요에 따라 사용자 정의 함수(User-Defined Functions, UDFs)를 생성할 수도 있습니다.

함수의 중요성은 다음과 같습니다:

데이터 변환 및 포맷팅: 데이터를 원하는 형식으로 변환하거나 표현합니다.
복잡한 계산 수행: 통계, 수학적 계산, 문자열 처리 등 복잡한 로직을 처리합니다.
데이터 유효성 검사: 특정 조건을 만족하는지 검사하거나 값을 정규화합니다.
코드 재사용 및 모듈화: 자주 사용되는 로직을 함수로 만들어 코드 중복을 줄이고 유지보수성을 높입니다.
성능 최적화: 특정 함수는 인덱스와 함께 사용될 때 쿼리 성능을 향상시킬 수 있습니다.

주요 PostgreSQL 내장 함수 및 실무 활용

PostgreSQL은 다양한 범주에 걸쳐 풍부한 내장 함수를 제공합니다. 실무에서 자주 사용되는 함수들을 중심으로 살펴보겠습니다.

1. 문자열 함수 (String Functions)

문자열 데이터를 조작하고 변환하는 데 사용됩니다.

LENGTH(string): 문자열의 길이를 반환합니다.

SELECT product_name, LENGTH(product_name) AS name_length
FROM products
ORDER BY name_length DESC;

UPPER(string) / LOWER(string): 문자열을 대문자/소문자로 변환합니다.

SELECT username, UPPER(username) AS upper_username
FROM users
WHERE LOWER(email) LIKE '%@example.com%'; -- 대소문자 구분 없이 이메일 검색

TRIM([BOTH | LEADING | TRAILING] [characters] FROM string): 문자열의 시작, 끝 또는 양쪽에서 지정된 문자(기본값은 공백)를 제거합니다.
```
SELECT '   Hello World   ', TRIM('   Hello World   '); -- 'Hello World'
SELECT 'abcde', TRIM(BOTH 'ae' FROM 'abcde'); -- 'bcd'
```

SUBSTRING(string FROM start [FOR length]): 문자열의 일부를 추출합니다.

SELECT email, SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users
WHERE email IS NOT NULL; -- 이메일 주소에서 도메인 추출

REPLACE(string, from, to): 문자열 내에서 특정 부분을 다른 문자열로 바꿉니다.

SELECT description, REPLACE(description, '옛날', '최신') AS new_description
FROM products;

CONCAT(string1, string2, ...) / ||: 여러 문자열을 연결합니다.

SELECT first_name || ' ' || last_name AS full_name
FROM users;
SELECT CONCAT(first_name, ' ', last_name, ' (', email, ')') AS user_info
FROM users;

LEFT(string, n) / RIGHT(string, n): 문자열의 왼쪽/오른쪽에서 n개의 문자를 추출합니다.
LPAD(string, length, [fill]) / RPAD(string, length, [fill]): 문자열의 왼쪽/오른쪽에 지정된 문자를 채워 특정 길이로 만듭니다.

2. 숫자 함수 (Numeric Functions)

숫자 데이터를 계산하고 조작하는 데 사용됩니다.

ROUND(numeric, [decimal_places]): 숫자를 반올림합니다.

SELECT price, ROUND(price, 0) AS rounded_price
FROM products;
SELECT total_amount, ROUND(total_amount, 2) AS two_decimal_amount
FROM orders;

CEIL(numeric) / FLOOR(numeric): 숫자를 올림/내림합니다.
```
SELECT 123.45, CEIL(123.45), FLOOR(123.45); -- 124, 123
```
ABS(numeric): 숫자의 절댓값을 반환합니다.
MOD(numeric, divisor): 나눗셈의 나머지를 반환합니다.
POWER(base, exponent): 거듭제곱을 계산합니다.
SQRT(numeric): 제곱근을 계산합니다.

3. 날짜/시간 함수 (Date/Time Functions)

날짜 및 시간 데이터를 조작하고 정보를 추출하는 데 사용됩니다.

NOW() / CURRENT_TIMESTAMP: 현재 날짜와 시간을 반환합니다 (타임스탬프와 시간대 정보 포함).
```
SELECT NOW() AS current_time;
```
CURRENT_DATE: 현재 날짜를 반환합니다.
```
SELECT CURRENT_DATE AS today;
```

EXTRACT(field FROM source): 날짜/시간 값에서 특정 필드(년, 월, 일, 시, 분, 초 등)를 추출합니다.

SELECT order_date,
       EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       EXTRACT(DAY FROM order_date) AS order_day
FROM orders;

-- 특정 요일에 발생한 주문 조회 (1=일요일, 7=토요일)
SELECT order_id, order_date
FROM orders
WHERE EXTRACT(DOW FROM order_date) = 1;

AGE(timestamp) / AGE(timestamp1, timestamp2): 현재 날짜 또는 두 날짜 사이의 기간을 계산합니다.
```
SELECT customer_name, AGE(CURRENT_DATE, date_of_birth) AS customer_age
FROM customers;
```

DATE_TRUNC(field, source): 날짜/시간 값을 특정 필드(예: 'month', 'year')의 시작 지점으로 잘라냅니다.

-- 각 월별 총 판매 금액 집계
SELECT DATE_TRUNC('month', order_date) AS sales_month,
       SUM(total_amount) AS monthly_sales
FROM orders
GROUP BY sales_month
ORDER BY sales_month;

TO_CHAR(timestamp, format): 날짜/시간 값을 특정 형식의 문자열로 포맷합니다.

SELECT order_date, TO_CHAR(order_date, 'YYYY년 MM월 DD일 HH시 MI분') AS formatted_date
FROM orders;

TO_DATE(string, format) / TO_TIMESTAMP(string, format): 문자열을 날짜/시간 값으로 변환합니다.

4. 집계 함수 (Aggregate Functions)

여러 행의 값을 그룹화하여 단일 결과를 반환합니다. GROUP BY 절과 함께 사용될 때 강력합니다.

COUNT(*) / COUNT(column): 행의 개수를 세거나 NULL이 아닌 값의 개수를 셉니다.

SELECT COUNT(*) FROM users; -- 총 사용자 수
SELECT category, COUNT(product_id) AS num_products
FROM products
GROUP BY category; -- 카테고리별 제품 수

SUM(numeric): 숫자 컬럼의 합계를 계산합니다.

SELECT SUM(total_amount) AS total_sales
FROM orders;

AVG(numeric): 숫자 컬럼의 평균을 계산합니다.

SELECT AVG(price) AS average_product_price
FROM products;

MIN(column) / MAX(column): 컬럼의 최소/최대 값을 찾습니다.

SELECT MIN(order_date) AS earliest_order, MAX(order_date) AS latest_order
FROM orders;

STRING_AGG(expression, delimiter): 그룹 내 문자열을 구분자를 사용하여 하나의 문자열로 연결합니다.

SELECT customer_id, STRING_AGG(product_name, ', ') AS ordered_products
FROM orders
JOIN order_items USING (order_id)
JOIN products USING (product_id)
GROUP BY customer_id;

5. 조건 함수 (Conditional Functions)

CASE 문 외에 NULLIF, COALESCE 등이 있습니다.

COALESCE(value1, value2, ...): 첫 번째 NULL이 아닌 값을 반환합니다.

-- 사용자 이름이 없으면 이메일, 이메일도 없으면 'Unknown'으로 표시
SELECT COALESCE(username, email, 'Unknown') AS display_name
FROM users;

NULLIF(expression1, expression2): 두 표현식이 같으면 NULL을, 아니면 expression1을 반환합니다.

-- 0으로 나누기 방지 (분모가 0이면 NULL 반환)
SELECT total_sales / NULLIF(total_customers, 0) AS sales_per_customer
FROM daily_summary;

6. 형 변환 함수 (Type Conversion Functions)

데이터 타입을 다른 타입으로 변환합니다. CAST 또는 :: 연산자를 사용합니다.

CAST(expression AS type) / expression::type:

SELECT '123'::INTEGER + 10 AS result; -- 133
SELECT CAST(price AS TEXT) || '원' AS formatted_price
FROM products;

7. JSON/JSONB 함수 (JSON Functions)

JSON/JSONB 데이터를 쿼리하고 조작하는 데 사용됩니다.

jsonb_typeof(jsonb): JSONB 값의 타입을 텍스트로 반환합니다.
jsonb_build_object(key1, value1, key2, value2, ...): 키-값 쌍으로 JSONB 객체를 생성합니다.
jsonb_agg(expression): 그룹 내의 JSONB 값들을 JSONB 배열로 집계합니다.

jsonb_each(jsonb) / jsonb_each_text(jsonb): JSONB 객체를 키-값 쌍의 집합으로 확장합니다.

SELECT
    product_id,
    metadata ->> 'color' AS color, -- 텍스트로 값 추출
    (metadata -> 'dimensions' ->> 'width')::NUMERIC AS width -- 중첩된 JSON 값 추출 후 숫자형으로 변환
FROM products
WHERE metadata @> '{"material": "wood"}'; -- JSONB 데이터에서 특정 속성 검색

사용자 정의 함수 (User-Defined Functions, UDFs)

내장 함수만으로는 부족할 때, 비즈니스 로직에 맞춰 직접 함수를 생성할 수 있습니다. PL/pgSQL이 가장 일반적입니다.

활용 예시:

-- 두 날짜 사이의 영업일 수를 계산하는 함수
CREATE OR REPLACE FUNCTION calculate_working_days(start_date DATE, end_date DATE)
RETURNS INTEGER AS $$
DECLARE
    working_days INTEGER := 0;
    current_date DATE := start_date;
BEGIN
    WHILE current_date <= end_date LOOP
        IF EXTRACT(DOW FROM current_date) NOT IN (0, 6) THEN -- 0=일요일, 6=토요일
            working_days := working_days + 1;
        END IF;
        current_date := current_date + INTERVAL '1 day';
    END LOOP;
    RETURN working_days;
END;
$$ LANGUAGE plpgsql;

-- 함수 사용 예시
SELECT calculate_working_days('2024-06-01', '2024-06-30');

고려사항:

성능: 복잡한 사용자 정의 함수는 쿼리 성능에 영향을 줄 수 있습니다. 가능하면 내장 함수나 SQL 쿼리 자체로 해결하는 것을 우선 고려하세요.
인덱스: 함수를 WHERE 절에 직접 사용하면 인덱스를 활용하지 못해 풀 스캔이 발생할 수 있습니다. 이 경우 함수 기반 인덱스(Functional Index)를 고려하거나, 조건을 함수 없이 작성하는 방법을 모색해야 합니다.
```
-- LENGTH(product_name)을 기준으로 인덱스 생성
CREATE INDEX idx_product_name_length ON products (LENGTH(product_name));
```

9. 기초적인 GROUP BY

PostgreSQL `GROUP BY` 절, 왜 중요할까?

GROUP BY 절은 SQL에서 데이터를 요약하고 분석하는 데 필수적인 도구입니다. 단순히 개별 행을 조회하는 것을 넘어, 특정 기준(카테고리, 날짜, 사용자 등)별로 데이터를 그룹화하여 합계, 평균, 개수, 최댓값, 최솟값 등의 집계(Aggregate) 결과를 얻을 수 있게 해줍니다.

GROUP BY의 중요성은 다음과 같습니다:

데이터 요약 및 통계: 방대한 데이터를 특정 기준별로 요약하여 비즈니스 인사이트를 얻을 수 있습니다. (예: 월별 매출, 카테고리별 제품 수)
보고서 작성: 다양한 유형의 보고서를 생성하는 데 기본이 됩니다.
복잡한 분석 지원: HAVING 절, 윈도우 함수 등과 결합하여 더욱 심층적인 분석을 가능하게 합니다.

기본 `GROUP BY` 절 (Basic GROUP BY Clause)

GROUP BY 절은 하나 이상의 컬럼을 기준으로 행들을 그룹화하며, SELECT 절에는 그룹화된 컬럼과 집계 함수(Aggregate Functions)만 올 수 있습니다.

예시:

-- 각 카테고리별 제품의 평균 가격 조회
SELECT
    category,
    AVG(price) AS average_price
FROM products
GROUP BY category;

-- 각 사용자(customer)별 총 주문 금액 조회
SELECT
    customer_id,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;

여러 컬럼으로 그룹화 (Grouping by Multiple Columns)

둘 이상의 컬럼을 기준으로 그룹을 나눌 수 있습니다. 첫 번째 컬럼으로 그룹화한 후, 각 그룹 내에서 다시 두 번째 컬럼으로 하위 그룹을 만듭니다.

예시:

-- 각 카테고리 내에서 상태(status)별 제품 개수 조회
SELECT
    category,
    status,
    COUNT(product_id) AS product_count
FROM products
GROUP BY category, status
ORDER BY category, status;

-- 각 년도/월별 총 주문 금액 조회
SELECT
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    SUM(total_amount) AS monthly_sales
FROM orders
GROUP BY order_year, order_month -- SELECT 절의 별칭(Alias) 사용 가능 (PostgreSQL 특정 기능)
ORDER BY order_year, order_month;

`HAVING` 절을 이용한 그룹 필터링 (Filtering Groups with HAVING)

WHERE 절이 개별 행을 필터링하는 데 사용되는 반면, HAVING 절은 GROUP BY에 의해 생성된 그룹화된 결과를 필터링하는 데 사용됩니다. HAVING 절에는 집계 함수를 직접 사용할 수 있습니다.

실행 순서: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

예시:

-- 평균 가격이 500 이상인 카테고리만 조회
SELECT
    category,
    AVG(price) AS average_price
FROM products
GROUP BY category
HAVING AVG(price) >= 500;

-- 총 주문 금액이 1000을 초과하는 고객만 조회
SELECT
    customer_id,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000;

-- 2023년 이후에 5건 이상의 주문을 한 고객만 조회
SELECT
    customer_id,
    COUNT(order_id) AS order_count
FROM orders
WHERE order_date >= '2023-01-01' -- 먼저 2023년 이후 주문만 필터링 (행 필터링)
GROUP BY customer_id
HAVING COUNT(order_id) >= 5; -- 그 다음 그룹화된 결과 필터링 (그룹 필터링)

`GROUPING SETS`, `ROLLUP`, `CUBE`를 이용한 고급 집계

PostgreSQL은 다양한 수준의 그룹화된 집계를 생성하는 고급 기능을 제공합니다. 이는 여러 개의 GROUP BY 쿼리를 하나로 통합하는 데 유용합니다.

GROUPING SETS: 여러 개의 그룹화 기준을 한 번의 쿼리로 지정합니다. 각 괄호 안의 집합이 독립적인 GROUP BY 절처럼 작동합니다.

-- 카테고리별 합계, 상태별 합계, 그리고 전체 합계 조회
SELECT
    category,
    status,
    SUM(price) AS total_price
FROM products
GROUP BY GROUPING SETS (
    (category),       -- 카테고리별 집계
    (status),         -- 상태별 집계
    ()                -- 전체 합계 (빈 괄호는 전체 그룹을 의미)
)
ORDER BY category NULLS LAST, status NULLS LAST;

ROLLUP: 계층적인 그룹화를 생성합니다. 지정된 컬럼들의 모든 가능한 "소계"를 포함합니다. (예: (A, B)는 (A, B), (A), ()의 그룹을 생성)

-- 연도별-월별 합계, 연도별 합계, 전체 합계 조회
SELECT
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    SUM(total_amount) AS total_sales
FROM orders
GROUP BY ROLLUP(EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date))
ORDER BY order_year NULLS LAST, order_month NULLS LAST;

결과 예시:
- 2023년 1월 합계
- 2023년 2월 합계
- ...
- 2023년 전체 합계 (월이 NULL)
- 2024년 1월 합계
- ...
- 2024년 전체 합계 (월이 NULL)
- 전체 합계 (연도, 월 모두 NULL)

CUBE: 지정된 컬럼들의 모든 가능한 조합에 대한 그룹화를 생성합니다. ROLLUP보다 더 많은 조합을 생성합니다. (예: (A, B)는 (A, B), (A), (B), ()의 그룹을 생성)
```
-- 카테고리별-상태별 합계, 카테고리별 합계, 상태별 합계, 전체 합계 조회
SELECT
    category,
    status,
    SUM(price) AS total_price
FROM products
GROUP BY CUBE(category, status)
ORDER BY category NULLS LAST, status NULLS LAST;
```
- 결과 예시:
  - 카테고리 'Electronics', 상태 'New' 합계
  - 카테고리 'Electronics', 상태 'Used' 합계
  - ...
  - 카테고리 'Electronics' 전체 합계 (상태가 NULL)
  - 상태 'New' 전체 합계 (카테고리가 NULL)
  - 상태 'Used' 전체 합계 (카테고리가 NULL)
  - 전체 합계 (카테고리, 상태 모두 NULL)

`GROUPING` 함수 (Identifying Grouping Levels)

GROUPING 함수는 GROUPING SETS, ROLLUP, CUBE 사용 시 특정 컬럼이 집계에 사용되었는지(0) 아니면 롤업/큐브에 의해 NULL로 처리되었는지(1)를 식별하는 데 사용됩니다.

예시:

SELECT
    category,
    status,
    SUM(price) AS total_price,
    GROUPING(category) AS is_category_rollup, -- category가 롤업/큐브에 의해 생성된 NULL이면 1, 아니면 0
    GROUPING(status) AS is_status_rollup
FROM products
GROUP BY CUBE(category, status)
ORDER BY category NULLS LAST, status NULLS LAST;

GROUPING(column)의 결과가 1인 행은 해당 column이 그룹화 기준에 포함되지 않고 상위 수준의 집계(소계 또는 총계)에 의해 NULL로 생성되었음을 의미합니다.

성능 고려사항

GROUP BY 절은 대량의 데이터에 대한 집계를 수행하므로 성능에 상당한 영향을 미칠 수 있습니다.

인덱스 활용: GROUP BY에 사용되는 컬럼에 인덱스가 있으면 정렬 및 그룹화 작업의 효율성을 높일 수 있습니다. 특히 ORDER BY와 GROUP BY에 동일한 컬럼이 동일한 순서와 방향으로 사용될 경우, 인덱스를 통해 'Sort' 작업을 생략할 수 있습니다.
WHERE 절의 중요성: GROUP BY 전에 WHERE 절을 사용하여 불필요한 행을 미리 필터링하는 것이 매우 중요합니다. 필터링된 데이터 양이 적을수록 그룹화 작업이 빨라집니다.
메모리 사용: GROUP BY는 많은 메모리를 사용할 수 있습니다. work_mem 설정을 적절히 조정하는 것이 도움이 될 수 있습니다.
EXPLAIN ANALYZE: 항상 EXPLAIN ANALYZE를 사용하여 쿼리 실행 계획을 분석하고, 'Sort' 또는 'HashAggregate' 같은 비용이 많이 드는 연산이 있는지 확인해야 합니다. 인덱스 힌트가 명시적으로 없으므로, 실행 계획을 통해 인덱스 활용 여부를 판단해야 합니다.
함수 사용 주의: GROUP BY 절에서 컬럼에 함수를 사용하면 인덱스를 활용하기 어렵습니다. (예: GROUP BY EXTRACT(YEAR FROM order_date)). 이 경우, order_date에 인덱스가 있더라도 EXTRACT 함수 때문에 인덱스를 직접 사용하지 못할 수 있습니다. 성능이 중요하다면 CTE나 서브쿼리를 사용하여 함수 결과를 먼저 계산하고 그 결과로 그룹화하거나, 함수 기반 인덱스를 고려해야 합니다.

10. 기초적인 HAVING

PostgreSQL `HAVING` 절, 왜 중요할까?

SQL에서 데이터를 필터링하는 방법은 크게 두 가지입니다. 개별 행을 필터링하는 WHERE 절과, 그룹화된 결과를 필터링하는 HAVING 절이죠. HAVING 절은 GROUP BY와 함께 사용되며, 집계 함수(Aggregate Functions)의 결과를 기준으로 그룹을 걸러낼 때 빛을 발합니다.

HAVING 절의 중요성은 다음과 같습니다:

그룹별 조건 필터링: 단순히 행을 걸러내는 것을 넘어, 특정 집계 조건을 만족하는 그룹만을 선택하여 더욱 심층적인 분석을 가능하게 합니다. (예: 평균 판매액이 특정 금액 이상인 카테고리만 보기)
보고서 세분화: 생성된 보고서에서 특정 기준(예: 최소 주문 건수, 최대 재고량)을 만족하는 데이터만 포함시켜 보고서의 유용성을 높입니다.
복잡한 비즈니스 로직 구현: WHERE 절로는 구현하기 어려운, 집계된 데이터에 기반한 비즈니스 규칙을 적용할 수 있습니다.

`WHERE` 절과 `HAVING` 절의 차이점 (핵심 개념)

HAVING 절을 이해하는 가장 중요한 부분은 WHERE 절과의 차이를 명확히 아는 것입니다.

WHERE 절:
- 개별 행(Row)에 조건을 적용합니다.
- GROUP BY 이전에 실행되어 데이터를 필터링합니다.
- 집계 함수를 사용할 수 없습니다. (집계는 GROUP BY 이후에 수행되기 때문)
HAVING 절:
- 그룹(Group)에 조건을 적용합니다.
- GROUP BY 이후에 실행되어 그룹화된 결과를 필터링합니다.
- 집계 함수를 사용할 수 있습니다. (COUNT(), SUM(), AVG(), MIN(), MAX() 등)

SQL 쿼리 실행 순서:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

이 순서를 이해하면 WHERE와 HAVING 중 어떤 것을 사용해야 할지 명확하게 판단할 수 있습니다.

기본 `HAVING` 절 활용 (Basic HAVING Clause)

GROUP BY 절 뒤에 HAVING 절을 추가하여 집계된 결과에 조건을 적용합니다.

예시:

-- 각 카테고리별 제품의 평균 가격을 조회하되, 평균 가격이 500 이상인 카테고리만 선택
SELECT
    category,
    AVG(price) AS average_price
FROM products
GROUP BY category
HAVING AVG(price) >= 500; -- 그룹화된 'average_price'에 조건 적용

-- 각 고객별 총 주문 금액을 조회하되, 총 주문 금액이 1000을 초과하는 고객만 선택
SELECT
    customer_id,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000; -- 그룹화된 'total_spent'에 조건 적용

`WHERE` 절과 `HAVING` 절 동시 활용 (Combined Usage)

두 절을 함께 사용하면, 먼저 개별 행을 필터링하여 데이터 양을 줄인 다음, 남은 데이터에 대해 그룹화를 수행하고, 마지막으로 그룹화된 결과를 필터링할 수 있습니다. 이는 성능 최적화에 매우 중요합니다.

예시:

-- 2023년 이후에 발생한 주문 중에서, 주문 건수가 5건 이상인 고객만 조회
SELECT
    customer_id,
    COUNT(order_id) AS order_count,
    SUM(total_amount) AS total_amount_2023_onwards
FROM orders
WHERE order_date >= '2023-01-01' -- 먼저 2023년 이후 주문만 필터링 (불필요한 행 제거)
GROUP BY customer_id
HAVING COUNT(order_id) >= 5; -- 그 다음 그룹화된 결과 중 5건 이상인 그룹만 선택

-- 'Electronics' 카테고리의 제품 중에서, 재고가 100개 미만인 제품이 3개 이상인 공급업체 조회
SELECT
    supplier_id,
    COUNT(product_id) AS low_stock_product_count
FROM products
WHERE category = 'Electronics' AND stock_quantity < 100 -- 먼저 필터링 (행 필터링)
GROUP BY supplier_id
HAVING COUNT(product_id) >= 3; -- 그 다음 그룹 필터링

팁: 일반적으로 가능한 한 WHERE 절에서 먼저 데이터를 필터링하여 GROUP BY로 전달되는 데이터의 양을 줄이는 것이 성능에 좋습니다. HAVING 절은 WHERE 절로 필터링할 수 없는 집계 조건에만 사용하세요.

`HAVING` 절에서 여러 조건 사용 (Multiple Conditions)

AND, OR, NOT과 같은 논리 연산자를 사용하여 HAVING 절에 여러 조건을 조합할 수 있습니다.

예시:

-- 평균 가격이 500 이상이고, 제품 수가 10개 미만인 카테고리 조회
SELECT
    category,
    AVG(price) AS average_price,
    COUNT(product_id) AS product_count
FROM products
GROUP BY category
HAVING AVG(price) >= 500 AND COUNT(product_id) < 10;

-- 총 판매액이 1000 이상이거나, 주문 건수가 10건 이상인 고객 조회
SELECT
    customer_id,
    SUM(total_amount) AS total_spent,
    COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) >= 1000 OR COUNT(order_id) >= 10;

`HAVING` 절에서 별칭(Alias) 사용 (PostgreSQL Specific)

PostgreSQL은 SELECT 절에서 정의한 별칭을 HAVING 절에서 직접 사용할 수 있도록 허용합니다. (다른 일부 SQL DB에서는 불가능하거나 제한적일 수 있습니다.) 이는 쿼리 가독성을 높여줍니다.

예시:

-- (이전 예시와 동일) 평균 가격이 500 이상인 카테고리만 조회
SELECT
    category,
    AVG(price) AS avg_price -- 별칭 정의
FROM products
GROUP BY category
HAVING avg_price >= 500; -- 별칭 사용

-- (이전 예시와 동일) 총 주문 금액이 1000을 초과하는 고객만 조회
SELECT
    customer_id,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 1000; -- 별칭 사용

성능 고려사항

HAVING 절은 GROUP BY 이후에 실행되므로, GROUP BY 자체의 성능과 밀접한 관련이 있습니다.

WHERE 절 우선: 가장 중요한 것은 GROUP BY 전에 최대한 많은 데이터를 WHERE 절로 필터링하는 것입니다. HAVING 절은 이미 그룹화된 데이터에 적용되기 때문에, 그룹화될 데이터의 양이 많으면 많을수록 성능 저하가 커집니다.
인덱스 활용: GROUP BY에 사용되는 컬럼에 인덱스가 있다면 그룹화 작업의 효율성이 높아지고, 이는 HAVING 절의 성능에도 간접적으로 영향을 미칩니다. 그러나 HAVING 절 자체의 조건에 사용되는 집계 함수에는 인덱스가 직접 적용되지 않습니다. (집계 결과는 동적으로 계산되기 때문)
EXPLAIN ANALYZE: GROUP BY와 HAVING이 포함된 쿼리는 항상 EXPLAIN ANALYZE를 통해 실행 계획을 분석해야 합니다. 특히 'HashAggregate'나 'Sort'와 같은 연산의 비용이 높은지 확인하고, WHERE 절에서 필터링을 더 강화할 수 있는지 검토해야 합니다.

11. 기초적인 JOIN

PostgreSQL `JOIN` 절, 왜 중요할까?

데이터베이스는 효율적인 데이터 관리와 중복 제거를 위해 데이터를 여러 개의 테이블로 나누어 저장하는 정규화(Normalization) 과정을 거칩니다. 예를 들어, 사용자 정보는 users 테이블에, 주문 정보는 orders 테이블에, 주문된 상품 목록은 order_items 테이블에 각각 저장될 수 있습니다.

하지만 실제 애플리케이션에서는 이러한 분리된 데이터를 한 번에 조회해야 하는 경우가 많습니다. 이때 JOIN 절이 등장하여 서로 다른 테이블의 관련 행들을 연결해줍니다.

JOIN의 중요성은 다음과 같습니다:

데이터 통합: 분리된 데이터를 연결하여 필요한 정보를 한 번의 쿼리로 가져올 수 있습니다.
복잡한 쿼리 작성: 여러 테이블 간의 관계를 기반으로 복잡한 비즈니스 로직을 구현할 수 있습니다.
보고서 및 분석: 다양한 소스에서 데이터를 결합하여 포괄적인 보고서나 분석 결과를 생성합니다.

주요 `JOIN` 타입 및 실무 활용

PostgreSQL은 다양한 JOIN 타입을 제공하며, 각각의 목적과 결과가 다릅니다. 실무에서 가장 자주 사용되는 JOIN 타입들을 중심으로 살펴보겠습니다.

1. `INNER JOIN` (내부 조인)

정의: 두 테이블에서 조인 조건이 일치하는 행들만을 반환합니다. 조인 조건이 양쪽에 모두 존재해야만 결과에 포함됩니다. 가장 일반적이고 기본이 되는 조인 타입입니다.

활용 예시:

-- 고객 정보와 해당 고객이 주문한 내역을 연결
-- customers 테이블과 orders 테이블에 모두 존재하는 customer_id를 기준으로 연결
SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM
    customers AS c
INNER JOIN
    orders AS o ON c.customer_id = o.customer_id;

결과: customer_id가 customers 테이블과 orders 테이블 양쪽에 모두 있는 행만 나옵니다. 주문이 없는 고객이나, 고객 ID가 존재하지 않는 주문은 결과에서 제외됩니다.
실무 팁: 대부분의 JOIN 상황에서 기본적으로 고려하는 조인입니다. 양쪽 테이블에 모두 존재하는 유효한 관계 데이터를 조회할 때 사용합니다. 테이블 별칭(Alias, AS c, AS o)을 사용하여 쿼리 가독성을 높이는 것이 좋습니다.

2. `LEFT JOIN` (LEFT OUTER JOIN, 좌측 외부 조인)

정의: 왼쪽 테이블의 모든 행을 반환하고, 오른쪽 테이블에서는 조인 조건이 일치하는 행을 연결합니다. 오른쪽 테이블에서 일치하는 행이 없으면, 해당 오른쪽 컬럼들은 NULL로 채워집니다.

활용 예시:

-- 모든 고객 정보와 (주문이 있다면) 해당 주문 내역을 연결
-- 주문이 없는 고객도 결과에 포함되며, 이 경우 주문 관련 컬럼은 NULL이 됩니다.
SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM
    customers AS c
LEFT JOIN
    orders AS o ON c.customer_id = o.customer_id;

결과: 모든 고객이 나옵니다. 주문이 있는 고객은 주문 정보와 함께, 주문이 없는 고객은 주문 정보가 NULL로 표시되어 나옵니다.
실무 팁: "왼쪽 테이블의 모든 데이터를 보여주고 싶고, 만약 오른쪽 테이블에 관련된 데이터가 있다면 함께 보여줘"라는 시나리오에 적합합니다. 예를 들어, 모든 상품 목록을 보여주되, 특정 기간의 판매량이 있다면 함께 보여주는 경우 등에 사용됩니다.

3. `RIGHT JOIN` (RIGHT OUTER JOIN, 우측 외부 조인)

정의: 오른쪽 테이블의 모든 행을 반환하고, 왼쪽 테이블에서는 조인 조건이 일치하는 행을 연결합니다. 왼쪽 테이블에서 일치하는 행이 없으면, 해당 왼쪽 컬럼들은 NULL로 채워집니다.

활용 예시:

-- 모든 주문 내역과 (고객 정보가 있다면) 해당 고객 정보를 연결
-- 고객 정보가 없는 주문 (잘못된 데이터일 가능성)도 결과에 포함되며, 이 경우 고객 관련 컬럼은 NULL이 됩니다.
SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM
    customers AS c
RIGHT JOIN
    orders AS o ON c.customer_id = o.customer_id;

결과: 모든 주문이 나옵니다. 고객 정보가 있는 주문은 고객 정보와 함께, 고객 정보가 없는 주문은 고객 정보가 NULL로 표시되어 나옵니다.
실무 팁: LEFT JOIN과 기능적으로 유사하며, 테이블의 위치만 바꾸면 LEFT JOIN으로 대체할 수 있습니다. (예: A RIGHT JOIN B는 B LEFT JOIN A와 동일). 가독성 측면에서 주된 테이블을 왼쪽에 두고 LEFT JOIN을 사용하는 것이 더 일반적입니다.

4. `FULL JOIN` (FULL OUTER JOIN, 완전 외부 조인)

정의: 양쪽 테이블의 모든 행을 반환합니다. 조인 조건이 일치하는 행은 연결하고, 일치하지 않는 행은 다른 테이블의 컬럼을 NULL로 채워 반환합니다.

활용 예시:

-- 모든 고객과 모든 주문 내역을 연결 (고객만 있거나 주문만 있어도 모두 포함)
SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM
    customers AS c
FULL JOIN
    orders AS o ON c.customer_id = o.customer_id;

결과: 모든 고객과 모든 주문이 나옵니다.
- 고객과 주문 모두 있는 경우: 연결된 정보가 나옵니다.
- 주문이 없는 고객: 주문 정보 컬럼이 NULL로 나옵니다.
- 고객 정보가 없는 주문: 고객 정보 컬럼이 NULL로 나옵니다.
실무 팁: 양쪽 테이블의 모든 잠재적인 데이터를 탐색하고 싶을 때 사용합니다. 예를 들어, 모든 상품과 모든 카테고리를 보여주되, 연결되지 않은 상품이나 카테고리도 함께 보고 싶을 때 유용합니다. 데이터 불일치(data inconsistency)를 찾을 때도 활용됩니다.

5. `CROSS JOIN` (교차 조인)

정의: 두 테이블의 모든 행 조합을 반환합니다. 즉, 첫 번째 테이블의 각 행이 두 번째 테이블의 모든 행과 조인됩니다. 조인 조건이 필요 없습니다. (결과 행의 수 = 테이블1 행수 * 테이블2 행수)

활용 예시:

-- 모든 고객과 모든 제품의 가능한 모든 조합을 생성 (매우 큰 결과셋)
SELECT
    c.customer_name,
    p.product_name
FROM
    customers AS c
CROSS JOIN
    products AS p;

결과: (고객1, 제품1), (고객1, 제품2), ..., (고객N, 제품M) 과 같이 모든 고객이 모든 제품과 매칭된 결과가 나옵니다.
실무 팁: 일반적으로는 잘 사용되지 않지만, 특정 조합의 데이터를 생성하거나, 테스트 데이터 생성, 또는 미리 정의된 집합(예: 모든 요일과 모든 시간대의 조합)을 만들 때 유용하게 사용될 수 있습니다. 실수로 INNER JOIN에 ON 절을 누락하면 CROSS JOIN처럼 동작하여 예상치 못한 대량의 결과를 반환할 수 있으니 주의해야 합니다.

고급 `JOIN` 활용 및 고려사항

USING 절: 조인하려는 두 테이블의 컬럼 이름이 같을 때 ON 대신 USING(column_name)을 사용할 수 있습니다. 코드가 간결해집니다.
```
SELECT *
FROM orders
JOIN order_items USING (order_id); -- ON orders.order_id = order_items.order_id 와 동일
```
NATURAL JOIN: 두 테이블에서 이름과 타입이 같은 모든 컬럼을 기준으로 자동으로 조인합니다. 실무에서는 컬럼 이름 불일치나 의도치 않은 조인 조건을 피하기 위해 잘 사용되지 않습니다.

여러 테이블 조인: 3개 이상의 테이블을 조인할 수 있습니다. 각 조인 단계마다 적절한 JOIN 타입과 ON 조건을 명시합니다.

SELECT
    c.customer_name,
    o.order_id,
    p.product_name,
    oi.quantity
FROM
    customers AS c
INNER JOIN
    orders AS o ON c.customer_id = o.customer_id
INNER JOIN
    order_items AS oi ON o.order_id = oi.order_id
INNER JOIN
    products AS p ON oi.product_id = p.product_id
WHERE
    p.category = 'Electronics'
ORDER BY
    o.order_date DESC;

자기 조인 (Self-Join): 같은 테이블을 두 번 사용하여 조인하는 경우입니다. 예를 들어, 직원 테이블에서 각 직원의 매니저 정보를 찾을 때 사용됩니다.
```
SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM
    employees AS e
LEFT JOIN
    employees AS m ON e.manager_id = m.employee_id;
```
성능 최적화:
- 인덱스 활용: JOIN 조건(ON 절)에 사용되는 컬럼에는 반드시 인덱스가 있어야 합니다. 특히 FOREIGN KEY 컬럼에는 자동으로 인덱스가 생성되지 않으므로, 명시적으로 생성하는 것이 좋습니다 (단, PRIMARY KEY나 UNIQUE로 참조되는 경우는 인덱스가 생성됨).
- WHERE 절 우선: 조인하기 전에 WHERE 절을 사용하여 불필요한 행을 먼저 필터링하면 조인 대상 데이터의 양을 줄여 성능을 크게 향상시킬 수 있습니다.
- 조인 순서: PostgreSQL 옵티마이저가 최적의 조인 순서를 결정하지만, 매우 복잡한 쿼리에서는 조인 순서가 성능에 영향을 미칠 수 있습니다.
- EXPLAIN ANALYZE: JOIN 쿼리는 복잡해질수록 성능 분석이 필수적입니다. EXPLAIN ANALYZE를 통해 어떤 조인 방식(Nested Loop, Hash Join, Merge Join)이 사용되는지, 그리고 인덱스가 효과적으로 사용되는지 확인해야 합니다.

실무 팁 요약:

올바른 JOIN 타입 선택: 쿼리의 목적에 따라 INNER, LEFT, RIGHT, FULL, CROSS 중 적절한 조인 타입을 선택하는 것이 가장 중요합니다. 대부분의 경우 INNER JOIN 또는 LEFT JOIN이 사용됩니다.
ON 절의 중요성: 조인 조건을 명확하게 정의하는 ON 절을 정확히 사용해야 합니다. 조건이 잘못되면 예상치 못한 결과나 성능 문제를 야기할 수 있습니다.
테이블 별칭 사용: 여러 테이블을 조인할 때는 테이블 별칭(AS 키워드 또는 생략)을 사용하여 쿼리 가독성을 높이고 컬럼 충돌을 방지하세요.
WHERE 절로 사전 필터링: 조인 전에 WHERE 절로 가능한 한 많은 데이터를 필터링하여 조인할 데이터의 양을 줄이는 것이 성능 최적화의 핵심입니다.
인덱스 활용: ON 절에 사용되는 모든 컬럼에는 인덱스가 있는지 확인하고, 필요하다면 추가하세요. 특히 FOREIGN KEY 관계를 맺는 컬럼들은 인덱스의 좋은 후보입니다.
EXPLAIN ANALYZE로 디버깅: JOIN 쿼리의 성능 문제를 분석할 때는 반드시 EXPLAIN ANALYZE를 사용하세요. 어떤 조인 방식이 사용되는지, 비용은 얼마나 드는지 등을 파악할 수 있습니다.
정규화와 역정규화: 데이터를 정규화하여 여러 테이블로 분리하되, 성능상의 이유로 특정 데이터를 역정규화하여 조인을 줄이는 것을 고려할 수도 있습니다 (다만, 데이터 일관성 유지에 대한 추가적인 노력이 필요).

12. 기초적인 서브쿼리

PostgreSQL 서브쿼리, 왜 중요할까?

서브쿼리는 메인 쿼리에서 필요한 데이터를 직접 얻기 어렵거나, 복잡한 논리 조건을 적용해야 할 때 사용됩니다. "한 번에 처리하기 어려운 복잡한 문제를 작은 단위로 쪼개어 해결한다"는 개념과 유사합니다.

서브쿼리의 중요성은 다음과 같습니다:

복잡한 문제 해결: 다단계의 데이터 처리나 조건부 로직이 필요할 때 유용합니다.
가독성 향상: 특정 단계를 서브쿼리로 분리하여 쿼리 자체의 논리 흐름을 명확하게 할 수 있습니다. (하지만 너무 복잡하면 오히려 가독성을 해칠 수도 있습니다.)
다양한 활용: SELECT, FROM, WHERE, HAVING 절 등 SQL 쿼리의 거의 모든 부분에서 사용될 수 있습니다.

서브쿼리의 종류 및 실무 활용

서브쿼리는 사용되는 위치와 반환하는 값의 형태에 따라 여러 가지 방식으로 분류될 수 있습니다.

1. 스칼라 서브쿼리 (Scalar Subquery)

정의: 하나의 행과 하나의 컬럼, 즉 단일 값을 반환하는 서브쿼리입니다. 이 값은 메인 쿼리의 특정 위치에서 단일 값처럼 사용될 수 있습니다.

활용 예시:

-- 각 제품의 가격과 해당 카테고리의 평균 가격을 함께 조회
SELECT
    product_name,
    price,
    category,
    (SELECT AVG(price) FROM products WHERE category = p.category) AS avg_category_price
FROM
    products AS p;

결과: 각 제품별로, 해당 제품의 카테고리와 동일한 카테고리의 평균 가격이 새로운 컬럼으로 추가되어 나옵니다.
실무 팁: SELECT 절이나 WHERE 절의 비교 연산자(=, >, < 등)와 함께 사용됩니다. 만약 스칼라 서브쿼리가 여러 행을 반환하면 오류가 발생합니다. 상관 서브쿼리(Correlated Subquery)의 대표적인 형태로, 메인 쿼리의 행마다 서브쿼리가 다시 실행될 수 있으므로 대량의 데이터에서는 성능에 주의해야 합니다.

2. 행 서브쿼리 (Row Subquery)

정의: 하나의 행을 반환하지만, 그 행은 여러 컬럼을 포함할 수 있습니다. 주로 WHERE 절에서 다중 컬럼 비교에 사용됩니다.

활용 예시:

-- 특정 고객의 가장 최근 주문과 동일한 날짜 및 총 금액을 가진 다른 주문 조회
SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
FROM
    orders
WHERE
    (order_date, total_amount) = (SELECT order_date, total_amount FROM orders WHERE order_id = 101);

결과: order_id가 101인 주문과 동일한 주문 날짜와 총 금액을 가진 모든 주문이 조회됩니다.
실무 팁: (col1, col2) = (SELECT col1, col2 FROM ...) 형태로 사용하며, 특정 복합 조건을 만족하는 행을 찾을 때 유용합니다.

3. 테이블 서브쿼리 (Table Subquery) 또는 파생 테이블 (Derived Table)

정의: 여러 행과 여러 컬럼을 반환하는 서브쿼리입니다. 마치 임시 테이블처럼 동작하며, 메인 쿼리의 FROM 절에서 사용됩니다. 반드시 별칭(Alias)을 지정해야 합니다.

활용 예시:

-- 각 카테고리별 제품 수와 평균 가격을 구한 후, 평균 가격이 500 이상인 카테고리만 필터링
SELECT
    category_summary.category,
    category_summary.product_count,
    category_summary.avg_price
FROM
    (SELECT
        category,
        COUNT(product_id) AS product_count,
        AVG(price) AS avg_price
    FROM
        products
    GROUP BY category
    ) AS category_summary -- 서브쿼리 결과에 별칭 부여 필수
WHERE
    category_summary.avg_price >= 500;

결과: 카테고리별 요약 정보 중 평균 가격이 500 이상인 카테고리만 나옵니다.
실무 팁: GROUP BY, JOIN 등 복잡한 중간 계산을 수행한 후 그 결과를 메인 쿼리에서 다시 다룰 때 매우 유용합니다. CTE (Common Table Expression)가 나오면서 테이블 서브쿼리의 가독성 문제를 개선할 수 있게 되었지만, 여전히 널리 사용됩니다.

4. `IN` / `EXISTS` 를 활용한 서브쿼리 (Conditional Subqueries)

정의: WHERE 절에서 특정 컬럼의 값이 서브쿼리가 반환하는 값 집합에 포함되는지(IN) 또는 서브쿼리가 하나 이상의 행을 반환하는지(EXISTS) 확인하는 데 사용됩니다.

IN: 서브쿼리가 반환하는 값 리스트에 속하는지 확인합니다.
```
-- 주문을 한 적이 있는 고객의 정보만 조회
SELECT
    customer_id,
    customer_name
FROM
    customers
WHERE
    customer_id IN (SELECT customer_id FROM orders); -- orders 테이블에 존재하는 customer_id 목록
```
- 고려사항: IN 서브쿼리는 서브쿼리가 반환하는 결과 집합의 크기가 너무 커지면 성능 문제가 발생할 수 있습니다. NULL 값이 포함되면 예상치 못한 결과가 나올 수 있습니다.
EXISTS: 서브쿼리가 하나라도 행을 반환하는지 여부를 확인합니다. 성능 면에서 IN보다 유리할 때가 많습니다. 특히 상관 서브쿼리 형태로 많이 사용됩니다.
```
-- 주문을 한 적이 있는 고객의 정보만 조회 (EXISTS 사용)
SELECT
    customer_id,
    customer_name
FROM
    customers AS c
WHERE
    EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id);
```
- 고려사항: EXISTS는 서브쿼리 내부에서 SELECT 1처럼 최소한의 컬럼을 선택하는 것이 일반적입니다. 서브쿼리가 특정 조건만 만족하면 되므로 전체 데이터를 가져올 필요가 없습니다. NOT EXISTS는 해당 조건이 없는 경우를 찾을 때 사용됩니다.

5. `WITH` 절을 이용한 CTE (Common Table Expression)

정의: WITH 절은 복잡한 쿼리를 여러 개의 가독성 높은 작은 단위(CTE)로 분리하여 정의할 수 있도록 해줍니다. 각 CTE는 서브쿼리처럼 동작하며, 이후 쿼리에서 참조할 수 있습니다. 테이블 서브쿼리의 가독성 문제를 해결하는 데 매우 효과적입니다.

활용 예시 (블로그 시작 부분의 예시와 동일하지만 다시 한번 강조):

-- CTE를 사용하여 조건에 따라 분류된 후 필터링
WITH categorized_products AS ( -- 첫 번째 CTE 정의
    SELECT
        product_id,
        product_name,
        price,
        CASE
            WHEN price >= 1000 THEN 'Expensive'
            WHEN price BETWEEN 500 AND 999 THEN 'Moderate'
            ELSE 'Cheap'
        END AS price_category
    FROM products
),
expensive_products_summary AS ( -- 두 번째 CTE 정의 (첫 번째 CTE 참조)
    SELECT
        price_category,
        COUNT(product_id) AS num_expensive_products
    FROM
        categorized_products
    WHERE price_category = 'Expensive'
    GROUP BY price_category
)
SELECT
    cp.product_id,
    cp.product_name,
    cp.price,
    eps.num_expensive_products -- 두 번째 CTE의 결과도 함께 조회 가능
FROM
    categorized_products AS cp
JOIN
    expensive_products_summary AS eps ON cp.price_category = eps.price_category
WHERE cp.price_category = 'Expensive';

결과: 가격 카테고리가 'Expensive'인 제품들과 그 제품 수 요약을 함께 조회합니다.
실무 팁:
- 가독성: 쿼리가 길고 복잡해질 때, 중간 단계를 CTE로 분리하여 쿼리의 논리적 흐름을 명확하게 만듭니다.
- 재사용성: 하나의 CTE를 메인 쿼리나 다른 CTE에서 여러 번 참조할 수 있어 코드 중복을 줄입니다.
- 재귀 CTE: 계층적 데이터를 처리할 때 재귀 CTE를 사용할 수 있습니다.
- 성능: 옵티마이저가 CTE를 최적화하는 방식은 서브쿼리와 유사할 수 있지만, 복잡한 쿼리에서는 CTE를 사용함으로써 더 나은 실행 계획을 유도할 수도 있습니다.

13. 기초적인 고급쿼리

CASE 문과 WHERE 절

CASE 문은 SQL에서 조건에 따라 다른 값을 반환하거나 다른 로직을 수행할 때 사용하는 강력한 도구입니다. 주로 SELECT 절에서 특정 컬럼의 값을 동적으로 변경하거나, ORDER BY 절에서 정렬 기준을 유연하게 설정할 때 활용됩니다.

하지만 WHERE 절 내에서 CASE 문을 직접적으로 사용하여 복잡한 필터링 조건을 구현하는 것은 일반적으로 권장되지 않으며, 문법적으로도 제약이 따를 수 있습니다. WHERE 절은 불리언(Boolean) 값을 기대하기 때문입니다.

그렇다면 CASE 문으로 정의된 논리적 결과를 바탕으로 WHERE 절에서 필터링하려면 어떻게 해야 할까요? 바로 서브쿼리(Subquery)나 CTE (Common Table Expression)를 활용하는 것입니다. CASE 문을 통해 계산된 새로운 컬럼을 서브쿼리나 CTE로 먼저 정의한 후, 외부 WHERE 절에서 이 새로운 컬럼을 필터링 조건으로 사용하는 방식입니다. 이 방식은 쿼리의 가독성을 높이고 복잡한 논리를 단계별로 처리하는 데 매우 효과적입니다.

활용 예시

-- CTE를 사용하여 조건에 따라 분류된 후 필터링
-- 이 예시에서는 제품 가격에 따라 'Expensive', 'Moderate', 'Cheap'으로 분류한 뒤,
-- 'Expensive' 카테고리에 속하는 제품만 조회합니다.
WITH categorized_products AS (
    SELECT
        product_id,
        product_name,
        price,
        CASE
            WHEN price >= 1000 THEN 'Expensive'
            WHEN price BETWEEN 500 AND 999 THEN 'Moderate'
            ELSE 'Cheap'
        END AS price_category -- CASE 문을 통해 새로운 컬럼 'price_category' 생성
    FROM products
)
SELECT product_id, product_name, price
FROM categorized_products
WHERE price_category = 'Expensive'; -- 생성된 컬럼을 WHERE 절에서 필터링

배열 컬럼 (ARRAY) 조건

PostgreSQL은 다른 관계형 데이터베이스와 차별화되는 강력한 기능 중 하나로 배열 데이터 타입을 지원합니다. 이는 단일 컬럼 내에 여러 개의 값을 저장해야 할 때 매우 유용하며, JSONB 타입과 함께 복잡한 데이터 구조를 효율적으로 다룰 수 있게 합니다. 배열 컬럼에 대한 WHERE 절 필터링은 특정 요소의 포함 여부, 배열 간의 관계 등을 정교하게 제어할 수 있게 해줍니다.

주요 연산자

ANY / ALL: 배열 내의 어떤 요소라도 (ANY) 또는 모든 요소가 (ALL) 특정 조건을 만족하는지 확인합니다.
@> (contains): 한 배열이 다른 배열의 모든 요소를 포함하는지 확인합니다. (좌측 배열이 우측 배열을 포함)
<@ (is contained by): 한 배열이 다른 배열에 의해 모든 요소를 포함하는지 확인합니다. (좌측 배열이 우측 배열에 포함)
&& (overlap): 두 배열이 공통 요소를 가지는지 확인합니다. 즉, 교집합이 있는지 확인합니다.

활용 예시

-- tags 컬럼(text array)에 'electronics' 태그가 포함된 제품 조회
-- 'electronics' 태그가 하나라도 포함된 모든 제품을 찾습니다.
SELECT product_name, tags
FROM products
WHERE 'electronics' = ANY(tags);

-- tags 컬럼에 'sale'과 'new_arrival' 태그가 모두 포함된 제품 조회
-- 'sale'과 'new_arrival' 두 태그가 모두 포함된 제품을 찾습니다.
SELECT product_name, tags
FROM products
WHERE tags @> ARRAY['sale', 'new_arrival']::text[];

-- tags 컬럼에 'sale' 또는 'clearance' 태그가 포함된 제품 조회
-- 'sale' 태그나 'clearance' 태그 중 하나라도 포함된 제품을 찾습니다.
SELECT product_name, tags
FROM products
WHERE tags && ARRAY['sale', 'clearance']::text[];

14. 기초적인 INDEX

인덱스의 기본 이해와 종류

인덱스는 특정 컬럼의 값을 기준으로 정렬된 복사본을 만들어 데이터 검색 속도를 향상시킵니다. 하지만 인덱스도 무조건 많이 생성한다고 좋은 것은 아닙니다. 인덱스는 쓰기(INSERT, UPDATE, DELETE) 작업 시 추가적인 오버헤드를 발생시키므로, 읽기(SELECT) 성능 향상과 쓰기 성능 저하 사이의 균형을 잘 맞춰야 합니다.

PostgreSQL은 다양한 인덱스 타입을 제공하며, 각각 다른 용도와 장점을 가집니다.

B-tree (기본):
- 가장 일반적이고 기본적으로 사용되는 인덱스입니다.
- =, <, >, <=, >=, BETWEEN, IN 등 대부분의 비교 연산자에 최적화되어 있습니다.
- 문자열, 숫자, 날짜 등 거의 모든 데이터 타입에 적용할 수 있습니다.
- 정렬(ORDER BY) 작업에도 효율적입니다.
Hash:
- = 연산자에만 최적화되어 있습니다.
- B-tree보다 공간 효율적일 수 있지만, 충돌(collision)이 발생할 수 있고 복구 메커니즘이 제한적입니다.
- PostgreSQL 10부터는 WAL(Write-Ahead Log) 로깅을 지원하여 안정성이 향상되었지만, 여전히 B-tree만큼 범용적이지는 않습니다.
GiST (Generalized Search Tree):
- B-tree가 다루기 어려운 복잡한 데이터 타입(예: 지리 공간 데이터 PostGIS, 배열 ARRAY, 전문 검색 Full-Text Search)에 적합한 다목적 인덱스입니다.
- 트리 구조를 일반화하여 다양한 연산자에 대응할 수 있도록 확장 가능합니다.
- 예를 들어, 공간 데이터의 교차(intersection)나 포함(containment) 질의에 효과적입니다.
GIN (Generalized Inverted Index):
- 다중 값 컬럼(multi-valued column)에 대한 검색에 최적화된 역 인덱스입니다.
- 배열(ARRAY), JSONB, 전문 검색(Full-Text Search)과 같은 데이터 타입에서 특정 요소의 존재 여부를 빠르게 찾을 때 강력합니다.
- GiST보다 생성 시간은 오래 걸리지만, 검색 성능은 더 빠를 수 있습니다.
BRIN (Block Range Index):
- 크고 정렬된 데이터셋에서 매우 효율적인 인덱스입니다.
- 데이터의 물리적 저장 위치(블록 범위)를 기반으로 해당 범위 내의 최소/최대 값을 저장하여 인덱스 크기가 매우 작습니다.
- 대규모 로그 데이터나 시계열 데이터와 같이 특정 컬럼이 물리적으로 정렬되어 저장될 때 (timestamp나 id 컬럼 등) 큰 효과를 발휘합니다.
- =, <, >, <=, >= 등의 비교 연산자에 적합합니다.

실무 인덱스 활용 전략

인덱스를 효과적으로 활용하려면 쿼리 패턴을 분석하고, 데이터 특성을 고려하여 적절한 인덱스 타입을 선택하고 조합해야 합니다.

1. 단일 컬럼 인덱스 (B-tree)

가장 기본적인 인덱스로, WHERE 절이나 ORDER BY 절에 자주 사용되는 컬럼에 생성합니다.

-- `product_id` 컬럼에 인덱스 생성
CREATE INDEX idx_products_product_id ON products (product_id);

-- `product_name` 컬럼에 인덱스 생성 (문자열 검색 성능 향상)
CREATE INDEX idx_products_product_name ON products (product_name);

2. 다중 컬럼 인덱스 (복합 인덱스, B-tree)

여러 컬럼이 WHERE 절에 함께 사용되거나 ORDER BY 절에 사용될 때 유용합니다. 인덱스 생성 시 컬럼의 순서가 중요하며, 쿼리에서 가장 먼저 필터링되는 컬럼을 인덱스의 선두에 두는 것이 일반적입니다.

-- `category_id`와 `price` 컬럼에 복합 인덱스 생성
-- WHERE category_id = X AND price > Y 와 같은 쿼리에 유리
CREATE INDEX idx_products_category_price ON products (category_id, price);

-- ORDER BY price DESC, product_name ASC 와 같은 쿼리에 유리
-- 정렬 방향까지 지정 가능 (PostgreSQL 8.3 이상)
CREATE INDEX idx_products_price_name_desc ON products (price DESC, product_name ASC);

3. 부분 인덱스 (Partial Index)

테이블의 특정 부분에만 인덱스를 적용하여 인덱스 크기를 줄이고, 인덱스 유지 보수 비용을 절감하며, 특정 조건의 쿼리 성능을 향상시킬 수 있습니다. 특히 데이터의 대부분이 특정 상태 값을 가질 때 유용합니다.

-- 'active' 상태인 사용자만 자주 조회될 때
-- is_active = true 인 레코드에만 인덱스 생성
CREATE INDEX idx_users_active_email ON users (email) WHERE is_active = TRUE;

-- 주문 상태가 'pending'인 주문만 자주 조회될 때
CREATE INDEX idx_orders_pending_order_date ON orders (order_date) WHERE status = 'pending';

4. 표현식 인덱스 (Expression Index)

컬럼 값 자체가 아닌, 컬럼에 함수나 연산자를 적용한 결과에 인덱스를 생성할 수 있습니다. WHERE 절에서 자주 사용되는 함수 호출 결과를 인덱싱할 때 유용합니다.

-- `UPPER()` 함수를 사용하여 대소문자 구분 없이 검색할 때
-- WHERE UPPER(product_name) = 'APPLE' 과 같은 쿼리에 유리
CREATE INDEX idx_products_upper_name ON products (UPPER(product_name));

-- 날짜 컬럼에서 날짜 부분만 추출하여 검색할 때
-- WHERE date_trunc('day', order_date) = '2023-01-01' 과 같은 쿼리에 유리
CREATE INDEX idx_orders_order_date_trunc_day ON orders (date_trunc('day', order_date));

5. 배열 컬럼 인덱싱 (GIN)

배열 컬럼(ARRAY)이나 JSONB 컬럼 내의 특정 요소에 대한 검색 성능을 향상시키려면 GIN 인덱스를 사용해야 합니다. 위에서 언급한 ANY, @>, && 연산자들과 함께 사용될 때 강력합니다.

-- tags 컬럼(text array)의 요소 검색 성능 향상
-- WHERE 'electronics' = ANY(tags); 와 같은 쿼리에 유리
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- `product_details` (JSONB) 컬럼의 특정 키/값 검색 성능 향상
-- WHERE product_details @> '{"color": "red"}' 와 같은 쿼리에 유리
CREATE INDEX idx_products_details_gin ON products USING GIN (product_details);

인덱스 사용 시 주의사항 및 팁

EXPLAIN ANALYZE로 쿼리 계획 분석: 인덱스가 실제로 사용되는지, 어떤 인덱스가 사용되는지 확인하는 가장 중요한 도구입니다. 쿼리를 실행하기 전에 항상 EXPLAIN ANALYZE를 사용하여 쿼리 성능을 평가하고 인덱스 튜닝의 방향을 잡으세요.
너무 많은 인덱스 피하기: 인덱스는 쓰기 성능을 저하시키고 디스크 공간을 차지합니다. 꼭 필요한 인덱스만 최소한으로 유지하는 것이 중요합니다.
인덱스 무효화 조건:
- 인덱싱된 컬럼에 함수나 연산을 적용하면 인덱스를 사용하지 못할 수 있습니다 (표현식 인덱스를 사용하지 않는 한).
- LIKE '%값' 처럼 선행 와일드카드(%)를 사용하면 인덱스를 사용할 수 없습니다 (pg_trgm 모듈의 GIN/GiST 인덱스를 사용하지 않는 한).
- 데이터 타입 불일치도 인덱스 사용을 방해할 수 있습니다.
클러스터링(Clustering): CLUSTER 명령을 사용하여 테이블을 인덱스 순서대로 물리적으로 재정렬할 수 있습니다. 이는 특정 쿼리에서 인덱스 스캔 효율을 극대화할 수 있지만, 대규모 테이블에서는 시간과 I/O 비용이 많이 들고 데이터 변경 시 다시 분산될 수 있습니다.

마무리

PostgreSQL의 인덱스는 단순한 CREATE INDEX 문을 넘어, 다양한 타입과 옵션을 통해 복잡한 쿼리 패턴과 데이터 특성에 최적화될 수 있습니다. 여러분의 데이터베이스 워크로드에 맞는 인덱싱 전략을 수립하고, EXPLAIN ANALYZE를 통해 지속적으로 쿼리 성능을 모니터링한다면, PostgreSQL의 강력한 성능을 실무에서 최대한으로 끌어낼 수 있을 것입니다.

이 외에도 PostgreSQL의 어떤 성능 최적화 기법에 대해 더 알아보고 싶으신가요?
블로그 글의 주제를 PostgreSQL의 인덱스로 변경하셨군요! 아주 좋은 선택입니다. 인덱스는 데이터베이스 성능 최적화의 핵심 요소이며, 실무에서 그 중요성이 매우 큽니다.

제공해주신 쿼리 예시(CASE 문과 배열 컬럼)는 고급 쿼리 기법에 대한 내용이므로, 인덱스에 대한 블로그 글에서는 해당 내용들을 삭제하고 인덱스에 초점을 맞추는 것이 좋습니다.

다음은 PostgreSQL 인덱스에 대한 블로그 글의 구성과 포함하면 좋을 내용들입니다.

PostgreSQL 실무 인덱스 활용 가이드: 쿼리 성능 최적화의 핵심

PostgreSQL은 강력한 데이터베이스이지만, 대량의 데이터에서 원하는 정보를 빠르게 찾아내기 위해서는 인덱스(Index)의 효율적인 활용이 필수적입니다. 인덱스는 마치 책의 목차나 찾아보기와 같아서, 데이터베이스가 전체 테이블을 스캔하지 않고도 필요한 데이터를 신속하게 찾을 수 있도록 돕습니다.

이 글에서는 PostgreSQL에서 실무 수준으로 인덱스를 효과적으로 활용하는 방법과 다양한 인덱스 유형, 그리고 성능 최적화를 위한 팁을 알아보겠습니다.

인덱스란 무엇이며 왜 필요한가?

인덱스는 테이블의 하나 이상의 컬럼에 대해 정렬된 데이터 구조를 생성하여, SELECT 쿼리의 데이터 조회 속도를 향상시키는 데이터베이스 객체입니다. 특히 WHERE 절, JOIN 조건, ORDER BY 절 등에 자주 사용되는 컬럼에 인덱스를 생성하면 쿼리 성능을 크게 개선할 수 있습니다.

장점:

빠른 데이터 조회: 특정 조건을 만족하는 데이터를 빠르게 찾고 정렬하는 데 유리합니다.
고유성(Uniqueness) 보장: UNIQUE 인덱스를 통해 특정 컬럼의 값이 중복되지 않도록 강제할 수 있습니다. (예: 사용자 이메일)

단점 (주의사항):

쓰기(Write) 작업 부하: INSERT, UPDATE, DELETE 시 인덱스도 함께 갱신되어야 하므로, 쓰기 작업 성능에는 부정적인 영향을 줄 수 있습니다.
저장 공간 사용: 인덱스도 데이터이므로 추가적인 디스크 공간을 차지합니다.
유지보수 오버헤드: 인덱스가 많아질수록 데이터 변경 시 발생하는 오버헤드가 커지며, 주기적인 VACUUM 및 ANALYZE 작업이 중요해집니다.

PostgreSQL의 다양한 인덱스 유형

PostgreSQL은 다양한 쿼리 패턴과 데이터 타입에 최적화된 여러 인덱스 유형을 제공합니다.

1. B-Tree 인덱스 (기본값)

가장 일반적인 인덱스 유형이며, CREATE INDEX 문으로 인덱스를 생성할 때 특별히 지정하지 않으면 B-Tree 인덱스가 만들어집니다.
활용: 등호(=), 부등호 (<, >, <=, >=), BETWEEN, IN, IS NULL, IS NOT NULL 등 광범위한 조건에서 효율적입니다.
정렬(Sorting): ORDER BY 절이 있는 쿼리에서도 B-Tree 인덱스가 유용하게 사용될 수 있습니다.

-- product_name 컬럼에 B-Tree 인덱스 생성
CREATE INDEX idx_products_name ON products (product_name);

-- 여러 컬럼에 걸쳐 복합 B-Tree 인덱스 생성 (순서 중요!)
-- 이 인덱스는 (category_id, price) 순으로 필터링하거나 정렬할 때 유용합니다.
CREATE INDEX idx_products_category_price ON products (category_id, price DESC);

팁: 복합 인덱스에서는 컬럼의 순서가 중요합니다. 쿼리의 WHERE 절에서 가장 자주 사용되거나, 등호(=) 조건으로 필터링되는 컬럼을 왼쪽에 배치하는 것이 좋습니다.

2. Hash 인덱스

활용: 등호(=) 비교에만 사용됩니다.
특징: PostgreSQL 10 이전 버전에서는 트랜잭션 안정성 문제가 있었으나, 현재는 개선되어 사용할 수 있습니다. 그러나 B-Tree 인덱스가 일반적으로 더 유연하고 광범위하게 사용될 수 있어, Hash 인덱스는 특정 상황이 아니면 잘 사용되지 않습니다.

-- user_email 컬럼에 Hash 인덱스 생성 (등호 비교에만 효과적)
CREATE INDEX idx_users_email_hash ON users USING HASH (user_email);

3. GiST (Generalized Search Tree) 인덱스

활용: 복잡한 데이터 타입 (지리 정보, 전문 검색, 배열, 네트워크 주소 등) 및 연산자 클래스를 지원합니다. 특정 데이터 범위, 중첩, 공간 연산 등에 유용합니다.
예시: PostGIS (지리 정보 시스템)에서 공간 인덱싱에 활용됩니다.

-- 공간 데이터 타입(geometry)에 GiST 인덱스 생성 (PostGIS 확장 기능 사용 시)
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

4. GIN (Generalized Inverted Index) 인덱스

활용: 배열(ARRAY) 데이터나 JSONB, 전문 검색(Full-Text Search)과 같이 단일 컬럼 내에 여러 개의 검색 가능한 값(예: 태그 목록, 문서의 단어들)이 있는 경우에 매우 강력합니다.
ANY, @>, && (배열 연산자), @@ (전문 검색 연산자) 등 복잡한 연산자를 효율적으로 처리할 수 있습니다.

-- tags 컬럼(text array)에 GIN 인덱스 생성
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- JSONB 컬럼(data)에 GIN 인덱스 생성 (특정 키-값 검색에 유용)
CREATE INDEX idx_orders_data_gin ON orders USING GIN (data);

고급 인덱스 기법

1. 부분(Partial) 인덱스

정의: WHERE 절을 포함하여 테이블의 일부 행에만 인덱스를 생성하는 것입니다.
활용: 특정 조건에 해당하는 데이터가 전체 데이터 중 극히 일부인 경우, 인덱스 크기를 줄이고 관리 효율성을 높일 수 있습니다. (예: status = 'active'인 사용자만 인덱싱)

-- 활성(active) 상태의 사용자만 인덱싱하여, 자주 조회되는 활성 사용자 쿼리 성능 개선
CREATE INDEX idx_users_active_email ON users (email) WHERE status = 'active';

2. 표현식(Expression) 인덱스

정의: 컬럼 자체가 아닌, 컬럼에 적용된 함수나 표현식의 결과에 인덱스를 생성하는 것입니다.
활용: LOWER(), MD5() 등 함수를 사용하여 쿼리하는 경우, 해당 표현식에 인덱스를 걸어 성능을 높일 수 있습니다.

-- 이메일 주소의 대소문자 구분 없이 검색하는 쿼리를 위한 인덱스
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- (쿼리 예시: SELECT * FROM users WHERE LOWER(email) = 'test@example.com';)

3. 고유(Unique) 인덱스

정의: 인덱스된 컬럼의 모든 값이 고유함을 보장합니다. PRIMARY KEY나 UNIQUE 제약 조건을 생성하면 자동으로 고유 인덱스가 생성됩니다.
활용: 데이터 무결성을 보장하고, 고유 값을 통한 빠른 검색에 최적화되어 있습니다.

-- user_id 컬럼에 고유 인덱스 생성 (동시에 PRIMARY KEY 제약 조건 생성)
ALTER TABLE users ADD PRIMARY KEY (user_id);

-- username 컬럼에 고유 인덱스 생성
CREATE UNIQUE INDEX idx_users_username ON users (username);

4. CONCURRENTLY 인덱스 생성

정의: 대용량 테이블에 인덱스를 생성할 때, CONCURRENTLY 옵션을 사용하면 테이블에 락(Lock)을 걸지 않고 인덱스를 생성할 수 있습니다. 즉, 인덱스 생성 중에도 다른 트랜잭션이 해당 테이블에 데이터를 읽거나 쓸 수 있습니다.
활용: 서비스 운영 중인 프로덕션 환경에서 다운타임 없이 인덱스를 추가할 때 필수적입니다.

-- 대용량 테이블에 인덱스를 만들 때 서비스 중단을 피하기 위해 사용
CREATE INDEX CONCURRENTLY idx_orders_date ON orders (order_date);

주의: CONCURRENTLY로 인덱스를 삭제할 때도 동일하게 사용 (DROP INDEX CONCURRENTLY idx_name;)

15. 기초적인 트랜잭션

블로그 주제를 PostgreSQL 트랜잭션으로 다시 변경하셨군요! 트랜잭션은 데이터의 일관성과 무결성을 보장하는 매우 중요한 개념이므로, 실무에서 정확히 이해하고 사용하는 것이 필수적입니다.

제공해주신 쿼리 예시들은 트랜잭션과 직접적인 관련이 없으므로, 블로그 글에서는 해당 내용들을 제외하고 트랜잭션에 집중하는 것이 좋겠습니다.

아래는 PostgreSQL 트랜잭션에 대한 블로그 글의 구성과 포함하면 좋을 내용들입니다.

PostgreSQL 실무 트랜잭션 활용 가이드: 데이터 일관성 및 무결성 보장

데이터베이스를 다루는 실무에서 트랜잭션(Transaction)은 데이터의 일관성(Consistency)과 무결성(Integrity)을 보장하는 핵심 메커니즘입니다. 특히 여러 개의 데이터베이스 작업이 하나의 논리적인 단위로 묶여 실행되어야 할 때 트랜잭션은 빛을 발합니다. 예를 들어, 은행 송금과 같이 돈이 한 계좌에서 인출되고 다른 계좌로 입금되는 일련의 작업은 모두 성공하거나 모두 실패해야 합니다.

이 글에서는 PostgreSQL에서 트랜잭션을 실무 수준으로 효과적으로 활용하는 방법과 트랜잭션의 주요 특성, 그리고 격리 수준(Isolation Level)에 대해 자세히 알아보겠습니다.

트랜잭션이란 무엇이며 왜 필요한가?

트랜잭션은 데이터베이스의 논리적인 작업 단위입니다. 여러 SQL 문들이 하나의 그룹으로 묶여 실행되며, 이 그룹 내의 모든 작업이 성공적으로 완료되면 (COMMIT) 변경 사항이 영구적으로 저장되고, 도중에 하나라도 실패하거나 취소되면 모든 변경 사항이 되돌려집니다 (ROLLBACK).

트랜잭션은 데이터베이스의 중요한 속성인 ACID 특성을 보장합니다.

Atomicity (원자성): 트랜잭션 내의 모든 작업은 전부 실행되거나 전혀 실행되지 않아야 합니다. (All or Nothing)
Consistency (일관성): 트랜잭션이 성공적으로 완료되면 데이터베이스는 항상 유효하고 일관된 상태를 유지해야 합니다.
Isolation (격리성): 여러 트랜잭션이 동시에 실행될 때, 각 트랜잭션은 마치 독립적으로 실행되는 것처럼 동작해야 합니다. (다른 트랜잭션의 영향을 받지 않음)
Durability (영속성): 트랜잭션이 성공적으로 커밋되면, 그 변경 사항은 시스템 장애와 관계없이 영구적으로 저장되어야 합니다.

PostgreSQL에서 트랜잭션 사용하기

PostgreSQL에서 트랜잭션을 명시적으로 시작하고 종료하는 방법은 매우 간단합니다.

기본 트랜잭션 명령어

BEGIN; 또는 START TRANSACTION;: 새로운 트랜잭션을 시작합니다.
COMMIT;: 현재 트랜잭션 내의 모든 변경 사항을 영구적으로 저장하고 트랜잭션을 종료합니다.
ROLLBACK;: 현재 트랜잭션 내의 모든 변경 사항을 취소하고 트랜잭션을 시작하기 전의 상태로 되돌립니다.

활용 예시: 은행 송금 시나리오

-- 1. 트랜잭션 시작
BEGIN;

-- 2. 계좌 A에서 1000원 인출
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 'A123';

-- 3. 계좌 B로 1000원 입금
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 'B456';

-- 4. 조건부 커밋/롤백
-- 만약 모든 작업이 성공했다면
COMMIT;

-- 만약 중간에 오류가 발생했거나 특정 조건에 따라 취소해야 한다면
-- ROLLBACK;

위 예시에서 UPDATE 문 두 개는 하나의 논리적인 송금 작업을 구성합니다. 만약 두 번째 UPDATE가 실패한다면, ROLLBACK을 통해 첫 번째 UPDATE로 인한 변경 사항(계좌 A에서 1000원 인출)도 함께 취소되어 데이터 일관성이 유지됩니다.

저장점(Savepoint) 활용하기

복잡한 트랜잭션 내에서 부분적으로 롤백해야 할 필요가 있을 때 저장점(Savepoint)을 사용할 수 있습니다. 저장점은 트랜잭션 내에서 특정 지점을 표시하고, 문제가 발생했을 때 그 지점까지만 롤백할 수 있게 해줍니다.

SAVEPOINT savepoint_name;: 현재 트랜잭션 내에 savepoint_name이라는 이름의 저장점을 생성합니다.
ROLLBACK TO SAVEPOINT savepoint_name;: 지정된 저장점까지의 모든 변경 사항을 롤백합니다. 하지만 트랜잭션 자체는 여전히 활성 상태입니다.
RELEASE SAVEPOINT savepoint_name;: 저장점을 해제합니다. 더 이상 해당 저장점으로 롤백할 수 없습니다.

활용 예시: 복합적인 주문 처리

BEGIN;

-- 1단계: 주문 정보 생성
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES ('ORD001', 'CUST001', NOW(), 0);

-- 저장점 설정: 주문 상세 항목 처리 시작 전
SAVEPOINT before_order_items;

-- 2단계: 주문 상세 항목 추가 (오류 발생 가능성이 있는 부분)
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES ('ORD001', 'PROD001', 2, 500); -- 첫 번째 상품
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES ('ORD001', 'PROD002', 1, 1200); -- 두 번째 상품

-- 만약 두 번째 상품 추가 중 문제가 발생하면, 첫 번째 상품 추가까지만 롤백
-- ROLLBACK TO SAVEPOINT before_order_items;

-- 저장점 해제 (선택 사항, 롤백하지 않고 계속 진행할 경우)
RELEASE SAVEPOINT before_order_items;

-- 3단계: 주문 총액 업데이트 (성공적으로 진행되었다고 가정)
UPDATE orders
SET total_amount = (SELECT SUM(quantity * price) FROM order_items WHERE order_id = 'ORD001')
WHERE order_id = 'ORD001';

COMMIT; -- 모든 작업 성공적으로 완료

트랜잭션 격리 수준 (Isolation Level)

여러 트랜잭션이 동시에 실행될 때, 서로에게 어떤 영향을 미칠지 정의하는 것이 격리 수준입니다. PostgreSQL은 SQL 표준에 정의된 네 가지 격리 수준을 모두 지원하며, 기본값은 READ COMMITTED입니다.

SET TRANSACTION ISOLATION LEVEL level;: 현재 트랜잭션의 격리 수준을 설정합니다. (트랜잭션 시작 전에 설정해야 함)

1. `READ UNCOMMITTED` (PostgreSQL에서는 `READ COMMITTED`로 자동 상향 조정)

가장 낮은 격리 수준으로, 커밋되지 않은 다른 트랜잭션의 변경 사항(Dirty Read)을 읽을 수 있습니다.
PostgreSQL은 이 수준을 명시하더라도 내부적으로 READ COMMITTED처럼 동작하여 Dirty Read를 방지합니다.

2. `READ COMMITTED` (PostgreSQL 기본값)

커밋된 데이터만 읽을 수 있습니다. (Dirty Read 방지)
하나의 트랜잭션 내에서 같은 SELECT 쿼리를 여러 번 실행해도, 중간에 다른 트랜잭션이 커밋한 데이터는 읽을 수 있습니다. 이로 인해 Non-Repeatable Read (반복 불가능한 읽기) 현상이 발생할 수 있습니다. (처음 읽었을 때와 다르게 읽힐 수 있음)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- 여기서 SELECT A
-- 다른 트랜잭션이 A를 변경하고 COMMIT
-- 여기서 SELECT A (다른 결과가 나올 수 있음: Non-Repeatable Read)
COMMIT;

3. `REPEATABLE READ`

트랜잭션이 시작된 시점의 스냅샷(Snapshot)을 사용하여 데이터를 읽습니다.
트랜잭션이 종료될 때까지 동일한 SELECT 쿼리는 항상 같은 결과를 반환합니다. (Non-Repeatable Read 방지)
하지만 새로운 행이 삽입되는 Phantom Read (유령 읽기) 현상은 여전히 발생할 수 있습니다. (처음엔 없었던 데이터가 나중에 나타나는 경우)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- 여기서 SELECT COUNT(*) from orders WHERE status = 'pending'; (결과: 5)
-- 다른 트랜잭션이 새로운 'pending' 상태의 주문을 INSERT하고 COMMIT
-- 여기서 SELECT COUNT(*) from orders WHERE status = 'pending'; (결과: 5, 동일한 스냅샷)
-- 하지만 만약 UPDATE/DELETE를 하고 다시 SELECT하면 Conflict 발생 가능성 있음
COMMIT;

4. `SERIALIZABLE`

가장 높은 격리 수준입니다. 여러 트랜잭션이 동시에 실행되더라도, 마치 직렬적으로(순차적으로) 실행되는 것과 같은 결과를 보장합니다.
Dirty Read, Non-Repeatable Read, Phantom Read 등 모든 동시성 문제를 방지합니다.
주의: 동시성이 크게 저하될 수 있으며, 직렬화 실패(Serialization Failure) 오류가 발생하여 트랜잭션이 롤백될 수 있습니다. 이 경우 애플리케이션에서 트랜잭션을 재시도하는 로직이 필요합니다.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- 트랜잭션 내에서 읽고 쓰는 모든 작업이 다른 트랜잭션과 충돌하지 않도록 보장
-- 충돌 시 Serialization Failure 오류 (SQLSTATE 40001) 발생 가능
COMMIT;

실무에서 고려할 점

기본값 사용: 대부분의 웹 애플리케이션에서는 PostgreSQL의 기본 격리 수준인 READ COMMITTED로도 충분합니다. 이는 성능과 데이터 일관성 사이의 좋은 균형을 제공합니다.
고려 없는 SERIALIZABLE 사용 금지: SERIALIZABLE은 가장 안전하지만, 동시성이 매우 중요한 시스템에서는 성능 병목의 원인이 될 수 있습니다. 정말로 강력한 일관성이 요구되는 경우 (예: 금융 거래 시스템)에만 신중하게 사용하고, 애플리케이션 레벨에서 재시도 로직을 반드시 구현해야 합니다.
FOR UPDATE / FOR SHARE 절: SELECT 문에 FOR UPDATE나 FOR SHARE를 추가하여 특정 행에 대한 락(Lock)을 걸어 동시성 문제를 더욱 명확하게 제어할 수 있습니다. 이는 REPEATABLE READ나 SERIALIZABLE 격리 수준에서 특정 행에 대한 동시성 이슈를 더욱 강력하게 해결하는 데 도움이 됩니다.
```
BEGIN;
SELECT * FROM products WHERE product_id = 123 FOR UPDATE; -- 해당 행에 설정
-- 재고 감소, 주문 생성 등 작업 수행
UPDATE products SET stock = stock - 1 WHERE product_id = 123;
COMMIT;
```

16. 기초적인 VIEW

VIEW란 무엇이며 왜 필요한가?

VIEW는 하나 이상의 테이블에서 데이터를 가져와 정의된 SELECT 문을 기반으로 하는 가상 테이블입니다. VIEW는 데이터를 실제로 저장하지 않으며, VIEW를 조회할 때마다 VIEW 정의에 포함된 SELECT 문이 실행되어 최신 데이터를 가져옵니다.

VIEW의 주요 장점:

복잡성 단순화 (Abstraction): 여러 테이블을 조인하거나 복잡한 계산을 수행하는 쿼리를 VIEW로 정의해두면, 사용자는 복잡한 원본 쿼리를 알 필요 없이 VIEW를 단순한 테이블처럼 조회할 수 있습니다.
보안 강화 (Security): 특정 사용자에게 전체 테이블이 아닌 VIEW에 대한 접근 권한만 부여하여, 민감한 컬럼이나 행을 숨길 수 있습니다. 예를 들어, 급여 정보가 포함된 직원 테이블에서 급여 컬럼을 제외한 VIEW를 생성하여 일반 사용자에게 제공할 수 있습니다.
데이터 일관성 및 재사용성 (Consistency & Reusability): 자주 사용되는 복잡한 쿼리를 VIEW로 만들어두면, 여러 애플리케이션이나 보고서에서 동일한 논리를 일관되게 재사용할 수 있습니다.
호환성 유지 (Compatibility): 원본 테이블의 스키마가 변경되더라도, VIEW의 정의를 수정함으로써 외부 애플리케이션의 쿼리를 변경하지 않고도 호환성을 유지할 수 있습니다.

PostgreSQL에서 VIEW 생성 및 사용하기

VIEW는 CREATE VIEW 문을 사용하여 생성하며, 일반 테이블과 동일하게 SELECT 문으로 조회할 수 있습니다.

1. 기본 VIEW 생성 및 조회

가장 기본적인 형태의 VIEW는 하나 또는 여러 테이블에서 특정 컬럼을 선택하거나 필터링하는 쿼리입니다.

-- 예시: products 테이블에서 가격이 1000 이상인 'Expensive' 제품만 보여주는 VIEW
CREATE VIEW expensive_products AS
SELECT
    product_id,
    product_name,
    price
FROM products
WHERE price >= 1000;

-- VIEW 조회 (일반 테이블처럼 사용)
SELECT * FROM expensive_products;

-- 특정 조건 추가하여 조회
SELECT product_name FROM expensive_products WHERE product_id = 101;

2. 복잡한 쿼리를 VIEW로 캡슐화

여러 테이블을 조인하거나 집계 함수를 사용하는 복잡한 쿼리도 VIEW로 만들 수 있습니다.

-- 예시: 각 카테고리별 총 판매액을 보여주는 VIEW
CREATE VIEW category_sales_summary AS
SELECT
    c.category_name,
    SUM(oi.quantity * oi.price) AS total_sales_amount,
    COUNT(DISTINCT o.order_id) AS total_orders
FROM categories c
JOIN products p ON c.category_id = p.category_id
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
GROUP BY c.category_name;

-- VIEW 조회
SELECT category_name, total_sales_amount
FROM category_sales_summary
ORDER BY total_sales_amount DESC;

3. 컬럼 이름 지정

VIEW를 생성할 때 컬럼 이름을 명시적으로 지정할 수 있습니다. 이는 원본 쿼리의 컬럼 이름이 복잡하거나 명확하지 않을 때 유용합니다.

CREATE VIEW active_users_info (user_id, full_name, user_email, registration_date) AS
SELECT
    id,
    first_name || ' ' || last_name, -- 함수를 사용한 컬럼
    email,
    created_at
FROM users
WHERE is_active = TRUE;

SELECT user_id, full_name FROM active_users_info;

VIEW의 유형 및 고급 활용

1. 업데이트 가능한 VIEW (Updatable Views)

특정 조건을 만족하는 VIEW는 INSERT, UPDATE, DELETE와 같은 DML(Data Manipulation Language) 작업을 수행할 수 있습니다.
조건:
- VIEW가 하나의 테이블만을 기반으로 해야 합니다.
- 집계 함수, GROUP BY, DISTINCT, UNION, INTERSECT, EXCEPT를 포함하지 않아야 합니다.
- VIEW의 모든 컬럼이 원본 테이블의 컬럼과 직접적으로 매핑되어야 합니다. (계산된 컬럼, 함수 결과 컬럼 등은 업데이트 불가능)
WITH CHECK OPTION: VIEW를 통해 삽입/갱신되는 행이 VIEW의 WHERE 절 조건을 만족하는지 강제합니다. 이를 통해 VIEW의 정의 범위 내에서만 데이터가 변경되도록 할 수 있습니다.

-- 예시: 활성 사용자만 보여주는 VIEW, 업데이트 가능
CREATE VIEW active_users AS
SELECT user_id, username, email
FROM users
WHERE is_active = TRUE;

-- VIEW를 통한 데이터 업데이트 (원본 테이블에 반영됨)
UPDATE active_users SET email = 'new_email@example.com' WHERE user_id = 1;

-- WITH CHECK OPTION을 추가하여 VIEW의 조건 유지 강제
CREATE OR REPLACE VIEW active_users_with_check AS
SELECT user_id, username, email, is_active
FROM users
WHERE is_active = TRUE
WITH CHECK OPTION; -- is_active가 FALSE로 변경되는 UPDATE 방지

-- 이 UPDATE는 성공: is_active가 TRUE로 유지되므로
UPDATE active_users_with_check SET email = 'another@example.com' WHERE user_id = 1;

-- 이 UPDATE는 실패: is_active가 FALSE로 변경되므로 VIEW의 조건에 위배
-- UPDATE active_users_with_check SET is_active = FALSE WHERE user_id = 1;
-- ERROR:  new row violates check option for view "active_users_with_check"

2. 재귀 VIEW (Recursive Views) - CTE와 유사

WITH RECURSIVE를 사용하여 재귀적인 쿼리 결과를 VIEW로 저장할 수 있습니다. 이는 계층형 데이터(조직도, 댓글 스레드 등)를 다룰 때 유용합니다.

-- 예시: 직원과 그들의 모든 상사를 보여주는 계층형 VIEW
CREATE RECURSIVE VIEW employee_hierarchy (employee_id, employee_name, manager_id, level) AS
SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    0 AS level
FROM employees e
WHERE e.manager_id IS NULL -- 최상위 관리자

UNION ALL

SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id;

SELECT * FROM employee_hierarchy;

3. Materialized View (구체화된 뷰) - 성능 최적화의 핵심

정의: Materialized View는 일반 VIEW와 달리, SELECT 쿼리의 결과를 디스크에 물리적으로 저장하는 VIEW입니다.
장점: 쿼리 실행 시마다 원본 쿼리를 다시 실행하지 않으므로 조회 성능이 매우 빠릅니다.
단점: 원본 테이블의 데이터가 변경되어도 Materialized View는 자동으로 업데이트되지 않습니다. 최신 데이터를 반영하려면 수동으로 REFRESH MATERIALIZED VIEW 명령을 실행해야 합니다.
활용: 자주 조회되지만 데이터 변경 빈도가 낮거나, 약간의 데이터 지연을 허용하는 대시보드, 보고서, 캐시 데이터 등에 적합합니다.

-- 예시: 일별 총 판매액 Materialized View 생성
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
    DATE_TRUNC('day', order_date) AS sale_date,
    SUM(total_amount) AS daily_total_sales
FROM orders
GROUP BY DATE_TRUNC('day', order_date)
ORDER BY sale_date;

-- Materialized View 조회
SELECT * FROM daily_sales_summary WHERE sale_date = '2025-06-17';

-- 원본 데이터가 변경되었을 경우, Materialized View 갱신
-- (이 작업은 데이터 양에 따라 시간이 소요될 수 있으며, 락이 발생할 수 있음)
REFRESH MATERIALIZED VIEW daily_sales_summary;

-- 락을 최소화하면서 갱신 (PostgreSQL 9.4 이상)
-- Materialized View에 인덱스가 있다면 CONCURRENTLY 사용 가능
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

팁: Materialized View는 일반 테이블처럼 인덱스를 생성할 수 있어, 조회 성능을 더욱 높일 수 있습니다.

VIEW 관리 및 주의사항

VIEW 변경: ALTER VIEW는 제한적이며, 보통 CREATE OR REPLACE VIEW를 사용하여 VIEW 정의를 완전히 대체합니다.

CREATE OR REPLACE VIEW expensive_products AS
SELECT product_id, product_name, price, description -- description 컬럼 추가
FROM products
WHERE price >= 1000;

VIEW 삭제: DROP VIEW view_name; 또는 DROP MATERIALIZED VIEW view_name;
성능 고려: 일반 VIEW는 매번 쿼리를 실행하므로, 너무 복잡하거나 자주 조회되는 VIEW는 성능 병목이 될 수 있습니다. 이런 경우 Materialized View나 적절한 인덱스 생성을 고려해야 합니다. EXPLAIN ANALYZE로 VIEW를 조회하는 쿼리의 실행 계획을 분석하는 것이 중요합니다.
의존성: VIEW는 원본 테이블에 의존합니다. 원본 테이블의 이름이나 컬럼이 변경되면 VIEW가 깨질 수 있습니다. pg_views 또는 pg_matviews 시스템 카탈로그 뷰를 통해 VIEW 정의를 확인할 수 있습니다.

17. 기초적인 권한관리

PostgreSQL 권한 관리의 기본 개념

PostgreSQL의 권한 관리는 주로 역할(Role)을 기반으로 합니다. 역할은 사용자(User)와 그룹(Group)의 개념을 통합한 것으로, 권한을 개별 사용자에게 직접 부여하는 대신 역할에 부여하고, 사용자들을 해당 역할에 할당하는 방식으로 관리됩니다. 이 방식은 대규모 시스템에서 권한 관리를 훨씬 효율적으로 만들어 줍니다.

주요 개념:

역할 (Role): 데이터베이스에 로그인할 수 있는 권한 (LOGIN), 데이터베이스 객체에 접근할 수 있는 권한 등을 가질 수 있는 엔티티입니다. 사용자 또는 그룹으로 사용될 수 있습니다.
권한 (Privilege): 특정 데이터베이스 객체(테이블, 스키마, 데이터베이스 등)에 대해 수행할 수 있는 작업(SELECT, INSERT, UPDATE, DELETE 등)을 정의합니다.
소유자 (Owner): 모든 데이터베이스 객체는 소유자를 가집니다. 소유자는 해당 객체에 대한 모든 권한을 암묵적으로 가집니다.

PostgreSQL 역할(Role) 생성 및 관리

새로운 사용자나 애플리케이션 접속 계정을 만들 때는 CREATE ROLE 또는 CREATE USER (login 권한이 포함된 role) 명령어를 사용합니다.

1. 역할 생성

-- 가장 기본적인 역할 (로그인 권한만 있는 사용자)
CREATE ROLE my_app_user WITH LOGIN PASSWORD 'strong_password';

-- 데이터베이스 생성 권한 및 역할 생성 권한을 가진 역할 (개발 리더 등)
CREATE ROLE dev_lead WITH LOGIN CREATEDB CREATEROLE PASSWORD 'dev_lead_pw';

-- 만료 기한을 가진 역할 (임시 접근용)
CREATE ROLE temporary_analyst WITH LOGIN PASSWORD 'temp_pw' VALID UNTIL '2025-07-31';

-- 슈퍼유저 (최고 권한, 신중하게 사용)
CREATE ROLE super_admin WITH LOGIN SUPERUSER PASSWORD 'super_admin_pw';

2. 역할 속성 변경

ALTER ROLE 명령어로 역할의 속성을 변경할 수 있습니다.

-- 비밀번호 변경
ALTER ROLE my_app_user WITH PASSWORD 'new_strong_password';

-- 로그인 권한 제거 (역할은 유지되나 로그인 불가)
ALTER ROLE temporary_analyst NOLOGIN;

-- 데이터베이스 생성 권한 추가
ALTER ROLE dev_lead CREATEDB;

3. 역할 삭제

-- 역할 삭제 (해당 역할이 소유한 객체가 없어야 함)
DROP ROLE my_app_user;

-- 강제 삭제 (소유한 객체도 함께 삭제 또는 재할당)
-- DROP ROLE my_app_user CASCADE; -- 권장하지 않음! 데이터 손실 위험

주의: 역할 삭제 전에 해당 역할이 소유한 객체는 다른 역할로 변경하거나 삭제해야 합니다. REASSIGN OWNED BY old_role TO new_role; 또는 DROP OWNED BY old_role CASCADE;를 사용하여 소유권을 이전하거나 삭제할 수 있지만, CASCADE는 매우 위험하므로 신중하게 사용해야 합니다.

PostgreSQL 권한(Privilege) 부여 및 회수

GRANT 명령어로 특정 객체에 대한 권한을 부여하고, REVOKE 명령어로 회수합니다.

1. 데이터베이스 권한

데이터베이스 자체에 대한 권한입니다.

-- 'my_database' 데이터베이스에 'my_app_user'가 접속할 수 있도록 허용
GRANT CONNECT ON DATABASE my_database TO my_app_user;

-- 'my_database' 데이터베이스에 'my_app_user'가 임시 테이블을 생성할 수 있도록 허용
GRANT CREATE ON DATABASE my_database TO my_app_user;

2. 스키마 권한

테이블, 뷰 등 객체들을 그룹화하는 스키마에 대한 권한입니다.

-- 'public' 스키마에서 'my_app_user'가 테이블을 생성할 수 있도록 허용
GRANT CREATE ON SCHEMA public TO my_app_user;

-- 'public' 스키마 내 객체에 대한 'my_app_user'의 기본 사용 권한 허용
GRANT USAGE ON SCHEMA public TO my_app_user;

3. 테이블 권한

가장 빈번하게 사용되는 권한으로, 특정 테이블에 대한 접근/조작 권한입니다.

-- 'products' 테이블에 대한 SELECT 권한 부여
GRANT SELECT ON TABLE products TO my_app_user;

-- 'orders' 테이블에 대한 INSERT, UPDATE, DELETE 권한 부여
GRANT INSERT, UPDATE, DELETE ON TABLE orders TO my_app_user;

-- 모든 권한 부여 (신중하게 사용)
GRANT ALL PRIVILEGES ON TABLE customers TO my_app_user;

-- 특정 컬럼에 대한 UPDATE 권한 부여 (세분화된 제어)
GRANT UPDATE (product_name, price) ON TABLE products TO product_manager_role;

-- 모든 테이블에 대한 SELECT 권한 부여 (특정 스키마 내)
-- 이 권한은 현재 존재하는 테이블에만 적용되며, 앞으로 생성될 테이블에는 적용되지 않음
GRANT SELECT ON ALL TABLES IN SCHEMA public TO data_analyst_role;

4. 시퀀스 권한

SERIAL 또는 BIGSERIAL 타입 컬럼에 의해 생성되는 시퀀스에 대한 권한입니다.

-- 'users_id_seq' 시퀀스에 대한 USAGE (nextval) 권한 부여
GRANT USAGE ON SEQUENCE users_id_seq TO my_app_user;

5. 뷰(VIEW) 권한

뷰는 가상 테이블이므로 테이블과 유사하게 권한을 부여합니다.

-- 'user_public_info' 뷰에 대한 SELECT 권한 부여
GRANT SELECT ON VIEW user_public_info TO guest_user;

6. 권한 회수 (`REVOKE`)

GRANT와 동일한 구문으로 부여된 권한을 회수할 수 있습니다.

REVOKE INSERT ON TABLE orders FROM my_app_user;
REVOKE ALL PRIVILEGES ON TABLE customers FROM my_app_user;

역할 계층 구조 및 그룹 관리

PostgreSQL에서는 역할을 다른 역할의 "멤버"로 만들 수 있어 그룹 개념을 구현하고 권한 관리를 더욱 효율적으로 만들 수 있습니다.

-- 'read_only_role'이라는 그룹 역할 생성
CREATE ROLE read_only_role;

-- 'read_only_role'에게 모든 테이블에 대한 SELECT 권한 부여
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_role;

-- 사용자 'analyst1'과 'analyst2'를 'read_only_role'의 멤버로 추가
GRANT read_only_role TO analyst1;
GRANT read_only_role TO analyst2;

-- 역할에서 멤버 제거
REVOKE read_only_role FROM analyst1;

팁: GRANT role_name TO user_role WITH ADMIN OPTION;을 사용하면 user_role이 role_name의 멤버를 추가/제거할 수 있는 관리 권한도 가집니다.

기본 권한 설정 (`ALTER DEFAULT PRIVILEGES`)

새롭게 생성되는 객체에 대해 자동으로 부여될 권한을 설정할 수 있습니다. 이는 권한 관리를 자동화하고 일관성을 유지하는 데 매우 중요합니다.

-- 앞으로 'my_app_user'가 'public' 스키마에 생성하는 모든 테이블에 대해
-- 'dev_lead' 역할에 SELECT, INSERT, UPDATE 권한을 자동으로 부여
ALTER DEFAULT PRIVILEGES FOR ROLE my_app_user
IN SCHEMA public
GRANT SELECT, INSERT, UPDATE ON TABLES TO dev_lead;

-- 앞으로 'data_uploader'가 생성하는 모든 시퀀스에 대해 'data_consumer'에게 USAGE 권한 부여
ALTER DEFAULT PRIVILEGES FOR ROLE data_uploader
IN SCHEMA public
GRANT USAGE ON SEQUENCES TO data_consumer;

주의: ALTER DEFAULT PRIVILEGES는 명령어를 실행하는 역할이 생성하는 객체에 적용됩니다. 즉, 이 명령어를 my_app_user로 로그인하여 실행해야 합니다. 만약 슈퍼유저로 실행한다면, 슈퍼유저가 생성하는 객체에 적용됩니다.

실무 권한 관리 팁

최소 권한 원칙 (Principle of Least Privilege): 사용자나 애플리케이션에게 필요한 최소한의 권한만 부여하세요. ALL PRIVILEGES는 꼭 필요한 경우에만 사용하고, 세분화된 권한을 부여하는 것을 습관화하세요.
역할 기반 접근 제어 (RBAC): 개별 사용자보다는 역할(그룹)에 권한을 부여하고, 사용자들을 역할에 할당하는 방식을 사용하세요. 이는 관리의 복잡성을 줄이고 실수를 방지하는 데 효과적입니다.
데이터베이스 소유자 권한 분리: 실제 애플리케이션에서 사용하는 계정은 데이터베이스의 소유자(Owner)가 아니어야 합니다. 소유자 계정은 데이터베이스 생성, 스키마 변경 등 관리 목적으로만 사용하고, 애플리케이션은 필요한 최소 권한을 가진 별도의 계정을 사용하게 하세요.
public 스키마 주의: PostgreSQL은 기본적으로 public 스키마에 대해 CREATE 권한을 public 그룹(모든 사용자)에게 부여합니다. 이는 보안에 취약할 수 있으므로, REVOKE CREATE ON SCHEMA public FROM public; 명령어를 통해 회수하고 필요한 역할에만 명시적으로 GRANT하는 것을 고려하세요.
비밀번호 관리: 강력한 비밀번호 정책을 사용하고, 주기적으로 비밀번호를 변경하며, 비밀번호 재사용을 금지하세요.
접속 제한: pg_hba.conf 파일을 통해 특정 IP 주소나 사용자만 데이터베이스에 접속할 수 있도록 제한하는 것이 좋습니다.
정기적인 감사: pg_roles, pg_class, information_schema.table_privileges 등의 시스템 카탈로그 뷰를 활용하여 현재 설정된 권한을 주기적으로 감사하고 불필요하거나 과도한 권한이 없는지 확인하세요.

18. 기초적인 백업

백업의 중요성 및 3-2-1 백업 규칙

백업은 단순히 데이터 복사본을 만드는 것을 넘어, 서비스 연속성과 비즈니스 지속성을 보장하는 핵심 요소입니다. 아무리 잘 설계된 시스템이라도 데이터 손실의 위험은 항상 존재하며, 백업이 없다면 치명적인 결과를 초래할 수 있습니다.

3-2-1 백업 규칙: 효과적인 백업 전략을 수립하기 위한 업계 표준 규칙입니다.

3개의 백업 복사본을 유지합니다. (원본 데이터 포함)
2가지 다른 저장 매체에 저장합니다. (예: 로컬 디스크, 네트워크 스토리지, 클라우드)
1개는 오프사이트(Offsite)에 보관합니다. (다른 물리적 위치, 재해 발생 시 안전)

PostgreSQL 백업 방식의 이해

PostgreSQL은 크게 두 가지 주요 백업 방식을 제공합니다.

1. 논리적 백업 (Logical Backup)

데이터베이스의 스키마(CREATE TABLE, CREATE INDEX 등)와 데이터를 SQL 문 형식으로 덤프(dump)하는 방식입니다.

장점:
- 특정 데이터베이스, 스키마, 또는 테이블만 선택적으로 백업하고 복원할 수 있습니다.
- PostgreSQL 버전 간 이동, 다른 데이터베이스 시스템으로의 마이그레이션에도 유용할 수 있습니다.
- 텍스트 파일로 저장되므로 내용 확인이 용이합니다.
단점:
- 백업 및 복원 속도가 물리적 백업에 비해 느릴 수 있습니다 (특히 대용량 데이터베이스의 경우).
- 백업 시 데이터베이스에 약간의 부하가 발생할 수 있습니다.
- PITR(Point-In-Time Recovery)을 지원하지 않습니다.

도구: pg_dump, pg_dumpall

활용 예시:

# 1. 특정 데이터베이스 전체 백업 (가장 일반적인 사용)
# -F c: 사용자 정의 형식 (압축 및 유연성 높음, 기본값)
# -Fc: 압축된 사용자 정의 형식
# -Ft: tar 형식
# -Fp: plain 텍스트 형식 (기본값)
pg_dump -h localhost -p 5432 -U username -F c -b -v -f /path/to/backup/mydb_$(date +%Y%m%d_%H%M%S).backup mydatabase

# 2. 특정 스키마만 백업
pg_dump -h localhost -p 5432 -U username -n myschema -f /path/to/backup/myschema_backup.sql mydatabase

# 3. 특정 테이블만 백업
pg_dump -h localhost -p 5432 -U username -t mytable -f /path/to/backup/mytable_backup.sql mydatabase

# 4. 데이터베이스의 스키마(구조)만 백업 (데이터 제외)
pg_dump -h localhost -p 5432 -U username -s -f /path/to/backup/mydb_schema.sql mydatabase

# 5. 모든 데이터베이스 백업 (글로벌 객체 포함: 역할, 테이블스페이스 등)
# 슈퍼유저 권한 필요
pg_dumpall -h localhost -p 5432 -U postgres > /path/to/backup/all_databases_$(date +%Y%m%d_%H%M%S).sql

# 복원 예시 (plain text 형식의 경우)
psql -h localhost -p 5432 -U username -d mydatabase < /path/to/backup/mydb_backup.sql

# 복원 예시 (custom/tar 형식의 경우)
pg_restore -h localhost -p 5432 -U username -d mydatabase /path/to/backup/mydb_backup.backup

# pg_dumpall로 백업한 전체 데이터베이스 복원
# (데이터베이스 생성 권한을 가진 역할로 실행)
psql -h localhost -p 5432 -U postgres < /path/to/backup/all_databases_backup.sql

2. 물리적 백업 (Physical Backup) / 베이스 백업 & WAL 아카이빙

데이터베이스 클러스터의 실제 데이터 파일(데이터 디렉토리)을 통째로 복사하는 방식입니다. 이는 PostgreSQL의 PITR(Point-In-Time Recovery)을 구현하는 데 필수적입니다.

장점:
- 가장 빠르고 효율적인 백업 및 복원 방식 (특히 대용량 데이터베이스).
- PITR (Point-In-Time Recovery) 지원: 특정 시점(초 단위)으로 데이터베이스를 복구할 수 있어, 치명적인 데이터 손실 상황에서 최신 상태로 복구하는 데 매우 중요합니다.
- 재해 복구 및 고가용성(High Availability) 구성 (예: 스트리밍 복제)의 기반이 됩니다.
단점:
- 백업 시 전체 데이터베이스 클러스터가 포함되므로 특정 데이터베이스/테이블만 복원하기 어렵습니다 (별도 도구 필요).
- 동일한 PostgreSQL 버전, 아키텍처(32/64비트) 및 파일 시스템 구조에서만 복원 가능합니다.
- 백업 공간 요구 사항이 큽니다.
- WAL 아카이빙 설정 및 관리가 필요합니다.

도구: pg_basebackup, 파일 시스템 복사 (rsync 등), 전문 백업 도구 (Barman, pgBackRest)

활용 예시 (pg_basebackup 및 WAL 아카이빙):

WAL 아카이빙 설정 (postgresql.conf):
PITR을 위해서는 WAL(Write-Ahead Log) 파일을 아카이브 저장소로 지속적으로 복사하도록 설정해야 합니다.

# postgresql.conf 파일 수정
wal_level = replica       # minimal, replica, logical 중 replica 이상 권장
archive_mode = on         # WAL 아카이빙 활성화
archive_command = 'cp %p /path/to/wal_archive/%f'  # WAL 파일을 아카이브 디렉토리로 복사하는 명령어
# 예시: 'rsync -a %p user@remotehost:/path/to/wal_archive/%f' (원격 저장)
# 예시: 'aws s3 cp %p s3://your-s3-bucket/wal/%f' (S3 저장)

archive_command 설정 후에는 PostgreSQL 서비스를 재시작해야 합니다.

베이스 백업 수행 (pg_basebackup):
실제 데이터 파일의 스냅샷을 생성합니다.

# pg_basebackup 명령어로 베이스 백업 수행
# -h: 호스트, -p: 포트, -U: 사용자, -D: 백업 저장 경로
# -F t: tar 형식으로 백업 (디렉토리 백업보다 일반적으로 권장)
# -Xs fetch: 백업 도중 생성되는 WAL 파일을 함께 가져옴
# -P: 진행 상황 표시
# --wal-method=stream: 스트리밍 방식으로 WAL 가져옴 (active connection 필요)
pg_basebackup -h localhost -p 5432 -U replica_user -D /path/to/backup/base_backup_$(date +%Y%m%d_%H%M%S) -F t -Xs fetch -P -R

# -R 옵션은 복원 시 필요한 recovery.signal, postgresql.auto.conf 파일을 자동으로 생성해줍니다.
# replica_user는 최소한 REPLICATION 권한을 가진 사용자여야 합니다.

복원 및 PITR 수행:
- 데이터 디렉토리 초기화: 기존 데이터 디렉토리의 모든 내용을 삭제하거나 다른 곳으로 이동합니다.
- 베이스 백업 복원: pg_basebackup으로 생성된 백업 파일을 데이터 디렉토리에 압축 해제합니다.
- recovery.conf (또는 postgresql.auto.conf에 recovery 관련 설정) 설정:
  PostgreSQL 12부터 recovery.conf 파일 대신 postgresql.auto.conf에 복구 관련 설정을 직접 추가하고, recovery.signal 또는 standby.signal 파일을 사용합니다.
  restore_command: WAL 아카이브에서 WAL 파일을 가져올 명령어를 설정합니다.
```
# postgresql.auto.conf (또는 recovery.conf < 12)
restore_command = 'cp /path/to/wal_archive/%f %p' # WAL 아카이브에서 %f 파일을 %p 위치로 복사
# 예시: 'aws s3 cp s3://your-s3-bucket/wal/%f %p'

# 특정 시점 복구 (PITR)
recovery_target_time = '2025-06-18 10:30:00 KST' # 특정 시간으로 복구
# recovery_target_xid = '...' # 특정 트랜잭션 ID로 복구
# recovery_target_lsn = '...' # 특정 LSN으로 복구
# recovery_target_name = '...' # 특정 named restore point로 복구
# recovery_target_inclusive = on # 대상 시간/XID를 포함할지 여부
```
- PostgreSQL 시작: PostgreSQL 서비스를 시작하면 자동으로 복구 모드로 진입하여 베이스 백업 이후의 WAL 파일을 적용하고, 설정된 recovery_target_time까지 복구합니다.

19. 기초적인 파티셔닝

파티셔닝이란 무엇이며 왜 필요한가?

파티셔닝은 하나의 논리적인 테이블을 특정 기준에 따라 여러 개의 물리적인 테이블(파티션)로 나누는 기법입니다. 이 과정은 데이터베이스 시스템에 의해 투명하게 관리되므로, 애플리케이션은 여전히 하나의 테이블에 접근하는 것처럼 데이터를 조회하고 조작할 수 있습니다.

파티셔닝의 주요 이점:

쿼리 성능 향상:
- 파티션 프루닝(Partition Pruning): 쿼리의 WHERE 절 조건에 따라 관련 없는 파티션을 스캔 대상에서 제외하여, 스캔해야 할 데이터의 양을 획기적으로 줄여줍니다.
- 인덱스 효율성: 각 파티션은 독립적인 인덱스를 가질 수 있어, 인덱스 크기가 줄어들고 B-Tree 인덱스의 깊이가 얕아져 검색 성능이 향상됩니다.
- 병렬 처리: 특정 쿼리는 여러 파티션을 병렬로 스캔하여 처리 속도를 높일 수 있습니다.
데이터 관리 용이성:
- 데이터 보관 정책 구현: 오래된 데이터를 포함하는 파티션을 쉽게 아카이빙하거나 삭제할 수 있어, 데이터 수명 주기 관리가 용이합니다.
- 유지보수 효율성: VACUUM, ANALYZE, 인덱스 재구축 등의 유지보수 작업이 전체 테이블이 아닌 특정 파티션에만 적용될 수 있어, 작업 시간이 단축되고 서비스 중단 위험이 줄어듭니다.
- 대규모 데이터 로드/언로드: ATTACH PARTITION, DETACH PARTITION 기능을 사용하여 대량의 데이터를 빠르게 로드하거나 제거할 수 있습니다.
저장 공간 활용 최적화:
- 자주 접근되지 않는 오래된 데이터를 저렴한 스토리지로 이동하거나 압축하여 보관할 수 있습니다.

PostgreSQL의 파티셔닝 유형

PostgreSQL 10부터 선언적(Declarative) 파티셔닝을 공식적으로 지원하며, 사용자는 파티셔닝 전략을 더욱 쉽게 구현할 수 있습니다.

1. 범위 파티셔닝 (RANGE Partitioning)

기준: 특정 컬럼의 값이 정의된 범위 내에 속하는지에 따라 데이터를 분할합니다.
활용: 날짜(timestamp, date), 숫자(integer, bigint) 범위 등 연속적인 값에 기반한 파티셔닝에 가장 일반적으로 사용됩니다. 시계열 데이터(로그, 이벤트, 거래 내역)에 매우 효과적입니다.

-- 부모 테이블 생성: Range 파티셔닝 선언
CREATE TABLE orders (
    order_id BIGSERIAL,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2),
    status VARCHAR(50)
) PARTITION BY RANGE (order_date); -- order_date 컬럼을 기준으로 범위 파티셔닝

-- 파티션 테이블 생성 (각 범위에 해당하는 자식 테이블)
-- 2024년 1월 데이터
CREATE TABLE orders_y2024m01 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- 2024년 2월 데이터
CREATE TABLE orders_y2024m02 PARTITION OF orders
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- ... (매월 또는 매년 파티션 추가)

-- 2025년 1월 데이터
CREATE TABLE orders_y2025m01 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- 데이터 삽입 (부모 테이블에 삽입하면 자동으로 적절한 파티션으로 라우팅됨)
INSERT INTO orders (customer_id, order_date, total_amount, status)
VALUES (101, '2024-01-15', 250.00, 'completed'),
       (102, '2024-02-01', 120.50, 'pending');

-- 쿼리 예시 (파티션 프루닝 적용)
-- 2024년 1월 데이터만 빠르게 조회
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
-- Explain Plan을 보면 orders_y2024m01 파티션만 스캔함을 확인할 수 있습니다.

2. 리스트 파티셔닝 (LIST Partitioning)

기준: 특정 컬럼의 값이 정의된 목록(리스트) 중 하나와 일치하는지에 따라 데이터를 분할합니다.
활용: 특정 국가 코드, 지역, 제품 유형, 상태 코드 등 이산적인 값에 기반한 파티셔닝에 적합합니다.

-- 부모 테이블 생성: List 파티셔닝 선언
CREATE TABLE users_by_country (
    user_id BIGSERIAL,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    country_code CHAR(2) NOT NULL,
    registration_date DATE
) PARTITION BY LIST (country_code); -- country_code 컬럼을 기준으로 리스트 파티셔닝

-- 파티션 테이블 생성
CREATE TABLE users_us PARTITION OF users_by_country
FOR VALUES IN ('US');

CREATE TABLE users_kr PARTITION OF users_by_country
FOR VALUES IN ('KR');

CREATE TABLE users_jp PARTITION OF users_by_country
FOR VALUES IN ('JP', 'CN'); -- 여러 값을 하나의 파티션에 할당 가능

-- 기본 파티션 (정의된 목록에 없는 모든 값)
-- DEFAULT 파티션은 필수 아님, 그러나 정의하면 예외 처리 편리
CREATE TABLE users_other_countries PARTITION OF users_by_country DEFAULT;

-- 데이터 삽입
INSERT INTO users_by_country (username, email, country_code, registration_date)
VALUES ('john.doe', 'john@example.com', 'US', '2023-01-01'),
       ('kim.minjun', 'kim@example.com', 'KR', '2023-03-10'),
       ('li.wei', 'li@example.com', 'CN', '2024-05-20');

-- 쿼리 예시 (파티션 프루닝 적용)
SELECT * FROM users_by_country WHERE country_code = 'KR';

3. 해시 파티셔닝 (HASH Partitioning)

기준: 특정 컬럼의 해시 값에 따라 데이터를 분할합니다.
활용: 데이터 분포가 불균일하거나, 특정 컬럼에 명확한 범위/목록 기준을 적용하기 어려울 때 데이터를 균등하게 분산시키고자 할 때 사용됩니다.

-- 부모 테이블 생성: Hash 파티셔닝 선언
CREATE TABLE events (
    event_id UUID DEFAULT gen_random_uuid(),
    user_id BIGINT NOT NULL,
    event_type VARCHAR(50),
    event_timestamp TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY HASH (user_id); -- user_id 컬럼의 해시 값을 기준으로 파티셔닝

-- 파티션 테이블 생성 (모듈러 연산을 기반으로 N개의 파티션 생성)
-- 예: 4개의 파티션으로 분할
CREATE TABLE events_p0 PARTITION OF events
FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE events_p1 PARTITION OF events
FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE events_p2 PARTITION OF events
FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE events_p3 PARTITION OF events
FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 데이터 삽입 (랜덤 user_id에 따라 균등하게 분산)
INSERT INTO events (user_id, event_type)
SELECT generate_series(1, 100000), 'click';

-- 쿼리 예시
SELECT * FROM events WHERE user_id = 12345;
-- 특정 user_id에 대한 쿼리도 해당 user_id의 해시 값에 해당하는 파티션만 스캔하게 됩니다.

실무 파티셔닝 활용 팁 및 고려사항

파티션 키 선택의 중요성:
- 쿼리 패턴 고려: WHERE 절에서 자주 사용되는 컬럼을 파티션 키로 선택해야 파티션 프루닝 효과를 극대화할 수 있습니다.
- 데이터 분포: 데이터가 파티션 간에 고르게 분산되어야 핫스팟(Hotspot) 발생을 방지하고 성능을 균등하게 유지할 수 있습니다.
- 파티션 변경 빈도: 파티션 키의 값이 자주 변경되지 않는 컬럼을 선택하는 것이 좋습니다.
파티션 개수 결정:
- 너무 적은 파티션: 파티션 프루닝 효과가 미미하고, 관리 이점이 줄어듭니다.
- 너무 많은 파티션: 메타데이터 관리 오버헤드가 증가하고, 각 파티션에 대한 인덱스 관리 비용이 커질 수 있습니다. 적절한 균형점을 찾는 것이 중요하며, 보통 수십에서 수백 개의 파티션이 일반적입니다.
인덱스 전략:
- 글로벌 인덱스 vs. 로컬 인덱스: PostgreSQL의 선언적 파티셔닝은 로컬 인덱스(Local Index)만 지원합니다. 즉, 각 파티션에 별도의 인덱스가 생성됩니다. 이는 인덱스 크기를 줄이고 관리 효율성을 높이는 데 유리하지만, 파티션 키가 아닌 다른 컬럼으로 조회할 때는 모든 파티션의 인덱스를 스캔할 수 있습니다.
- 모든 인덱스(PK 포함)는 파티션 키 컬럼을 포함해야 합니다.
새 파티션 추가 및 오래된 파티션 관리:
- 미래 파티션 미리 생성: 날짜 기반 파티셔닝의 경우, 데이터가 삽입되기 전에 다음 달 또는 다음 연도의 파티션을 미리 생성해두어야 합니다.
- 오래된 파티션 아카이빙/삭제:
  - DETACH PARTITION: 특정 파티션을 부모 테이블에서 분리하여 독립적인 테이블로 만듭니다. 이를 통해 아카이빙하거나 빠르게 삭제할 수 있습니다.
  - DROP TABLE: 분리된 파티션 테이블을 삭제합니다.
  - 주의: DETACH 시 데이터 유효성 검사(CONCURRENTLY)는 시간이 오래 걸릴 수 있으므로, 대규모 파티션 분리 시 트랜잭션 주의.
```
-- 2024년 1월 파티션을 분리하여 보관 또는 삭제 준비
ALTER TABLE orders DETACH PARTITION orders_y2024m01;

-- 분리된 파티션에 대한 백업 수행 후 삭제
DROP TABLE orders_y2024m01;
```
기존 테이블 파티셔닝으로 전환:
- 이미 대용량 테이블이 있고 이를 파티셔닝으로 전환하고 싶다면, 먼저 새로운 파티션 부모 테이블을 만들고, 기존 테이블을 각 파티션으로 분리한 후 ATTACH PARTITION하거나, 데이터를 새 파티션 구조로 마이그레이션해야 합니다. 이 과정은 서비스 중단을 최소화하기 위한 신중한 계획과 테스트가 필요합니다.
DEFAULT 파티션 활용:
- 범위나 리스트에 속하지 않는 데이터를 처리하기 위해 DEFAULT 파티션을 정의할 수 있습니다. 이는 예상치 못한 데이터가 들어왔을 때 유용하지만, DEFAULT 파티션이 너무 커지면 성능 문제가 발생할 수 있으므로 주기적인 모니터링이 필요합니다.
EXPLAIN ANALYZE 활용:
- 파티셔닝 후 쿼리가 의도한 대로 파티션 프루닝을 제대로 활용하고 있는지 EXPLAIN ANALYZE 명령어로 실행 계획을 반드시 확인해야 합니다.

20. 기초적인 JSON 처리

JSON vs. JSONB: 어떤 것을 사용해야 할까?

PostgreSQL은 두 가지 JSON 관련 데이터 타입을 제공합니다.

JSON:
- 입력된 JSON 텍스트를 그대로 저장합니다.
- 공백, 키의 순서 등 원본 포맷을 유지합니다.
- 단점: 쿼리 시마다 파싱(parsing)을 수행해야 하므로 JSONB에 비해 조회 성능이 느릴 수 있습니다.
- 장점: 입력된 텍스트 원형을 보존해야 하는 특정 로깅 또는 감사 시나리오에 적합합니다.
JSONB (JSON Binary):
- 입력된 JSON 텍스트를 바이너리 형태로 변환하여 저장합니다. 이 과정에서 공백이 제거되고, 키의 순서가 재정렬되며, 중복 키가 제거되는 등 정규화됩니다.
- 장점:
  - 데이터 저장 시 한 번 파싱되므로, 조회 시 파싱 비용이 없어 JSON 타입보다 훨씬 빠릅니다.
  - GIN 인덱스 생성이 가능하여 특정 키-값 쌍이나 배열 요소에 대한 빠른 검색을 지원합니다.
- 단점: 데이터를 저장할 때 파싱 및 변환 오버헤드가 발생하며, 원본 텍스트 포맷이 유지되지 않습니다.

결론: 특별한 이유가 없다면 대부분의 실무 환경에서는 JSONB를 사용하는 것이 강력히 권장됩니다.

JSONB 데이터 조작 및 쿼리

JSONB 컬럼에 데이터를 저장하고 조회하는 것은 일반 컬럼과 유사하지만, JSON 구조 내부의 데이터를 다루기 위한 특별한 연산자와 함수가 필요합니다.

1. JSONB 데이터 삽입 및 기본 조회

-- 예시 테이블 생성 (JSONB 타입 컬럼 포함)
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2),
    details JSONB -- 제품의 다양한 속성을 담을 JSONB 컬럼
);

-- JSONB 데이터 삽입
INSERT INTO products (name, price, details) VALUES
('Laptop Pro', 1500.00, '{"weight": "1.5kg", "color": "silver", "specs": {"cpu": "i7", "ram": "16GB", "storage": "512GB SSD"}, "tags": ["electronics", "laptop"]}'),
('Mechanical Keyboard', 120.00, '{"layout": "TKL", "backlight": true, "color": "black", "switch_type": "brown", "tags": ["peripherals", "keyboard"]}'),
('Wireless Mouse', 50.00, '{"color": "white", "battery_life_days": 90, "tags": ["peripherals", "mouse"]}'),
('External Monitor', 300.00, '{"size_inch": 27, "resolution": "4K", "features": ["HDR", "USB-C"], "tags": ["electronics", "monitor"]}');

-- 전체 조회
SELECT product_id, name, details FROM products;

2. JSONB 연산자를 이용한 값 추출 및 필터링

PostgreSQL은 JSONB 데이터를 쿼리하는 데 강력한 연산자들을 제공합니다.

-> (JSON 객체 필드 추출): JSON 객체에서 키에 해당하는 값을 JSONB 타입으로 추출합니다.
->> (JSON 객체 필드 추출, 텍스트 반환): JSON 객체에서 키에 해당하는 값을 TEXT 타입으로 추출합니다. (WHERE 절 조건에 유용)
#> (JSON 경로를 통해 값 추출): JSON 경로(배열 포함)를 통해 값을 JSONB 타입으로 추출합니다.
#>> (JSON 경로를 통해 값 추출, 텍스트 반환): JSON 경로를 통해 값을 TEXT 타입으로 추출합니다.

-- 'color' 필드의 값 추출 (JSONB 타입)
SELECT name, details->'color' AS product_color FROM products;

-- 'color' 필드의 값 추출 (TEXT 타입)
SELECT name, details->>'color' AS product_color_text FROM products;

-- 'specs' 객체 내의 'cpu' 필드 추출 (JSON 경로 사용)
SELECT name, details->'specs'->>'cpu' AS cpu_spec FROM products;
SELECT name, details#>>'{specs,cpu}' AS cpu_spec_path FROM products;

-- 'specs' 객체 내의 'ram'이 '16GB'인 제품 조회
SELECT name, price FROM products WHERE details->'specs'->>'ram' = '16GB';

-- 'details'에 'weight' 키가 있는 제품 조회
SELECT name, details FROM products WHERE details ? 'weight';

-- 'details'에 'backlight' 키가 있고 그 값이 true인 제품 조회
SELECT name, details FROM products WHERE details ? 'backlight' AND details->>'backlight' = 'true';

3. JSONB 포함(Containment) 연산자

@> (Contains / 포함): 좌측 JSONB 값이 우측 JSONB 값을 포함하는지 확인합니다. 부분적인 JSON 객체 또는 배열 내 특정 요소 존재 여부를 검사할 때 매우 유용합니다.
<@ (Is Contained By / 포함됨): 좌측 JSONB 값이 우측 JSONB 값에 포함되는지 확인합니다.

-- 'color': 'silver'와 'weight': '1.5kg'을 모두 포함하는 제품 조회
SELECT name, details FROM products
WHERE details @> '{"color": "silver", "weight": "1.5kg"}';

-- 'tags' 배열에 'electronics' 태그가 포함된 제품 조회
SELECT name, details FROM products
WHERE details->'tags' @> '["electronics"]';

-- 'features' 배열에 'HDR' 또는 'USB-C' 중 하나라도 포함된 제품 조회 (NOT @> or <@)
-- 배열의 부분 집합 검사
-- '["HDR", "USB-C"]'이 'features'에 모두 포함되는지 확인:
SELECT name, details FROM products
WHERE details->'features' @> '["HDR", "USB-C"]'; -- Monitor만 해당

4. JSONB 존재(Existence) 연산자

? (Exists): JSONB 객체에 특정 키가 존재하는지 확인합니다.
?| (Any Exists): JSONB 객체에 주어진 키 목록 중 하나라도 존재하는지 확인합니다.
?& (All Exists): JSONB 객체에 주어진 키 목록이 모두 존재하는지 확인합니다.

-- 'specs' 키가 존재하는 제품 조회
SELECT name, details FROM products WHERE details ? 'specs';

-- 'layout' 또는 'battery_life_days' 키 중 하나라도 존재하는 제품 조회
SELECT name, details FROM products WHERE details ?| ARRAY['layout', 'battery_life_days'];

-- 'size_inch'와 'resolution' 키가 모두 존재하는 제품 조회
SELECT name, details FROM products WHERE details ?& ARRAY['size_inch', 'resolution'];

JSONB 함수 활용 (고급)

PostgreSQL은 JSONB 데이터를 더욱 정교하게 다룰 수 있는 다양한 함수를 제공합니다.

jsonb_each() / jsonb_each_text(): JSONB 객체를 키-값 쌍의 집합으로 확장합니다.
jsonb_array_elements() / jsonb_array_elements_text(): JSONB 배열을 개별 요소의 집합으로 확장합니다.
jsonb_agg() / jsonb_object_agg(): 행을 JSONB 배열 또는 객체로 집계합니다.
jsonb_build_object() / jsonb_build_array(): 스칼라 값을 JSONB 객체 또는 배열로 구성합니다.
jsonb_set(): JSONB 값 내의 특정 경로에 값을 설정합니다.
jsonb_strip_nulls(): JSONB 객체에서 null 값을 가진 필드를 제거합니다.

-- 제품 상세 정보의 모든 키-값 쌍 조회 (jsonb_each_text 사용)
SELECT p.name, key, value
FROM products p, jsonb_each_text(p.details) AS details_kv;

-- tags 배열의 각 요소 추출
SELECT p.name, tag
FROM products p, jsonb_array_elements_text(p.details->'tags') AS tag;

-- 특정 태그('electronics')를 가진 제품의 이름과 해당 태그 조회
SELECT p.name, tag
FROM products p, jsonb_array_elements_text(p.details->'tags') AS tag
WHERE tag = 'electronics';

-- 사용자 정의 JSONB 객체 생성
SELECT jsonb_build_object('product_name', name, 'product_price', price) AS product_summary
FROM products WHERE product_id = 1;

-- JSONB 객체 업데이트 (색상 변경)
UPDATE products
SET details = jsonb_set(details, '{color}', '"black"')
WHERE product_id = 1;

-- JSONB 객체에 새 필드 추가 ('stock_available')
UPDATE products
SET details = jsonb_set(details, '{stock_available}', 'true', true) -- 마지막 true는 필드가 없으면 추가
WHERE product_id = 1;

JSONB 인덱싱: 성능 최적화

JSONB 컬럼에 대한 쿼리 성능을 향상시키기 위해 GIN(Generalized Inverted Index) 인덱스를 활용할 수 있습니다.

일반 GIN 인덱스: ?, ?|, ?&, @>와 같은 존재 및 포함 연산자의 성능을 향상시킵니다.

-- details 컬럼 전체에 GIN 인덱스 생성
CREATE INDEX idx_products_details_gin ON products USING GIN (details);

-- 이제 다음 쿼리들이 훨씬 빨라집니다.
SELECT name FROM products WHERE details @> '{"color": "silver"}';
SELECT name FROM products WHERE details ? 'specs';

표현식(Expression) GIN 인덱스: 특정 JSON 필드의 값에 대한 검색 성능을 향상시킵니다. (details->'color')와 같이 추출된 결과에 인덱스를 걸 수 있습니다.
```
-- 'details->>color' 필드에 대한 GIN 인덱스 (텍스트 값 검색에 최적화)
CREATE INDEX idx_products_details_color ON products USING GIN ((details->>'color'));

-- 이제 다음 쿼리가 빨라집니다.
SELECT name FROM products WHERE details->>'color' = 'black';
```
주의: (details->'color') 처럼 JSONB 타입으로 추출된 값에 인덱스를 걸 때는 @> 연산자 등 JSONB 연산자를 사용해야 인덱스가 활용됩니다. (details->>'color') 처럼 TEXT 타입으로 추출된 값에 인덱스를 걸 때는 = 연산자 등 일반 텍스트 연산자를 사용해야 인덱스가 활용됩니다.

실무 JSONB 활용 팁

스키마 변경 유연성: 자주 변경될 수 있는 부가적인 속성이나 옵션, 태그 등을 JSONB 컬럼에 저장하여 스키마 변경에 대한 유연성을 확보하세요.
정형 데이터와 비정형 데이터의 조화: 핵심적인 비즈니스 로직에 사용되는 중요 데이터는 정형화된 컬럼에 저장하고, 유연성이 필요한 부가 데이터는 JSONB 컬럼에 저장하여 두 가지 장점을 모두 활용하세요.
성능 모니터링: EXPLAIN ANALYZE를 사용하여 JSONB 쿼리가 의도한 대로 인덱스를 사용하는지, 성능 병목이 없는지 주기적으로 확인하세요.
데이터 크기 고려: JSONB 컬럼에 너무 많은 데이터를 저장하면 테이블 크기가 커지고, 전체 스캔 시 성능 저하를 야기할 수 있습니다. 적절한 크기와 구조를 유지하는 것이 중요합니다.

마무리

PostgreSQL의 JSONB 데이터 타입과 강력한 함수, 연산자들은 관계형 데이터베이스에서 비정형 데이터를 효율적으로 관리할 수 있는 강력한 도구를 제공합니다. 이를 통해 스키마 변경에 유연하게 대응하고, 복잡한 데이터를 효과적으로 쿼리하며, 필요한 경우 GIN 인덱스를 통해 성능을 최적화할 수 있습니다.

여러분의 프로젝트에서 JSONB를 사용하여 어떤 종류의 데이터를 저장하고, 어떤 문제를 해결하셨나요? 댓글로 경험을 공유해주세요!
You've landed on PostgreSQL's JSON processing capabilities! This is a fantastic topic for a blog post, especially in modern application development where semi-structured data (like configuration settings, user preferences, or external API responses) is very common. PostgreSQL's JSONB type, in particular, offers powerful and efficient ways to handle this data directly within the database.

As before, the provided CASE statement and array queries are not directly related to JSON processing. I'll craft a blog post that focuses solely on PostgreSQL's JSON features, particularly JSONB, and how to use them effectively in a real-world scenario.

Here's a detailed outline and content for your blog post on PostgreSQL JSON Processing:

PostgreSQL 실무 JSON 처리 가이드: 유연한 데이터 모델링과 강력한 쿼리

현대의 많은 애플리케이션은 정형화된 관계형 데이터뿐만 아니라, 유연하고 동적인 형태의 데이터(예: 사용자 설정, 외부 API 응답, 이벤트 로그)를 다룹니다. PostgreSQL은 이러한 요구사항을 충족시키기 위해 강력한 JSON(JavaScript Object Notation) 데이터 타입을 지원하며, 특히 JSONB 타입은 인덱싱과 효율적인 쿼리 성능을 제공하여 실무에서 매우 유용하게 활용될 수 있습니다.

이 글에서는 PostgreSQL의 JSON과 JSONB 타입의 차이점, 데이터를 저장하고 추출하는 방법, 복잡한 쿼리 작성, 그리고 성능 최적화를 위한 인덱싱 전략까지, 실무 수준의 JSON 처리 방법을 자세히 알아보겠습니다.

JSON vs. JSONB: 어떤 것을 선택해야 할까?

PostgreSQL은 두 가지 JSON 관련 데이터 타입을 제공합니다: JSON과 JSONB. 둘 다 JSON 형식의 데이터를 저장하지만, 내부적인 저장 방식과 성능 특성에서 중요한 차이가 있습니다.

JSON 타입:
- 입력된 JSON 텍스트를 그대로 저장합니다.
- 쿼리 시 매번 JSON 텍스트를 파싱(Parsing)해야 하므로 조회 성능이 느릴 수 있습니다.
- 원본 JSON의 공백, 키 순서, 중복 키(마지막 값만 유효하지만 저장 시 모두 유지) 등을 보존합니다.
- 초기 저장 속도는 빠를 수 있습니다.
JSONB (JSON Binary) 타입:
- 입력된 JSON 텍스트를 이진 형식(Binary Format)으로 분해하여 저장합니다.
- 저장 시 파싱 오버헤드가 있지만, 저장된 이후에는 재파싱이 필요 없으므로 조회 및 조작 성능이 훨씬 빠릅니다.
- 공백, 키 순서, 중복 키(중복 키의 경우 마지막 값만 저장) 등은 보존되지 않습니다.
- 인덱싱을 지원하여 대용량 JSONB 데이터에서 특정 키를 이용한 검색 시 성능을 크게 향상시킬 수 있습니다.

결론: 특수한 경우가 아니라면, 대부분의 실무 환경에서는 JSONB 타입을 사용하는 것이 강력히 권장됩니다. 특히 쿼리, 검색, 업데이트 등 데이터 조작이 빈번한 경우 JSONB의 성능 이점은 압도적입니다.

JSONB 데이터 저장하기

JSONB 타입의 컬럼을 포함하는 테이블을 생성하고 데이터를 삽입하는 방법은 다음과 같습니다.

-- 제품 정보 테이블 생성 (유연한 속성을 저장하기 위해 'details' 컬럼을 JSONB로 정의)
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    details JSONB
);

-- JSONB 데이터 삽입 예시
INSERT INTO products (name, category, details) VALUES
('Laptop Pro', 'Electronics', '{"brand": "Acme", "specs": {"cpu": "i7", "ram_gb": 16, "storage_gb": 512}, "features": ["lightweight", "long battery life"]}'),
('Smartphone X', 'Electronics', '{"brand": "Mobius", "specs": {"cpu": "Snapdragon", "ram_gb": 8, "camera_mp": 48}, "color": "black", "price": 999.99}'),
('Mechanical Keyboard', 'Accessories', '{"brand": "KeyMaster", "type": "TKL", "features": ["RGB lighting", "tactile switch"], "layout": "US", "price": 120.00}');

JSONB 데이터 추출 및 조회하기

PostgreSQL은 JSONB 데이터 내부의 값을 추출하기 위한 다양한 연산자와 함수를 제공합니다.

1. 기본 추출 연산자 (`->`, `->>`)

->: JSON 객체에서 키(key) 또는 배열에서 인덱스(index)에 해당하는 값을 JSONB 타입으로 반환합니다. (결과가 다시 JSON 구조)
->>: JSON 객체에서 키(key) 또는 배열에서 인덱스(index)에 해당하는 값을 TEXT 타입으로 반환합니다. (최종 값을 텍스트로)

-- 제품 이름과 브랜드 추출
SELECT
    name,
    details->>'brand' AS brand
FROM products;
-- 결과:
--    name          | brand
-- -------------------+----------
--  Laptop Pro        | Acme
--  Smartphone X      | Mobius
--  Mechanical Keyboard | KeyMaster

-- 중첩된 JSON 값 추출 (CPU 정보)
SELECT
    name,
    details->'specs'->>'cpu' AS cpu_spec
FROM products;
-- 결과:
--    name          | cpu_spec
-- -------------------+------------
--  Laptop Pro        | i7
--  Smartphone X      | Snapdragon
--  Mechanical Keyboard | (null)  -- 해당 키가 없으면 NULL 반환

2. 경로 기반 추출 연산자 (`#>`, `#>>`)

TEXT 배열을 사용하여 중첩된 JSON 구조의 특정 경로를 지정하여 값을 추출합니다.

#>: 경로에 해당하는 값을 JSONB 타입으로 반환
#>>: 경로에 해당하는 값을 TEXT 타입으로 반환

-- CPU 스펙 정보 추출 (->> 와 동일한 결과)
SELECT
    name,
    details#>>'{specs,cpu}' AS cpu_spec
FROM products;

-- features 배열의 첫 번째 요소 추출
SELECT
    name,
    details#>>'{features,0}' AS first_feature
FROM products;
-- 결과:
--    name          | first_feature
-- -------------------+---------------
--  Laptop Pro        | lightweight
--  Smartphone X      | (null)
--  Mechanical Keyboard | RGB lighting

JSONB 데이터 필터링 (WHERE 절)

JSONB 데이터를 WHERE 절에서 활용하여 특정 조건을 만족하는 행을 필터링할 수 있습니다.

1. 특정 키-값 존재 여부 (`?` 연산자)

?: JSON 객체에 특정 키가 존재하는지 확인합니다.
?|: JSON 객체에 주어진 키들 중 하나라도 존재하는지 확인합니다.
?&: JSON 객체에 주어진 키들이 모두 존재하는지 확인합니다.

-- 'color' 키를 가지고 있는 제품 조회
SELECT name, details FROM products WHERE details ? 'color';
-- 결과: Smartphone X

-- 'brand' 또는 'type' 키를 가지고 있는 제품 조회
SELECT name, details FROM products WHERE details ?| ARRAY['brand', 'type'];
-- 결과: Laptop Pro, Smartphone X, Mechanical Keyboard

-- 'brand'와 'price' 키를 모두 가지고 있는 제품 조회
SELECT name, details FROM products WHERE details ?& ARRAY['brand', 'price'];
-- 결과: Smartphone X, Mechanical Keyboard

2. JSONB 포함 여부 (`@>`, `<@`)

@> (Contains): 왼쪽 JSONB 값이 오른쪽 JSONB 값을 포함하는지 확인합니다. (오른쪽이 왼쪽의 부분집합인지)
<@ (Contained By): 왼쪽 JSONB 값이 오른쪽 JSONB 값에 포함되는지 확인합니다. (왼쪽이 오른쪽의 부분집합인지)

이 연산자들은 특히 GIN 인덱스와 함께 사용될 때 매우 강력합니다.

-- 스펙에 'cpu': 'i7'이 포함된 제품 조회
SELECT name, details FROM products WHERE details @> '{"specs": {"cpu": "i7"}}';
-- 결과: Laptop Pro

-- 'features' 배열에 'RGB lighting'이 포함된 제품 조회
SELECT name, details FROM products WHERE details @> '{"features": ["RGB lighting"]}';
-- 결과: Mechanical Keyboard

-- 브랜드가 'Mobius'이고 가격이 900 이상인 제품 조회
SELECT name, details FROM products WHERE details @> '{"brand": "Mobius"}' AND (details->>'price')::NUMERIC >= 900;

JSONB 데이터 변경 및 추가하기

JSONB 데이터는 jsonb_set 함수를 사용하여 특정 경로의 값을 업데이트하거나 새로운 값을 추가할 수 있습니다.

jsonb_set(target jsonb, path text[], new_value jsonb, [create_if_missing boolean]): target JSONB에서 path에 해당하는 값을 new_value로 설정합니다. create_if_missing이 true이고 경로가 존재하지 않으면 생성합니다.

-- 'Laptop Pro'의 RAM을 32GB로 업데이트
UPDATE products
SET details = jsonb_set(details, '{specs,ram_gb}', '32')
WHERE name = 'Laptop Pro';

-- 'Smartphone X'에 'storage_gb': 128 속성 추가 (기존에 없으면 생성)
UPDATE products
SET details = jsonb_set(details, '{specs,storage_gb}', '128', true)
WHERE name = 'Smartphone X';

-- 'Mechanical Keyboard'의 'features' 배열에 'hot-swappable' 추가
UPDATE products
SET details = jsonb_set(details, '{features}', details->'features' || '["hot-swappable"]'::jsonb)
WHERE name = 'Mechanical Keyboard';

JSONB 인덱싱: 성능 최적화의 핵심

JSONB 컬럼에 대한 쿼리 성능을 높이려면 적절한 인덱스 생성이 필수적입니다. PostgreSQL은 JSONB를 위해 주로 GIN (Generalized Inverted Index) 인덱스를 사용합니다.

1. 일반 GIN 인덱스

@>, ?, ?|, ?& 연산자를 사용하는 쿼리 성능을 향상시킵니다. jsonb 전체 컬럼에 인덱스를 생성합니다.

-- 'details' 컬럼 전체에 GIN 인덱스 생성
CREATE INDEX idx_products_details_gin ON products USING GIN (details);

-- 이제 이 쿼리는 인덱스를 활용하여 매우 빠르게 실행됩니다.
SELECT * FROM products WHERE details @> '{"brand": "Acme"}';
SELECT * FROM products WHERE details ? 'color';

2. 특정 키에 대한 GIN 인덱스 (Path/Expression GIN Index)

자주 쿼리되는 JSONB 내의 특정 키에 대해서만 인덱스를 생성하여 인덱스 크기를 줄이고 특정 쿼리의 성능을 더욱 최적화할 수 있습니다.

-- 'brand' 키에 대한 GIN 인덱스 생성 (text_pattern_ops는 텍스트 검색에 유용)
CREATE INDEX idx_products_details_brand ON products USING GIN ((details->>'brand') jsonb_path_ops);

-- 'category' 컬럼과 'brand' JSONB 키를 함께 활용하는 복합 인덱스 (PostgreSQL 13 이상)
CREATE INDEX idx_products_category_brand ON products (category, (details->>'brand'));

-- 스펙 내의 'ram_gb' 값에 대한 B-Tree 인덱스 (정렬 및 범위 검색에 유용)
CREATE INDEX idx_products_details_ram ON products USING BTREE ((details->>'specs.ram_gb')::INT);

참고: jsonb_path_ops는 jsonb_ops보다 더 작은 인덱스를 생성하지만, ?나 ?| 연산자 대신 jsonb_path_exists 같은 JSONPATH 연산자에 더 최적화되어 있습니다. 일반적인 key = value 검색에는 (details->>'key')와 같은 표현식 인덱스가 더 적합할 수 있습니다.

실무에서 JSONB 활용 시 고려사항

데이터 모델링 결정:
- 완전한 NoSQL 대체는 아님: JSONB는 비정형 데이터를 위한 훌륭한 도구지만, 관계형 데이터 모델을 완전히 대체하는 것은 아닙니다. 대부분의 경우, 핵심적인 식별자와 관계는 일반 컬럼으로 유지하고, 변경 빈도가 높거나 스키마가 유동적인 부가 정보에 JSONB를 활용하는 하이브리드 모델링이 가장 효과적입니다.
- 자주 쿼리될 필드 분리: JSONB 내의 특정 필드가 매우 자주 쿼리되거나 조인 조건으로 사용될 예정이라면, 해당 필드를 별도의 일반 컬럼으로 분리하여 인덱싱하는 것이 성능상 유리합니다.
쿼리 복잡성: JSONB 쿼리는 일반 SQL 쿼리보다 다소 복잡해 보일 수 있습니다. 가독성과 유지보수를 위해 쿼리를 명확하게 작성하고 필요시 뷰(VIEW)로 캡슐화하는 것을 고려하세요.
성능 모니터링: EXPLAIN ANALYZE를 사용하여 JSONB 컬럼을 사용하는 쿼리의 실행 계획을 정기적으로 확인하고, 인덱스가 제대로 활용되는지 검증해야 합니다.
jsonb_pretty() 및 jsonb_typeof(): 데이터 확인 및 디버깅 시 jsonb_pretty(details)로 가독성 좋게 출력하거나, jsonb_typeof(details->'some_key')로 특정 키의 데이터 타입을 확인할 수 있습니다.

참고 사이트
https://ysyblog.tistory.com/390
https://arcozz.tistory.com/25?category=1056644

gyumingim

김규민

이전 포스트

asd

다음 포스트

PostgreSQL 인 딥다이브

1. PostgeSQL

장점

단점

2. PostgreSQL 설치 / 기본 셋팅

3. 기초적인 CRUD

CREATE

INSERT

SELECT (READ)

UPDATE

DELETE

4. 기초적인 WHERE

비교 연산자, 논리 연산자

범위 조건, 목록 조건, 패턴 매칭

NULL 값 처리

서브쿼리, JOIN

인덱스

날짜/시간 데이터 타입 활용

CASE 문과 WHERE 절

배열 컬럼 (ARRAY) 조건

5. 기초적인 ORDER BY

1. 기본 ORDER BY 절 (Basic ORDER BY Clause)

2. 여러 컬럼으로 정렬 (Sorting by Multiple Columns)

3. 컬럼 번호로 정렬 (Sorting by Column Number)

4. 표현식 또는 함수로 정렬 (Sorting by Expression or Function)

5. CASE 문을 이용한 동적 정렬 (Dynamic Sorting with CASE)

6. NULL 값의 정렬 순서 (NULLs Ordering)

7. 인덱스 (Indexes) 활용 및 성능 고려

8. LIMIT 및 OFFSET과 함께 사용

6. 기초적인 DATA TYPE

PostgreSQL 데이터 타입, 왜 중요할까?

주요 PostgreSQL 데이터 타입 및 실무 활용

1. 숫자형 (Numeric Types)

2. 문자열형 (Character Types)

3. 날짜/시간형 (Date/Time Types)

4. 불리언형 (Boolean Type)

5. JSON/JSONB (JavaScript Object Notation)

6. 배열형 (Array Types)

7. UUID (Universally Unique Identifier)

7. 기초적인 제약조건

PostgreSQL 제약 조건, 왜 중요할까?

주요 PostgreSQL 제약 조건 및 실무 활용

1. PRIMARY KEY (기본 키)

2. NOT NULL (널 아님)

3. UNIQUE (고유)

4. CHECK (체크)

5. FOREIGN KEY (외래 키)

제약 조건 추가/제거 (ALTER TABLE)

8. 기초적인 함수

PostgreSQL 함수, 왜 중요할까?

주요 PostgreSQL 내장 함수 및 실무 활용

1. 문자열 함수 (String Functions)

2. 숫자 함수 (Numeric Functions)

3. 날짜/시간 함수 (Date/Time Functions)

4. 집계 함수 (Aggregate Functions)

5. 조건 함수 (Conditional Functions)

6. 형 변환 함수 (Type Conversion Functions)

7. JSON/JSONB 함수 (JSON Functions)

사용자 정의 함수 (User-Defined Functions, UDFs)

9. 기초적인 GROUP BY

PostgreSQL GROUP BY 절, 왜 중요할까?

기본 GROUP BY 절 (Basic GROUP BY Clause)

여러 컬럼으로 그룹화 (Grouping by Multiple Columns)

HAVING 절을 이용한 그룹 필터링 (Filtering Groups with HAVING)

GROUPING SETS, ROLLUP, CUBE를 이용한 고급 집계

GROUPING 함수 (Identifying Grouping Levels)

성능 고려사항

10. 기초적인 HAVING

PostgreSQL HAVING 절, 왜 중요할까?

WHERE 절과 HAVING 절의 차이점 (핵심 개념)

기본 HAVING 절 활용 (Basic HAVING Clause)

WHERE 절과 HAVING 절 동시 활용 (Combined Usage)

HAVING 절에서 여러 조건 사용 (Multiple Conditions)

HAVING 절에서 별칭(Alias) 사용 (PostgreSQL Specific)

성능 고려사항

11. 기초적인 JOIN

PostgreSQL JOIN 절, 왜 중요할까?

주요 JOIN 타입 및 실무 활용

1. INNER JOIN (내부 조인)

2. LEFT JOIN (LEFT OUTER JOIN, 좌측 외부 조인)

8. `LIMIT` 및 `OFFSET`과 함께 사용

1. `PRIMARY KEY` (기본 키)

2. `NOT NULL` (널 아님)

3. `UNIQUE` (고유)

4. `CHECK` (체크)

5. `FOREIGN KEY` (외래 키)

PostgreSQL `GROUP BY` 절, 왜 중요할까?

기본 `GROUP BY` 절 (Basic GROUP BY Clause)

`HAVING` 절을 이용한 그룹 필터링 (Filtering Groups with HAVING)

`GROUPING SETS`, `ROLLUP`, `CUBE`를 이용한 고급 집계

`GROUPING` 함수 (Identifying Grouping Levels)

PostgreSQL `HAVING` 절, 왜 중요할까?

`WHERE` 절과 `HAVING` 절의 차이점 (핵심 개념)

기본 `HAVING` 절 활용 (Basic HAVING Clause)

`WHERE` 절과 `HAVING` 절 동시 활용 (Combined Usage)

`HAVING` 절에서 여러 조건 사용 (Multiple Conditions)

`HAVING` 절에서 별칭(Alias) 사용 (PostgreSQL Specific)

PostgreSQL `JOIN` 절, 왜 중요할까?

주요 `JOIN` 타입 및 실무 활용

1. `INNER JOIN` (내부 조인)

2. `LEFT JOIN` (LEFT OUTER JOIN, 좌측 외부 조인)

3. `RIGHT JOIN` (RIGHT OUTER JOIN, 우측 외부 조인)

4. `FULL JOIN` (FULL OUTER JOIN, 완전 외부 조인)

5. `CROSS JOIN` (교차 조인)

고급 `JOIN` 활용 및 고려사항

4. `IN` / `EXISTS` 를 활용한 서브쿼리 (Conditional Subqueries)

5. `WITH` 절을 이용한 CTE (Common Table Expression)

1. `READ UNCOMMITTED` (PostgreSQL에서는 `READ COMMITTED`로 자동 상향 조정)

2. `READ COMMITTED` (PostgreSQL 기본값)

3. `REPEATABLE READ`

4. `SERIALIZABLE`