For Engineering

[PostgreSQL] array_position 으로 상태값을 커스텀 순서로 정렬한다

설치·접속: PostgreSQL 설치와 접속부제: 티켓 상태를 '접수 → 처리중 → 완료' 순으로 보여줘야 하는데 ORDER BY status 는 사전순으로 흩어질 때CREATE TABLE ticket (id int, title text, status text);-- '접수' '처리중' '완료' 가 섞여 들어있다-- 사전순(원치 않음): '완료'가 맨 앞SELECT id, status FROM ticket ORDER BY status, id;-- array_position 으로 커스텀 순서: 배열에서의 위치를 정렬 키로SELECT id, status, array_position(ARRAY['접수','처리중','완료'], status) AS ordFROM ticketORDER BY array_p..

[PostgreSQL] 배열 검색 = ANY / && / @> 와 GIN 인덱스

설치·접속: PostgreSQL 설치와 접속부제: 게시글 tags text[] 컬럼에서 '리눅스'나 'DB' 태그가 붙은 글을 조인 테이블 없이 빠르게 찾을 때CREATE TABLE post (id int PRIMARY KEY, title text, tags text[]);-- ... 5000행 적재, id ARRAY['리눅스','DB'];-- 단일 원소: '리눅스'가 배열 안에 있나SELECT count(*) FROM post WHERE '리눅스' = ANY(tags);EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)SELECT id FROM post WHERE tags && ARRAY['리눅스','DB'];-- && (겹침) 결과 count -------..

[PostgreSQL] LATERAL 조인 — 행마다 상관 서브쿼리를 조인처럼 펼친다

설치·접속: PostgreSQL 설치와 접속부제: "상품마다 최근 주문 2건", "카테고리별 최신 N개"처럼 바깥 행 값을 참조하는 Top-N을 조인 한 번으로 뽑을 때-- 상품마다, 그 상품의 최근 주문 2건씩 (per-row Top-N)SELECT p.id, p.name, o.ord_id, o.qtyFROM products pCROSS JOIN LATERAL ( SELECT id AS ord_id, qty FROM orders WHERE product_id = p.id -- ← 바깥 행 p를 참조 ORDER BY id DESC LIMIT 2) oWHERE p.id id | name | ord_id | qty----+-------+--------+----- 1 ..

[PostgreSQL] 집계 안의 ORDER BY — string_agg/array_agg 결과 순서를 고정한다

설치·접속: PostgreSQL 설치와 접속부제: 주문별 상품명을 콤마 리스트로 뽑는 리포트가 실행할 때마다 순서가 바뀌어 diff가 지저분할 때-- 정렬 없는 string_agg: 입력 순서에 좌우돼 재현이 보장되지 않는다SELECT order_id, string_agg(product, ', ') AS itemsFROM lines GROUP BY order_id ORDER BY order_id; order_id | items----------+--------------------------------- 1 | Keyboard, Mouse, Monitor, Cable 2 | Desk, Chair(2 rows)-- 집계 함수 괄호 안에 ORDER BY를 넣어..

[PostgreSQL] 윈도우 last_value 프레임 함정 — "파티션 최종값"이 자기 값으로 나올 때

설치·접속: PostgreSQL 설치와 접속부제: 부서별 최고 급여를 last_value로 뽑았더니 매 행이 자기 급여를 반환할 때-- 부서별 급여를 오름차순 정렬하고, 그 파티션의 "마지막(=최고) 급여"를 붙이려는 의도SELECT dept, emp, salary, last_value(salary) OVER (PARTITION BY dept ORDER BY salary) AS wrong_lastFROM sal ORDER BY dept, salary; dept | emp | salary | wrong_last-------+-----+--------+------------ dev | Fay | 4800 | 4800 dev | Dan | 5500 | 5500 ..

[PostgreSQL] PREPARE 제네릭 플랜 함정 — 배포 직후엔 빠르다가 갑자기 느려질 때

