Re: delete then insert

On Fri, Jan 17, 2020 at 1:59 PM Ken Benson <Ken(at)infowerks(dot)com> wrote:

> *From:* Ron <ronljohnsonjr(at)gmail(dot)com>
> *Sent:* Friday, January 17, 2020 12:12 PM
> *To:* 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.

David J.