Recomendaciones para trabajar con PostgreSQL / Sudo Null IT News

¡Hola usuario_actual()!

Bienvenido a un apasionante viaje por el mundo del desarrollo con PostgreSQL, un potente sistema de gestión de bases de datos. En este artículo, profundizaremos en los aspectos clave de la creación de código y estructura para PostgreSQL, con énfasis en estrategias de desarrollo efectivas.

Estas no son reglas, sino recomendaciones útiles que te ayudarán a evitar situaciones desagradables en el futuro. La experiencia presentada es mía y por supuesto puede ser un poco subjetiva ya que estoy relativamente lejos de ser un súper desarrollador 🙂

Aquí consideraremos puntos que se relacionan específicamente con el desarrollo y su organización (enfoque). No tocaremos la configuración del servidor, copias de seguridad/réplicas/fragmentos, etc.

Intentaré ser breve. Brevemente y punto por punto:

Normalización de la estructura.

Al aprender programación SQL, todos aprendimos que existen niveles de normalización en las bases de datos. Pero debemos optimizar no sólo la ubicación de los datos en diferentes objetos, sino también estos objetos (tablas, funciones, roles, accesos) en diferentes esquemas.

Utilice circuitos separados para diferentes módulos

Supongamos que necesita implementar un sistema para enviar notificaciones automáticas a los dispositivos Android de los clientes. Está escribiendo un servicio que funcionará con FCM. Escriba funciones en su base de datos que produzcan listas de texto simbólico (sí, hay muchos campos en general).

Entonces, es necesario que tales manejas para diferentes módulos (backends) estaban en diagramas separados.

Utilice grupos de roles y asigne subvenciones específicamente a roles

Supongamos que ha implementado el punto anterior, ahora su servicio debería conectarse a la base de datos y ejecutar sus consultas. ¿Pero bajo qué usuario? ¿No se ejecutará en postgres o admin?)
Crearemos un usuario separado para él y le permitiremos iniciar sesión con él. Hmm, ¿pero aún necesitas otorgar derechos? Bueno, démoslo.

Recomiendo utilizar el siguiente enfoque: crear roles, configurarlos como políticas de grupo y otorgar permisos específicamente a los roles.

Por ejemplo, estos son los roles:

• api_role: para acceder a las funciones internas de la API

• client_api_role: para acceso de clientes móviles

• admin_api_role: para acceder al sitio (bueno, por ejemplo, este es el panel de administración)

• notify_role: para acceder a los sistemas de notificación

• desarrollador: lo que está disponible para los desarrolladores (por ejemplo, parte de las tablas está disponible para lectura en producción)

  Y luego cree usuarios y conéctelos a roles. Y otorgar subvenciones específicamente para roles. No los hagas personales.

En cualquier caso, respete las siguientes reglas:

1. Para cada nuevo backend (aplicación): su propio usuario

2. El administrador solo debe ser el administrador del clúster + algunos desarrolladores (por ejemplo, líder técnico)

Usar extensiones

Postgresql es un poderoso DBMS. Pero tampoco puede ser universal. No debes asumir que todo se hace utilizando herramientas estándar de PostgreSQL. Además, no intentes escribir tus propios dispositivos/sistemas de nada.

Muchas herramientas buenas y listas para usar existen desde hace mucho tiempo y se utilizan ampliamente. Lo tomamos, lo estudiamos y lo aplicamos, nada complicado.

Si no sabe exactamente qué es mejor usar en este o aquel caso, intentamos probarlo, promocionarlo y analizarlo todo, o puede pedir ayuda, por ejemplo, en HabrQA o Canal TG de PostgreSQL

Utilice diferentes esquemas para diferentes extensiones

Y, volviendo a la primera recomendación: para cada extensión, use su propio esquema (o al menos todas las extensiones en un esquema de extensión común):

-- создадим схему
CREATE SCHEMA hstore;

-- установим расширение в его схему
CREATE EXTENSION hstore WITH SCHEMA hstore;

-- не забываем сразу подключить схему в search_path
ALTER SYSTEM SET search_path="$user", 'public', 'hstore';

