For organizations utilizing 1C:Enterprise, the importance of database stability and speed cannot be overstated; these factors are essential for operational success. At Postgres Professional, our commitment to enhancing the DBMS core is unwavering. We are dedicated to removing architectural bottlenecks that often arise under the substantial loads characteristic of 1C deployments.
In line with this commitment, we are pleased to announce the release of Postgres Pro Enterprise 17.6, which introduces a series of enhancements tailored specifically for 1C users.
Documentation for configuring Postgres Pro with 1C
One of the most anticipated features has been the official documentation for configuring Postgres Pro with 1C solutions. Previously, administrators faced the challenge of assembling the correct DBMS configuration from various online sources. Now, we have published a comprehensive documentation section titled Configuring Postgres Pro for 1C solutions. This resource consolidates best practices and addresses common inquiries regarding performance tuning and optimal cluster operation.
Fewer messages between server processes
Those familiar with high-load databases understand the frustration caused by excessive catalog cache invalidation messages. While we had previously optimized this mechanism for tables, our latest release takes it a step further. We now suppress these messages when creating or dropping indexes on temporary tables and during statistics gathering. Given that temporary objects are session-specific, broadcasting notifications to other processes is unnecessary. Additionally, we have replaced spinlocks in the shared message queue with more efficient synchronization primitives, alleviating a bottleneck that occurs when processes compete for resources.
No more lock overhead on temporary table indexes
Even brief locks on temporary tables can consume significant CPU cycles. To address this, we have introduced the skip_temp_rel_lock parameter, which bypasses lock acquisition not only for temporary relations but also for their indexes. This enhancement builds upon functionality introduced in version 17.4, resulting in tangible performance improvements during frequent DDL operations.
Smarter cleanup of temporary tables
In previous versions, TRUNCATE operations were less efficient than desired, as the system had to scan the entire local buffer pool for each layer of a relation. We have reengineered this algorithm to collect information about buffers to be deleted in advance, allowing for a single scan of the pool across all layers simultaneously. This enhancement accelerates non-transactional truncation of temporary tables while reducing overhead.
Faster search in 1C dynamic lists
1C applications frequently rely on substring searches. In scenarios involving LIKE operators on mvarchar fields (which ensure MS SQL compatibility), the planner previously struggled with selectivity, often resulting in suboptimal plans. Our latest updates rectify this issue, enabling accurate cost estimation that allows the optimizer to favor index access over sequential scans more frequently.
Optimizing queries with ANTI JOIN
Complex queries utilizing NOT EXISTS or NOT IN constructs, which translate to ANTI JOIN, have historically suffered from inefficiencies due to identical scans for the same parameters. We have introduced memoization support for parameterized ANTI JOIN nodes within the optimizer. This enhancement allows the caching and reuse of computation results for specific parameters, thereby eliminating unnecessary data processing cycles.
Better locking during query planning
When executing queries involving multiple relations, contention for locks on the pg_attribute and pg_statistic system tables was a significant issue. Our optimizations have reduced the number of lock requests required when information is not cached, resulting in a smoother operation, particularly when numerous new sessions (with empty caches) connect and attempt to execute large queries on tables with extensive indexes.
Optimized VACUUM ANALYZE on catalog tables
Previously, analyzing system catalog tables would invalidate the pg_internal.init file, a vital cache of system catalog information. This invalidation process consumed resources and could hinder user connections. We have refined the algorithm so that pg_internal.init remains intact during catalog analysis, even with frequent statistics updates. This change ensures that new sessions can connect swiftly without delays, regardless of ongoing background maintenance activities.
These enhancements stem from an in-depth analysis of Postgres Pro’s performance under the demanding loads typical of large enterprise systems. Our ongoing efforts are focused on refining the product to tackle real-world performance and scalability challenges. For further details on these features and others that optimize the operation of 1C:Enterprise, please visit the Postgres Pro Enterprise for 1C page.
