▲

SQLite JSON을 전체 인덱스 속도로 활용하는 방법: 생성된 컬럼을 이용한 고속 쿼리

 (dbpro.app)
9P by GN⁺ 1일전 | ★ favorite | 댓글 2개
  • 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의 다양한 기능을 다루는 추가 글을 예고함
▲
GN⁺ 1일전  [-]
Hacker News 의견들

  • JSON 문서를 직렬화된 B-tree로 직접 인코딩할 수 있음
    이렇게 하면 인덱싱된 속도로 내부 필드를 바로 조회할 수 있고, 문서 자체가 이미 인덱싱되어 있으므로 파싱이 필요 없게 됨
    이 포맷을 Lite³라 부름. 내가 직접 작업 중인 프로젝트임
    GitHub 링크

    • 정말 멋짐! 나는 Rkyv를 좋아하지만 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 파일을 바로 읽을 수 있음

    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 내부 필드에 직접 인덱스를 걸 수 있지 않음?
      예를 들어 
      CREATE INDEX idx_status_gin
ON my_table
USING gin ((data->'status'));
      참고 블로그
    • 하지만 이런 방식은 결국 key/value 테이블로 분리하는 게 더 나을 때가 많음
      JSON 스키마가 바뀌면 파싱이나 마이그레이션이 실패할 수 있음
    • 사실 그렇게 ‘저주받은’ 방식도 아님. 필요한 곳엔 정규화된 관계형 구조를, 나머진 jsonb로 처리하면 됨
    • STORED 대신 VIRTUAL 컬럼을 써도 되는지 궁금했는데, 이 예시는 VIRTUAL을 사용함

  • 최근 Claude Code가 제안한 최적화 예시를 통해 이 기법을 알게 되었음
    SQLite의 새로운 기능을 몰라서 놓쳤던 부분이었는데, 성능 향상이 꽤 컸음
    교훈은, 익숙한 도구라도 주기적으로 문서를 다시 읽어보는 게 좋다는 것임

    • 매뉴얼을 다시 읽는 게 의외로 깨달음을 주는 일임

  • 2023년 HN에서 bambax의 댓글을 보고 블로그 글을 쓰게 되었음
    원문 댓글 링크

  • JSON을 직접 투영하지 않고도 인덱스를 만들 수 있지만, 계산된 컬럼(computed column) 은 쿼리를 단순하게 만들어줌
    MS-SQL 2025(v17) 이전에는 JSON 지원이 제한적이라 이 방식이 필수였음

    • JSON을 직접 쿼리하지 않고 계산된 컬럼만 쓰면, 비인덱스 쿼리를 실수로 작성할 일이 없음
    • 로컬 DBA 컨퍼런스에서 이 기능을 들었지만, 그땐 큰 변화로 느끼지 못했음

  • HN에서 기사를 열었는데 내 댓글이 인용되어 있고, 그 댓글이 글의 주제라니 신기한 경험이었음
    “고마워요, bambax!”라는 문구를 보고 미소가 나왔음. SQLite는 정말 멋진 도구임

    • 실제로 영감을 준 댓글은 이 링크에 있음

  • 흥미롭지만, SQLite의 "Index On Expression"을 써도 되지 않음?
    예를 들어 CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'))
    다만 JSON path 문법이 조금만 달라져도 인덱스를 사용하지 않을 수 있음. 반면 Virtual Generated Column은 항상 인덱스를 보장함

    • 인덱스 표현식과 뷰를 함께 쓰면 표현식 일치를 보장할 수 있음
      예시: 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가 이런 기능을 기본으로 제공한다는 점이 정말 흥미로움

답변달기
▲
iolothebard 9시간전  [-]

20세기 말에… universal database라는게 있었는데… (지금은 맞고 그때는 틀리다.)

답변달기