👵🏾 🚕 🧙🏼 Tras la reunión "Nuevas funciones de PostgreSQL 11" 👈🏾 🍂 🤙🏾

Hoy hablaremos sobre las características más importantes de PostgreSQL 11. ¿Por qué solo sobre ellas? Porque no todos necesitan algunas características, por lo que nos decidimos por las más populares.

Contenido

Compilación jit

PostgreSQL finalmente ha introducido la compilación JIT, es decir, compilar consultas en código binario. Para hacer esto, compile PostgreSQL con soporte para compilación JIT (Compile time 1 (--with-llvm)) . Al mismo tiempo, la máquina debe tener una versión LLVM no inferior a 3.9.

¿Qué puede acelerar JIT?

Consultas con la cláusula WHERE, es decir, todo lo que viene después de esta palabra clave. Esto no siempre es necesario, pero la oportunidad es útil.
Cálculo de la lista de destino: en la terminología de PostgreSQL, esto es todo lo que se encuentra entre select y from.
Agregados
Convierta registros de una vista a otra (Proyección). Por ejemplo, cuando aplica unir a dos tablas, el resultado es una nueva tupla que contiene campos de ambas tablas.
Deformación de tuplas. Uno de los problemas de cualquier base de datos, al menos en minúsculas, relacional, es cómo obtener un campo de un registro en el disco. Después de todo, puede ser nulo, tienen registros diferentes y, en general, esta no es la operación más barata.

Compile time 2 significa que JIT no se usa. En PostgreSQL hay un momento de planificación de consultas, cuando el sistema decide qué vale JIT y qué no. En este punto, se JIT y luego el ejecutor se ejecuta tal como está.

JIT se hace conectable. Por defecto, funciona con LLVM, pero puede conectar cualquier otro JIT.

Si compiló PostgreSQL sin compatibilidad con JIT, entonces la primera configuración no funciona. Opciones implementadas para desarrolladores, hay configuraciones para funciones JIT individuales.

El siguiente punto sutil está relacionado con jit_above_cost. JIT en sí no es gratis. Por lo tanto, PostgreSQL usa de manera predeterminada la optimización JIT si el costo de una consulta excede los 100 mil loros condicionales, en los que se miden explicar, analizar, etc. Este valor se elige al azar, así que preste atención.

Pero no siempre después de encender JIT todo funciona de inmediato. Por lo general, todos comienzan a experimentar con JIT usando select * from table donde id = 600 query y fallan. Probablemente, es necesario complicar de alguna manera la solicitud, y luego todos generan una base de datos gigante y componen la solicitud. Como resultado, PostgreSQL se basa en las capacidades del disco; carece de la capacidad de buffers y cachés compartidos.

Aquí hay un ejemplo completamente abstracto. Hay 9 campos nulos con diferentes frecuencias, para que pueda notar el efecto de la deformación de la tupla.

select i as x1,
case when i % 2 = 0 then i else null end as x2,
case when i % 3 = 0 then i else null end as x3,
case when i % 4 = 0 then i else null end as x4,
case when i % 5 = 0 then i else null end as x5,
case when i % 6 = 0 then i else null end as x6,
case when i % 7 = 0 then i else null end as x7,
case when i % 8 = 0 then i else null end as x8,
case when i % 9 = 0 then i else null end as x9
into t
from generate_series(0, 10000000) i;

vacuum t;
analyze t;

PostgreSQL tiene muchas posibilidades, y para ver las ventajas de JIT, desactive las dos primeras líneas para no interferir y restablezca los umbrales.

set max_parallel_workers=0;
set max_parallel_workers_per_gather=0;
set jit_above_cost=0;
set jit_inline_above_cost=0;
set jit_optimize_above_cost=0;

Aquí está la solicitud en sí:

set jit=off;
explain analyze
select count(*) from t where
sqrt(pow(x9, 2) + pow(x8,2)) < 10000;

