Vue Actividad: visualización instantánea en grandes bases (PR #2647)

Desarrollo
1

Tras la salida de la 4.82 y las discusiones con @pierre-gilles sobre el rendimiento de la nueva vista de Actividad en instalaciones un poco pesadas (al menos como la mía), aquí está el balance de la PR #2647, probada en mi base de 448 millones de estados.

El problema

En una base grande, filtrar la Actividad en una categoría poco densa (ej. « Aperturas »: pocos estados, antiguos) obligaba al servidor a escanear una enorme parte del historial antes de responder: el servidor solo respondía una vez que su página de 80 estados estaba llena, sin importar la profundidad a recorrer. Resultado: un spinner congelado durante 20 a 50 segundos, a veces 3 minutos.

Se midieron meticulosamente todas las pistas « puramente del servidor » (ventanas progresivas, anclaje en la última actividad — gracias @pierre-gilles por la #2642 —, segmentos disjuntos, compactación física de la tabla): cada una mejora, pero ninguna elimina el muro — el costo mínimo es el rendimiento de escaneo de DuckDB multiplicado por el volumen a recorrer.

La solución: dejar que el cliente controle la búsqueda

  • Servidor: getDeviceStatesHistory acepta un límite since → una consulta = una ventana temporal limitada, que devuelve lo que contiene (incluso menos de 80) y nunca se expande. Una ventana limitada responde en milisegundos gracias a las zone maps de DuckDB. Sin since, el comportamiento es estrictamente el mismo (retrocompatible).
  • Frontend: la vista de Actividad sondea 1 → 2 → 4 → 8 → 16 → 32 meses y luego una última consulta ilimitada, muestra cada lote tan pronto como llega, con una banda « Búsqueda de actividades — marzo 2025… » mientras se escanean las ventanas más antiguas en segundo plano.

Las mediciones (448 M de estados)

Caso Antes Después
Filtro « Aperturas » (pocos estados, antiguos) 20 a 33 s de spinner congelado Primeros estados en ~100 ms, la página se completa en segundo plano
Filtro « Botones » (estados a 17 meses) 22 a 51 s Banda inmediata indicando el mes escaneado, estados tan pronto como se encuentren
Vista « Todo » / en vivo / sensores habladores ~100 ms Sin cambios (~100 ms)

El trabajo total del peor caso no cambia — pero se realiza detrás de una página ya utilizable en lugar de bloquear la primera visualización. Es el principio del logbook de Home Assistant, adaptado a la API de Gladys.

image
image1111×499 25.5 KB

image
image1814×755 83 KB

La PR está lista para revisión. No toca ni el modelo de datos ni los agregados — es un simple corte de consultas.

2

¡Hola @Terdious, gracias por este análisis! :folded_hands:

Aun así, me sorprende que DuckDB no maneje este caso correctamente. Es bastante increíble tener que esperar 30 segundos solo para un SELECT [...] ORDER BY created_at DESC, cuando precisamente era una de las grandes promesas de DuckDB.

En mi opinión, no lo estamos utilizando de la manera correcta. O nos falta un índice (teniendo en cuenta que los índices multicomplejos pueden ser muy costosos en espacio en disco), o nuestra consulta no es óptima. :slightly_smiling_face:

3

Hola @pierre-gilles,

Nos hemos planteado exactamente la misma pregunta y hemos probado las dos hipótesis (consulta y estructura) antes de escribir la PR. Bueno, ya te imaginas, para responderte lo mejor posible, he puesto mis puntos a la IA en función de las pruebas y reflexiones que hemos tenido durante el desarrollo, espero que sea más claro de lo que yo podría hacer ^^ :

Respuesta corta: la consulta no es «solo» un ORDER BY, y el índice que la salvaría no existe en DuckDB — por diseño.

1. La verdadera consulta. No es SELECT … ORDER BY created_at DESC LIMIT 80 solo — eso, DuckDB lo hace muy bien (Top-N vectorizado). Es WHERE device_feature_id IN (las características de la categoría) ORDER BY created_at DESC LIMIT 80. En una categoría rara, las 80 líneas que coinciden están dispersas en el pasado — y sin un índice secundario, el motor no tiene ningún medio de saber dónde: escanea hasta encontrarlas. Peor caso = toda la tabla.

2. ¿Por qué no un índice? El único índice de DuckDB es el ART, diseñado para claves primarias y búsquedas puntuales: no se usa para ordenar (no hay recorrido ordenado como en B-tree), la documentación desaconseja crearlos más allá de este caso, y en 448 M de líneas costaría gigabytes además de un sobrecoste en cada una de nuestras decenas de escrituras/segundo. El «verdadero» índice de DuckDB son los zonemaps (min/max por bloque de ~120 k líneas) — eficaces únicamente cuando el filtro está correlacionado con el orden físico de los datos.

3. «¿Estamos estructurando mal nuestros datos?» — también probado, tomó unos 30 minutos. Reescribí toda la tabla en ORDER BY created_at en mi copia (el diseño óptimo para los zonemaps temporales): las ventanas acotadas pasan a unos pocos milisegundos :white_check_mark:… pero la categoría rara solo gana ×2 (33 s → 15 s). El problema no es el almacenamiento: de todas formas hay que leer un año de datos densos para encontrar 80 estados de una categoría que casi no los produce. Y agrupar por característica en su lugar rompería la vista «Todo» y se desharía continuamente (los inserts en vivo llegan en orden temporal). Tu anclaje last_value_changed (#2642) y una variante con tramos disjuntos también fueron medidos: mismos órdenes de magnitud.

4. La promesa de DuckDB está en otro lugar — y se cumple. Columna + OLAP = scans, agregaciones masivas ultra rápidas (nuestros gráficos de energía en millones de líneas ¡) y ligereza de la base de datos, a cambio del abandono de los índices secundarios. «Los 80 últimos estados de una entidad» es la consulta OLTP por excelencia — es exactamente por eso que Home Assistant la sirve con un índice compuesto (entidad, tiempo) en un row-store, pagando lo que cuesta: espacio en disco y escrituras más pesadas. Mismo arbitraje, otro lado.

De ahí la elección de la #2647: ventanas temporales acotadas (donde DuckDB sobresale, unos pocos milisegundos por consulta) controladas por el cliente, que muestra en tiempo real — el peor caso ya no bloquea nunca el primer renderizado. Si algún día queremos eliminar también el coste total del peor caso, la pista «limpia» sería una mini tabla-índice (1 línea por característica y por mes) consultada primero — pero es un añadido al modelo de datos.