# SQLite JSON을 전체 인덱스 속도로 활용하는 방법: 생성된 컬럼을 이용한 고속 쿼리 > Clean Markdown view of GeekNews topic #25041. Use the original source for factual precision when an external source URL is present. ## Metadata - GeekNews HTML: [https://news.hada.io/topic?id=25041](https://news.hada.io/topic?id=25041) - GeekNews Markdown: [https://news.hada.io/topic/25041.md](https://news.hada.io/topic/25041.md) - Type: GN+ - Author: [neo](https://news.hada.io/@neo) - Published: 2025-12-13T13:33:00+09:00 - Updated: 2025-12-13T13:33:00+09:00 - Original source: [dbpro.app](https://www.dbpro.app/blog/sqlite-json-virtual-columns-indexing) - Points: 11 - Comments: 2 ## Summary SQLite의 **JSON 기능과 가상 생성 컬럼**을 결합하면, JSON 문서를 그대로 저장하면서도 필요한 필드만 인덱싱해 **B-tree 수준의 쿼리 속도**를 얻을 수 있습니다. `json_extract`로 정의한 가상 컬럼은 실제 데이터를 복제하지 않아도 되며, 새로운 검색 패턴이 필요할 때마다 **데이터 마이그레이션 없이 컬럼과 인덱스를 추가**해 확장할 수 있습니다. 스키마리스 데이터의 유연성과 관계형 DB의 성능을 함께 확보하는 실용적 패턴으로, SQLite 기반 애플리케이션의 구조를 단순하게 유지하면서도 성능을 극대화합니다. ## Topic Body - **SQLite의 JSON 기능**을 활용해 원본 JSON 문서를 그대로 저장하고, 필요한 필드를 **가상 생성 컬럼(virtual generated columns)** 으로 추출해 인덱싱하는 방식 소개 - `json_extract` 함수를 이용해 JSON 내부 데이터를 컬럼처럼 다루며, **B-tree 인덱스 속도**로 쿼리 수행 가능 - 새로운 쿼리 패턴이 필요할 때마다 **데이터 마이그레이션 없이** 컬럼과 인덱스를 추가해 확장 가능 - 이 방식은 **스키마리스 데이터의 유연성**과 **관계형 데이터베이스의 성능**을 동시에 확보 - SQLite를 사용하는 개발자에게 **간결한 구조와 높은 성능을 제공하는 실용적 패턴**으로 강조 --- ### SQLite와 JSON 기능의 결합 - SQLite는 **JSON 함수와 연산자**를 지원하며, 이를 통해 JSON 데이터를 직접 저장하고 조작 가능 - JSON 문서를 한 컬럼에 그대로 저장하고, 필요한 정보만 가상 컬럼으로 추출 - 이 방식은 스키마 정의 없이 데이터를 유연하게 다루는 형태 - DB Pro 팀은 최근 몇 달간 SQLite를 집중적으로 사용하며 이러한 기능을 실무에서 검증함 - SQLite는 적절히 구성하면 **프로덕션 환경에서도 안정적으로 사용 가능** ### 가상 생성 컬럼(Generated Columns) - `json_extract`를 이용해 JSON 내부의 특정 값을 **가상 생성 컬럼**으로 정의 - 이 컬럼은 실제 데이터를 저장하지 않고, 쿼리 시점에 계산되어 즉시 사용 가능 - 별도의 백필(backfill) 과정이나 데이터 복제가 필요 없음 - 예를 들어 JSON 데이터에서 특정 필드를 추출해 컬럼처럼 다루는 구조를 구현 가능 ### 인덱스 추가와 성능 향상 - 가상 컬럼에 **인덱스를 추가**하면 JSON 데이터도 일반 컬럼처럼 **B-tree 인덱스 속도**로 검색 가능 - 인덱스가 추가된 가상 컬럼은 관계형 데이터베이스의 컬럼과 동일한 성능 제공 - 이 접근법은 JSON 데이터의 크기가 커도 빠른 검색을 가능하게 함 ### 새로운 쿼리 패턴 추가 - 나중에 새로운 필드로 검색이 필요할 경우, 단순히 **새로운 가상 컬럼과 인덱스를 추가**하면 됨 - 예시: `user_id` 필드를 추출해 인덱스 생성 - 기존 데이터 행을 수정하거나 마이그레이션할 필요 없음 - 이로써 데이터 구조 변경 없이 **즉각적인 쿼리 확장성 확보** ### 패턴의 장점과 의의 - 이 패턴은 **스키마리스 JSON 저장의 유연성**과 **관계형 DB의 인덱스 성능**을 결합 - 초기 설계 단계에서 인덱싱 전략을 미리 결정할 필요 없음 - 필요 시점에 맞춰 컬럼과 인덱스를 추가해 최적화 가능 - SQLite를 활용하는 개발자에게 **단순하면서도 강력한 데이터 처리 방식**으로 제시됨 - DB Pro는 앞으로도 SQLite의 다양한 기능을 다루는 추가 글을 예고함 ## Comments ### Comment 47674 - Author: neo - Created: 2025-12-13T13:33:00+09:00 - Points: 1 ###### [Hacker News 의견들](https://news.ycombinator.com/item?id=46243904) - JSON 문서를 **직렬화된 B-tree**로 직접 인코딩할 수 있음 이렇게 하면 인덱싱된 속도로 내부 필드를 바로 조회할 수 있고, 문서 자체가 이미 인덱싱되어 있으므로 파싱이 필요 없게 됨 이 포맷을 **Lite³**라 부름. 내가 직접 작업 중인 프로젝트임 [GitHub 링크](https://github.com/fastserial/lite3) - 정말 멋짐! 나는 [Rkyv](https://rkyv.org)를 좋아하지만 Rust가 필요해서 작은 프로젝트에는 부담이 있었음 Lite³는 `lite3_val_bytes`로 **바이너리 데이터**를 지원하는 점이 특히 마음에 듦 - Lite³가 PostgreSQL의 **JSONB**와 어떻게 다른지 궁금했음 JSONB는 배열 길이와 오프셋을 함께 인코딩해서 압축 효율과 속도 사이를 조절할 수 있음 Lite³는 **in-place 업데이트**가 가능하지만, 민감 데이터가 남지 않도록 주기적으로 “vacuum”이 필요함 JSONB는 재인코딩 없이는 업데이트가 어렵지만, Lite³는 구조 순회만으로 간단히 정리 가능함 압축성은 JSONB가 더 좋을 것 같지만, Lite³의 설계는 매우 **영리한 접근**이라 생각함 나도 ASN.1 컴파일러를 유지보수 중이라 이런 직렬화 포맷에 관심이 많음. Lite³ 덕분에 새로운 아이디어를 얻었음 - Rust 버전의 구현이 있으면 정말 좋겠음 - SQLite를 정말 좋아하지만, 분석용으로는 **DuckDB**를 더 자주 쓰고 있음 DuckDB는 SQLite처럼 단일 파일을 사용하면서도 대용량 데이터셋을 **극도로 빠르게 처리**함 M2 맥북에서 2천만 건 데이터를 다루는데도 매우 빠름 예를 들어 다음 쿼리로 JSON 파일을 바로 읽을 수 있음 ```sql SELECT avg(sale_price), count(DISTINCT customer_id) FROM '/my-data-lake/sales/2024/*.json'; ``` 또한 JSON 타입 컬럼을 로드해 Postgres 스타일의 `col->>'$.key'` 문법도 사용 가능함 - 첫 번째 쿼리가 파일 시스템의 JSON 파일들을 즉석에서 인덱싱하는 건지 궁금함 - DuckDB는 시각화 도구 **pygwalker**와 함께 쓰면 수백만 건의 데이터를 몇 초 만에 분석할 수 있음 다만 SQLite와 비교하는 건 약간 불공평함. SQLite는 시스템 구축용, DuckDB는 분석 전용으로 쓰는 게 맞음 여러 플랫폼에 배포할 때 DuckDB는 다소 까다로움 - 데이터셋을 **압축하지 않고 저장**하는 일은 절대 피해야 함. DuckDB는 다양한 압축 포맷을 지원함 - JSON 성능을 위해 **Generated Column**을 쓰는 건 흔한 방식이라 생각했음 Postgres에서도 JSON 컬럼 안의 키를 외래키로 유지하기 위해 이렇게 사용한 적이 있음. 약간 ‘저주받은’ 방식이지만 잘 작동했음 - Postgres라면 JSONB 내부 필드에 직접 인덱스를 걸 수 있지 않음? 예를 들어 ```sql CREATE INDEX idx_status_gin ON my_table USING gin ((data->'status')); ``` [참고 블로그](https://www.crunchydata.com/blog/indexing-jsonb-in-postgres) - 하지만 이런 방식은 결국 **key/value 테이블**로 분리하는 게 더 나을 때가 많음 JSON 스키마가 바뀌면 파싱이나 마이그레이션이 실패할 수 있음 - 사실 그렇게 ‘저주받은’ 방식도 아님. 필요한 곳엔 정규화된 관계형 구조를, 나머진 jsonb로 처리하면 됨 - STORED 대신 **VIRTUAL 컬럼**을 써도 되는지 궁금했는데, 이 예시는 VIRTUAL을 사용함 - 최근 Claude Code가 제안한 최적화 예시를 통해 이 기법을 알게 되었음 SQLite의 새로운 기능을 몰라서 놓쳤던 부분이었는데, **성능 향상**이 꽤 컸음 교훈은, 익숙한 도구라도 주기적으로 문서를 다시 읽어보는 게 좋다는 것임 - 매뉴얼을 다시 읽는 게 의외로 **깨달음을 주는 일**임 - 2023년 HN에서 **bambax**의 댓글을 보고 블로그 글을 쓰게 되었음 [원문 댓글 링크](https://news.ycombinator.com/item?id=37082941) - JSON을 직접 투영하지 않고도 인덱스를 만들 수 있지만, **계산된 컬럼(computed column)** 은 쿼리를 단순하게 만들어줌 MS-SQL 2025(v17) 이전에는 JSON 지원이 제한적이라 이 방식이 필수였음 - JSON을 직접 쿼리하지 않고 계산된 컬럼만 쓰면, **비인덱스 쿼리**를 실수로 작성할 일이 없음 - 로컬 DBA 컨퍼런스에서 이 기능을 들었지만, 그땐 큰 변화로 느끼지 못했음 - HN에서 기사를 열었는데 내 댓글이 인용되어 있고, 그 댓글이 글의 주제라니 신기한 경험이었음 “고마워요, bambax!”라는 문구를 보고 미소가 나왔음. **SQLite는 정말 멋진 도구**임 - 실제로 영감을 준 댓글은 [이 링크](https://news.ycombinator.com/item?id=37083561)에 있음 - 흥미롭지만, SQLite의 ["Index On Expression"](https://sqlite.org/expridx.html)을 써도 되지 않음? 예를 들어 `CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'))` 다만 JSON path 문법이 조금만 달라져도 인덱스를 사용하지 않을 수 있음. 반면 **Virtual Generated Column**은 항상 인덱스를 보장함 - 인덱스 표현식과 뷰를 함께 쓰면 표현식 일치를 보장할 수 있음 예시: [recordlite 프로젝트](https://github.com/fsaintjacques/recordlite) - JSON path 구문이 조금만 달라도 인덱스가 깨질 수 있음 예를 들어 `json_extract(data, "$.type")`과 `data -> '$.type'`은 서로 다르게 인식됨 즉, WHERE 절의 표현식이 바뀌면 인덱스가 무용지물이 됨 - 단순하고 빠른 해결책임. 쿼리와 인덱스가 일치해야 한다는 건 언제나 사실임 - 표현식 인덱스 기능은 SQLite 3.9.0(2015-10-14)부터 추가된 비교적 **새로운 기능**임 - 개발자들이 일관된 스키마가 있음에도 데이터를 전부 **JSON(B) 컬럼**에 넣는 걸 자제했으면 함 인덱스 설정, 제약 조건 관리가 어려워지고, 실제 사용 시 오버헤드가 커짐 - JSON 컬럼은 트리 구조처럼 테이블로 표현하기 어려운 데이터를 다룰 때 빛을 발함 예를 들어 Haskell+TypeScript 환경에서는 복잡한 중첩 구조를 JSON으로 직렬화하는 게 훨씬 편리함 - JSON(B)는 서로 다른 타입의 데이터를 한 컬렉션에 담을 때 유용함 예: 여러 결제 프로세서의 결과를 하나의 테이블에 저장하거나, **클래스파이드 사이트**에서 품목별로 다른 속성을 다룰 때 C#이나 JS/TS에서도 타입 검증 도구(Zod, OpenAPI 등)를 함께 쓰면 관리가 쉬움 - 단순한 JSON이라면 정규화가 낫지만, 복잡한 API 응답은 테이블로 풀면 **JOIN 지옥**이 됨 결국 유지보수성과 성능의 균형 문제임. 이 글의 핵심은 JSON에도 인덱스를 쉽게 걸 수 있다는 점임 - 센서 데이터처럼 **트리 구조 전체를 한 번에 다루는 경우**, JSON 컬럼이 훨씬 간단함 읽기 성능은 인덱스로 충분히 보완 가능함 - 완전한 정규화는 비효율적일 때가 많음 예를 들어 제품 가격 설정 시스템에서 고객별 특이한 할인 규칙을 JSON으로 표현하면 훨씬 유연함 - JSON 대신 XML을 쓴다면, 이는 90~00년대의 **문서형 데이터베이스(document store)** 와 동일한 모델임 삽입·갱신 시 파싱하고, 쿼리 시 인덱스만 접근하는 구조였음 SQLite가 이런 기능을 기본으로 제공한다는 점이 정말 흥미로움 ### Comment 47710 - Author: iolothebard - Created: 2025-12-14T14:36:45+09:00 - Points: 1 - Parent comment: 47674 - Depth: 1 20세기 말에… universal database라는게 있었는데… (지금은 맞고 그때는 틀리다.)