When troubleshooting YSQL memory, there are really three different questions:
- 1. How much memory is being used?
2. Which YSQL backend is consuming it?
3. Why is that backend consuming so much memory?
Two previous YugabyteDB Tips help answer the first two questions.
Start here when you want to understand how much memory a YSQL session or server is using. This tip covers session-level memory, PostgreSQL memory contexts, backend RSS, and overall server memory pressure.
Once you know memory usage is high, use VmRSS and VmHWM to identify YSQL backend processes that are currently consuming significant memory or that experienced a large memory spike earlier in their lifetime.
Use VmHWM to Catch “Memory-Heavy” YSQL Backends (and Help Prevent OOMs)
Once you have identified an interesting backend PID, the next question is:
- Why is this particular backend using so much memory?
That is where these two diagnostic functions become especially useful:
SELECT pg_log_backend_memory_contexts(pid);
SELECT yb_log_backend_heap_snapshot(pid);
Used together, they provide complementary views of memory associated with a YSQL backend.
A Simple YSQL Memory Troubleshooting Flow
The two previous tips and this one fit together as a simple YSQL memory troubleshooting workflow:
Is there memory pressure?
|
| Check Session and Server Memory from ysqlsh
v
Which backend is interesting?
|
| VmRSS / VmHWM
v
Why is that backend large?
|
| pg_log_backend_memory_contexts(pid)
| yb_log_backend_heap_snapshot(pid)
v
Catalog cache?
Relcache?
Cached plans?
Executor memory?
PGGate?
Native heap?
Allocator footprint?
The two previous YugabyteDB Tips help you find the backend worth investigating.
This tip concentrates on explaining where that backend’s memory is going.
The Two Diagnostic Functions
The important point is that these functions do not show exactly the same thing:
- ●
pg_log_backend_memory_contexts()gives you the PostgreSQL memory-context view. - ●
yb_log_backend_heap_snapshot()gives you another view of the broader process heap.
Why RSS Alone Is Not Enough
Suppose Linux shows a YSQL backend consuming approximately:
100 MB RSS
That tells you how much resident memory the operating system currently associates with the process.
It does not mean PostgreSQL has exactly 100 MB of active PostgreSQL memory-context allocations.
A diagnostic investigation might reveal measurements similar to:
PostgreSQL contexts allocated: ~38 MB
PostgreSQL contexts actually used: ~32 MB
PGGate allocations: ~6 MB
tcmalloc current allocations: ~44 MB
tcmalloc physical footprint: ~82 MB
Backend RSS: ~100 MB
The important lesson is:
PostgreSQL MemoryContext total
!=
tcmalloc physical footprint
!=
process RSS
This is one reason using both diagnostic functions can be so useful.
Demo
Let’s create a simple workload that gives a YSQL backend some catalog objects, partitions, indexes, and prepared statements to work with.
The goal is not to deliberately exhaust memory… We simply want enough backend state to make the diagnostic output interesting.
Step 1: Create the Demo Schema
DROP SCHEMA IF EXISTS memory_diag_demo CASCADE;
CREATE SCHEMA memory_diag_demo;
SET search_path = memory_diag_demo, public;
Create a customer table:
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
customer_name TEXT NOT NULL,
region TEXT NOT NULL
);
Now create a partitioned orders table:
CREATE TABLE orders (
order_id BIGINT NOT NULL,
customer_id BIGINT NOT NULL,
order_ts TIMESTAMPTZ NOT NULL,
status TEXT NOT NULL,
amount NUMERIC(12,2) NOT NULL,
PRIMARY KEY (customer_id, order_id)
)
PARTITION BY RANGE (customer_id);
Create 20 partitions:
DO $$
DECLARE
i INTEGER;
low_value INTEGER;
high_value INTEGER;
BEGIN
FOR i IN 0..19 LOOP
low_value := (i * 1000) + 1;
high_value := ((i + 1) * 1000) + 1;
EXECUTE format(
'CREATE TABLE memory_diag_demo.orders_p%s
PARTITION OF memory_diag_demo.orders
FOR VALUES FROM (%s) TO (%s)',
i,
low_value,
high_value
);
END LOOP;
END
$$;
Add several indexes:
CREATE INDEX orders_status_idx
ON orders (status);
CREATE INDEX orders_order_ts_idx
ON orders (order_ts);
CREATE INDEX orders_customer_ts_idx
ON orders (customer_id, order_ts);
Step 2: Load Some Data
Insert 20,000 customers:
INSERT INTO customers
SELECT
g,
'Customer ' || g,
CASE g % 4
WHEN 0 THEN 'NORTH'
WHEN 1 THEN 'SOUTH'
WHEN 2 THEN 'EAST'
ELSE 'WEST'
END
FROM generate_series(1, 20000) AS g;
Insert 100,000 orders:
INSERT INTO orders
SELECT
g,
((g - 1) % 20000) + 1,
now() - ((g % 365) * interval '1 day'),
CASE g % 4
WHEN 0 THEN 'NEW'
WHEN 1 THEN 'PROCESSING'
WHEN 2 THEN 'SHIPPED'
ELSE 'COMPLETE'
END,
round((10 + random() * 990)::numeric, 2)
FROM generate_series(1, 100000) AS g;
Verify the row counts:
SELECT count(*) FROM customers;
SELECT count(*) FROM orders;
Step 3: Identify the Demo Backend PID
Keep this ysqlsh session open.
Run:
SELECT pg_backend_pid();
Example:
.pg_backend_pid
----------------
123456
Remember this PID.
Everything we do next in this session contributes to the state associated with this backend.
Step 4: Exercise the Schema
Touch several ranges of the partitioned table:
SELECT count(*)
FROM orders
WHERE customer_id BETWEEN 1 AND 1000;
SELECT count(*)
FROM orders
WHERE customer_id BETWEEN 5001 AND 6000;
SELECT count(*)
FROM orders
WHERE customer_id BETWEEN 10001 AND 11000;
SELECT count(*)
FROM orders
WHERE customer_id BETWEEN 19001 AND 20000;
Now execute a join:
SELECT
c.region,
count(*),
round(sum(o.amount), 2) AS total_amount
FROM orders o
JOIN customers c
ON c.customer_id = o.customer_id
GROUP BY c.region
ORDER BY c.region;
The backend is now interacting with table, partition, index, catalog, and query-planning metadata.
Step 5: Create Prepared Statements
Prepared statements are useful for this demonstration because their cached plans can appear in PostgreSQL memory contexts.
Create 200 named prepared statements:
SELECT format(
'PREPARE mem_stmt_%s(bigint) AS
SELECT count(*)
FROM memory_diag_demo.orders
WHERE customer_id = $1;',
g
)
FROM generate_series(1, 200) AS g
\gexec
Check how many prepared statements exist in this session:
SELECT count(*)
FROM pg_prepared_statements;
Expected:
.count
-------
200
Inspect a few:
SELECT
name,
statement,
prepare_time
FROM pg_prepared_statements
ORDER BY name
LIMIT 5;
pg_prepared_statements shows prepared statements available to the current session. If you are investigating prepared statements, query this view from the session that owns them when possible.
Step 6: Log the PostgreSQL Memory Contexts
Now comes the first deep-dive diagnostic.
For our example PID:
SELECT pg_log_backend_memory_contexts(123456);
If you want to inspect the current backend instead:
SELECT pg_log_backend_memory_contexts(pg_backend_pid());
The SQL commands do not return the full memory-context tree to ysqlsh.
Instead, the details are written to the PostgreSQL server log.
A successful call typically returns:
.pg_log_backend_memory_contexts
--------------------------------
t
Now inspect the PostgreSQL log associated with the target backend.
What Does pg_log_backend_memory_contexts() Show?
PostgreSQL organizes many allocations into a hierarchy of memory contexts.
You might see output conceptually similar to:
LOG: logging memory contexts of PID 123456
LOG: level: ...; TopMemoryContext:
... total; ... free; ... used
LOG: level: ...; CacheMemoryContext:
... total; ... free; ... used
LOG: level: ...; CachedPlanSource:
... total; ... free; ... used
LOG: level: ...; CachedPlanQuery:
... total; ... free; ... used
LOG: Grand total:
... bytes in ... blocks;
... free;
... used
Some particularly interesting context names can include:
CacheMemoryContext
CachedPlanSource
CachedPlanQuery
ExecutorState
PortalContext
TopTransactionContext
MessageContext
RowDescriptionContext
The exact contexts will depend on what that backend has been doing.
Catalog and Relation Cache Accumulation
A long-lived backend may interact with many:
- ● tables
- ● partitions
- ● indexes
- ● types
- ● operators
- ● constraints
As it does, backend-local metadata can accumulate.
This can become particularly interesting for workloads involving:
- ● large numbers of partitions
- ● large schemas
- ● many indexes
- ● many different application workloads
- ● long-lived pooled backends
If a significant portion of PostgreSQL memory is associated with caching, pay close attention to:
- CacheMemoryContext
and its related child contexts.
For example, an investgation with onecustomer revealed a long-lived backend had accumulated substantial catalog/cache state as it encountered different tables, partitions, and indexes over its lifetime.
Prepared Statements and Cached Plans
Another important pattern is a large number of:
- ● CachedPlanSource
- ● CachedPlanQuery
contexts.
These can be associated with prepared statements and cached plans.
Check the session’s prepared statements with:
SELECT count(*)
FROM pg_prepared_statements;
And:
SELECT
name,
statement,
prepare_time
FROM pg_prepared_statements
ORDER BY prepare_time;
If you discover a surprisingly large number of prepared statements, investigate things such as:
- ● Application driver behavior
- ● Prepared-statement naming
- ● Statement lifecycle
- ● Whether statements are deallocated
- ● Connection lifetime
- ● Connection pooling behavior
A customer investigation, for example, found a large number of cached plan sources in the backend being analyzed.
A large number of cached plans does not automatically mean there is a memory leak.
It tells you where to investigate next.
Don’t Assume Every Child Context Will Be Printed
Memory-context output can become extremely large.
When the hierarchy contains a very large number of child contexts, the output may summarize portions of the tree rather than make every individual child easy to inspect.
You might therefore see many entries such as:
- ● CachedPlanSource
- ● CachedPlanQuery
- ● CachedPlanSource
- ● CachedPlanQuery
- ● …
followed by a summary of additional child contexts.
Pay particular attention to:
- Grand total
because the overall total can still reveal that considerably more memory is represented than is obvious from only the individually displayed child contexts.
Step 7: Log the YugabyteDB Heap Snapshot
Now inspect the same backend from the YugabyteDB/native heap perspective:
SELECT yb_log_backend_heap_snapshot(123456);
Or for the current backend:
SELECT yb_log_backend_heap_snapshot(pg_backend_pid());
Again, inspect the PostgreSQL server logs for the diagnostic output.
pg_log_backend_memory_contexts() concentrates on PostgreSQL’s memory-context hierarchy, yb_log_backend_heap_snapshot() provides another view of the underlying process heap. Useful measurements can include areas such as:
- ● tcmalloc current allocations
- ● tcmalloc physical footprint
- ● PGGate allocations
Step 8: Compare the Two Views
This is where the two functions become especially useful together.
Suppose the PostgreSQL memory contexts show:
PostgreSQL contexts allocated: ~38 MB
PostgreSQL contexts actually used: ~32 MB
while the heap diagnostics show:
PGGate allocations: ~6 MB
tcmalloc current allocations: ~44 MB
tcmalloc physical footprint: ~82 MB
and Linux reports:
Backend RSS: ~100 MB
Now you can begin separating:
- ● PostgreSQL MemoryContexts
- ● Native allocations
- ● PGGate
- ● Allocator footprint
- ● Resident process memory
That is much more useful than RSS alone.
What Should I Look For?
A Useful Production Diagnostic Pattern
Once the previous tips identify an interesting PID, capture the session information first:
SELECT
pid,
usename,
datname,
application_name,
client_addr,
state,
backend_start,
xact_start,
query_start,
query
FROM pg_stat_activity
WHERE pid = 123456;
Then capture the PostgreSQL memory contexts:
SELECT pg_log_backend_memory_contexts(123456);
And the YugabyteDB heap snapshot:
SELECT yb_log_backend_heap_snapshot(123456);
Together, you now have:
Linux process memory
+
YSQL session information
+
PostgreSQL MemoryContexts
+
YugabyteDB/native heap information
That gives you a much more complete picture of the backend.
What About YSQL Connection Manager?
YSQL Connection Manager can make backend lifetime particularly relevant.
Application connections can be multiplexed onto physical PostgreSQL backends. As a result, a physical backend can live considerably longer than an individual logical application connection and potentially encounter many different objects or workloads during its lifetime.
If memory appears to grow primarily with backend lifetime, backend recycling may be one area to evaluate.
- ●
ysql_conn_mgr_idle_time - ●
ysql_conn_mgr_server_lifetime
Shortening backend lifetime can periodically replace physical YSQL backends rather than allowing the same processes to live for long periods.
However, this should not be treated as a universal fix.
Recycling backends may reduce accumulated backend-local state, but it can also introduce additional backend creation, connection, and catalog-loading activity.
Don’t immediately change Connection Manager timeouts simply because a backend has high RSS. First use the diagnostic functions to determine what is actually consuming memory. Backend recycling may reduce accumulated state, but it can also mask rather than explain an underlying application, configuration, or database behavior.
Don’t Immediately Call It a Memory Leak
This is one of the most important lessons from this type of investigation.
You may discover a backend using:
50 MB
100 MB
200 MB
or more.
That alone does not establish a memory leak.
The backend may be holding memory associated with:
- ● Catalog metadata
- ● Relation metadata
- ● Prepared statements
- ● Cached plans
- ● Query execution
- ● PGGate
- ● Native allocations
- ● Allocator behavior
A memory leak generally requires stronger evidence that memory continues growing unexpectedly without a reasonable workload-related explanation or without being released when it should be.
These diagnostics help you gather that evidence.
A large YSQL backend is worth investigating, but its RSS alone cannot tell you whether the cause is cached metadata, prepared plans, active query memory, PGGate, allocator behavior, or an actual memory leak.
Be Careful with Diagnostic Logging
These functions can generate substantial diagnostic output, particularly when inspecting a backend with a large memory-context tree or heap.
Use them as targeted troubleshooting tools rather than continuously calling them against every backend.
A good workflow is:
Identify an interesting PID
↓
Capture its current memory measurements
↓
Dump the PostgreSQL memory contexts
↓
Dump the YugabyteDB heap snapshot
↓
Analyze the logs
↓
Repeat later if you need to compare growth
Capturing the same diagnostics at different points in time can also be useful when trying to determine whether memory is steadily accumulating.
Clean Up the Demo
Remove the prepared statements:
DEALLOCATE ALL;
Then remove the demo schema:
DROP SCHEMA memory_diag_demo CASCADE;
Final Takeaway
YSQL memory troubleshooting works best as a progression:
Measure it
↓
Find the backend
↓
Explain the memory
Use the previous tips to determine whether memory pressure exists and identify the YSQL backend worth investigating.
Then use:
SELECT pg_log_backend_memory_contexts(pid);
SELECT yb_log_backend_heap_snapshot(pid);
to dig deeper into that particular backend.
Instead of stopping at:
- “This YSQL backend looks big.”
you can work toward:
- “Now I know what is making it big.”
That distinction can be extremely valuable when diagnosing memory pressure, investigating potential OOM conditions, evaluating long-lived YSQL backends, or deciding whether suspicious memory growth actually looks like a memory leak.
Thanks to Suranjan Kumar, Sr. Director, Engineering at YugabyteDB, for suggesting the idea behind this YugabyteDB Tip!
Have Fun!
After a productive day on site with a client in Fort Lauderdale, FL, it was time for dinner!
We found out that one of the folks we were visiting is a bit of a foodie, so we asked for a recommendation.
He pointed us to this Italian restaurant… and wow, what a great recommendation! The food was fantastic.
I especially enjoyed the fire-baked appetizer bread. 🔥🥖
Sorry, Olive Garden… luv ya, but your breadsticks have officially been knocked down a notch! 😂