For Engineering

[PostgreSQL] 흩어진 데이터를 SQL 하나로 — postgres_fdw + dblink + file_fdw

설치·접속: PostgreSQL 설치와 접속부제: 결제 서비스는 별도 DB, 회원 정보는 우리 DB, 장애 흔적은 서버 CSV 로그에 있는데 "유저별 결제 리포트"를 한 판에 뽑아야 할 때주문/결제는 다른 서버의 PostgreSQL(orders DB)에 있고, 회원(users)은 지금 이 shop DB에 있다. 게다가 "그 시간에 에러가 몇 건 났나"는 서버 로그(CSV) 안에 있다. 데이터를 한 곳으로 ETL 하기 전에, 세 소스를 한 SELECT로 붙여서 먼저 보고 싶다. FDW 계열 세 형제 — postgres_fdw(원격 테이블), dblink(애드혹 원격 SQL), file_fdw(파일) — 를 순서대로 얹는다.CREATE EXTENSION IF NOT EXISTS postgres_fdw;CRE..

[PostgreSQL] SLA 대시보드: percentile_cont(p50/p95/p99) + FILTER + TABLESAMPLE

설치·접속: PostgreSQL 설치와 접속부제: API 응답시간 로그에서 엔드포인트별 p50/p95/p99를 뽑고, 2xx/5xx를 나눠 보고, 로그가 수억 행일 때 표본으로 근사할 때문제 상황응답시간 대시보드에 avg(latency_ms)만 그려 놓고 "평균 35ms, 괜찮네"라고 안심하던 팀이 있다. 그런데 사용자 항의는 계속 들어온다. 평균은 롱테일에 속는다. SLA는 평균이 아니라 p95/p99로 말해야 한다. API 로그 60만 건(엔드포인트 3종, 롱테일 지연 분포)을 스크래치 테이블 api_log(endpoint, status_code, latency_ms)로 만들어 세 기능을 얹는다.1단계 — percentile_cont로 분위수를 한 번에percentile_cont(f) WITHIN G..

[PostgreSQL] 배치 동기화 ETL — COPY FROM PROGRAM + MERGE 다분기 + PROCEDURE 청크 커밋

설치·접속: PostgreSQL 설치와 접속부제: 매일 벤더가 주는 재고 파일을 본 테이블에 반영하는데, 신규는 INSERT·변경은 UPDATE·재고 0은 DELETE를 한 번에 처리하고 대량이면 트랜잭션이 몇 시간 잠길 때매일 벤더 재고 스냅샷을 받아 재고 테이블과 동기화한다. 앱에서 파일을 파싱해 행마다 조회하고 분기 INSERT/UPDATE/DELETE를 돌리던 루프를, 세 가지 SQL 기능으로 갈아엎는다. 파일 적재는 COPY FROM PROGRAM, 다분기 반영은 MERGE, 대량일 때 롱 트랜잭션 회피는 PROCEDURE 청크 커밋이다.1단계 — COPY FROM PROGRAM으로 스테이징 적재 (HEADER MATCH로 컬럼 검증)COPY ... FROM PROGRAM은 서버가 셸 명령을 실..

[PostgreSQL] 월간 매출 리포트 한 방에: ROLLUP + FILTER + GROUPING()

