Troubleshoot high memory utilization

Memory problems on PostgreSQL are usually configuration problems. The parameters that govern memory work together. The work_mem parameter applies to each sort or hash operation, for each query, and for each connection. So, a value that looks modest can be consumed hundreds of times. The Memory guide helps you find which multiplier is the problem.

Symptoms this guide explains

Memory utilization is consistently high, or it climbs without recovering.
Out-of-memory errors, or backends terminated by the OOM killer.
Memory usage rose sharply after a parameter change.
Degraded performance that correlates with connection count rather than query volume.

Before you start

Enable PostgreSQL Server Logs, Sessions data, and Query Store Runtime. Set pg_qs.query_capture_mode to TOP or ALL. Enable metrics.collector_database_activity. See Use the troubleshooting guides.

Where PostgreSQL memory actually goes

Understanding the four consumers makes the tabs self-explanatory, because each tab maps to one:

Consumer	What drives it	Tab
Shared memory	`shared_buffers`, allocated once at startup for the whole server	Memory parameters
Per-operation memory	`work_mem`, allocated per sort or hash, so a single complex query can use it many times over	Queries
Per-connection memory	Every connection is a process with its own overhead, before it runs anything	User connections
Maintenance memory	`maintenance_work_mem`, used by vacuum, index builds, and restores	Memory parameters

The multiplicative ones, work_mem and per-connection overhead, cause almost all surprises.

How this guide is organized

Tab	The question it answers
Memory	Is memory genuinely elevated, and when?
Workload	Did the amount of work increase?
Sessions	Is a specific session holding memory?
Queries	Which queries touch the most data?
User connections	Is connection count the real driver?
Memory parameters	Are the parameters sized appropriately for this SKU?

Walkthrough: memory climbing after a tuning change

Note the banner. The guide reports Memory parameter change detected, meaning one or more memory parameters are above their defaults. This information immediately changes the focus of the investigation: suspect configuration before workload.
Confirm the timing. The Memory tab shows utilization stepping up and staying there, rather than spiking and recovering. A step change that persists is characteristic of a configuration change, not a traffic event.
Rule out workload. Workload shows tuple activity unchanged across the step. The server isn't doing more; each unit of work now costs more memory.
Check the multiplier. User connections shows roughly 400 active connections. Memory parameters shows work_mem was raised to 64 MB. Those two numbers together are the finding: 400 connections, each potentially running several sorts or hashes at 64 MB apiece, is a far larger commitment than the headline value suggests.
Act on both sides. You reduce work_mem back toward a safe baseline and set it per-role for the few reporting queries that genuinely needed more. Separately, you introduce PgBouncer to cut the connection count, which reduces both the multiplier and the per-connection overhead.

The general lesson: with memory, always read a parameter value together with how many times it can be applied concurrently.

Tab reference

Memory

Confirms the symptom and the window. Distinguish two shapes:

A step change that persists points to configuration.
A spike that recovers points to a specific query or workload event.

Workload

Read tuple activity versus write tuple activity. Rising alongside memory means real additional work. Flat while memory climbs means each unit of work became more expensive, or something isn't releasing memory.

Sessions

Long-running sessions from sampled pg_stat_activity:

Connection duration (collection_time - backend_start): expected to be high when pooling.
Query duration (collection_time - query_start): should stay within your normal range.

A session running a single query for a very long time might accumulate memory in sorts or hashes that it doesn't release until it finishes.

The guide raises a Long idle session detected banner when a session is idle and connected beyond the threshold. Idle sessions still hold their per-connection memory, so a population of them inflates baseline memory without doing any work. Identify and end the stale ones, review the application logic that leaves sessions open, and set idle_session_timeout (PostgreSQL 14 and later).

Immediate mitigation. Cancel the running statement first. It's the less disruptive of the two options, because the session and its transaction survive:

SELECT pg_cancel_backend(<pid>);

If the session is idle in a transaction, or canceling doesn't release the resource, terminate the whole backend:

SELECT pg_terminate_backend(<pid>);

Caution

Terminating a backend rolls back its open transaction. Confirm what the session is doing before you end it. The PID might belong to a long-running migration or a batch job that restarts from the beginning.

Guardrails so it doesn't recur. Set limits on how long sessions and transactions can run:

Parameter	What it bounds	Notes
`statement_timeout`	A single statement	The broadest safety net. Set per role or per session rather than server-wide if some jobs legitimately run long.
`idle_in_transaction_session_timeout`	A session sitting idle with an open transaction	Defaults to `0` (never). This setting most often prevents blocked cleanup. `300000` (5 minutes) is a common starting point.
`idle_session_timeout`	A session idle with no transaction	PostgreSQL 14 and later. Reclaims connection slots from abandoned clients.
`transaction_timeout`	Total transaction duration	PostgreSQL 17 and later.

Queries

Ranks queries by data usage, the sum of shared_blks_hit and shared_blks_dirtied over the window.

Note

