Azure PostgreSQL Flexible Server — Troubleshooting Checklist

Structured checks for diagnosing incidents using Log Analytics diagnostic data. Each check carries a type badge that tells you how to read its result.

Check types

	Type	What it answers
🔴	Threshold	Is this value above/below a known-bad line right now? Single-window query; pass/fail verdict.
📊	Delta	How did this metric change between the good and bad window? Renders as a chart for visual comparison.
🔗	Correlation	Do two signals co-move? Two series overlaid in a single timechart.
🗂️	Inventory	What's present — no verdict, builds your baseline.

render only works in the Portal. render directives produce charts in the Azure Portal Log Analytics UI and Azure Workbooks only. When running queries via az monitor log-analytics query, the directive is silently ignored and results come back as JSON/table. To chart CLI output, export to CSV and use a local tool (Python, Excel, etc.).

PostgreSQLFlexSessions

Source: pfl-sessions.md · pg_stat_activity snapshot every ~5 min

Set these variables at the top of every query in this section:

let server    = "my-pg-prd-1";
let db        = "mydb";
let badStart  = datetime(2026-06-23T10:00:00Z);
let badEnd    = datetime(2026-06-23T11:00:00Z);
let goodStart = datetime(2026-06-14T10:00:00Z);
let goodEnd   = datetime(2026-06-14T11:00:00Z);

The db filter constrains checks to client sessions connected to that database. Internal backends (autovacuum workers, checkpointer, walwriter) are not tied to a single database and will be excluded.

🗂️ 1 · Connection inventory

Who is connecting and from what applications. Run this first to establish a baseline before checking thresholds.

Kusto — table

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| summarize sessions = dcount(Pid_d) by Application_name_s, Backend_type_s
| order by sessions desc

Kusto — line plot (connections over time by application)

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| summarize connections = dcount(Pid_d) by Collection_time_t, Application_name_s
| render timechart with (xcolumn=Collection_time_t, series=Application_name_s, ycolumns=connections)

🔴 2 · Connection count vs `max_connections`

Peak number of concurrent connections to this database during the bad window. A connection surge exhausts the server-wide max_connections limit and causes new connections to be rejected with 53300.

Threshold: peak > 80 % of max_connections → connection saturation. Get the limit: az postgres flexible-server parameter show --name max_connections --server-name <server> --resource-group <rg>.

Kusto

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| summarize connections = dcount(Pid_d) by Collection_time_t
| summarize peak = max(connections)

🔴 3 · Idle-in-transaction sessions

Sessions stuck in idle in transaction hold locks, block autovacuum, and inflate the xmin horizon. They are usually a sign of application code that opens a transaction and fails to commit or roll back.

Threshold: > 3 sessions across two or more consecutive snapshots → investigate application connection handling.

Kusto

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| where State_s in ("idle in transaction", "idle in transaction (aborted)")
| summarize idle_in_xact = dcount(Pid_d) by Collection_time_t
| order by Collection_time_t asc

🔴 4 · Long-running open transactions

Transactions open for more than 10 minutes hold their xmin horizon for the entire duration, preventing dead-tuple reclaim on any table they touched — and they hold any locks acquired during that time.

Threshold: any row with xact_age_min > 10 → bloat and lock risk. Identify the Application_name_s and investigate why the transaction is not closed.

Kusto

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| where isnotempty(Xact_start_t)
| extend xact_age_min = datetime_diff("minute", Collection_time_t, Xact_start_t)
| where xact_age_min > 10
| project Collection_time_t, Pid_d, Application_name_s, Client_addr_s,
          State_s, xact_age_min
| order by xact_age_min desc

🔴 5 · Lock waiters

Sessions blocked on a Lock wait are being held up by another session holding an incompatible lock. Even a single sustained lock waiter indicates a blocking chain worth investigating.

Threshold: any non-zero count sustained across ≥ 2 snapshots → find the blocker. Cross-reference with checks 3 and 4 — the blocker is almost always an idle-in-transaction or long-running session.

Kusto

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| where Wait_event_type_s == "Lock"
| summarize lock_waiters = dcount(Pid_d) by Collection_time_t
| order by Collection_time_t asc

