What's New in Postgres Pro Enterprise 17: From Proxima to Intelligent Data Management

Proxima: All-in-One Solution for Scalability, High Availability, and Performance

Forget about poolers, proxies, and load balancers. Proxima is a new technology that combines all these functions and is built into the Postgres Pro Enterprise DBMS core, thus enhancing efficiency and reducing latency. This enhances efficiency and reduces latency. What does it offer?

• Connection pooling. Proxima creates several connections that can be used for numerous client sessions. This allows establishing connections with minimal delays, skipping the time-consuming process of creating connections and memory allocation. Proxima also reduces the load on the primary for traffic encryption and user authorization. Client connections use the ready-to-go connections from the pool, which ensures that resources are used efficiently.
• Query proxying. Proxima automatically detects the designated leader node in a BiHA cluster and transparently redirects client queries to it, even if the client has initially connected to a replica. This eliminates the need for applications to manually track changes in cluster topology and ensures that write requests always reach the correct node.
• Load balancing (under development). Proxima monitors loads on replicas and dynamically distributes incoming connections among the replicas. This prevents overloading specific replicas and maximizes overall system performance.

Efficient Queue Management Directly in the Database

For asynchronous message processing, Postgres Pro Enterprise introduces pgpro_queue, an extension that allows you to manage message queues directly within the database, eliminating the need for external services. This enables complex business logic to be implemented at the database level.

• Transactionality. A message will either be successfully processed or returned to the queue for a retry. If there is a failure in the message handler, the corresponding transaction is rolled back and a visibility delay is set to prevent looping.
• Prioritization, filtering, and exclusions. Messages can be prioritized, filtered based on attributes, or excluded if they remain unprocessed for too long.
• Message storage model: Messages are stored in the queue until they are read. All messages and their statuses are preserved across Postgres Pro server restarts or failovers, ensuring reliability and high availability.

New Tools for Query Plan Management

Postgres Pro 17 Enterprise introduces enhanced query plan management tools, making performance optimization easier.

• pgpro_multiplan. This extension replaces the legacy sr_plan, allowing administrators to store an unlimited number of approved execution plans for each query. The system selects the optimal plan based on query parameters. Plans can also be transferred between servers for improved stability. The extension introduces a new type of hard-wired execution plan — hintset. It consists of set of hints formed based on the frozen execution plan, including optimizer environment variables, join types, join order, and data access methods, similar to those supported by the pg_hint_plan extension. Unlike previous plan types, hintsets are tied not to object IDs (OIDs) but to object names. This ensures stability even when tables are recreated or fields are added.
• Template plans. Starting from version 17.2.2, a single plan can be assigned to a group of queries that differ only in table names, by utilizing wildcards. This simplifies plan management for dynamic table names, being particularly beneficial for 1C users (temp_tab_*).
• Query registration assistant. The process of selecting and storing queries is now automated for plan management. All executed queries, along with their plans, are saved in a dedicated table, allowing administrators to easily select and "freeze" plans without interacting with live queries. This simplifies plan capturing by storing query IDs along with their plans.

Adaptive Execution and Query Optimization

Postgres Pro Enterprise 17 introduces features that dynamically adjust to workload conditions, accelerating complex query execution:

• AQE (Adaptive Query Execution). The system automatically detects long-running queries and dynamically reschedules and re-executes them in real time. AQE can restart queries if they exceed execution time, encounter planner errors, or trigger memory overflow. Triggers now include time, memory, and cardinality mismatches. Extended protocol/prepared statements are supported.
• AQO Enterprise. Available from version 17.2.2, this module continuously learns from all executed queries, automatically correcting planner errors without manual intervention. It leverages query execution history and the Delta Learning cardinality estimation method, syncing the knowledge base across replicas via WAL.

Security and Access Control Enhancements

Security features have been significantly improved in Postgres Pro Enterprise 17. The pgpro_usage extension collects database object and function usage statistics per user, generating reports on actual privilege usage and identifying unnecessary access rights. The report lists database objects, users, their privileges, and the roles granting those privileges. 

An example of a pgpro_usage report

Security specialists can see which permissions are actively used and remove unnecessary ones to enhance security. Data collection is handled by the pg_stat_all_tables_per_user and pg_stat_all_functions_per_user system views.

Intelligent Data Storage with pgpro_ilm

The pgpro_ilm extension automates storage optimization by offloading rarely accessed data to lower-cost disks and compressing it when needed. This allows for space savings and efficient resource utilization by moving tables or their parts to other storages with different properties.

A possible implementation scheme for intelligent data storage

• Heatmap. Tracks access time (reads and modifications) for each table and partition, visualized as bitmaps. Based on this information, a heatmap is generated, which pgpro_ilm uses to track "stale" data.
• Policies. You can declaratively define rules for data movement and compression based on the last access or modification time. The “no access” and “no modification” policies are supported. Policies can be executed automatically via a scheduler.
• Compression. Rarely modified data is moved to a compressed tablespace automatically via CFS.
  
  

Infrastructure and Usability Enhancements

Postgres Pro Enterprise 17 introduces new features that simplify configuration, monitoring, and cluster management, giving administrators more flexibility in optimizing infrastructure.

• BiHA improvements. Supports user-defined SQL functions for adding/removing nodes and leader availability checks. A new biha_callbacks_user role has been introduced. In two-node configurations, a lightweight BiHA referee can be deployed on a third node without a user database. Remote replicas in external data centers can now be promoted to leaders.

• multimaster. Speeds up transaction application on lagging nodes in catchup mode, reducing replication lag.
• pgpro_pwr. Now tracks extension versions and allows data masking for specific databases in reports. Updated to version 4.8.
• Improved management of partitioned tables. The ALTER TABLE command now includes two new subcommands: SPLIT PARTITION, which divides a partition into multiple ones, and MERGE PARTITIONS, which combines multiple partitions into one. Additionally, the pgpro_autopart extension dynamically creates partitions when adding or modifying a table's partitioning key, eliminating the need to manually create new partitions. The extension also enables automatic partition creation when using interval-based partitioning.
• PPEM 2.0: Features a redesigned architecture and Golang-based platform, improved UI, query plan visualization, cluster status display, lock tree, session wait profiles, Point-In-Time Recovery, schema object management, and automatic instance tuning for 1C/OLTP.

The innovations in Postgres Pro 17 cover a broad range of improvements, from enhanced performance and scalability to strengthened security and simplified administration. Tools like proxima, pgpro_queue, pgpro_multiplan, AQE, and pgpro_ilm empower developers and database administrators to build more efficient and reliable applications.