1. 틀린 숫자

그것은 내가 어렵지 않다고 생각했던 질문 하나에서 시작되었습니다.

나: 특송 배송(express delivery)을 사용하는 주문이 몇 건인가요?

에이전트 (Agent): _(SELECT count(_) FROM orders WHERE details ->> 'express_delivery' = 'true' 실행)*

에이전트 (Agent): 없습니다. 테이블 내에 특송 배송을 사용하는 주문이 없습니다.

나는 그것을 믿었을 것입니다. 바로 그 점이 내가 계속해서 되짚어보게 되는 부분입니다. 의심할 특별한 이유가 없었습니다. 쿼리는 내가 작성했을 법한 쿼리처럼 보였고, "특송 배송을 출시했지만 아무도 사용하지 않는다"는 것은 비즈니스에서 충분히 발생 가능한 사실이기 때문입니다.

내가 확인을 하게 된 이유는 우연히 특송 배송을 사용하는 누군가의 지원 티켓(support ticket)을 보고 있었기 때문입니다.

SELECT count(*) FROM orders
WHERE details #>> '{fulfillment,shippingV2,expressDelivery}' = 'true';
-- 2317

2,317건이었습니다. 즉, 에이전트가 환각(hallucination)을 일으킨 것은 아니었습니다. 에이전트는 실제 데이터베이스에 대해 실제 쿼리를 실행했고 실제 결과를 충실히 보고했습니다. 단지 쿼리가 틀렸을 뿐이었으며, 전체 스택(stack) 중 그 무엇도 이를 알아챌 수 없었습니다.

그것이 내가 파고든 실마리였습니다. 이야기는 이렇게 진행되었습니다.

2. 그 안에 무엇이 들어있는지 아무도 모른다

첫 번째 질문: 에이전트가 어떻게 올바른 경로를 알 수 있었을까요? 그래서 나는 에이전트가 했던 것과 똑같은 질문을 데이터베이스에 던졌습니다.

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'orders';

 column_name | data_type
-------------+-----------
 id          | uuid
...

jsonb. 그게 전부였습니다. 그것이 계약(contract)의 전부였습니다.

해당 컬럼은 모든 행에 {}를 담고 있을 수도 있고, 400개의 서로 다른 경로를 가진 9단계의 fulfillment 트리를 담고 있을 수도 있지만, 카탈로그(catalog)는 진정으로 어느 쪽인지 알려줄 수 없습니다. 왜냐하면 Postgres 타입 시스템(type system) 관점에서 볼 때, jsonb는 단일 스칼라 타입(scalar type)이기 때문입니다. 구조는 타입(type)이 아닌 값(value) 안에 존재합니다. 아무도 카탈로그에 아무것도 알려준 적이 없습니다. 왜냐하면 그것을 기록해 둘 장소가 아예 없었기 때문입니다.

따라서 에이전트(agent)가 할 수 있는 유일한 일은 컬럼 이름(column name)과 주변 대화 내용으로부터 그럴듯한 키(key) 이름을 패턴 매칭(pattern-matching)하는 것뿐이었습니다. 에이전트는 express_delivery라고 추측했습니다. 데이터에는 fulfillment.shippingV2.expressDelivery라고 적혀 있었습니다. 스네이크 케이스(snake case)인지 카멜 케이스(camel case)인지, 평탄한(flat) 구조인지 중첩된(nested) 구조인지, 단수(singular)인지 복수(plural)인지 — 세 가지 독립적인 동전 던지기와 같았으며, 에이전트는 이 세 가지를 모두 맞춰야 했습니다.

하지만 동전 던지기에서 지는 것은 흥미로운 부분이 아닙니다. 진짜 문제는 이것입니다:

SELECT * FROM orders WHERE details ->> 'express_delivery' = 'true';
-- 0 rows

오류(error)도 아니고, 타입 불일치(type mismatch)도 아닙니다. 결과가 0건(Zero rows)입니다.

