El DBMS estándar de Postgres Pro está diseñado para entregar nuestros productos a los usuarios más rápido que nosotros a través de PostgreSQL. Aquellas características que aún no están incluidas en PostgreSQL, pero que están en una ruta sólida allí, las incluimos en Postgres Pro Standard. Además, Postgres Pro Standard incluye algunas extensiones que nuestros clientes exigen, pero que no están disponibles en la distribución estándar de PostgreSQL.
A veces hay excepciones cuando en Postgres Pro Standard, a petición de los usuarios y para su satisfacción, se incluyen características menos triviales, que en un buen lugar solo en Postgres Pro Enterprise. En particular, es PTRACK, sobre esto a continuación.
Postgres Professional desarrolló una parte justa de las extensiones y utilidades adicionales incluidas en Standard. Todos los parches Postgres Pro fueron inventados e implementados por nuestros propios esfuerzos. Comencemos con las mejoras que requirieron intervención en el motor de la base de datos.
Postgres Pro Standard difiere de PostgreSQL en dos niveles: el conjunto de extensiones y utilidades que se encuentran en el ensamblado, y el núcleo mismo. Se han aplicado algunos parches útiles al núcleo que optimizan el rendimiento (por ejemplo, un detector de bloqueo sin frenado) y parches que aumentan la eficiencia de las utilidades y extensiones (por ejemplo, para hacer que pg_probackup funcione con toda su fuerza, se aplica el parche PTRACK 2.0). Las diferencias entre la versión central de Standard y PostgreSQL se minimizan para la mayor compatibilidad posible. Digamos que la extensión pg_pathman es parte de Standard, pero se puede descargar desde el github, construir e instalar en el PostgreSQL de Vanilla, no habrá problemas de compatibilidad.
Comencemos con los cambios en el núcleo.
Comprobación de versiones de ICU
En PostgreSQL, por defecto, se usan para comparar cadenas comparándolas usando la biblioteca estándar C. Pero también existe la posibilidad de usar la biblioteca
ICU desarrollada por IBM para el mismo propósito. Esta biblioteca es valiosa para nosotros principalmente porque proporciona una clasificación independiente de la plataforma. Es por eso que, por ejemplo, se usa en 1C, y los ensamblajes "para uno" de PostgreSQL han estado trabajando con esta biblioteca durante mucho tiempo.
Además, las comparaciones de cadenas a través de la UCI a veces son más rápidas que a través de libc, y la cantidad de caracteres conocidos es mayor. En general, una biblioteca útil. Postgres Pro Standard ha estado trabajando con él desde la primera versión (9.5). En PostgreSQL, trabajar con ICU ha sido posible desde la versión 10.
La biblioteca es útil, pero debe tener en cuenta algunas situaciones de emergencia. Supongamos que un usuario de DBMS ha decidido actualizar el sistema operativo. Junto con el sistema operativo, la biblioteca de la UCI también se puede actualizar, y el orden de las palabras en la clasificación cambiará. Después de eso, inmediatamente todos los índices quedarán inutilizables: la búsqueda de índice dará resultados incorrectos. En tales casos, la base dijo que la versión de la UCI había cambiado y se detuvo.
Pero esta es una decisión dolorosamente difícil. Después de discusiones y una encuesta de clientes, se decidió suavizar el comportamiento. Ahora solo se verifican las versiones de COLLATION (reglas de clasificación). Si las versiones de COLLATION que se usan en la base de datos han cambiado, la base de datos emite una advertencia cuando se inicia el DBMS, pero no se detiene. También le recuerda al usuario al comienzo de cada sesión.
Optimización de cerraduras, uniones y GROUP BY
El mecanismo de detección de punto muerto puede degradar el rendimiento. Standard ya no puede: el parche del kernel le permite funcionar sin frenar. Después de importantes mejoras en el mecanismo de verificación, estos problemas aparecen solo en una gran cantidad de núcleos y conexiones.
Estimación mejorada del número de resultados de uniones en presencia de índices adecuados.
Ahora puede usar índices adecuados para agrupar y ordenar campos. Esta característica se incluyó por primera vez en Standard 11.1.1 y Enterprise 11.2.1. Nuestro estándar 12 también tiene uno.
Fedor Sigaev, CTO de Postgres Professional, ha ofrecido estos parches útiles a la comunidad, se están considerando y, con suerte, se incluirán en la versión PG 13.
Ilustramos la optimización de la operación GROUP BY con ejemplos: son claros y fácilmente reproducibles.
El punto de este parche es que Postgres no optimizó el orden de los campos enumerados en GROUP BY. Y el tiempo de ejecución depende de la secuencia de agrupación (con el mismo resultado de la consulta). Hay detalles en la
discusión sobre la lista de correo de los
hackers .
Si el valor en la primera columna que se procesará es único, entonces no es necesario comparar nada más. Si comienzas desde otra columna, entonces tienes que comparar.
Llegando a la prueba:
DROP TABLE IF EXISTS btg; SELECT i AS id, i/2 AS p, format('%60s', i%2) AS v INTO btg FROM generate_series(1, 1000000) i;
En el campo de texto v, se generan 60 espacios, seguidos de los números 0 o 1. Las entradas se ven así:
SELECT * FROM btg ORDER BY id DESC LIMIT 3; id | p | v
VACUUM btg; ANALYSE btg; SET enable_hashagg=off; SET max_parallel_workers= 0; SET max_parallel_workers_per_gather = 0;
Agrupe los resultados:
VACUUM btg; EXPLAIN ANALYZE SELECT count(*) FROM btg GROUP BY p, v;
Plan PostgreSQL:
QUERY PLAN
Ahora en el orden inverso: v, y solo entonces p:
EXPLAIN ANALYZE SELECT count(*) FROM btg GROUP BY v, p; QUERY PLAN
Resulta que lo contrario es notablemente más lento. Esto se debe a que el primer campo
v analiza con una pequeña extensión de valores. Debe hacer muchas comprobaciones en los campos restantes (aquí, el campo p).
Veamos cómo funcionará la misma consulta con un parche que selecciona el orden óptimo para procesar columnas:
QUERY PLAN
Y en orden inverso:
QUERY PLAN
El plan dice que allí y allá el orden de procesamiento es el mismo: Ordenar clave: p, v. En consecuencia, el tiempo es aproximadamente el mismo. Ahora compare lo que sucede cuando se usa el índice.
CREATE INDEX ON btg(p, v); SET enable_seqscan=off; SET enable_bitmapscan=off; VACUUM btg; EXPLAIN ANALYZE SELECT count(*) FROM btg GROUP BY v, p ;
En PostgreSQL:
QUERY PLAN
Y en orden inverso:
QUERY PLAN
Ahora en estándar:
QUERY PLAN
Y en orden inverso:
QUERY PLAN
El tiempo vuelve a ser el mismo, lo cual es natural: de hecho, las acciones son las mismas.
Sustitución de un byte nulo en el arranque
Postgres Pro no acepta cero bytes (0x00) en los datos, por lo que con COPY FROM deben ser reemplazados, de lo
contrario habrá un error . Este es el verdadero problema que el cliente encontró al importar datos de un archivo CSV. Su solución es reemplazar bytes nulos con el carácter ASCII dado. Debe ser diferente de los caracteres QUOTE y DELIMITER utilizados al ejecutar COPY FROM; de lo contrario, el resultado puede ser inesperado. De forma predeterminada, el valor de la variable nul_byte_replacement_on_import (string) '\ 0', es decir, no se realiza ningún reemplazo.
WaitLSN
LSN es un
número secuencial en el registro , es decir, un puntero a una posición en el WAL (Número de secuencia de registro). El comando WAITLSN está esperando para reproducir el LSN especificado. Si la aplicación funciona tanto con el maestro como con la réplica, debe asegurarse de que estén sincronizados de vez en cuando. WAITLSN es un mecanismo entre procesos en PostgrePro que controla la sincronización durante
la replicación sincrónica . Por defecto, el tiempo de espera es ilimitado. Puede cancelar la espera presionando Ctrl + C o deteniendo el servidor postgres. También puede establecer el tiempo de espera agregando la sugerencia TIMEOUT, o verificar el estado del LSN objetivo sin esperar utilizando la sugerencia NOWAIT.
Supongamos que una aplicación realiza una determinada acción, recibe el número LSN del DBMS en el maestro y ahora quiere asegurarse de que las acciones en la réplica se sincronizarán con el maestro, es decir la aplicación puede estar segura de que lo que registró en el asistente ya llegó a la réplica y está listo para leer. Por defecto, esto generalmente no está garantizado. WAITLSN le permite controlar esta interacción y seleccionar un modo de suspensión desde INFINITAMENTE por defecto, hasta TIEMPO DE ESPERA y AHORA.
Releyendo variables de la antigua recovery.conf
En una señal SIGHUP, PostgreSQL vuelve a leer postgresql.conf, pero no recovery.conf. Un parche de kernel relativamente nuevo introducido en Standard y Enterprise 10.4.1. obligado a releer y recovery.conf. Pero en Postgres 12 no hay ningún archivo recovery.conf: todas las variables se transfieren a postgresql.conf. Sin embargo, aunque todo el archivo se vuelve a leer, SIGHUP no redefinió las variables de recovery.conf, sino que requirió un reinicio de Postgres. En Standard, esto no es obligatorio: todo se lee y se redefine.
Soporte PTRACK
PTRACK 2.0 es un mecanismo PTRACK rediseñado para las versiones Standard y Enterprise 11 y anteriores. En el nivel DBMS, funcionó gracias al parche del núcleo, y ahora la extensión ptrack se ha agregado al
parche . PTRACK 2.0 rastrea los cambios en la página de datos y proporciona una interfaz para recuperar esta información. Se puede usar tanto con fines de diagnóstico, por ejemplo, para tener una idea de cuán fuertemente "muta" la instancia en relación con un punto específico en el tiempo, establecerla como un número secuencial en el registro (LSN) y crear copias de seguridad incrementales.
La parte más difícil y "costosa" de un procedimiento de copia de seguridad incremental, como regla, es aislar un subconjunto de páginas cambiadas de todo el conjunto de páginas en un clúster. Debido al hecho de que el servidor puede asumir esta tarea y proporcionar rápidamente información sobre las páginas modificadas, el tiempo de las copias de seguridad incrementales usando PTRACK se reduce significativamente.
PTRACK 2.0 utiliza una tabla hash de un tamaño especificado en la memoria compartida, sincronizada periódicamente con el archivo ptrack.map.
Debido a una alteración fundamental del mecanismo interno de operación y una interfaz de usuario incompatible con versiones anteriores, la extensión ptrack solo está disponible en la versión 12 de PostgresPro Standard y Enterprise, y estará disponible como parche y extensión en PostgreSQL 12.
Edición de comandos en psql para Windows
El soporte avanzado para editar comandos de entrada en psql para Windows se implementa usando WinEditLine. Ahora puede mostrar los caracteres de diferentes alfabetos simultáneamente (en particular, el cirílico normalmente se muestra en Windows no ruso).
Estructura de paquete unificado
La estructura de los paquetes binarios para todas las distribuciones de Linux está unificada para simplificar la migración entre ellos y permitir la instalación de varios productos basados en PostgreSQL juntos sin ningún conflicto. Esto se puede encontrar en el
Capítulo 16 de la Documentación.
Ahora sobre las extensiones:
dump_stat
Apareció ya en 9.5. Al transferir o restaurar datos, las estadísticas acumuladas generalmente no se transfieren. Si lo vuelve a ensamblar con el comando ANALIZAR, se ejecutará para todo el clúster y no para la base de datos especificada. Esto puede requerir mucho tiempo extra para grandes bases de datos.
La extensión dump_stat
proporciona funciones que le permiten descargar y restaurar el contenido de la tabla pg_statistic. Al realizar la carga / recuperación de datos, puede usar dump_stat para transferir estadísticas existentes a un nuevo servidor, sin tener que ejecutar el comando ANALIZAR para todo el clúster.
La función dump_statistic descarga el contenido del catálogo del sistema pg_statistic. Produce un INSERT para cada tupla en pg_statistic, excepto las que contienen estadísticas sobre tablas en los esquemas information_schema y pg_catalog.
jsquery
Recuerde que
esta es una extensión para trabajar con JSON (B), no con JS. Proporciona un conjunto de funciones para procesar estos tipos de datos. Este es un lenguaje de consulta especial para la búsqueda eficiente, mediante índices, en JSON (B). En el
artículo sobre el centro, puede ver algunos ejemplos de jsquery y métodos alternativos para trabajar con JSON (B), por ejemplo, JSONPath (ambos desarrollos de nuestra compañía).
online_analyze
Esta extensión
proporciona un conjunto de funciones que actualizan inmediatamente las estadísticas en las tablas que se especifican después de las operaciones INSERT, UPDATE, DELETE o SELECT INTO en ellas. El autor de la extensión es Fedor Sigaev.
Para usar el módulo online_analyze, debe cargar la biblioteca compartida:
LOAD 'online_analyze';
Las actualizaciones de estadísticas se pueden personalizar. Por ejemplo, establezca un porcentaje del tamaño de la tabla o el número mínimo (umbral) de cambios de fila, después de lo cual se recopilarán estadísticas inmediatamente.
pg_pathman
La
extensión pg_pathman en Postgres Professional se creó antes que en el núcleo de PostgreSQL e implementó un conjunto bastante completo de funciones para crear particiones. Por lo tanto, muchas operaciones con secciones se pueden realizar con uno y otro mecanismo. Solo es aconsejable no mezclar las secciones creadas por la partición declarativa y pg_pathman.
Sin embargo, muchas operaciones de pg_pathman son aún más rápidas y faltan algunas características en PostgreSQL. Por ejemplo, creación automática (corte) de secciones. En PostgreSQL, debe establecer los límites de cada sección. Si completamos datos sobre los cuales no se sabe de antemano cuántas secciones pueden y deben estar dispersas, entonces es conveniente simplemente establecer el intervalo y dejar que el software corte las secciones por sí mismo, tanto como sea necesario. pg_pathman sabe cómo, PostgreSQL no. Pero, comenzando con PG 11, hay una sección predeterminada (predeterminada), donde puede volcar todos los registros que no caen en secciones con límites específicos.
Existe un acuerdo básico con los líderes de la comunidad PostgreSQL que en el futuro será el mejor, mientras que las características únicas de pg_pathman caerán en la rama principal. Pero hasta ese momento, pg_pathman puede hacer la vida más fácil para los administradores de aplicaciones DB y los programadores de aplicaciones.
Crea una extensión:
CREATE EXTENSION pg_pathman;
pg_pathman le permite dividir tablas grandes en secciones y proporciona una API conveniente: un conjunto de funciones para crear secciones y trabajar con ellas. Por ejemplo, usando la función
create_range_partitions(relation REGCLASS, expression TEXT, start_value ANYELEMENT, p_interval INTERVAL, p_count INTEGER DEFAULT NULL, partition_data BOOLEAN DEFAULT TRUE);
podemos preguntar
SELECT create_range_partitions('log', 'dt', NULL::date, '1 month'::interval);
después de lo cual agregamos secciones:
SELECT add_range_partition('log', NULL, '2017-01-01'::date, 'log_archive', 'ts0'); SELECT add_range_partition('log', '2017-01-01'::date, '2017-02-01'::date, 'log_1'); SELECT add_range_partition('log', '2017-02-01'::date, '2017-03-01'::date', log_2');
El registro de archivo se creará en el espacio de tabla ts0, el resto son por defecto. Pero no puede especificar secciones explícitamente, pero confíe en esta operación DBMS configurando el intervalo y creando secciones en un solo paso:
SELECT create_range_partitions('log', 'dt', '2017-01-01'::date, '1 month'::interval);
En una tabla simple, se verá así:
CREATE TABLE pg_pathmania(id serial, val float); INSERT INTO pg_pathmania(val) SELECT random() * 1000 FROM generate_series(1, 1000); SELECT create_range_partitions('pg_pathmania', 'id', 0, 50); test_parti=# \d+ pg_pathmania Table "public.pg_pathmania" Column | Type | Collation | Nullable | Default | Storage | S tats target | Description
En PostgreSQL, tendríamos que crear cada sección con nuestro propio equipo. En tales casos, por supuesto, escriben un script que genera el código DDL requerido automáticamente. No necesita escribir scripts en pg_pathman, todo ya está allí. Pero esto no es lo más interesante. Insertaremos un registro que no solo no se obtiene por identificación en ninguna de las secciones existentes, sino que tampoco se incluye en la más cercana:
INSERT INTO pg_pathmania(id, val) VALUES (2000, 277.835794724524);
Nuevamente, verifique el contenido de la tabla con \ d + pg_pathmania:
Child tables: pg_pathmania_1, pg_pathmania_10, ... pg_pathmania_39, pg_pathmania_4, pg_pathmania_40, pg_pathmania_41,
Esto es lo que sucedió: pg_pathman vio que el registro con id = 2000 no cae en las secciones ya creadas, calculó cuántas necesitan crearse, conociendo el intervalo de RANGO con el que se dividió la tabla antes, y creó la sección donde cae el nuevo registro y, por supuesto, todas las secciones entre el límite superior de las secciones antiguas y el límite inferior de la nueva sección. Esto es muy conveniente, y en los casos en que los valores del campo de división de los datos actualizados no se predicen adecuadamente, esta es una gran ventaja de pg_pathman.
pg_query_state
Esta extensión que desarrollamos nos
permite conocer el estado actual de las solicitudes en el proceso de publicación. Ha existido desde la versión 9.5 y se debe al nacimiento de numerosas solicitudes de administradores de clientes.
El hecho es que EXPLAIN ANALYZE le permite ver las estadísticas de ejecución recopiladas de cada nodo del árbol del plan, pero estas estadísticas se recopilan solo después de que se completa la consulta. Pero en la vida, por desgracia, hay situaciones en las que necesita ver lo que la solicitud aún no se ha completado y tal vez no va a terminar. pg_query_state le permite ver las estadísticas actuales de una consulta que se ejecuta en un proceso de servicio externo. En este caso, el formato de la salida resultante es casi idéntico a la salida del comando EXPLAIN ANALYZE habitual.
Utilidades:
pgBouncer
Este es un
extractor de conexiones tan
popular que sería extraño hablar de eso aquí. Es solo que es parte de Standard, y tendrá que instalarse por separado en el caso de Vanilla PostgreSQL.
pg_probackup
pg_probackup es uno de nuestros desarrollos más populares. Este es un administrador de respaldo y recuperación que está siendo desarrollado y actualizado principalmente por Anastasia Lubennikova, Grigory Smolkin y la comunidad de usuarios.
Ventajas competitivas de pg_probackup: copia de seguridad incremental con granularidad de bloque (8 KB), tres modos de copia de seguridad incremental (PAGE, DELTA, PTRACK), verificación de integridad de copia de seguridad a pedido, verificación de clúster PostgreSQL, compresión de copia de seguridad, recuperación parcial, etc.
El modo de copia incremental PTRACK, que se basa en
la extensión del mismo nombre como parte del mecanismo rediseñado, PTRACK 2.0, se ha vuelto aún más rápido y ahora es inequívocamente el modo más rápido y más barato de pg_probackup.
pg_repack
pg_repack es una utilidad popular, su funcionamiento es similar a VACUUM FULL o
CLUSTER . No solo reempaqueta tablas, elimina vacíos, sino que también sabe cómo restaurar el orden físico de los índices agrupados. A diferencia de CLUSTER y VACUUM FULL, realiza estas operaciones "sobre la marcha", sin bloqueos de tabla exclusivos y, en general, funciona de manera eficiente. No está incluido en la versión vainilla.
pg_variables
Sobre esta extensión en un habr hay un interesante
artículo de nuestro empleado Ivan Frolkov. La razón de la extensión es que trabajar con resultados intermedios a veces es inconveniente y costoso. El artículo explora alternativas. Las más comunes son las tablas temporales.
Como almacén de datos temporal, la extensión pg_variables es mucho más productiva que las tablas temporales (las pruebas de pgbench están en el artículo), y es más conveniente: el conjunto de datos se define mediante un par "paquete - variable", que puede pasarse como parámetros, devolverse de una función, etc. Hay funciones set / get para trabajar con variables. Entonces, por ejemplo, puede almacenar muchas variables (paquete es el nombre del paquete, y la expresión después del punto decimal son las variables en este paquete:
SELECT pgv_set_int('package','#'||n,n), n FROM generate_series(1,1000000) AS gs(n);
Las variables tienen una propiedad interesante: no un error o ventaja, sino una característica: los datos almacenados por los medios de extensión existen fuera de las transacciones: se guardan tanto en el caso de arreglar una transacción como en caso de reversión; Además, incluso cuando se ejecuta un comando separado, se pueden obtener datos parciales:
SELECT pgv_insert('package', 'errs', row(n)) FROM generate_series(1,5) AS gs(n) WHERE 1.0/(n-3)<>0; ERROR: there is a record in the variable "errs" with same key test_parti=# SELECT * FROM pgv_select('package','errs') AS r(i int); i
Por un lado, esto no es muy conveniente: en algunos casos es necesario proporcionar la eliminación de datos ingresados incorrectamente, pero en otros puede resultar muy útil, por ejemplo, guardar algunos datos incluso en el caso de una reversión de la transacción. La
documentación tiene detalles.
En conclusión, algunas extensiones más:
sr_plan, plantuner
sr_plan guarda y restaura los planes de consulta. Inclúyalo así:
SET sr_plan.write_mode = true;
Después de eso, los planes para todas las consultas posteriores se almacenarán en la tabla sr_plans hasta que esta variable se establezca en falso. Se guardan los planes para todas las solicitudes, incluidas las repetidas.
plantuner admite sugerencias para que el planificador conecte o desconecte índices específicos al ejecutar una consulta. Solo hay dos variables GUC: enable_index / desable_index:
SET plantuner.disable_index='id_idx2';
Extensiones para búsqueda de texto completo: shared_ispell, pg_tsparser
La extensión shared_ispell, que le permite colocar
diccionarios en la memoria compartida, está en Standard y no en PostgreSQL. Nuestro set hunspell-dict tiene diccionarios para idiomas:
- hunspell_en_us,
- hunspell_fr,
- hunspell_nl_nl,
- hunspell_ru_ru
La
extensión pg_tsparser es
un analizador de búsqueda de texto
alternativo . Esta extensión cambia la estrategia de análisis de texto estándar para palabras que incluyen guiones bajos, así como números y letras separados por guiones bajos. Además de las partes individuales de la palabra devueltas por defecto, pg_tsparser también devuelve la palabra completa. Esto es muy importante para la documentación técnica o artículos como este, en los que se encuentra el código del programa, y en él hay palabras como "pg_tsparser", "pg_probackup", "jsonb_build_object". Este analizador percibe estas palabras no solo como un conjunto de componentes, sino también como un token único, y por lo tanto mejora la calidad de la búsqueda.
Extensiones para 1C
- mchar es un tipo de datos opcional para compatibilidad con Microsoft SQL Server;
- fulleq : proporciona un operador de igualdad adicional para la compatibilidad con Microsoft SQL Server;
- fasttrun — - , pg_class.
, PostgresPro Standard PostgreSQL. , , , ,
.