pg_stat_ch가 PostgreSQL 쿼리 기록을 ClickHouse로 보낸다

이 본문은 AI로 번역되었으며, 오역이 있을 수도 있습니다. · 출처: github.com

pg_stat_ch는 PostgreSQL에서 실행한 쿼리별 실행 데이터를 수집해 ClickHouse로 실시간 내보내는 확장 프로그램이다. PostgreSQL 안에서 통계를 합산하는 pg_stat_statements와 달리 원시 이벤트를 ClickHouse에 저장하고, 집계는 ClickHouse에서 수행한다.

빠른 시작

저장소의 빠른 시작 스크립트는 스키마가 미리 설정된 PostgreSQL과 ClickHouse를 실행한다.

./scripts/quickstart.sh up
./scripts/quickstart.sh check

중지할 때는 다음 명령을 실행한다.

./scripts/quickstart.sh down

접속 엔드포인트와 구성 세부 사항은 docker/quickstart/README.md에서 확인할 수 있다.

수집 방식과 기능

확장 프로그램은 PostgreSQL 훅에서 쿼리의 시작과 종료를 포착한 뒤, 공유 메모리 링 버퍼에 이벤트를 넣는다. 백그라운드 작업자가 이벤트를 묶어 ClickHouse로 내보내며, ClickHouse는 원시 이벤트를 events_raw 테이블에 저장한다. 뷰와 구체화 뷰를 이용해 집계할 수 있다.

제작자가 밝힌 설계상 특징은 다음과 같다.

쿼리 처리 경로에서는 네트워크 입출력을 하지 않는다. 이벤트를 동기적으로 전송하는 대신 공유 메모리에 대기시킨다.
집계값이 아니라 원시 이벤트를 전송한다. p50·p95·p99 백분위, 상위 쿼리, 오류 집계는 ClickHouse에서 수행한다.
메모리 사용량은 고정 크기 링 버퍼로 제한한다. 버퍼가 넘치면 카운터를 증가시키며 이벤트를 버리고, 쿼리를 차단하지 않는다.
문장 하나를 수집할 때 추가되는 지연은 p99 기준 약 5μs라고 제작자는 설명한다.

수집 항목에는 실행 시간, 행 수, 버퍼 사용량, WAL 사용량, CPU 시간 등이 포함된다. SELECT, INSERT, UPDATE, DELETE, MERGE 같은 DML뿐 아니라 DDL과 유틸리티 문장도 지원한다. emit_log_hook을 통한 오류 캡처는 SQLSTATE 코드와 오류 수준을 기록한다. PostgreSQL 15 이상에서는 JIT 함수 수와 생성·인라이닝·최적화·코드 방출 시간을 계측하고, PostgreSQL 18 이상에서는 계획된 병렬 작업자와 실제 실행된 작업자 수를 기록한다.

클라이언트 문맥으로 애플리케이션 이름과 클라이언트 IP 주소를 수집하며, 쿼리 문자열은 최대 2KB까지 저장한다. 대기열이 넘치면 이벤트를 버리고 카운터를 남긴다. ClickHouse를 사용할 수 없는 경우에도 PostgreSQL을 차단하지 않도록 설계됐다.

지원 버전과 설치

PostgreSQL 16, 17, 18을 완전히 지원한다. 기능과 버전별 필드의 호환성 표는 버전 호환성 문서에서 확인할 수 있다. 네이티브 방식으로 내보내려면 ClickHouse 23.3 이상이 필요하다.

소스에서 빌드하려면 CMake 3.16 이상, C++17 컴파일러(GCC 9 이상 또는 Clang 10 이상), PostgreSQL 16 이상의 개발 헤더가 필요하다. PostgreSQL 설치에는 mise 사용을 권장하며, 수동 설치도 가능하다. clickhouse-c는 서브모듈로 포함되며 정적으로 링크된다.

git submodule update --init --recursive

# Using mise (recommended)
mise run build # Debug build (use build:16/17/18 for specific versions)
mise run build:release # Release build
mise run install # Install the extension

# Or manually
cmake -B build -G Ninja -DPG_CONFIG=/path/to/pg_config
cmake --build build && cmake --install build

PostgreSQL 설정 파일 postgresql.conf에 다음 항목을 추가한다. ClickHouse 연결 값은 환경에 맞게 바꾼다. 운영 환경에서는 TLS 사용을 권장한다.

