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
behaviorsomehow involved. But if the toast table involves an index, then maybe all of this gets back to just the
indexeslike 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
thememory 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
maybegrowing at the same rate, it's just that this loop goes slower against real tables than temp tables. For example,
Isee similar total memory growth by the time this reaches 100,000 loops for either temp or non-temp tables, the temp
versionjust reaches that point a lot more quickly (which makes sense).
Thanks!
Nick