La arquitectura de microservicios, como todo en este mundo, tiene sus pros y sus contras. Algunos procesos se vuelven más fáciles con él, otros más difíciles. Y por el bien de la velocidad del cambio y una mejor escalabilidad, debe hacer sacrificios. Uno de ellos es la complejidad de la analítica. Si en un monolito todos los análisis operativos se pueden reducir a consultas SQL en una réplica analítica, en una arquitectura multiservicio cada servicio tiene su propia base de datos y parece que una consulta no es suficiente (¿o tal vez lo será?). Para aquellos que estén interesados ​​en cómo resolvimos el problema del análisis operativo en nuestra empresa y cómo aprendimos a vivir con esta solución, bienvenidos.

Mi nombre es Pavel Sivash, en DomClick trabajo en un equipo que se encarga de mantener el almacén de datos analíticos. Convencionalmente, nuestras actividades pueden atribuirse a la ingeniería de datos, pero, de hecho, la gama de tareas es mucho más amplia. Existen estándares de ingeniería de datos ETL/ELT, soporte y adaptación de herramientas de análisis de datos y desarrollo de herramientas propias. En particular, para los informes operativos, decidimos "pretender" que tenemos un monolito y dar a los analistas una base de datos que contendrá todos los datos que necesitan.

En general, consideramos diferentes opciones. Fue posible construir un repositorio completo; incluso lo intentamos, pero, para ser honesto, no pudimos hacer amigos con cambios bastante frecuentes en la lógica con un proceso bastante lento de construir un repositorio y realizar cambios en él ( si alguien tuvo éxito, escriba en los comentarios cómo). Podría decirles a los analistas: “Chicos, aprendan python y vayan a las líneas analíticas”, pero este es un requisito de reclutamiento adicional, y parecía que esto debería evitarse en la medida de lo posible. Decidimos intentar usar la tecnología FDW (Foreign Data Wrapper): de hecho, este es un dblink estándar, que está en el estándar SQL, pero con su interfaz mucho más conveniente. Sobre la base de ello, tomamos una decisión, que finalmente echó raíces, nos decidimos por ella. Sus detalles son el tema de un artículo aparte, y tal vez más de uno, porque quiero hablar de mucho: desde la sincronización del esquema de la base de datos hasta el control de acceso y la despersonalización de los datos personales. También se debe tener en cuenta que esta solución no reemplaza las bases de datos y repositorios analíticos reales, solo resuelve un problema específico.

En el nivel superior se ve así:

Hay una base de datos PostgreSQL donde los usuarios pueden almacenar sus datos de trabajo y, lo que es más importante, las réplicas analíticas de todos los servicios están conectadas a esta base de datos a través de FDW. Esto hace posible escribir una consulta en varias bases de datos, y no importa cuál sea: PostgreSQL, MySQL, MongoDB u otra cosa (archivo, API, si de repente no hay un envoltorio adecuado, puede escribir uno propio). Bueno, ¡todo parece estar genial! ¿Romper?

Si todo terminara de manera tan rápida y simple, entonces, probablemente, el artículo no existiría.

Es importante tener claro cómo Postgres maneja las solicitudes a los servidores remotos. Esto parece lógico, pero a menudo la gente no le presta atención: postgres divide la consulta en partes que se ejecutan de forma independiente en servidores remotos, recopila estos datos y realiza los cálculos finales por sí mismo, por lo que la velocidad de ejecución de la consulta dependerá en gran medida de cómo está escrito. También se debe tener en cuenta: cuando los datos provienen de un servidor remoto, ya no tienen índices, no hay nada que ayude al programador, por lo tanto, solo nosotros mismos podemos ayudar y sugerirlo. Y eso es de lo que quiero hablar con más detalle.

Una simple solicitud y un plan con ella.

Para mostrar cómo Postgres consulta una tabla de 6 millones de filas en un servidor remoto, veamos un plan simple.

explain analyze verbose  
SELECT count(1)
FROM fdw_schema.table;

