Rendimiento maestro de PostgreSQL: prácticas de WeWard para bases de datos eficientes

En WeWard, donde cada milisegundo cuenta para mejorar la experiencia del usuario, emprendimos un ambicioso viaje para transformar PostgreSQL en un motor de rendimiento excepcional. Este artículo explora los desafíos a los que nos enfrentamos, las soluciones implementadas y las mejores prácticas que nos permitieron optimizar PostgreSQL de manera significativa.

🎯 ¿Por qué es crucial la optimización de bases de datos?

En un mundo en el que los usuarios esperan tiempos de respuesta instantáneos, la lentitud de las bases de datos puede provocar frustración y pérdida de oportunidades. En WeWard, este desafío se traduce en millones de transacciones diarias y consultas complejas que requieren un rendimiento impecable. Nuestros desafíos incluían la lentitud de las consultas, la carga excesiva y una arquitectura que requería una mayor escalabilidad.

Así es como superamos estos obstáculos.

⚙ Paso 1: Optimización de consultas

La base de cualquier optimización de PostgreSQL comienza con el análisis de consultas. Identificamos las ineficiencias estudiando los planes de ejecución de consultas.

Análisis del plan de ejecución

Uso EXPLICAR y ANALIZAR, obtuvimos una vista de rayos X de nuestras consultas.

Ejemplo de consulta problemática:

SELECCIONA level_customer. *
DE level_customer
ÚNETE A NIVEL ON level.id = level_customer.level_id
DONDE level_customer.customer_id = XXXXXXX
Y level_customer.start_date <= '2024-05-07': :fecha
Y nivel.version = 2
ORDENAR POR level_customer.start_date DESC, level.value DESC
LÍMITE 1;


Problemas detectados:

• Falta de un índice para gestionar la clasificación de forma eficiente.
• Estrategia de unión subóptima elegida por el planificador.

Solución: Creación de índices:

CREAR ÍNDICE idx_level_customer_customer_date_value
EN level_customer (customer_id, start_date DESC, level_id DESC);


Ajuste del planificador:Obligamos a PostgreSQL a usar Unión de bucles anidados en lugar del predeterminado Hash Join para esta consulta específica.

Vistas materializadas:Para las consultas complejas, las vistas materializadas redujeron la sobrecarga de cálculo en cada ejecución, lo que proporcionó un rendimiento notablemente estable.

⚖ Paso 2: Distribución de carga con réplica

Con millones de consultas diarias, adoptamos una arquitectura basada en réplicas para distribuir la carga.

Configuración de la arquitectura:

• Nodo principal: Gestiona las operaciones de lectura y escritura, lo que garantiza la coherencia de los datos.
• Nodo de réplica: Dedicado a las operaciones de solo lectura, lo que reduce la carga en el nodo principal y mejora los tiempos de respuesta para las consultas de lectura intensiva.

Ventajas:

• Rendimiento mejorado: La transferencia de consultas de lectura a réplicas reduce la latencia y aumenta el rendimiento.
• Alta disponibilidad: En caso de fallo del nodo principal, se pueden promover las réplicas, lo que garantiza la continuidad del servicio.
• Escalabilidad: Se pueden agregar varios nodos de réplica para escalar horizontalmente a medida que aumenta el tráfico.

Consideraciones técnicas:

• Latencia de replicación: Es posible que se produzca un ligero retraso entre los nodos principal y de réplica.
• Consistencia eventual: Las consultas de lectura de las réplicas pueden arrojar datos ligeramente desactualizados.
• Monitorización: La supervisión proactiva es esencial para garantizar una replicación confiable.

🗂 Paso 3: Administrar tablas grandes con particiones

¿Por qué particionar?

Las tablas que contienen miles de millones de filas pueden ralentizar considerablemente las operaciones de lectura y escritura. La partición divide una tabla en subconjuntos más pequeños, lo que mejora la eficiencia de las consultas.

Herramienta: pg_partman

Ventajas:

• Administración automatizada de particiones: Gestiona la creación y eliminación de particiones según criterios predefinidos.
• Rendimiento de consulta mejorado: Las consultas se dirigen solo a las particiones relevantes.
• Mantenimiento más sencillo de los datos históricos: Simplifica el archivado y la purga sin interrumpir los datos actuales.

🚦 Paso 4: Estabilización de la conexión

Cada conexión a PostgreSQL consume recursos del sistema. Para evitar la sobrecarga del servidor:

• Límites de conexión: número máximo de conexiones se ajustó en función de las necesidades de hardware y aplicaciones disponibles.
• Agrupación de conexiones: La reutilización de las conexiones existentes redujo la carga del servidor y mejoró la capacidad de respuesta de las aplicaciones.

Parámetros clave de memoria de PostgreSQL:

• búferes compartidos: Controla la asignación de memoria para almacenar en caché los datos a los que se accede con frecuencia.
• work_mem: Define la memoria para la clasificación y las tablas hash durante la ejecución de la consulta.

📈 Paso 5: Una arquitectura escalable con lago de datos

Los datos críticos («calientes») permanecen en PostgreSQL, mientras que los datos a los que se accede con menos frecuencia («fríos») se transfieren a Amazon S3 o Almacenamiento en la nube de Google. El análisis de los datos fríos se realiza utilizando Google BigQuery.

Ventajas:

• Escalabilidad: Capacidad de almacenamiento ilimitada en plataformas en la nube.
• Eficiencia de costos: Reducción de los costos del almacenamiento de datos en frío.
• Flexibilidad: Herramientas adecuadas para cada tipo de datos.

👁 Paso 6: Monitoreo y observabilidad

WeWard utiliza herramientas de supervisión avanzadas como:

• Información sobre el rendimiento de AWS: Visualización del rendimiento de bases de datos en tiempo real
• Supervisión del rendimiento de las aplicaciones (APM): Seguimiento de transacciones de principio a fin.
• Supervisión del rendimiento de la base de datos (DBM): Consulta información sobre el rendimiento.

Ventajas:

• Detección rápida de problemas.
• Optimización del rendimiento.

🏁 Conclusión: una base sólida para el futuro

Al combinar una rigurosa optimización de consultas, administración inteligente de recursos y arquitecturas modernas, PostgreSQL se ha convertido en un aliado clave para WeWard. Estas soluciones son aplicables a cualquier organización que desee maximizar el potencial de las bases de datos.

💬 ¿Cuáles son sus estrategias para optimizar PostgreSQL? ¡Comparta sus experiencias y únase a la conversación!

👉 Explore nuestra aplicación móvil: We Ward.

‍