RE: delete then insert

From: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Sent: Friday, January 17, 2020 1:51 PM
To: Ken Benson <Ken(at)infowerks(dot)com>
Cc: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Re: delete then insert

On Fri, Jan 17, 2020 at 1:59 PM Ken Benson <Ken(at)infowerks(dot)com<mailto:Ken(at)infowerks(dot)com>> wrote:
From: Ron <ronljohnsonjr(at)gmail(dot)com<mailto:ronljohnsonjr(at)gmail(dot)com>>
Sent: Friday, January 17, 2020 12:12 PM
To: pgsql-admin(at)lists(dot)postgresql(dot)org<mailto:pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Re: delete then insert

On 1/17/20 2:03 PM, Ken Benson wrote:

So – I THINK I know – that when a row is deleted from a table – the row is not actually removed from the table – but, merely marked as deleted, thus becoming a dead tuple.
AUTOVACUUM – takes care of the process of removing these dead tuples.

My question. If – an insert occurs to that same table before autovacuum comes along, does that inserted record use a dead tuple? Or – does the insert result in an actual added row?

If there's an open transaction which is still looking at the now-deleted record, then over-writing that area of the file would be a Bad Thing.
So – the answer is – NO – the dead tuple won’t be used until is has been reclaimed by vacuum – and that it doesn’t because it would be a bad thing, right
Vacuum takes care of dead tuple management. Auto-Vacuum is just a process whereby the system decides when it should run vacuum for you.

"The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse."

Vacuum is the only thing that marks space available for use, everything else relies on those marks as opposed to everyone checking for themselves.
[Ken.B]
That’s what we thought – but wanted to make certain. Thank you!

David J.