📋

Description

This error indicates that the PostgreSQL server or a specific backend process has run out of available memory (RAM). It typically occurs when a query or operation requires more resources than allocated or available on the system.

💬

Error Message

out of memory

🔍

Known Causes

4 known causes

⚠️

Excessive Query Memory Usage

Complex queries involving large sorts, joins, or aggregations can consume significant amounts of RAM, exceeding allocated limits.

⚠️

Insufficient Server RAM

The physical server hosting PostgreSQL may not have enough installed RAM to handle the overall database workload.

⚠️

Misconfigured PostgreSQL Parameters

Parameters like `work_mem`, `maintenance_work_mem`, or `shared_buffers` might be set too low for the expected workload, leading to resource starvation.

⚠️

Operating System Memory Pressure

Other applications or processes running on the same server might be consuming a significant portion of available RAM, leaving insufficient resources for PostgreSQL.

🛠️

Solutions

4 solutions available

1. Increase shared_buffers medium

Allocate more memory to PostgreSQL

1

Check current settings

SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;

2

Update postgresql.conf

# Typically 25% of total RAM
shared_buffers = 4GB
work_mem = 256MB
maintenance_work_mem = 1GB

3

Restart PostgreSQL

sudo systemctl restart postgresql

2. Optimize Queries medium

Reduce memory-intensive operations

1

Check query memory usage

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_table ORDER BY column;

2

Add indexes to avoid sorts

CREATE INDEX idx_column ON big_table(column);

3

Limit result sets

SELECT * FROM big_table ORDER BY column LIMIT 1000;

3. Reduce Concurrent Connections medium

Fewer connections = less memory per query

1

Check active connections

SELECT count(*) FROM pg_stat_activity;

2

Lower max_connections if too high

-- In postgresql.conf:
max_connections = 100  -- Lower from default 100
work_mem = 256MB  -- Can be higher with fewer connections

3

Use connection pooling (PgBouncer)

# Install PgBouncer
apt install pgbouncer

# Configure to limit actual PostgreSQL connections

4. Increase Server Memory advanced

Add more RAM to the server

1

Check current memory

free -h

2

Add RAM and adjust PostgreSQL settings accordingly

# After adding RAM, update postgresql.conf:
shared_buffers = 8GB  # ~25% of new total
effective_cache_size = 24GB  # ~75% of total

🔗

Related Errors

5 related errors

PostgreSQL Error 53200: Out of Memory