2. Difference between Postgres Pro Standard and PostgreSQL

2. Difference between Postgres Pro Standard and PostgreSQL
Prev	Up	Preface	Home	Next

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.

Note

Postgres Pro Standard is provided under the following license: https://postgrespro.com/products/postgrespro/eula. Make sure to review the license terms before downloading Postgres Pro Standard.

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.28.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.
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 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.)
The ability to explore corrupted databases using the pg_snapshot_any function. See Section 9.28.12 for details.
Enhanced performance of segment search for better speed of table size calculations.
Improved performance on multi-core systems by increasing the number of partitions of the shared buffer mapping hash table to 1024.
Faster WAL and TOAST processing and reduced disk space utilization by updating the default values of default_toast_compression and wal_compression to lz4.
Pluggable TOAST that allows you to develop and plug in custom TOAST implementations for table columns and data types in addition to the default one. (See Section 64.2.3 for details.)
Automatic detection of the locale provider based on the --lc-collate value if the locale provider is not specified in the database: libc is used for the C or POSIX locales, and icu is used for other locales. Also, the default value of the --icu-locale parameter is the setting of --locale if specified and otherwise the value of lc_collate. For more information, see initdb, createdb, and CREATE DATABASE.
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.)

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.
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.
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_proaudit extension that enables detailed logging of various security events.
pgpro_autopart extension that enables dynamic partition creation.
pg_trgm_mchar extension that allows fast trigram-based search for similar mchar and mvarchar strings. This extension is useful for 1C.
pgpro_anonymizer extension that provides the ability to mask or replace personally identifiable information or commercially sensitive data from a Postgres Pro database.
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.
pg_filedump application that displays formatted contents of a Postgres Pro heap, index, or control file.
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_datactl utility to manage Postgres Pro data files, which includes a module for estimating compression ratios for different compression algorithms.
pgpro_pwr extension that enables you to generate workload reports, which help to discover most resource-intensive activities in your database.
pgpro_scout utility to detect sensitive (confidential) information in your database. The utility performs data intelligence to determine whether such information exists and show sensitive data samples.
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.

Prev	Up	Next
1. What Is Postgres Pro Standard?	Home	3. A Brief History of PostgreSQL