Lock Contention & Bloat Analysis
This section of the AWR report covers two important operational health dimensions: lock contention between sessions, and bloat accumulation in tables and indexes.
Lock Contention Analysis
{screenshot: awr-lock-contention-section}
What Is Lock Contention?
Lock contention occurs when one session holds a lock that another session needs. The waiting session is blocked until the lock is released. Under high concurrency, lock contention can cascade into serious performance degradation.
Data Source
Lock contention data is captured from pg_locks samples recorded at each AWR snapshot interval. The report shows the most frequent blocking patterns observed between the two selected snapshots.
Lock Contention Table
| Column | Description |
|---|---|
| Blocking Query | The query holding the lock |
| Blocked Query | The query waiting for the lock |
| Lock Type | Type of lock requested |
| Relation | Table or index involved |
| Occurrences | Number of times this blocking pattern was observed |
| Max Wait (s) | Longest observed wait duration |
{screenshot: awr-lock-contention-table}
Common Lock Types in PostgreSQL
| Lock Mode | Triggered By |
|---|---|
AccessShareLock | SELECT statements |
RowShareLock | SELECT FOR UPDATE / SHARE |
RowExclusiveLock | INSERT, UPDATE, DELETE |
ShareUpdateExclusiveLock | VACUUM, CREATE INDEX CONCURRENTLY |
AccessExclusiveLock | ALTER TABLE, DROP TABLE, TRUNCATE |
Interpreting Lock Contention Results
Frequent transactionid locks → Long-running transactions blocking others. Review application transaction boundaries and ensure transactions are kept as short as possible.
AccessExclusiveLock contention → DDL operations (ALTER TABLE, VACUUM FULL) blocking normal DML. Schedule maintenance operations during off-peak windows.
Row-level lock contention on the same relation → Hot-row contention in high-concurrency tables. Consider application-level redesign or optimistic locking patterns.
Bloat Analysis
{screenshot: awr-bloat-section}
What Is Bloat?
PostgreSQL uses an MVCC (Multi-Version Concurrency Control) architecture. When rows are updated or deleted, the old versions are not immediately removed — they become dead tuples that accumulate as bloat in tables and indexes until VACUUM reclaims them.
Excessive bloat:
- Wastes disk space
- Slows down sequential and index scans
- Increases I/O load
Bloat Estimates Table
The AWR report provides bloat estimates for the most bloated tables and indexes in the monitored databases:
Table Bloat:
| Column | Description |
|---|---|
| Table | Schema and table name |
| Live Rows | Estimated number of live rows |
| Dead Rows | Estimated dead tuple count |
| Table Size | Current physical size |
| Bloat Size | Estimated reclaimable space |
| Bloat % | Percentage of table that is bloat |
Index Bloat:
| Column | Description |
|---|---|
| Index | Index name and associated table |
| Index Size | Current physical size |
| Bloat Size | Estimated reclaimable space |
| Bloat % | Percentage of index that is bloat |
{screenshot: awr-bloat-table}
Bloat Thresholds
| Bloat % | Action |
|---|---|
| < 10% | Normal — no action needed |
| 10–30% | Monitor — increase autovacuum aggressiveness |
| > 30% | Investigate — consider manual VACUUM or VACUUM FULL during a maintenance window |
:::warning VACUUM FULL
VACUUM FULL reclaims all bloat but takes an AccessExclusiveLock on the table, blocking all access during the operation. Use it only during scheduled maintenance windows. For online bloat reduction, consider pg_repack.
:::