PostgreSQL Reference

PostgreSQL 데이터베이스 레퍼런스

27개 결과

PostgreSQL Reference 소개

PostgreSQL 레퍼런스는 PostgreSQL을 사용하는 데이터베이스 개발자와 관리자를 위한 실용적인 치트 시트입니다. 핵심 DML 쿼리(JOIN과 CTE를 사용한 SELECT, RETURNING이 포함된 INSERT, UPDATE, DELETE), 인덱스 관리(B-tree, JSONB용 GIN, 공간 데이터용 GiST, 유니크 인덱스, EXPLAIN ANALYZE), PL/pgSQL 함수(CREATE FUNCTION, COALESCE, string_agg, generate_series, jsonb_build_object), 트리거 생성 및 관리(NEW/OLD 행 접근을 포함한 BEFORE/AFTER 트리거), 논리적 복제(퍼블리케이션과 구독), 데이터베이스 설정(SHOW, ALTER SYSTEM, VACUUM ANALYZE, pg_stat_activity) 등 6가지 주요 영역을 다룹니다.

이 레퍼런스는 복잡한 쿼리를 작성하는 백엔드 개발자, PostgreSQL 성능을 튜닝하는 DBA, 고가용성 복제를 설정하는 엔지니어에게 특히 유용합니다. JSONB 지원, 다양한 인덱스 타입, 네이티브 논리적 복제 등 PostgreSQL의 풍부한 기능 세트는 전체 문서를 열지 않고도 GIN 인덱스나 PL/pgSQL 함수 본문의 정확한 구문을 기억해야 할 때 간결한 레퍼런스를 특히 가치 있게 만듭니다.

각 항목에는 바로 복사해 사용할 수 있는 완전한 SQL 예제가 포함되어 있습니다. 레퍼런스는 테이블 구축 및 인덱싱, 저장 프로시저 작성, 이벤트 기반 트리거 설정, 스트리밍 복제 구성, pg_stat_activity와 pg_stat_replication 같은 시스템 카탈로그 뷰로 활성 연결 및 느린 쿼리 모니터링 등 실제 관리 작업에 맞게 구성되어 있습니다.

주요 기능

핵심 DML: JOIN과 CTE(WITH 절)를 사용한 SELECT, INSERT RETURNING, UPDATE, DELETE
B-tree, GIN(JSONB/배열), GiST(공간/범위), 유니크 인덱스 생성
쿼리 실행 계획 분석 및 인덱스 사용 검증을 위한 EXPLAIN ANALYZE
달러 인용 및 반환 타입을 사용한 PL/pgSQL CREATE FUNCTION
내장 함수: COALESCE, string_agg, generate_series, jsonb_build_object
NEW/OLD 행 참조와 DROP TRIGGER를 포함한 BEFORE/AFTER 트리거 생성
논리적 복제: CREATE PUBLICATION, CREATE SUBSCRIPTION, pg_stat_replication
설정: SHOW, ALTER SYSTEM SET, VACUUM ANALYZE, pg_stat_activity 모니터링

자주 묻는 질문

CTE(공통 테이블 표현식)란 무엇이며 언제 사용해야 하나요?

`WITH ... AS (...)`구문으로 작성하는 CTE는 메인 쿼리에서 참조할 수 있는 명명된 임시 결과 집합입니다. 복잡한 쿼리를 읽기 쉬운 단계로 분리하거나, 서브쿼리 결과를 여러 번 재사용하거나, 재귀 쿼리에 사용하세요. PostgreSQL 12+에서 CTE는 기본적으로 인라인 처리되지만(옵티마이저가 조건을 내부로 밀어 넣을 수 있음), `WITH ... AS MATERIALIZED (...)`를 사용해 강제로 구체화할 수 있습니다.

PostgreSQL에서 GIN과 GiST 인덱스의 차이점은?

