PostgreSQL 17: un avance importante en rendimiento, replicación lógica y más

Tutoriales
PostgreSQL

Después de un exitoso tercer beta en agosto de 2024, el equipo de desarrollo de PostgreSQL publicó la versión GA el 26 de septiembre. Recientemente, escribí en mi blog sobre algunas de las características lógicas de replicación clave que verán en PostgreSQL 17. En este blog, describiré dos nuevas características de rendimiento que encontrarán en Postgres 17, así como otra importante característica de replicación lógica que no cubrí en mi blog anterior de esta serie.

PostgreSQL ha crecido notablemente a lo largo de los años, y con cada versión mayor, se ha convertido en una base de datos más robusta, confiable y responsiva para aplicaciones empresariales tanto críticas como no críticas. La comunidad global y vibrante de PostgreSQL contribuye al éxito de PostgreSQL, diligentemente asegurando que todos los cambios se examinan y revisan antes de agregarlos al código fuente del proyecto. También es muy alentador ver que nombres tecnológicos importantes como Microsoft, Google, Apple y otros invierten en Postgres desarrollando experiencia interna y dando back a la comunidad de código abierto.

Los mejoramientos en la replicación lógica están pavimentando el camino para agregar el soporte distribuido de PostgreSQL a la funcionalidad central. PostgreSQL distribuido se refiere a la implementación de PostgreSQL en una arquitectura distribuida, permitiendo mejores escalabilidad, tolerancia a fallas y mejores rendimientos en varios nodos.

Ahora, sin más adelanto, vamos a discutir algunas características de rendimiento de PostgreSQL 17.

Mejora en el Rendimiento de las consultas con CTEs Materializadas.

Expresiones de Tabla Común (CTEs) en PostgreSQL son conjuntos de resultados temporales que pueden ser referenciados dentro de una declaración SELECT, INSERT, UPDATE o DELETE. Mejoran la legibilidad y la organización de consultas complejas y pueden ser recursivas, lo que las hace particularmente útiles para datos jerárquicos. La sintaxis básica de una consulta CTE es la siguiente:

  

WITH cte_names AS  
(– QUERY here ) 
Select * from cte_names;

Incluya el keyword WITH en una consulta para crear la CTE; la consulta padre (que define el conjunto de resultados) sigue la cláusula AS después del nombre de la CTE. Después de definir la CTE, puede referirse a la CTE por nombre para referenciar el conjunto de resultados de la CTE y realizar operaciones adicionales en el conjunto de resultados dentro de la misma consulta.

PostgreSQL 17 continúa mejorando el rendimiento y las capacidades en torno a las CTEs, incluyendo mejoras en la planificación de consultas y ejecución. Versiones anteriores de Postgres trataban a las CTEs como vallas de optimización, lo que significa que el planificador no podía desplazarse hacia abajo predicados en ellas. Sin embargo, a partir de PostgreSQL 12, puede definir planes de ejecución más eficientes. Siempre debe analizar sus consultas y considerar los planes de ejecución cuando el rendimiento es crítico.

Consejo de rendimiento: Si van a referirse al mismo conjunto de resultados varias veces, creen la CTE con la palabra clave MATERIALIZED. Cuando crean una CTE materializada, Postgres calcula y almacena el resultado de la consulta padre. Entonces, las consultas subsiguientes no son necesarias para realizar computaciones complejas varias veces si se hace referencia a la CTE varias veces. 

Extracción de Estadísticas de Columnas de Referencias de CTE, Postgres 17 Mejora las CTE Materializadas

Una CTE materializada actúa básicamente como una barrera de optimización, lo que significa que la consulta exterior no influirá en el plan de la subconsulta una vez que se haya elegido el plan. La consulta exterior tiene visibilidad en la anchura estimada y el conteo de filas del conjunto de resultados de la CTE, de modo que tiene sentido propagar las estadísticas de columnas desde la subconsulta al planificador para la consulta exterior. La consulta exterior puede hacer uso de cualquier información disponible, lo que permite que la información estadística de las columnas se propague hacia la consulta exterior pero no hacia el plan de la CTE.