conf

shared_preload_libraries = 'pg_stat_ch'
track_io_timing = on # Enables I/O timing columns for captured events

# ClickHouse connection (change for your setup)
pg_stat_ch.clickhouse_host = 'localhost'
pg_stat_ch.clickhouse_port = 9000
pg_stat_ch.clickhouse_database = 'pg_stat_ch'

# TLS (recommended for production)
pg_stat_ch.clickhouse_use_tls = on
pg_stat_ch.clickhouse_skip_tls_verify = off

설정을 바꾼 뒤 systemd, brew services, Docker 등 사용하는 서비스 관리 도구로 PostgreSQL을 재시작한다. 빠른 시작 스크립트를 사용하면 ClickHouse 스키마가 자동으로 설정된다. 기존 ClickHouse를 사용하거나 수동으로 설정할 때는 다음 명령으로 스키마를 적용한다. 이 작업은 events_raw 테이블과 구체화 뷰를 만든다.

clickhouse-client < docker/init/00-schema.sql

스키마 전체 설명은 ClickHouse 문서에 있다. 이후 PostgreSQL에서 확장 프로그램을 만들고 설치 상태를 확인한다.

sql

CREATE EXTENSION pg_stat_ch;

sql

SELECT pg_stat_ch_version();
SELECT * FROM pg_stat_ch_stats();

설정 항목과 SQL API

아래 설정에서 SIGHUP은 설정을 다시 읽어 적용할 수 있음을 뜻한다. Restart는 PostgreSQL 재시작이 필요하고, Superuser는 슈퍼유저 권한이 필요한 항목이다.

매개변수	유형	기본값	적용 조건	설명
`pg_stat_ch.enabled`	bool	`on`	SIGHUP	텔레메트리 수집을 켜거나 끈다
`pg_stat_ch.clickhouse_host`	string	`localhost`	재시작	ClickHouse 서버 호스트 이름
`pg_stat_ch.clickhouse_port`	int	`9000`	재시작	ClickHouse 네이티브 프로토콜 포트
`pg_stat_ch.clickhouse_user`	string	`default`	재시작	ClickHouse 사용자 이름
`pg_stat_ch.clickhouse_password`	string	`""`	재시작	ClickHouse 비밀번호
`pg_stat_ch.clickhouse_database`	string	`pg_stat_ch`	재시작	ClickHouse 데이터베이스 이름
`pg_stat_ch.queue_capacity`	int	`131072`	재시작	링 버퍼 크기. 2의 거듭제곱이어야 한다
`pg_stat_ch.string_area_size`	int	`64MB`	재시작	길이가 변하는 쿼리·오류 문자열에 사용하는 DSA 크기
`pg_stat_ch.flush_interval_ms`	int	`500`	SIGHUP	내보내기 배치 간격(밀리초)
`pg_stat_ch.batch_max`	int	`131072`	SIGHUP	ClickHouse에 한 번에 넣을 최대 이벤트 수. `queue_capacity`를 넘지 않도록 제한된다
`pg_stat_ch.clickhouse_use_tls`	bool	`off`	재시작	ClickHouse 연결에서 TLS를 사용한다
`pg_stat_ch.clickhouse_skip_tls_verify`	bool	`off`	재시작	TLS 인증서 검증을 건너뛴다(안전하지 않음)
`pg_stat_ch.log_min_elevel`	enum	`warning`	슈퍼유저	수집할 최소 오류 수준(`debug5`부터 `panic`까지)

오류 수준과 ClickHouse에서 사용하는 숫자 값의 전체 목록은 오류 수준 값 문서를 참고하면 된다.

SQL API에는 확장 프로그램 버전 문자열을 반환하는 pg_stat_ch_version(), 대기열과 내보내기 통계를 반환하는 pg_stat_ch_stats(), 대기열 카운터를 모두 0으로 초기화하는 pg_stat_ch_reset(), 대기 중인 이벤트를 즉시 내보내는 pg_stat_ch_flush()가 있다. pg_stat_ch_stats()의 열 정보는 문제 해결 문서의 SQL API 참고 항목에서 확인할 수 있다.

쿼리 예시와 문제 해결

실행별 원시 이벤트를 저장하면 백분위와 시계열을 계산하거나 애플리케이션별로 데이터를 살펴볼 수 있다. 다음 예시는 특정 애플리케이션의 최근 1시간 쿼리 중 p99 지연이 높은 항목을 보여준다.