존재하지 않는 키에 대한 -> 연산은 NULL을 반환합니다. NULL = 'true'는 NULL로 평가됩니다. NULL은 해당 행(row)을 필터링하여 제외합니다. 따라서 잘못된 추측은 아무것도 찾지 못한 올바른 쿼리와 픽셀 단위로 동일한(pixel-identical) 결과 집합을 생성합니다.

저는 이 문제를 한동안 곱씹어 보았습니다. 왜냐하면 이것이 문제 전체를 재구성하기 때문입니다. 에이전트는 거짓말을 하고 있었던 것이 아닙니다. 에이전트에게는 피드백 루프(feedback loop)가 없었습니다. 잘못된 쿼리와 결과가 비어 있는 올바른 쿼리는 내부적으로 구분이 불가능하므로, 반응할 오류도, 스스로 수정할 신호(signal)도 없었습니다. 에이전트는 기상 캐스터와 같은 평온한 자신감을 가지고 자신의 추측을 결과로 보고했으며, 영원히 그렇게 행동했을 것입니다.

그리고 — 불편하게도 — 저 역시 그랬을 것입니다. 저 또한 같은 종류의 추측을 합니다. 저는 단지 이를 보완해 줄 Slack 기록과 Dave라는 동료가 있을 뿐이고, 에이전트에게는 둘 다 없습니다. (Dave는 이 업계의 훌륭한 전통에 따라, 이후 퇴사했습니다.)

2.1 플래너(planner) 또한 볼 수 없습니다

이 과정에서 저는 시스템 내의 그 무엇이라도 해당 컬럼의 형태(shape)를 알고 있는지 궁금해졌습니다. Postgres는 ANALYZE를 실행하므로, 분명 무언가를 수집했을 것 아닙니까?

SELECT attname, n_distinct, most_common_vals IS NOT NULL AS has_mcv
FROM pg_stats WHERE tablename = 'orders' AND attname = 'details';

 attname | n_distinct | has_mcv
---------+------------+---------
 details |      -0.91 | t

통계(Statistics) 말입니다! 하지만 — 이 통계가 무엇에 대한 통계인지 보십시오. Postgres는 이 값들을 **전체 jsonb 블롭 (blobs)**에 대해 계산했습니다. jsonb는 다행히도 동등 연산자(equality operator)와 정렬 순서(sort order)를 가지고 있기 때문에, ANALYZE는 "이 정확한 40KB 문서가 얼마나 자주 나타나는가?"라는 질문에 성실히 답변했습니다. 하지만 이는 그 어떤 인간도 던진 적 없는 질문입니다. 경로별 통계(Per-path statistics)는 없습니다. 그 어디에서도 status가 행의 91%에서 "delivered"이고 2%에서 "refunded"라는 사실을 알지 못했습니다.

이는 제가 몇 달 동안 막연하게 짜증을 느꼈던 이유를 설명해 줍니다:

EXPLAIN SELECT * FROM orders WHERE details @> '{"status":"refunded"}';

 Seq Scan on orders  (cost=0.00..6120.00 rows=620 width=744)
   Filter: (details @> '{"status": "refunded"}'::jsonb)

620,000개 중 620개 행. 정확히 0.1%입니다. 그리고 저는 정말로 _정확히_라고 말하는 것입니다. 이것은 추정치가 아니라 상수(constant)입니다. @> 뒤에 숨겨진 선택도 함수(selectivity function)는 contsel이며, contsel은 매번 조건 없이 DEFAULT_CONTAIN_SEL = 0.001을 반환합니다. selfuncs.c에 명시되어 있으며, 데이터에 의존하는 분기(branch)는 어디에도 없습니다.

따라서 플래너(planner)는 행의 91%가 status = 'delivered'인 경우에도, 전체 테이블에서 단 두 번 나타나는 키에 대해서와 동일한 답변을 내놓습니다. 이러한 허구를 조인(join)에 입력하면, 해시 조인(hash join)이 필요한 곳에 중첩 루프(nested loop)를 선택하게 되고, 여러분의 p99(99번째 백분위수) 지연 시간은 장애 보고서(incident report)를 작성해야 할 수준으로 치솟게 됩니다.

