For Engineering

[PostgreSQL] MERGE 심화 — ON CONFLICT로는 안 되는 DELETE 다분기와 cardinality violation

설치·접속: PostgreSQL 설치와 접속부제: 밤마다 들어오는 재고 변동 파일을 반영하는데, 신규는 INSERT·증감 후 양수면 UPDATE·0 이하가 되면 그 행을 DELETE — 세 갈래를 한 문장으로 하고 싶을 때대부분 upsert를 INSERT ... ON CONFLICT로만 안다. 하지만 ON CONFLICT는 "충돌 시 UPDATE"만 되고 DELETE나 "조건별 다른 액션"은 표현할 수 없다. MERGE는 소스 행마다 WHEN 절을 순서대로 평가해 처음 참이 되는 절 하나만 실행한다. 재고가 0 이하로 떨어지면 DELETE, 아니면 UPDATE 같은 다분기가 한 문장에 담긴다.CREATE TABLE wines (winename text PRIMARY KEY, stock int);CREAT..

[PostgreSQL] 월별 파티션 롤링 운영 — 계획/실행 프루닝 + DETACH/DROP + COPY 적재 + ATTACH

설치·접속: PostgreSQL 설치와 접속부제: 로그를 월별 RANGE 파티션으로 나눴는데, 특정 달만 조회할 때 정말 그 파티션만 읽는지, 오래된 달은 어떻게 무중단으로 내리고 새 달은 어떻게 붙이는지로그 테이블 log를 logdate 기준 월별 RANGE 파티션으로 운영한다. 파티션을 나눴다고 자동으로 빨라지는 건 아니다. 조회가 실제로 필요한 파티션만 읽는지(프루닝), 만료된 달을 어떻게 즉시 내리고 새 달을 어떻게 대량 적재해 붙이는지가 "운영"의 핵심이다.CREATE TABLE log ( id bigserial, logdate date NOT NULL, level text, msg text) PARTITION BY RANGE (logdate);CREATE TABLE log_2026m01 PA..

[PostgreSQL] '활성 행 하나만' 제약 모음: 부분 유니크 + 표현식 유니크

설치·접속: PostgreSQL 설치와 접속부제: "사용자당 활성 쿠폰 1개", "대소문자 무시 이메일 중복 금지" 같은 비즈니스 규칙을 앱 검증이 아니라 DB 인덱스로 강제할 때"사용자당 미사용 쿠폰은 하나만", "Foo@x.com과 foo@x.com은 같은 계정"—이런 규칙을 애플리케이션 코드로 검사하면 동시 요청에 뚫린다. 부분 유니크 인덱스와 표현식 유니크 인덱스를 겹쳐 쓰면 이 규칙들을 DB가 원자적으로 막는다.1단계 — 부분 유니크로 "활성 행 하나만" (WHERE status='active')쿠폰 테이블에서 발급 이력(사용완료)은 여러 개 쌓이되, status='active'인 미사용 쿠폰은 사용자당 하나여야 한다. 일반 UNIQUE는 전체 행에 걸려 이력까지 막아버리니, 조건을 만족하는 부..

[PostgreSQL] DB만으로 끝내는 보안 기본기 — pgcrypto로 비밀번호·컬럼 암호화·무결성

설치·접속: PostgreSQL 설치와 접속부제: 앱 프레임워크 없이 굴리는 내부 도구에서 비밀번호 저장·민감 컬럼 암호화·감사 로그 변조 탐지를 SQL만으로 처리할 때내부 관리 툴을 급하게 하나 만들었는데 bcrypt 라이브러리도, 암호화 미들웨어도 없다. 그렇다고 비밀번호를 평문이나 md5()로 저장할 수는 없다. pgcrypto 하나로 세 가지를 DB 안에서 끝낸다: 비밀번호는 bcrypt로, 민감 컬럼은 PGP 대칭키로, 감사 로그는 HMAC로.CREATE EXTENSION IF NOT EXISTS pgcrypto;1단계 — 비밀번호: crypt() + gen_salt('bf')gen_salt('bf', 8)가 bcrypt salt를 만들고 crypt()가 salt를 섞은 adaptive 해시를 ..

[PostgreSQL] 스키마 안 바꾸고 유연한 속성 저장 — hstore vs JSONB vs intarray 태그

