database resource_error ai_generated partial

psycopg2.OperationalError: ERROR: out of memory DETAIL: Failed on request of size 123456.

ID: database/postgresql-too-many-pins

Also available as: JSON · Markdown · 中文
82%Fix Rate
84%Confidence
1Evidence
2024-03-05First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
PostgreSQL 14 active — — —
PostgreSQL 15 active — — —
PostgreSQL 16 active — — —

Root Cause

PostgreSQL server ran out of memory, typically due to excessive work_mem usage from multiple concurrent queries, or a single query with very large memory needs (e.g., sorting, hashing).

generic

中文

PostgreSQL 服务器内存耗尽，通常是由于多个并发查询使用了过多的 work_mem，或单个查询需要非常大的内存（如排序、哈希操作）。

Official Documentation

https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM

Workarounds

  1. 85% success Reduce work_mem for the session or globally to limit per-operation memory usage: SET work_mem = '32MB'; Or in postgresql.conf: work_mem = 32MB Then restart or reload. 
    Reduce work_mem for the session or globally to limit per-operation memory usage:
SET work_mem = '32MB';
Or in postgresql.conf:
work_mem = 32MB
Then restart or reload.
  2. 80% success Identify and optimize the problematic query using EXPLAIN ANALYZE, then rewrite it to use less memory (e.g., add indexes, reduce sort operations). 
    Identify and optimize the problematic query using EXPLAIN ANALYZE, then rewrite it to use less memory (e.g., add indexes, reduce sort operations).

中文步骤

  1. Reduce work_mem for the session or globally to limit per-operation memory usage:
SET work_mem = '32MB';
Or in postgresql.conf:
work_mem = 32MB
Then restart or reload.
  2. Identify and optimize the problematic query using EXPLAIN ANALYZE, then rewrite it to use less memory (e.g., add indexes, reduce sort operations).

Dead Ends

Common approaches that don't work:

  1. Increasing shared_buffers without adjusting work_mem 85% fail

    shared_buffers is for caching, not for per-query memory; it does not prevent out-of-memory errors from work_mem.

  2. Restarting PostgreSQL without changing memory settings 90% fail

    The memory is freed temporarily but the same query pattern will exhaust it again.