Cómo aprovechar SQLite JSON a velocidad de índice completo: consultas rápidas con columnas generadas

 (dbpro.app)
11 puntos por GN⁺ 2025-12-13 | 2 comentarios | Compartir por WhatsApp
  • Presenta un método para guardar el documento JSON original tal cual y extraer los campos necesarios como columnas generadas virtuales (virtual generated columns) para indexarlos usando las funciones JSON de SQLite
  • Con la función json_extract, los datos dentro del JSON pueden manejarse como si fueran columnas, lo que permite ejecutar consultas con velocidad de índice B-tree
  • Cada vez que se necesite un nuevo patrón de consulta, se puede ampliar agregando columnas e índices sin migración de datos
  • Este enfoque permite obtener al mismo tiempo la flexibilidad de los datos sin esquema y el rendimiento de una base de datos relacional
  • Se destaca como un patrón práctico que ofrece una estructura simple y alto rendimiento para desarrolladores que usan SQLite

Combinando SQLite con las funciones JSON

  • SQLite soporta funciones y operadores JSON, lo que permite almacenar y manipular datos JSON directamente
    • Se puede guardar el documento JSON completo en una sola columna y extraer solo la información necesaria como columnas virtuales
    • Este enfoque permite manejar los datos con flexibilidad sin definir un esquema por adelantado
  • En los últimos meses, el equipo de DB Pro ha estado usando SQLite de forma intensiva y ha validado estas capacidades en trabajo real
    • Si se configura correctamente, SQLite puede usarse de forma estable incluso en entornos de producción

Columnas generadas virtuales (Generated Columns)

  • Usando json_extract, es posible definir valores específicos dentro del JSON como columnas generadas virtuales
    • Estas columnas no almacenan físicamente los datos; se calculan al momento de la consulta y pueden usarse de inmediato
    • No hace falta un proceso de backfill independiente ni duplicación de datos
  • Por ejemplo, se puede construir una estructura que extraiga campos específicos desde datos JSON y los trate como columnas normales

Agregar índices y mejorar el rendimiento

  • Si se agregan índices sobre las columnas virtuales, los datos JSON también pueden buscarse con velocidad de índice B-tree como si fueran columnas comunes
    • Una columna virtual con índice ofrece el mismo rendimiento que una columna en una base de datos relacional
  • Este enfoque permite búsquedas rápidas incluso cuando el volumen de datos JSON crece

Agregar nuevos patrones de consulta

  • Si más adelante hace falta buscar por un nuevo campo, basta con agregar una nueva columna virtual y un índice
    • Ejemplo: extraer el campo user_id y crear un índice sobre él
    • No es necesario modificar las filas existentes ni hacer una migración
  • Así se obtiene escalabilidad inmediata para las consultas sin cambiar la estructura de los datos

Ventajas e importancia del patrón

  • Este patrón combina la flexibilidad de almacenar JSON sin esquema con el rendimiento de indexación de una base de datos relacional
    • No hace falta decidir la estrategia de indexación desde la etapa inicial del diseño
    • Se puede optimizar agregando columnas e índices justo cuando se necesiten
  • Se presenta como una forma de procesar datos simple pero potente para desarrolladores que trabajan con SQLite
  • DB Pro adelantó que publicará más artículos sobre distintas funciones de SQLite en el futuro

Lecturas relacionadas

2 comentarios

 
GN⁺ 2025-12-13
Opiniones de Hacker News

  • Es posible codificar directamente documentos JSON como un B-tree serializado
    Eso permite consultar campos internos directamente a velocidad de índice, y como el documento ya está indexado, no hace falta parsearlo
    A este formato lo llaman Lite³. Es un proyecto en el que estoy trabajando personalmente
    Enlace a GitHub

    • ¡Está realmente genial! Me gusta Rkyv, pero requiere Rust y eso se me hacía pesado para proyectos pequeños
      Me gusta especialmente que Lite³ soporte datos binarios con lite3_val_bytes
    • Me preguntaba en qué se diferencia Lite³ del JSONB de PostgreSQL
      JSONB puede codificar juntas la longitud de los arreglos y los offsets para ajustar el equilibrio entre compresión y velocidad
      Lite³ permite actualizaciones in-place, pero hace falta ejecutar “vacuum” periódicamente para evitar que queden datos sensibles
      En JSONB es difícil actualizar sin recodificar, pero Lite³ puede reorganizarse fácilmente solo recorriendo la estructura
      Supongo que JSONB comprime mejor, pero me parece que el diseño de Lite³ es un enfoque muy ingenioso
      Yo también mantengo un compilador ASN.1, así que estos formatos de serialización me interesan mucho. Lite³ me dio nuevas ideas
    • Estaría muy bien que existiera una implementación en Rust

  • Me encanta SQLite, pero para análisis estoy usando más DuckDB
    DuckDB usa un solo archivo como SQLite, pero procesa datasets grandes de forma extremadamente rápida
    En una MacBook M2 es muy veloz incluso manejando 20 millones de registros
    Por ejemplo, puedes leer directamente archivos JSON con una consulta como esta

    SELECT avg(sale_price), count(DISTINCT customer_id)