🔴 6 · xmin holders

A session with Backend_xmin_d set pins the global xmin horizon at (at most) its own xmin value. Autovacuum cannot reclaim dead tuples older than that horizon on any table, causing table bloat that compounds over time.

Threshold: persistent non-zero count — especially if the same session appears in multiple snapshots — is a long-term bloat risk even if no query is visibly slow right now.

Kusto

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| where isnotempty(Backend_xmin_d)
| summarize xmin_holders = dcount(Pid_d) by Collection_time_t
| order by Collection_time_t asc

📊 7 · Connection count delta (good vs bad)

Peak connection count in the bad window compared to the good window. A spike here points to a connection burst as the driver of the incident (pool misconfiguration, retry storm, traffic surge).

Kusto

let server    = "my-pg-prd-1";
let db        = "mydb";
let badStart  = datetime(2026-06-23T10:00:00Z);
let badEnd    = datetime(2026-06-23T11:00:00Z);
let goodStart = datetime(2026-06-14T10:00:00Z);
let goodEnd   = datetime(2026-06-14T11:00:00Z);
let good = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(goodStart .. goodEnd)
    | where Database_name_s == db
    | summarize connections = dcount(Pid_d) by Collection_time_t
    | summarize peak = max(connections)
    | extend window = "good";
let bad = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(badStart .. badEnd)
    | where Database_name_s == db
    | summarize connections = dcount(Pid_d) by Collection_time_t
    | summarize peak = max(connections)
    | extend window = "bad";
union good, bad
| render barchart with (xcolumn=window, ycolumns=peak)

📊 8 · Wait event distribution shift

Compares the share of each wait event class between the two windows. A new wait class dominating the bad window (e.g. Lock, IO, LWLock) is a direct signal of what the server was blocked on.

Kusto

let server    = "my-pg-prd-1";
let db        = "mydb";
let badStart  = datetime(2026-06-23T10:00:00Z);
let badEnd    = datetime(2026-06-23T11:00:00Z);
let goodStart = datetime(2026-06-14T10:00:00Z);
let goodEnd   = datetime(2026-06-14T11:00:00Z);
let good = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(goodStart .. goodEnd)
    | where Database_name_s == db
    | where isnotempty(Wait_event_type_s)
    | summarize samples = count() by Wait_event_type_s
    | extend window = "good";
let bad = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(badStart .. badEnd)
    | where Database_name_s == db
    | where isnotempty(Wait_event_type_s)
    | summarize samples = count() by Wait_event_type_s
    | extend window = "bad";
union good, bad
| render barchart with (xcolumn=Wait_event_type_s, series=window, ycolumns=samples)

🔗 9 · Idle-in-transaction × lock waiters over time

Overlays the idle-in-transaction session count and the lock waiter count on the same timechart. The causal chain runs left to right: idle-in-transaction sessions accumulate first, then lock waiters rise as blocked sessions queue up behind them.

Kusto

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
let idle_in_xact = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(badStart .. badEnd)
    | where Database_name_s == db
    | where State_s in ("idle in transaction", "idle in transaction (aborted)")
    | summarize value = dcount(Pid_d) by Collection_time_t
    | extend metric = "idle_in_transaction";
let lock_waiters = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(badStart .. badEnd)
    | where Database_name_s == db
    | where Wait_event_type_s == "Lock"
    | summarize value = dcount(Pid_d) by Collection_time_t
    | extend metric = "lock_waiters";
union idle_in_xact, lock_waiters
| render timechart with (xcolumn=Collection_time_t, series=metric, ycolumns=value)

📊 10 · Idle-in-transaction — good vs bad, time-aligned

Plots idle-in-transaction session count for both windows on the same relative X axis (minutes elapsed from the start of each window), skipping the days in between. Use this to compare the shape of the curve across days without the gap distorting the view.

Kusto

