PL/pgSQL 성능 개선 사례와 실무 적용

개요

이 글은 PostgreSQL의 PL/pgSQL 함수와 저장 프로시저 성능을 개선한 실제 사례를 바탕으로 한 설명서이다. 처음 접하는 개발자도 이해하기 쉽도록 병목 원인 파악부터 적용 가능한 해결책까지 단계별로 정리한다. 핵심은 반복 처리 최소화, 쿼리 재구성, 그리고 실행 계획 이해에 있다.

흔한 병목과 원인

1. 루프 기반의 행별 처리

많은 PL/pgSQL 성능 문제는 루프 안에서 한 행씩 처리할 때 발생한다. 네트워크 왕복이나 반복적인 쿼리 실행 비용이 누적되기 때문이다. 간단한 예로, 테이블의 각 행에 대해 개별 업데이트를 수행하면 비용이 크게 증가한다.

2. 동적 SQL의 과도한 재계획

EXECUTE로 동적 쿼리를 자주 호출하면 쿼리 계획이 매번 재생성돼 오버헤드가 발생할 수 있다. 파라미터 바인딩 없이 문자열을 직접 조합하는 방식은 성능 저하와 보안 위험을 모두 초래한다.

3. 불필요한 타입 변환과 함수 호출

잘못된 데이터 타입 사용이나 과도한 문자열 변환은 계산 비용을 늘린다. 또한 VOLATILE로 표시된 함수는 최적화가 제한된다.

개선 기법과 사례

1. 집합 연산으로 전환

행별 처리 대신 한 번의 집합 연산으로 처리하면 성능이 비약적으로 개선된다. 아래는 나쁜 예와 개선 예시다.

-- 나쁜 예: 각 행을 순회하며 업데이트함
CREATE OR REPLACE FUNCTION update_prices_bad() RETURNS void AS $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN SELECT id, qty FROM items LOOP
    IF r.qty < 10 THEN
      UPDATE items SET price = price * 0.9 WHERE id = r.id;
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 개선: 집합 연산으로 한 번에 처리
CREATE OR REPLACE FUNCTION update_prices_setbased() RETURNS void AS $$
BEGIN
  UPDATE items
  SET price = price * 0.9
  WHERE qty < 10;
END;
$$ LANGUAGE plpgsql;

집합 연산은 내부에서 최적화가 진행되며 I/O와 잠금 횟수를 줄인다.

2. EXECUTE 사용 시 파라미터 바인딩

동적 SQL이 불가피한 경우에는 EXECUTE ... USING으로 파라미터를 전달하면 재계획 비용과 SQL 인젝션 위험을 줄일 수 있다.

-- 권장: USING으로 파라미터 전달
CREATE OR REPLACE FUNCTION cnt_by_table(tname text, colname text, val text) RETURNS bigint AS $$
DECLARE
  sql text;
  result bigint;
BEGIN
  sql := format('SELECT count(*) FROM %I WHERE %I = $1', tname, colname);
  EXECUTE sql USING val INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

3. LANGUAGE SQL 및 INLINING 활용

단순한 쿼리 반환 함수는 LANGUAGE SQL로 작성하면 더 빠르고 인라인되어 실행 계획에 통합된다. 가능하면 SQL 함수로 대체하는 것을 고려한다.

-- SQL 함수 예시: 인라인이 가능하여 빠름
CREATE OR REPLACE FUNCTION get_active_users() RETURNS TABLE(id int, name text) AS $$
  SELECT id, name FROM users WHERE active;
$$ LANGUAGE sql;

4. 함수 속성으로 최적화 단서 제공

IMMUTABLE, STABLE, VOLATILE 속성은 최적화에 영향을 준다. 결과가 변하지 않으면 IMMUTABLE을 지정하면 캐시와 인라인화에 도움이 된다. 단, 실제 의미에 맞게 설정해야 한다.

5. EXPLAIN ANALYZE로 병목 지점 확인

변경 전후로 EXPLAIN ANALYZE를 비교하면 실제 개선 효과를 확인할 수 있다. 실행 시간, I/O, 노드별 비용을 확인해서 병목을 정확히 찾아낸다.

6. 배치 처리와 COPY 활용

대량 데이터 처리는 가능하면 COPY나 bulk INSERT/UPDATE로 처리한다. 트랜잭션을 분할하면 잠금 경쟁과 WAL 부하를 낮출 수 있다. 백그라운드 작업으로 처리하는 것도 방법이다.

7. 임시 테이블과 UNLOGGED 테이블 활용

중간 결과를 저장할 때는 임시 테이블이나 UNLOGGED 테이블을 사용하면 WAL 부담을 줄여 성능을 향상시킬 수 있다. 단, 복구와 복제 영향은 고려해야 한다.

실무 적용 체크리스트

• 핵심 쿼리에 대해 EXPLAIN ANALYZE 수행
• 행별 처리 여부를 점검하고 집합 연산으로 대체
• 동적 SQL에는 EXECUTE ... USING 사용
• 가능하면 LANGUAGE SQL 함수로 대체
• 함수 속성(IMMUTABLE/STABLE) 적절히 설정
• 대량 작업은 COPY 또는 배치로 분할
• 임시/UNLOGGED 테이블 사용으로 WAL/잠금 부담 감소
• 인덱스와 통계(ANALYZE) 점검

결론

PL/pgSQL 성능 개선은 작은 변경으로도 큰 효과를 낼 수 있다. 핵심은 집합 처리 우선, 동적 SQL의 안전한 사용, 그리고 실행 계획 기반의 검증이다. 위 사례와 체크리스트를 통해 우선순위를 정하고 단계적으로 적용하면 현실적인 성능 향상이 가능하다. 마지막으로 변경 전후를 반드시 계량적으로 비교하여 의도한 효과를 확인할 것을 권장한다.