set jit=on;
explain analyze
select count(*) from t where
sqrt(pow(x9, 2) + pow(x8,2)) < 10000;

Y aquí está el resultado:

Planning Time: 0.71 ms
Execution Time: 1986.323 ms

VS

Planning Time: 0.060 ms
JIT:
Functions: 4
Generation Time: 0.911 ms
Inlining: true
Inlining Time: 23.876 ms
Optimization: true
Optimization Time: 41.399 ms
Emission Time: 21.856 ms
Execution Time: 949.112 ms

JIT ayudó a acelerar la solicitud a la mitad. El tiempo de planificación es casi lo mismo, pero probablemente sea el resultado del almacenamiento en caché de PostgreSQL, así que ignórelo.

Si se resume, la compilación JIT tardó unos 80 ms. ¿Por qué JIT no es gratis? Antes de ejecutar la solicitud, debe compilarla, y esto también lleva tiempo. Y tres órdenes de magnitud más que la planificación. No es un placer costoso, pero vale la pena debido a la duración de la ejecución.

De esta manera puede usar JIT, aunque no siempre es beneficioso.

Particionamiento

Si prestó atención a la partición en PostgreSQL, probablemente notó que se hizo allí para mostrar. La situación mejoró ligeramente en la versión 10, cuando apareció una declaración declarativa de particiones (secciones). Por otro lado, todo permaneció igual por dentro y funcionó aproximadamente igual que en versiones anteriores, es decir, malo.
En muchos sentidos, este problema fue resuelto por el módulo pg_pathman, que permitió trabajar con secciones y cortarlas en el momento óptimo.

En la versión 11, la partición se mejora enormemente:

Primero, la tabla de partición puede tener una clave primaria, que debe incluir la clave de partición. De hecho, esta es una clave semi-primaria o una clave primaria. Desafortunadamente, no puede hacer una clave foránea en él. Espero que esto se solucione en el futuro.
Además, ahora es posible particionar no solo por rango, sino también por lista y hash. El hash es bastante primitivo, el resto de la expresión se toma por él.
Al actualizar, la línea se mueve entre secciones. Anteriormente, tenía que escribir un activador, pero ahora se hace automáticamente.

La gran pregunta es: ¿cuántas secciones puedo tener? Honestamente, con una gran cantidad de secciones (miles y decenas de miles) la función no funciona bien. Pg_pathman lo hace mejor.

También se hicieron secciones por defecto. Una vez más, en pg_pathman puede hacer la creación automática de secciones, lo cual es más conveniente. Aquí, todo lo que no pudo ser empujado a algún lado cae en la sección. Si está en un sistema real para hacer esto de forma predeterminada, luego de un tiempo obtienes tal desorden, que luego atormentas para rastrillar.

PostgreSQL 11 ahora puede optimizar la partición si dos tablas están unidas por una clave de partición y los esquemas de partición coinciden. Esto se controla mediante un parámetro especial, que está desactivado de manera predeterminada.

Puede calcular los agregados para cada sección por separado y luego sumarlos. Finalmente, puede crear un índice en la tabla particionada principal, y luego se crearán índices locales en todas las tablas que están conectadas.

En la sección "Novedades", se menciona algo maravilloso: la capacidad de eliminar secciones al ejecutar una solicitud. Veamos cómo funciona. El resultado es una tabla de este tipo:

Hacemos un tipo y una tabla de dos columnas con una clave primaria, con una gran columna de serie, inserte los datos. Creamos la segunda tabla, que será particionada y será una copia de la primera. Agregue la clave primaria a la tabla particionada.

La tabla constará de dos tipos de entradas: "niñeras" y "conductores masculinos". Y habrá una mujer conductora. Hacemos dos secciones, dividimos por lista, agregamos la clave primaria e insertamos todos los datos de la tabla en la que se genera todo esto. El resultado fue completamente poco interesante:

