Técnicas no convencionales de optimización en PostgreSQL

• Métodos de optimización que rompen con el enfoque tradicional para mejorar el rendimiento de consultas en PostgreSQL: se presentan 3 enfoques creativos que no consisten en agregar índices ni reescribir consultas
• Eliminación de escaneos completos de tabla con restricciones CHECK, optimización de baja cardinalidad con índices basados en funciones e implementación de restricciones UNIQUE mediante índices Hash
• 1. Uso de la configuración constraint_exclusion para evitar escaneos innecesarios en consultas con condiciones incorrectas
• 2. Uso de índices basados en funciones y columnas generadas virtuales (virtual generated column) para reducir el tamaño del índice y asegurar la consistencia de las consultas
• 3. Combinación de índices Hash y restricciones de exclusión (exclusion constraint) para implementar de forma eficiente restricciones UNIQUE sobre columnas de texto de gran tamaño, con un gran ahorro de espacio de almacenamiento

---

Eliminación de escaneos completos de tabla con restricciones CHECK

• Aunque exista una restricción CHECK que solo permite los valores 'free' y 'pro' en la columna plan, si se ejecuta por error una consulta con 'Pro', PostgreSQL escanea toda la tabla
  • En el plan de ejecución se leen las 100 mil filas completas, aunque el resultado real sea 0 filas
• Si se configura el parámetro constraint_exclusion en 'on', PostgreSQL tiene en cuenta la restricción y omite por completo el escaneo
  • El tiempo de ejecución se reduce de 7.4ms a 0.008ms
• El valor predeterminado es 'partition', y en consultas simples el overhead de planificación incluso puede aumentar
  • Sin embargo, en entornos de BI y reporting, donde los usuarios suelen ingresar condiciones incorrectas con frecuencia, la configuración 'on' resulta útil

Optimización de baja cardinalidad con índices basados en funciones

• Al ejecutar una consulta de agregación de ventas diarias sobre la tabla sale, que contiene 10 millones de registros de ventas, un escaneo completo tarda 627ms
• Si se agrega un índice B-Tree sobre la columna sold_at, el tiempo baja a 187ms, pero el tamaño del índice crece hasta 214MB
• Si se crea un índice basado en funciones sobre la expresión date_trunc('day', sold_at), el tamaño se reduce a 66MB y el tiempo de ejecución mejora aún más, hasta 145ms
  • Gracias a la baja cardinalidad, es posible la deduplicación de índices
• Sin embargo, como la expresión de la consulta debe coincidir exactamente con la definición del índice, es necesario mantener la consistencia de la expresión
  • Para ello se puede crear una VIEW con la misma expresión, o
  • agregar una columna generada virtual (virtual generated column), compatible desde PostgreSQL 18, para automatizar esa consistencia
• Al usar una columna generada virtual, el índice se aprovecha automáticamente y se obtiene al mismo tiempo un índice pequeño, consultas rápidas y consistencia de expresión
• Sin embargo, en PostgreSQL 18 todavía no se admite la creación de índices sobre columnas virtuales, y se espera soporte en la versión 19

Implementación de restricciones UNIQUE con índices Hash

• En una tabla urls que almacena URLs largas, si se crea un índice UNIQUE basado en B-Tree para evitar URLs duplicadas, el tamaño del índice llega a 154MB
• Un índice Hash es mucho más pequeño porque no almacena el valor real, sino solo su hash
  • PostgreSQL no admite por defecto índices Hash UNIQUE, pero
  • usando una restricción de exclusión (exclusion constraint) se puede implementar una restricción UNIQUE de forma indirecta con la forma EXCLUDE USING HASH (url WITH =)
• Con este método también se produce un error cuando se intenta insertar un duplicado, y el rendimiento de consulta también es mejor que con B-Tree (0.022ms vs 0.046ms)
• El tamaño del índice es de 32MB, más de 5 veces menor que el de B-Tree
• Desventajas:
  • No se puede referenciar con claves foráneas (no es posible la restricción REFERENCES)
  • Compatibilidad limitada con la sintaxis INSERT ... ON CONFLICT
  • Puede sustituirse por ON CONFLICT ON CONSTRAINT o por la sintaxis MERGE
• Los índices Hash son adecuados para garantizar unicidad en columnas de texto de gran tamaño y, cuando no se necesitan claves foráneas, son una alternativa eficiente en espacio

[GN⁺]

Comentarios en Hacker News

