2. Difference between Postgres Pro Shardman 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 Shardman users thus have early access to important features and fixes.
Note
Postgres Pro Shardman is provided under the following license: https://postgrespro.com/products/postgrespro/eula. Make sure to review the license terms before downloading Postgres Pro Shardman.
Postgres Pro Shardman 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.)
- Fair lightweight lock scheduling after the specified number of shared locks is acquired. (See lwlock_shared_limit 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.
- 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.
- Verification of unique constraints in B-tree indexes in the
amcheckmodule. - 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.
- 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.
The following enhancements are inherited from Postgres Pro Standard:
- 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.
- Changing the
restore_commandparameter without restarting the server. - 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.
Postgres Pro Shardman also includes the following additional modules and applications:
- aqo extension for adaptive query optimization.
- 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.
- pg_probackup, a backup and recovery manager.
- 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_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.
- pg_query_state module that enables you to get the current state of query execution for a backend.
- pgvector extension that provides vector similarity search for Postgres Pro.
To provide the advanced functionalities and features, Postgres Pro imposes more stringent requirements on operating systems supported.
Important
Postgres Pro Shardman 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 Shardman releases follow PostgreSQL releases, though sometimes occur more frequently. The Postgres Pro Shardman versioning scheme is based on the PostgreSQL one and has an additional decimal place.