Re: [HACKERS] full table delete query

[David G. Johnston]

On Tue, May 3, 2016 at 5:51 AM, hari.prasath 
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.​

[HACKERS] full table delete query

[hari.prasath]

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









cheers

- Harry