Presta atención a la solicitud. Seleccionamos todo de una tabla no particionada, nos conectamos a una tabla particionada. Tomamos una pieza pequeña y elegimos solo un tipo, pasan por uno. Indicamos que la columna oss debe tener un valor. Resulta una selección de controladores sólidos.

En la ejecución, deshabilitamos específicamente la paralelización, porque PostgreSQL 11 por defecto paraleliza muy activamente las consultas más o menos complejas. Si miramos el plan de ejecución (explique, analice), entonces se puede ver que el sistema agregó los datos en ambas secciones: en la niñera y en los controladores, aunque las niñeras no estaban allí. No hubo llamadas al búfer. Tiempo empleado, condición utilizada, aunque PostgreSQL podría resolverlo todo. Es decir, la declaración de eliminación de partición no funciona de inmediato. Quizás en las próximas versiones esto se corrija. En este caso, el módulo pg_pathman en este caso funciona sin problemas.

Índices

Optimización de ofertas de forma monótona, es decir, b-tree. Todo el mundo sabe que cuando inserta datos de crecimiento monótono, resulta que no es muy rápido. Ahora PostgreSQL puede almacenar en caché la página final de una manera especial y no ir desde la raíz para insertar. Esto acelera significativamente el trabajo.
PostgreSQL 10 hizo posible usar un índice hash porque comenzó a usar WAL (registro de escritura anticipada). Anteriormente, obtuvimos el valor, desbloqueamos la página, devolvimos el valor. Para el siguiente valor, tenía que bloquear la página nuevamente, regresar, desbloquear, etc. Ahora el hash se ha vuelto mucho más rápido. Le permite bloquear una página a la vez para recuperar un registro de un índice hash, devolver todos los valores desde allí y desbloquearlo. Ahora está implementado para HASH, GiST y GIN. En el futuro, esto probablemente se implementará para SP-GiST. Pero para BRIN con su lógica min / max esto no se puede hacer en principio.
Si solía crear índices funcionales, la actualización HOT (Heup Only Tuple) se deshabilitó efectivamente. Cuando se actualiza un registro en PostgreSQL, se crea una nueva copia, y esto requiere pegar en todos los índices que están en la tabla para que el nuevo valor apunte a la nueva tupla. Dicha optimización se ha implementado durante mucho tiempo: si la actualización no cambia los campos que no están incluidos en los índices y hay espacio libre en la misma página, los índices no se actualizan y, en la versión anterior de tupla, se coloca un puntero a la nueva versión. Esto le permite reducir un poco la gravedad del problema con las actualizaciones. Sin embargo, dicha optimización no funcionaría en absoluto si tuviera índices funcionales. En PostgreSQL 11, comenzó a funcionar. Si creó un índice funcional y actualiza una tupla que no cambia de qué depende el índice funcional, la actualización HOT funcionará.

Índices de cobertura

Esta funcionalidad fue implementada por PostgresPro hace tres años, y todo este tiempo PostgreSQL intentó agregarla. Los índices de cobertura significan que puede agregar columnas adicionales al índice único, directamente en la tupla de índice.

Por qué A todos les encanta el escaneo de solo índice por su trabajo rápido. Para esto, se construyen índices de "cobertura" condicionalmente:

Pero al mismo tiempo, debe mantener la singularidad. Por lo tanto, se están construyendo dos índices, estrecho y ancho.
La desventaja es que cuando aplica vacío, inserta o actualiza a una tabla, debe actualizar ambos índices. Por lo tanto, insertar en un índice es una operación lenta. Y el índice de cobertura permitirá administrar solo un índice.

Es cierto que tiene algunas limitaciones. Más precisamente, los beneficios que pueden no entenderse de inmediato. Las columnas cyd en el primer índice de creación no tienen que ser tipos escalares para los que se define un índice b-tree. Es decir, no necesariamente tienen una comparación más y menos. Pueden ser puntos o polígonos. Lo único es que la tupla debe ser inferior a 2,7 Kb, porque no hay tostado en el índice, pero puede encajar en lo que no se puede comparar.

