performance - particiones - postgres detach partition
¿Cuántas particiones de mesa hay demasiadas en Postgres? (4)
Estoy particionando una tabla muy grande que contiene datos temporales, y considerando a qué granularidad debo hacer las particiones. La documentación de la partición de Postgres afirma que "es probable que un gran número de particiones aumente considerablemente el tiempo de planificación de consultas" y recomienda que se utilice la partición con "hasta un máximo de cien" particiones.
Suponiendo que mi tabla contiene diez años de datos, si particionara por semana terminaría con más de 500 particiones. Antes de descartar esto, me gustaría entender mejor qué impacto tiene la cantidad de particiones en el tiempo de planificación de consultas. ¿Alguien ha evaluado esto, o alguien tiene un entendimiento de cómo funciona esto internamente?
"es probable que un gran número de particiones aumente considerablemente el tiempo de planificación de consultas" y recomienda que se utilice la partición con "hasta un máximo de cien" particiones.
Debido a que cada partición adicional generalmente estará vinculada a las restricciones de verificación, y esto hará que el planificador se pregunte en cuál de las particiones se debe consultar. En el mejor de los casos, el planificador identifica que solo está golpeando una única partición y se deshace del paso del anexo.
En términos de filas, y como DNS y Seth han señalado, su kilometraje variará con el hardware. En general, sin embargo, no hay una diferencia significativa entre consultar una tabla de filas de 1M y una tabla de filas de 10M, especialmente si sus discos duros permiten un acceso aleatorio rápido y si está agrupado (consulte la declaración del cluster ) usando el índice que más le convenga. frecuentemente golpeando.
Cada partición de tabla ocupa un inodo en el sistema de archivos. "Muy grande" es un término relativo que depende de las características de rendimiento del sistema de archivos que elija. Si desea obtener puntos de referencia de rendimiento explícitos, probablemente podría ver varios puntos de referencia de rendimiento de los sistemas de correo de su sistema operativo y FS de su elección. En general, no me preocuparía por eso hasta que llegues a las decenas de miles a cientos de miles de espacios de tablas (el uso de dirhash en UFS2 de FreeBSD sería una victoria). También tenga en cuenta que esta misma limitación se aplica a las BASES DE DATOS, TABLAS o cualquier otro objeto de base de datos respaldado por el sistema de archivos en PostgreSQL.
El planificador de consultas debe realizar una búsqueda lineal de la información de restricción para cada partición de las tablas utilizadas en la consulta, para determinar cuáles están realmente involucradas, las que pueden tener las filas necesarias para los datos solicitados. El número de planes de consulta que el planificador considera crece exponencialmente a medida que se unen más tablas. Por lo tanto, el lugar exacto donde la búsqueda lineal se suma al tiempo suficiente para ser problemático realmente depende de la complejidad de la consulta. Cuantas más combinaciones, peor te golpeará esto. La cifra de "hasta cien" vino de señalar que el tiempo de planificación de la consulta se estaba sumando a una cantidad de tiempo no trivial, incluso en consultas más simples alrededor de ese punto. En particular, en aplicaciones web, donde la latencia del tiempo de respuesta es importante, eso es un problema; De ahí la advertencia.
¿Puedes apoyar 500? Por supuesto. Pero va a buscar cada una de las 500 restricciones de verificación para cada plan de consulta que incluya esa tabla considerada por el optimizador. Si el tiempo de planificación de consultas no es una preocupación para usted, entonces tal vez no le importe. Pero a la mayoría de los sitios les disgusta la proporción de tiempo empleado en la planificación de consultas con tantas particiones, lo cual es una de las razones por las cuales la partición mensual es el estándar para la mayoría de los conjuntos de datos. Puede almacenar fácilmente 10 años de datos, particionados mensualmente, antes de comenzar a cruzar hacia donde la sobrecarga de planificación comienza a ser notable.
Si no desea confiar en los desarrolladores de PostgreSQL que escribieron el código, le recomiendo que lo intente usted mismo y ejecute algunas consultas de ejemplo con análisis de explicación y cronometraje usando diferentes esquemas de partición. Es probable que su configuración específica de hardware y software domine cualquier respuesta en cualquier caso.
Supongo que la memoria caché de optimización de filas que utiliza el optimizador de consultas para determinar qué combinaciones y restricciones usar se almacena con cada partición, por lo que probablemente deba cargar y leer partes de cada partición para planificar la consulta.