Proxima: All-in-One Solution for Scalability, High Availability, and Performance
In a significant advancement for database management, Proxima emerges as a comprehensive solution that integrates connection pooling, query proxying, and load balancing into the core of the Postgres Pro Enterprise DBMS. This innovative technology streamlines operations, enhancing efficiency while minimizing latency.
Proxima offers a range of features that redefine connection management:
- Connection pooling: By establishing multiple connections for various client sessions, Proxima allows for rapid connection establishment, bypassing the traditionally slow processes of connection creation and memory allocation. This not only optimizes resource usage but also alleviates the primary server’s burden related to traffic encryption and user authorization.
- Query proxying: In a BiHA cluster, Proxima intelligently identifies the leader node and seamlessly redirects client queries to it, even if the initial connection was made to a replica. This automation eliminates the need for applications to monitor cluster topology changes, ensuring that write requests consistently reach the appropriate node.
- Load balancing (under development): Proxima is designed to monitor the load on replicas and dynamically distribute incoming connections, preventing any single replica from becoming overloaded and thus optimizing overall system performance.
Efficient Queue Management Directly in the Database
Postgres Pro Enterprise also introduces pgpro_queue, an extension that facilitates asynchronous message processing directly within the database. This eliminates the reliance on external services, allowing for the implementation of complex business logic at the database level.
- Transactionality: Each message is either processed successfully or returned to the queue for a retry. In the event of a failure in the message handler, the transaction is rolled back, and a visibility delay is introduced to prevent looping.
- Prioritization, filtering, and exclusions: Messages can be prioritized, filtered based on specific attributes, or excluded if they remain unprocessed for extended periods.
- Message storage model: Messages are retained in the queue until read, with all statuses preserved across server restarts or failovers, ensuring reliability and high availability.
New Tools for Query Plan Management
The introduction of enhanced query plan management tools in Postgres Pro 17 Enterprise simplifies the process of performance optimization.
- pgpro_multiplan: This extension replaces the legacy sr_plan, enabling administrators to store an unlimited number of approved execution plans for each query. The system intelligently selects the optimal plan based on query parameters and allows for the transfer of plans between servers, enhancing stability. A new execution plan type, the hintset, is introduced, which is based on a frozen execution plan and includes various optimizer environment variables.
- Template plans: From version 17.2.2, a single plan can now be assigned to a group of queries that vary only in table names, utilizing wildcards for simplified management.
- Query registration assistant: This feature automates the selection and storage of queries for plan management, saving all executed queries and their plans in a dedicated table for easy access and management.
Adaptive Execution and Query Optimization
Postgres Pro Enterprise 17 also introduces features that adapt to workload conditions, enhancing the execution of complex queries:
- AQE (Adaptive Query Execution): This functionality automatically identifies long-running queries and dynamically reschedules and re-executes them in real time, addressing issues such as execution time overruns and planner errors.
- AQO Enterprise: Available from version 17.2.2, this module learns from executed queries, automatically correcting planner errors without manual intervention, leveraging query execution history for continuous improvement.
Security and Access Control Enhancements
Significant improvements have been made to security features in Postgres Pro Enterprise 17. The pgpro_usage extension now collects statistics on database object and function usage per user, generating reports that reveal actual privilege usage and helping to identify unnecessary access rights.
Security specialists can utilize these reports to enhance security by removing unused permissions, with data collection supported 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 relocating rarely accessed data to lower-cost disks and compressing it as needed, resulting in efficient resource utilization.
- Heatmap: This feature tracks access times for each table and partition, visualizing this data as bitmaps to identify “stale” data.
- Policies: Users can define rules for data movement and compression based on access or modification times, with support for automatic execution via a scheduler.
- Compression: Data that is infrequently modified is automatically moved to a compressed tablespace.
Infrastructure and Usability Enhancements
Postgres Pro Enterprise 17 introduces features that streamline configuration, monitoring, and cluster management, providing administrators with greater flexibility in optimizing infrastructure.
- BiHA improvements: This includes support for user-defined SQL functions for node management and leader availability checks, along with the introduction of a lightweight BiHA referee for two-node configurations.
- Multimaster: Enhancements speed up transaction applications on lagging nodes, effectively reducing replication lag.
- pgpro_pwr: This tool now tracks extension versions and enables data masking for specific databases in reports, updated to version 4.8.
- Improved management of partitioned tables allows for new commands such as SPLIT PARTITION and MERGE PARTITIONS, while the pgpro_autopart extension automates partition creation.
- PPEM 2.0: This version features a redesigned architecture and a Golang-based platform, enhancing UI, query plan visualization, and automatic instance tuning for 1C/OLTP.
The advancements in Postgres Pro 17 encompass a wide array of enhancements, from improved performance and scalability to fortified security and simplified administration. Tools such as Proxima, pgpro_queue, pgpro_multiplan, AQE, and pgpro_ilm empower developers and database administrators to create more efficient and reliable applications.
