Message-ID: <1512484854.2706.3.camel@cybertec.at>
Subject: Re: vacuum after truncate
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>, PostgreSQL mailing
 lists <pgsql-performance@postgresql.org>
Date: Tue, 05 Dec 2017 15:40:54 +0100
In-Reply-To: 
 <CA+t6e1=Xid-2j562k3J=-WKsYARt0STXBFT9ephcvMUo9NAQbQ@mail.gmail.com>
References: 
	<CA+t6e1=Xid-2j562k3J=-WKsYARt0STXBFT9ephcvMUo9NAQbQ@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

Mariel Cherkassky wrote:
> Hi,
> I have a big function that includes many truncates on different tables.
> In the documentation is is written that truncates creates a new file
> and resign the old filenode to the new filenode and the old file
> (old data of the table) is deleted in commit. 
> 
> In order to execute my function I run psql -d 'aa' -U 'bb' -C "select function()";
> 
> I have a few questions about it ?
> 
> 1.When I start the function, it means that the whole function is one big transaction right ?

Right.

> 2..Because the truncates I preform are part of a transaction it means that only at the end
>   of the transaction that space will be freed ? Which mean only when the function is finished?

Exactly.  The old file has to be retained, because there could be a ROLLBACK.

> 3..Does running vacuum at the end of the function on the tables that were truncated and
>   then populated with data will have any impact or is it better just to analyze them ?

For up-to-date statistics, ANALYZE is enough.
If you want to set hint bits so that the first reader doesn't have to do it,
VACUUM will help.  But that is not necessary.

Yours,
Laurenz Albe