sql

-- Slowest queries for a specific application, with percentiles
SELECT query_id,
 count() AS calls,
 quantile(0.95)(duration_us) / 1000 AS p95_ms,
 quantile(0.99)(duration_us) / 1000 AS p99_ms
FROM pg_stat_ch.events_raw
WHERE app = 'myapp'
 AND ts_start > now() - INTERVAL 1 HOUR
GROUP BY query_id
ORDER BY p99_ms DESC
LIMIT 10;

구체화 뷰 정의와 추가 쿼리 예시는 ClickHouse 문서에 있다.

테스트 명령은 다음과 같다.

mise run test:all # Run all tests
mise run test:regress # SQL regression tests only
./scripts/run-tests.sh 18 all # Specific PG version
./scripts/run-tests.sh ../postgres/install_tap tap # TAP tests with local PG build

테스트 종류와 TAP 테스트 설정, 테스트 파일 전체 목록은 테스트 문서에서 확인할 수 있다.

확장 프로그램이 로드되지 않으면 shared_preload_libraries 설정을 확인한다. 이벤트가 나타나지 않으면 pg_stat_ch_stats()에서 오류를 살핀다. 대기열 사용량이 높거나 이벤트가 버려지면 pg_stat_ch.queue_capacity, pg_stat_ch.flush_interval_ms, pg_stat_ch.batch_max를 조정할 수 있다. 자세한 해결 방법은 문제 해결 문서에 정리돼 있다.

프로젝트는 Apache License, Version 2.0으로 배포된다. 전문은 LICENSE.md에서 볼 수 있다.

빠른 시작

저장소의 빠른 시작 스크립트는 스키마가 미리 설정된 PostgreSQL과 ClickHouse를 실행한다.

./scripts/quickstart.sh up
./scripts/quickstart.sh check

중지할 때는 다음 명령을 실행한다.

./scripts/quickstart.sh down

접속 엔드포인트와 구성 세부 사항은 docker/quickstart/README.md에서 확인할 수 있다.

수집 방식과 기능

제작자가 밝힌 설계상 특징은 다음과 같다.

쿼리 처리 경로에서는 네트워크 입출력을 하지 않는다. 이벤트를 동기적으로 전송하는 대신 공유 메모리에 대기시킨다.
집계값이 아니라 원시 이벤트를 전송한다. p50·p95·p99 백분위, 상위 쿼리, 오류 집계는 ClickHouse에서 수행한다.
메모리 사용량은 고정 크기 링 버퍼로 제한한다. 버퍼가 넘치면 카운터를 증가시키며 이벤트를 버리고, 쿼리를 차단하지 않는다.
문장 하나를 수집할 때 추가되는 지연은 p99 기준 약 5μs라고 제작자는 설명한다.

지원 버전과 설치

git submodule update --init --recursive

# Using mise (recommended)
mise run build # Debug build (use build:16/17/18 for specific versions)
mise run build:release # Release build
mise run install # Install the extension

# Or manually
cmake -B build -G Ninja -DPG_CONFIG=/path/to/pg_config
cmake --build build && cmake --install build

PostgreSQL 설정 파일 postgresql.conf에 다음 항목을 추가한다. ClickHouse 연결 값은 환경에 맞게 바꾼다. 운영 환경에서는 TLS 사용을 권장한다.

conf

shared_preload_libraries = 'pg_stat_ch'
track_io_timing = on # Enables I/O timing columns for captured events

# ClickHouse connection (change for your setup)
pg_stat_ch.clickhouse_host = 'localhost'
pg_stat_ch.clickhouse_port = 9000
pg_stat_ch.clickhouse_database = 'pg_stat_ch'

# TLS (recommended for production)
pg_stat_ch.clickhouse_use_tls = on
pg_stat_ch.clickhouse_skip_tls_verify = off

clickhouse-client < docker/init/00-schema.sql

스키마 전체 설명은 ClickHouse 문서에 있다. 이후 PostgreSQL에서 확장 프로그램을 만들고 설치 상태를 확인한다.

sql

CREATE EXTENSION pg_stat_ch;

sql

SELECT pg_stat_ch_version();
SELECT * FROM pg_stat_ch_stats();

설정 항목과 SQL API

