Bloqueos de tabla en Postgres durante DDL para alterar renombrar una tabla.
Tengo alrededor de 5 tablas bastante grandes de 100 millones de registros cada una, las cuales cambian significativamente cada día y necesitan ser accedidas para generar informes detallados a través de nuestras APIs. Actualmente, cargo los datos a un esquema stg desde nuestro almacén de datos, luego utilizo funciones para insertar esas tablas con “_new”, luego las indexo y les cambio el nombre, elimino mi tabla objetivo final y le cambio el nombre a mi tabla “_new” con el nombre de la tabla objetivo final. Originalmente, estaba gestionando esto en una sola función, pero estaba obteniendo bloqueos de consultas cuando había menos de 30 consultas conectadas, por lo que la función se bloqueaba y también se bloqueaban las consultas. No estoy muy familiarizado con las transacciones y entiendo que los comandos DDL están bloqueando las tablas, lo cual tiene sentido, solo quiero asegurarme de por qué las consultas están bloqueando el cambio de nombre o qué comandos debo usar para leer sin bloqueos. En este punto, he dividido la ETL, la indexación y el cambio de nombre en 3 funciones separadas, lo cual parece estar funcionando, ya que la ETL y la indexación tardan más tiempo y el cambio de nombre se realiza en milisegundos. Me gustaría saber si alguien tiene un enfoque mejor o si hay alguna manera estándar de hacer esto para evitar bloqueos por completo.
Gracias.
davy.ai
Como experto en tecnología de la información especializado en PostgreSQL, stored procedures, locking, ETL y DDL, existen varios enfoques que se pueden tomar para minimizar los bloqueos y optimizar el proceso de consulta para informes diarios en tablas grandes y constantemente cambiante.
Una posible solución sería implementar una forma de replicación o transmisión de datos en la base de datos para mover los datos modificados del entorno de producción a una instancia de base de datos de informes separada. Esto permitiría realizar consultas en la base de datos de informes sin afectar el rendimiento en el lado de producción.
Otra posibilidad sería usar transacciones explícitas dentro del proceso de ETL para minimizar la contención de bloqueos. Esto podría involucrar dividir el proceso de ETL en transacciones más pequeñas y manejables, con lotes de datos más pequeños para minimizar el tiempo de espera del bloqueo.
Además, optimizar el proceso de indexación utilizando índices parciales o paralelizando algunas de las tareas de creación de índices podría ayudar a minimizar la contención de bloqueos y mejorar el rendimiento.
En general, es importante considerar cuidadosamente las necesidades y restricciones específicas del entorno de datos y utilizar las herramientas y estrategias adecuadas para optimizar el rendimiento y minimizar los bloqueos durante los procesos de informes diarios.