Almacenar datos temporales de tipo snapshot en PostgreSQL

Una de las necesidades que podemos tener en nuestro proyecto es el de almacenar datos sobre una entidad que evolucionan a lo largo del tiempo. Si bien es cierto que existen sistemas específicamente diseñados para estos propósitos como pudieran ser bases de datos temporales. Hoy vamos a ver cómo utilizando PostgreSQL podemos acometer esta tarea, además es especialmente interesante cuándo tenemos que tratar con estructuras de datos complejas.

Nota aclaratoria

Las pruebas se han realizado utilizando la última imagen docker disponible de la base de datos PostgresSQL. En este caso se ha ejecutado usando podman en lugar de docker.

 podman run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -p5432:5432 -d docker.io/library/postgres

Problema

Dado un sistema informático al cual sometemos a pruebas de rendimiento de forma periódica, queremos almacenar la evolución de los distintos casos los datos de uso. Para cada prueba guardamos los tiempos medios del caso de uso, si cumplió o no el objetivo de ese caso de uso y la versión del software que se empleó en cada momento. El objetivo es poder responder a las siguientes preguntas.

Cuál era el tiempo medio de ejecución de cada caso de uso para un día dado.
Para cada caso de uso, qué versión del sistema fue la última vez que se ejecutó.
Cuál es la evolución día a día de los tiempos medios de un caso de uso.

Un ejemplo del resultado de ejecución de una prueba sería:

{
  "useCase" : "U-0001",
  "result" : "OK",
  "duration" : 345,
  "version" : "1.0.1"
}

Almacenar datos

Para almacenar los datos de esta entidad vamos a crear una tabla de benchmarks con 4 columnas.

Una clave autogenerada de tipo serial.
Una columna que represente la fecha del registro.
Una columna que representa la clave primara de la entidad.
Finalmente, una columna que contenga los datos de la misma.

Vamos a insertar además los siguientes registros. - Un registro para el caso de uso uno - Dos registros para el caso de uso 2 donde el segundo caso de uso es satisfactorio. - Tres registros para el caso de uso 3 donde los dos primeros han sido problemáticos y el último se ha conseguido el objetivo usando una nueva versión del software.

CREATE TABLE BENCHMARK
(
    BENCHMARK_ID   serial PRIMARY KEY NOT NULL,
    USE_CASE       text               NOT NULL,
    BENCHMARK_DATE TIMESTAMP          NOT NULL,
    BENCHMARK_DATA jsonb              NOT NULL
);
INSERT INTO BENCHMARK(USE_CASE, BENCHMARK_DATE, BENCHMARK_DATA)
VALUES ('U-0001', '2022-12-01 12:55', '{
          "useCase": "U-0001",
          "result": "OK",
          "duration": 345,
          "version": "1.0.0"
        }'),
       ('U-0002', '2022-12-01 13:58', '{
         "useCase": "U-0002",
         "result": "KO",
         "duration": 568,
         "version": "1.0.0"
       }'),
       ('U-0002', '2022-12-01 16:24', '{
         "useCase": "U-0002",
         "result": "OK",
         "duration": 485,
         "version": "1.0.1"
       }'),
       ('U-0003', '2022-12-01 13:05', '{
         "useCase": "U-0003",
         "result": "KO",
         "duration": 1456,
         "version": "1.0.0"
       }'),
       ('U-0003', '2022-12-01 13:12', '{
         "useCase": "U-0003",
         "result": "KO",
         "duration": 1516,
         "version": "1.0.0"
       }'),
       ('U-0003', '2022-12-01 16:29', '{
         "useCase": "U-0003",
         "result": "KO",
         "duration": 1325,
         "version": "1.0.1"
       }'),
       ('U-0003', '2022-12-01 16:32', '{
         "useCase": "U-0003",
         "result": "KO",
         "duration": 1219,
         "version": "1.0.1"
       }'),
       ('U-0003', '2022-12-13 10:02', '{
         "useCase": "U-0003",
         "result": "OK",
         "duration": 275,
         "version": "1.1.0"
       }');

Preguntas sobre el último estado de cada prueba

