Understanding how PostgreSQL uses memory internally is important for database administrators, PostgreSQL developers, and engineers who work with high-performance database systems. PostgreSQL manages memory through a structured system called Memory Contexts, which allows the database to allocate and release memory safely during query execution and background operations.
PostgreSQL provides a useful built-in function named pg_log_backend_memory_contexts() that allows administrators to inspect the memory usage of any backend process using its process ID (PID). This function logs the entire memory context hierarchy of the target backend into the PostgreSQL server log.
Understanding PostgreSQL Memory Contexts
PostgreSQL does not directly rely on repeated calls to malloc() and free() for every memory operation. Instead, it organizes memory allocations into memory contexts. Each context acts as a container that manages related allocations together.
This design provides several benefits:
- Efficient cleanup of memory by releasing an entire context at once
- Reduced risk of memory leaks
- Structured memory usage for different database subsystems
- Better debugging capabilities
Memory contexts form a hierarchical tree structure where the TopMemoryContext acts as the root.
Example structure:
TopMemoryContext
+-- CacheMemoryContext
+-- TopTransactionContext
+-- PortalContext
+-- ErrorContext
+-- ExecutorState
Each backend process maintains its own memory context tree.
The pg_log_backend_memory_contexts() Function
PostgreSQL exposes a diagnostic function:
pg_log_backend_memory_contexts(pid)
Purpose
The function logs the memory context statistics of a specific backend process into the PostgreSQL server log.
Key Characteristics
- Requires the process ID (PID) of a PostgreSQL backend
- Outputs information only to the server log
- Returns a boolean value indicating whether the logging succeeded
- Does not display the memory information directly in SQL results
Identifying Backend Processes
Before using the function, you must identify the process ID of the backend you want to inspect.
This can be done using the pg_stat_activity system view.
Example:
SELECT * FROM pg_stat_activity;
Example output snippet:
-[ RECORD 2 ]----+---------------------------------
datid |
datname |
pid | 24813
leader_pid |
usesysid |
usename |
application_name |
client_addr |
client_hostname |
client_port |
backend_start | 2026-03-12 19:44:11.707502+05:30
wait_event_type | Activity
wait_event | AutovacuumMain
backend_type | autovacuum launcher
In this example, the backend process with PID 24813 corresponds to the autovacuum launcher.
Logging Memory Contexts for a Backend
Once the PID is known, the memory context tree can be logged.
Example:
SELECT * FROM pg_log_backend_memory_contexts('24813');SQL output:
-[ RECORD 1 ]------------------+--
pg_log_backend_memory_contexts | t
The returned value t simply indicates that the logging operation was successful.
The detailed information is written to the PostgreSQL log file.
Locating the PostgreSQL Log File
The location of the PostgreSQL log file depends on the installation.
Example command:
pg_lsclusters
Example output:
Ver Cluster Port Status Owner Data directory Log file
18 main 5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log
To view the logs in real time:
tail -f /var/log/postgresql/postgresql-18-main.log
Example Log Output
When the memory context logging function is executed, PostgreSQL produces output similar to the following:
2026-03-12 19:45:16.370 IST [24813] LOG: logging memory contexts of PID 24813
2026-03-12 19:45:16.370 IST [24813] LOG: level: 1; TopMemoryContext: 91264 total in 5 blocks; 7952 free (10 chunks); 83312 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; PgStat Shared Ref Hash: 9264 total in 2 blocks; 712 free (0 chunks); 8552 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; PgStat Shared Ref: 1024 total in 1 blocks; 496 free (1 chunks); 528 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; PgStat Pending: 1024 total in 1 blocks; 520 free (0 chunks); 504 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; smgr relation table: 16384 total in 2 blocks; 4664 free (3 chunks); 11720 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; TopTransactionContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; TransactionAbortContext: 32768 total in 1 blocks; 32528 free (0 chunks); 240 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; Autovacuum Launcher: 8192 total in 1 blocks; 7952 free (1 chunks); 240 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 3; Autovacuum database list: 8192 total in 1 blocks; 7816 free (0 chunks); 376 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; Portal hash: 8192 total in 1 blocks; 616 free (0 chunks); 7576 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; TopPortalContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; Relcache by OID: 16384 total in 2 blocks; 7736 free (3 chunks); 8648 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; CacheMemoryContext: 262144 total in 6 blocks; 118168 free (4 chunks); 143976 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; LOCALLOCK hash: 8192 total in 1 blocks; 616 free (0 chunks); 7576 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; WAL record construction: 50200 total in 2 blocks; 6400 free (0 chunks); 43800 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; PrivateRefCount: 8192 total in 1 blocks; 2672 free (0 chunks); 5520 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; MdSmgr: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; GUCMemoryContext: 32768 total in 3 blocks; 13512 free (5 chunks); 19256 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 3; GUC hash table: 32768 total in 3 blocks; 10664 free (6 chunks); 22104 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; Timezones: 104112 total in 2 blocks; 2672 free (0 chunks); 101440 used
2026-03-12 19:45:16.370 IST [24813] LOG: level: 2; ErrorContext: 8192 total in 1 blocks; 7952 free (4 chunks); 240 used
2026-03-12 19:45:16.370 IST [24813] LOG: Grand total: 723832 bytes in 39 blocks; 257488 free (37 chunks); 466344 used
Understanding the Log Output
Each memory context entry contains detailed information.
Example line:
CacheMemoryContext: 262144 total in 6 blocks; 118168 free; 143976 used
Explanation:
- Total - Total memory allocated for the context
- Blocks - Number of memory blocks allocated
- Free - Memory currently unused
- Used - Memory actively used
This allows administrators to understand how memory is distributed inside a backend process.
Understanding Memory Context Levels
The log also shows the hierarchical level of each memory context.
Example:
level: 1; TopMemoryContext
level: 2; CacheMemoryContext
level: 3; GUC hash table
This indicates the parent-child relationship of contexts.
TopMemoryContext
+-- CacheMemoryContext
+-- GUC hash table
This hierarchy helps developers understand how memory flows inside PostgreSQL subsystems.
Key Memory Contexts Observed
Some important contexts commonly observed include:
- TopMemoryContext: The root context that contains all other memory contexts.
- CacheMemoryContext: is used for system catalog caches and metadata structures.
- TopTransactionContext: Stores memory allocations related to the current transaction.
- PortalContext: Handles memory used by query portals and cursors.
- GUCMemoryContext: Stores configuration variables and runtime settings.
WAL record construction: Memory used while constructing write-ahead log records.
Interpreting the Grand Total
At the end of the output:
Grand total: 723832 bytes in 39 blocks; 257488 free; 466344 used
This summarizes the total memory allocated by the backend process.
In this example:
- Total allocated memory: 723 KB
- Used memory: 466 KB
- Free memory: 257 KB
This helps estimate the memory footprint of a backend process.
Benefits for Database Administrators
This function is extremely useful in production environments.
Memory Leak Investigation
If a backend process continuously grows in memory usage, administrators can inspect its memory contexts to identify which subsystem is consuming memory.
Query Execution Analysis
When large queries run, memory contexts reveal how much memory is used by components such as sorting, hashing, and execution nodes.
Debugging Long Running Sessions
If a session behaves abnormally or consumes excessive memory, its memory context tree can be inspected without stopping the database server.
Monitoring Background Workers
Processes such as:
- autovacuum workers
- logical replication workers
- background workers
can also be inspected using their PIDs.
The pg_log_backend_memory_contexts() function provides a powerful mechanism for inspecting the internal memory usage of PostgreSQL backend processes. By logging the complete memory context tree for a specific PID, administrators and developers gain deep visibility into how PostgreSQL allocates and manages memory.
This capability is particularly useful for diagnosing performance issues, analyzing memory consumption, and understanding PostgreSQL internals in real production environments. When combined with views like pg_stat_activity and careful log analysis, this function becomes a valuable tool for advanced PostgreSQL troubleshooting and database performance tuning.