설치·접속: PostgreSQL 설치와 접속부제: status='active'가 99%인 편향 컬럼을 ORM prepared 쿼리로 조회하는데, 특정 값에서만 플랜이 최악으로 굳을 때-- status: 99.9%가 'active', 0.1%만 'pending' (심하게 편향된 컬럼)PREPARE p1(text) AS SELECT count(*) FROM skew WHERE status = $1;-- 커스텀 플랜(force): 값을 보고 그 값에 최적인 플랜을 매번 새로 짠다SET plan_cache_mode = 'force_custom_plan';EXPLAIN (COSTS OFF) EXECUTE p1('pending'); -- 희귀값 → 인덱스EXPLAIN (COSTS OFF) EXECUTE p1('..

[PostgreSQL] 함수 volatility — 표현식 인덱스가 안 만들어지거나 안 타는 이유

설치·접속: PostgreSQL 설치와 접속부제: lower(email) 대신 커스텀 정규화 함수로 표현식 인덱스를 걸려는데 인덱스가 안 생기거나 안 탈 때-- plpgsql 함수는 기본 VOLATILE. 이 상태로 표현식 인덱스를 걸면?CREATE FUNCTION norm(t text) RETURNS text LANGUAGE plpgsql AS $$ BEGIN RETURN lower(trim(t)); END $$;CREATE INDEX idx_norm ON people (norm(email));ERROR: functions in index expression must be marked IMMUTABLE-- 실제로는 입력이 같으면 항상 같은 결과 → IMMUTABLE로 재선언하면 인덱스가 산다CREA..

[PostgreSQL] WITH RECURSIVE 심화 — 조직도/그래프 순회와 CYCLE 절로 순환 탐지

설치·접속: PostgreSQL 설치와 접속부제: 조직도를 재귀로 훑는데 데이터에 사이클이 섞여 무한 루프가 나고, UNION과 UNION ALL 중 뭘 써야 할지 헷갈릴 때재귀 CTE는 앵커(시작 행) + 재귀 항(자기 자신을 참조)으로 트리·그래프를 훑는다. 조직도부터 시작한다.CREATE TABLE org ( id int PRIMARY KEY, boss int REFERENCES org(id), name text);INSERT INTO org(id, boss, name) VALUES (1, NULL, 'CEO'), (2, 1, 'VP Eng'), (3, 1, 'VP Sales'), (4, 2, 'Eng Manager'), (5, 4, 'Backend Dev'), (6, 4, ..

[PostgreSQL] tablefunc crosstab — (연,월,수량) 행을 연×월 피벗 리포트로

설치·접속: PostgreSQL 설치와 접속부제: 월별 매출 로우를 "연도 × 1~12월" 표로 뽑아야 하는데, CASE WHEN을 12개 쓰기는 지겨울 때정규화된 (yr, mon, qty) 행 데이터를 연도별 12개월 열로 돌려야 하는 리포트. crosstab은 이 행→열 피벗을 함수 한 번으로 처리한다.CREATE EXTENSION IF NOT EXISTS tablefunc;CREATE TABLE sales (yr int, mon int, qty int);INSERT INTO sales(yr, mon, qty) VALUES (2024,1,120),(2024,2,90),(2024,3,140),(2024,5,60),(2024,11,200),(2024,12,240), (2025,1,150),(2025,2,..

[PostgreSQL] UUID v4 vs v5 — 같은 입력에 같은 UUID를 재현한다

설치·접속: PostgreSQL 설치와 접속부제: 외부 시스템의 (namespace, 이름)을 UUID로 매핑하는데, 재실행해도 중복 없이 동일한 ID가 나와야 할 때UUID 하면 보통 랜덤(v4)만 떠올린다. 코어의 gen_random_uuid()가 호출마다 다른 값을 준다.SELECT gen_random_uuid() AS v4_a, gen_random_uuid() AS v4_b; v4_a | v4_b--------------------------------------+-------------------------------------- 635ed2ec-6eb8-4b37-9b73-2371f7bfeede | 6d1453..