For Engineering

[PostgreSQL] 확장 통계(CREATE STATISTICS)로 상관 컬럼 추정 오차 잡기

설치·접속: PostgreSQL 설치와 접속부제: city='서울' AND zip='06...'처럼 서로 상관된 두 컬럼으로 걸었더니 플래너가 행 수를 100배 적게 잡아 엉뚱한 플랜을 탈 때-- city와 zip이 1:1로 묶인 100만 행 (zip -> city 함수적 종속)-- CREATE EXTENSION 불필요 (core 기능)EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, SUMMARY OFF)SELECT * FROM geo WHERE city = 'city_42' AND zip = 'zip_42'; QUERY PLAN-----------------------------------------..

[PostgreSQL] 복합 인덱스 컬럼 순서 — (user_id, status)가 status 단독 조회엔 안 먹히는 이유

설치·접속: PostgreSQL 설치와 접속부제: (user_id, status) 복합 인덱스를 걸었는데, WHERE status=... 만 쓰는 쿼리는 여전히 Seq Scan일 때CREATE INDEX idx_m5_us ON m5_orders(user_id, status);-- Q1 선두 컬럼만: 인덱스 OEXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM m5_orders WHERE user_id = 12345;-- Q2 선두+두번째: 인덱스 O (둘 다 조건으로)EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM m5_orders WHERE user_id = 12345 AND status = 'done';-..

[PostgreSQL] BRIN 인덱스로 200만 행 시계열을 24kB로 색인한다

설치·접속: PostgreSQL 설치와 접속부제: 센서 로그처럼 시간순으로 계속 쌓이기만 하는 대용량 테이블에서, 특정 날짜 구간만 뽑을 때-- 시간순 append-only 테이블에 BRINCREATE INDEX idx_events_ts_brin ON demo_events USING brin(ts);SELECT count(*), avg(temperature) FROM demo_events WHERE ts >= '2025-01-10' AND ts -- 인덱스 크기: 같은 컬럼 btree 43MB vs BRIN 24kB (약 1800배 차이) Aggregate (actual time=12.359..12.360 rows=1 loops=1) Buffers: shared hit=5 read=640 -..

[PostgreSQL] pg_advisory_lock 크론·배치가 두 번 동시에 도는 걸 앱 레벨 뮤텍스로 막는다

설치·접속: PostgreSQL 설치와 접속부제: 같은 크론 잡이 서버 두 대에서 동시에 뜨더라도, 한 번에 한 프로세스만 실제로 돌게 하고 싶을 때-- 각 실행이 시작할 때: 락을 못 잡으면 이미 누가 돌고 있다는 뜻 → 조용히 종료SELECT pg_try_advisory_lock(42) AS got_lock; -- 잡으면 t, 이미 잠겼으면 f-- ... 작업 수행 ...SELECT pg_advisory_unlock(42) AS released;=== Cron run B tries the same lock while A holds it === got_lock---------- f(1 row)=== who holds advisory locks right now === locktype | objid ..

[PostgreSQL] FETCH FIRST n ROWS WITH TIES — 동점까지 포함한 Top-N

설치·접속: PostgreSQL 설치와 접속부제: 이벤트 "상위 3명 경품"에서 3위가 동점일 때, 억울하게 잘리는 사람 없이 뽑을 때SELECT player, scoreFROM leaderboardORDER BY score DESCFETCH FIRST 3 ROWS WITH TIES; player | score --------+------- alice | 100 bob | 95 carol | 90 dave | 90 erin | 90(5 rows)3명을 요청했는데 5행이 나왔다. FETCH FIRST 3 ROWS WITH TIES는 상위 3행 + ORDER BY 상 마지막 자리(3위, score=90)와 동점인 행을 모두 추가로 반환한다. carol·dave·eri..

[PostgreSQL] DEFERRABLE INITIALLY DEFERRED — 순환 FK를 커밋 시점에 검증한다

설치·접속: PostgreSQL 설치와 접속부제: 두 행이 서로를 FK로 참조(A↔B)해서 어느 쪽을 먼저 INSERT해도 막힐 때-- 즉시 검사되는 일반 FK: 순환 참조를 못 넣는다CREATE TABLE node_immediate ( id integer PRIMARY KEY, name text, partner_id integer REFERENCES node_immediate(id));BEGIN;INSERT INTO node_immediate (id, name, partner_id) VALUES (1, 'A', 2); -- 2가 아직 없음INSERT INTO node_immediate (id, name, partner_id) VALUES (2, 'B', 1);..

[PostgreSQL] ntile(4) / percent_rank() / cume_dist() — 분위수 버킷으로 나누기

설치·접속: PostgreSQL 설치와 접속부제: 학생 성적(또는 고객 결제액)을 사분위(4등분)로 나눠 "상위 25%"를 라벨링할 때SELECT student_id, score, ntile(4) OVER w AS quartile, round(percent_rank() OVER w::numeric, 3) AS pct_rank, round(cume_dist() OVER w::numeric, 3) AS cume_distFROM scoresWINDOW w AS (ORDER BY score)ORDER BY score; student_id | score | quartile | pct_rank | cume_dist ----..

[PostgreSQL] multirange — 불연속 기간을 한 값으로 다룬다

설치·접속: PostgreSQL 설치와 접속부제: 강사의 주간 가용 시간이 오전·오후로 쪼개져 있는데, 예약 요청이 그 안에 통째로 들어가는지 한 번에 판정할 때-- 겹치지 않는 여러 range 를 하나의 값으로: int4multirangeSELECT '{[9,12), [14,18)}'::int4multirange AS availability;-- 예약 구간이 가용시간 안에 완전히 들어가는가 (@>)SELECT '{[9,12), [14,18)}'::int4multirange @> '[10,11)'::int4range AS ok_morning, '{[9,12), [14,18)}'::int4multirange @> '[11,15)'::int4range AS spans_gap, '{[9,..

[PostgreSQL] range 반열림 구간 [) — 인접 기간이 안 겹치는 이유

설치·접속: PostgreSQL 설치와 접속부제: 구독 기간을 [시작, 종료) 로 저장했더니 종료일과 다음 구독 시작일이 딱 붙어도 겹침 판정이 안 나야 할 때-- range 는 무조건 반열림 [) 표준형으로 정규화된다SELECT '[3,7]'::int4range AS closed, -- 7 포함 -> [3,8) '[3,7)'::int4range AS half, -- [3,7) '(3,7)'::int4range AS open; -- 3 제외 -> [4,7)-- 인접 구간은 겹치지 않는다 (경계값 공유해도)SELECT int4range(10,20) && int4range(20,30) AS overlap, -- 겹침? int4range(10,20) -..

[PostgreSQL] "배열은 집합이 아니다" — 언제 배열, 언제 조인 테이블

설치·접속: PostgreSQL 설치와 접속부제: 게시글 태그를 tags int[] 로 넣을지 article_tag 조인 테이블로 뺄지, 공식 문서 기준으로 판단할 때공식 배열 문서에는 이런 경고가 박혀 있다.Arrays are not sets; searching for specific array elements can be a sign of database misdesign.(배열은 집합이 아니다. 특정 원소를 검색하는 일이 잦다면 설계가 잘못됐다는 신호일 수 있다.)즉 원소 검색이 잦으면 정규화(별도 행 테이블)를 권한다. 배열은 "한 행에 딸린 순서 있는 값 묶음"을 통째로 다룰 때 좋고, "이 원소를 가진 행 찾기"가 핵심 질의라면 관계형으로 펼치는 게 정석이다. 다만 실측해 보면 배열 + 인덱스..