LG CNS AI Campus KG | JSONB 데이터 타입 처리 및 인덱싱 최적화 | 09.23

About this offer

1. 왜 JSONB가 필요할까? 문서의 메타데이터를 저장한다고 생각해 보자. 메타데이터 는 문서의 제목, 작성자, 회사, 문서 유형처럼 문서를 설명하는 정보다. 문서마다 가지고 있는 정보는 다를 수 있다. 문서 포함된 정보 사업보고서 작성자, 회사, 문서 유형 감사보고서 작성자, 회사, 회계연도, 감사인 반기보고서 작성자, 회사, 태그, 페이지 수 모든 정보를 일반 컬럼으로 만들면 새로운 문서 유형이 들어올 때마다 컬럼을 추가해야 할 수 있다. 이런 변경이 반복되면 다음 문제가 생긴다. 특정 문서에서만 사용하는 컬럼이 많아진다. 해당 정보가 없는 행에는 NULL이 많이 생긴다. 새로운 필드가 생길 때마다 테이블 구조를 변경해야 한다. 운영 중 스키마 변경에 따른 잠금과 관리 부담이 발생할 수 있다. 핵심 문제는 컬럼 수 자체보다, 앞으로 어떤 필드가 생길지 미리 알기 어렵다는 점이다. JSONB는 행마다 필드 구성이 다른 데이터를 PostgreSQL에 저장할 때 유용하다. 모든 데이터를 JSONB에 넣기보다, 공통 핵심 정보는 일반 컬럼에 두고 가변적인 부가 정보는 JSONB에 저장한다. 2. 정형 데이터와 반정형 데이터 구분 특징 예시 정형 데이터 정해진 컬럼과 자료형을 따름 관계형 테이블 반정형 데이터 구조는 있지만 포함되는 필드가 달라질 수 있음 JSON, XML, YAML 비정형 데이터 고정된 행·열이나 키 구조로 표현되지 않음 자유 텍스트, 이미지 JSON은 키와 값의 쌍으로 정보를 표현하는 형식 이다. { "author": "김영수", "company": "삼성", "tags": ["공시", "연간"] } 요소 의미 "author" 키: 정보의 이름 "김영수" 값: 실제 내용 { ... } 객체: 키와 값을 묶은 구조 [ ... ] 배열: 여러 값을 담은 구조 다른 문서에는 tags 가 없고 financial_year 가 있을 수도 있다. 테이블의 컬럼은 그대로 유지하면서, JSONB 컬럼 내부의 키 구성을 행마다 다르게 저장하는 것이다. 유연함에는 대가가 있다 JSONB를 사용하면 필드를 유연하게 저장할 수 있지만, 일반 컬럼처럼 내부 필드마다 자료형을 지정한 것은 아니다. 예를 들어 페이지 수를 숫자로 저장할 수도 있고, 문자열로 저장할 수도 있다. {"pages": 220} {"pages": "220페이지"} 이런 차이는 나중에 숫자 비교나 집계에서 문제가 될 수 있다. 또한 JSONB 내부 필드의 통계를 이용한 조회 건수 예측은 일반 컬럼보다 어려울 수 있다. JSONB는 정형 테이블을 없애는 방법이 아니라, 고정하기 어려운 부가 정보를 함께 담는 방법이다. 3. json과 jsonb의 차이 PostgreSQL에는 json 과 jsonb 라는 두 가지 JSON 저장 타입이 있다. 3-1. json: 입력한 텍스트 보존 입력한 JSON 텍스트의 표현을 유지한다. 내부 값을 처리할 때는 텍스트를 해석하는 과정이 필요하다. 3-2. jsonb: 해석한 구조를 저장 저장할 때 JSON을 해석해 내부 이진 구조로 바꾼다. 이후 조회에서 그 구조를 활용하고, GIN 인덱스도 만들 수 있다. 여기서 파싱(parsing) 은 텍스트를 읽어 키·값·배열 등의 구조를 파악하는 과정이다. 항목 json jsonb 저장 형태 JSON 텍스트 원문 파싱된 내부 이진 구조 입력한 키 순서 보존 보존하지 않음 중복 키 원문에 유지 마지막 값만 유지 저장 시 처리 원문 저장 중심 구조 변환 비용 발생 내부 값 조회 읽을 때 파싱 필요 저장 시 파싱한 구조 활용 JSONB용 GIN 인덱스 직접 적용 불가 적용 가능 원문 보존이 중요하면 JSON, 내부 데이터를 검색하고 활용하려면 JSONB를 고려한다. 3-3. 중복 키 예시 입력 데이터가 다음과 같다고 하자. { "b": 2, "a": 1, "a": 99 } json 은 입력한 표현을 유지하지만, jsonb 에서는 a 의 마지막 값인 99가 남는다. {"a": 99, "b": 2} 따라서 JSONB를 사용할 때는 입력한 키 순서나 중복 키 보존에 의존하지 않는다. 4. JSONB 컬럼에 문서 정보 저장하기 4-1. 테이블 생성 CREATE TABLE documents ( id SERIAL PRIMARY KEY, title TEXT, metadata JSONB ); 컬럼 역할 id 문서를 구별하는 번호 title 문서 제목 metadata 문서마다 다른 부가 정보 4-2. 데이터 입력 INSERT INTO documents (title, metadata) VALUES ('삼성 사업보고서', '{"author":"김영수","company":"삼성","doc_type":"사업보고서","tags":["공시","연간"]}'), ('LG 감사보고서', '{"author":"이미영","company":"LG","doc_type":"감사보고서","financial_year":2025}'), ('현대 반기보고서', '{"author":"박지수","company":"현대","doc_type":"반기보고서","tags":["공시","반기"],"pages":220}'); 문서 모두 metadata 에 저장하지만 내부 키 구성은 다르다. 삼성 문서에는 tags 가 있다. LG 문서에는 financial_year 가 있다. 이 차이 때문에 테이블 컬럼을 추가할 필요는 없다. SQL에서는 바깥쪽 작은따옴표로 JSON 내용을 감싸고, JSON 안의 키와 문자열에는 큰따옴표를 사용한다. 5. JSONB 연산자 JSONB 연산자는 목적에 따라 구분하면 이해하기 쉽다. 목적 연산자 결과 값 꺼내기 -> JSONB 값 꺼내기 ->> TEXT 중첩 경로로 꺼내기 #> JSONB 중첩 경로로 꺼내기 #>> TEXT 지정한 JSON 포함 확인 @> 참·거짓 키 하나 존재 확인 ? 참·거짓 여러 키 중 하나라도 존재 ?| 참·거짓 지정한 키 모두 존재 ?& 참·거짓 5-1. ->: JSONB 형태로 꺼내기 SELECT metadata -> 'company' FROM documents; metadata 에서 company 값을 꺼내되 JSONB 형태를 유지한다. 문자열 값이라면 다음처럼 표현된다. "삼성" ← 큰따옴표 포함 배열의 원소도 꺼낼 수 있다. SELECT metadata -> 'tags' -> 0 FROM documents WHERE id = 1; 다음 순서로 해석한다. metadata 에서 tags 배열을 꺼낸다. 배열의 0번 원소를 꺼낸다. JSON 배열의 첫 원소는 0번이므로 결과는 "공시" 다. 5-2. ->>: TEXT로 꺼내기 SELECT metadata ->> 'company' FROM documents; 같은 값을 꺼내지만 결과 타입은 TEXT다. 삼성 문자열 조건을 비교할 때 사용할 수 있다. SELECT * FROM documents WHERE metadata ->> 'doc_type' = '감사보고서'; ->와 ->>의 차이는 출력 모양만이 아니라 반환 타입의 차이다. 숫자 비교 시 주의 metadata ->> 'pages' JSON 안에서 pages 가 숫자였더라도 ->> 로 꺼낸 결과는 TEXT다. 숫자로 비교하려면 형변환한다. SELECT * FROM documents WHERE (metadata ->> 'pages')::INT > 100; metadata ->> 'pages' : 페이지 수를 텍스트로 추출 ::INT : 정수로 변환 > 100 : 100보다 큰지 비교 단, "220페이지" 처럼 정수로 바꿀 수 없는 값이 들어 있다면 변환에 실패할 수 있으므로 데이터 형식을 일관되게 관리해야 한다. 5-3. #>와 #>>: 중첩 경로 접근 다음처럼 객체 안에 객체가 들어 있을 수 있다. {"info":{"author":{"name":"홍길동","dept":"재무"}}} info → author → name 경로의 값을 JSONB로 꺼내려면: SELECT metadata #> '{info,author,name}' FROM documents; 결과는 "홍길동" 이다. 부서 값을 TEXT로 꺼내려면: SELECT metadata #>> '{info,author,dept}' FROM documents; 결과는 재무 다. 중첩된 키를 따라가는 경로를 {...} 안에 순서대로 적는다. 5-4. @>: 포함 여부 -- "지정한 JSON이 포함되어 있는가" — GIN 인덱스를 탄다 ★ SELECT * FROM documents WHERE metadata @> '{"company":"삼성"}'; -- 삼성 관련 문서만 반환 “metadata에 company가 삼성이라는 내용이 포함된 문서”를 찾는다. metadata 전체가 오른쪽 JSON과 완전히 같아야 하는 것은 아니다. 다른 키가 함께 있어도 조건을 만족할 수 있다. 태그 배열에서 특정 태그 포함 여부도 확인할 수 있다. SELECT * FROM documents WHERE metadata @> '{"tags":["반기"]}'; tags 배열에 "반기" 가 포함된 문서를 찾는다. @>는 GIN 인덱스를 활용할 수 있는 주요 연산자다. 5-5. ?: 키 존재 여부 SELECT * FROM documents WHERE metadata ? 'financial_year'; 회계연도가 어떤 값인지를 비교하는 것이 아니라, financial_year 라는 키가 있는지 확인한다. 여러 키 중 하나라도 있으면: WHERE metadata ?| ARRAY['financial_year', 'pages'] 두 키가 모두 있어야 하면: WHERE metadata ?& ARRAY['author', 'company'] 6. 왜 JSONB 검색에는 GIN이 필요할까? 6-1. B-tree와 JSONB 내부 검색 B-tree는 일반 컬럼의 값이나 특정 표현식의 결과를 정렬해 관리한다. 회사명이 일반 TEXT 컬럼이라면 company = '삼성' 같은 검색에 사용할 수 있다. 하지만 JSONB 한 값 안에는 여러 정보가 함께 들어 있다. { "author": "박지수", "company": "현대", "tags": ["공시", "반기"] } JSONB 전체에 B-tree를 만든다고 해서, 그 안의 각 키·값과 배열 원소가 따로 검색 가능해지는 것은 아니다. JSONB 전체 값의 비교와, 내부에 특정 정보가 포함되는지 찾는 작업은 다르다. 6-2. GIN은 역색인 GIN은 검색할 항목에서 그 항목을 포함하는 행 목록을 찾는 인덱스 다. 일반적인 데이터 읽기 방향은 다음과 같다. 행 포함한 값 1 a, b, c 2 b, d 3 a, c, e 역색인은 방향을 바꾼다. 검색할 값 포함한 행 a 1, 3 b 1, 2 c 1, 3 d 2 e 3 a 를 찾으려고 모든 행을 읽는 대신, 인덱스에서 a 를 찾아 관련 행을 좁힌다. 책 전체를 읽어 단어를 찾는 대신, 책 뒤의 찾아보기에서 해당 단어가 있는 페이지를 확인하는 방식이다. GIN 인덱스 생성 -- 기본 GIN 인덱스: 키, 값, 키+값 쌍을 모두 인덱싱 CREATE INDEX idx_meta_gin # 인덱스 생성 맻 이름 지정 ON documents # 대상 테이블 USING GIN (metadata); # GIN 방식 선택 (인텍싱할 컬럼) 이후 다음 조건에서 인덱스를 활용할 수 있다. WHERE metadata @> '{"company":"삼성"}' 다만 사용할 수 있다는 것과 실제로 선택된다는 것은 다르다. 테이블이 작거나 전체를 읽는 비용이 낮다면 옵티마이저가 Seq Scan을 선택할 수도 있다. 7. GIN이 있어도 ->> 비교는 왜 다를까? 다음 두 조건은 회사가 삼성인 문서를 찾는다는 점에서 비슷하다. WHERE metadata @> '{"company":"삼성"}' WHERE metadata ->> 'company' = '삼성' 하지만 수행하는 연산이 다르다. 조건 처리 방식 대응 인덱스 @> JSON 포함 여부 검사 JSONB GIN ->> ... = ... TEXT를 추출한 뒤 값 비교 해당 추출식의 표현식 인덱스 metadata 전체에 만든 GIN은 ->> 로 꺼낸 텍스트의 등치 비교를 직접 지원하지 않는다. 표현식 인덱스로 해결 CREATE INDEX idx_meta_company ON doc_meta ((metadata ->> 'company')); 표현식 인덱스 는 컬럼 원본이 아니라 계산한 결과에 만드는 인덱스다. 여기서는 다음 계산 결과를 인덱싱한다. metadata ->> 'company' 따라서 아래 조회에 활용할 수 있다. SELECT * FROM doc_meta WHERE metadata ->> 'company' = '삼성'; USING 을 별도로 지정하지 않은 이 인덱스는 기본 B-tree 방식이다. 회사명 추출식에 만든 인덱스이므로, 다른 필드를 검색하는 모든 조건까지 해결해 주는 것은 아니다. 필드별 인덱스가 늘어나면 저장 공간과 관리 비용도 증가한다. 8. 기본 GIN과 jsonb_path_ops GIN에도 어떤 연산을 지원하고 어떻게 인덱싱할지 정하는 연산자 클래스(operator class) 가 있다. 항목 기본 GIN ( jsonb_ops ) jsonb_path_ops 생성 방법 USING GIN (metadata) USING GIN (metadata jsonb_path_ops) 지원 연산자 @> , ? , ?| , ?& @> 만 인덱스 크기 크다 (키·값 쌍 모두) 작다 (경로 해시만) @> 검색 속도 빠름 더 빠름 키 존재 검색 ( ? ) 가능 ❌ 불가 -- 기본 GIN (키 존재 검색도 필요할 때) CREATE INDEX idx_meta_default ON documents USING GIN (metadata); -- jsonb_path_ops (오직 @> 검색만 하고 인덱스를 작게 유지하고 싶을 때) CREATE INDEX idx_meta_path ON documents USING GIN (metadata jsonb_path_ops); 키가 있는지 확인하는 검색까지 필요하다면 기본 GIN을 고려하고, 포함 검색이 중심이라면 jsonb_path_ops를 비교한다. 인덱스 크기 확인 SELECT pg_size_pretty(pg_relation_size('idx_meta_default')) AS default_gin_size, pg_size_pretty(pg_relation_size('idx_meta_path')) AS path_ops_size; -- 결과 예시: default_gin_size 112 kB, path_ops_size 80 kB 함수 역할 pg_relation_size() 저장 크기 확인 pg_size_pretty() 사람이 읽기 쉬운 단위로 표시 9. EXPLAIN으로 인덱스 사용 확인하기 9-1. 어떤 연산자가 GIN을 타는가 -- 인덱스 없이: @> 도 Seq Scan EXPLAIN SELECT * FROM doc_meta WHERE metadata @> '{"company":"삼성"}'; -- GIN(기본, jsonb_ops) 생성 후 CREATE INDEX idx_meta_default ON doc_meta USING GIN (metadata); -- @> 는 인덱스를 탄다 EXPLAIN SELECT * FROM doc_meta WHERE metadata @> '{"company":"삼성"}'; -- 그런데 ->> 비교는 여전히 Seq Scan (GIN이 못 도와줌) EXPLAIN SELECT * FROM doc_meta WHERE metadata ->> 'company' = '삼성'; 9-2. 표현식 인덱스로 ->> 해결하기 -- 표현식 인덱스로 ->> 조건도 인덱스 타게 만들기 CREATE INDEX idx_meta_company ON doc_meta ((metadata ->> 'company')); EXPLAIN SELECT * FROM doc_meta WHERE metadata ->> 'company' = '삼성'; -- → Bitmap Heap Scan + Bitmap Index Scan on idx_meta_company (더 이상 Seq Scan 아님) 실행 계획에서 볼 항목 표시 의미 Seq Scan 테이블을 순차적으로 읽음 Bitmap Index Scan on 인덱스명 해당 인덱스로 후보 위치를 찾음 Bitmap Heap Scan 후보 위치를 바탕으로 실제 테이블 데이터를 읽음 EXPLAIN : 실행 계획 확인 EXPLAIN ANALYZE : 실제로 실행한 결과까지 확인 어떤 연산자를 사용했는지와, 그 연산에 맞는 인덱스가 있는지를 함께 확인한다. 10. 인덱스는 읽기를 빠르게 하지만 비용도 든다 JSONB 한 행에는 여러 키와 값이 포함될 수 있다. GIN은 이 정보를 검색할 수 있도록 여러 인덱스 항목을 관리한다. 따라서 INSERT·UPDATE 시 데이터뿐 아니라 인덱스도 함께 관리해야 한다. 얻는 것 드는 비용 필요한 행을 빠르게 찾을 수 있음 인덱스 저장 공간 전체 테이블을 읽는 작업 감소 가능 삽입·수정 시 갱신 작업 포함·키 존재 검색 지원 인덱스 유지·관리 부담 갱신 비용을 완화하기 위해 pending list 를 쓴다. 변경 내용을 모아 두었다가 나중에 인덱스에 병합하는 방식으로, 비용을 완전히 없애는 것은 아니다. 모든 필드에 인덱스를 만들기보다, 실제로 자주 사용하는 검색 조건에 맞춰 선택해야 한다. 11. 하이브리드 설계: 자주 쓰는 필드는 컬럼으로 처음에는 어떤 정보가 들어올지 몰라 JSONB에 저장했더라도, 시간이 지나면 자주 사용하는 필드가 정해질 수 있다. 예를 들어 대부분의 조회와 집계가 다음을 기준으로 이루어진다고 하자. 회사: company 문서 유형: doc_type 이 경우 해당 값을 일반 컬럼으로 옮겨 관리하는 방식을 고려할 수 있다. 저장 위치 적합한 정보 일반 컬럼 자주 검색·집계·JOIN하는 핵심 정보 JSONB 문서마다 다르거나 변경이 잦은 부가 정보 이렇게 두 방식을 함께 사용하는 것이 하이브리드 설계 다. 컬럼 승격의 진행 순서 company , doc_type 일반 컬럼을 추가한다. 기존 JSONB에서 값을 꺼내 새 컬럼에 채운다. JSONB의 값과 새 컬럼의 값이 일치하는지 확인한다. 일반 컬럼에 B-tree 인덱스를 만든다. JSONB 검색과 일반 컬럼 검색의 실행 계획을 비교한다. 이후 입력되는 데이터도 새 컬럼을 채우도록 INSERT 방식을 변경한다. 기존 데이터를 새 컬럼에 채워 넣는 작업을 백필(backfill) 이라고 한다. 중복 저장도 관리해야 한다 같은 회사명을 일반 컬럼과 JSONB에 모두 남겨두면 같은 정보가 두 곳에 존재한다. 중요한 것은 JSONB를 계속 유지하는 것 자체가 아니라, 사용 패턴에 맞게 필드의 저장 위치를 결정하는 것이다. 12. Python에서 JSONB 데이터를 전달할 때 SQL 안에 JSON 문자열을 직접 작성하는 방식과, Python 딕셔너리를 매개변수로 전달하는 방식은 구분해야 한다. Python 딕셔너리를 psycopg에 그대로 전달하면 변환 방법을 찾지 못하는 오류가 발생할 수 있다. psycopg v3에서는 Jsonb 어댑터를 사용한다. from psycopg.types.json import Jsonb 전달할 딕셔너리를 다음처럼 감싼다. Jsonb(dict_value) 이것은 해당 Python 값을 PostgreSQL의 JSONB로 전달할 수 있도록 변환을 맡기는 것 이다. 13. 검색 목적에 따른 선택 기준 하고 싶은 작업 사용할 방법 고려할 인덱스 JSONB 형태로 값 꺼내기 -> , #> 추출 자체와 검색 조건을 구분 텍스트로 꺼내 값 비교 ->> , #>> 해당 표현식 인덱스 특정 JSON 내용 포함 확인 @> GIN 키 존재 여부 확인 ? , ?| , ?& 기본 GIN 같은 필드로 반복 집계·JOIN 일반 컬럼으로 승격 검토 B-tree JSONB는 유연하게 저장할 수 있게 해 주고, 인덱스는 필요한 내용을 효율적으로 찾게 해 준다. 두 기능을 연결하려면 검색 연산자와 인덱스의 대응 관계 를 이해해야 한다.