DB — Database Notes SHELL / SQL

실무에서 자주 쓰는 데이터베이스 패턴 모음 — Postgres, Redis 등. 새 주제는 아래에 계속 추가.

EN: A growing collection of practical database patterns — Postgres, Redis, and more. New topics get appended below.

1. Postgres Upsert — 멱등 인덱싱 SQL

온체인 이벤트 인덱서를 위한 멱등(idempotent) 적재 — INSERT ... ON CONFLICT

왜 필요한가 — 멱등성(idempotency)

블록체인 인덱서는 같은 이벤트를 두 번 이상 읽는 일이 정상입니다. (1) reorg로 블록이 갈렸다 재구성되면 같은 로그를 다시 받고, (2) 크래시 후 마지막 처리 블록부터 재시작하면 겹치는 구간을 다시 훑고, (3) RPC/큐가 보통 at-least-once(중복 가능) 전달이기 때문입니다. ON CONFLICT ... DO UPDATE(UPSERT)가 "같은 입력을 몇 번 넣어도 결과가 같다"를 DB 레벨에서 보장합니다.

EN: An indexer normally reads the same event more than once — reorgs replay logs, restarts re-scan overlapping ranges, and RPC/queues are usually at-least-once. ON CONFLICT ... DO UPDATE (UPSERT) guarantees idempotency at the DB level.

스키마 + UPSERT

CREATE TABLE bets (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tx_hash   text NOT NULL,
  log_index int  NOT NULL,
  market_id int,
  amount    numeric,
  side      text,
  UNIQUE (tx_hash, log_index)        -- ★ 충돌 타깃 = 멱등성의 닻 (먼저 있어야 함)
);

INSERT INTO bets (tx_hash, log_index, market_id, amount, side)
VALUES ('0xAA', 0, 7, 100, 'YES')
ON CONFLICT (tx_hash, log_index) DO UPDATE
  SET amount = EXCLUDED.amount, side = EXCLUDED.side;

괄호 (tx_hash, log_index)는 충돌 타깃. 그 조합이 이미 있으면 INSERT를 버리고 DO UPDATE로 전환하며, 들어오려던 새 값은 EXCLUDED 가상 테이블로 접근합니다. 가장 흔한 실수: 대응하는 UNIQUE 제약이 없으면 ON CONFLICT가 에러.

EN: The parentheses are the conflict target; on collision it switches to DO UPDATE with the incoming row in EXCLUDED. Common mistake: without a matching UNIQUE constraint, ON CONFLICT errors out.

Docker로 한 번에 실행 + 검증

# 1) 일회용 Postgres 컨테이너
docker run --rm -e POSTGRES_PASSWORD=pw -p 5432:5432 -d --name pgtest postgres:16
sleep 5
# 2) 스크립트 실행 (psql 설치 불필요)
docker exec -i pgtest psql -U postgres < examples/pg-upsert-demo.sql
# 3) 행 확인
docker exec -i pgtest psql -U postgres -c \
  "SELECT tx_hash, log_index, amount, side FROM bets ORDER BY id;"
# 4) 정리
docker rm -f pgtest

결과

총 2행 ✅ 멱등(중복 0). 대조군: UNIQUE/ON CONFLICT 없이 INSERT 두 번 → 2행 중복 ❌. DO NOTHING은 로그가 불변일 때 더 싸고 흔함.

2. Redis 분산 락 — 중복 잡 방지 Redis

여러 워커가 같은 정산을 동시에 돌리지 않게 — SET key val NX PX

NX(없을 때만) + PX(만료시간) 덕분에 단 하나의 워커만 락을 잡고, 워커가 죽어도 TTL이 지나면 자동 해제됩니다. 해제는 반드시 내 토큰일 때만 삭제(Lua)해야 만료 후 남의 락을 지우는 사고를 막습니다.

EN: NX (only if absent) + PX (TTL) means exactly one worker grabs the lock, and it auto-releases on TTL if a worker dies. Release only when the token is yours (via Lua), or you'll delete someone else's lock after expiry.

# 획득
redis-cli SET lock:settle:42 workerA NX PX 30000   # OK
redis-cli SET lock:settle:42 workerB NX PX 30000   # (nil) — 다른 워커 실패

# 안전 해제: 내 토큰일 때만 DEL (Lua)
redis-cli EVAL "if redis.call('get',KEYS[1])==ARGV[1] \
  then return redis.call('del',KEYS[1]) else return 0 end" 1 lock:settle:42 workerA

// Node (ioredis) — 워커 잡
const token = `${workerId}-${crypto.randomUUID()}`;
const ok = await redis.set(`lock:settle:${marketId}`, token, "NX", "PX", 30000);
if (!ok) return;                    // 다른 워커가 점유 중
try { await settle(marketId); }
finally {
  await redis.eval(
    "if redis.call('get',KEYS[1])==ARGV[1] then return redis.call('del',KEYS[1]) else return 0 end",
    1, `lock:settle:${marketId}`, token);
}

← Back to Knowledge Base