Sin embargo, dentro del índice con estas columnas cubiertas garantizadas, no se realizan cálculos al buscar. Esto debe hacerse mediante un filtro que se encuentre por encima del índice. Por un lado, ¿por qué no calcularlo dentro del índice? Por otro lado, esta es una llamada de función adicional. Pero no todo es tan aterrador como parece.

Bueno, además, puede agregar estas columnas cubiertas a la clave primaria.

SP GiST

Pocas personas usan este índice porque es bastante específico. Sin embargo, se hizo posible almacenar en él no exactamente lo que se insertó. Esto se refiere a pérdida - índice, compresión. Toma los polígonos como ejemplo. En cambio, se coloca un cuadro delimitador en el índice, es decir, el rectángulo mínimo que contiene el polígono deseado. En este caso, representamos el rectángulo como un punto en el espacio de cuatro dimensiones, y luego trabajamos con el quad3 clásico, en el espacio de cuatro dimensiones.

También para el SP-GiST introdujo la operación "búsqueda de prefijos". Devuelve verdadero si una línea es un prefijo de otra. Lo presentaron no solo así, sino en aras de tal solicitud con soporte para SP-GiST.

SELECT * FROM table WHERE c ^@ „abc“

En b-tree hay un límite de 2.7 Kb por línea, pero SP-GiST no. Es cierto que PostgreSQL tiene una limitación: un solo valor no puede exceder 1 GB.

Rendimiento

El escaneo del índice de mapa de bits solo ha aparecido Funciona igual que el escaneo de índice clásico solamente, excepto que no puede garantizar ningún orden. Por lo tanto, es aplicable solo para algunos agregados como count (*), porque el mapa de bits no puede transferir campos del índice al ejecutor. Solo puede informar el hecho de un registro que satisfaga las condiciones.
La siguiente innovación es la actualización del Free Space Map durante la aplicación del vacío . Desafortunadamente, ninguno de los desarrolladores de sistemas que trabajan con PostgreSQL cree que es necesario eliminar al final de la tabla; de lo contrario, aparecen agujeros, espacios no asignados. Para rastrear esto, implementamos FSM, que nos permite no agrandar la tabla, sino insertar tuplas en huecos. Anteriormente, esto se hacía con vacío, pero al final. Y ahora el vacío puede hacer esto en el proceso, y en sistemas con mucha carga ayuda a mantener el tamaño de la mesa bajo control.
Posibilidad de omitir el escaneo de índice durante la ejecución de vacío . El hecho es que todos los índices PostgreSQL, según la teoría de la base de datos, se denominan secundarios. Esto significa que los índices se almacenan lejos de la tabla; los punteros se los llevan. La exploración de solo índice le permite no hacer este salto en punteros, sino tomar directamente del índice. Pero el vacío, que elimina registros, no puede mirarlos en el índice y decidir si los elimina o no, simplemente porque no hay tales datos en el índice. Por lo tanto, el vacío siempre se realiza en dos pasadas. Primero, revisa la tabla y descubre lo que necesita eliminar. Luego va a los índices adjuntos a esta tabla, elimina los registros que hacen referencia a lo encontrado, vuelve a la tabla y elimina a lo que iba. Y la etapa de ir a los índices no siempre es necesaria.

Si desde el último vacío no hubo eliminación o actualización, entonces no tiene registros muertos, no necesita eliminarlos. En este caso, no puede ir al índice. Hay sutilezas adicionales, b-tree no elimina sus páginas inmediatamente, sino en dos pasadas. Por lo tanto, si eliminó una gran cantidad de datos en la tabla, entonces debe hacer vacío. Pero si desea liberar espacio en los índices, aspire dos veces.