Este error reportado a la comunidad contiene un caso de prueba simple que puede demostrar la mejora y el efecto del planificador de consultas como resultado de esta mejora.

Ejemplo: Comparando el Comportamiento de Postgres 16 con Postgres 17

Antes, creamos nuestro espacio de trabajo en Postgres 16 y ejecutamos ANALYZE contra él; dos tablas y índices:

  

postgres=# create table t1(a int);
CREATE TABLE
postgres=# create table t2(b int);
CREATE TABLE
postgres=# create index my_index on t1 using btree (a);
CREATE INDEX
postgres=# insert into t1 select generate_series(1, 100000) from generate_series(1, 3);
INSERT 0 300000
postgres=# insert into t2 select generate_series(1, 100) from generate_series(1, 10);
INSERT 0 1000
postgres=# analyze t1;
ANALYZE
postgres=# analyze t2;
ANALYZE

Then, we create our materialized CTE:

postgres=# explain analyze with my_cte as materialized (select b from t2) select *
from t1 where t1.a in (select b from my_cte);

The query plan from our Postgres 16 code sample contains:

QUERY PLAN         
----------------------------------------------------------------------
 Nested Loop  (cost=37.92..856.50 rows=2966 width=4) (actual time=0.574..0.722 rows=300 loops=1)
   CTE my_cte
     ->  Seq Scan on t2  (cost=0.00..15.00 rows=1000 width=4) (actual time=0.038..0.161 rows=1000 loops=1)
   ->  HashAggregate  (cost=22.50..24.50 rows=200 width=4) (actual time=0.449..0.461 rows=100 loops=1)
         Group Key: my_cte.b
         Batches: 1  Memory Usage: 40kB
         ->  CTE Scan on my_cte  (cost=0.00..20.00 rows=1000 width=4) (actual time=0.046..0.322 rows=1000 loops=1)
   ->  Index Only Scan using my_index on t1  (cost=0.42..4.06 rows=3 width=4) (actual time=0.002..0.002 rows=3 loops=1
00)
         Index Cond: (a = my_cte.b)
         Heap Fetches: 0
 Planning Time: 1.242 ms
 Execution Time: 1.051 ms
(12 rows)

Como puede ver en el plan de consulta, las estadísticas de columnas de 200 filas de la subconsulta son incorrectas, lo que impacta en el plan general.

  

   ->  HashAggregate  (cost=22.50..24.50 rows=200 width=4) (actual time=0.449..0.461 rows=100 loops=1)
         Group Key: my_cte.b

Después, probamos la misma configuración y consulta contra PostgreSQL 17:

  

postgres=# explain analyze with my_cte as materialized (select b from t2) select *
from t1 where t1.a in (select b from my_cte);
                                                            QUERY PLAN                           
                                 
-------------------------------------------------------------------------------------------------
---------------------------------
 Merge Join  (cost=42.25..54.29 rows=302 width=4) (actual time=0.627..0.712 rows=300 loops=1)
   Merge Cond: (t1.a = my_cte.b)
   CTE my_cte
     ->  Seq Scan on t2  (cost=0.00..15.00 rows=1000 width=4) (actual time=0.031..0.134 rows=1000
 loops=1)
   ->  Index Only Scan using my_index on t1  (cost=0.42..7800.42 rows=300000 width=4) (actual tim
e=0.027..0.049 rows=301 loops=1)
         Heap Fetches: 0
   ->  Sort  (cost=26.82..27.07 rows=100 width=4) (actual time=0.598..0.604 rows=100 loops=1)
         Sort Key: my_cte.b
         Sort Method: quicksort  Memory: 25kB
         ->  HashAggregate  (cost=22.50..23.50 rows=100 width=4) (actual time=0.484..0.494 rows=1
00 loops=1)
               Group Key: my_cte.b
               Batches: 1  Memory Usage: 24kB
               ->  CTE Scan on my_cte  (cost=0.00..20.00 rows=1000 width=4) (actual time=0.033..0
.324 rows=1000 loops=1)
 Planning Time: 1.066 ms
 Execution Time: 0.946 ms
