Database Technology

This follow-up article details how to use Amazon CloudWatch Database Insights to identify blocking sessions causing row lock contention. It outlines resolution strategies including query termination, timeout adjustments, and architectural patterns like SKIP LOCKED and row splitting to restore throughput.

• Use CloudWatch Database Insights Lock Tree to pinpoint specific blocking sessions.
• Resolve immediate contention via query termination or timeout parameter tuning.
• Implement SKIP LOCKED or row splitting to prevent lock contention at the architectural level.

On PostgreSQL 17, a prepared statement can execute quickly for five runs before the query planner changes its strategy, causing a significant performance drop on the sixth execution. This behavior is invisible to single-run diagnostics like a standard EXPLAIN ANALYZE, requiring repeated executions to detect the plan shift. The issue stems from the internal plan cache mechanism and affects various drivers, not just psql.

• Single EXPLAIN ANALYZE runs mask plan instability that emerges after repeated executions.
• Prepared statements may trigger a plan cache update that degrades performance on run six.
• Use multiple repetitions in benchmarks to detect intermittent plan changes.
• This behavior is specific to PostgreSQL 17 and the plan cache mechanism.
• A one-line fix exists to mitigate the issue in affected driver configurations.

An InfoQ article demonstrates how PostgreSQL can function as the sole durable state store for workflows, removing the dependency on external orchestration tools. The approach leverages SKIP LOCKED for concurrent processing, primary-key checkpoints for idempotency, and leases for crash recovery. It also shows how to persist long-running states like sleeps and human approvals directly in the database.

• SKIP LOCKED enables safe concurrent work processing without complex locking logic.
• Primary-key checkpoints enforce idempotency, preventing duplicate execution on restart.
• Leases provide crash recovery mechanisms to handle node failures gracefully.
• Workflow pauses and human approvals can be stored as database rows for durability.

PostgreSQL 19's SQL/PGQ feature is facing criticism due to unresolved bugs that could hinder production readiness. The project team acknowledges these issues, suggesting the current state fails a strict shipping viability test. Meanwhile, the introduction of concurrent REPACK offers a practical alternative for maintenance, potentially reducing the need for disruptive midnight operations for database administrators.

• SQL/PGQ in PG19 has unresolved bugs; expect caution before adopting for graph workloads.
• Concurrent REPACK is available, allowing index rebuilds without long-term locking.
• DBAs can reduce overnight maintenance windows using the new REPACK capabilities.
• Monitor upstream for bug fixes on SQL/PGQ before planning graph-based migrations.

AWS Database Blog Part 1 explains how PostgreSQL row locking mechanics can degrade throughput during traffic spikes despite healthy CPU and I/O metrics. The guide details methods to monitor lock contention using system views, the pgrowlocks extension, and the log_lock_waits parameter. This foundational knowledge supports troubleshooting efforts in both Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL environments.

• Row lock contention can bottleneck throughput even when CPU and I/O remain healthy.
• Use system views and pgrowlocks extension to identify specific locked rows.
• Enable log_lock_waits parameter to capture detailed lock wait events.
• Techniques apply to both Aurora PostgreSQL and standard RDS for PostgreSQL.

Fleet impact: For Aurora PostgreSQL and RDS for PostgreSQL fleets, this blog provides the diagnostic baseline needed to distinguish between resource saturation and locking issues during peak loads. Ensure log_lock_waits is tuned appropriately in your parameter groups to capture sufficient context for future contention analysis.