Re: full table delete query

On Tue, May 3, 2016 at 5:51 AM, hari.prasath <hari(dot)prasath(at)zohocorp(dot)com>
wrote:

> Hi all,
> How postgresql handles full table delete in terms of loading the
> full table in these scenarios
>
> consider one big table(tablename: bigtable)
> and the query will be >> delete from bigtable;
>
> 1)which doesn't have any foreign table reference with any other tables
>
> 2)And when this table is referenced by other table
>
>
You should at least consider whether you can use TRUNCATE, especially in #1

An actual delete has to modify every page for the table so it can mark
every row as having been deleted. I don't think it needs to load TOAST
data but am uncertain. I reasonably confident all non-TOASTED data will
end up in buffers.

References would depend on CASCADE behavior but in a restrict mode only FK
resolution triggers will be involved. In most well-design scenarios
indexes are then used instead of the corresponding triggers. So less data
but still likely every row will be read in.

David J.​