A continuación vamos a lanzar un conjunto de consultas para consultar en qué estado están las pruebas para un día dado, para ello vamos a utilizar dos capacidades específicas de PostgreSQL. Por un lado, vamos a emplear la cláusula DISCTINCT ON la cual nos permite para una clave obtener el valor de su último registro. Por otro lado, vamos a emplear las funciones de tratamiento de json para extraer información directamente de nuestros objetos resultado.

¿Cuál es el estado de los distintos casos de uso a día 5 de diciembre?

Podemos obtener esa información directamente con la siguiente consulta, vamos a ver las partes principales que la componen:

SELECT DISTINCT ON (USE_CASE) -- 2
                              USE_CASE,
                              BENCHMARK_DATA ->> 'result' -- 4
from BENCHMARK
WHERE BENCHMARK_DATE < '2022-12-05' -- 1
ORDER BY USE_CASE, BENCHMARK_DATE DESC; -- 3

1 Filtrar por fecha para obtener solo los registros anteriores a la fecha deseada.
2 Indicar que vamos a querer solo una fila para cada caso de uso.
3 Indicar que para cada caso de uso nos vamos a quedar con su valor más reciente.
4 Obtener el valor del resultado de la prueba del json.

Table 1. Resultado estados de los casos de uso
use_case	result
U-0001	OK
U-0002	OK
U-0003	KO

¿Cuál es el tiempo de ejecución más actualizado para cada los casos de uso OK a dia 5 de diciembre?

Además de lo anterior, podemos incluir filtrados dentro del json (1) así como extraer el valor numérico directamente (2).

SELECT DISTINCT ON (USE_CASE) USE_CASE,
                              BENCHMARK_DATA -> 'duration' as duration -- 2
from BENCHMARK
WHERE BENCHMARK_DATE < '2022-12-05'
  AND benchmark_data ->> 'result' = 'OK' -- 1
ORDER BY USE_CASE, BENCHMARK_DATE DESC;

Table 2. Resultado de los tiempos de ejecución
use_case	duration
U-0001	345
U-0002	485

Para el caso de uso 3, calcula para cada versión en qué fecha se obtuvieron los mejores tiempos, ordena el resultado usando la versión del software ascendentemente.

SELECT *
FROM (select DISTINCT ON (USE_CASE,BENCHMARK_DATA ->> 'version' ) -- 2
                                                                  USE_CASE,
                                                                  BENCHMARK_DATE,
                                                                  BENCHMARK_DATA ->> 'version' AS VER,
                                                                  BENCHMARK_DATA -> 'duration' AS DURATION
      FROM BENCHMARK
      WHERE USE_CASE = 'U-0003' -- 1
      ORDER BY USE_CASE, VER, DURATION) FASTEST_VERSIONS -- 3
ORDER BY STRING_TO_ARRAY(VER, '.')::INT[]; --4

1 Filtramos el caso de uso.
2 Elegimos obtener un registro para cada caso de uso para cada versión.
3 Ordenamos por duración ascendente para obtener para cada versión su tiempo de ejecución más rápido.
4 Utilizamos una sub-consulta y ordenamos por la versión semántica convirtiendo el string a un array y ordenando cada parte de la versión semántica (major.minor.patch) de forma independiente.

Table 3. Resultado de los mejores tiempos para cada versión del caso de uso 3
use_case	benchmark_date	ver	duration
U-0003	2022-12-01 13:05:00.000000	1.0.0	1456
U-0003	2022-12-01 16:32:00.000000	1.0.1	1219
U-0003	2022-12-13 10:02:00.000000	1.1.0	275

Evolución temporal y uso de la cláusula LATERAL

Hasta ahora hemos consultado siempre los datos tal cual se nos presentan, pero una de las necesidades que nos pueden aparecer es la de querer generar una gráfica con la evolución día a día de los tiempos. Para eso lo que nos gustaría hacer es tomar el último tiempo obtenido cada día para un caso de uso y en caso de que ese día no se hubiesen ejecutado pruebas obtener el valor del último día.

Para solventar este problema vamos a hacer uso de la cláusula LATERAL de PostgreSQL.

