In PostgreSQL, the memory consumed by a single connection running a sequence of four SQL statements is not a simple cumulative total. Instead, it is determined by a combination of fixed process overhead, local memory allocations, and shared memory mappings.
Table of Contents
Key Takeaways for Connection Memory
- Process-per-Connection → PostgreSQL starts (forks) a new backend process for every client connection, giving each session its own private virtual address space.
- Shared Memory Mapping → Every process maps the entire
shared_bufferspool, though this memory is physically shared across all connections and exists only once in physical RAM. - Work Memory (work_mem) → This is the most volatile component, used for sorting and hashing; it is allocated per-operation, meaning one complex query can use multiple chunks of this memory.
- External Spilling → If a query requires more RAM than the
work_memlimit allows, PostgreSQL “spills” the data to temporary disk files to protect system stability. - Serial Reuse → Because SQL statements in one connection run one after another, memory for one query’s sort or join is typically released or reused before the next query begins.
1. Architecture: One Connection Equals One Process
Whenever a client establishes a connection, the PostgreSQL supervisor process forks a new backend process dedicated to that session. This means every connection has its own private virtual address space.
2. Components of Memory Consumption
The memory footprint of this backend process consists of two primary parts:
- Shared Memory Mapping: This includes the
shared_buffersused to cache data blocks. While operating system tools (likepsortop) show this as part of the process’s memory, it is physically shared across all connections and exists only once in the server’s RAM. - Local (Session) Memory: This is RAM allocated specifically for the tasks required by the connection’s current query. The most significant parameter here is
work_mem.
3. Memory Dynamics While Running Four SQLs
The actual RAM usage fluctuates based on the complexity and execution plan of each query, rather than the total number of queries:
- On-Demand Allocation: Memory for operations like sorting or hash tables is allocated as needed during execution. Once a query completes, this memory is typically released back to the process’s internal pool or the operating system.
- The Multiplier Effect of
work_mem: Thework_memsetting is a limit for each individual operation (such as a sort or hash join) within a query. A single complex SQL statement containing three separate sort operations could potentially consume three times thework_memvalue simultaneously. - Serial Execution: Within a single connection, SQL statements are executed serially (one after another). Because memory for the first query is usually freed before the second begins, the peak memory usage for the entire session is generally determined by the single most complex query in the sequence, not the sum of all four.
Method 1: Auditing Precision with Backend Memory Contexts
The most accurate way to see exactly how many bytes a connection is using is to use the pg_log_backend_memory_contexts() function. This command forces a specific backend process to dump its entire internal memory breakdown—including used and free bytes—into the PostgreSQL server log.
Command Syntax: First, identify the PID (Process ID) of the target connection from the pg_stat_activity view. Then, execute the following: SELECT pg_log_backend_memory_contexts(target_pid);.
Output Expectation: Check the PostgreSQL server log for the entry labeled “Grand total”. This will show the total bytes in blocks, the free bytes, and the used bytes. The “used” figure represents the actual local memory currently held by that specific connection. For a deeper look at the parameters controlling this, see Essential PostgreSQL configuration parameters for better performance.
Method 2: Monitoring at the OS Level with ps
Since every connection is a standard Linux process, you can use the ps command to view memory usage from the kernel’s perspective. This is a quick way to see the Resident Set Size (RSS) of every connection.
Command Syntax: ps auxww | grep ^postgres.
Understanding the Output: The RSS column shows the physical memory used. However, be aware that this number includes the shared memory mapping. Because the shared buffers are mapped to every process, adding up the RSS of 100 connections will report far more memory than is physically installed on the server. To understand the relationship between these processes, refer to Understanding PostgreSQL’s memory architecture.
See also: Mastering the Linux Command Line — Your Complete Free Training Guide
Method 3: Analyzing Specific Query Demands (EXPLAIN ANALYZE)
If one of your four SQLs is particularly heavy (e.g., a large ORDER BY or JOIN), you can use EXPLAIN ANALYZE to see its peak memory demand during execution.
Command Example: EXPLAIN ANALYZE SELECT * FROM table ORDER BY column;.
Output Details: The output will show a “Sort Method” or “Hash” node. If the operation fit in RAM, it will state the Memory used (e.g., “Memory: 74kB”). If the query exceeded work_mem, it will display “External sort Disk”. If you find specific queries are hitting these limits, check the guide on Troubleshooting slow queries in PostgreSQL.
Step-by-Step Process to Calculate Total Connection Load
- Baseline Overhead: Assume a baseline of approximately 5MB to 10MB of RAM for the basic backend process infrastructure.
- Verify Serial Execution: Confirm that your application executes the four SQLs serially within the same session; memory is usually reused between them rather than being held indefinitely.
- Identify the Peak Query: Use EXPLAIN to find which of the four queries has the highest number of sort or hash operations.
- Calculate Peak Potential: The theoretical peak usage is roughly:
Baseline + (work_mem * active_operations). - Trigger Audit: While the most complex query is running, use Method 1 to capture the real-time peak in the logs.
- Monitor Temp Usage: If queries run long, check
pg_stat_databasefor increases intemp_bytesto see if memory settings are forcing disk usage.
PostgreSQL Connection Memory Summary
| Component | Scope | Typical Impact | Behavior Across 4 SQLs |
|---|---|---|---|
shared_buffers | Cluster-wide | 25% of RAM | Constant; shared by all sessions. |
work_mem | Per Operation | 4MB (Default) | Reused or released after each SQL. |
temp_buffers | Per Session | 8MB (Default) | Active only if temp tables are used. |
max_stack_depth | Per Process | 2MB (Default) | Limits depth of recursion. |
FAQs
Is memory cumulative across the four SQL runs? No. PostgreSQL executes SQLs serially in a connection. Memory used for a sort in the first query is typically freed or returned to the process’s local pool before the second query starts.
Can one complex SQL use more than the work_mem limit? Yes. work_mem is applied to each sort or hash operation. A single query with multiple joins and sorts can simultaneously use several times the configured value.
Why does my connection memory stay high after a query finishes? PostgreSQL often keeps memory in its internal Memory Contexts to avoid the overhead of frequent malloc calls to the operating system. The memory isn’t “leaking”; it’s being cached for the next query in that session.
