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.