Utilice pg_notify

Supongamos que tiene un backend que debe tomar datos de la base de datos y procesarlos.
La solución estándar sería algo como esto:
update table set processed_at = now() returning *;

Y esta es una opción bastante funcional, pero ¿cuándo hacerlo? Por ejemplo, ¿una vez cada 5 segundos? – Sí, esta opción es adecuada.

Pero, ¿qué pasa si esos registros que necesitamos extraer no aparecen con tanta frecuencia (menos de una vez cada 5 segundos), pero debemos responder a ellos muy rápidamente?

Sí, aquí es donde pg_notify viene en nuestra ayuda.
Simplemente configuras tu servicio para escuchar el canal deseado y cuando recibas un mensaje, podrás solicitar los datos a través de una solicitud (también puedes enviarlos a través de Notify como último recurso).

Rápido, fresco y sin suciedad en el sistema.

Introduces la fecha y hora en la marca de tiempo.

Adquiera el hábito de almacenar siempre marca de tiempoTZ y algún día te lo agradecerán mucho.
Este es un error muy común tanto para principiantes como para desarrolladores experimentados.

Sí, esto no es una regla, sucede que necesitas usarlo sin zona horaria. Pero si no sabes qué usar, úsalo con zona horaria.

Un poco sobre cómo se almacena la marca de tiempo en la base de datos:
PostgreSQL no almacena la zona horaria que usted le proporciona; convierte todo a UTC y lo almacena de esa manera.
Y cuando necesitas dárselo a un cliente, se lanza a la zona del cliente. Cuando le das un filtro de tiempo, ella lo convierte en UTC y trabaja con él.
Creo que ahora está claro.

Un poco de programación, seguimiento y optimización.

Decidí limitar este bloque precisamente a aquellos puntos que de una forma u otra están relacionados con la programación misma. Quizás algún consejo sea dudoso, pero lo mencionaré de todos modos.

No olvide establecer la configuración correcta del servidor.

Aunque este punto en general otorgada, pero sí, sí, la configuración de PostgreSQL es inicialmente tal que su clúster puede comenzar sobre una base de hierro fundido. Pero no tienes que hacer eso 🙂

Tan pronto como haya creado un clúster que será de desarrollo/producción, e incluso si solo hay 1 o 2 clientes allí, configure el servidor para su hardware y tareas. De hecho, un buen número de problemas están relacionados de una forma u otra con la configuración del clúster.

En Internet se puede encontrar información sobre qué configuraciones necesita ver de inmediato y cuáles puede ver más tarde.

Una regla: no se exceda, de lo contrario el servidor puede perder el conocimiento 🙂
Por ejemplo:
darle al cliente 10 GB de RAM para operaciones + conectar 100 clientes
o establezca Shared_buffers = 100% RAM y, por lo tanto, piense por qué falla el sistema
o simplemente establezca este parámetro irrealmente grande y véalo en pg_stat_activity LWLock: BufferMapping

Utilice métricas. Utilice pg_stat_statements

También recomiendo instalar inmediatamente extensiones para monitorear solicitudes (por ejemplo, pg_stat_statements).

Aunque es raro, vaya a la página del producto, mire las estadísticas, reinícielas y agréguelas. No es necesario esperar a que el departamento de control de calidad derribe su ventana con ambas piernas.

En general, configure alertas para algunas métricas.!

Es mejor utilizar CTE y consultas largas en funciones en lugar de tablas temporales.

Quizás este punto suene incorrecto.

PostgreSQL siempre coloca tablas temporales en el disco, lo que llevará mucho tiempo. Utilice expresiones de tabla. Y es mejor escribir todo con un solo operador. Porque incluso en el aislamiento de función estándar (con lectura confirmada), observará diferentes datos en diferentes declaraciones de la función.

No hagas secciones donde puedas (y donde tampoco puedas)

Sí, sí, las secciones son geniales. Pero recomendaría usarlos sólo en determinados casos:

• mesa enorme

• para una rápida eliminación