설치·접속: PostgreSQL 설치와 접속부제: 상품마다 부가 스펙이 제각각이고 태그도 붙는데, 속성 하나 늘 때마다 ALTER TABLE 하기는 싫을 때"유연한 속성"이라고 무조건 JSONB부터 꺼내는 경우가 많다. 실제로는 데이터 모양에 따라 셋이 갈린다. 평면 key-value는 hstore, 태그 집합은 intarray, 중첩 구조는 JSONB. 상품 테이블 하나에 셋을 다 얹어 놓고 언제 무엇이 이기는지 본다.CREATE EXTENSION IF NOT EXISTS hstore;CREATE EXTENSION IF NOT EXISTS intarray;CREATE TABLE prod ( id serial PRIMARY KEY, name text, specs hstore,..

[PostgreSQL] 재귀 CTE를 대체하는 계층 데이터 3가지 접근 — ltree vs connectby vs WITH RECURSIVE

설치·접속: PostgreSQL 설치와 접속부제: 카테고리 트리에서 "이 노드 아래 전부"를 뽑아야 하는데, 매번 재귀 CTE를 짜기가 지겨울 때같은 카테고리 트리를 세 가지 방식으로 저장·조회해 보고, 조회 편의와 성능이 어떻게 갈리는지 비교한다. 트리는 이렇게 생겼다.Top├─ Electronics│ ├─ Computers│ │ ├─ Laptops│ │ └─ Desktops│ └─ Phones└─ Books ├─ Fiction └─ Tech한 테이블에 adjacency-list용 parent_id와 ltree용 path를 같이 담아, 세 방식을 나란히 돌린다.CREATE EXTENSION IF NOT EXISTS ltree;CREATE EXTENSION IF NOT EXISTS ta..

[PostgreSQL] 재시작 콜드스타트 없애기 — pg_buffercache로 보고 pg_prewarm으로 데운다

설치·접속: PostgreSQL 설치와 접속부제: "서버 재시작이나 failover 직후 첫 트래픽이 유독 느릴 때" — shared_buffers가 텅 비어 첫 쿼리들이 전부 디스크를 친다. 지금 캐시에 뭐가 들었는지 실측하고, 핵심 테이블을 미리 데운다.재시작하면 shared_buffers가 비워진다(콜드스타트). 첫 쿼리들은 캐시 미스로 전부 디스크를 읽어 평소보다 몇 배 느리다. pg_buffercache는 지금 공유 버퍼 안에 무엇이 얼마나 들었는지 실시간으로 들여다보고, pg_prewarm은 특정 릴레이션을 디스크에서 버퍼로 미리 끌어올린다. "추측하지 말고 보고, 보고 나서 데운다"가 흐름이다.CREATE EXTENSION IF NOT EXISTS pg_buffercache;CREATE EX..

[PostgreSQL] 손상 의심 릴레이션 부검 — amcheck로 발견하고 pageinspect + pg_visibility로 규명

설치·접속: PostgreSQL 설치와 접속부제: 인덱스가 논리적으로 깨진 것 같을 때, REINDEX로 무작정 재구축하기 전에 손상을 확진하는 절차데이터 체크섬은 물리적 비트 손상만 잡는다. 인덱스-힙 불일치, 누락 다운링크, VM 비트 어긋남 같은 "논리적" 손상은 체크섬을 통과해도 잘못된 SELECT 결과를 낼 수 있다. amcheck로 손상 여부를 판정하고, pg_visibility로 visibility map 일치를 확인하고, pageinspect로 구조를 눈으로 확인하는 확진 절차를 만든다.문제 상황인덱스가 있는 테이블. index-only scan 결과가 이상하거나 특정 행이 인덱스에서 안 걸리는 것 같아 손상을 의심하는 상황이다.CREATE TABLE chk (id int primary k..

[PostgreSQL] 블로트 정밀 진단 3종 — pgstattuple + pg_freespacemap + pageinspect

설치·접속: PostgreSQL 설치와 접속부제: autovacuum이 도는데도 테이블이 계속 커질 때, VACUUM FULL/REINDEX를 진짜 숫자로 결정하기pg_stat_user_tables.n_dead_tup은 통계 추정치라, 통계가 리셋됐거나 갱신이 안 되면 dead tuple을 0으로 보고한다. 실제로 페이지를 스캔해 정확한 dead_tuple_percent를 재고, 빈 공간이 페이지별로 어떻게 흩어졌는지 보고, 특정 페이지의 튜플 헤더까지 부검하는 3단 진단을 붙여본다.문제 상황대량 INSERT 후 절반을 DELETE하고 나머지 일부를 UPDATE한 테이블. autovacuum을 끈 상태(autovacuum_enabled=false)라 dead tuple이 그대로 쌓여 있다.CREATE T..

[PostgreSQL] 감사 로그 싸게 남기기 — 문장 트리거 + transition table + JSONB + 이벤트 트리거

설치·접속: PostgreSQL 설치와 접속부제: 만 건 배치 UPDATE도 누가 무엇을 바꿨는지 감사 테이블에 남겨야 하는데, 행 트리거를 걸었더니 배치가 기어갈 때계정 잔액을 바꾸는 만 건짜리 배치 UPDATE가 있고, 규정상 변경 전후를 모두 감사 테이블에 남겨야 한다. 흔히 하듯 FOR EACH ROW 트리거를 걸면 트리거 함수가 만 번 발화한다. 여기서는 문장 트리거 한 번으로 벌크 기록하고, no-op은 걸러내고, DDL 변경까지 잡는 감사 파이프라인을 단계별로 얹는다.1단계 — 문장 트리거 + transition table로 만 건을 1회 발화로 기록FOR EACH STATEMENT 트리거는 영향 행 수와 무관하게 문장당 딱 1회 실행된다. REFERENCING OLD TABLE / NEW ..