대용량 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 ..

TimescaleDB 사용 중 hypertable 및 chunk 포함 전체 백업과 복원하기PostgreSQL에서 TimescaleDB 확장을 사용할 때, 일반적인 pg_dump 명령어를 사용해서 전체 데이터를 백업할 수 있습니다. 하지만 hypertable과 chunk가 일반 테이블과는 다르게 동작하기 때문에, 복원 시 문제가 생길 수 있습니다. 특히 restore 시에 chunk를 찾지 못한다는 오류가 발생하면 당황스럽죠. 이 글에서는 hypertable과 chunk를 포함한 전체 백업 및 복원 방법과 그 과정에서 자주 발생하는 문제점 및 해결법을 정리해봤습니다.pg_dump를 이용한 전체 백업TimescaleDB는 PostgreSQL의 확장이므로 pg_dump 명령만으로 hypertable, chun..

1. PostgreSQL에서 JSONB 데이터 평균 구하기JSONB 컬럼이 포함된 테이블에서 특정 키(예: "efficiency") 값을 필터링하고 평균을 구하는 기본적인 SQL 쿼리는 다음과 같습니다.SELECT key AS tag_name, AVG(value::NUMERIC) AS avg_valueFROM sensor_data, LATERAL jsonb_each_text(outputs)WHERE timestamp_col BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59' AND key ILIKE '%efficiency%' AND (value::NUMERIC) BETWEEN 0 AND 100GROUP BY key;이 쿼리는..

PostgreSQL에서 최댓값을 구하는 방법은 MAX() 함수와 ORDER BY ... DESC LIMIT 1 방식이 있습니다. 각각의 차이점과 성능을 비교해보겠습니다.1. MAX() 함수 사용최댓값을 구할 때 MAX()를 사용하면 쉽게 값을 찾을 수 있습니다.SELECT MAX(column_name) FROM table_name;장점최댓값을 빠르게 조회할 수 있음정렬이 필요하지 않음단점해당 값을 가진 행을 가져오려면 서브쿼리를 사용해야 함SELECT * FROM table_name WHERE column_name = (SELECT MAX(column_name) FROM table_name);2. ORDER BY DESC LIMIT 1 사용최댓값을 포함한 행을 가져올 때 유용합니다.SELECT * FRO..

1. DISTINCT ON 사용 (PostgreSQL 전용)PostgreSQL에서는 DISTINCT ON을 사용하면 간단하게 해결할 수 있습니다.SELECT DISTINCT ON (tagname) tagname, status, timestampFROM your_tableORDER BY tagname, timestamp DESC;설명DISTINCT ON (tagname)을 사용하여 각 tagname별로 첫 번째 행만 선택합니다.ORDER BY tagname, timestamp DESC를 지정하여 최신 timestamp 값이 먼저 오도록 정렬합니다.최신 데이터의 status 값을 가져올 수 있습니다.2. 윈도우 함수 사용 (다른 DBMS에서도 활용 가능)PostgreSQL 외의 다른 DBMS에서도 적용할 수..