설치·접속: PostgreSQL 설치와 접속부제: 카테고리별·월별 매출에 소계와 총계, 환불액까지 붙인 리포트를 UNION ALL 없이 단일 SELECT로 뽑을 때문제 상황카테고리별 매출, 카테고리+월별 매출, 그리고 전사 총계를 한 표에 뿌려야 한다. 흔한 방식은 GROUP BY가 다른 쿼리를 3~4개 만들어 UNION ALL로 붙이거나, 앱에서 각 레벨을 다시 집계하는 것이다. 여기에 "완료 매출과 환불액을 나란히" 요구가 붙으면 쿼리는 더 늘어난다. 이걸 ROLLUP + FILTER + GROUPING() 조합으로 단일 SELECT에 담는다.검증용 스크래치 테이블은 주문 200건에 카테고리(electronics/apparel/grocery), 판매월(1~4월), 상태(paid/refunded/can..

[PostgreSQL] 검색 결과 하이라이팅 완성 — 검색창 입력을 그대로 받아 관련도순 + 강조 스니펫까지

설치·접속: PostgreSQL 설치와 접속부제: 게시글 검색에서 사용자가 검색창에 "file search" -crab 처럼 입력하는데, 매칭만 되고 관련도순 정렬도 구글식 하이라이트도 없을 때@@ 로 매칭만 하고 끝난 검색을 네 단계로 "검색답게" 완성한다. 사용자 입력 파싱(websearch_to_tsquery) → 매칭(@@) → 관련도 정렬(ts_rank_cd) → 강조 스니펫(ts_headline). 제목 가중치는 setweight 로 미리 넣는다. 한글 형태소 사전 없이도 되도록 english config 로 검증한다.CREATE EXTENSION IF NOT EXISTS unaccent; -- 필요 시(악센트 무시). 아래 예제엔 없어도 됨CREATE TABLE doc (id int pri..

[PostgreSQL] 커버링 인덱스로 힙 안 건드리기 — INCLUDE + index-only scan + 가시성맵/VACUUM + DESC NULLS LAST

설치·접속: PostgreSQL 설치와 접속부제: 상품 목록에서 category로 걸고 name, price만 뽑는데 매번 힙 랜덤 I/O가 병목이라, 조회 컬럼을 인덱스에 실었더니 기대만큼 안 빨라질 때상품 목록 API가 WHERE category=? 로 걸고 name, price 두 컬럼만 반환한다. 인덱스는 category에 걸려 있는데, 인덱스에서 행 위치만 찾고 실제 name, price를 읽으려고 매번 힙(테이블 본체)을 랜덤하게 다시 방문한다. 이 힙 페치가 병목이다. 조회하는 컬럼을 아예 인덱스에 실어서 힙을 건드리지 않게 만들어보자. 그런데 커버링 인덱스를 걸고도 안 빨라지는 함정이 하나 있다.1단계 — INCLUDE로 조회 컬럼을 인덱스에 싣는다검색 키는 category 하나지만, na..

[PostgreSQL] 느린 쿼리 자동 사냥 — pg_stat_statements로 범인 찾고 auto_explain으로 플랜 자동 로깅

설치·접속: PostgreSQL 설치와 접속부제: "DB가 전반적으로 굼뜬데 어떤 쿼리 탓인지 감이 안 잡힐 때" — 워크로드 전체를 랭킹으로 훑어 범인을 지목하고, 그 쿼리가 실제로 느려지는 순간의 실행계획을 로그에서 건져낸다.느린 쿼리를 잡을 때 흔히 EXPLAIN을 떠올리지만, 그건 "이 쿼리 하나"만 본다. 문제는 대개 "어떤 쿼리가 범인인지 모른다"는 데 있다. pg_stat_statements는 서버가 돌린 모든 SQL의 누적 통계를, auto_explain은 임계 시간을 넘긴 쿼리의 실행계획을 자동으로 잡아준다. 둘을 이으면 누가(랭킹) → 왜(플랜) 파이프라인이 완성된다.두 모듈 모두 shared_preload_libraries에 등록하고 서버를 재시작해야 한다(로드 후에는 세션 단위로 a..

[PostgreSQL] 오타·발음·악센트를 견디는 이름 검색 — pg_trgm + unaccent + fuzzystrmatch

설치·접속: PostgreSQL 설치와 접속부제: CS 검색창에서 'Katharyn', 'Jose Garcia', 'Catherine'을 각각 다르게 흔들어 쳐도 같은 사람을 찾아야 할 때CREATE EXTENSION IF NOT EXISTS pg_trgm;CREATE EXTENSION IF NOT EXISTS unaccent;CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;CREATE TABLE names (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL);-- 후보 좁히기용 trigram GIN, 발음 폴백용 daitch_mokotoff GINCREATE INDEX names_trgm_gix ON..

[PostgreSQL] PostGIS 없이 반경 매장 검색 — cube + earthdistance + GiST 표현식 인덱스

설치·접속: PostgreSQL 설치와 접속부제: "내 위치 기준 반경 3km 매장"을 PostGIS 도입 없이 인덱스 스캔으로 조회할 때-- cube + earthdistance 조합. lat/lon 저장 테이블CREATE EXTENSION IF NOT EXISTS cube;CREATE EXTENSION IF NOT EXISTS earthdistance;CREATE TABLE store ( id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, lat double precision NOT NULL, lon double precision NOT NULL);-- 위경도를 3D 큐브 좌표로 바꾸는 ll_to_earth() 표현..

[PostgreSQL] 예약 이중예약 원천봉쇄: range + EXCLUDE + btree_gist + DEFERRABLE + IDENTITY

설치·접속: PostgreSQL 설치와 접속부제: 회의실 예약 API에서 동시 요청이 겹쳐 이중예약이 가끔 뚫릴 때, 애플리케이션 락 없이 DB 제약만으로 막는다회의실 예약에서 두 요청이 거의 동시에 들어오면 이런 코드가 뚫린다.1) SELECT ... WHERE 시간대 겹침 → 0건2) INSERT 예약두 요청이 1번을 동시에 통과한 뒤 각자 2번을 INSERT하면 겹치는 예약 두 건이 들어간다. 애플리케이션 락으로 막을 수도 있지만, 여기서는 DB 제약 하나로 옮긴다. tsrange + EXCLUDE USING GIST부터 시작한다.1단계 — 단일 리소스 겹침 차단 (range + EXCLUDE)시간 구간을 tsrange로 저장하고, "겹치는(&&) 두 행은 공존할 수 없다"를 EXCLUDE USI..