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