On Tue, May 3, 2016 at 5:51 AM, hari.prasath <hari.pras...@zohocorp.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.‚Äč

Reply via email to