이 시점에서 저에게 문제의 형태가 바뀌었습니다. 이것은 에이전트(agent)의 문제가 아니었습니다. 해당 컬럼을 사용하는 세 명의 별개 소비자 — 저, 쿼리 플래너(query planner), 그리고 LLM — 모두가 정확히 같은 이유로 눈을 가린 채 비행하고 있었으며, 우리 중 누구도 잘못된 기준이 없었기에 아무도 알아차리지 못했습니다. 아무도 스키마(schema)를 문서화하지 않았기에, 모두가 추측할 뿐이었습니다. 우리는 무지의 작은, 조용한 합의를 구축했고, 그것을 2년 동안 운영 환경(production)에서 실행해 온 것입니다.

당연한 조치는 스키마 (schema)를 기록하는 것이었습니다. 몇 개의 행을 샘플링하고, JSON을 순회하며, 모든 키 경로 (key path)와 그 타입, 그리고 각 경로가 얼마나 자주 나타나는지를 나타내는 맵 (map)을 생성하는 것입니다.

이 작업에는 이름이 필요하며, 제가 결정한 이름은 JSON 프로파일링 (JSON profiling) 입니다. 즉, 스키마 온 리드 (schema-on-read) 컬럼에 대한 스키마 추론 (schema inference) 입니다. 기본적으로 이는 만약 jsonb가 감정을 가졌다면, Postgres가 jsonb를 위해 제공했을 \d+와 같습니다.

한나절 정도 걸릴 일처럼 들리겠지만, 독자 여러분, 결코 한나절이 아니었습니다.

3. 스테이징 환경을 거의 날려먹을 뻔한 첫 번째 오후

첫 번째 버전은 20분짜리 작업이었는데, 이것이 저에게 주어야 했던 첫 번째 단서였어야 했습니다. 중요한 일 중 20분 만에 끝나는 일은 없습니다.

SELECT details FROM orders ORDER BY random() LIMIT 1000;

Go 언어에서 언마샬링 (Unmarshal) 하고, map[string]any를 재귀적으로 탐색하며, 경로들을 집합 (set)에 축적합니다. 저는 코드를 작성하여 5,200만 행이 담긴 테이블의 스테이징 복제본 (staging replica)을 대상으로 실행해 두고 커피를 만들러 갔습니다.

돌아왔을 때도 작업은 여전히 실행 중이었습니다. 그것이 제가 이 문제를 올바르게 생각하지 않았다는 첫 번째 힌트였습니다. 세 가지 뚜렷한 잘못된 점이 있었으며, 그중 단 하나만 있었더라도 자격 미달이었을 것입니다.

ORDER BY random()은 트렌치코트를 입은 풀 스캔 (full scan) 입니다. 무작위 정렬을 만족하는 인덱스는 세상 어디에도 없습니다. Postgres는 모든 행을 읽고, 각 행에 무작위 숫자를 할당하며, 5,200만 개를 모두 정렬한 뒤, 상위 1,000개만 전달하고 방금 수행한 작업의 99.998%를 버립니다. 저는 샘플을 요청했지만, 인구 조사 (census) 비용을 지불한 셈이었습니다.

저는 재미로 컬럼 전체의 TOAST를 해제하고 있었습니다. 약 2KB를 초과하는 jsonb 값은 TOAST 관계 (relation)로 밀려나 압축됩니다. 기본값은 pglz이며, PG14 이상에서 default_toast_compression을 설정했다면 lz4를 사용합니다. 컬럼을 읽는다는 것은 TOAST 테이블에서 모든 청크 (chunk)를 가져와 압축을 해제해야 함을 의미합니다. 따라서 40KB 페이로드(payload)를 가진 1,000개의 행에 대해 details를 선택하는 것은 약 40MB의 압축 해제, 이후 네트워크를 통한 약 40MB의 전송, 그리고 제 프로세스 내에서 encoding/json에 의한 약 40MB의 할당을 의미합니다. 이 모든 과정을 거쳐 고작 몇 킬로바이트의 경로 이름들을 얻기 위해서 말입니다.

