티스토리 뷰

카테고리 없음

PostgreSQL 최신 기능 활용법 (파티셔닝, JSON, CTE)

코비테 2025. 3. 1. 20:18

목차



    반응형

    PostgreSQL 최신 기능 활용법 (파티셔닝, JSON, CTE) 관련 사진

     

    PostgreSQL은 오픈소스 데이터베이스 중에서도 강력한 기능을 제공하는 시스템입니다. 최신 버전에서는 성능 최적화와 개발 편의성을 위한 다양한 기능이 추가되었습니다. 특히 파티셔닝(partitioning), JSON 데이터 처리, 공통 테이블 표현식(CTE, Common Table Expressions) 기능은 대용량 데이터 처리와 복잡한 쿼리 최적화에 큰 도움을 줍니다. 이번 글에서는 PostgreSQL의 최신 기능을 활용하는 방법을 알아보겠습니다.

    파티셔닝을 활용한 대용량 데이터 최적화

    대용량 데이터를 처리할 때, 하나의 테이블에 모든 데이터를 저장하면 조회 속도가 느려지고 성능이 저하됩니다. PostgreSQL의 테이블 파티셔닝(Table Partitioning) 기능을 활용하면 이를 해결할 수 있습니다.

    테이블 파티셔닝이란?

    파티셔닝은 큰 테이블을 여러 개의 작은 테이블(파티션)로 나누어 저장하는 방식입니다. 데이터 검색 시 필요한 파티션만 조회하기 때문에 성능이 향상됩니다. PostgreSQL에서는 범위 파티셔닝(Range Partitioning), 리스트 파티셔닝(List Partitioning), 해시 파티셔닝(Hash Partitioning)을 지원합니다.

    범위 파티셔닝 예제

    CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    sale_date DATE NOT NULL,
    amount NUMERIC NOT NULL
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2024_01 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE sales_2024_02 PARTITION OF sales
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

    리스트 파티셔닝 예제

    CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    region TEXT NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE customers_asia PARTITION OF customers FOR VALUES IN ('Korea', 'Japan', 'China');
CREATE TABLE customers_europe PARTITION OF customers FOR VALUES IN ('Germany', 'France', 'Italy');

    JSON 데이터를 활용한 NoSQL 기능

    PostgreSQL은 JSON 데이터 타입을 지원하여, NoSQL 데이터베이스처럼 문서 기반 데이터를 저장하고 검색할 수 있습니다.

    JSON 컬럼 생성 및 데이터 삽입

    CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    details JSONB NOT NULL
);

INSERT INTO products (name, details) VALUES
('Laptop', '{"brand": "Apple", "model": "MacBook Pro", "price": 2500}'),
('Phone', '{"brand": "Samsung", "model": "Galaxy S23", "price": 1200}');

    JSON 데이터 조회

    SELECT name, details->>'brand' AS brand FROM products;

    JSON 데이터 필터링

    SELECT * FROM products WHERE details @> '{"brand": "Apple"}';

    JSON 인덱스 최적화

    CREATE INDEX idx_products_details ON products USING GIN(details);

    CTE(Common Table Expressions)를 활용한 복잡한 쿼리 최적화

    공통 테이블 표현식(CTE)은 서브쿼리를 보다 가독성 좋게 작성하고, 쿼리 성능을 향상할 수 있는 기능입니다.

    CTE 기본 사용법

    WITH recent_sales AS (
    SELECT * FROM sales WHERE sale_date > NOW() - INTERVAL '30 days'
)
SELECT * FROM recent_sales WHERE amount > 100;

    재귀 CTE 활용

    WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

    결론

    PostgreSQL의 최신 기능을 활용하면 성능 최적화와 데이터 처리 속도를 향상할 수 있습니다.

    • 파티셔닝을 활용하면 대용량 데이터를 효율적으로 저장하고 조회 성능을 높일 수 있습니다.
    • JSON 데이터 타입을 사용하면 NoSQL처럼 유연한 데이터 모델링이 가능합니다.
    • CTE를 활용하면 복잡한 쿼리를 최적화하고 가독성을 개선할 수 있습니다.
    반응형