대용량 데이터 작업을 위한 함수 최적화

PostgreSQL에서 함수 최적화 얘기할 때 보통 두 가지 핵심 요소가 있어: 인덱싱이랑 파티셔닝. 이 두 가지 테크닉 덕분에 불필요한 계산을 줄이고, 데이터에 "딱 맞게" 접근할 수 있어서 대용량 데이터도 훨씬 빠르게 처리할 수 있어. 하나씩 자세히 알아보자.

DB에서 인덱스는 책의 목차랑 똑같이 생각하면 돼. 책에서 정보를 찾을 때 모든 페이지를 다 읽지 않잖아? 목차를 보고 원하는 주제를 찾아 바로 그 페이지로 가는 거지. PostgreSQL의 인덱스도 딱 그런 역할을 해.

인덱스 생성하기

인덱스는 CREATE INDEX 명령어로 만들어. 간단한 예시를 볼게:

-- users 테이블의 id 컬럼에 인덱스를 만들어서 검색 속도를 높임
CREATE INDEX idx_users_id ON users (id);

이제 이런 쿼리를 실행하면:

SELECT * FROM users WHERE id = 42;

PostgreSQL이 방금 만든 인덱스를 써서 원하는 행을 빠르게 찾을 거야.

예시: 인덱스를 활용한 함수 최적화

예를 들어, orders 테이블에서 특정 유저의 주문 데이터를 뽑아오는 함수가 있다고 해보자:

CREATE OR REPLACE FUNCTION get_user_orders(user_id INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE user_id = user_id;
END; 
$$ LANGUAGE plpgsql;

orders 테이블에 수백만 행이 있다면 이 함수는 엄청 느릴 거야. 해결책? user_id에 인덱스를 만들어주자:

CREATE INDEX idx_orders_user_id ON orders (user_id);

이제 함수 안의 쿼리가 훨씬 빨라질 거고, PostgreSQL이 인덱스를 써서 행을 찾을 거야.

인덱스 종류

PostgreSQL은 여러 가지 인덱스 타입을 지원하는데, 제일 많이 쓰는 건 B-TREE랑 GIN이야. 간단히 비교해보면:

인덱스를 더 깊게 공부하고 싶으면 PostgreSQL 공식 문서를 참고해봐.

데이터 파티셔닝

인덱스가 검색을 빠르게 해주는 거라면, 파티셔닝은 테이블을 더 작은 "조각"(파티션)으로 나누는 방법이야. 한 테이블에 데이터가 엄청 많을 때 유용하지.

예를 들어 orders 테이블이 있고, 지난 10년치 주문이 다 들어있다고 해보자. 최근 한 달 주문만 찾으려고 쿼리해도 PostgreSQL은 전체 테이블을 다 뒤져야 해서 느려. 파티셔닝을 하면 데이터를 연도별로 쪼갤 수 있어서 이런 문제를 해결할 수 있어.

파티셔닝 테이블 만들기

파티셔닝 테이블 만드는 방법은 이래:

-- orders 테이블을 부모 파티션으로 만듦
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    user_id INT NOT NULL
) PARTITION BY RANGE (order_date);

-- 각 연도별로 자식 테이블 생성
CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2022 PARTITION OF orders FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

이제 이런 쿼리를 실행하면:

SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2023-02-01';

PostgreSQL이 orders_2023 테이블만 딱 찾아보고, 전체 테이블을 뒤지지 않아도 돼.

함수에서 파티셔닝 활용하기

특정 연도의 주문을 뽑는 함수가 있다고 해보자. 파티셔닝 덕분에 함수 안 쿼리도 훨씬 빨라져. PostgreSQL이 해당 연도 파티션만 딱 찾아보거든.

CREATE OR REPLACE FUNCTION get_orders_by_year(year INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE order_date >= make_date(year, 1, 1) 
      AND order_date < make_date(year + 1, 1, 1);
END;
$$ LANGUAGE plpgsql;

실전 케이스

1. 인덱싱 케이스

문자열 검색: 상품 테이블이 있고, 상품 이름으로 자주 검색한다면 name 필드에 인덱스를 만들어줘:

CREATE INDEX idx_products_name ON products (name);

정렬 속도 높이기: 쿼리에서 날짜로 정렬을 자주 한다면 인덱스를 만들어:

CREATE INDEX idx_orders_date ON orders (order_date);

2. 파티셔닝 케이스

이력 데이터: 테이블에 타임스탬프 데이터가 있다면, 일/월/연도별로 파티셔닝하면 쿼리가 훨씬 빨라져.

지리 데이터: 테이블에 국가별 데이터가 있다면, 각 나라별로 파티션을 만들어줘.

자주 하는 실수와 해결법

많은 개발자들이 인덱스를 너무 많이 만들어서 실수해. 그러면 데이터 삽입이나 업데이트가 느려져. 왜냐면 테이블이 바뀔 때마다 PostgreSQL이 인덱스도 같이 갱신해야 하거든. 팁: 자주 조건이나 정렬에 쓰는 필드에만 인덱스를 만들어.

또 하나 흔한 실수는 파티셔닝을 잘못하는 거야. 너무 잘게(예: 월별 대신 일별) 파티션을 나누면, 테이블 관리 오버헤드가 커질 수 있어.