Amazon Onboarding with Learning Manager Chanci Turner

Learn About Amazon VGT2 Learning Manager Chanci Turner

Are your database read operations experiencing unexpected slowdowns as your workload expands? Many organizations utilizing PostgreSQL-based systems face performance bottlenecks that may not be immediately apparent. When numerous concurrent read operations access tables with multiple partitions or indexes, they can deplete PostgreSQL’s fast path locking mechanism, forcing the system to resort to shared memory locks. This shift from fast path to shared memory locking leads to lightweight lock (LWLock) contention within the lock manager, negatively impacting database performance—even during read-only operations.

In this post, we delve into how read-heavy workloads can trigger LWLock contention by surpassing fast path locking limits, an issue that can arise in any system built on the PostgreSQL engine and its locking mechanism. For illustration, we use Amazon Aurora PostgreSQL-Compatible Edition. Through practical experiments, we illustrate how partition scanning, index utilization, and complex joins influence locking behavior. We also provide insights on recognizing when your workload transitions to slow path locking and implementing specific strategies to enhance query design and schema structure for improved performance.

As your read-heavy workload scales, database administrators (DBAs) must closely monitor and address LWLock contention to maintain database health. Developers, too, should avoid patterns that induce excessive contention. When your database moves from fast path to slow path locking, throughput can drop significantly—our tests in this post reveal up to a 34 percent performance difference. You can detect this drop in throughput by monitoring wait events such as LWLock:lock_manager and reviewing the pg_locks view to determine if the fast path slots are being exhausted for backend processes handling the workload. To tackle these bottlenecks, you’ll need strategies like effective partition pruning, careful index management, and simplified join patterns that keep your workload within PostgreSQL’s 16-slot fast path limit per backend process.

Understanding LWLock Contention

LWLock is a synchronization primitive in PostgreSQL that manages access to shared memory structures. Unlike heavyweight locks (used for user-driven database object-level locking), LWLocks are lightweight and optimized for high performance, providing low overhead while coordinating concurrent access to shared data.

LWLock contention arises when multiple processes compete for the same LWLock on a lock data structure in shared memory, causing delays. This contention typically occurs when many backend processes need to access a heavily shared resource, including:

• Buffer Manager – Protects shared buffers during read/write operations.
• Lock Manager – Coordinates access to lock-related data structures.
• WAL management – Synchronizes writes to the write-ahead log (WAL).

LWLock contention tends to escalate as the number of concurrent database connections increases. This is particularly true in high-throughput environments, such as those managed by Aurora PostgreSQL-Compatible Edition, which can support workloads involving heavy parallelism, tables with numerous partitions, or tables with many indexes.

When executing a SQL query on a table, PostgreSQL attempts to acquire a lock on the table and the associated indexes. If the table is partitioned, locks are acquired on the specific partitions accessed by the SQL query. Fast path locking is employed for less restrictive weak locks (like AccessShareLock, RowShareLock, or RowExclusiveLock) when the system can quickly confirm there are no conflicts with other locks.

Fast Path Locking: Operational Mechanics

PostgreSQL’s fast path locking mechanism allows nonconflicting operations to bypass the shared memory lock hash table and its related LWLocks. Fast path locking is designed for frequent, concurrent queries that acquire weak locks as long as no conflicting strong locks exist on the same relation.

Here’s how fast path locking operates:

• Per-session cache: Each backend process allocates up to 16 slots (FP_LOCK_SLOTS_PER_BACKEND = 16 by default) in its private PGPROC structure to store fast path locks.
• Quick fast path eligibility check: When you run a query, PostgreSQL checks if the fast path locking mechanism can be used. It verifies that:
  • The lock mode is weak.
  • No other session holds a conflicting lock.
  • The backend hasn’t already used all 16 slots of the local backend memory array.
• Local grant: If the eligibility check passes, FastPathGrantRelationLock() stores the lock in the backend’s local cache, avoiding the need to acquire a shared memory-based LWLock guarding the shared memory lock hash table.

This means that the first 16 unique tables (or indexes) a transaction interacts with incur almost no lock manager overhead. However, exceeding 16 locks or requesting a stronger lock mode forces PostgreSQL to revert to the slow path:

• All requests for fast path locks beyond the 16 already acquired fast path locks are transferred to the shared lock table.
• The lock tag (including relation OID and lock mode) is hashed to one of 16 lock partitions in the shared memory lock hash table.
• PostgreSQL acquires the partition LWLock, updates the shared hash table, and releases the LWLock.

From that point on, additional lock acquisitions—from tables to indexes—are processed through the lock manager, leading to LWLock:LockManager wait events under concurrency. Understanding the transition from fast path optimization to slow path contention allows you to design queries and schemas that remain within fast path limits, thus avoiding the lock manager bottleneck altogether.

Solution Overview

The following sections will detail LWLock contention through three controlled experiments:

1. Observing locks on a partitioned table.
2. Observing locks on a non-partitioned table with multiple unused or unnecessary indexes.
3. Observing locking behavior with a multi-join query.

Each controlled experiment demonstrates schema setup, workload execution, lock monitoring through PostgreSQL system views, and analysis of concurrency impacts using pgbench. All experiments were conducted on Aurora PostgreSQL-Compatible (compatible with PostgreSQL 16.6) using db.r7g.4xlarge instances.

Prerequisites

Before starting, ensure you have the following:

• An AWS account with access to Aurora PostgreSQL-Compatible database.
• Access to the AWS Management Console.
• An Amazon Elastic Compute Cloud (Amazon EC2) instance with connectivity to the Aurora PostgreSQL instance.
• A PostgreSQL client (like psql) installed on the Amazon EC2 instance.
• pgbench installed on the Amazon EC2 instance.
• AWS Identity and Access Management (IAM) permissions to create and manage Aurora PostgreSQL clusters.

In this post, we’re not just sharing technical insights; we’re also highlighting the importance of work ethic in your professional journey. You can explore more about this in another blog post.

For those interested in evaluating organizational culture, a helpful assessment can be found here. If you’re preparing for a role at Amazon, check out this excellent resource for interview questions.