SELECT t::date as date, BENCHMARK_DAY.* -- 6
FROM generate_series('2022-12-10', '2022-12-14', interval '1 day') AS t(day), -- 1
     LATERAL ( -- 2
         SELECT DISTINCT ON (USE_CASE) USE_CASE, BENCHMARK_DATA -> 'duration' as DURATION -- 3
         FROM BENCHMARK
         WHERE BENCHMARK_DATE <= t -- 4
         ORDER BY USE_CASE, BENCHMARK_DATE DESC ) BENCHMARK_DAY -- 3

1 Generamos una serie temporal desde el día 10 de diciembre hasta el día 14.
2 Hacemos un LATERAL JOIN para poder rellenar cada día con la información necesaria.
3 Calculamos para un día los mejores tiempos de ejecución.
4 Filtramos los resultados solo para los días anteriores al día de la secuencia.
5 Obtenemos el día de la secuencia en formato fecha así como los resultados de los tiempos.

Table 4. Result lateral join
t	use_case	duration
2022-12-10	U-0001	345
2022-12-10	U-0002	485
2022-12-10	U-0003	1219
2022-12-11	U-0001	345
2022-12-11	U-0002	485
2022-12-11	U-0003	1219
2022-12-12	U-0001	345
2022-12-12	U-0002	485
2022-12-12	U-0003	1219
2022-12-13	U-0001	345
2022-12-13	U-0002	485
2022-12-13	U-0003	1219
2022-12-14	U-0001	345
2022-12-14	U-0002	485
2022-12-14	U-0003	275

Podemos visualizar mejor el resultado si generamos una gráfica a partir de los datos.

almacenar datos temporales de tipo snapshot en postgresql benchmark

Optimización

Si comenzamos a hacer uso de las capacidades de PostgreSQL como puede ser el DISTINCT ON, hemos de tener en cuenta que vamos a hacer un uso intensivo de funciones de agregación y de ordenación. Para un conjunto de datos mediano podemos observar bajadas significativas de rendimiento si no disponemos de los índices adecuados. Podemos verlo fácilmente si empleamos un Explain plan de nuestra primera consulta.

EXPLAIN
SELECT DISTINCT ON (USE_CASE) *
from BENCHMARK
ORDER BY USE_CASE, BENCHMARK_DATE DESC;

El resultado es que estamos realizando un full scan de la tabla, en este caso el coste es de 1.2. Evidentemente, estamos hablando de solo 8 filas, si nos planteamos hablar de varios millones, el coste de un full scan no es viable para tener tiempos rápidos de ejecución.

Unique  (cost=1.20..1.24 rows=8 width=76)
  ->  Sort  (cost=1.20..1.22 rows=8 width=76)
        Sort Key: use_case, benchmark_date DESC
        ->  Seq Scan on benchmark  (cost=0.00..1.08 rows=8 width=76)

De cara a plantear un índice debemos de considerar no solo los campos si no también él uso que haremos de los mismos, por ejemplo, en este caso lo más habitual será calcular para cada indicador su último valor a un día dado, para ello deberemos de crear un índice sobre el caso de uso y la fecha del mismo de forma descendente.

CREATE INDEX BENCHMARK_DISCTINCT_ON_INDEX ON BENCHMARK (USE_CASE, BENCHMARK_DATE DESC);

Si volvemos a lanzar el explain plan vemos que al hacer uso del índice podemos reducir hasta un máximo de 0.13 el tiempo.

Unique  (cost=0.13..12.27 rows=3 width=108)
  ->  Index Scan using benchmark_use_case_benchmark_date_idx on benchmark  (cost=0.13..12.25 rows=8 width=108)

Conclusión

PostgreSQL es una herramienta de software libre que nos permite realizar una gran cantidad de funciones. En este caso la versatilidad de su DISCTINCT ON, LATERAL y su tratamiento de datos en formato json nos permite ejecutar operaciones que a priori pudieran parecer complejas en muy pocas líneas. Si además acompañamos estas sentencias con los índices apropiados conseguiremos un bajo tiempo de desarrollo con unos bajos tiempos de ejecución.