Aggregate  (cost=418383.23..418383.24 rows=1 width=8) (actual time=3857.198..3857.198 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..402376.14 rows=6402838 width=0) (actual time=4.874..3256.511 rows=6406868 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table
Planning time: 0.986 ms
Execution time: 3857.436 ms

El uso de la declaración VERBOSE le permite ver la consulta que se enviará al servidor remoto y los resultados que recibiremos para su posterior procesamiento (cadena RemoteSQL).

Vayamos un poco más allá y agreguemos varios filtros a nuestra consulta: uno por booleano campo, uno por entrada fecha y hora por intervalo y uno por jsonb.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=577487.69..577487.70 rows=1 width=8) (actual time=27473.818..25473.819 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..577469.21 rows=7390 width=0) (actual time=31.369..25372.466 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 5046843
        Remote SQL: SELECT created_dt, is_active, meta FROM fdw_schema.table
Planning time: 0.665 ms
Execution time: 27474.118 ms

Aquí es donde radica el momento, al que debe prestar atención al escribir consultas. Los filtros no se transfirieron al servidor remoto, lo que significa que para ejecutarlo, postgres extrae los 6 millones de filas para filtrar localmente más tarde (la línea Filter) y realizar la agregación. La clave del éxito es escribir una consulta para que los filtros se transmitan a la máquina remota y recibamos y agreguemos solo las filas necesarias.

Eso es algo booleano

Con campos booleanos, todo es simple. En la consulta original, el problema se debía al operador is. Si lo reemplazamos por =, entonces obtenemos el siguiente resultado:

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=508010.14..508010.15 rows=1 width=8) (actual time=19064.314..19064.314 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..507988.44 rows=8679 width=0) (actual time=33.035..18951.278 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: ((("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 3567989
        Remote SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planning time: 0.834 ms
Execution time: 19064.534 ms

Como puede ver, el filtro voló al servidor remoto y el tiempo de ejecución se redujo de 27 a 19 segundos.

Cabe señalar que el operador is diferente del operador = el que puede trabajar con el valor Nulo. Esto significa que no es verdad en el filtro dejaremos los valores False y Null, mientras que != Verdadero dejará solo valores falsos. Por lo tanto, al reemplazar el operador no es debe pasar dos condiciones al filtro con el operador OR, por ejemplo, DONDE (col != True) O (col es nulo).

Con el booleano resuelto, seguimos adelante. Mientras tanto, devolvamos el filtro por valor booleano a su forma original para considerar de forma independiente el efecto de otros cambios.

marca de tiempo? Hz

En general, a menudo tiene que experimentar cómo escribir correctamente una consulta que involucra servidores remotos, y solo luego buscar una explicación de por qué sucede esto. En Internet se puede encontrar muy poca información al respecto. Entonces, en los experimentos, encontramos que un filtro de fecha fija vuela a un servidor remoto con fuerza, pero cuando queremos establecer la fecha dinámicamente, por ejemplo, now() o CURRENT_DATE, esto no sucede. En nuestro ejemplo, hemos agregado un filtro para que la columna created_at contenga datos de exactamente 1 mes en el pasado (ENTRE FECHA_ACTUAL - INTERVALO '7 meses' Y FECHA_ACTUAL - INTERVALO '6 meses'). ¿Qué hicimos en este caso?

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta->>'source' = 'test';

Aggregate  (cost=306875.17..306875.18 rows=1 width=8) (actual time=4789.114..4789.115 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..306874.86 rows=105 width=0) (actual time=23.475..4681.419 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
        Rows Removed by Filter: 76934
        Remote SQL: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))
Planning time: 0.703 ms
Execution time: 4789.379 ms

Solicitamos al planificador que calcule la fecha por adelantado en la subconsulta y pase la variable ya preparada al filtro. Y esta sugerencia nos dio un gran resultado, ¡la consulta se volvió casi 6 veces más rápida!

Nuevamente, es importante tener cuidado aquí: el tipo de datos en la subconsulta debe ser el mismo que el del campo por el cual filtramos, de lo contrario el planificador decidirá que dado que los tipos son diferentes y es necesario obtener primero todos los datos y filtrarlos localmente.

Devolvamos el filtro por fecha a su valor original.

freddy contra jsonb

En general, los campos booleanos y las fechas ya han acelerado suficientemente nuestra consulta, pero había un tipo de datos más. La batalla por filtrarlo, para ser honesto, aún no ha terminado, aunque aquí también hay éxitos. Entonces, así es como logramos pasar el filtro por jsonb campo a un servidor remoto.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=245463.60..245463.61 rows=1 width=8) (actual time=6727.589..6727.590 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=1100.00..245459.90 rows=1478 width=0) (actual time=16.213..6634.794 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 619961
        Remote SQL: SELECT created_dt, is_active FROM fdw_schema.table WHERE ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.747 ms
Execution time: 6727.815 ms

En lugar de filtrar operadores, debe utilizar la presencia de un operador. jsonb en un diferente. 7 segundos en lugar de los 29 originales. Hasta ahora, esta es la única opción exitosa para transferir filtros jsonb a un servidor remoto, pero aquí es importante tener en cuenta una limitación: usamos la versión 9.6 de la base de datos, pero para fines de abril planeamos completar las últimas pruebas y pasar a la versión 12. Tan pronto como actualicemos, escribiremos cómo afectó, porque hay muchos cambios para los que hay muchas esperanzas: json_path, nuevo comportamiento de CTE, push down (existente desde la versión 10). Tengo muchas ganas de probarlo pronto.

Acabar con él

Verificamos cómo cada cambio afecta la velocidad de consulta individualmente. Veamos ahora qué sucede cuando los tres filtros están escritos correctamente.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active = True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=322041.51..322041.52 rows=1 width=8) (actual time=2278.867..2278.867 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..322041.41 rows=25 width=0) (actual time=8.597..2153.809 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone)) AND ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 ms

Sí, la consulta parece más complicada, es un precio forzado, pero la velocidad de ejecución es de 2 segundos, ¡más de 10 veces más rápida! Y estamos hablando de una consulta simple sobre un conjunto de datos relativamente pequeño. En solicitudes reales, recibimos un aumento de hasta varios cientos de veces.

Para resumir: si está utilizando PostgreSQL con FDW, siempre verifique si todos los filtros se envían al servidor remoto y estará contento... Al menos hasta que llegue a las uniones entre tablas de diferentes servidores. Pero esa es una historia para otro artículo.

¡Gracias por su atención! Me encantaría escuchar preguntas, comentarios e historias sobre sus experiencias en los comentarios.

Fuente: habr.com