For Engineering

[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] jsonb_set — 문서 전체 덮어쓰기 없이 JSONB 한 필드만 갱신한다

설치·접속: PostgreSQL 설치와 접속부제: 사용자 설정 JSONB에서 notifications.email 플래그 하나만 토글하고 싶은데, 문서 전체를 앱에서 읽고 다시 쓰는(read-modify-write) 방식은 그사이 다른 갱신을 덮어쓸 위험이 있을 때jsonb_set 은 경로를 지정해 문서의 그 부분만 갱신한다. 앱이 JSONB를 통째로 읽어 수정하고 다시 UPDATE하는 대신, UPDATE ... SET prefs = jsonb_set(prefs, '{경로}', 새값) 한 문장으로 끝낸다. 나머지 필드는 DB 안에서 그대로 보존되므로 동시 갱신이 서로를 덮어쓸 창이 좁아진다.CREATE TABLE settings (user_id int primary key, prefs jsonb);INSE..

[PostgreSQL] jsonb_to_recordset — JSON 객체 배열을 관계형 행으로 펼쳐 집계한다

설치·접속: PostgreSQL 설치와 접속부제: 외부 API 응답을 통째로 JSONB에 저장했는데, 그 안의 주문 라인 배열을 ETL이나 임시 테이블 없이 그 자리에서 행으로 펼쳐 GROUP BY 하고 싶을 때jsonb_to_recordset 은 JSON 객체 배열을 타입 지정 SQL 행 집합으로 바꾼다. AS x(col type) 로 스키마를 즉석에서 붙이면, JSONB 안에 갇혀 있던 배열이 일반 테이블처럼 조인·집계 대상이 된다. 컬럼 정의 AS x(...) 는 필수다(빼면 에러).CREATE TABLE api (id int primary key, resp jsonb);INSERT INTO api VALUES (1, '{"order":"o1","lines":[{"cat":"book","qty":2..

[PostgreSQL] jsonpath 필터 — JSONB 내부에서 부등호·범위·정규식으로 검색한다

설치·접속: PostgreSQL 설치와 접속부제: 이벤트 로그 JSONB payload에서 items 중 amount가 100 이상인 항목만 뽑아야 하는데, @> 포함 연산으로는 등호밖에 안 돼서 못 할 때@> 는 "이 값을 포함하느냐"는 등호 매칭만 된다. 부등호·범위·정규식은 SQL/JSON path로 간다. @? 는 경로 조건을 만족하는 요소가 하나라도 있는지 판정하고, jsonb_path_query 는 조건에 맞는 요소만 집합으로 꺼낸다. $min/$max 변수 바인딩까지 된다.CREATE TABLE events (id int primary key, payload jsonb);INSERT INTO events VALUES (1, '{"user":"alice","items":[{"sku":"A1",..

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

부제: AI↔백엔드 계약을 맺을 사람이 없어서 어떤 구조든 받게 만든 이야기시스템은 백엔드(BE)가 채워 보내는 사용자 정보를 받아 대화 컨텍스트에 넣는다.그 정보를 어떤 형태로 주고받을지가 문제였다.그리고 이 결정은 기술이 아니라 조직 현실이 정했다.계약할 상대가 없었다프로젝트를 시작할 때 CTO도, 백엔드 개발자도 없었다.AI 서버와 백엔드 사이에 "무슨 필드를 어떤 스키마로 주고받자"는 계약을 맺을 상대가 없었다는 뜻이다.엄격한 스키마를 정하려면 양쪽이 합의하고, 백엔드가 그에 맞춰 개발해야 한다.그럴 사람이 없으니 그 길은 애초에 막혀 있었다.어떤 구조든 받는다그래서 방향을 뒤집었다."스키마를 맞춘다"가 아니라 "어떤 구조가 와도 추가 개발 없이 받는다"로.최소한의 뼈대만 정했다.사용자 정보를 세..