let server    = "my-pg-prd-1";
let db        = "mydb";
let badStart  = datetime(2026-06-23T10:00:00Z);
let badEnd    = datetime(2026-06-23T11:00:00Z);
let goodStart = datetime(2026-06-14T10:00:00Z);
let goodEnd   = datetime(2026-06-14T11:00:00Z);
let idle_bad = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(badStart .. badEnd)
    | where Database_name_s == db
    | where State_s in ("idle in transaction", "idle in transaction (aborted)")
    | summarize value = dcount(Pid_d) by Collection_time_t
    | extend elapsed_min = datetime_diff("minute", Collection_time_t, badStart)
    | extend series = "bad";
let idle_good = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(goodStart .. goodEnd)
    | where Database_name_s == db
    | where State_s in ("idle in transaction", "idle in transaction (aborted)")
    | summarize value = dcount(Pid_d) by Collection_time_t
    | extend elapsed_min = datetime_diff("minute", Collection_time_t, goodStart)
    | extend series = "good";
union idle_bad, idle_good
| render timechart with (xcolumn=elapsed_min, series=series, ycolumns=value)

Drill-down: idle-in-transaction confirmed

Once checks 3–5 have established that idle-in-transaction sessions are driving lock contention, run the following checks across three additional log categories to answer:

Who is holding the sessions open and what was the last statement they ran? (PostgreSQLLogs)
Which queries are being blocked? (QueryStoreWaitStats + SqlText)
What is the downstream impact on tables? (TableStats)

Set these variables at the top of every query in this section:

let server   = "my-pg-prd-1";
let db       = "mydb";
let dbid     = 0.0;       // replace with result of the Dbid helper below
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);

Getting dbid: QueryStore categories filter by OID (Dbid_d), not database name. Run the helper query in check 14 first to resolve the OID for your database.

🗂️ 11 · Offending session detail

Returns one row per idle-in-transaction PID showing the worst transaction age seen and how many snapshots the session persisted across. The Pid_d values here feed directly into check 13.

Kusto

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| where State_s in ("idle in transaction", "idle in transaction (aborted)")
| where isnotempty(Xact_start_t)
| extend xact_age_min = datetime_diff("minute", Collection_time_t, Xact_start_t)
| summarize
    max_age_min = max(xact_age_min),
    snapshots   = dcount(Collection_time_t)
    by Pid_d, Application_name_s, Client_addr_s
| order by max_age_min desc

Scans PostgreSQLLogs for engine-level evidence: deadlock kills (40P01), lock-wait timeouts (55P03), and idle-in-transaction session kills. Any row here means the engine already acted on the contention.

Threshold: any row → confirmed engine-level lock event; check sqlerrcode_s for the type. Prerequisite: PostgreSQLLogs must be enabled in Diagnostic Settings (it is free to export).

Kusto

let server   = "my-pg-prd-1";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLLogs" and LogicalServerName_s == server
| where TimeGenerated between(badStart .. badEnd)
| where sqlerrcode_s in ("40P01", "55P03")
    or Message has_any ("deadlock detected", "lock wait timeout", "idle-in-transaction timeout")
| where errorLevel_s in ("ERROR", "WARNING", "LOG")
| project timestamp_s, processId_d, errorLevel_s, sqlerrcode_s, Message
| order by timestamp_s asc

🗂️ 13 · Last statement from offending PIDs

Finds the last SQL statement logged for each PID identified in check 11. This reveals what the application was doing before the session went idle in transaction.

Prerequisite: statement_s is only populated when log_statement = 'all' or log_min_duration_statement is configured. If no rows are returned the parameter is not set — consider enabling it temporarily.

Kusto

let server        = "my-pg-prd-1";
let badStart      = datetime(2026-06-23T10:00:00Z);
let badEnd        = datetime(2026-06-23T11:00:00Z);
let offending_pids = dynamic([12345, 67890]);  // paste Pid_d values from check 11
AzureDiagnostics
| where Category == "PostgreSQLLogs" and LogicalServerName_s == server
| where TimeGenerated between(badStart .. badEnd)
| where processId_d in (offending_pids)
| where isnotempty(statement_s)
| project timestamp_s, processId_d, userName_s, applicationName_s, statement_s, detail_s
| order by timestamp_s asc

🔗 14 · Queries accumulating Lock waits

