Ten en cuenta que en las ciencias un error casi siempre tiene varias resoluciones, no obstante nosotros aquí te mostramos lo más óptimo y eficiente.
Solución:
Estrictamente hablando, el término “procedimientos almacenados” apunta a Procedimientos SQL en Postgres, introducido con Postgres 11. Relacionado:
- ¿Cuándo utilizar el procedimiento almacenado / la función definida por el usuario?
Tambien hay funciones, haciendo casi pero no exactamente lo mismo, y esos han estado ahí desde el principio.
Funciones con LANGUAGE sql son básicamente archivos por lotes con comandos SQL simples en un contenedor de funciones (y, por lo tanto, atómicos, siempre se ejecutan dentro de un único transacción) aceptando parámetros. Todas las declaraciones en una función SQL están planificadas En seguida, que es sutilmente diferente de ejecutar una instrucción tras otra y puede afectar el orden en el que se realizan los bloqueos.
Para algo más, el idioma más maduro es PL / pgSQL (LANGUAGE plpgsql). Funciona bien y se ha mejorado con cada lanzamiento durante la última década, pero sirve mejor como pegamento para los comandos SQL. No está diseñado para cálculos pesados (excepto con comandos SQL).
Las funciones PL / pgSQL ejecutan consultas como declaraciones preparadas. La reutilización de planes de consultas en caché reduce algunos gastos generales de planificación y los hace un poco más rápidos que las declaraciones SQL equivalentes, lo que puede tener un efecto notable según las circunstancias. También puede tener efectos secundarios como en esta pregunta relacionada:
- Problemas de PL / pgSQL cuando la función se usa dos veces (¿problema de almacenamiento en caché?)
Esto conlleva las ventajas y desventajas de las declaraciones preparadas, como se explica en el manual. Para consultas en tablas con distribución de datos irregular y parámetros variables SQL dinámico con EXECUTE puede funcionar mejor cuando la ganancia de un plan de ejecución optimizado para los parámetros dados supera el costo de la nueva planificación.
Dado que los planes de ejecución genéricos de Postgres 9.2 todavía se almacenan en caché para la sesión, pero, citando el manual:
Esto ocurre inmediatamente para declaraciones preparadas sin parámetros; de lo contrario, ocurre solo después de que cinco o más ejecuciones producen planes cuyo costo promedio estimado (incluidos los gastos generales de planificación) es más caro que el costo estimado del plan genérico.
Obtenemos lo mejor de ambos mundos la mayor parte del tiempo (menos algunos gastos generales adicionales) sin (ab) usar EXECUTE. Detalles en Novedades de PostgreSQL 9.2 del Wiki de PostgreSQL.
Postgres 12 introduce la variable de servidor adicional plan_cache_mode para forzar planes genéricos o personalizados. Para casos especiales, utilícelo con cuidado.
Usted puede ganar a lo grande con funciones del lado del servidor que evitar viajes de ida y vuelta adicionales al servidor de la base de datos desde su aplicación. Haga que el servidor ejecute tanto como sea posible a la vez y solo devuelva un resultado bien definido.
Evita anidar de funciones complejas, especialmente funciones de tabla (RETURNING SETOF record o TABLE (...)). Las funciones son cajas negras que se presentan como barreras de optimización para el planificador de consultas. Se optimizan por separado, no en el contexto de la consulta externa, lo que simplifica la planificación, pero puede resultar en planes menos que perfectos. Además, el costo y el tamaño del resultado de las funciones no se pueden predecir de manera confiable.
El excepción a esta regla son funciones SQL simples (LANGUAGE sql), que puede ser “en línea” – si se cumplen algunas condiciones previas. Lea más sobre cómo funciona el planificador de consultas en esta presentación de Neil Conway (material avanzado).
En PostgreSQL, una función siempre se ejecuta automáticamente dentro de una sola transacción. Todo tiene éxito o nada. Si ocurre una excepción, todo se revierte. Pero hay manejo de errores …
Es también por eso que las funciones son no exactamente “procedimientos almacenados” (aunque ese término se usa a veces, de manera engañosa). Algunos comandos como VACUUM, CREATE INDEX CONCURRENTLY o CREATE DATABASE no se pueden ejecutar dentro de un bloque de transacciones, por lo que no están permitidos en funciones. (Ni en los procedimientos de SQL, todavía, a partir de Postgres 11. Eso podría agregarse más adelante).
He escrito miles de funciones plpgsql a lo largo de los años.
Algunas HACER:
- Utilice SQL como lenguaje de funciones cuando sea posible, ya que PG puede incorporar las declaraciones
- Use INMUTABLE / STABLE / VOLATILE correctamente, ya que PG puede almacenar en caché los resultados si es inmutable o estable
- Use STRICT correctamente, ya que PG solo puede regresar null si alguna entrada es null en lugar de ejecutar la función
- Considere PL / V8 cuando no pueda usar SQL como lenguaje de funciones. Es más rápido que PL / pgSQL en algunas pruebas no científicas que ejecuté
- Use ESCUCHAR / NOTIFICAR para procesos de ejecución más larga que pueden ocurrir fuera de transacción
- Considere el uso de funciones para implementar la paginación como keyla paginación basada en LIMIT puede ser más rápida que la paginación basada en LIMIT
- Asegúrese de realizar una prueba unitaria de sus funciones
En términos generales, mover la lógica de la aplicación a la base de datos significará que es más rápido; después de todo, se ejecutará más cerca de los datos.
Creo (pero no estoy 100% seguro) que las funciones del lenguaje SQL son más rápidas que las que usan cualquier otro lenguaje porque no requieren cambio de contexto. La desventaja es que no se permite ninguna lógica de procedimiento.
PL / pgSQL es el más maduro y con todas las funciones de los lenguajes integrados, pero para el rendimiento, se puede usar C (aunque solo beneficiará a las funciones computacionalmente intensivas)
Puntuaciones y comentarios
Puedes respaldar nuestro estudio escribiendo un comentario y dejando una puntuación te estamos agradecidos.