FROM '/my-data-lake/sales/2024/*.json';

    También puedes cargar columnas de tipo JSON y usar sintaxis estilo Postgres como col->>'$.key'

    • Me pregunto si esa primera consulta indexa al vuelo los archivos JSON del sistema de archivos
    • DuckDB, usado junto con la herramienta de visualización pygwalker, permite analizar millones de registros en cuestión de segundos
      Aun así, compararlo con SQLite es un poco injusto. SQLite encaja para construir sistemas, mientras que DuckDB está más orientado al análisis
      Al distribuir en varias plataformas, DuckDB puede ser algo complicado
    • Nunca deberías guardar datasets sin comprimir. DuckDB soporta varios formatos de compresión

  • Pensaba que usar Generated Column para mejorar el rendimiento con JSON ya era una práctica común
    En Postgres lo he usado para mantener como claves foráneas las keys dentro de columnas JSON. Es una forma un poco “maldita”, pero funcionó bien

    • ¿En Postgres no se pueden poner índices directamente sobre campos internos de JSONB?
      Por ejemplo 
      CREATE INDEX idx_status_gin
ON my_table
USING gin ((data->'status'));
      Blog de referencia
    • Pero muchas veces, con este enfoque, termina siendo mejor separarlo en una tabla key/value
      Si el esquema JSON cambia, el parseo o la migración pueden fallar
    • En realidad, no es una forma tan “maldita”. Puedes usar una estructura relacional normalizada donde haga falta y jsonb para el resto
    • Me preguntaba si se podía usar una columna VIRTUAL en lugar de STORED, y este ejemplo usa VIRTUAL

  • Conocí esta técnica por un ejemplo de optimización que Claude Code me sugirió recientemente
    Era una parte nueva de SQLite que yo no conocía y por eso la había pasado por alto, pero la mejora de rendimiento fue bastante grande
    La lección es que, incluso con herramientas conocidas, conviene volver a leer la documentación de vez en cuando

    • Volver a leer el manual da insights de forma inesperada

  • Terminé escribiendo una entrada de blog después de ver un comentario de bambax en HN en 2023
    Enlace al comentario original

  • Se pueden crear índices sin proyectar directamente el JSON, pero una computed column hace que las consultas sean más simples
    Antes de MS-SQL 2025(v17), el soporte para JSON era limitado, así que este enfoque era imprescindible

    • Si usas solo columnas calculadas y no consultas el JSON directamente, evitas escribir por accidente consultas sin índice
    • Escuché sobre esta función en una conferencia local de DBAs, pero en ese momento no me pareció un cambio tan grande

  • Abrí el artículo en HN y fue curioso ver que citaban mi comentario, y que además ese comentario era el tema principal del texto
    Sonreí al ver la frase “¡Gracias, bambax!”. SQLite realmente es una herramienta increíble

    • El comentario que realmente inspiró esto está en este enlace

  • Es interesante, pero ¿no bastaría con usar "Index On Expression" de SQLite?
    Por ejemplo, CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'))
    Sin embargo, si la sintaxis del JSON path cambia aunque sea un poco, puede que el índice deje de usarse. En cambio, una Virtual Generated Column siempre garantiza el índice

    • Si combinas índices sobre expresiones con vistas, puedes garantizar que la expresión coincida
      Ejemplo: proyecto recordlite
    • Si la sintaxis del JSON path cambia aunque sea un poco, el índice puede romperse
      Por ejemplo, json_extract(data, "$.type") y data -> '$.type' se interpretan de forma distinta
      Es decir, si cambia la expresión del WHERE, el índice deja de servir
    • Es una solución simple y rápida. Que la consulta y el índice coincidan siempre ha sido una condición necesaria
    • La función de índices sobre expresiones es una característica relativamente reciente añadida desde SQLite 3.9.0 (2015-10-14)

  • Ojalá los desarrolladores evitaran meter todos los datos en columnas JSON(B) cuando sí existe un esquema consistente
    Se vuelve más difícil configurar índices y manejar restricciones, y en uso real el overhead termina siendo grande

    • Las columnas JSON brillan cuando se trata de datos que son difíciles de representar como tablas, como estructuras de árbol
      Por ejemplo, en un entorno Haskell+TypeScript es mucho más práctico serializar estructuras anidadas complejas como JSON
    • JSON(B) es útil cuando quieres guardar distintos tipos de datos dentro de una misma colección
      Ejemplo: guardar en una sola tabla los resultados de varios procesadores de pago, o manejar atributos distintos por ítem en un sitio de clasificados
      En C# o JS/TS también es fácil de gestionar si usas herramientas de validación de tipos como Zod u OpenAPI
    • Para JSON simple, normalizar suele ser mejor, pero las respuestas complejas de APIs se convierten en un infierno de JOINs si las descompones en tablas
      Al final es una cuestión de equilibrio entre mantenibilidad y rendimiento. La idea central del artículo es que también se puede indexar JSON fácilmente
    • Cuando manejas de una sola vez toda una estructura de árbol, como pasa con datos de sensores, una columna JSON es mucho más simple
      El rendimiento de lectura puede compensarse bastante bien con índices
    • La normalización completa muchas veces es ineficiente
      Por ejemplo, en un sistema de configuración de precios de productos, expresar reglas de descuento inusuales por cliente en JSON da mucha más flexibilidad

  • Si usaras XML en lugar de JSON, esto sería básicamente el mismo modelo que una base de datos documental (document store) de los 90 y 2000
    Se parseaba al insertar o actualizar, y al consultar solo se accedía al índice
    Es realmente interesante que SQLite ofrezca esto de forma nativa
 
iolothebard 2025-12-14

A finales del siglo XX… existía algo llamado base de datos universal… (ahora está bien, pero en ese entonces estaba mal.)