Database Technology

AWS Database Blog outlines a method to run change data capture migrations from self-managed SQL Server using AWS DMS without requiring sysadmin privileges for the endpoint login. The approach relies on certificate-signed wrapper procedures and granular permissions to maintain full CDC functionality while satisfying separation-of-duties compliance. AWS Secrets Manager is integrated to handle credential management securely within this restricted access model.

• Eliminate sysadmin requirement for DMS endpoint logins on SQL Server sources.
• Use certificate-signed wrapper procedures to grant minimal necessary permissions.
• Preserve full CDC capabilities while meeting strict separation-of-duties policies.
• Integrate AWS Secrets Manager for secure credential handling in the migration workflow.

PGX Inc. has launched version 1.0 of pgx-bm25, an extension that integrates Okapi BM25 ranked full-text search directly into PostgreSQL as a native index access method. Compatible with PostgreSQL 17 and 18, it operates as bm25_native and leverages standard index scan mechanics to return results ordered by relevance without requiring a separate Sort node. The index resides within the database's own pages, ensuring it is automatically handled by WAL logging, crash recovery, physical replication, and VACUUM processes. This eliminates the need for external search engines or separate runtimes for basic full-text retrieval needs.

• Native index access method removes dependency on external search engines like Elasticsearch.
• Results are ordered by BM25 relevance score directly via the index, avoiding extra Sort nodes.
• Fully integrated with core PostgreSQL lifecycle: WAL, crash recovery, replication, and VACUUM.
• Supports PostgreSQL 17 and 18; install via CREATE EXTENSION bm25_native.
• Simplifies architecture by keeping search logic and storage within the database cluster.

The pg_plan_filter extension has reached version 1.0.0, enabling administrators to block queries that exceed configured planner cost thresholds before execution. It supports statement-level and transaction-level cost caps, with an option to restrict filtering to SELECT statements only. The module is compatible with PostgreSQL versions 14 through 18.

• Prevent expensive queries from hitting production by enforcing planner cost limits.
• Cap total transaction cost to avoid resource exhaustion from long-running batches.
• Filter can be scoped to SELECT statements only to protect write paths.
• Available for PostgreSQL 14 through 18 as a loadable module.

Percona's new DBTrail tool enables analytical reporting on MySQL data without executing queries against the primary database server. This approach prevents large scans from evicting hot pages from the buffer pool and stops long-running reads from delaying the purge thread. It also avoids performance degradation caused by temporary table creation during complex aggregations.

• Preserve buffer pool efficiency by isolating analytical workloads from OLTP traffic
• Prevent purge thread latency spikes caused by long-running read transactions
• Avoid temporary table overhead when performing large GROUP BY operations
• Use DBTrail to offload reporting without impacting application responsiveness