4 ways to Find the Query Using the Most Memory in PostgreSQL

Your PostgreSQL server is running low on memory. Maybe you saw the Linux OOM killer in the logs, or free -h shows almost nothing left. The obvious question is: which query is eating all the RAM?

There’s no single command that tells you, and that surprises people. pg_stat_statements tracks time, rows, and disk reads for every query, but it has no memory column. PostgreSQL doesn’t keep a “memory used per query” counter.

You can still find the culprit. You just have to combine a few clues. This guide shows you how, with examples you can run.

First, Know Where PostgreSQL Memory Goes

Before you go hunting, it helps to know the two main kinds of memory PostgreSQL uses:

1. Shared memory (shared_buffers) This is one big cache that all connections share. You set its size once, for example 4 GB, and it doesn’t grow because of a particular query.

2. Private memory per connection Each connection is its own Linux process, and it uses its own memory for query work. This is the part that grows. The biggest consumers are:

• Sorts (ORDER BY, DISTINCT, merge joins)
• Hash tables (hash joins, GROUP BY)

Each sort or hash step can use up to work_mem, and hash steps can use a bit more (work_mem × hash_mem_multiplier). The catch is that one query can have several of these steps, and parallel workers each get their own allowance.

A simple example: with work_mem = 64MB, a query with three hash joins and two parallel workers could use around 64 MB × 3 × 3 processes, which is 576 MB, before counting the hash multiplier. Now picture 50 connections doing that at once.

So when you’re looking for a memory-hungry query, you’re usually looking for a query doing big sorts or hashes.

Method 1: Find the Biggest Process in Linux (Carefully)

Every PostgreSQL connection is a Linux process, so start there:

See also: Mastering the Linux Command Line — Your Complete Linux Training Course

ps -u postgres -o pid,rss,cmd --sort=-rss | head -6

  PID   RSS CMD
48211 1153024 postgres: report shop 10.0.0.15(51234) SELECT
48390  612340 postgres: app_user shop 10.0.0.22(40112) idle
 1021  540112 postgres: checkpointer
48502  210884 postgres: app_user shop 10.0.0.22(40188) SELECT
 1019   98320 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main

RSS is in kilobytes, so process 48211 shows about 1.1 GB. But don’t trust that number yet.

Why top and ps Exaggerate

RSS counts every page the process has touched, including the shared buffer cache. If a connection scanned a big table, it touched a lot of shared buffers, and they show up in its RSS even though that memory is shared and isn’t really “its own.”

That’s why even the idle checkpointer above looks like it’s using 540 MB. It has simply touched a lot of shared buffers.

Check the Process’s Real Private Memory

Linux can tell you how much memory belongs to that process alone. Look at smaps_rollup:

sudo grep -E '^(Rss|Pss|Shared_Dirty|Private_Dirty)' /proc/48211/smaps_rollup

Rss:             1153024 kB
Pss:              712040 kB
Shared_Dirty:     498304 kB
Private_Dirty:    646600 kB

Now it’s clearer:

• Shared_Dirty, about 490 MB: mostly shared buffers. This isn’t the query’s fault.
• Private_Dirty, about 630 MB: memory this process allocated for itself. This is the real query memory, for things like sorts and hash tables.

Run the same check on the top few PIDs and compare their Private_Dirty values. The largest one is your suspect.

Turn the PID Into a Query

Now ask PostgreSQL what that process is running:

SELECT pid, usename, state,
       now() - query_start AS running_for,
       query
FROM pg_stat_activity
WHERE pid = 48211;

  pid  | usename | state  | running_for |                          query
-------+---------+--------+-------------+-----------------------------------------------------------
 48211 | report  | active | 00:07:12    | SELECT customer_id, array_agg(product_id ORDER BY created_at) FROM order_items GROUP BY customer_id

There’s your memory-hungry query: a big GROUP BY with an ordered aggregate across a large table.

Method 2: Ask PostgreSQL to Break Down a Process’s Memory

PostgreSQL 14 and newer can print a detailed memory report for any connection. Run this as a superuser:

SELECT pg_log_backend_memory_contexts(48211);

It returns t, and the report is written to the PostgreSQL server log, not to your screen. Check the log:

sudo tail -50 /var/log/postgresql/postgresql-16-main.log

LOG:  logging memory contexts of PID 48211
LOG:  level: 0; TopMemoryContext: 152360 total in 7 blocks; ...
LOG:  level: 1; PortalContext: 1024 total in 1 blocks; ...
LOG:  level: 2; ExecutorState: 612368432 total in 84 blocks; ...
LOG:  level: 3; HashAgg meta context: 8192 total in 1 blocks; ...
...
LOG:  Grand total: 649871224 bytes in 312 blocks; ...

