For Engineering

[PostgreSQL] LISTEN/NOTIFY 폴링 없이 DB 이벤트를 실시간으로 받는다

설치·접속: PostgreSQL 설치와 접속부제: "새 주문 들어왔나?"를 1초마다 SELECT로 캐묻는 대신, 변화가 생기는 그 순간 DB가 대기 중인 세션에 직접 알려 주게 하고 싶을 때-- [세션 A] 채널을 구독하고 기다린다. 알림이 오면 즉시 출력된다LISTEN m10_chan;SELECT pg_sleep(4); -- 대기하는 동안 알림 수신-- [세션 B] 같은 채널로 발신 (다른 접속에서)NOTIFY m10_chan, 'order 42 paid';-- 세션 A 화면에 뜨는 결과LISTEN pg_sleep ----------(1 row)Asynchronous notification "m10_chan" with payload "order 42 paid"received from server p..

[PostgreSQL] JSONB @> 컨테인먼트로 JSON 컬럼 조건검색을 인덱스 태우기

설치·접속: PostgreSQL 설치와 접속부제: 상품 속성을 통째로 jsonb 컬럼에 넣어뒀는데, "브랜드가 삼성인 것"처럼 JSON 안쪽 값으로 매번 필터링하니 풀스캔이 걸릴 때-- @> 는 "왼쪽 JSON이 오른쪽 JSON을 통째로 포함하는가"SELECT id, name FROM m4_products WHERE attrs @> '{"brand":"삼성"}';-- 중첩도 그대로: specs 안의 ram 이 16SELECT id, name FROM m4_products WHERE attrs @> '{"specs":{"ram":16}}'; id | name ----+----------- 1 | 갤럭시북4 4 | 버즈3 id | name ----+----------- 1 | 갤럭시북..

[PostgreSQL] jsonb_path_query (JSONPath) 로 중첩 JSON을 조건까지 걸어 질의하기

