2. Difference between Postgres Pro Enterprise 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 Enterprise users thus have early access
to important features and fixes.
Postgres Pro Enterprise provides the following enhancements
over PostgreSQL:
- 64-bit transaction IDs that are not subject to wraparound.
(See Section 24.1.5.)
- Page-level compression. (See Chapter 33.)
- Support for autonomous transactions. (See Chapter 16.)
- Lazy placement of temporary tables on disk.
Disk space for temporary tables is allocated only when they
exceed the temp_buffers size and have to be
spilled to disk. Since disk space for temporary tables is not
reserved in advance anymore, it allows to significantly reduce
disk usage when working with multiple small temporary tables.
-
Automatic page repair via streaming replication from standby
in case of data corruption. (See Section 26.2.5.3.)
-
Fair lightweight lock scheduling after the specified number of
shared locks is acquired. (See lwlock_shared_limit
parameter description.)
-
Controlling the amount of cache used by prepared statements.
With plan_cache_lru_size or
plan_cache_lru_memsize configuration
parameters enabled, query trees and generic plans of
the least recently used statements are evicted from cache once
the specified limit is reached.
-
Improved prioritization of sequential and index scans.
(See seq_scan_startup_cost_first_row parameter description.)
-
Improved multi-host connection handling and failover
by libpq. (See hostorder
and failover_timeout parameter descriptions.)
-
Enabling libpq to forget the entered password,
which allows to prevent reconnections when required by a security policy.
(See reusepass parameter description.)
-
Support for timestamp output in pg_waldump.
-
Support for relaxed synchronous replication restrictions, which allows
the primary server to continue running while some of the standbys are temporarily unavailable.
(See synchronous_standby_gap parameter description.)
-
The autoprepare mode that allows to implicitly prepare
frequently used statements, thus eliminating the cost of their compilation
and planning on each subsequent execution. (See Section 14.6.)
-
Support for changing configuration of other sessions.
For example, you can use this feature to switch on debug
messages to trace sessions with unexpected behavior.
(See Section 9.28.1.)
- K-nearest neighbors (k-NN) algorithm for B-tree indexes.
(See Section 11.13.)
-
Removing a practically reachable limit on the number of entries
in ACL (access control list, i.e., privileges list) associated
with tables and indexes.
-
The pgpro_stat_wal_activity view that shows
the size of WAL files generated by each process.
-
Restoration of corrupted WAL data from in-memory WAL buffers.
-
Verification of unique constraints in B-tree indexes in the
amcheck module.
-
Support for packages, which are essentially enhanced schemas
that help to organize named objects with a related purpose.
This feature provides extended functionality, familiar to Oracle
users, for PL/pgSQL where new
function modifiers and
conventions were introduced, as well as new
CREATE PACKAGE
and DROP PACKAGE
commands.
-
Support for passing named and positional arguments to scripts invoked
by the
\i command in
psql.
-
pg_probackup
enterprise edition, which provides Simple Storage Service (S3) support
for storing data in private clouds,
CFS (Compressed File System) support
for incremental backups, and support for lz4 and zstd
compression algorithms.
-
Lock deduplication that allows to effectively store in memory
and track all exclusive locks held by a standby server's startup process
during WAL replay.
-
The
vault
schema that allows protecting sensitive data against unauthorized access
of malicious users by designating a separate role called security officer
who manages access to the schema and its objects.
-
The built-in high availability that is achieved by deploying the
BiHA cluster with physical replication, built-in
failover, automatic node failure detection, response, and subsequent
cluster reconfiguration. Such cluster is configured with one dedicated
leader node and several follower nodes, which can be both synchronous and
asynchronous. The new functionality enables protection against server
failures and data storage system failures and does not require any
additional external cluster software.
-
Adaptive query execution (AQE), which
enables reoptimizing a query, if during the execution some trigger
indicates that it is non-optimal, so a more optimal plan should be
looked for.
-
Background freezer that vacuums
modified and non-locked data pages of permanent tables stored in RAM. This
feature is useful for 1C.
-
Enhanced lock logic to reduce CPU consumption significantly in case of
startup recovery on physical replicas used as a source for logical
replication with many downstreams. Walsenders do not slow down WAL
application as a more efficient synchronization primitive is used instead
of spinlock.
-
Enabling the query planner to overestimate the expected number
of rows in statements that contain a comparison to an unknown constant.
This feature is useful for 1C. (See
planner_upper_limit_estimation parameter description.)
-
Using an in-memory system catalog for temporary tables. This feature is
useful for 1C. (See
enable_temp_memory_catalog parameter description.)
-
The experimental possibility for the autovacuum daemon to process indexes
of a table in a parallel mode. This feature is useful for 1C.
(See parallel_autovacuum_workers and
max_parallel_autovacuum_workers parameter descriptions.)
-
Optimized mechanism for working with table metadata, which allows
obtaining information about attributes using the system cache instead of
direct reading from the system catalog.
-
Skipping locking for temporary relations and indexes on these relations
as well as restricting superuser actions with temporary relations of other
sessions. This is useful for 1C. (See
skip_temp_rel_lock parameter description.)
-
Controlling the number of insertion locks on concurrent write-ahead
logging. This helps to improve the efficiency of writing write-ahead logs.
(See num_xloginsert_locks parameter description.)
The following enhancements are inherited from
Postgres Pro Standard:
- 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.)
-
Changing the
restore_command
parameter without restarting the server.
-
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 17.)
-
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.28.12.)
-
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.
-
Improved partitioned table management by means of the
SPLIT PARTITION
and MERGE PARTITIONS
subcommands in the ALTER TABLE command, which split
a single partition into several partitions and merge
several partitions into one, respectively.
-
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 Enterprise also includes the
following additional modules and applications:
-
apache_age
extension that provides graph database functionality.
- aqo extension for adaptive query optimization.
- biha extension, which is managed with the
bihactl utility, that turns Postgres Pro
into a cluster with physical replication and built-in failover, high
availability, and node failure recovery.
-
daterange_inclusive extension, which allows you to
include the upper bound of a time range in the output.
- dbms_lob
extension that allows accessing and manipulating specific parts of a LOB
or complete LOBs.
- 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.
-
in_memory module
that enables you to store data in Postgres Pro shared memory.
- jsquery module that
provides a specific language for effective index-supported querying of JSONB data.
- mamonsu monitoring service, which
is implemented as a Zabbix agent.
- mchar module that provides
additional data type for compatibility with Microsoft SQL Server.
- multimaster extension
that turns Postgres Pro Enterprise into a synchronous shared-nothing
cluster to provide Online Transaction Processing (OLTP) scalability for read
transactions and high availability with automatic disaster recovery.
- 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_hint_plan
module that controls the execution plan by providing hints to the planner.
- pg_integrity_check
module that calculates and validates checksums for controlled files. (Certified edition only.)
-
pg_proaudit extension that enables detailed
logging of various security events.
-
pgpro_anonymizer
extension that provides the ability to mask or replace personally
identifiable information or commercially sensitive data
from a Postgres Pro database.
-
pgpro_application_info
extension designed to help developers who port applications
using the
DBMS_APPLICATION_INFO package
from Oracle to Postgres Pro.
-
pgpro_autopart extension that enables dynamic partition creation.
- pg_probackup, a backup and recovery manager.
-
pg_probackup3
solution for backup and recovery of Postgres Pro database
clusters.
-
pgpro_bfile extension providing a composite type
bfile that implements an Oracle-like technique to access an external
file.
-
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_datactl
utility to manage Postgres Pro data files, which includes a
module for unpacking and analyzing CFS files.
- pgpro_gbtree extension that enables
creation and use of a global index on a partitioned table. The global index is created for one or several
columns of a partitioned table and does not include the partition key. It allows you to perform index scan
without iterating through all the partitions.
- pgpro_ilm extension that enables moving rarely
used relations to a lower-cost storage. This feature is similar to Oracle's information
lifecycle management (ILM).
- pgpro_multiplan
extension that allows the user to save a specific
plan of a parameterized query for future usage regardless of how
planner settings may change.
- pgpro_pwr extension that enables you
to generate workload reports, which help to discover most resource-intensive activities in your database.
- pgpro_queue
extension that provides message queueing management directly within
the database.
-
pgpro_result_cache
extension to save query results for reuse.
-
pgpro_rp
extension that implements resource prioritization feature,
which allows allocating more resources to high-priority sessions.
- pgpro_scheduler module that provides background workers for task scheduling.
- pgpro_sfile
module, which is similar to Oracle LOBs. It allows storing multiple large
objects, called
sfile objects. The
maximum number of such objects as well as object size in bytes is limited
by 2^63 - 1.
- 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.
- pgpro_usage
extension that provides per-user statistics on accessing relations and calling
functions, including a view that shows which privileges are unused and functions
that return the date and time of the last access to tables.
- 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_transfer module that provides support for relocatable 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.
-
proxima
extension that combines functionality of a proxy server and a connection
pooler.
- rum module that
provides RUM index based on GIN.
- shared_ispell
module that enables storing dictionaries in shared memory.
-
utl_http
extension that allows accessing data on the Internet over the HTTP
protocol by invoking HTTP callouts from SQL and
PL/pgSQL.
-
utl_mail
extension designed for managing emails, which includes commonly
used email features, such as attachments, CC, and BCC.
-
utl_smtp
extension designed for sending emails over SMTP from
PL/pgSQL.
-
pgpro_tde
extension which enables page level encryption in Postgres Pro Enterprise.
To provide the advanced functionalities and features, Postgres Pro
imposes more stringent requirements on operating systems supported.
Important
Postgres Pro Enterprise 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 Enterprise releases follow PostgreSQL releases, though sometimes occur more frequently.
The Postgres Pro Enterprise versioning scheme is based on the PostgreSQL one and has an additional decimal place.