그 비율이 실제로 저를 멈춰 세웠습니다. 저는 데이터를 코드로 옮기고 있었는데, 데이터가 정답보다 4자릿수(four orders of magnitude)나 더 컸습니다.

그리고 그 탐색(walk) 자체도 가능한 한 가장 느린 방식의 탐색이었습니다. Go의 encoding/json을 사용하여 map[string]any로 변환하는 과정은 노드당 하나의 interface{}를, 객체당 하나의 map을 할당합니다. 깊게 중첩된 주문 문서들을 대상으로 할 때, 언마샬링(unmarshalling)이 다른 모든 과정을 압도하며 프로세스를 불필요한 GC(Garbage Collection) 압박 상태로 몰아넣었습니다.

세 가지 문제와 하나의 근본 원인, 그리고 이를 깨닫고 나니 더 이상 무시할 수 없었습니다: 탐색이 잘못된 장소에서 일어나고 있었다는 점입니다.

4. 탐색을 데이터베이스 내부로 이동하기

제가 jsonb에 대해 잊고 있었던 사실은 이것입니다: 그것은 텍스트가 아닙니다.

그것은 파싱된 바이너리 구조입니다. 헤더, JEntry 오프셋/타입 워드(word) 배열, 그리고 값이 이어지며, 객체 키는 길이에 따라, 그 다음은 바이트 단위로 미리 정렬되어 있어 키 조회(lookup)를 이진 탐색(binary search)으로 수행할 수 있습니다. 헤더 파일을 읽는 것을 즐기신다면 jsonb.h에 이 모든 것이 구현되어 있으며, 저는 그것을 즐깁니다. 여기서 중요한 결과는 Postgres가 아무것도 파싱하지 않고도 해당 구조를 반복(iterate)할 수 있다는 것이며, 이는 트리 탐색(tree walk)에 필요한 정확히 세 가지 기본 요소(primitives)를 노출한다는 점입니다.

이는 전체 순회(traversal)를 WITH RECURSIVE CTE로 수행할 수 있으며, 네트워크를 통해 전송되는 유일한 것은 GROUP BY 집계(aggregate)뿐임을 의미합니다. 문서당 하나의 행이 아니라, *고유한 경로(distinct path)*당 하나의 행만 전송됩니다.

이 포스트 전체를 관통하는 단 하나의 아이디어가 있다면 바로 이것입니다. 비용이 페이로드 크기(기가바이트 단위)의 함수가 아니라, 고유 경로 수(수백 개 단위)의 함수가 된다는 것입니다. 이 섹션 이후의 모든 내용은 이를 정교화하는 과정입니다.

그래서 저는 다음과 같이 작성했습니다:

WITH RECURSIVE walk AS (
    SELECT s.rid, e.key AS path, e.value, 1 AS depth
    FROM sample s, LATERAL jsonb_each(s.details) e
...

그러자 Postgres가 거부했습니다.

ERROR:  recursive reference to query "walk" must not appear more than once

4.1 하나의 자기 참조 (One self-reference)

이것은 Postgres의 특이한 동작이 아니라 실제 제약 사항입니다. SQL 표준은 재귀적 CTE (Recursive CTE) 내부의 재귀 항(recursive term)에서 자기 자신을 정확히 한 번만 참조하도록 허용합니다. 하지만 저는 두 번의 참조가 필요했습니다. 객체 내부로 내려가는 규칙과 배열 내부로 내려가는 규칙이 서로 다르기 때문입니다.

해결책을 찾기 전까지 그 에러 메시지와 씨름하며 저녁 시간을 보냈습니다. walk를 각각 참조하는 두 개의 분기(branch)가 필요한 것이 아닙니다. walk를 참조하는 _하나_의 분기가 필요하며, 이 분기를 두 가지 규칙 중 하나로 자식 노드를 생성하는 서브쿼리와 조인(join)해야 합니다.

WITH RECURSIVE walk AS (
    -- 시드(Seed): 최상위 객체 키
    SELECT s.rid, e.key AS path, e.value AS value, 1 AS depth
...

CROSS JOIN LATERAL을 사용하면 두 가지 하강 규칙을 하나의 관계(relation)로 통합할 수 있습니다. 따라서 walk는 한 번만 나타나면서도 두 경로가 모두 실행됩니다. 이 트릭이 전체 접근 방식을 실행 가능하게 만든 핵심이며, 제가 표준에서 이를 유도해냈다고 말씀드리고 싶지만 사실은 그렇지 않습니다.

4.2 처음에 틀렸던 세 가지 더

쿼리는 컴파일되었습니다. 하지만 정답을 내놓기 전까지 세 번이나 틀린 답을 내놓았습니다.

발생 횟수(Occurrence counts)가 터무니없었습니다. 1,000개의 행 샘플 중 lineItems[].sku가 12,000개의 "문서"에서 나타나는 것으로 표시되었습니다. 당연한 결과였습니다. 배열 내부의 경로는 요소당(per element) 하나의 walk 행을 생성하므로, 12개의 품목(line item)이 있는 주문은 12개의 행을 생성합니다. 빈도(Frequency)는 문서당 기준으로만 의미가 있으므로, count(*)가 아니라 항상 count(DISTINCT rid)를 사용해야 합니다. 지나고 나면 당연한 일입니다. 원래 항상 그렇습니다.

경로 기수성(Path cardinality)이 폭발했습니다. 첫 번째 버전은 배열 인덱스를 유지했기 때문에 lineItems[0].sku와 lineItems[47].sku가 서로 다른 경로로 취급되었습니다. 큰 배열을 가진 테이블은 수천 개의 쓸모없는 고유 항목(distinct entries)을 만들어냈습니다. 모든 인덱스를 []로 축소(collapsing)하면 이 문제가 해결되며, 동시에 좋은 부수적 효과도 얻을 수 있습니다. []는 jsonb_array_elements나 details @? '$.lineItems[*].sku'를 통해 SQL로 바로 매핑되는 표기법입니다.

문서 하나가 메모리를 전부 잡아먹었습니다. 깊이 제한(depth limit)이 없으니, 병적으로 깊게 자기 자신을 중첩하는(deeply-self-nesting) 블롭(blob) 하나가 들어오면 CTE가 메모리 한도에 도달할 때까지 실행되다가 연결(connection)까지 함께 끊어버립니다. depth < 8은 서킷 브레이커(circuit breaker) 역할을 하며, 노드당 배열 팬아웃(array fan-out)이나 컬럼당 총 고유 경로(total distinct paths)에 대해서도 동일한 논리가 적용됩니다. 사용자 제공 데이터에 대해 경계가 없는 재귀(unbounded recursion)를 허용하는 것은 장전된 총을 들고 있는 것과 같습니다.

이 문제들을 해결하자, 다음과 같은 결과가 다시 돌아왔습니다:

                  path                   |   types   | occurrences |   examples
-----------------------------------------+-----------+-------------+---------------
 fulfillment.shippingV2.enabled          | {boolean} |         973 | {true}
...

네 줄의 데이터. 몇 백 바이트. 그리고 §1에서 발생했던 미스터리는 보는 즉시 스스로 해결됩니다. 에이전트가 추측하지 못했던 방식 그대로 경로(path)가 명시되어 있습니다.

그것은 기분 좋은 순간이었습니다. 제가 실행 시간을 측정하기 전까지만 말이죠.

5. 여전히 5,200만 개의 행을 읽는 중

워크(walk)를 서버 측(server-side)으로 옮긴 것은 전송(transfer) 문제를 완전히 해결했습니다. 하지만 ORDER BY random()이 여전히 테이블의 모든 행을 건드리는 문제에 대해서는 정확히 아무런 조치도 취하지 못했습니다.