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 31.)
- Support for autonomous transactions. (See Chapter 16.)
- K-nearest neighbors (k-NN) algorithm for B-tree, GiST, and SP-GiST index types. (See Section 11.13.)
- Reduced WAL size on
CREATE INDEXoperation for GiST, GIN, SP-GiST. - 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.
- Fair lightweight lock scheduling after the specified number of shared locks is acquired. (See lwlock_shared_limit parameter description.)
- 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.)
- Timeout for idle sessions on the server side. (See idle_session_timeout parameter description.)
- Support for timestamp output in pg_xlogdump.
- Periodic verification of client-server connection during query execution, which allows to detect broken connections earlier and free server resources upon disconnection. (See client_connection_check_interval parameter description.)
- An improved version of pgbench that handles transactions with serialization or deadlock errors, as well as supports compound commands.
- Restoration of corrupted WAL data from in-memory WAL buffers.
- Covering indexes. (See the
INCLUDEdescription in CREATE INDEX.) - Improved deadlock detection mechanism that does not cause performance degradation.
- Displaying planning time in the output of the auto_explain module.
- ICU collation support on all platforms to provide platform-independent sort for various locales. The
icucollation provider is used for all locales exceptCandPOSIX. - PTRACK implementation, which enables pg_probackup to track page changes on the fly when creating incremental backups.
- Consistent reads on standby servers. (See WAITLSN.)
- Improvements for command-line editing using WinEditLine in the Windows version of psql, including autocomplete support in psql console and changing the psql default encoding to UTF-8.
- SHA-2 algorithm for password encryption. (See Section 20.3.2.)
Postgres Pro Enterprise also includes the following additional modules:
- aqo extension for adaptive query optimization.
- 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.
- jsquery module 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. - pgbouncer connection pooler.
- pg_hint_plan module that controls the execution plan by providing hints to the planner.
- pg_pathman module that provides optimized partitioning mechanism and functions to manage partitions, as well as declarative syntax.
- pg_probackup, a backup and recovery manager.
- pgpro_scheduler module that provides background workers for task scheduling.
- 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_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.
- 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.
- sr_plan module that allows to save and restore query plans.
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.