Diseñar una tabla de series temporales de solo inserción en PostgreSQL

Objetivo

Una tabla que acepta un flujo constante de filas con marca de tiempo, responde rápidamente a "serie X entre t1 y t2", y descarta los datos antiguos de forma económica y sin bloat.

Requisitos previos

Un período de retención conocido, las formas de consulta dominantes (una serie a lo largo de una ventana; agregados por ventana), una estimación de filas por día, y el tipo de reloj: timestamptz, la abreviatura documentada de timestamp with time zone, almacena un instante absoluto y evita la ambigüedad del horario de verano.

Pasos

1. Definir la fila: series_id (clave foránea a una tabla de metadatos), ts timestamptz NOT NULL, columnas de medición con NOT NULL allí donde un valor ausente es imposible, y sin id sustituto a menos que las filas deban referenciarse individualmente. Una clave primaria sobre (series_id, ts) sirve también para la consulta principal; la documentación exige que la clave primaria o restricción de unicidad de una tabla particionada incluya todas las columnas de la clave de partición.
2. Crear la tabla con PARTITION BY RANGE (ts) y una partición por día, semana o mes, elegida de modo que una ventana de consulta habitual abarque pocas particiones y el período de retención sea un número entero de ellas. La documentación advierte de que demasiadas particiones alargan la planificación y aumentan el uso de memoria.
3. Crear las particiones futuras con antelación mediante una tarea programada (o una herramienta como pg_partman). Una inserción en un rango inexistente falla; una partición DEFAULT captura esas filas de forma silenciosa, y la documentación señala que adjuntar más tarde una partición implica entonces recorrerla bajo un bloqueo ACCESS EXCLUSIVE, a menos que una restricción CHECK descarte el nuevo rango.
4. Indexar según la forma de la consulta: la clave primaria (B-tree) para las consultas de rango por serie; un índice BRIN sobre ts para los recorridos de tiempo sobre toda la tabla. La documentación de tipos de índice describe BRIN como el almacenamiento de resúmenes por rango de bloques físicos, eficaz cuando los valores se correlacionan con la posición física, que es el caso de los datos de solo inserción.
5. Implementar la retención como ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY, con archivado opcional, y después DROP TABLE. La documentación de particionado indica que esto es mucho más rápido que un DELETE masivo y evita la sobrecarga de VACUUM que este provocaría.
6. Añadir una tabla de resumen (agregados por hora o por día) rellenada por una tarea cuando los dashboards consultan rangos largos.
7. Cargar por lotes (COPY o INSERT de varias filas) ordenados por tiempo, para que los rangos de BRIN se mantengan compactos.

Resultado esperado

Las consultas con un predicado de tiempo tocan solo las particiones dentro del rango (partition pruning), las inserciones llegan a la partición más reciente, los datos antiguos desaparecen mediante una operación de metadatos, y la eliminación no provoca bloat.

Límites y base de verificación

El pruning necesita un predicado sobre la clave de partición; una consulta por serie sin límite de tiempo lee el índice de todas las particiones. Las actualizaciones y las llegadas fuera de orden debilitan la correlación de BRIN. Sigue la documentación citada; no se afirma ninguna cifra de rendimiento ni de tamaño.