Alguien se sorprenderá, ¿cuál es esta tabla en la que no hubo eliminación o actualización? De hecho, muchos tratan con esto, simplemente no piensen. Estas son solo tablas de agregar, donde, por ejemplo, se agregan registros. En ellos, la eliminación es extremadamente rara. Y esto ahorra enormemente la duración del vacío / autovacío, reduce la carga en el disco, el uso de cachés, etc.
Compromiso simultáneo de transacciones competitivas . Esto no es una innovación, sino una mejora. Ahora PostgreSQL detecta que se comprometerá ahora y retrasa la confirmación de la transacción actual, esperando el resto de las confirmaciones. Tenga en cuenta que esta característica tiene poco efecto si tiene un servidor pequeño con 2–4 núcleos.
postgres_fdw (Contenedores de datos extranjeros) . FDW es una forma de conectar una fuente de datos externa para que parezca un verdadero post-Congreso. postgres_fdw le permite conectar una tabla de una instancia vecina a su instancia, y se verá casi como una real. Ahora se ha eliminado una de las restricciones para actualizar y eliminar. PostgreSQL a menudo puede adivinar que necesita enviar datos sin procesar. La forma de ejecutar la solicitud de unión es bastante simple: la ejecutamos en nuestra máquina, extraemos la tabla de la instancia usando FDW, descubrimos la clave primaria de identificación que necesitamos eliminar y luego aplicamos la actualización y / o eliminación, es decir, los datos que vamos y venimos. . Ahora es posible hacerlo. Por supuesto, si las tablas están en máquinas diferentes, esto no es tan fácil, pero FDW le permite hacer que la máquina remota realice operaciones, y solo esperamos.
toast_tuple_target . Hay situaciones en las que los datos van un poco más allá de los límites, después de los cuales es necesario brindar, pero al mismo tiempo brindar por tales valores no siempre es agradable. Suponga que tiene un límite de 90 bytes, y necesita un ajuste de 100. Debe iniciar una tostada de 10 bytes, agregarlos por separado, luego, cuando seleccione este campo, deberá ir al índice de tostadas, averiguar dónde están los datos necesarios, ir a la tabla de tostadas, coleccionar y dar.

Ahora, con la ayuda del ajuste fino, puede cambiar este comportamiento para toda la base de datos o una tabla separada para que las salidas tan pequeñas no requieran el uso de tostadas. Pero debes entender lo que estás haciendo, sin esto, nada funcionará.

WAL

WAL (registro de escritura anticipada) es un registro de escritura anticipada. El tamaño del segmento WAL ahora se establece en initdb. Gracias a Dios, no cuando compilamos.
La lógica también ha cambiado. Anteriormente, el conjunto de segmentos WAL se guardaba desde el momento del penúltimo punto de control, y ahora desde el último. Esto puede reducir significativamente la cantidad de datos almacenados. Pero si tiene una base de datos de 1 TB y TPS = 1, es decir, una solicitud por segundo, entonces no verá la diferencia.

Copia de seguridad y replicación

Truncate apareció en la replicación lógica . Fue la última de las operaciones DML que no se reflejó en la replicación lógica. Ahora reflejado.
Un mensaje sobre prepare apareció en la replicación lógica . Ahora puede capturar la transacción de preparación, una confirmación de dos fases en la replicación lógica. Esto se implementa para la construcción de grupos: heterogéneos, homogéneos, fragmentados y no sombreados, multimaster, etc.
Excepción de las tablas temporales y no registradas de pg_basebackup . Muchos se han quejado de que pg_basebackup incluye las tablas enumeradas. Y excluyéndolos, reducimos el tamaño de la copia de seguridad. Pero siempre que use tablas temporales y no registradas, de lo contrario esta opción será inútil para usted.
Control de suma de comprobación en la replicación de transmisión (para tablas) . Esto le permite comprender qué sucedió con su réplica. Hasta ahora, la función se implementa solo para tablas.
Hubo una promoción de las posiciones de la ranura de replicación . Como siempre, solo puede avanzar, retroceder solo si hay un WAL. Además, debe comprender muy bien lo que está haciendo con él y por qué. En mi opinión, esta es más una opción de desarrollo, pero aquellos que usan la replicación lógica para algunas aplicaciones exóticas pueden disfrutarla.

