On Fri, May 28, 2021, at 7:22 AM, Tom Lane wrote:
> The text column would cause the table to have an associated toast table [1],
> which in turn would have an index. Both of those would be reallocated as
> new files on-disk during TRUNCATE, just like the table proper.
>
> A plausible theory here is that TRUNCATE leaks some storage associated
> with an index's relcache entry, but not any for a plain table.
>
> regards, tom lane
>
> [1] https://www.postgresql.org/docs/current/storage-toast.html
Yeah, I forgot to mention this originally, but I see memory growth against a
"varchar(501)" field, but *not* against a "varchar(500)" field, so I was
wondering if there was some length threshold that triggered something with
toast table behavior somehow involved. But if the toast table involves an
index, then maybe all of this gets back to just the indexes like you say.
And I originally thought this issue was limited to temp tables, but now I'm not
so sure. I seem to be able to reproduce the memory growth against regular
tables (both normal and UNLOGGED) too:
DO $$
DECLARE
i bigint;
BEGIN
CREATE TABLE public.foo (id integer, bar text);
FOR i IN 1..200000000 LOOP
TRUNCATE public.foo;
END LOOP;
END
$$
The memory growth seems to be slower in this case, so maybe that's why I didn't
catch it earlier, but I think it's maybe growing at the same rate, it's just
that this loop goes slower against real tables than temp tables. For example, I
see similar total memory growth by the time this reaches 100,000 loops for
either temp or non-temp tables, the temp version just reaches that point a lot
more quickly (which makes sense).
Thanks!
Nick