How to read it:

• Grand total is how much private memory the process is using inside PostgreSQL, about 620 MB here. That roughly matches Private_Dirty from Method 1.
• ExecutorState is memory used while running the query. When it’s huge, the query itself is the problem, not caching or connection overhead.

For your own session, you can query this as a view instead of reading the log:

SELECT name, pg_size_pretty(total_bytes) AS size
FROM pg_backend_memory_contexts
ORDER BY total_bytes DESC
LIMIT 5;

Method 3: Find Memory-Hungry Queries in History (Temp Files)

Methods 1 and 2 only work while the query is still running. What about the query that ran at 3 a.m. and pushed the server over?

This is the trick: when a sort or hash needs more than work_mem, PostgreSQL writes the extra data to temporary files on disk. So the queries writing the most temp files are the ones that wanted the most memory.

If you have pg_stat_statements enabled:

SELECT calls,
       pg_size_pretty(temp_blks_written * 8192) AS temp_written,
       round(mean_exec_time::numeric, 1)         AS avg_ms,
       left(query, 70)                           AS query
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

 calls | temp_written |  avg_ms  |                                 query
-------+--------------+----------+-----------------------------------------------------------------------
   140 | 186 GB       | 45120.3  | SELECT customer_id, array_agg(product_id ORDER BY created_at) FROM ...
  9822 | 3812 MB      |   210.7  | SELECT * FROM orders ORDER BY total DESC LIMIT $1 OFFSET $2
    51 | 940 MB       |  3302.9  | SELECT DISTINCT email FROM newsletter_signups

The first query wrote 186 GB of temp data over 140 runs, more than 1 GB each time. That’s the one to look at.

You can also have PostgreSQL log every temp file it creates, along with the query that created it:

ALTER SYSTEM SET log_temp_files = '100MB';
SELECT pg_reload_conf();

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp48211.3", size 1342177280
STATEMENT:  SELECT customer_id, array_agg(product_id ORDER BY created_at) FROM order_items GROUP BY customer_id

Method 4: Confirm With EXPLAIN ANALYZE

Once you have a suspect, EXPLAIN ANALYZE shows how much memory each step used:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders ORDER BY total DESC;

 Sort  (actual time=2841.112..3920.554 rows=5000000 loops=1)
   Sort Key: total DESC
   Sort Method: external merge  Disk: 412160kB
   ->  Seq Scan on orders  (actual time=0.012..801.330 rows=5000000 loops=1)

Look for these lines:

• Sort Method: quicksort Memory: 2048kB: the sort fit in memory.
• Sort Method: external merge Disk: 412160kB: the sort needed about 400 MB, didn’t fit in work_mem, and spilled to disk.
• Batches: 8 under a Hash node: the hash table was too big and was split into batches.

Remember that EXPLAIN ANALYZE actually runs the query. Try it on a replica or during a quiet period.

How to Fix a Memory-Hungry Query

Once you’ve found it, here are the usual fixes, starting with the safest:

• Add an index that avoids the sort. If a query does ORDER BY created_at, an index on created_at can return rows already in order, so there’s no sort at all.
• Fetch less data. Avoid SELECT * on wide tables. Filter earlier, and paginate with WHERE id > $1 instead of a large OFFSET.
• Give memory only where it’s needed. Don’t raise work_mem for the whole server. Set it for the one session that needs it:

SET work_mem = '512MB';
-- run the big report
RESET work_mem;

• Limit connections. 300 connections × several work_mem each adds up fast. A pooler like PgBouncer keeps the number of active connections under control.
• Stop the OOM killer from crashing everything. If Linux kills one PostgreSQL process, PostgreSQL restarts all connections to protect its data. The PostgreSQL docs recommend setting vm.overcommit_memory = 2 on dedicated database servers, so allocations fail cleanly instead of triggering the OOM killer.

Summary

Question	How to find out
Which process is using the most memory right now?	ps --sort=-rss, then check Private_Dirty in /proc/<pid>/smaps_rollup
What query is that process running?	pg_stat_activity with the PID
Where inside PostgreSQL is the memory going?	pg_log_backend_memory_contexts(pid) (PostgreSQL 14+)
Which past queries needed the most memory?	pg_stat_statements.temp_blks_written, or log_temp_files
How much did each step use?	EXPLAIN (ANALYZE, BUFFERS)

PostgreSQL doesn’t hand you a “memory per query” number, but the clues are all there. Ignore the shared buffers that make top misleading, look at private memory and temp files, and the memory-hungry query will stand out.