Para el procesamiento complejo de grandes conjuntos de datos (diferentes : importaciones, conversiones y sincronización con una fuente externa) a menudo es necesario "recordar" temporalmente y procesar inmediatamente rápidamente algo voluminoso.
Una tarea típica de este tipo suele ser algo así: "Aquí mismo los últimos pagos recibidos, debe cargarlos rápidamente en el sitio web y vincularlos a sus cuentas"
Pero cuando el volumen de este “algo” comienza a medir cientos de megabytes, y el servicio debe seguir funcionando con la base de datos 24 horas al día, 7 días a la semana, surgen muchos efectos secundarios que arruinarán tu vida.
Para tratarlos en PostgreSQL (y no sólo en él), puedes utilizar algunas optimizaciones que te permitirán procesar todo más rápido y con menos consumo de recursos.
1. ¿Dónde realizar el envío?
Primero, decidamos dónde podemos cargar los datos que queremos "procesar".
1.1. Tablas temporales (TABLA TEMPORAL)
En principio, para PostgreSQL las tablas temporales son iguales que cualquier otra. Por lo tanto, supersticiones como “Todo lo que hay allí se almacena sólo en la memoria y puede terminar”. Pero también hay varias diferencias significativas.
Su propio “espacio de nombres” para cada conexión a la base de datos
Si dos conexiones intentan conectarse al mismo tiempo CREATE TABLE x, entonces alguien definitivamente obtendrá error de no unicidad objetos de base de datos.
Pero si ambos intentan ejecutar CREATE TEMPORARY TABLE x, entonces ambos lo harán normalmente y todos obtendrán tu copia mesas. Y no habrá nada en común entre ellos.
"Autodestrucción" al desconectarse
Cuando se cierra la conexión, todas las tablas temporales se eliminan automáticamente, por lo que manualmente DROP TABLE x no tiene sentido excepto...
Si estás trabajando a través de pgbouncer en modo transacción, entonces la base de datos sigue creyendo que esta conexión todavía está activa y en ella esta tabla temporal todavía existe.
Por lo tanto, intentar crearlo nuevamente, desde otra conexión a pgbouncer, generará un error. Pero esto se puede evitar utilizando CREATE TEMPORARY TABLE IF NOT EXISTS x.
Es cierto que es mejor no hacer esto de todos modos, porque entonces puede "de repente" encontrar allí los datos restantes del "propietario anterior". En cambio, es mucho mejor leer el manual y ver que al crear una tabla es posible agregar ON COMMIT DROP - es decir, cuando se complete la transacción, la tabla se eliminará automáticamente.
No replicación
Como pertenecen únicamente a una conexión específica, las tablas temporales no se replican. Pero Esto elimina la necesidad de un doble registro de datos. en montón + WAL, por lo que INSERTAR/ACTUALIZAR/ELIMINAR es significativamente más rápido.
Pero como una tabla temporal sigue siendo una tabla "casi normal", tampoco se puede crear en una réplica. Al menos de momento, aunque el parche correspondiente ya lleva tiempo circulando.
1.2. TABLA NO REGISTRADA
Pero, ¿qué debería hacer, por ejemplo, si tiene algún tipo de proceso ETL engorroso que no se puede implementar en una sola transacción, pero aún tiene pgbouncer en modo transacción? ..
O el flujo de datos es tan grande que No hay suficiente ancho de banda en una conexión ¿desde una base de datos (lectura, un proceso por CPU)?...
O algunas operaciones están en marcha asincrónicamente en diferentes conexiones?..
Aquí solo hay una opción: crear temporalmente una tabla no temporal. Juego de palabras, sí. Eso es:
- Creé "mis propias" tablas con nombres máximamente aleatorios para no cruzarse con nadie
- Extraer: los llenó con datos de una fuente externa
- Transformar: campos de enlace clave convertidos y completados
- Carga: vierte datos listos en tablas de destino
- eliminó “mis” tablas
Y ahora, una mosca en el ungüento. De hecho, todas las escrituras en PostgreSQL ocurren dos veces - , luego en los cuerpos de la tabla/índice. Todo esto se hace para respaldar ACID y corregir la visibilidad de los datos entre COMMIT'loco y ROLLBACK'transacciones nulas.
¡Pero no necesitamos esto! Tenemos todo el proceso. O fue completamente exitoso o no lo fue.. No importa cuántas transacciones intermedias haya: no nos interesa "continuar el proceso desde el medio", especialmente cuando no está claro dónde estaba.
Para hacer esto, los desarrolladores de PostgreSQL, allá por la versión 9.1, introdujeron algo como :
Con esta indicación, la tabla se crea como no registrada. Los datos escritos en tablas no registradas no pasan por el registro de escritura anticipada (consulte el Capítulo 29), lo que hace que dichas tablas trabajar mucho más rápido de lo habitual. Sin embargo, no son inmunes al fracaso; en caso de falla del servidor o apagado de emergencia, una tabla no registrada truncado automáticamente. Además, el contenido de la tabla no registrada no replicado a servidores esclavos. Todos los índices creados en una tabla no registrada se desregistran automáticamente.
En resumen, será mucho más rápido, pero si el servidor de la base de datos "cae", será desagradable. Pero, ¿con qué frecuencia sucede esto? ¿Sabe su proceso ETL cómo corregir esto correctamente “desde el medio” después de “revitalizar” la base de datos?
Si no, y el caso anterior es similar al tuyo, usa UNLOGGEDpero nunca no habilite este atributo en tablas reales, cuyos datos le son queridos.
1.3. EN COMPROMISO {ELIMINAR FILAS | GOTA}
Esta construcción le permite especificar el comportamiento automático cuando se completa una transacción al crear una tabla.
Про ON COMMIT DROP Ya escribí arriba, genera DROP TABLE, pero con ON COMMIT DELETE ROWS La situación es más interesante: se genera aquí. TRUNCATE TABLE.
Dado que toda la infraestructura para almacenar la metadescripción de una tabla temporal es exactamente la misma que la de una tabla normal, entonces La creación y eliminación constante de tablas temporales provoca un grave "hinchazón" de las tablas del sistema. pg_class, pg_attribute, pg_attrdef, pg_depend,…
Ahora imagine que tiene un trabajador en conexión directa con la base de datos, que abre una nueva transacción cada segundo, crea, llena, procesa y elimina una tabla temporal... Habrá un exceso de basura acumulada en las tablas del sistema, y esto provocará frenos adicionales para cada operación.
En general, ¡no hagas esto! En este caso es mucho más efectivo CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS sáquelo del ciclo de transacción; luego, al comienzo de cada nueva transacción, las tablas ya están existirá (guardar una llamada CREATE), pero Estará vacíogracias TRUNCATE (también guardamos su llamada) al completar la transacción anterior.
1.4. COMO...INCLUYENDO...
Mencioné al principio que uno de los casos de uso típicos de las tablas temporales son varios tipos de importaciones, y el desarrollador cansadamente copia y pega la lista de campos de la tabla de destino en la declaración de su tabla temporal...
¡Pero la pereza es el motor del progreso! Es por eso crear una nueva tabla "basada en la muestra" puede ser mucho más simple:
CREATE TEMPORARY TABLE import_table(
LIKE target_table
);Dado que luego puede generar una gran cantidad de datos en esta tabla, la búsqueda en ella nunca será rápida. Pero existe una solución tradicional para esto: ¡índices! Y si, una tabla temporal también puede tener índices.
Dado que, a menudo, los índices requeridos coinciden con los índices de la tabla de destino, simplemente puede escribir LIKE target_table INCLUDING INDEXES.
Si tu también necesitas DEFAULT-values (por ejemplo, para completar los valores de la clave principal), puede usar LIKE target_table INCLUDING DEFAULTS. O simplemente - LIKE target_table INCLUDING ALL — copia valores predeterminados, índices, restricciones,...
Pero aquí debes entender que si creaste importe la tabla inmediatamente con índices, entonces los datos tardarán más en cargarseque si primero llena todo y solo luego enrolla los índices; mire cómo lo hace como ejemplo .
En general, !
2. ¿Cómo escribir?
Déjame decirte: úsalo -fluir en lugar de "empacar" INSERT, . Incluso puedes hacerlo directamente desde un archivo pregenerado.
3. ¿Cómo tramitar?
Entonces, dejemos que nuestra introducción se vea así:
- tienes una tabla con datos de clientes almacenados en tu base de datos 1 millón de registros
- cada día un cliente te envía uno nuevo imagen completa"
- por experiencia sabes que de vez en cuando no se modifican más de 10 registros
Un ejemplo clásico de tal situación es — en total hay muchas direcciones, pero en cada carga semanal hay muy pocos cambios (cambio de nombre de asentamientos, combinación de calles, apariencia de casas nuevas), incluso a escala nacional.
3.1. Algoritmo de sincronización completo
Para simplificar, digamos que ni siquiera necesita reestructurar los datos; simplemente lleve la tabla al formato deseado, es decir:
- remover todo lo que ya no existe
- actualizar todo lo que ya existía y necesita ser actualizado
- para insertar todo lo que aún no ha sucedido
¿Por qué las operaciones deberían realizarse en este orden? Porque así es como el tamaño de la mesa crecerá mínimamente ().
ELIMINAR DEL horario de verano
No, por supuesto que puedes arreglártelas con sólo dos operaciones:
- remover (
DELETE) todo en general - para insertar todo desde la nueva imagen
Pero al mismo tiempo, gracias a MVCC, El tamaño de la mesa aumentará exactamente el doble.! Obtener +1 millón de imágenes de registros en la tabla debido a una actualización de 10K es una redundancia regular...
TRUNCAR horario de verano
Un desarrollador más experimentado sabe que toda la tableta se puede limpiar de forma bastante económica:
- claro (
TRUNCATE) tabla completa - para insertar todo desde la nueva imagen
El método es eficaz, , pero hay un problema... Estaremos agregando 1 millón de registros durante mucho tiempo, por lo que no podemos darnos el lujo de dejar la tabla vacía durante todo este tiempo (como sucederá sin envolverla en una sola transacción).
Y eso significa:
- estamos empezando transacción de larga duración
TRUNCATEimpone Acceso Exclusivo-bloqueo- Hacemos la inserción durante mucho tiempo, y todos los demás en este momento. ni siquiera puedo
SELECT
Algo no va bien...
ALTERAR TABLA… RENOMBRAR… / SOLTAR TABLA…
Una alternativa es llenar todo en una nueva tabla separada y luego simplemente cambiarle el nombre en lugar de la anterior. Un par de pequeñas cosas desagradables:
- todavía también Acceso Exclusivo, aunque mucho menos tiempo
- todos los planes de consulta/estadísticas para esta tabla se restablecen,
- todas las claves foráneas están rotas (FK) a la mesa
Hubo un parche WIP de Simon Riggs que sugería hacer ALTER-una operación para reemplazar el cuerpo de la tabla a nivel de archivo, sin tocar las estadísticas y FK, pero no reunió quórum.
BORRAR, ACTUALIZAR, INSERTAR
Entonces, nos decidimos por la opción sin bloqueo de tres operaciones. Casi tres... ¿Cómo hacer esto de forma más eficaz?
-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
BEGIN;
-- создаем временную таблицу с импортируемыми данными
CREATE TEMPORARY TABLE tmp(
LIKE dst INCLUDING INDEXES -- по образу и подобию, вместе с индексами
) ON COMMIT DROP; -- за рамками транзакции она нам не нужна
-- быстро-быстро вливаем новый образ через COPY
COPY tmp FROM STDIN;
-- ...
-- .
-- удаляем отсутствующие
DELETE FROM
dst D
USING
dst X
LEFT JOIN
tmp Y
USING(pk1, pk2) -- поля первичного ключа
WHERE
(D.pk1, D.pk2) = (X.pk1, X.pk2) AND
Y IS NOT DISTINCT FROM NULL; -- "антиджойн"
-- обновляем оставшиеся
UPDATE
dst D
SET
(f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
tmp T
WHERE
(D.pk1, D.pk2) = (T.pk1, T.pk2) AND
(D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- незачем обновлять совпадающие
-- вставляем отсутствующие
INSERT INTO
dst
SELECT
T.*
FROM
tmp T
LEFT JOIN
dst D
USING(pk1, pk2)
WHERE
D IS NOT DISTINCT FROM NULL;
COMMIT;
3.2. Importar posprocesamiento
En el mismo KLADR, todos los registros modificados deben pasar adicionalmente por un posprocesamiento: normalizarse, resaltarse las palabras clave y reducirse a las estructuras requeridas. Pero, como lo sabes - ¿Qué cambió exactamente?¿Sin complicar el código de sincronización, idealmente sin tocarlo en absoluto?
Si solo su proceso tiene acceso de escritura en el momento de la sincronización, entonces puede usar un activador que recopilará todos los cambios por nosotros:
-- целевые таблицы
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);
-- таблицы с историей изменений
CREATE TABLE kladr$log(
ro kladr, -- тут лежат целые образы записей старой/новой
rn kladr
);
CREATE TABLE kladr_house$log(
ro kladr_house,
rn kladr_house
);
-- общая функция логирования изменений
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
dst varchar = TG_TABLE_NAME || '$log';
stmt text = '';
BEGIN
-- проверяем необходимость логгирования при обновлении записи
IF TG_OP = 'UPDATE' THEN
IF NEW IS NOT DISTINCT FROM OLD THEN
RETURN NEW;
END IF;
END IF;
-- создаем запись лога
stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
CASE TG_OP
WHEN 'INSERT' THEN
EXECUTE stmt || 'NULL,$1)' USING NEW;
WHEN 'UPDATE' THEN
EXECUTE stmt || '$1,$2)' USING OLD, NEW;
WHEN 'DELETE' THEN
EXECUTE stmt || '$1,NULL)' USING OLD;
END CASE;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Ahora podemos aplicar disparadores antes de iniciar la sincronización (o habilitarlos a través de ALTER TABLE ... ENABLE TRIGGER ...):
CREATE TRIGGER log
AFTER INSERT OR UPDATE OR DELETE
ON kladr
FOR EACH ROW
EXECUTE PROCEDURE diff$log();
CREATE TRIGGER log
AFTER INSERT OR UPDATE OR DELETE
ON kladr_house
FOR EACH ROW
EXECUTE PROCEDURE diff$log();
Y luego extraemos tranquilamente todos los cambios que necesitamos de las tablas de registro y los ejecutamos a través de controladores adicionales.
3.3. Importación de conjuntos vinculados
Arriba consideramos casos en los que las estructuras de datos de origen y destino son las mismas. ¿Pero qué pasa si la carga desde un sistema externo tiene un formato diferente a la estructura de almacenamiento en nuestra base de datos?
Tomemos como ejemplo el almacenamiento de clientes y sus cuentas, la clásica opción “muchos a uno”:
CREATE TABLE client(
client_id
serial
PRIMARY KEY
, inn
varchar
UNIQUE
, name
varchar
);
CREATE TABLE invoice(
invoice_id
serial
PRIMARY KEY
, client_id
integer
REFERENCES client(client_id)
, number
varchar
, dt
date
, sum
numeric(32,2)
);Pero la descarga desde una fuente externa nos llega en forma de “todo en uno”:
CREATE TEMPORARY TABLE invoice_import(
client_inn
varchar
, client_name
varchar
, invoice_number
varchar
, invoice_dt
date
, invoice_sum
numeric(32,2)
);Obviamente, los datos del cliente se pueden duplicar en esta versión, y el registro principal es “cuenta”:
0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00
Para el modelo, simplemente insertaremos nuestros datos de prueba, pero recuerde: COPY ¡más eficiente!
INSERT INTO invoice_import
VALUES
('0123456789', 'Вася', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Петя', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Вася', 'B-03', '2020-03-16', 9999.00);Primero, resaltemos aquellos “recortes” a los que se refieren nuestros “hechos”. En nuestro caso, las facturas se refieren a clientes:
CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
client_inn inn
, client_name "name"
FROM
invoice_import;Para asociar correctamente las cuentas con los ID de los clientes, primero debemos encontrar o generar estos identificadores. Agreguemos campos debajo de ellos:
ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;Usemos el método de sincronización de tablas descrito anteriormente con una pequeña modificación: no actualizaremos ni eliminaremos nada en la tabla de destino, porque importamos clientes "solo para agregar":
-- проставляем в таблице импорта ID уже существующих записей
UPDATE
client_import T
SET
client_id = D.client_id
FROM
client D
WHERE
T.inn = D.inn; -- unique key
-- вставляем отсутствовавшие записи и проставляем их ID
WITH ins AS (
INSERT INTO client(
inn
, name
)
SELECT
inn
, name
FROM
client_import
WHERE
client_id IS NULL -- если ID не проставился
RETURNING *
)
UPDATE
client_import T
SET
client_id = D.client_id
FROM
ins D
WHERE
T.inn = D.inn; -- unique key
-- проставляем ID клиентов у записей счетов
UPDATE
invoice_import T
SET
client_id = D.client_id
FROM
client_import D
WHERE
T.client_inn = D.inn; -- прикладной ключ
En realidad, todo está en invoice_import Ahora ya tenemos el campo de contacto rellenado client_id, con el que insertaremos la factura.
Fuente: habr.com