update - Actualización a granel/por lotes/upsert en PostgreSQL

update on conflict postgres

Question

Estoy escribiendo una mejora de Django-ORM que intenta almacenar en caché los modelos y posponer el ahorro del modelo hasta el final de la transacción. Casi todo está hecho, sin embargo, encontré una dificultad inesperada en la sintaxis de SQL.

No soy un DBA, pero por lo que entiendo, las bases de datos no funcionan de manera eficiente para muchas pequeñas consultas. Pocas consultas más grandes son mucho mejores. Por ejemplo, es mejor usar inserciones de lotes grandes (digamos 100 filas a la vez) en lugar de 100 líneas simples.

Ahora, por lo que puedo ver, SQL realmente no proporciona ningún enunciado para realizar una actualización por lotes en una tabla. El término parece ser confuso , así que explicaré lo que quiero decir con eso. Tengo una matriz de datos arbitrarios, cada entrada describe una sola fila en una tabla. Me gustaría actualizar ciertas filas en la tabla, cada una usando datos de su entrada correspondiente en la matriz. La idea es muy similar a una inserción por lotes.

Por ejemplo: mi tabla podría tener dos columnas "id" y "some_col" . Ahora la matriz que describe los datos para una actualización por lotes consta de tres entradas (1, ''first updated'') , (2, ''second updated'') y (3, ''third updated'') . Antes de la actualización, la tabla contiene filas: (1, ''first'') , (2, ''second'') , (3, ''third'') .

Me encontré con esta publicación:

¿Por qué las inserciones / actualizaciones de lotes son más rápidas? ¿Cómo funcionan las actualizaciones por lotes?

que parece hacer lo que quiero, sin embargo, no puedo entender la sintaxis al final.

También podría eliminar todas las filas que requieren actualización y reinsertarlas usando un inserto por lotes, sin embargo, me resulta difícil creer que esto realmente funcione mejor.

Trabajo con PostgreSQL 8.4, por lo que algunos procedimientos almacenados también son posibles aquí. Sin embargo, como planeo abrir el proyecto de código fuente eventualmente, cualquier idea más portátil o formas de hacer lo mismo en un RDBMS diferente son bienvenidas.

Pregunta de seguimiento: ¿Cómo hacer una declaración por lotes "insertar-o-actualizar" / "levantar"?

Resultados de la prueba

He realizado 100 veces más de 10 operaciones de inserción repartidas en 4 tablas diferentes (por lo que 1000 inserciones en total). Probé en Django 1.3 con un backend PostgreSQL 8.4.

Estos son los resultados:

• Todas las operaciones se realizan a través de Django ORM: cada pase ~ 2.45 segundos ,
• Las mismas operaciones, pero hechas sin Django ORM: cada pase ~ 1,48 segundos ,
• Solo inserte operaciones, sin consultar la base de datos para valores de secuencia ~ 0.72 segundos ,
• Solo inserte operaciones, ejecutadas en bloques de 10 (100 bloques en total) ~ 0.19 segundos ,
• Solo inserte operaciones, un gran bloque de ejecución ~ 0.13 segundos .
• Solo inserte operaciones, aproximadamente 250 declaraciones por bloque, ~ 0.12 segundos .

Conclusión: ejecutar tantas operaciones como sea posible en una sola conexión.execute (). Django en sí introduce una sobrecarga sustancial.

Descargo de responsabilidad: no introduje ningún índice aparte de los índices de clave primaria predeterminados, por lo que las operaciones de inserción podrían correr más rápido debido a eso.

Answer 1

Inserción a granel

Puede modificar la inserción masiva de tres columnas por Ketema:

INSERT INTO "table" (col1, col2, col3)
  VALUES (11, 12, 13) , (21, 22, 23) , (31, 32, 33);

Se vuelve:

INSERT INTO "table" (col1, col2, col3)
  VALUES (unnest(array[11,21,31]), 
          unnest(array[12,22,32]), 
          unnest(array[13,23,33]))

Reemplazar los valores con marcadores de posición:

INSERT INTO "table" (col1, col2, col3)
  VALUES (unnest(?), unnest(?), unnest(?))