• Si tiene el 100%, una solicitud irá a una sección específica.

• separación de datos residuales

  • Probablemente éste sea un caso especial del punto anterior. Por ejemplo, tiene tareas para un servicio externo. Lo que funcionó – en el archivo por mes, lo que no funcionó – en la tabla operativa (adjuntar de forma predeterminada)

En cualquiera de estos casos, es sumamente importante que la consulta acceda a una pequeña cantidad de particiones (la consulta siempre tiene una condición de clave de partición).

No olvides hacer estadísticas adicionales.

Sí, cuando el programador representa el escape de manera incorrecta, puede terminar con consultas muy lentas.

Dejame darte un ejemplo:
Tienes pacientes en tu tabla: nombre completo, sexo, signo de embarazo, otros campos

Por los datos que conocemos: el 90% de los pacientes son niñas, de las cuales el 50% están embarazadas.
¿Qué sabe la base de datos después de recopilar estadísticas? El 10% son hombres y el 45% son personas embarazadas.

Y cuando preguntes por todos los hombres embarazadas, el planificador dirá que será el 45% de toda la mesa.
Así que gracias estadísticas avanzadaspuede construir conexiones entre las columnas y luego el planificador entenderá que si el atributo es género = masculino, no hay mujeres embarazadas.

Puede que el ejemplo no sea el mejor. Pero en las tareas analíticas, las estadísticas avanzadas ayudarán al planificador a no tropezar y elegir un bucle anidado, sino a elegir alguna combinación hash.

Compruebe también sus planes de consulta; si el número planificado de filas difiere del número real en al menos un orden de magnitud, tiene problemas con las estadísticas.

Trate de no meter todo en TOAST

Probablemente valga la pena agregar inmediatamente el punto de que no es necesario almacenar datos analíticos en TOAST (por ejemplo, json/text con el parámetro de almacenamiento predeterminado).
Puede encontrarse con problemas de rendimiento muy grandes.

Hace literalmente medio año tuve ese problema en mi práctica. Resultó que para crear el informe tomamos 1 campo para el cálculo de json (que es bastante grande).
Tomaron el significado del json a una tableta. La solicitud comenzó a ejecutarse no durante 3 horas, sino solo entre 1 y 4 segundos.

Utilice explicar en lugar de crear índice

Sí, sí, muy a menudo hay problemas cuando la solicitud comienza a ralentizarse y el programador inmediatamente А почему бы не сделать индекс отдельный ?.

Este es un tema muy doloroso. Siéntese, ejecute, explique, analice, analícelo por completo y encuentre cuellos de botella.

Créame, esto ayuda mucho. _{y también aumenta tu frecuencia cardíaca 🙂}

Y, en general, la cuestión del uso de determinados índices para determinadas tareas, así como de la configuración de los índices, es un tema completamente aparte.

Utilice visualizadores de planes

Ver el plan de consultas en PGAdmin no es muy conveniente. Sí, puedes ver todo lo que contiene, pero hay herramientas más convenientes que facilitarán esta tarea.

Por ejemplo yo uso este.

Busque cuidadosamente signos de variabilidad y concurrencia en las funciones que cree.

Lamentablemente nadie le presta atención.

Establezca como regla comprobar siempre la volatilidad de las funciones (volátil/estable/inmutable).
En mi práctica, me encontré con un rastrillo cuando escribí funciones API internas (funciones auxiliares) con el atributo volátil (que es el predeterminado), que simplemente proporcionaba información.
Y cada vez esto tuvo que corregirse, cuando en producción tales funciones convirtieron el tiempo de ejecución de consultas OLTP ordinarias de 0,05 a 0,1 segundos a 4 a 6 segundos.

En su mayor parte, no he encontrado ningún problema con el paralelismo, pero es mejor no dejar este parámetro como predeterminado (solo lea con anticipación cuándo lo necesita y cuándo no).

Además, no olvide que las funciones con el parámetro volátil funcionan en su propia instantánea (instantánea en MVCC) y puedo ver información más actual en la base de datos que el operador madre; este comportamiento puede romper algo en su lógica de negocios.

