Thread

Possible opportunity to reset LocalBufferContext memory on ResetTempTableNamespace

Haiyang Li <mohen.lhy@alibaba-inc.com> — 2025-09-30T08:22:41Z

Hi all,
I've been experimenting with reusing a long-lived backend connection by
sending a DISCARD ALL command after it has been running for a while,
as part of an approach similar to a connection pool.
During this process, I noticed that LocalBufferContext tends to grow
larger and larger over time.
After some investigation, I found that LocalBufferContext is used
exclusively for temporary table buffers. However, after
ResetTempTableNamespace is called, it doesn't reset LocalBufferContext,
even though the corresponding temporary tables have already been dropped
in that backend.
While the allocated space in LocalBufferContext can indeed be reused by
subsequent temp table usage, the issue is that the memory can only grow
over time and will never shrink, up to its upper bound.
Here’s a quick reproduction workflow:
```
select
 name,
 sum(total_bytes)
from
 pg_get_backend_memory_contexts()
where
 name in (
 'LocalBufferContext',
 'Local Buffer Lookup Table'
 )
group by
 name
order by
 sum(total_bytes) desc;
 name | sum
------+-----
(0 rows)
create temp table t_temp(a int, b int);
insert into t_temp select i , i from generate_series(1,100000) i;
select
 name,
 sum(total_bytes)
from
 pg_get_backend_memory_contexts()
where
 name in (
 'LocalBufferContext',
 'Local Buffer Lookup Table'
 )
group by
 name
order by
 sum(total_bytes) desc;
 name | sum
---------------------------+---------
 LocalBufferContext | 4092224
 Local Buffer Lookup Table | 65536
(2 rows)
discard all;
create temp table t_temp(a int, b int);
insert into t_temp select i , i from generate_series(1,200000) i;
select
 name,
 sum(total_bytes)
from
 pg_get_backend_memory_contexts()
where
 name in (
 'LocalBufferContext',
 'Local Buffer Lookup Table'
 )
group by
 name
order by
 sum(total_bytes) desc;
 name | sum
---------------------------+---------
 LocalBufferContext | 8425920
 Local Buffer Lookup Table | 65536
(2 rows)
```
It would make sense to provide a way to reset LocalBufferContext (and
related memory contexts). I think ResetTempTableNamespace could be a
suitable place to trigger this reset.
If there's interest, I can work on a patch/prototype.
Any thoughts?
—
Regards
Haiyang Li