Get best of the week

Análisis en línea en la arquitectura de microservicios: ayuda y sugerencia Postgres FDW ̶ и Post Post ̶п̶р̶о̶с̶т̶и̶т иь̶

La arquitectura de microservicios, como todo en este mundo, tiene sus ventajas y desventajas. Algunos procesos con él se vuelven más fáciles, otros más complicados. Y en aras de la velocidad de cambio y una mejor escalabilidad, se deben hacer sacrificios. Una de ellas es la complicación de la analítica. Si en un monolito todos los análisis operativos pueden reducirse a consultas SQL para una réplica analítica, entonces, en una arquitectura multiservicio, cada servicio tiene su propia base y parece que no se puede prescindir de una consulta (¿o se puede prescindir de ella?). Para aquellos que estén interesados ​​en cómo resolvimos el problema de la analítica operativa en nuestra empresa y cómo aprendimos a vivir con esta solución, bienvenidos.


Mi nombre es Pavel Sivash, en DomKlik trabajo en un equipo responsable de mantener el almacén de datos analíticos. Convencionalmente, nuestras actividades pueden atribuirse a la fecha de ingeniería, pero, de hecho, el rango de tareas es mucho más amplio. Existen estándares ETL / ELT para ingeniería de fecha, soporte y adaptación de herramientas para el análisis de datos y el desarrollo de sus propias herramientas. En particular, para los informes operativos, decidimos "fingir" que tenemos un monolito y darles a los analistas una base en la que tendrán 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 suficientes cambios 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). Se podría decir a los analistas: "Chicos, aprendan Python e ir a señales analíticas", pero este es un requisito adicional para la contratación de personal, y parecía que esto debería evitarse si es posible. Decidimos intentar usar la tecnología FDW (Foreign Data Wrapper): de hecho, este es el dblink estándar, que está en el estándar SQL, pero con su interfaz mucho más conveniente. Sobre la base de eso, tomamos una decisión, que finalmente se calmó, nos detuvimos. Sus detalles son objeto de un artículo separado, o tal vez no uno,porque quiero hablar mucho: desde la sincronización de esquemas de bases de datos hasta el control de acceso y el anonimato de datos personales. También debe hacer una reserva de que esta solución no sustituye a bases de datos analíticas y repositorios reales, solo resuelve un problema específico.

Nivel superior se ve así:


Existe una base de datos PostgreSQL, donde los usuarios pueden almacenar sus datos de trabajo, y lo 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, sin importar cuál sea: PostgreSQL, MySQL, MongoDB u otra cosa (archivo, API, si de repente no hay un contenedor adecuado, puede escribir el suyo). Bueno, todo parece estar super! ¿Estamos en desacuerdo?

Si todo terminara tan rápido y simplemente, entonces, probablemente, no habría habido ningún artículo.

Es importante comprender claramente cómo postgres maneja las solicitudes a servidores remotos. Esto parece lógico, pero a menudo no le prestan atención: postgres divide la solicitud en partes que se realizan en servidores remotos de forma independiente, recopila estos datos y los cálculos finales se llevan a cabo por sí mismos, por lo que la velocidad de la solicitud dependerá en gran medida de cómo se escriba. También debe tenerse en cuenta: cuando los datos provienen de un servidor remoto, ya no tienen índices, no hay nada que pueda ayudar al planificador, por lo tanto, solo nosotros podemos ayudarlo y sugerirlo. Y me gustaría contarles más sobre eso.

Solicitud simple y plan con ella


Para mostrar cómo Postgres ejecuta una consulta en 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 instrucción VERBOSE le permite ver la solicitud que se enviará al servidor remoto y los resultados que recibiremos para su posterior procesamiento (línea RemoteSQL).