• El índice pesa 214 MB, o sea, aproximadamente la mitad del tamaño de toda la tabla
  Desde la perspectiva de analítica está bien, pero desde la de rendimiento de escritura aparece el problema de write amplification
  Los índices se diseñan distinto según la proporción entre lecturas y escrituras, y por eso existen los data warehouses o read replicas
  Si estás atendiendo a demasiados usuarios, es mejor no poner índices de BI/OLAP en la base de datos OLTP

  • Ojalá PostgreSQL soportara clustered index (el Index Organized Table de Oracle)
    Si el patrón de acceso a la tabla es constante, la propia tabla puede convertirse en el índice y obtener eficiencia sin write amplification

• Creo que en el primer ejemplo sería mejor definir Plan como un tipo enum
  Es más liviano que texto y, si entra un filtro inválido, responde con error en vez de devolver un resultado vacío, lo que lo hace más seguro

• Fue un gran artículo. Llevo décadas usando PostgreSQL y MySQL, pero incluso viendo este texto sentí que todavía solo conocía una parte de lo que es posible

  • Yo también llevo más de 10 años usando Postgres, pero cada vez que veo la documentación siento que apenas estoy rascando la superficie. Es un sistema realmente poderoso
  • PostgreSQL es como Emacs. Por fuera parece simple, pero en realidad tiene una flexibilidad casi de sistema operativo

• Lo que más me interesó fue la cláusula MERGE mencionada al final del artículo
  Normalmente hago upsert con INSERT ... ON CONFLICT DO UPDATE, pero MERGE parece más potente y útil en más situaciones

  • MERGE lleva mucho tiempo en el estándar SQL, pero Postgres retrasó su adopción por el problema de no atomicidad en el modelo MVCC
    También lo explican en esta entrada del blog de pganalyze
    Personalmente prefiero INSERT ... ON CONFLICT y solo uso MERGE cuando de verdad hace falta, cuidando mucho el manejo de errores
  • En términos de concurrencia, INSERT ... ON CONFLICT es más predecible
    Ver también esta comparación en modern-sql.com
  • Si se trata de inserciones masivas por lotes, lo más rápido es usar COPY INTO en formato binary. Casi no tiene sobrecarga del lado del servidor

• Me parecieron interesantes los índices BRIN, que el artículo no cubrió
  Si los datos crecen de forma monótona, son ideales por ser índices muy pequeños y rápidos

  • No hace falta que los datos sean perfectamente monótonos. Si en su mayoría lo son, ya funciona bastante bien
    Por ejemplo, va bien incluso cuando el orden se altera un poco, como con datos de timestamp recibidos por un servidor
    En el caso de UUIDv7, quizá haya que ajustar pages_per_range

• Siempre me ha parecido una lástima que en los índices hash no se puedan imponer restricciones de unicidad
  Parece que se podría resolver con algo de glue code que simplemente lo convierta en una exclusion constraint, así que me pregunto por qué aún no existe

• La validación de unicidad basada en hash no está soportada en índices porque no maneja colisiones
  La solución propuesta sufre el mismo problema

  • Eso no es cierto en absoluto. El índice solo guarda el hash, pero en la tabla se almacena el valor completo
    Postgres solo lo considera duplicado si coinciden tanto el hash como el valor real
  • También se puede comprobar en este ejemplo de dbfiddle

• El contenido del artículo me pareció fresco. Las columnas virtuales y los índices hash son interesantes, pero todavía da la impresión de que no están totalmente integrados en el ecosistema

  • Las columnas virtuales están casi listas. La mayor parte ya está implementada en PostgreSQL 18
    Los índices hash tuvieron muchas limitaciones durante mucho tiempo, pero poco a poco han ido mejorando, y la restricción única automática sigue siendo lo pendiente

• Pensé que usando una stored generated column se podría crear el índice directamente

  • El artículo explica por qué intenta evitar ese método
    Está soportado desde PostgreSQL 14, pero como el resultado queda almacenado físicamente y ocupa almacenamiento adicional, tiene ese costo
  • Me pregunto si no sería posible crear un partial index basado en una expresión
  • Al final igualmente aumenta el uso de almacenamiento, así que en el ejemplo del artículo es justo lo que se quiere evitar

• Desde que me mudé a la nube, ya casi no me toca administrar pgsql directamente como en un entorno de servidores fijos
  Me da curiosidad si el resaltado de sintaxis SQL que aparece en el artículo es una función integrada o una herramienta aparte

  • Yo uso pgcli. Tiene varias funciones cómodas como indicador de estado de transacción, autocompletado y resaltado
    Eso sí, me molesta que al copiar consultas largas agregue espacios automáticamente después de los saltos de línea
  • Si usas un IDE como IntelliJ, también puedes tener resaltado de sintaxis y autocompletado juntos