(15 rows)

Como puede ver en el plan de consulta para Postgres 17, las estadísticas de columnas de la subconsulta se están propagando correctamente al planificador superior de la consulta exterior. Esto ayuda a PostgreSQL a elegir un mejor plan que mejora el tiempo de ejecución de la consulta.

Esta es una consulta simple, pero con consultas mayores y complejas, este cambio puede resultar en una diferencia de rendimiento importante.

La propagación de Pathkeys de una CTE a una consulta exterior

Otra mejora interesante en la funcionalidad de CTE en Postgres 17 es la propagación de pathkeys de la subconsulta a la consulta exterior. En PostgreSQL, las pathkeys son parte del proceso de planificación de ejecución de consultas utilizadas principalmente para ordenar y organizar filas en consultas que requieren resultados ordenados, como las consultas con una cláusula ORDER BY, o cuando se necesita ordenar para otras operaciones como las uniones de concatenación.

Antes de Postgres 17, el orden de clasificación de la subconsulta materializada de la CTE no se compartía con la consulta exterior, incluso si el orden de clasificación era garantizado por un nodo de exploración de índice o un nodo de clasificación. No tener un orden de clasificación garantizado permite que el planificador de PostgreSQL elija un plan menos optimizado, mientras que tener un orden de clasificación garantizado hará que sea más probable elegir un plan optimizado.

Con PostgreSQL 17, si una CTE (Common Table Expression) se materializa y tiene un ordenamiento específico, el planificador puede reutilizar esa información en la consulta exterior, mejorando el rendimiento evitando el ordenamiento redundante o permitiendo métodos de unión más eficientes. Como se menciona en los comentarios del commit por Tom Lane:

“El código para elevar las rutas de claves en la consulta exterior ya existe para las subconsultas de RTE_SUBQUERY regulares, pero no se utilizaba para CTEs, posiblemente debido a un interés en mantener una barrera de optimización entre la CTE y la consulta exterior.”

Esta simple modificación en el código fuente de Postgres debería resultar en mejoras de rendimiento para las consultas que involucran CTEs complejas, especialmente aquellas donde el ordenamiento o las uniones por mezcla pueden ser optimizados basándose en el orden inherente de los resultados de la CTE.

Aquí hay un ejemplo usando los datos en la prueba de regresión de PostgreSQL:

  

