postgres memory contex memory architecture - ghdrako/doc_snipets GitHub Wiki

Memory architecture

-- View all memory-related settings
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem',
    'effective_cache_size',
    'temp_buffers',
    'wal_buffers',
    'max_connections'
)
ORDER BY name;

-- Calculate maximum possible memory usage
SELECT
    (SELECT setting::int FROM pg_settings WHERE name = 'shared_buffers') *
    (SELECT setting::int FROM pg_settings WHERE name = 'block_size') / (1024*1024) AS shared_buffers_mb,

    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') *
    (SELECT setting::int FROM pg_settings WHERE name = 'work_mem') / 1024 AS max_work_mem_mb,

    (SELECT setting::int FROM pg_settings WHERE name = 'maintenance_work_mem') / 1024 AS maintenance_work_mem_mb;

-- Check memory usage by backend
SELECT
    pid,
    usename,
    application_name,
    state,
    backend_type,
    query
FROM pg_stat_activity
WHERE state = 'active';

-- Check for memory-intensive operations
SELECT
    pid,
    now() - query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE state != 'idle'
AND query NOT LIKE '%pg_stat_activity%'
ORDER BY query_start;
System-Level Memory Check
# Check system memory

free -h

# Check PostgreSQL process memory
ps aux --sort=-%mem | grep postgres | head -20

# Check for OOM killer activity
dmesg | grep -i "out of memory"
dmesg | grep -i "killed process"

# Check system logs
journalctl -u postgresql --since "1 hour ago" | grep -i memory

Memory context

To reduce the danger of memory leaks, PostgreSQL has implemented its own memory management system: memory contexts. Memory contexts are chunks of memory that can grow on demand. You never directly malloc() memory in PostgreSQL, but you request memory from a memory context. If necessary, PostgreSQL will extend the memory context.

The big advantage of memory contexts is that you can delete them, which frees all the memory at once. That means that you don't have to keep track of all your memory allocations. Simply make sure that you allocate memory in a memory context with the appropriate lifespan, and there is no danger of memory leaking past the end of that lifespan. For example, the PostgreSQL query executor will create an ExecutorState context before it starts processing a statement. If you need memory, you simply allocate it in that memory context. The executor will delete the ExecutorState when it is done, and there is no danger that any memory can leak past the end of the query execution.

Global Memory Contexts as a tree

Memory contexts form a hierarchy. The top memory context TopMemoryContext exists for the whole lifetime of a backend process. Any other memory context has a parent memory context. When PostgreSQL deletes a memory context, it will recursively delete all descendent memory contexts.

TopMemoryContext* # The root of the hierarchy. It is never deleted.

  • PostmasterContext
  • CacheMemoryContext* # Contains a cache for database metadata and cached query execution plans. Consumes more space if your database # contains many objects, for example table partitions, or if you have many prepared statements.
  • MessageContext # Contains the statement from the client and sometimes planning and parsing data.
  • TopTransactionContext
    • CurTransactionContext*
      • PortalContext* # Memory associated with the currently active statement (known as portal or cursor)
      • ErrorContext*

The view pg_backend_memory_contexts shows all memory contexts held by the current session.

You can only query this view between statements, but it would be much more useful to see the contents while you are executing an SQL statement. For that, you can create a function and build it into your SQL statement at strategic points:

CREATE FUNCTION dump_my_mem() RETURNS void
   LANGUAGE plpgsql AS
$$DECLARE
   r record;
BEGIN
   FOR r IN
      SELECT name, ident, level, total_bytes
      FROM pg_backend_memory_contexts
   LOOP
      RAISE NOTICE '% % % %',
         repeat('  ', r.level - 1),
         r.name,
         r.total_bytes,
         r.ident;
   END LOOP;
END;$$;