Postgres Pro Shardman

Main article: Database Management System (DMS)

2024

Postgres Pro Shardman 14.12.2

Postgres Professional has released an updated version of the Postgres Pro Shardman 14.12.2 DBMS. The developer announced this on August 8, 2024.

Postgres Pro Shardman is distributed relational DBMSs for high-load systems, suitable for the largest installations up to a hundred TB and above, when a single server is not enough. DBMS allows you to divide data into parts and distribute it to different servers - shards. At the same time, the solution provides built-in fault tolerance and guaranteed compliance with ACID requirements: atomicity, consistency, isolation and stability.

The current release adds some features, improves performance, and fixes some bugs. Among the most important changes:

Added the ability to create a global or segmented table based on another global, segmented, or local. As of August 2024, there are restrictions on creating tables based on local ones.

Fixed IF NOT EXISTS processing of CREATE TABLE command for global and partitioned tables. Previously, there was a possibility of creating a table with an incorrect structure if a partitioned table with an identical name already existed on one of the cluster nodes.

Updated the default value for the num_parts storage parameter by 24 to achieve a more uniform distribution of data for clusters with 2, 3, 4, 6, and 8 nodes. A enable_merge_append configuration option has been added that enables or disables the use of MergeAppend plans by the scheduler. In particular, it allows you to disable the use of these plans if they are too expensive.

A pgpro_stats.track_shardman_connections configuration parameter has been added that enables or disables the processing of Shardman statements.

Added the ability to make connections to the VALUES expression on the remote server. The 64 thousand limit on the number of tables in the query has been removed.

Added a shardman.pg_stat_monitor view that provides statistics on the distributed deadlock detector and prepared transaction resolution services.

Added a global shardman.gv_stats_sdm_statements view that provides access to aggregated statistics for distributed queries.

Updated view of pgpro_stats pgpro_stats_sdm_statements. Now it contains statistics only for queries that use segmented tables.

Updated the supported version of the pg_probackup utility to 2.8.2. Management utilities

Added the ability to back up a cluster with table spaces that are now in the backup directory.

Added the ability to restore a fully or partially working cluster from a backup made on a partially working cluster using shardmanctl probackup.

The --no-wait parameter was introduced for the shardmanctl shard add command, with which the shardmanctl command does not wait for the segment to start and unlocks other commands. The s|--scale parameter for the shardmanctl bench run command was entered. Specifies the scaling factor by which the number of rows generated is multiplied. The shardmanctl script command has been added, which executes non-transaction commands from a file or command line on the specified segments.

The sdmspec.json configuration file generated by the shardmanctl config generate command has been changed. The file excludes parameters that depend on system resources and workload on the cluster node. These settings retain the default values. Previously, cluster initialization may have failed on nodes with smaller resources due to high values of these parameters.

Added the ability to restore others from a backup of one cluster if they have the same topology. The command shardmanctl config update ip has been added, which updates the IP addresses of the specified cluster nodes.

The --log-format parameter has been added to shardmand, which specifies the format of the log: json or text.

Postgres Pro Shardman Minor Update Release Version 14.12.1

Postgres Professional on June 20, 2024 announced the release of minor updates to Postgres Pro Shardman version 14.12.1.

𝓲

postgrespro.ru

Postgres Pro Shardman is distributed relational DBMSs for high-load systems, suitable for the largest installations in tens and hundreds of TB, when a single server is not enough. DBMS allows you to divide data into parts and distribute it to different servers - shards. At the same time, the solution provides built-in fault tolerance and guaranteed compliance with ACID requirements: atomicity, consistency, isolation and stability.

In the next release, Postgres Pro Shardman has added various features, improved performance and fixed some bugs. Among the key changes are:

Core and Extensions

• The REMOTE parameter of the EXPLAIN command has been added, which allows the EXPLAIN output for requests made on the remote server. By default, enabled.
• Own logic for estimating the cost of plans is implemented. It allows the scheduler to more often choose common plans with the similarity of common and specialized.
• Added support for partitioning during execution for plan nodes that aggregate data on third-party servers. First of all, optimization is necessary to eliminate sections in general plans.
• Added cumulative metrics on the amount of network traffic between Shardman cluster nodes in the view shardman.pg_stat_netusage.
• Updated the pg_stat_activity view, which now shows the status of Shardman service processes.

Management utilities

• Fixed CVE-2023-45288 and CVE-2023-44487 vulnerabilities.
• Fixed an error in the shardmanctl cleanup command that caused replication groups to not be deleted.
• Improved output of the shardmanctl forall command when the result is empty.
• Fixed a previous shardmand failure if the Shardman cluster is not fully configured.

Release of Shardman

Postgres Professional March 4, 2024 announced the release of Shardman - distributed DBMS for high-load systems. The DBMS was developed specifically at the request of customers and is suitable for the largest installations in tens and hundreds of TB, when a single one servers is not enough. Shardman is already listed in, certified and in the Register of Russian Software FSTEC final stages in testings large companies oil and gas financial and the sector.

𝓲

Postgres Professional

From an architectural point of view, Shardman is a distributed DBMS consisting of several servers, where each shard stores part of the data. At the same time, some servers are used to replicate the data of each shard, which ensures fault tolerance. For communication between charms, a multiplexer is used - a special "transport," which optimizes work with the network and the load on the DBMS when performing distributed requests. Shardman is distinguished by three key features:

• Scale horizontally. When the DBMS server ceases to cope with loads, the data stored on it can be evenly distributed among other servers - shards. The system can continue to scale transparently, adding new server power to it.
• Built-in fault tolerance. Shardman automatically replicates the data of each shard to backup servers so that in the event of a failure of any of the nodes, it is transparent for users to redirect requests to another server.
• Strict guarantees of data isolation and consistency. Shardman extends the PostgreSQL data consistency model for distributed systems and ensures transparent transaction operation for database clients.

In the information systems of large companies or banks, we approach high transaction loads approaching the ceiling of the capabilities of traditional DBMS. In such cases, horizontal scalability cannot be dispensed with. In response to such customer needs, we have released Shardman - a DBMS that will be able to provide horizontal scaling of OLTP databases in strict compliance with ACID requirements (atomicity, consistency, isolation and reliability), - said Ivan Panchenko, Deputy General Director, co-founder of Postgres Professional.

According to the developer, Shardman is suitable for cases when:

• There are so many users that one server cannot cope with so many connections;
• The write load is so large (OLTP system) that one server cannot cope with it;
• There is so much data that it does not fit on one server: neither in memory nor on disk;
• Large data processing required in parallel (OLAP system);
• The application has complex logic on the DBMS side, as a result of which a lot of processor time is consumed.

Additional features: transparent data management, application acceleration, incremental backup, and data compression Shardman is based on PostgreSQL 14, but is equipped with additional features for large installations. Among the main advantages of DBMS:

• Transparent data handling. The client, connecting to any node of the distributed system, gains access to all data stored in the system.
• An interconnect mechanism that cuts off redundant TCP sessions between charms, reduces network load, and reduces resource competition.
• Speed up applications that have complex logic on the DBMS side and increase the total TPS of the entire DBMS.
• Additional features of Postgres Pro Enterprise DBMS: CFS data compression mechanism, incremental page-level backup, PTRACK backup mechanism and pgpro_stats extension.