매개변수	유형	기본값	적용 조건	설명
`pg_stat_ch.enabled`	bool	`on`	SIGHUP	텔레메트리 수집을 켜거나 끈다
`pg_stat_ch.clickhouse_host`	string	`localhost`	재시작	ClickHouse 서버 호스트 이름
`pg_stat_ch.clickhouse_port`	int	`9000`	재시작	ClickHouse 네이티브 프로토콜 포트
`pg_stat_ch.clickhouse_user`	string	`default`	재시작	ClickHouse 사용자 이름
`pg_stat_ch.clickhouse_password`	string	`""`	재시작	ClickHouse 비밀번호
`pg_stat_ch.clickhouse_database`	string	`pg_stat_ch`	재시작	ClickHouse 데이터베이스 이름
`pg_stat_ch.queue_capacity`	int	`131072`	재시작	링 버퍼 크기. 2의 거듭제곱이어야 한다
`pg_stat_ch.string_area_size`	int	`64MB`	재시작	길이가 변하는 쿼리·오류 문자열에 사용하는 DSA 크기
`pg_stat_ch.flush_interval_ms`	int	`500`	SIGHUP	내보내기 배치 간격(밀리초)
`pg_stat_ch.batch_max`	int	`131072`	SIGHUP	ClickHouse에 한 번에 넣을 최대 이벤트 수. `queue_capacity`를 넘지 않도록 제한된다
`pg_stat_ch.clickhouse_use_tls`	bool	`off`	재시작	ClickHouse 연결에서 TLS를 사용한다
`pg_stat_ch.clickhouse_skip_tls_verify`	bool	`off`	재시작	TLS 인증서 검증을 건너뛴다(안전하지 않음)
`pg_stat_ch.log_min_elevel`	enum	`warning`	슈퍼유저	수집할 최소 오류 수준(`debug5`부터 `panic`까지)

오류 수준과 ClickHouse에서 사용하는 숫자 값의 전체 목록은 오류 수준 값 문서를 참고하면 된다.

쿼리 예시와 문제 해결

sql

-- Slowest queries for a specific application, with percentiles
SELECT query_id,
 count() AS calls,
 quantile(0.95)(duration_us) / 1000 AS p95_ms,
 quantile(0.99)(duration_us) / 1000 AS p99_ms
FROM pg_stat_ch.events_raw
WHERE app = 'myapp'
 AND ts_start > now() - INTERVAL 1 HOUR
GROUP BY query_id
ORDER BY p99_ms DESC
LIMIT 10;

구체화 뷰 정의와 추가 쿼리 예시는 ClickHouse 문서에 있다.

테스트 명령은 다음과 같다.

mise run test:all # Run all tests
mise run test:regress # SQL regression tests only
./scripts/run-tests.sh 18 all # Specific PG version
./scripts/run-tests.sh ../postgres/install_tap tap # TAP tests with local PG build

테스트 종류와 TAP 테스트 설정, 테스트 파일 전체 목록은 테스트 문서에서 확인할 수 있다.

프로젝트는 Apache License, Version 2.0으로 배포된다. 전문은 LICENSE.md에서 볼 수 있다.

pg_stat_ch가 PostgreSQL 쿼리 기록을 ClickHouse로 보낸다

빠른 시작

수집 방식과 기능

지원 버전과 설치

설정 항목과 SQL API

쿼리 예시와 문제 해결

— MoAI 오피니언AI 생성

댓글 (0)

pg_stat_ch가 PostgreSQL 쿼리 기록을 ClickHouse로 보낸다

빠른 시작

수집 방식과 기능

지원 버전과 설치

설정 항목과 SQL API

쿼리 예시와 문제 해결

— MoAI 오피니언AI 생성

댓글 (0)

pg_stat_ch가 PostgreSQL 쿼리 기록을 ClickHouse로 보낸다

빠른 시작

수집 방식과 기능

지원 버전과 설치

설정 항목과 SQL API

쿼리 예시와 문제 해결

Ultrathink — MoAI 오피니언AI 생성

댓글 (0)

pg_stat_ch가 PostgreSQL 쿼리 기록을 ClickHouse로 보낸다

빠른 시작

수집 방식과 기능

지원 버전과 설치

설정 항목과 SQL API

쿼리 예시와 문제 해결

Ultrathink — MoAI 오피니언AI 생성

댓글 (0)

— MoAI 오피니언AI 생성

— MoAI 오피니언AI 생성