Imagen de perfil de Andrew Vogel

byAndrew Vogelon2 de abril de 2019

La semana pasada, me llamaron para saltar a un proyecto de Rails que necesitaba algunas optimizaciones de rendimiento. Nuestro enfoque elegido fue tomar un proceso de búsqueda y actualización en masa de ActiveRecord land y mover todo a Postgres. La mejora de rendimiento fue enorme para grandes conjuntos de récords. En el extremo superior, vimos que los tiempos de solicitud iban de > 5 minutos a menos de 2 segundos.

Había usado SQL y Postgres antes, pero no estaba totalmente familiarizado con su conjunto completo de características. Al principio, me emparejé con un compañero de trabajo que tiene un conocimiento más profundo de Postgres. Después de unos días de escribir consultas y refactorizar el conjunto de pruebas, todo estaba verde y estaba listo para implementarlo en la etapa de pruebas.

Aquí hay algunas cosas interesantes de SQL y PostgreSQL que aprendí en el camino.

Las expresiones CTE

Aunque no son exclusivas de PostgreSQL, las expresiones CTE o de tablas Comunes, son una forma de escribir consultas reutilizables en un sistema de base de datos.

Digamos que tenemos 2 tablas: books y authors.

Mesa de Libros
id título author_id género
1 Digital Fortress 1 thriller
2 El Código Da Vinci 1 thriller
3 Harry Potter y La Cámara de los Secretos 2 la fantasía
los Autores de la Tabla
id nombre edad
1 Dan Brown 54
2 J. K. Rowling 53

podemos escribir un simple realmente CTE para conseguir los libros con los autores:

with authors_and_books as ( SELECT b.id as book_id, b.title, a.name as author_name FROM books b JOIN authors a on b.author_id = a.id; ); 

a Continuación, la selección de nuestra CTE, obtenemos los siguientes:

SELECT * FROM authors_and_books; 
book_id título author_name
1 Digital Fortress Dan Brown
2 El Código Da Vinci Dan Brown
3 Harry Potter y La Cámara de los Secretos J. K. Rowling

Este ejemplo es simple, pero puede ver cómo esto puede ser útil cuando necesita reutilizar consultas complejas.

Tablas temporales (PostgreSQL)

Otra característica de base de datos realmente útil es la capacidad de crear tablas temporales. En mi caso, esto fue muy útil para organizar datos «sin procesar» que se publicaban desde el front-end. Para crear una tabla temporal, deberá definir su esquema como su instrucción create table .. normal.

CREATE TEMPORARY TABLE temp_isbns ( title varchar(255) NOT NULL, isbn varchar(255) NOT NULL, author_name varchar(255) NOT NULL ) ON COMMIT DROP; 

La parte importante aquí es ON COMMIT al final. Tendrás que decirle a Postgres cómo manejar la tabla temporal al final del bloque de transacción. DROP le dice a Postgres que elimine la tabla temporal al final del bloque de transacción. Los documentos de Postgres describen más opciones de confirmación de cambios.

COALESCE

La función COALESCE devuelve el primer valor no nulo que se le pasa. Esta función acepta un número ilimitado de argumentos y devuelve el primer argumento no nulo, evaluado de izquierda a derecha.

Aquí hay algunos ejemplos de lo que podría parecer:

Unen Ejemplo 1

select coalesce(null, 1); 

se unen

se Unen Ejemplo 2

select coalesce(null, null, 1, null); 

se unen

se Unen Ejemplo 3

select coalesce(2, null, 1); 

se unen

los upserts

Upserting, o la actualización y la inserción, es un superful característica en Postgres. Le permite manejar inserciones con resolución de conflictos si ya existe un registro en la base de datos.

¿Recuerdas nuestra mesa de libros?

Mesa de Libros
id título author_id género
1 Digital Fortress 1 thriller
2 El Código Da Vinci 1 thriller
3 Harry Potter y La Cámara de los Secretos 2 la fantasía

los upserts Ejemplo 1

vamos a insertar algunos registros en los libros, pero si el libro existe, vamos a elegir no hacer nada:

INSERT INTO books (id, title, author_id, genre) VALUES (3, 'Harry Potter and The Chamber of Secrets', 2, 'fantasy'), (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy') ON CONFLICT DO NOTHING; 

Ahora echemos un vistazo a nuestra tabla:

SELECT * FROM books; 
id título author_id género
1 Digital Fortress 1 thriller
2 El Código Da Vinci 1 thriller
3 Harry Potter y La Cámara de los Secretos 2 la fantasía
4 Harry Potter y El misterio del Príncipe 2 la fantasía

Los upserts Ejemplo 2

Otros otro lado, vamos a insertar registros en libros y actualización de la grabar con el ID correspondiente. Según los documentos de Postgres, deberá usar la tabla EXCLUDED para hacer referencia a los valores propuestos para la inserción.

INSERT INTO books (id, title, author_id, genre) VALUES (3, 'Harry Potter and The Goblet of Fire', 2, 'fantasy'), (4, 'Harry Potter and The Half Blood Prince', 2, 'fantasy') ON CONFLICT (id) DO UPDATE SET title = EXCLUDED.title; 

Mirando nuestra mesa por última vez:

SELECT * FROM books; 
id título author_id género
1 Digital Fortress 1 thriller
2 El Código Da Vinci 1 thriller
3 Harry Potter y El Cáliz de Fuego 2 la fantasía
4 Harry Potter y El misterio del Príncipe 2 la fantasía

Gracias por seguir con este blog. Si hay una función de Postgres en particular que le gustaría escuchar, no dude en comunicarse con [email protected]

¿Fue útil este post? Compártelo con otros.

  • Logo de Twitter Tweet
  •  Logo de Facebook Compartir
  •  Logo de Linkedin Publicar

Deja una respuesta

Tu dirección de correo electrónico no será publicada.