GIN(Generalized Inverted Index)은 JSONB 문서, 배열, 전문 검색 벡터처럼 여러 컴포넌트를 포함하는 값에 최적화되어 있습니다. 요소 포함 여부를 확인하는 쿼리(`@>`, `?`, `@@`)에 빠릅니다. GiST(Generalized Search Tree)는 PostGIS 지오메트리 같은 공간 데이터 타입, 범위 타입, 최근접 이웃 검색에 더 적합합니다. GIN 인덱스는 쿼리 속도가 빠르지만 GiST보다 빌드 및 업데이트가 느립니다.

PostgreSQL에서 삽입된 행의 ID를 얻으려면 RETURNING을 어떻게 사용하나요?

INSERT, UPDATE, DELETE 문의 끝에 `RETURNING id`(또는 `RETURNING *`)를 추가합니다: `INSERT INTO users (name, email) VALUES ('홍길동', 'hong@example.com') RETURNING id;`. 이는 표준 SQL의 PostgreSQL 확장으로, 자동 생성된 기본 키 값을 가져오기 위해 별도의 SELECT 쿼리가 필요 없습니다.

타임스탬프 컬럼을 자동으로 업데이트하는 트리거를 어떻게 만드나요?

먼저 TRIGGER를 반환하고 `NEW.updated_at = NOW()`를 설정하는 PL/pgSQL 함수를 만듭니다. 그런 다음 `CREATE TRIGGER update_timestamp BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_modified_column()`으로 트리거를 생성합니다. `NEW` 키워드는 삽입되거나 업데이트되는 행을 참조하고, `OLD`는 UPDATE와 DELETE 트리거에서 이전 행 상태를 참조합니다.

논리적 복제와 스트리밍 복제의 차이점은?

스트리밍 복제(물리적)는 PostgreSQL 클러스터 전체를 바이트 단위로 복사하며 복제본이 동일한 PostgreSQL 메이저 버전에 있어야 합니다. 논리적 복제(CREATE PUBLICATION/SUBSCRIPTION)는 개별 테이블을 복제하며 다른 PostgreSQL 버전이나 외부 시스템 간에도 복제할 수 있습니다. 논리적 복제는 선택적 테이블 복제도 가능하여 무중단 메이저 버전 업그레이드에 유용합니다.

PostgreSQL에서 느리거나 차단하는 쿼리를 찾아 중단하는 방법은?

`SELECT pid, query, state, wait_event_type FROM pg_stat_activity WHERE state = 'active';`로 실행 중인 쿼리를 확인합니다. 블로킹 락은 `pg_locks`와 조인하여 확인합니다. 쿼리를 정상 종료하려면 `SELECT pg_cancel_backend(pid)`(SIGINT 전송)를 사용하고, 강제 종료하려면 `SELECT pg_terminate_backend(pid)`(SIGTERM 전송)를 사용합니다. 쿼리가 정리할 수 있도록 terminate보다 cancel을 선호하세요.

VACUUM ANALYZE는 무엇을 하며 언제 실행해야 하나요?

VACUUM은 UPDATE와 DELETE 연산으로 남겨진 죽은 행 버전을 제거하여 저장 공간을 회수합니다. ANALYZE는 쿼리 플래너가 최적의 실행 계획을 선택하는 데 사용하는 통계를 업데이트합니다. `VACUUM ANALYZE 테이블명`은 두 작업을 모두 수행합니다. PostgreSQL은 백그라운드에서 autovacuum을 자동으로 실행하지만, 대량 데이터 로드 후나 성능이 중요한 작업 전에 수동으로 실행할 수 있습니다. `VACUUM FULL`은 더 적극적이지만 테이블을 독점 잠금합니다.

재시작 없이 PostgreSQL 설정을 변경하는 방법은?

`ALTER SYSTEM SET shared_buffers = '4GB';`를 사용해 설정을 `postgresql.auto.conf`에 씁니다. 그런 다음 `SELECT pg_reload_conf();`를 호출해 재시작이 필요 없는 설정(`work_mem`, `log_min_duration_statement` 등)을 적용합니다. 재시작이 필요한 설정(`shared_buffers`, `max_connections`)은 다음 PostgreSQL 재시작 시 적용됩니다. `SHOW 설정명`으로 현재 활성 값을 확인하세요.