2. Difference between Postgres Pro Standard and PostgreSQL #
Postgres Pro provides the most actual PostgreSQL
version with some additional patches applied and extensions added.
It includes new features developed by Postgres Professional,
as well as third-party patches already accepted by the PostgreSQL community
for the upcoming PostgreSQL versions.
Postgres Pro Standard users thus have early access
to important features and fixes.
Postgres Pro Standard provides the following enhancements
over PostgreSQL:
- Improved deadlock detection mechanism that does not cause performance degradation.
-
Better planning speed and accuracy for various query types.
-
Reduced memory consumption in complex queries that involve multiple tables.
-
Displaying planning time in the output of the
auto_explain module.
NUL byte replacement with the specified ASCII code while
loading data using the COPY FROM command. (See
nul_byte_replacement_on_import parameter
description.)
'\u0000' character replacement with the specified
unicode character when calling a function processing JSONB (See
unicode_nul_character_replacement_in_jsonb parameter
description.)
- PTRACK implementation, which enables pg_probackup
to track page changes on the fly when creating incremental backups.
-
Support for reading
pg_control of previous
PostgreSQL/Postgres Pro major
versions by pgpro_controldata.
-
Cluster compatibility verification, which allows you to determine whether the
current Postgres Pro version is compatible with
the specified cluster and identify all parameters that can affect
the compatibility without starting the cluster.
(See pgpro_controldata and
-Z option of postgres.)
-
Unified structure of binary installation packages across all
Linux distributions, which facilitates migration between them and
allows installing PostgreSQL-based
products side by side, without any conflicts. (See Chapter 16.)
-
Operation log, which stores information about system events such as an
upgrade, execution of pg_resetwal and so on,
which is highly useful for vendor's technical support. Recording to the
operation log is only done at the system level, and SQL functions are
used to read it. (See Section 9.27.11.)
-
Advanced authentication policies that provide effective password management and access control.
(See CREATE PROFILE and ALTER ROLE).
-
Built-in data security mechanisms that enable sanitizing an object
by filling it with zeroes before deletion. Zeroing can be done before
purging files in external memory and removing outdated row versions (page vacuum),
freeing RAM, and deleting or overwriting WAL files. (Certified edition only.)
-
Statistics about vacuuming tables, indexes and databases in system
views.
-
Predefined roles, which
allow creating tablespaces and managing profiles without superuser rights.
-
Getting information on crashes of a backend, which is enabled by the
crash_info configuration parameter and controlled
by more of them.
-
Optimized memory consumption during selectivity estimation for each
array element.
-
The experimental feature that enables the parallel
query execution mechanism for queries that involve at least one
temporary table. This feature is useful for 1C.
(See enable_parallel_temptables parameter description.)
-
Estimating the cost of flushing temporary table pages to disk. This feature
is useful for 1C. (See write_page_cost
parameter description.)
-
Enabling transformation of
ANY subqueries into
LATERAL joins. (See enable_any_to_lateral_transformation
parameter description.)
Postgres Pro Standard also includes the
following additional modules and applications:
- aqo extension for adaptive query optimization.
- auto_dump
module that collects data on long-running and problematic queries and
reproduces these problems for troubleshooting.
-
daterange_inclusive extension, which allows you to
include the upper bound of a time range in the output.
- dump_stat module that allows
to save and restore database statistics when dumping/restoring the database.
- fasttrun module
that provides transaction-unsafe function to truncate temporary tables
without growing
pg_class size. - fulleq module that
provides additional equivalence operator for compatibility with Microsoft SQL Server.
- hunspell-dict module that provides
dictionaries for several languages.
- hypopg extension, which provides
support for hypothetical indexes in Postgres Pro.
- jsquery module that
provides a specific language for effective index-supported querying of JSONB data.
- ldap2pg
utility that automates the creation, update, and removal of
Postgres Pro roles and users from an enterprise
directory.
- mamonsu monitoring service, which
is implemented as a Zabbix agent.
- mchar module that provides
additional data type for compatibility with Microsoft SQL Server.
- online_analyze
module that provides a set of changes to immediately update statistics after
INSERT, UPDATE, DELETE
or SELECT INTO operations applied for affected tables. - pgbadger application
that rapidly analyzes Postgres Pro logs, producing detailed reports
and graphs.
- pgbouncer connection pooler.
- pg_integrity_check
module that calculates and validates checksums for controlled files. (Certified edition only.)
-
pg_pathman module that
provides optimized partitioning mechanism and functions to manage partitions.
Starting from Postgres Pro 12, using
pg_pathman is not recommended. Use vanilla
declarative partitioning instead, as described in
Section 5.11.
-
pg_proaudit extension that enables detailed
logging of various security events.
-
pg_trgm_mchar
extension that allows fast trigram-based search for similar
mchar
and mvarchar strings. This extension is useful for 1C.
- pg_probackup, a backup and recovery manager.
-
pg_probackup3
solution for backup and recovery of Postgres Pro database
clusters.
-
pgpro_bindump module to manage backup and restore
operations. This module implements specialized replication commands for
an extended replication protocol, has its own format for archiving files,
and does not require SSH connection. It is designed specifically for use
with the pg_probackup3
utility.
- pgpro_controldata, an application to
display control information of a
PostgreSQL/Postgres Pro
database cluster and compatibility information for a
cluster and/or server.
- pgpro_cpumeter,
an extension providing access to a new CPU usage monitoring system function.
This
pgpro_cpumeter function continuously collects
and records information about CPU cores used by
Postgres Pro server processes.
- pgpro_pwr extension that enables you
to generate workload reports, which help to discover most resource-intensive activities in your database.
- pgpro_stats extension that tracks execution statistics
of SQL statements, calculates wait event statistics and provides other useful
metrics that are not collected elsewhere in PostgreSQL.
It also provides tracing of application sessions and can create views that emulate
other statistic collecting extensions.
-
pgpro_tune utility
for automatic tuning of Postgres Pro
configuration parameters. It provides a set of built-in presets including
a preset for 1C-specific configuration.
- pg_query_state
module that enables you to get the current state of query execution for a backend.
- pg_repack
utility for reorganizing tables.
- pg_tsparser module,
which is an alternative text search parser.
- pg_variables module
that provides functions for working with variables of various types. To facilitate migration
of Oracle code that processes collections, these functions include those that
allow working with general collection variables, whose elements can be accessed
by a key that can have either integer or text type, and those that provide iterator
functionality for any collections.
-
pgvector extension that provides
vector similarity search for Postgres Pro.
-
pg_wait_sampling
extension for sampling-based statistics of wait events. With this extension,
you can get an insight into the server activity, including the current wait
events for all processes and background workers.
- plantuner
module that provides hints for the planner to disable or enable indexes for query execution.
- rum module that
provides RUM index based on GIN.
- shared_ispell
module that enables storing dictionaries in shared memory.
To provide the advanced functionalities and features, Postgres Pro
imposes more stringent requirements on operating systems supported.
Important
Postgres Pro Standard runs on all major Linux operating systems.
Any reference to Windows or another operating system different from Linux in this documentation
is inapplicable for Postgres Pro.
Postgres Pro Standard releases follow PostgreSQL releases, though sometimes occur more frequently.
The Postgres Pro Standard versioning scheme is based on the PostgreSQL one and has an additional decimal place.