Tienes que pasar arreglos o listas como argumentos a esta consulta. Esto significa que puedes hacer grandes inserciones a granel sin hacer una concatenación de cadenas (y todas sus amenazas y peligros: inyección sql y citando el infierno).

Actualización masiva

PostgreSQL ha agregado la extensión FROM para ACTUALIZAR. Puedes usarlo de esta manera:

update "table" 
  set value = data_table.new_value
  from 
    (select unnest(?) as key, unnest(?) as new_value) as data_table
  where "table".key = data_table.key;

Al manual le falta una buena explicación, pero hay un ejemplo en la lista de correo postgresql-admin . Traté de explicarlo:

create table tmp
(
  id serial not null primary key,
  name text,
  age integer
);

insert into tmp (name,age) 
values (''keith'', 43),(''leslie'', 40),(''bexley'', 19),(''casey'', 6);

update tmp set age = data_table.age
from
(select unnest(array[''keith'', ''leslie'', ''bexley'', ''casey'']) as name, 
        unnest(array[44, 50, 10, 12]) as age) as data_table
where tmp.name = data_table.name;

También hay other posts en StackExchange explicando UPDATE...FROM.. utilizando una cláusula VALUES lugar de una subconsulta. Podrían ser más fáciles de leer, pero están restringidos a un número fijo de filas.

Answer 2

Desactiva la confirmación automática y solo haz una confirmación al final. En SQL simple, esto significa emitir BEGIN al comienzo y COMMIT al final. Debería crear una function para hacer un upsert real.

Answer 3

He usado 3 estrategias para el trabajo transaccional por lotes:

1. Genere declaraciones SQL sobre la marcha, concatenarlas con punto y coma, y ​​luego envíe las declaraciones en una sola toma. He hecho hasta 100 inserciones de esta manera, y fue bastante eficiente (hecho contra Postgres).
2. JDBC tiene capacidad de procesamiento por lotes integrada, si está configurada. Si genera transacciones, puede descargar sus declaraciones JDBC para que realicen transacciones de una vez. Esta táctica requiere menos llamadas a la base de datos, ya que todas las instrucciones se ejecutan en un solo lote.
3. Hibernate también admite el procesamiento por lotes JDBC a lo largo de las líneas del ejemplo anterior, pero en este caso se ejecuta un método flush() contra la Session Hibernate, no la conexión JDBC subyacente. Cumple lo mismo que el procesamiento por lotes JDBC.

Por cierto, Hibernate también admite una estrategia de lotes en la recolección de colecciones. Si anota una colección con @BatchSize , al buscar asociaciones, Hibernate usará IN lugar de = , lo que generará menos instrucciones SELECT para cargar las colecciones.

Answer 4

Inserciones a granel se pueden hacer como tales:

INSERT INTO "table" ( col1, col2, col3)
  VALUES ( 1, 2, 3 ) , ( 3, 4, 5 ) , ( 6, 7, 8 );

Insertará 3 filas.

La actualización múltiple se define mediante el estándar SQL, pero no se implementa en PostgreSQL.

Citar:

"De acuerdo con el estándar, la sintaxis de la lista de columnas debe permitir asignar una lista de columnas a partir de una única expresión con valores de fila, como una selección secundaria:

ACTUALIZAR cuentas SET (contact_last_name, contact_first_name) = (SELECT last_name, first_name FROM salesmen WHERE salesmen.id = accounts.sales_id); "

Referencia: http://www.postgresql.org/docs/9.0/static/sql-update.html

Answer 5

es bastante rápido completar json en recordset (postgresql 9.3+)

big_list_of_tuples = [
    (1, "123.45"),
    ...
    (100000, "678.90"),
]

connection.execute("""
    UPDATE mytable
    SET myvalue = Q.myvalue
    FROM (
        SELECT (value->>0)::integer AS id, (value->>1)::decimal AS myvalue 
        FROM json_array_elements(%s)
    ) Q
    WHERE mytable.id = Q.id
    """, 
    [json.dumps(big_list_of_tuples)]
)