-- не описываю прям всё, чисто ради примера

-- создадим таблицу
create table users(id, created_at);

-- генерим даныне
insert into users()... generate_series(1,10)

-- создаём функцию волатайл, которая возвращает created_at по ид
create function get_user_created_at(user_id) returning created_at ....

-- делаем запрос:
select created_at, get_user_created_at(id), pg_sleep(1)
from users

-- в параллельной транзакции делаем
update users set created_at - now()

-- ну и в запросе, который написан в предыдущем операторе
-- мы увидим расхождения в created_at,
-- поскольку функция volatile отрабатывает в своём (свежем) снапшоте
-- и увидит там created_at из запроса в последнего оператора

Implementar cerraduras correctamente

Un tema muy doloroso.
No diré mucho aquí, simplemente daré un enlace a un artículo muy bueno que me ayudó a deshacerme de una gran cantidad de puntos muertos.

Organización de la codificación.

Y así, es hora de comenzar el último bloque. Parte de la organización de la escritura del código, su diseño, etc. se describe con más detalle aquí.

Haz un diagrama especial deven prode

Digamos que en su proyecto a algunos desarrolladores de bases de datos se les permite acceder al producto con derechos de solo lectura, por ejemplo, necesitarán realizar algunas depuraciones y, a menudo, reproducir consultas (por la misma razón, explique analizar).

Por eso te aconsejo que hagas un circuito especial, por ejemplo dev. Dale acceso completo allí, permíteles copiar, modificar y ejecutar funciones en su entorno de pruebas (bueno, no podemos hacer una copia completa del producto para probarlo).

Observo que será necesario recordar a los desarrolladores que creen letreros con el parámetro UNLOGGED, para que no accedan a la réplica.

Mantenga el código en un repositorio y versionelo

No es necesario escribir mensajes en una solicitud de fusión de algún servicio backend indicando que se necesita una corrección en tal o cual procedimiento, cambiar el código allí (o, en general, mantener una nueva versión del procedimiento/solicitudes para agregar campos, etc. en los comentarios)

Cree una carpeta separada y almacene el código en el repositorio, siempre tenga un historial de modificación de ciertos objetos en la base de datos.

La forma más sencilla de hacer esto es simplemente hacer una instantánea de la estructura de la base de datos y mostrarla en forma de diferentes archivos (cada objeto tiene su propio archivo). Las herramientas disponibles incluyen pgCodeKeeper, DataGrip, etc.

Actualmente estamos probando el sistema usando pg_dump + pg_query_go.

Vierta en los alimentos sólo a través de herramientas, no lo haga con las manos.

Y ahora, cuando el código ya esté en el repositorio, puedes realizar migraciones desde él y subirlos a producción.

Utilice herramientas CICD e implemente cambios en la base de datos en producción mediante utilidades especiales.
Un ejemplo de tales utilidades es: liquibase, flyway, pgCodeKeeper, pg-migrator y muchas otras.

Todo depende de cómo creas las migraciones y cómo es tu base de datos.

Nunca realice cambios en el producto sin enviarlos al código del repositorio o a algún otro lugar. Recogerás problemas, 100% garantía.

Escribe comentarios en todas partes

Obligar a los programadores a escribir comentarios para casi todas las funciones, tablas, sus columnas, etc.
Esto ayudará a los nuevos desarrolladores a respaldar el proyecto en el futuro, así como a generar documentación.

^{Sí, sí, el punto es banal hasta el punto de la vergüenza.}

Utilice revisión cruzada

Creo que no hay necesidad de comentarios aquí.

Acordar con el equipo un estilo de codificación unificado

Y parecería que los últimos 3 puntos se dan por sentados; muy a menudo esto no se cumple.

Si surgen problemas, utilice linters, por ejemplo pgFormatter.

Conclusión

Siguiendo ciertos principios y utilizando herramientas avanzadas de desarrollo y mantenimiento de PostgreSQL, puede garantizar la estabilidad, el rendimiento y la seguridad tanto de la base de datos como del código base de su proyecto.