A page can be pinned repeatedly during a single execution, and each pin counts. A query's reported data usage can therefore legitimately exceed shared_buffers, or even total server memory. It measures work done against the buffer pool, not bytes resident.

Select a query ID for per-bucket detail: mean and total rows, mean/min/max data usage, and execution duration. A query with high data usage and low row output is reading far more than it returns, usually because of a missing index or an over-broad predicate.

Run index tuning to get index recommendations based on your actual workload. This step is the most effective long-term fix.
Run EXPLAIN (ANALYZE, BUFFERS) on the query to see where time and I/O actually go. Look for sequential scans on large tables, nested loops over big row counts, and sorts that spill to disk.
Reduce table bloat. As a one-time step, run VACUUM (ANALYZE, VERBOSE) <table_name>;. If bloat keeps returning, the cause is upstream. See Monitor autovacuum.
Add the indexes the plan needs, and remove joins that don't narrow the result.
Partition large tables that are consistently hot.
Revisit table design: index foreign keys in child tables, drop unused indexes (they cost on every write), and disable triggers during bulk loads.

For memory specifically, tune shared_buffers, work_mem, maintenance_work_mem, max_locks_per_transaction, and max_parallel_workers_per_gather deliberately. Each setting raises memory consumption, and parallel workers multiply it per query. If the workload is genuinely memory-intensive, moving from General Purpose to Memory Optimized gives more memory per vCore than scaling up within the same tier.

Note

On a read replica, run VACUUM and create indexes on the primary; changes replicate. You can set work_mem per session or at the replica server level independently of the primary.

User connections

Connections by state and by duration: short (under 1 second), normal (1 second to 20 minutes), long (over 20 minutes), derived from log disconnection messages.

Connection count is a memory multiplier, so this tab often matters more than it appears to. Bound idle sessions with statement_timeout, idle_in_transaction_session_timeout, and idle_session_timeout (PostgreSQL 14 and later).

Every PostgreSQL connection is a separate operating-system process with its own memory. Hundreds of mostly idle connections use real CPU and memory before running a single query, so pooling is usually a bigger win than scaling up. PgBouncer in Azure Database for PostgreSQL flexible server is built into Azure Database for PostgreSQL flexible server. Enable it with pgbouncer.enabled, and enable metrics.pgbouncer_diagnostics to get its metrics. Both are dynamic and need no restart. PgBouncer isn't supported on the Burstable tier.

Sizing the pool:

Setting	Guidance
`pool_mode`	Use `transaction` for high-throughput workloads made of many short transactions, since it gives far better connection reuse. Use `session` when you need features that span a connection, such as prepared statements, advisory locks, or `LISTEN`/`NOTIFY`.
`default_pool_size`	Applies per user/database pair, not per server. Add up every pool you expect and keep the total safely under `max_connections`.
`server_idle_timeout`	How long an unused server connection is kept. Lower it to release idle backends sooner.
`query_timeout`	Bounds a single query through the pooler.

Two failure modes worth recognizing:

Pool exhaustion. Active server connections sit at default_pool_size while waiting clients climb. Either raise default_pool_size, tune the slow queries that hold connections, or move from session to transaction mode.
PgBouncer saturation. PgBouncer is single-threaded, so it uses one core no matter how large the server is. The signature is latency on port 6432 that's worse than connecting directly on 5432, with waiting clients growing while active server connections stay below default_pool_size. Scale out across multiple PgBouncer instances on separate VMs, or evaluate a multithreaded pooler such as PgCat.

Memory parameters

Current values, defaults, and what's been modified.

How to read it:

Parameters left at their PostgreSQL default show no risk indicator. Only modified values are evaluated.
Recommended values are derived from your SKU's memory and current max_connections, and never suggest going below the PostgreSQL default.
If the SKU isn't recognized, ratio-based recommendations show as Unknown and only absolute rules apply.
Values are server-level. Per-session and per-role overrides via SET or ALTER ROLE ... SET aren't shown, so a healthy-looking value here doesn't guarantee what's actually in effect.

What good looks like

Signal	Healthy	Investigate
Memory utilization	Stable with headroom	Climbing without recovering, or no headroom at peak
Memory shape over time	Spikes that recover	Step change that persists
`work_mem` × concurrent connections	Comfortably within total memory	Approaching or exceeding it
Connection count	Stable, pooled	Hundreds of direct connections
Data usage vs rows returned	Proportionate	High data usage, few rows
Parameters	At or near recommended	Multiple modified values flagged

What this guide can't tell you

Actual resident memory per backend. Data usage measures buffer-pool work, not bytes held.
What effective settings a given session has. Only server-level values appear.
Which specific sort or hash spilled. Use EXPLAIN (ANALYZE, BUFFERS), and see Troubleshoot high temporary file utilization for spill analysis.
Whether an OOM was PostgreSQL's fault. Extensions and background processes also consume memory.
Sub-sample events. Session data is sampled; a brief allocation spike can be missed.

Feedback

Was this page helpful?

Last updated on 2026-09-16