For Engineering

[PostgreSQL] work_mem와 정렬 spill — 큰 ORDER BY가 디스크로 새는 순간 잡아내기

설치·접속: PostgreSQL 설치와 접속부제: ORDER BY·GROUP BY·DISTINCT가 유독 느린데, EXPLAIN에 "external merge Disk"가 찍혀 있을 때-- 병렬 제거해 정렬 방식만 또렷이 본다SET max_parallel_workers_per_gather = 0;SET work_mem = '4MB'; -- 기본값EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM m5_orders ORDER BY amount;SET work_mem = '256MB'; -- 넉넉히EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM m5_orders ORDER BY amount;-- work_me..

[PostgreSQL] 윈도우 프레임 ROWS/RANGE BETWEEN으로 이동평균·누적합을 낸다

설치·접속: PostgreSQL 설치와 접속부제: 일별 매출에서 "최근 3일 이동평균"과 "그날까지 누적합"을 GROUP BY 없이 각 행 옆에 나란히 붙이고 싶을 때-- ROWS 프레임으로 "현재 행 기준 앞뒤 몇 행"을 집계 범위로 지정한다SELECT day, amount, round(avg(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS ma3, sum(amount) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runningFROM m10_sales ORDER BY day LIMIT ..

[PostgreSQL] FOR UPDATE SKIP LOCKED 여러 워커가 같은 큐에서 안 겹치게 하나씩 집어간다

설치·접속: PostgreSQL 설치와 접속부제: 워커 여러 대가 같은 작업 큐 테이블을 동시에 폴링할 때, 서로 잡은 행은 건너뛰고 각자 다른 행만 집어가게 한다-- 각 워커가 실행: 남이 잠근 행은 건너뛰고 안 잠긴 것만 3개 집는다BEGIN;SELECT id, payload FROM jobsWHERE status = 'pending'ORDER BY idFOR UPDATE SKIP LOCKEDLIMIT 3;-- ... 처리 후 status 갱신 ...COMMIT;=== Worker B (A가 id 1~3을 잡고 있는 중) ===BEGIN id | payload----+--------- 4 | job-4 5 | job-5 6 | job-6(3 rows)=== Worker A got === id |..

[PostgreSQL] SERIALIZABLE 동시 갱신이 서로를 덮어써 lost update가 날 때

설치·접속: PostgreSQL 설치와 접속부제: 두 요청이 잔액을 각각 읽어 +50씩 더하는데, 하나가 다른 하나의 갱신을 덮어써 결과가 틀어지는 상황-- 흔한 read-modify-write 패턴 (앱이 읽은 값으로 다시 쓴다)BEGIN ISOLATION LEVEL READ COMMITTED;SELECT balance FROM m7_acct WHERE id=1; -- 둘 다 100을 읽음-- 앱에서 100+50 계산UPDATE m7_acct SET balance = 150 WHERE id=1; -- 읽은 값 기준으로 덮어씀COMMIT;########## LOST UPDATE (READ COMMITTED) — 둘 다 +50 하려는데 ##########B: 읽은 잔액=100 -> +50..

[PostgreSQL] SELECT FOR UPDATE / NOWAIT / FOR SHARE 행을 잠그고 안전하게 갱신한다

설치·접속: PostgreSQL 설치와 접속부제: 재고 1개를 두 요청이 동시에 빼가지 못하게, 읽는 순간 그 행을 잠그고 나만 갱신하게 하는 상황-- 세션 A: 읽으면서 행을 잠근다. 커밋 전까지 다른 세션의 갱신을 막는다BEGIN;SELECT qty FROM m7_stock WHERE id=1 FOR UPDATE; -- 이 행에 배타 락-- ... 재고 확인 후 ...UPDATE m7_stock SET qty = qty - 1 WHERE id=1;COMMIT;########## A가 FOR UPDATE로 행을 잠근 사이 B가 접근 ########## A: FOR UPDATE로 잠금---------------------- 5--- B: NOWAIT (즉시 실패) -..

[PostgreSQL] 파티셔닝 큰 테이블을 기간별로 쪼개 관리한다

설치·접속: PostgreSQL 설치와 접속부제: 로그·이벤트처럼 계속 쌓이는 테이블을 월 단위로 나눠, 넣을 땐 자동으로 알맞은 조각에 들어가고 조회할 땐 필요한 조각만 스캔하고 싶을 때-- 부모는 껍데기. 실제 데이터는 날짜 범위별 자식 테이블에 저장된다CREATE TABLE m10_events ( id bigint GENERATED ALWAYS AS IDENTITY, occurred_at date NOT NULL, payload text) PARTITION BY RANGE (occurred_at);CREATE TABLE m10_events_2026_01 PARTITION OF m10_events FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');CREAT..

[PostgreSQL] 부분 인덱스(partial index)로 소수 행만 인덱싱한다

설치·접속: PostgreSQL 설치와 접속부제: 주문 50만 건 중 status='pending'은 5%뿐인데, 그 대기 건만 자주 조회할 때-- pending 행만, created_at 기준으로 인덱스를 만든다CREATE INDEX idx_partial_pending ON demo_orders(created_at) WHERE status='pending';SELECT * FROM demo_orders WHERE status='pending' ORDER BY created_at DESC LIMIT 20;-- 인덱스 크기: 전체 status 인덱스 3408kB vs 부분 인덱스 568kB Limit (actual time=0.011..0.024 rows=20 loops=1) Buffers: s..

[PostgreSQL] REFRESH MATERIALIZED VIEW CONCURRENTLY 갱신 중에도 조회를 막지 않는다

설치·접속: PostgreSQL 설치와 접속부제: 대시보드가 계속 읽는 집계 뷰를 새로 굽고 싶은데, 일반 REFRESH는 갱신 내내 조회를 통째로 막아 버려서 서비스 중단 없이 최신화하고 싶을 때-- 전제조건: 행을 유일하게 식별하는 UNIQUE 인덱스가 반드시 있어야 한다CREATE UNIQUE INDEX m10_mv_pk ON m10_mv(id);-- [세션 A] 대용량 뷰를 CONCURRENTLY로 갱신 (약 1.9초 소요)REFRESH MATERIALIZED VIEW CONCURRENTLY m10_big; -- Time: 1889.277 ms-- [세션 B] 세션 A가 한창 갱신하는 중에 조회 → 차단되지 않고 즉답SELECT grp, c FROM m10_big ORDER BY grp LIM..

[PostgreSQL] 머티리얼라이즈드 뷰 무거운 집계를 미리 구워 대시보드에서 즉답한다

설치·접속: PostgreSQL 설치와 접속부제: 대시보드에서 매번 돌리기엔 무거운 상품별 매출 집계를, 결과를 통째로 저장해 두고 빠르게 읽고 싶을 때-- 집계 결과를 물리적으로 저장. 조회는 일반 테이블처럼 즉시 응답한다CREATE MATERIALIZED VIEW product_sales ASSELECT p.id, p.name, count(o.id) AS orders, coalesce(sum(o.qty), 0) AS total_qty, round(coalesce(sum(o.qty * p.price),0),2) AS revenueFROM products pLEFT JOIN orders o ..

[PostgreSQL] 긴 트랜잭션 열어둔 채 방치하면 VACUUM이 죽은 튜플을 못 지운다

설치·접속: PostgreSQL 설치와 접속부제: 애플리케이션이 BEGIN만 해놓고 커밋을 깜빡한 커넥션 하나 때문에, 테이블이 계속 부풀고 autovacuum이 헛도는 상황-- 세션 A: 트랜잭션을 열고 스냅샷만 잡은 채 방치 (idle in transaction)BEGIN;SELECT 1; -- 여기서 스냅샷 획득 -> 커밋 전까지 계속 붙들고 있음-- (커밋을 잊고 커넥션이 살아있음)-- 그 사이 다른 세션이 같은 테이블을 대량 UPDATE -> 죽은 튜플(dead tuple) 발생UPDATE m7_bloat SET v = v + 1; -- 1000행 갱신 = 1000개 dead tupleVACUUM (VERBOSE) m7_bloat;--- 오래된 스냅샷을 붙든 백엔드 (stat..