2. Postgres Pro Shardman Features

2. Postgres Pro Shardman Features
Prev	Up	Preface	Home	Next

2. Postgres Pro Shardman Features #

Postgres Pro Shardman provides the most actual PostgreSQL version with some additional patches applied and extensions added. It includes new features developed by Postgres Professional, third-party patches already accepted by the PostgreSQL community for the upcoming PostgreSQL versions, as well as a number of features inherited from Postgres Pro Enterprise. Postgres Pro Shardman users thus have early access to important features and fixes.

Postgres Pro Shardman is a solution for the distributed systems. It provides advantages of a single DB with the guaranteed consistency and a wider range of scaling possibilities. It also requires a higher level of expertise of the system's DBAs, along with the responsible approach to processing applications and queries, while serving as NoSQL alternative.

Strong ACID guarantees compared to other distributed databases, high availability, and transparent horizontal scaling provide a strong basis for the real-time OLTP for CRM/ERP, Massively Parallel Processing (MPP) framework, PaaS, SaaS, FaaS, and ODS/MDM. Postgres Pro Shardman will also be of a great use for the systems where it is impossible to physically split the DB, or if the the DB tables have sophisticated architecture.

For internal specifics refer to Distributed System Internals.

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 advantages, compatibility features to your applications:

Strong ACID guarantees compared to other distributed databases.
Compatibility with Postgres Pro Enterprise.
Several clusters support within a distributed cluster.
Transparent horizontal scaling without a need in adopting NoSQL-based DBMS.
Built-in support of replication with no single point of failure, with any node being able to become coordinator that requires no system shut down and prevents any data loss.
Capacity of up to 100 cluster nodes.
High availability with primary and stand-by modes, along with the synchronous solution for the failover scenarios.
Tools to support REPEATABLE READ isolation level in a distributed system, along with advisory locks.
Work with cluster as with a fully functional DBMS.
Backup and recovery tools that support full consistent and incremental backup with logs.
Point-in-time recovery (PITR) as a consistent syncpoint.
High-availability cluster creation with multiple primary nodes with special utilities.
Stored procedures.
Full text search.
Covering indexes.
B-tree, hash, GiST, GIN, SP-GiST, BRIN indexes.
Perl and Python procedural languages.
ANSI SQL standard.
Storing and processing of big amounts of data (1 PB and more).
Interfaces for C++, Ruby, C, ODBC, Perl, Python, Tcl, and Java.
EUC, UTF-8, and Mule character set.
Compressed file system.
Access data stored in external PostgreSQL servers with postgres_fdw.
In-built monitoring agent.
No limits for the number of records or indexes, with the maximum partitioned table partition size of 32 TB, maximum attribute size of 1 GB, and maximum number of attributes of 1600.
Detailed access management with different access levels and roles.

The following enhancements are also inherited from Postgres Pro Enterprise:

64-bit transaction IDs that are not subject to wraparound. (See Section 23.1.5.)
Page-level compression. (See Chapter 31.)
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 amcheck module.
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.
Support for bulk data insertion to the same table in multiple threads simultaneously bypassing the Postgres Pro Shardman buffer manager. (See Multi-Segment Insertion.)
The built-in high availability that is achieved by using the BiHA within the Postgres Pro Shardman 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.
Reducing the number of catalog locks to improve performance:
- 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.
- Avoiding invalidation of the pg_internal.init file when analyzing catalog tables, particularly when statistics for these tables change frequently. This may be useful for 1C.
- Reducing the number of locking requests for the pg_attribute and pg_statistic tables for queries with a large number of relations that are not present in the system cache. This may be useful for 1C.
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.
Enabling the background writer to create WAL segments in advance during its main operation cycle for reducing the load on backend processes. The max_wal_segments_prealloc configuration parameter sets the maximum number of such segments.
Restricting the maximum amount of memory that can be allocated to a backend to prevent OOM errors. (See the max_backend_memory configuration parameter.)
Allocating up to 1 GB of memory for processing XML data. (See the xml_parse_huge configuration parameter.)
Enabling acquisition of a single SHARE lock on the table during foreign key constraint checks. (See the share_lock_fk storage parameter.)
Transforming OR clauses into UNION ALL queries. (See the enable_orunion_transformation configuration parameter.)

The following enhancements are also 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 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.
Changing the restore_command parameter 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.
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.13 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 66.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 Shardman also includes the following additional modules and applications:

aqo extension for adaptive query optimization.
Built-in High Availability (BiHA) extension, which is managed with the shardmanctl and shardmand, that turns Postgres Pro Shardman into a cluster with physical replication and built-in failover, high availability, and node failure recovery.
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 Shardman 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 Shardman.
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.

To provide the advanced functionalities and features, Postgres Pro Shardman imposes more stringent requirements on operating systems supported. Postgres Pro Shardman runs on the following operating systems:

Ubuntu 22.04/24.04
Debian 12
Red OS 7.3/8
ALT 10/11 and ALT SP Release 10
Astra Linux 1.7/1.8

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.

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