🚨 PostgreSQL Performance Investigation Workflow

A practical, step-by-step 3-tier framework for diagnosing PostgreSQL performance bottlenecks—from live lock triage to OS log audits and pg_profile historical analysis.

• 🛑 Stop the Guesswork: Learn a structured, step-by-step decision tree to diagnose PostgreSQL bottlenecks under pressure without running random queries.
• ⚡ Master 3-Tier Triage: Move seamlessly from Live In-DB Lock Analysis $\rightarrow$ OS & Log Diagnostics $\rightarrow$ Historical pg_profile Deep Dives.
• 🔍 Uncover Hidden Bottlenecks: Trace blocking session trees, detect work_mem memory spills to disk, and spot optimizer blind spots in real time.
• 📊 Free Master Cheat Sheet: Download a ready-to-use Excel workbook packed with copy-paste SQL scripts, grep commands, and snapshot queries!

📌 From Slow Query to Root Cause Using PostgreSQL Monitoring Tools

📌 Extensions Used

• 📊 pg_stat_statements – Query execution statistics
• ⏳ pg_wait_sampling – Wait event sampling
• 🔗 dblink – Required by pg_profile
• 📚 pg_profile – Historical performance analysis

Disclaimer: This guide and the downloadable cheat sheet are provided for educational purposes only. Always validate commands in a test environment before running them on live systems. Use at your own risk.

1️⃣ Section 1: The Master Diagnostic Flowchart

 ┌─────────────────────────────────────────────────────────┐
 │        PERFORMANCE ISSUE DETECTED IN POSTGRES           │
 └────────────────────────────┬────────────────────────────┘
                              │
 ┌────────────────────────────▼────────────────────────────┐
 │ TIER 1: Live In-Database Triage                         │
 │ ├─► Check Lock Tree (Find blocking PIDs)                │
 │ ├─► Identify Long-Running Queries (>5m)                 │
 │ └─► Check Memory Cache Hit Ratio (<99%)                 │
 └────────────────────────────┬────────────────────────────┘
                              │
 ┌────────────────────────────▼────────────────────────────┐
 │ TIER 2: Operating System & Log Diagnostics              │
 │ ├─► Filter Critical Errors (ERROR, FATAL, PANIC)        │
 │ ├─► Scan for "temporary file:" (work_mem spill)         │
 │ └─► Check WAL Checkpoint Frequency                      │
 └────────────────────────────┬────────────────────────────┘
                              │
 ┌────────────────────────────▼────────────────────────────┐
 │ TIER 3: Historical Deep-Dive (pg_profile)               │
 │ ├─► Compare Snapshot Windows (Baseline vs Spike)        │
 │ ├─► Top Time-Consuming Queries                          │
 │ └─► Table & Index Write Churn Analysis                  │
 └─────────────────────────────────────────────────────────┘

2️⃣ Section 2: Walkthrough — Tier 1: Live In-Database Triage

(Refers to 01-performance-checklist in your Excel sheet)

When the database is actively choking or application connections are queuing up, do not panic and kill random queries. Follow this exact sequence:

1. Step 1: Unblock the Connection Cascade (check for blocking session)
   • Why: A single process holding an exclusive lock on a core table will cause every subsequent query touching that table to wait.
   • Action: Run the recursive lock tree query to find the Leader PID (depth 1).
   • Remediation: Attempt a graceful cancellation first (SELECT pg_cancel_backend(pid)). If the process is idle in transaction, forcefully terminate it (SELECT pg_terminate_backend(pid)).
2. Step 2: Inspect Active Lock Modes (check for locking session)
   • Why: Identify whether sessions are competing for row-level locks or full relation locks (AccessExclusiveLock).
3. Step 3: Scan for Runaway SQL (identify the long running queries)
   • Why: Catch un-indexed, heavy transactions that have been running longer than 5 minutes before they exhaust memory or CPU.
4. Step 4: Check Engine RAM Efficiency (The Cache Hit Ratio Query)
   • Why: If your shared buffer hit ratio drops below 99%, your database is forcing heavy disk I/O reads instead of serving data from RAM.
5. Step 5: Identify Optimizer Blindspots (Stale Statistics)
   • Why: Tables with a high volume of un-analyzed changes (n_mod_since_analyze) fool the PostgreSQL query planner into choosing bad execution paths (e.g., choosing a sequential scan over an index scan).

3️⃣ Section 3: Walkthrough — Tier 2: OS & Log File Audits

(Refers to 02-logfile-checks in your Excel sheet)

If live triage doesn’t immediately reveal an active lock contention, look at the engine logs on the server:

1. Locate Configuration & Log Paths: Confirm active setting files (pg_file_settings) and active log paths (pg_current_logfile()).
2. Filter Out User Syntax Noise: Use awk to filter for real database panics, fatal errors, and storage failures (ERROR:, FATAL:, PANIC:).
3. Scan for Memory Overshot (temporary file:): Search for temporary file creations. When PostgreSQL logs temporary file:, it means queries exceeded work_mem and spilled their sort or hash aggregate operations directly to disk storage.
4. Audit Checkpoint Frequency: Search logs for checkpoint occurring too frequently. This indicates max_wal_size is too small, forcing premature WAL flushes that degrade write throughput.

4️⃣ Section 4: Walkthrough — Tier 3: Historical Deep-Dive (pg_profile)

(Refers to 03-pg_profile-checklist in your Excel sheet)

For post-mortem analysis (Root Cause Analysis / RCA) or capacity planning.

1. Capture & List Snapshot Windows: Trigger samples (SELECT profile.take_sample();) and map sample IDs to the specific incident timeframe.
2. Audit Macro Throughput: Check transaction commits vs. rollbacks and deadlock counts across snapshot intervals.
3. Isolate Top Time-Consuming SQL: Rank query IDs by total runtime to pinpoint queries needing index tuning or structural rewriting.
4. Track Table Write Churn & Index Utilization: Identify tables accumulating dead tuples and verify whether maintenance jobs (vacuum/analyze) were performed during the incident window.

⚠️ Important Note on Schema Names:

Throughout the cheat sheet and queries in this section, we assume pg_profile is installed in its default schema name: profile (e.g., profile.take_sample() or FROM profile.samples).

In customer or production environments, DBAs may install the extension in a custom schema (such as perf, monitoring, or dba). Be sure to replace the word profile in these queries with your environment’s specific schema name!