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.
Note
Postgres Pro Enterprise is provided under the following license: https://postgrespro.com/products/postgrespro/eula. Make sure to review the license terms before downloading Postgres Pro Enterprise.
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 32.)
- 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
amcheckmodule. - Login event triggers, which fire when a user connects to the database after authentication. For example, you can use this feature to verify the connection and assign roles according to circumstances, or for session data initialization.
- 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 PACKAGEandDROP PACKAGEcommands. - Support for passing named and positional arguments to scripts invoked by the
\icommand 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
vaultschema 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.
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 FROMcommand. (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_controlof 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
-Zoption of postgres.) - Changing the
restore_commandparameter 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 PARTITIONandMERGE PARTITIONSsubcommands in theALTER TABLEcommand, which split a single partition into several partitions and merge several partitions into one, respectively.
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.
- 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_classsize. - 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,DELETEorSELECT INTOoperations 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_INFOpackage from Oracle to Postgres Pro. - pgpro_autopart extension that enables dynamic partition creation.
- pg_probackup, a backup and recovery manager.
- pgpro_bfile extension providing a composite type
bfilethat implements an Oracle-like technique to access an external file. - 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_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_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
sfileobjects. 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_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.
- vops experimental module that provides a vertical data model for Postgres Pro Enterprise. It can speed up OLAP queries with filtering and aggregation by times.
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.