Vayamos un poco más allá y agreguemos varios filtros a nuestra consulta: uno por el campo booleano , uno por la marca de tiempo en el 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 se encuentra 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, el postgres extiende los 6 millones de líneas, solo para filtrarlo localmente más tarde (línea de filtro) y realizar la agregación. La clave del éxito es escribir una solicitud para que los filtros se transfieran a la máquina remota, y recibamos y agreguemos solo las filas necesarias.

Eso es algo booleanshit


Con los campos booleanos, todo es simple. En la solicitud original, el problema se debió a la declaración is . Si lo reemplazamos con = , 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ó a un servidor remoto y el tiempo de ejecución se redujo de 27 a 19 segundos.

Vale la pena señalar que el operador is difiere del operador = en que puede funcionar con el valor Nulo. Esto significa que no es Verdadero en el filtro dejará Falso y Nulo, mientras que ! = Verdadero solo dejará Falso. Por lo tanto, al reemplazar el operador no es , se deben pasar dos condiciones con el operador OR al filtro, por ejemplo, WHERE (col! = True) OR (col es nulo) .

Con booleano resuelto, sigue adelante. Mientras tanto, devuelva el filtro por valor booleano a su forma original, para considerar independientemente el efecto de otros cambios.

marca de tiempo? hz


En general, a menudo hay que experimentar cómo escribir una consulta que involucra servidores remotos, y solo entonces buscar una explicación de por qué sucede esto. Se puede encontrar muy poca información sobre esto en Internet. Entonces, en experimentos, descubrimos que el filtro por una fecha fija vuela al servidor remoto con una explosión, pero cuando queremos establecer la fecha dinámicamente, por ejemplo, now () o CURRENT_DATE, esto no sucede. En nuestro ejemplo, agregamos un filtro para que la columna created_at contenga datos exactamente de 1 mes en el pasado (ENTRE CURRENT_DATE - INTERVAL '7 month' AND CURRENT_DATE - INTERVAL '6 month'). ¿Qué hemos hecho 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

Le pedimos al planificador que calcule la fecha en la subconsulta por adelantado y que pase la variable preparada al filtro. Y esta pista nos dio un excelente 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 campo para el que estamos filtrando, de lo contrario, el planificador decidirá que dado que los tipos son diferentes y primero debe obtener todos los datos y filtrarlos localmente.

Devuelva el filtro por fecha a su valor original.

Freddy vs. Jsonb


En general, los campos booleanos y las fechas ya han acelerado nuestra consulta, pero había un tipo de datos más. La batalla con el filtrado, francamente, todavía no ha terminado, aunque hay éxitos. Entonces, así es como logramos pasar el filtro por campo jsonb al 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 usar el operador de tener un jsonb en otro. 7 segundos en lugar de los 29 iniciales. Hasta ahora, esta es la única opción exitosa de transferir filtros a través de jsonb a un servidor remoto, pero es importante tener en cuenta una limitación: utilizamos la versión de base de datos 9.6, sin embargo, planeamos completar las últimas pruebas y pasar a la versión 12 a finales de abril. A medida que actualicemos, escribiremos cómo afectó, porque hay muchos cambios para los cuales hay muchas esperanzas: json_path, nuevo comportamiento CTE, push down (existente desde la versión 10). Me gustaría probarlo pronto.

Acabar con él


Verificamos cómo cada cambio afecta la velocidad de la solicitud individualmente. Ahora veamos qué sucede cuando los tres filtros se escriben 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 solicitud parece más complicada, es un tablero forzado, pero la velocidad de ejecución es de 2 segundos, ¡que es más de 10 veces más rápida! Y estamos hablando de una consulta simple en un conjunto de datos relativamente pequeño. En solicitudes reales, recibimos un crecimiento de varios cientos de veces.

Para resumir: si usa PostgreSQL con FDW, compruebe siempre que 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 esta es la historia para otro artículo.

¡Gracias por la atención! Estaré encantado de escuchar preguntas, comentarios e historias sobre su experiencia en los comentarios.

More articles:


All Articles