설치·접속: PostgreSQL 설치와 접속부제: 중첩된 jsonb에서 "specs.ram이 16 초과인 것", "배열 안에 조건 맞는 원소가 있는 것"처럼 화살표(->)로는 지저분해지는 조건부 탐색이 필요할 때-- $.a.b 경로 문법. jsonb_path_query 는 매칭 값을 뽑는다SELECT name, jsonb_path_query(attrs, '$.specs.ram') AS ramFROM m4_products WHERE attrs @> '{"category":"laptop"}';-- ? (@ ...) 는 필터. @ 는 "현재 값". jsonb_path_exists 로 WHERESELECT name FROM m4_products WHERE jsonb_path_exists(attrs, '$.spe..

[PostgreSQL] 조인 알고리즘 3종 — Nested Loop·Hash·Merge를 플래너가 언제 고르나

설치·접속: PostgreSQL 설치와 접속부제: 같은 두 테이블 조인인데 어떤 쿼리는 Nested Loop, 어떤 건 Hash Join으로 잡힐 때 그 판단 기준-- (A) 한쪽이 아주 좁다: user_id 한 명의 주문만EXPLAIN (ANALYZE, COSTS OFF)SELECT o.id, u.name FROM m5_orders o JOIN m5_users u ON u.id=o.user_idWHERE o.user_id = 12345;-- (B) 양쪽 대부분을 맞붙인다: 전체 주문 × 전체 회원EXPLAIN (ANALYZE, COSTS OFF)SELECT count(*) FROM m5_orders o JOIN m5_users u ON u.id=o.user_id;-- (A) Nested Loop (ro..

[PostgreSQL] 격리수준 Read Committed vs Repeatable Read 같은 트랜잭션 안에서 값이 바뀔 때

설치·접속: PostgreSQL 설치와 접속부제: 리포트를 만드느라 한 트랜잭션 안에서 같은 행을 두 번 읽는데, 그 사이 다른 세션이 값을 바꿔 커밋해버리는 상황-- 세션 A: 트랜잭션을 열고, 잠깐 텀을 둔 뒤 같은 행을 두 번 읽는다BEGIN ISOLATION LEVEL READ COMMITTED; -- 기본값SELECT price FROM m7_prod WHERE id=1; -- 첫 읽기-- (이 사이에 세션 B가 UPDATE price=250; COMMIT)SELECT price FROM m7_prod WHERE id=1; -- 둘째 읽기COMMIT;########## READ COMMITTED ########## A: 첫 SELECT (RC)------------------- ..

[PostgreSQL] 인덱스가 있는데 안 타는 이유 — 형변환·함수·OR·낮은 선택도

설치·접속: PostgreSQL 설치와 접속부제: 분명히 인덱스를 만들었는데 EXPLAIN을 보면 Seq Scan이다 — 왜 안 먹히는지 네 가지 전형-- 컬럼에 형변환/함수를 씌우면 인덱스가 죽는다SELECT * FROM demo_users WHERE id::text = '12345'; -- Seq ScanSELECT * FROM demo_users WHERE id = 12345; -- Index Scan-- id::text = '12345' (형변환): 인덱스 무시 Gather -> Parallel Seq Scan on demo_users Filter: ((id)::text = '12345'::text)-- id = 12345 (원본 그대로): 인덱스 사..

[PostgreSQL] CREATE INDEX CONCURRENTLY — 운영 중 테이블에 인덱스를 무중단으로 건다

설치·접속: PostgreSQL 설치와 접속부제: 실서비스 테이블에 인덱스를 걸어야 하는데, 일반 CREATE INDEX가 쓰기를 통째로 막아 장애가 날 때-- (일반) 테이블에 ShareLock → INSERT/UPDATE/DELETE 대기BEGIN;CREATE INDEX idx_lock_demo ON m5_orders(product_id);SELECT mode, granted FROM pg_locks l JOIN pg_class c ON c.oid=l.relation WHERE c.relname='m5_orders' AND locktype='relation';ROLLBACK;-- (무중단) 트랜잭션 밖에서, 약한 잠금으로CREATE INDEX CONCURRENTLY idx_cc_pid ON m5_or..

[PostgreSQL] GIN vs GiST — 전문검색·배열은 GIN, 범위·좌표·최근접은 GiST

설치·접속: PostgreSQL 설치와 접속부제: tsvector·배열·jsonb에 인덱스를 걸려는데 GIN과 GiST 중 뭘 골라야 할지 매번 헷갈릴 때-- GIN: 값을 잘게 쪼개 "어떤 문서에 이 토큰이 있나"를 역색인. 전문검색·배열 포함CREATE INDEX idx_docs_gin ON m5_docs USING gin(doc);SELECT count(*) FROM m5_docs WHERE doc @@ to_tsquery('english','performance & planner');-- GiST: 값을 경계 상자(bounding box)로 요약. 범위 겹침·좌표·최근접()CREATE INDEX idx_pts_gist ON m5_pts USING gist(loc);SELECT id FROM m5_..

[PostgreSQL] 표현식 인덱스(expression index)로 LOWER(email) 검색을 태운다

설치·접속: PostgreSQL 설치와 접속부제: 대소문자 섞인 이메일을 LOWER(email)='...'로 대소문자 무시 조회할 때, 일반 인덱스는 안 먹힌다-- 컬럼이 아니라 "함수 결과"에 인덱스를 만든다CREATE INDEX idx_users_lower_email ON demo_users(LOWER(email));SELECT * FROM demo_users WHERE LOWER(email)='user12345@example.com';-- 일반 인덱스 demo_users(email)는 LOWER()가 씌워지면 못 쓴다: Seq Scan, 1728 buffers, 18.7 ms Bitmap Heap Scan on demo_users (actual time=0.021..0.021 rows=1 loops..

[PostgreSQL] 데드락 두 트랜잭션이 서로의 잠금을 기다리다 하나가 강제 중단될 때

설치·접속: PostgreSQL 설치와 접속부제: 계좌이체 두 건이 각각 다른 순서로 두 행을 잠그다가, 서로가 상대의 락을 기다려 영원히 못 끝나는 상황-- 세션 A: 계좌1 → 계좌2 순서로 잠근다BEGIN;UPDATE m7_acct SET balance=balance-10 WHERE id=1; -- 계좌1 락UPDATE m7_acct SET balance=balance+10 WHERE id=2; -- 계좌2 락 시도 (B가 쥐고 있음)COMMIT;-- 세션 B: 계좌2 → 계좌1 순서로 잠근다 (반대!)BEGIN;UPDATE m7_acct SET balance=balance-10 WHERE id=2; -- 계좌2 락UPDATE m7_acct SET balance=balance+10 WHERE ..