Cómo acelerar las consultas recursivas con las nuevas funciones de Postgres
TuBrief 편집팀
2026년 8월 7일
0
Computing/Software원본 영상을 바탕으로 AI의 도움을 받아 작성했습니다. 원본 영상이 기준입니다.
커뮤니티의 다른 글
댓글 (0)
Log in to leave a comment
아직 작성된 글이 없습니다
원본 영상을 바탕으로 AI의 도움을 받아 작성했습니다. 원본 영상이 기준입니다.
Log in to leave a comment
아직 작성된 글이 없습니다
Las CTE recursivas utilizadas para explorar relaciones padre-hijo o rutas múltiples en sistemas heredados colapsan a medida que los datos se profundizan. En el momento en que la fan-out del índice vacila, el motor crea tablas temporales en cada paso y repite las auto-uniones (self-joins). El conjunto de resultados llena la memoria y se produce un spillover hacia el disco. El I/O se bloquea y la CPU se agota. La cláusula nativa CYCLE introducida en PostgreSQL 14 reduce el costo de búsqueda en arreglos, pero a medida que la profundidad de exploración aumenta, persisten las repetidas búsquedas de índices secundarios y el costo de copia de memoria de tuplas grandes.
Para aumentar la velocidad de consulta y ahorrar CPU, debes cambiar a las funciones de grafos nativos basadas en SQL/PGQ. Primero, utiliza la cláusula CREATE PROPERTY GRAPH manteniendo la estructura física de las tablas relacionales existentes. Especifica las tablas de entidades individuales como VERTEX TABLES y las tablas de intersección de muchos a muchos como EDGE TABLES. Segundo, dado que las columnas especificadas como claves no se incluyen automáticamente en las propiedades del grafo, asegúrate de listar los campos en la cláusula PROPERTIES para conservar los permisos de filtrado de GRAPH_TABLE. Tercero, cambia las consultas cuya profundidad de exploración supere los 3 niveles, cuya tasa de ramificación promedio sea de 10 o más, o cuyo número de tuplas supere los 10 millones, al patrón GRAPH_TABLE MATCH. El código recursivo complejo se reduce a un solo patrón de línea y el uso del búfer de memoria disminuye notablemente.
Los errores de inserción duplicada que ocurren cuando múltiples subprocesos introducen datos simultáneamente en un entorno distribuido no se pueden solucionar con bloqueos de aplicación. Se genera sobrecarga de RTT de red y estallan condiciones de carrera (race conditions) en el resquicio justo antes de la confirmación de la transacción (commit). Debes internalizar los bloqueos a nivel de fila mediante las cláusulas INSERT ON CONFLICT y MERGE. La cláusula ON CONFLICT utiliza una técnica de inserción especulativa para bloquear la página del índice único e intentar la inserción. Si ocurre un conflicto, se ramifica inmediatamente a DO UPDATE o DO NOTHING para reducir la frecuencia de sesiones en interbloqueo (deadlock).
Al utilizar sentencias atómicas, cambiar el nivel de aislamiento de transacciones tiene un gran efecto de propagación. En READ COMMITTED, una transacción rezagada espera a que una transacción anterior confirme y luego vuelve a leer la tupla más reciente para procesarla de manera segura. Sin embargo, si lo elevas a REPEATABLE READ o SERIALIZABLE, cuando choca con una transacción anterior, emite inmediatamente un error de fallo de serialización sin esperar y realiza un rollback. Para reducir el costo de rollback y evitar el agotamiento del grupo de conexiones (connection pool), debes aplicar un bucle de reintento solo a los códigos de error 40001 y 40P01. Agrega un valor de fluctuación (jitter) aleatorio al tiempo de espera predeterminado para dispersar los momentos de intento, y limita el número máximo de reintentos entre 3 y 5 antes de lanzarlo a la capa de negocio superior.
El inicio de la gestión del almacenamiento es captar el momento en que la eficiencia del escaneo de índices disminuye debido a la acumulación de fragmentación de bloques y tuplas muertas. Debido a las características de la arquitectura MVCC, las tuplas muertas por UPDATE o DELETE no se devuelven inmediatamente al sistema operativo, sino que quedan como fragmentación. Al observar conjuntamente el indicador n_dead_tup de la vista pg_stat_user_tables y la extensión pgstattuple, si dead_tuple_ratio supera el 20 por ciento o la proporción de free_space ocupa el 30 por ciento, debes recuperar el espacio en disco. Si se ignora, la lectura de bloques de I/O aumentará y la eficiencia del grupo de búferes (buffer pool) se arruinará.
Para recuperar el almacenamiento en segundo plano sin detener el servicio, utiliza la herramienta pg_repack. Primero, crea una tabla sombra (shadow table) de la tabla de origen de destino y adjunta un desencadenador de registro (logging trigger) para rastrear los cambios. Segundo, copia masivamente las tuplas válidas a la tabla sombra, regenera los índices de forma asincrónica y en paralelo, y luego cambia el catálogo del sistema. Tercero, ejecuta el comando de proceso en segundo plano directamente desde la terminal.
`bash pg_repack
--dbname=production_db
--table=public.orders
--jobs=4
--wait-timeout=10
--no-superuser-check
`
Para prevenir la contención de bloqueos, establece SET lock_timeout = '3s'; a nivel de sesión para que, si no se puede asegurar un bloqueo en un plazo de 3 segundos, no permanezca en la cola y genere un error inmediatamente. Agrega la opción --wait-timeout=10 para cortar situaciones en las que las consultas posteriores se bloqueen en cadena.
Si el ciclo de recopilación de información estadística está desalineado y el plan de consulta cambia repentinamente, conduce a un fallo de producción. En lugares donde se concentran trabajos de gran volumen, la estadística y la distribución real de los datos difieren, provocando un cambio de plan (plan flip). Si el optimizador elige una unión de bucles anidados (nested loop join) en lugar de un escaneo de índice, se producen picos de CPU y cuellos de botella de I/O. Es necesario incluir el módulo auto_explain en shared_preload_libraries y establecer log_min_duration en 500 milisegundos para registrar el plan de ejecución real de las consultas lentas en el registro del servidor y así poder diagnosticarlas.
Para fijar por la fuerza la ruta de ejecución de una consulta específica, utiliza un módulo de pistas (hints). Después de capturar la consulta problemática en los registros, carga la extensión pg_hint_plan para agregar comentarios antes de la sentencia SQL o registra la consulta en la tabla de catálogo hint_plan.hints. Si te encuentras en una situación en la que es difícil implementar el código fuente, puedes inyectar directamente una cadena de pistas estáticas en el nivel del catálogo para fijar el plan inmediatamente sin necesidad de redesplegar. Es la forma más rápida de proteger la velocidad de respuesta de las consultas en situaciones de emergencia donde la recolección de estadísticas o la regeneración de índices toman tiempo.