Para dba

Alterar tabla, agregar columna, no X nulo predeterminado , escribir toda la tabla. Hay una pequeña tarifa por esto: el valor predeterminado se almacena por separado. Si recoge la tupla y requiere esta columna, PostgreSQL se ve obligado a seguir una ruta de codificación adicional para extraer un valor temporal, sustituirlo en la tupla y dárselo. Sin embargo, uno puede vivir con eso.
Aspirar / analizar . Anteriormente, solo podía aplicar vacío o analizar a una base de datos completa o una sola tabla. Ahora es posible hacer esto en varias tablas, con un solo comando.

Ejecución paralela

Construcción paralela de índices b-tree . En la versión 11, se hizo posible incrustar índices b-tree en varios trabajadores. Si tiene una máquina realmente buena, muchos discos y muchos núcleos, puede construir índices en paralelo, esto promete un aumento notable en el rendimiento.
Hash conexión paralela utilizando una tabla hash compartida para ejecutores . , -. , . - , . .
, union, create table as, select create materialized view!
- (limit) . .

alter table usr reset (parallel_workers)
create index on usr(lower((so).occ)) — 2
alter table usr set (parallel_workers=2)
create index on usr(upper((so).occ)) — 1.8

parallel worker. . 16 4 ( ) 2 ., — 1,8 . , , . , .

:

explain analyze
select u1.* from usr u, usr1 u1 where
u.id=u1.id+0

, . , user — , . . , , .

, PostgreSQL 11 .

1425 , 1,5 . 1,4 . 2 . , 9.6 : 1 — 1 ., 2 1 . , 10 tuple. 11 . : user, batch, x-scan append .

:

. 211 , 702 . , 510 1473. , 2 .

parallel hash join. . — 4. , .

parallel index scan . batch . ¿Qué significa esto? hash join, . user . , parallel hash, .

1 . , OLAP-, OLTP . OLTP , .

. , . , «» «», index scan, . (highly skewed data), , . . , , .
«», .

Window-

SQL:2011, .

, , . , , , , , .

websearch, . , . , .

# select websearch_to_tsquery('dog or cat');
----------------------
'dor' | 'cat'
# select websearch_to_tsquery('dog -cat');
----------------------
'dor' & !'cat'
# select websearch_to_tsquery('or cat');
----------------------
'cat'

— dog or cat — . Websearch . | , . “or cat”. , . websearch “or” . , -, .

Websearch — . : , . , .

Json(b)

10- , 11- . json json(b), tsvector. ( json(b)) - . , , , bull, numeric, string, . .

# select jsonb_to_tsvector
('{"a":"texts", "b":12}', '"string"');
-------------------
'text':1
# select jsonb_to_tsvector
('{"a":"texts", "b":12}', '["string", "numeric"]');
-------------------
'12':3 'text':1

json(b), . , , , .

PL/*

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
FOR i IN 0..9 LOOP
INSERT INTO test1 (a) VALUES (i);
IF i % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
END
$$;
CALL transaction_test1();

call, , . . . select, insert .

, , PostgreSQL . Perl, Python, TL PL/pgSQL. Perl sp begin, .

PL/pgSQL : , .

pgbench

pgbench ICSB bench — , , . if, , . case, - . --init-steps , , .

random-seed. zipfian- . / — , . - , , - , .

, , - .

PSQL

, PSQL, . exit quit.

— copy, 2 ³² . copy : 2 ³² - . , 2 ³¹ 2 ³² copy . 64- , 2 ⁶⁴ .
POSIX : NaN ⁰ = 1 1 ^NaN = 1.

Tras la reunión "Nuevas funciones de PostgreSQL 11"