[PostgreSQL] PostgreSQL JSON 쿼리 성능 개선과 Java에서의 활용

대용량 JSON 데이터를 PostgreSQL에서 조회하면서 분석 지표를 만들던 중, 생각보다 쿼리 속도가 느리고 결과가 기대와 다르게 나오는 문제가 있었습니다. 특히 JSON 필드 안의 특정 값들을 기준으로 통계 데이터를 구하고, 그 결과를 Java + MyBatis 환경에서 리스트 형태로 받아야 했기 때문에, 정렬이나 형식까지 모두 신경 써야 했습니다.

문제 상황

초기에 작성한 쿼리는 다음과 같은 형태였습니다:

WITH filtered_data AS (
    SELECT
        t."timestamp",
        (t."json_field" ->> 'target_metric')::double precision AS value
    FROM
        "data_table" t
    WHERE
        t."json_field" ->> 'target_metric' IS NOT NULL
        AND t."timestamp" BETWEEN '2025-03-30T15:00:00Z' AND '2025-03-31T15:00:00Z'
)
SELECT
    array_agg("timestamp"::text) AS series,
    MAX(value) AS max,
    MIN(value) AS min,
    AVG(value) AS value
FROM
    filtered_data
GROUP BY
    "timestamp"
ORDER BY
    "timestamp";

이 쿼리는 결과적으로 시간별로 그룹화된 여러 row를 반환했으며, JSON 필드를 반복적으로 파싱하고 있었고, series에는 시간값만 담겨 있어 Java에서 원하는 형태로 데이터를 처리하기 까다로웠습니다.

원하는 결과

1. 결과는 단 한 줄(row) 만 반환
2. series 컬럼에는 [timestamp, value] 쌍의 리스트가 JSON 배열 형태로 포함되어야 함
3. max, min, value는 각각 전체 데이터의 최댓값, 최솟값, 평균값
4. JSON 배열은 시간 기준 오름차순으로 정렬되어 있어야 함
5. Java에서는 List<List<Object>> 또는 커스텀 DTO로 쉽게 파싱 가능해야 함
6. 특정 필드(target_metric) 에는 0 과 100 사이의 값만 필터링 (협업 팀 이슈사항)

성능 개선과 쿼리 리팩토링

기존 쿼리의 중복 파싱, 불필요한 GROUP BY, 정렬 누락 등을 해결하기 위해 다음과 같이 리팩토링했습니다:

WITH filtered_data AS (
    SELECT
        t."timestamp",
        (t."json_field" ->> 'target_metric')::double precision AS value
    FROM
        "data_table" t
    WHERE
        t."json_field" ->> 'target_metric' IS NOT NULL
        AND t."timestamp" BETWEEN '2025-03-30T15:00:00Z' AND '2025-03-31T15:00:00Z'
        AND (
            (
                LOWER(t."json_field" ->> 'target_metric') LIKE '%metric%'
                AND (t."json_field" ->> 'target_metric')::double precision BETWEEN 0 AND 100
            )
            OR LOWER(t."json_field" ->> 'target_metric') NOT LIKE '%metric%'
        )
)
SELECT
    json_agg(json_build_array(
        f."timestamp"::text,
        f.value
    ) ORDER BY f."timestamp") AS series,
    MAX(f.value) AS max,
    MIN(f.value) AS min,
    AVG(f.value) AS value
FROM
    filtered_data f;

쿼리 개선 포인트

• JSON 필드 파싱을 한 번만 수행하고, 필터링과 변환을 CTE에서 처리해 효율화
• json_agg(... ORDER BY ...) 구문을 사용하여 JSON 배열 내부 순서 보장
• 최종 결과는 단일 row 로 반환되며, Java에서 바로 사용 가능한 형태 제공

Java에서 받는 형태 예시

{
  "series": [
    ["2025-03-30T15:01:00", 82.4],
    ["2025-03-30T15:02:00", 83.1],
    ...
  ],
  "max": 88.4,
  "min": 80.5,
  "value": 84.26
}

Java DTO 예시

public class MetricStats {
    private List<List<Object>> series;
    private Double max;
    private Double min;
    private Double value;
}

혹은 더 명확하게 사용하고 싶다면:

public class TimestampValuePair {
    private String timestamp;
    private Double value;
}

public class MetricStats {
    private List<TimestampValuePair> series;
    private Double max;
    private Double min;
    private Double value;
}

PGobject 에러 해결 방법

PostgreSQL의 json_agg 결과는 Java에서 PGobject로 넘어올 수 있기 때문에, 아래 중 하나를 적용해 에러 없이 처리할 수 있습니다.

1. 쿼리에서 ::text 캐스팅 사용
2. 커스텀 TypeHandler 등록
3. DTO에서 PGobject로 받고 직접 Jackson 등으로 파싱

가장 쉬운 방법은 쿼리에서 ::text로 강제 문자열 캐스팅하는 것입니다:

json_agg(...)::text AS series

마무리

PostgreSQL의 JSON 처리 기능은 유연하지만, 성능과 실사용 측면에서 신경 써야 할 부분이 많습니다. 위와 같은 방식으로 쿼리를 정리하고, Java와 연동하기 편한 형태로 가공하면 실시간 데이터 처리나 지표 시각화에서도 성능과 생산성을 동시에 잡을 수 있습니다.

데이터가 많아질수록 이런 최적화를 점점 더 고려하는 방향으로 진행해야 한다는 점을 느꼈습니다. 지금까지는 느리고 다루기 어려웠던 JSON 기반 쿼리도, 이제는 빠르고 명확하게 처리할 수 있게 되었습니다.