Particionamiento en PostgreSQL: Consultas eficientes sobre miles de millones de filas

Cuándo se necesita el Partitioning

A partir de un determinado tamaño de tabla, incluso los índices B-Tree se vuelven ineficientes. VACUUM tarda horas, los backups se convierten en un problema y las consultas acceden a volúmenes de datos innecesariamente grandes. El Partitioning divide una tabla grande en partes más pequeñas y manejables.

Estrategias de Partitioning

• Range Partitioning: Ideal para datos de series temporales. Particione por mes o trimestre: las consultas que abarcan un período de tiempo solo escanean las particiones relevantes.
• List Partitioning: Para datos categóricos como región, tenant o estado. Cada partición contiene filas con determinados valores.
• Hash Partitioning: Distribución uniforme sobre un número fijo de particiones, bueno para workloads con escritura intensiva sin una clave de partición natural.

Buenas prácticas

• Clave de partición en la consulta: Las consultas deben incluir la clave de partición en la cláusula WHERE para que el Partition Pruning sea efectivo.
• No demasiadas particiones: Cientos de particiones ralentizan el Query Planner. Entre 12 y 36 particiones activas es un buen punto de referencia.
• Creación automática de particiones: Utilice pg_partman para la creación y gestión automática de particiones de series temporales.
• Índices por partición: Cada partición tiene sus propios índices, lo que mantiene el mantenimiento de índices rápido.

Resultado

En un proyecto de e-commerce, dividimos una tabla de pedidos con 2.400 millones de filas mediante Range Partitioning por mes. El tiempo promedio de consulta se redujo de 12 segundos a 80 milisegundos, un factor de 150x.