Database Technology

This third installment in a series examines why performance builds optimized via sysbench often underperform when Profile Guided Optimization (PGO) is applied. The author identifies three compounding reasons for this regression, focusing on uncovered code paths that PGO cannot effectively profile. The post details the correct methodology for implementing PGO to avoid these pitfalls and achieve genuine performance gains.

• Sysbench-only profiles miss critical code paths, leading to suboptimal PGO decisions.
• Three specific factors cause performance regressions in PGO-optimized builds.
• Proper workload diversity is required to generate accurate profile data.
• Correct PGO implementation requires more than just standard benchmarking.

PostgreSQL 17 introduces a check that raises 'infinite recursion detected in policy' when row-level security policies trigger themselves. The analysis breaks down the specific conditions causing this recursion and evaluates the performance cost of various fixes on a million-row dataset. Benchmarks show fix costs ranging from 0.09 ms to 6.6 seconds, highlighting significant trade-offs between correctness and query speed.

• PostgreSQL 17 detects and blocks recursive RLS policy execution to prevent stack exhaustion.
• Fix performance varies widely: 0.09 ms for optimal cases up to 6.6 seconds for complex logic.
• Review RLS policies for self-referencing conditions that trigger infinite loops.
• Optimize policy expressions to minimize evaluation overhead on large tables.

Chris van Eijk investigates the specific triggers for 'new row violates row-level security policy' errors in PostgreSQL 17. The analysis measures failure modes across plain inserts, upserts, MERGE statements, and queries using RETURNING clauses. It details four distinct variations of the error message to help pinpoint the exact context of the policy breach.

• RLS checks apply during INSERT, UPDATE, and MERGE operations in PG 17
• RETURNING clauses can trigger violations if the filtered row is inaccessible
• UPsERTs and MERGE statements have distinct error signatures to debug
• Four message forms exist; matching the exact text aids rapid triage
• Policy logic must account for all write paths including upserts

Hollis Varden benchmarks five common SQL keywords that trigger unnecessary work in PostgreSQL 18.6, revealing significant performance penalties for implicit operations. Using a 2 million row table on default settings, the test highlights UNION as the most costly, taking 13.6 seconds compared to 0.68 seconds for UNION ALL. The analysis measures execution time via EXPLAIN ANALYZE to isolate the overhead added by each keyword.

• UNION adds ~13 seconds of overhead vs UNION ALL on 2M rows due to implicit deduplication and sorting
• Always prefer UNION ALL unless distinct results are strictly required to avoid unnecessary work
• Default settings (4MB work_mem) can exacerbate performance gaps for queries requiring disk-based sorting
• Explain ANALYZE TIMING OFF provides consistent median metrics for warm-cache benchmarks
• Five specific SQL keywords were identified as hidden sources of extra computational load

AWS Database Blog outlines a method to run Change Data Capture migrations from self-managed SQL Server instances without requiring sysadmin privileges for the DMS endpoint. The approach leverages certificate-signed wrapper procedures and granular permissions to maintain full CDC functionality while adhering to separation-of-duties policies. AWS Secrets Manager is integrated to handle credential management securely within this restricted access model.

• Eliminate sysadmin requirement for DMS SQL Server endpoints using certificate-signed wrappers.
• Granular permissions enable full CDC capture while meeting strict separation-of-duties needs.
• Integrate AWS Secrets Manager for secure credential handling in least-privilege workflows.

PGX Inc. has released version 1.0 of pgx-bm25, introducing a native index access method for Okapi BM25 ranked full-text search in PostgreSQL. The extension, installed as bm25_native, allows users to create indexes that support relevance scoring and filtering without external dependencies. Queries leverage ordered index scans, eliminating the need for separate sort nodes, while the index is fully managed by PostgreSQL through WAL logging, crash recovery, and VACUUM.

• Native index access method removes need for external search engines like Elasticsearch.
• Supports PostgreSQL 17 and 18 with standard VACUUM and WAL integration.
• Uses @@@ for matching and &@@ for relevance ordering in a single scan.
• No separate runtime or external dependencies required for operation.
• Index pages are stored within the index relation for physical replication.

Production issues with MySQL Galera Cluster rarely involve write-set replication errors. Instead, operators frequently encounter silent stalls where queries pile up without log errors, leading to extended downtime until manual intervention. The primary culprit is typically flow control mechanisms rather than the replication library itself.

• Galera stability issues usually relate to flow control, not replication correctness.
• Silent query piling is a key symptom of flow control saturation.
• Manual node termination is often the only way to recover from these stalls.
• Monitor flow control metrics to detect saturation before downtime occurs.