Shows which query fingerprints were sampled most often waiting on Lock events during the bad window — these are the queries being blocked by the idle-in-transaction sessions. Joined with SqlText so the SQL is immediately readable.

Dbid helper — run this first to get the numeric database OID, then set dbid in subsequent queries:

Kusto — Dbid helper

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| distinct Datid_d

Kusto — blocked queries

let server   = "my-pg-prd-1";
let dbid     = 0.0;       // replace with Datid_d from helper above
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
let sql_text = AzureDiagnostics
    | where Category == "PostgreSQLQueryStoreSqlText" and LogicalServerName_s == server
    | distinct Queryid_str_s, Query_sql_text_s;
AzureDiagnostics
| where Category == "PostgreSQLFlexQueryStoreWaitStats" and LogicalServerName_s == server
| where Start_time_t between(badStart .. badEnd)
| where Dbid_d == dbid
| where Event_type_s == "Lock"
| summarize lock_samples = sum(Calls_d) by Queryid_str_s
| order by lock_samples desc
| join kind=leftouter sql_text on Queryid_str_s
| project Queryid_str_s, lock_samples, Query_sql_text_s

📊 15 · Query runtime inflation (good vs bad)

Compares the worst-case execution time (Max_time_d) of write queries between the two windows. Queries that were being blocked will show dramatically inflated Max_time_d in the bad window even if their average time looks normal.

Kusto

let server    = "my-pg-prd-1";
let dbid      = 0.0;       // replace with Datid_d from check 14 helper
let badStart  = datetime(2026-06-23T10:00:00Z);
let badEnd    = datetime(2026-06-23T11:00:00Z);
let goodStart = datetime(2026-06-14T10:00:00Z);
let goodEnd   = datetime(2026-06-14T11:00:00Z);
let sql_text = AzureDiagnostics
    | where Category == "PostgreSQLQueryStoreSqlText" and LogicalServerName_s == server
    | distinct Queryid_str_s, Query_sql_text_s;
let good = AzureDiagnostics
    | where Category == "PostgreSQLFlexQueryStoreRuntime" and LogicalServerName_s == server
    | where Start_time_t between(goodStart .. goodEnd)
    | where Dbid_d == dbid
    | where Query_type_s in ("update", "delete", "insert") and Is_system_query_b == false
    | summarize max_time_ms = max(Max_time_d) by Queryid_str_s
    | extend window = "good";
let bad = AzureDiagnostics
    | where Category == "PostgreSQLFlexQueryStoreRuntime" and LogicalServerName_s == server
    | where Start_time_t between(badStart .. badEnd)
    | where Dbid_d == dbid
    | where Query_type_s in ("update", "delete", "insert") and Is_system_query_b == false
    | summarize max_time_ms = max(Max_time_d) by Queryid_str_s
    | extend window = "bad";
union good, bad
| join kind=leftouter sql_text on Queryid_str_s
| render barchart with (xcolumn=Queryid_str_s, series=window, ycolumns=max_time_ms)

🔴 16 · Dead tuple accumulation (bloat signal)

Identifies tables where dead tuples are building up — the delayed consequence of xmin being held by idle-in-transaction sessions. Autovacuum cannot reclaim dead tuples on any table while a session holds an xmin horizon older than those tuples.

Threshold: bloat_pct > 20 % on any table → autovacuum is falling behind; bloat is compounding. Note: Relname_s (table name) may not appear in all workspace configurations; if it is absent, the data is aggregated at schema level only.

Kusto

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexTableStats" and LogicalServerName_s == server
| where TimeGenerated between(badStart .. badEnd)
| where DatabaseName_s == db
| where N_live_tup_d > 0
| extend bloat_pct = round(100.0 * N_dead_tup_d / (N_live_tup_d + N_dead_tup_d), 1)
| where bloat_pct > 20
| summarize
    max_bloat_pct = max(bloat_pct),
    max_dead_tup  = max(N_dead_tup_d),
    max_live_tup  = max(N_live_tup_d)
    by Schemaname_s, Relname_s
| order by max_bloat_pct desc

Keyboard shortcuts

M'Goun