PostgreSQL 슬로우 쿼리 진단 — pg_stat_statements와 EXPLAIN
PostgreSQL에서 느린 쿼리를 찾고 고치는 실전 워크플로우를 정리합니다. pg_stat_statements로 문제 쿼리를 특정하고, EXPLAIN ANALYZE BUFFERS로 실행 계획을 해석해 인덱스나 쿼리를 개선하는 과정을 단계별로 다룹니다.
일치하는 글이 없습니다.
54개의 글이 있습니다.
PostgreSQL에서 느린 쿼리를 찾고 고치는 실전 워크플로우를 정리합니다. pg_stat_statements로 문제 쿼리를 특정하고, EXPLAIN ANALYZE BUFFERS로 실행 계획을 해석해 인덱스나 쿼리를 개선하는 과정을 단계별로 다룹니다.
shared_buffers·work_mem·effective_cache_size 메모리 3총사, WAL 튜닝, 체크포인트 설정, autovacuum 공격적 조정까지 운영 환경 postgresql.conf에서 반드시 손봐야 할 파라미터를 정리합니다.
PostgreSQL은 연결마다 새 프로세스를 생성합니다. PgBouncer가 커넥션을 풀링해 이 오버헤드를 줄이는 원리, 세 가지 풀링 모드(Session/Transaction/Statement)의 차이, pgbouncer.ini 핵심 설정을 정리합니다.
pg_basebackup보다 풍부한 기능을 제공하는 pgBackRest와 Barman의 핵심 기능, 증분/차등 백업, S3 연동, PITR 복구 명령어를 비교하고 선택 기준을 제시합니다.
pg_basebackup으로 베이스 백업을 만들고 WAL 아카이브를 활용해 특정 시점으로 되돌리는 PITR(Point-In-Time Recovery)의 동작 원리, archive_command 설정, recovery_target_time 지정까지 실전 순서로 다룹니다.
물리 복제와 달리 테이블 단위로 복제할 수 있는 논리 복제(Logical Replication)의 동작 원리, Publication·Subscription 설정, DDL 복제 제한, 그리고 무중단 버전 업그레이드 활용법을 다룹니다.
WAL 스트림으로 Primary에서 Standby로 변경을 전파하는 스트리밍 복제의 동작 원리, 동기·비동기 복제 설정, replication slot 관리, Hot Standby 읽기 분산 패턴을 정리합니다.
뷰와 달리 결과를 디스크에 물리적으로 저장하는 구체화 뷰의 생성, 갱신(REFRESH) 전략, CONCURRENTLY 옵션, 인덱싱 패턴, 그리고 쿼리 플래너가 구체화 뷰를 활용하는 방법을 다룹니다.
PostgreSQL 10+의 선언적 파티셔닝 PARTITION BY RANGE·LIST·HASH 문법, 자식 파티션 생성, 파티션 프루닝 동작 원리, DETACH PARTITION CONCURRENTLY, 슬라이딩 윈도우 패턴으로 오래된 데이터 관리, 파티션 인덱스·PK 제약 주의사항을 설명합니다.
PostgreSQL FDW(Foreign Data Wrapper)의 아키텍처, FOREIGN DATA WRAPPER·FOREIGN SERVER·USER MAPPING·FOREIGN TABLE 설정 순서, postgres_fdw로 원격 PostgreSQL·MySQL·Oracle·CSV 파일 연결, IMPORT FOREIGN SCHEMA, 조건 푸시다운, 비동기 실행 옵션을 설명합니다.
PostgreSQL 내장 전문 검색의 tsvector 색인 벡터 구조, 토크나이징·불용어·어간 추출 파이프라인, tsquery 연산자(&·|·!·<->), to_tsvector·plainto_tsquery·websearch_to_tsquery, @@ 연산자, GIN 인덱스, ts_rank 랭킹, ts_headline 하이라이트, 한국어 simple 설정을 설명합니다.
PostgreSQL 공간 데이터 확장 PostGIS의 geometry·geography 타입 차이, OGC 지오메트리 타입 계층(POINT·LINESTRING·POLYGON·MULTI*), SRID와 좌표계 개념, ST_DWithin·ST_Contains·ST_Intersects 등 핵심 공간 함수, GiST 인덱스 적용법을 설명합니다.
PostgreSQL의 pg_stat_statements 확장 설치·설정·동작 원리, 주요 컬럼(calls, total_exec_time, mean_exec_time, shared_blks_hit/read), 평균 실행 시간·I/O 핫스팟 기준 상위 쿼리 조회, pg_stat_statements_reset()으로 배포 전후 비교하는 방법을 설명합니다.
PostgreSQL 확장의 .control 파일·SQL 스크립트·공유 라이브러리 구성, CREATE EXTENSION 설치 흐름, pg_extension·pg_depend 카탈로그, ALTER EXTENSION UPDATE 버전 업그레이드, 성능·검색·타입·보안 분야 주요 확장 목록을 설명합니다.
PostgreSQL 함수의 IN·OUT·INOUT·VARIADIC 파라미터 모드, 함수 오버로딩(같은 이름 다른 시그니처), ANYELEMENT·ANYARRAY·ANYRANGE 등 다형성 타입, DEFAULT 값, 네임드 표기법으로 가독성 높이는 법을 실전 예제와 함께 설명합니다.
PostgreSQL 서버 사이드 함수에서 사용할 수 있는 언어 SQL·PL/pgSQL·PL/Python·PL/Perl·PL/Tcl·C를 신뢰도(TRUSTED/UNTRUSTED)·성능·설치 방법·주요 용도 기준으로 비교하고, PL/Python의 SD·GD 캐시와 SPI 쿼리 활용법을 설명합니다.
PostgreSQL 11에서 추가된 PROCEDURE의 함수와의 차이점, CALL 호출 문법, PROCEDURE 내부에서 COMMIT·ROLLBACK으로 분할 커밋하는 대용량 배치 패턴, INOUT 파라미터 활용법을 실전 예제로 설명합니다.
PostgreSQL 트리거의 BEFORE/AFTER/INSTEAD OF 실행 시점, ROW/STATEMENT 레벨 차이, 트리거 함수에서 NEW·OLD·TG_OP 등 특수 변수 사용법, 감사 로그·뷰 갱신 패턴, CONSTRAINT TRIGGER와 무한 루프 방지를 실전 예제로 설명합니다.
PL/pgSQL의 IF/ELSIF/ELSE 조건문, LOOP/WHILE/FOR 루프, EXIT와 CONTINUE로 흐름 제어, EXCEPTION 블록으로 오류 처리, 커서(CURSOR)로 대량 결과 처리, RAISE로 메시지 출력하는 방법을 실전 예제와 함께 설명합니다.
PostgreSQL에서 SQL 함수와 PL/pgSQL 함수를 만드는 방법, 두 언어의 차이점과 선택 기준, 휘발성(VOLATILE/STABLE/IMMUTABLE), 반환 타입(스칼라/SETOF/RETURNS TABLE), 달러 인용($$), STRICT 키워드를 설명합니다.
PostgreSQL CREATE STATISTICS로 다중 컬럼 상관관계(dependencies), n_distinct, MCV(Most Common Values) 통계를 수집하는 방법, 단일 컬럼 통계의 독립 가정 오류, 표현식 확장 통계(PG14+)를 설명합니다.
PostgreSQL 플래너의 동작을 제어하는 enable_* 플래그와 비용 파라미터(random_page_cost, effective_cache_size, work_mem) 튜닝 방법, SSD 환경에서의 권장 설정, work_mem이 정렬과 해시에 미치는 영향을 설명합니다.
PostgreSQL 옵티마이저가 동적 프로그래밍으로 최적 조인 순서를 탐색하는 원리, 테이블 수가 많을 때 GEQO(유전자 알고리즘)로 전환하는 이유와 동작 방식, join_collapse_limit·from_collapse_limit 파라미터 튜닝, pg_hint_plan으로 조인 순서를 수동 제어하는 방법을 설명합니다.
PostgreSQL ANALYZE가 수집하는 MCV, 히스토그램, 상관관계 통계의 구조, pg_stats 뷰로 통계를 직접 조회하는 방법, statistics_target 튜닝, n_distinct 왜곡 문제, autovacuum과 통계 갱신 시점 관리를 설명합니다.
PostgreSQL EXPLAIN ANALYZE 출력에서 cost, actual time, rows, loops, Buffers를 읽는 방법, 추정과 실제의 차이로 통계 문제를 찾는 법, loops 함정, Bitmap vs Index vs Seq Scan 선택 기준, JSON 포맷과 시각화 도구 활용법을 설명합니다.
PostgreSQL Index-Only Scan이 힙 접근을 생략하는 정확한 조건, Visibility Map all-visible 비트의 역할, VACUUM과의 관계, pg_stat_user_indexes로 IOS 효과를 측정하는 방법을 실전 예제와 함께 설명합니다.
PostgreSQL 표현식 인덱스(Functional Index)가 함수 결과를 키로 저장하는 원리, IMMUTABLE 함수만 사용 가능한 이유, lower()·date_part()·JSON 추출 등 실전 패턴, 쓰기 오버헤드와 생성된 컬럼과의 비교를 설명합니다.
PostgreSQL INCLUDE 절로 커버링 인덱스를 만드는 방법, Index-Only Scan이 Visibility Map을 이용해 힙 접근을 최소화하는 원리, VACUUM 주기와의 관계, 그리고 INCLUDE 컬럼 설계 시 고려해야 할 트레이드오프를 설명합니다.
PostgreSQL SP-GiST(Space-Partitioned GiST)의 쿼드트리·kd-트리·트라이 구조와 공간·IP 범위 검색 활용, BRIN(Block Range Index)이 블록 단위 min/max 요약으로 시계열 데이터에서 B-Tree 대비 99% 이상 인덱스 크기를 줄이는 원리, pages_per_range 튜닝 기준을 설명합니다.
PostgreSQL MVCC의 부산물인 Dead 튜플이 쌓이는 원인, VACUUM이 힙 페이지를 스캔하며 공간을 회수하는 과정, autovacuum 트리거 임계값 설정, VACUUM FULL의 위험성과 대안 pg_repack을 설명합니다.
PostgreSQL GiST(Generalized Search Tree)가 R-Tree 기반으로 Bounding Box를 계층적으로 저장하는 원리, Consistent/Union/Penalty/PickSplit 콜백으로 오퍼레이터 클래스를 확장하는 방식, 범위 타입(tsrange), PostGIS 공간 검색, pg_trgm 유사도 검색에서의 활용을 설명합니다.
PostgreSQL MVCC의 핵심인 xmin(삽입 트랜잭션 ID), xmax(삭제/업데이트 트랜잭션 ID), ctid(물리 위치)의 역할, 튜플 버전 체인 구조, 스냅샷 기반 가시성 판단 알고리즘을 실무 쿼리와 함께 설명합니다.
PostgreSQL GIN(Generalized Inverted Index)이 tsvector 전문 검색, 배열 포함 연산, JSONB 키 검색에서 동작하는 역인덱스 원리, Pending List와 fastupdate 옵션, jsonb_ops vs jsonb_path_ops 오퍼레이터 클래스 선택 기준을 설명합니다.
PostgreSQL INHERITS로 테이블 상속 계층을 구성하는 방법, 부모 조회 시 자식 포함 동작, ONLY 키워드, tableoid를 이용한 자식 테이블 구분, 상속과 선언적 파티셔닝의 관계를 설명합니다.
PostgreSQL GENERATED ALWAYS AS IDENTITY와 GENERATED BY DEFAULT AS IDENTITY의 차이, serial 타입의 문제점, 독립 SEQUENCE 활용 패턴, CACHE와 GAP, 마이그레이션 시 setval 활용을 정리합니다.
PostgreSQL Hash 인덱스의 Linear Hashing 확장 메커니즘, Bucket·Overflow 페이지 구조, B-Tree와의 성능 비교, WAL 지원 이력, 그리고 UUID·세션 토큰처럼 긴 문자열 등호 조회에서 Hash 인덱스를 선택하는 기준을 설명합니다.
PostgreSQL B-Tree 인덱스의 Meta Page → Root → Branch → Leaf 계층 구조, 8KB 페이지 레이아웃(PageHeader, ItemId Array, BTPageOpaqueData), 페이지 분할과 fillfactor의 관계, VACUUM이 Dead 인덱스 튜플을 정리하는 방식을 심층 분석합니다.
PostgreSQL CREATE DOMAIN으로 제약을 내재화한 재사용 타입을 만들고, CREATE TYPE으로 ENUM·복합 타입·범위 타입을 정의하는 방법, 실전 이메일·상태 코드·주소 타입 패턴을 설명합니다.
PostgreSQL 범위 타입(int4range, daterange, tstzrange 등)의 경계 기호, @>, <@, &&, *, + 연산자, EXCLUDE USING GIST로 예약 시스템 겹침 방지 제약을 구현하는 실무 패턴을 정리합니다.
PostgreSQL의 SELECT FOR UPDATE / FOR SHARE / SKIP LOCKED / NOWAIT 동작 원리, 행 락 강도 비교, SKIP LOCKED로 구현하는 분산 작업 큐 패턴, 그리고 비관적 잠금이 SSI나 낙관적 락과 어떻게 다른지를 설명합니다.
PostgreSQL JSONB 컬럼에 GIN 인덱스를 적용해 @>, ?, ?| 등 포함 검색을 최적화하는 방법, jsonb_ops vs jsonb_path_ops 비교, 표현식 인덱스 패턴, fastupdate 옵션을 실무 예시로 설명합니다.
PostgreSQL의 테이블 수준 락 8가지, 행 수준 락 4가지, Advisory 락의 특성과 충돌 행렬, pg_locks 뷰와 pg_stat_activity를 조합해 락 대기 체인을 진단하는 방법, lock_timeout과 statement_timeout 설정으로 장기 대기를 방지하는 전략을 정리합니다.
PostgreSQL json과 jsonb의 저장 구조 차이, 경로 탐색 연산자(->, ->>, #>>, ?, @>), jsonb_path_query, jsonb_set 등 JSONB 핵심 기능과 실무 선택 기준을 정리합니다.
PostgreSQL SERIALIZABLE 격리 수준의 핵심인 SSI(Serializable Snapshot Isolation)가 rw-anti-dependency 사이클을 탐지해 Write Skew를 방지하는 원리, SIREAD Predicate Lock의 단위와 승격 규칙, 그리고 실무에서 SSI를 재시도 로직과 함께 적용하는 패턴을 설명합니다.
PostgreSQL이 MVCC 스냅샷(xmin, xmax, xip[])으로 격리 수준을 구현하는 원리, READ COMMITTED와 REPEATABLE READ에서 스냅샷 획득 시점의 차이, 그리고 실제 Non-Repeatable Read와 Phantom Read 발생 여부를 코드와 함께 설명합니다.
PostgreSQL의 배열 타입 선언, 삽입, 연산자(@>, &&), UNNEST로 행 집합 변환, array_agg 역집계, GIN 인덱스 활용까지 배열 데이터 처리 전 과정을 실무 쿼리와 함께 정리합니다.
Autovacuum worker가 언제 가동되는지 결정하는 임계값 계산 공식, scale_factor와 threshold 파라미터 조정 전략, XID Wraparound를 막는 Freeze 메커니즘, 그리고 대형 테이블에서 autovacuum이 너무 늦거나 빈번하지 않도록 튜닝하는 방법을 정리합니다.
PostgreSQL이 제공하는 uuid, inet, 배열, jsonb, range, numeric 등 고유 타입의 분류와 선택 기준을 정리합니다. 올바른 타입 선택이 인덱스 효율과 쿼리 단순화에 미치는 영향을 실무 관점에서 살펴봅니다.
PostgreSQL Checkpointer와 BGWriter 프로세스의 역할 분담, 체크포인트 I/O 폭풍 발생 원인과 checkpoint_completion_target으로 분산하는 방법, pg_stat_bgwriter로 성능을 진단하는 실무 쿼리를 정리합니다.
PostgreSQL WAL(Write-Ahead Log)의 영속성 보장 원리, WAL Buffer에서 pg_wal 디스크까지의 쓰기 흐름, LSN(Log Sequence Number) 구조, synchronous_commit 옵션, WAL을 활용한 PITR 기반 백업·복구 방법을 상세히 설명합니다.
PostgreSQL이 데이터를 저장하는 방식인 Heap 파일 구조, 8KB 페이지 레이아웃, MVCC의 Dead Tuple 발생 원리, 그리고 대용량 값을 자동 처리하는 TOAST 메커니즘(압축·외부화·전략 설정)을 상세히 설명합니다.
PostgreSQL의 Shared Buffers Clock-Sweep 교체 알고리즘, OS Kernel Page Cache와의 이중 버퍼링 문제, work_mem의 노드 단위 할당 방식, effective_cache_size의 역할, 그리고 pg_buffercache 확장으로 버퍼 활용도를 분석하는 방법을 다룹니다.
PostgreSQL의 멀티프로세스 설계를 깊이 파고듭니다. Backend Process의 로컬 메모리(work_mem, maintenance_work_mem, temp_buffers)와 Shared Memory의 역할, 연결당 메모리 비용, 그리고 pg_stat_activity로 프로세스를 모니터링하는 방법을 정리합니다.
PostgreSQL의 전체 아키텍처 — Postmaster 프로세스, Backend 프로세스 포크 모델, Shared Memory 구조, 그리고 Checkpointer·BGWriter·WAL Writer 등 핵심 백그라운드 프로세스의 역할을 정리합니다.