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.
Streaming replication.
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.
Support for bulk data insertion to the same table in multiple threads simultaneously bypassing the Postgres Pro Shardman buffer manager. (See Multi-Segment Insertion.)
Replication optimizations:
- Enhanced lock logic to reduce CPU consumption significantly in case of startup recovery on physical replicas used as a source for logical replication with many downstreams. Walsenders do not slow down WAL application as a more efficient synchronization primitive is used instead of spinlock.
- Lower CPU consumption on a standby instance with enabled cascading logical replication. Wake-ups of walsender processes are sent after applying a group of WAL records rather than each individual record in case of high stream of changes from the upstream instance.
Enabling the query planner to overestimate the expected number of rows in statements that contain a comparison to an unknown constant. This feature is useful for 1C. (See planner_upper_limit_estimation parameter description.)
Using an in-memory system catalog for temporary objects. This feature is useful for 1C. (See enable_temp_memory_catalog parameter description.)
The experimental possibility for the autovacuum daemon to process indexes of a table in a parallel mode. This feature is useful for 1C. (See parallel_autovacuum_workers and max_parallel_autovacuum_workers parameter descriptions.)
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. (See num_xloginsert_locks parameter description.)
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.)
The pg_temp_default tablespace created by default at cluster initialization to store temporary objects, such as temporary tables and indexes on such tables.
The reference partitioning strategy that allows dividing tables into partitions by specifying a foreign key that is used as a reference to the parent partitioned table and is defined by the PARTITION BY REFERENCE clause.
Bulk insertion into tables, which allows reducing the time spent searching for buffers in shared memory and generating fewer WAL records. (See append_optimized storage parameter description.)
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.
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.
In-core computation of query plan identifiers. (See compute_plan_id parameter description.)
Using a more efficient synchronization primitive instead of previously used spinlocks for a shared invalidation message queue, which allows avoiding a bottleneck. This may be useful for 1C.
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 67.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. For more information, see initdb, createdb, and CREATE DATABASE.

Postgres Pro Shardman 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.
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. Note that it is only managed with the shardmanctl utility.
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 (no certified edition)
Debian 12/13 (no certified edition)
Red OS 8 and 7.3 (no certified edition)
Alt 10/11 (no certified edition) and ALT SP Release 10
Astra Linux 1.7/1.8 and s390 (no certified edition)

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