How to Fix "max memory exceeded" in PostgreSQL

How do I resolve the Postgres "max memory exceeded" error?

The error appears when a query's memory demand exceeds the session-level work_mem limit or the server runs out of available RAM.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What triggers the "max memory exceeded" error?

PostgreSQL raises this message when a single backend needs more memory for sorts, hashes, or aggregates than allowed by work_mem. If multiple sessions expand simultaneously, the OS may also run out of RAM, forcing PostgreSQL to abort the query.

How do I identify the query using too much memory?

Enable log_min_error_statement = error and log_statement_stats = on, then re-run the workload. PostgreSQL logs the offending SQL along with memory usage statistics, letting you focus tuning efforts on the right statement.

Which parameters control per-query memory?

work_mem limits memory per sort or hash operation. maintenance_work_mem governs CREATE INDEX, VACUUM, and CLUSTER. Increasing either eases memory pressure but must match server RAM.

How can I safely raise work_mem?

Calculate: (max_connections × work_mem) + shared_buffers should not exceed 70–80% of physical RAM. Raise work_mem gradually, monitor pg_stat_database.stats_reset, and watch top or pg_stat_activity for spikes.

Can I tune memory just for one heavy report?

Yes. Use SET LOCAL work_mem = '256MB' inside a transaction or create a reporting role with ALTER ROLE analyst SET work_mem = '256MB'; so standard OLTP sessions remain lean.

What query rewrites help?

Break large aggregations into smaller steps, add indexes to avoid full sorts, and filter early. In ecommerce, pre-aggregate daily sales, then join to Customers for reporting instead of aggregating raw OrderItems each time.

How do I monitor ongoing memory usage?

Enable track_io_timing and query pg_stat_kcache (extension) or use pg_top. Alert when total resident memory nears 90% of RAM or when a single backend exceeds expected limits.

Why How to Fix "max memory exceeded" in PostgreSQL is important

How to Fix "max memory exceeded" in PostgreSQL Example Usage


-- Problem query (blows past default 4MB work_mem)
SELECT customer_id, COUNT(*)
FROM   Orders o
JOIN   OrderItems oi ON oi.order_id = o.id
GROUP  BY customer_id
ORDER  BY COUNT(*) DESC;

-- Fix: raise work_mem just for this session
SET work_mem = '128MB';
-- Re-run the query
SELECT customer_id, COUNT(*)
FROM   Orders o
JOIN   OrderItems oi ON oi.order_id = o.id
GROUP  BY customer_id
ORDER  BY COUNT(*) DESC;

How to Fix "max memory exceeded" in PostgreSQL Syntax


-- Session-level change
SET work_mem = '128MB';

-- Permanent per-role setting
ALTER ROLE analyst SET work_mem = '256MB';

-- Cluster-wide change (requires reload)
ALTER SYSTEM SET work_mem = '64MB';

-- Example: speed up large monthly sales report
BEGIN;
SET LOCAL work_mem = '512MB';
SELECT c.name,
       SUM(oi.quantity * p.price) AS monthly_total
FROM   Customers c
JOIN   Orders   o  ON o.customer_id = c.id
JOIN   OrderItems oi ON oi.order_id = o.id
JOIN   Products p ON p.id = oi.product_id
WHERE  o.order_date BETWEEN '2024-05-01' AND '2024-05-31'
GROUP  BY c.name
ORDER  BY monthly_total DESC;
COMMIT;

Common Mistakes

Blindly doubling work_mem for all sessions. This can exhaust RAM when many connections run in parallel. Instead, calculate total possible consumption and adjust gradually.
Increasing shared_buffers to fix the error. The issue is per-query memory (work_mem), not shared buffers. Tweak the correct setting or rewrite the query.