postgres=# CREATE TABLE tenk1 (
postgres(#         unique1         int4,
postgres(#         unique2         int4,
postgres(#         two                     int4,
postgres(#         four            int4,
postgres(#         ten                     int4,
postgres(#         twenty          int4,
postgres(#         hundred         int4,
postgres(#         thousand        int4,
postgres(#         twothousand     int4,
postgres(#         fivethous       int4,
postgres(#         tenthous        int4,
postgres(#         odd                     int4,
postgres(#         even            int4,
postgres(#         stringu1        name,
postgres(#         stringu2        name,
postgres(#         string4         name
postgres(# );
CREATE TABLE
postgres=# CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
CREATE INDEX
postgres=# \copy tenk1 FROM '~/projects/postgres/src/test/regress/data/tenk.data';
COPY 10000
postgres=# VACUUM ANALYZE tenk1;
VACUUM

El plan de consulta de nuestro ejemplo de código de Postgres 16 contiene lo siguiente:

  

postgres=# explain analyze with x as materialized (select unique1 from tenk1 b order by unique1)
select count(*) from tenk1 a
  where unique1 in (select * from x);
                                                                  QUERY PLAN                                                               
   
-------------------------------------------------------------------------------------------------------------------------------------------
---
 Aggregate  (cost=764.29..764.30 rows=1 width=8) (actual time=21.592..21.593 rows=1 loops=1)
   CTE x
     ->  Index Only Scan using tenk1_unique1 on tenk1 b  (cost=0.29..306.29 rows=10000 width=4) (actual time=0.046..1.415 rows=10000 loops=
1)
           Heap Fetches: 0
   ->  Nested Loop  (cost=225.28..445.50 rows=5000 width=0) (actual time=7.545..20.911 rows=10000 loops=1)
         ->  HashAggregate  (cost=225.00..227.00 rows=200 width=4) (actual time=7.535..9.051 rows=10000 loops=1)
               Group Key: x.unique1
               Batches: 1  Memory Usage: 929kB
               ->  CTE Scan on x  (cost=0.00..200.00 rows=10000 width=4) (actual time=0.070..3.933 rows=10000 loops=1)
         ->  Index Only Scan using tenk1_unique1 on tenk1 a  (cost=0.29..1.08 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=10000)
               Index Cond: (unique1 = x.unique1)
               Heap Fetches: 0
 Planning Time: 0.806 ms
 Execution Time: 21.890 ms
(14 rows)

El plan de consulta de nuestro ejemplo de código de Postgres 17 contiene lo siguiente:

  

postgres=# explain analyze with x as materialized (select unique1 from tenk1 b order by unique1)
select count(*) from tenk1 a
  where unique1 in (select * from x);
                                                                    
QUERY PLAN                                                                    
------------------------------------------------------------------------------------------------------------------------------- Aggregate  (cost=987.55..987.56 rows=1 width=8) (actual time=8.777..8.778 rows=1 loops=1)
   CTE x
     ->  Index Only Scan using tenk1_unique1 on tenk1 b  (cost=0.29..306.29 rows=10000 width=4) (actual time=0.010..1.095 rows=100
00 loops=1)
           Heap Fetches: 0
   ->  Merge Semi Join  (cost=0.31..656.26 rows=10000 width=0) (actual time=0.037..8.024 rows=10000 loops=1)
         Merge Cond: (a.unique1 = x.unique1)
         ->  Index Only Scan using tenk1_unique1 on tenk1 a  (cost=0.29..306.29 rows=10000 width=4) (actual time=0.013..1.262 rows
=10000 loops=1)
               Heap Fetches: 0
         ->  CTE Scan on x  (cost=0.00..200.00 rows=10000 width=4) (actual time=0.016..3.678 rows=10000 loops=1)
 Planning Time: 0.800 ms
 Execution Time: 8.899 ms
(11 rows)

Los planes de consulta en Postgres 16 y Postgres 17 son significativamente diferentes debido a esta mejora de versión 17. Este es un pequeño ejemplo; puede ver que la mejora en rendimiento será significativa en consultas más grandes. Tenga en cuenta que esta mejora solo es efectiva si la subconsulta de CTE tiene una cláusula ORDER BY.

Escaneo rápido de índices B-Tree para escalares de arreglo

En PostgreSQL, ScalarArrayOpExpr es un tipo de nodo en el plan de ejecución que maneja consultas que involucran operaciones como IN o ANY con arreglos o listas de valores. Es particularmente útil para consultas en las que se compara una columna contra un conjunto de valores, como: SELECT * FROM table WHERE column = ANY(ARRAY[1, 2, 3]);.

ScalarArrayOpExpr permite a PostgreSQL optimizar consultas que involucran múltiples comparaciones que usan IN o ANY. PostgreSQL 17 ha introducido nuevas mejoras de rendimiento para hacer estas operaciones aún más rápidas.

En PostgreSQL 17, se han realizado mejoras significativas en los escaneos de índices B-Tree, que optimizan el rendimiento, particularmente para consultas con grandes listas de IN o condiciones de ANY. Estas mejoras reducen el número de escaneos de índice realizados por el sistema, disminuyendo así la contención de CPU y páginas de buffer,resultando en una ejecución de consulta más rápida.

Una de las mejoras clave se encuentra en el manejo de las Expresiones de Operación de Arrays de escalares (SAOP), lo que permite un recorrido más eficiente de los índices de árbol B, particularmente para consultas multidimensionales. Por ejemplo, cuando se tienen varias columnas de índice (cada una con su propia lista IN), PostgreSQL 17 puede procesar ahora estas operaciones de manera más eficiente en una sola lectura de índice, en lugar de varias lecturas como en las versiones anteriores. Esto puede llevar a ganancias de rendimiento de 20-30% en cargas de trabajo enchufadas en CPU donde los accesos a página eran previamente un cuello de botella.

Además, PostgreSQL 17 introduce un mejor manejo de bloqueos internos, lo que resulta en un mejor rendimiento para cargas de trabajo de alta concurrencia, especialmente cuando se realizan escaneos en varias dimensiones dentro de un índice de árbol B.

Podemos demostrar esto con un ejemplo sencillo. Usaremos la misma tabla tenk1 y datos que usamos en el ejemplo anterior del conjunto de pruebas de regresión de Postgres.

Nuestro ejemplo, primero ejecutado en Postgres 16:

  

CREATE TABLE tenk1 (
postgres(#         unique1         int4,
postgres(#         unique2         int4,
postgres(#         two                     int4,
postgres(#         four            int4,
postgres(#         ten                     int4,
postgres(#         twenty          int4,
postgres(#         hundred         int4,
postgres(#         thousand        int4,
postgres(#         twothousand     int4,
postgres(#         fivethous       int4,
postgres(#         tenthous        int4,
postgres(#         odd                     int4,
postgres(#         even            int4,
postgres(#         stringu1        name,
postgres(#         stringu2        name,
postgres(#         string4         name
postgres(# );
CREATE TABLE
postgres=# CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
CREATE INDEX
postgres=# \copy tenk1 FROM '~/projects/postgres/src/test/regress/data/tenk.data';
COPY 10000
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 IN (1, 2, 3);
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=14.20..330.12 rows=176 width=244) (actual time=0.138..0.153 rows=3 loops=1)
   Recheck Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
   Heap Blocks: exact=3
   Buffers: shared hit=9
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..14.16 rows=176 width=0) (actual time=0.102..0.102 rows=3 loops=1)
         Index Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
         Buffers: shared hit=6
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.900 ms
 Execution Time: 0.242 ms
(11 rows)
postgres=# SELECT idx_scan, idx_tup_fetch FROM pg_stat_user_tables WHERE relname = 'tenk1';
 idx_scan | idx_tup_fetch 
----------+---------------
        3 |             3
(1 row)

En la consulta anterior, puede ver que el acierto en el búfer compartido para la consulta IN fue de 9 y que se necesitaron 3 lecturas de índice para obtener los resultados del escaneo de índice. En PostgreSQL, el término acierto compartido se refiere a un tipo específico de golpe relacionado con la gestión de búferes. Un acierto compartido ocurre cuando PostgreSQL accede a un bloque de datos o página del pool de búferes compartidos en lugar de del disco, mejorando el rendimiento de las consultas.

El mismo ejemplo, esta vez ejecutado en Postgres 17:

  

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 IN (1, 2, 3);
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=12.88..24.08 rows=3 width=244) (actual time=0.043..0.054 rows=3 loops=1)
   Recheck Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
   Heap Blocks: exact=3
   Buffers: shared hit=5
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..12.88 rows=3 width=0) (actual time=0.026..0.026 rows=3 loops=1)
         Index Cond: (unique1 = ANY ('{1,2,3}'::integer[]))
         Buffers: shared hit=2
 Planning:
   Buffers: shared hit=59
 Planning Time: 0.479 ms
 Execution Time: 0.116 ms
(11 rows)

postgres=# SELECT idx_scan, idx_tup_fetch FROM pg_stat_user_tables WHERE relname = 'tenk1';
 idx_scan | idx_tup_fetch 
----------+---------------
        1 |             3
(1 row)

Como puede ver, con Postgres 17 la carga de la memoria compartida se reduce a 5, y lo más importante es que solo realiza una escanearía de índice (en comparación con 3 escanearías en el caso de Postgres 16). Con esta mejora en Postgres 17, se puede observar una gran mejora en el rendimiento de operaciones de arreglos escalares, y Postgres puede elegir planes de consulta optimizados mejor.

Retención de los Slots de Replicación Lógica y Subscripciones Durante la Actualización

La retención de los slots de replicación lógica y la migración de las dependencias de las subscripciones durante el proceso de actualización mayorista es otra característica de replicación lógica añadida a PostgreSQL 17. Tenga en cuenta que esta característica será útil solo para actualizar de PostgreSQL 17 a versiones posteriores, esto no está soportado para actualizar antes de Postgres 17. Los slots de replicación y los orígenes de replicación se generan cuando se crea un entorno de replicación lógica. Sin embargo, esta información es específica para el nodo para registrar el estado de replicación, el estado de aplicación y el estado de transmisión de WAL, así que no se actualizan como parte del proceso de actualización. Una vez que el nodo publicador está actualizado, el usuario necesita construir manualmente estos objetos.

El proceso pg_upgrade se mejora en PostgreSQL 17 para referenciar y reconstruir estos objetos internos; esta funcionalidad permite que la replicación se reanuda automáticamente al actualizar un nodo con replicación lógica. Anteriormente, al realizar una actualización de versión mayorista, los usuarios tenían que eliminar los slots de replicación lógica, lo que les obligaba a sincronizar de nuevo los datos con los suscriptores después de la actualización. Esto aumentaba la complejidad y extendía la downtime durante las actualizaciones.

Para actualizar el cluster publicador, debe seguir los siguientes pasos:

  • Asegúrese de que cualquier suscripción al publicador está temporalmente deshabilitada realizando un ALTER SUBSCRIPTION….DISABLE. Estas se habilitan una vez completado el proceso de actualización.
  • Establezca el wal_level del nuevo cluster en logical.
  • El max_replication_slots del nuevo cluster debe establecerse en un valor mayor o igual que los slots de replicación del cluster antiguo.
  • Los plugins de salida utilizados por los slots deben instalarse en el nuevo cluster.
  • Todos los cambios del cluster antiguo ya se han replicado al cluster de destino antes de la actualización.
  • Todos los slots del cluster antiguo deben ser utilizables; puede asegurar esto consultando las columnas en conflicto en la vista pg_replication_slots. El conflicto debe ser falso para todos los slots del cluster antiguo.
  • Ningún slot en el nuevo cluster debe tener un valor de false en la columna Temporary de la vista pg_replication_slots. No debe haber slots de replicación lógica permanentes en el nuevo cluster.

El proceso de actualización de slots de replicación pg_upgrade resultará en un error si no se cumplen cualquiera de los prerequisitos mencionados anteriormente.

Conclusión

Con PostgreSQL 17, el enfoque de la comunidad continúa siendo hacer de PostgreSQL más eficiente, escalable, seguro y listo para el uso en empresas. Postgres 17 también mejora la experiencia para los desarrolladores agregando nuevas características para la compatibilidad y haciendo que las características existentes sean más poderosas y robustas.  

Más allá de la versión 17, PostgreSQL seguirá creciendo, mejorando y alcanzando mayores niveles de eficiencia para atender a aplicaciones empresariales que requieren bases de datos escalables. La escalabilidad (tanto horizontal como vertical) ha mejorado con los años, sin embargo, definitivamente hay espacio para mejorar la capacidad horizontal agregando capacidades de particionado a PostgreSQL.  Veremos más mejoras en la replicación lógica, con más en camino en el área de replicación de DDL o replicación de objetos faltantes (como secuencias) y mejores herramientas de gestión de nodos. La comunidad también reconoce la necesidad de hacer de PostgreSQL más compatible, de ahí las mejoras del comando MERGE en Postgres 17, y planes para más características de compatibilidad fuera de Postgres 17.

Source:
https://dzone.com/articles/postgresql-17-a-major-step-forward-in-performance