Re: Postgres 8.3 HOT and non-persistent xids

On Dec 20, 2007 3:44 AM, Mike C <smith(dot)not(dot)western(at)gmail(dot)com> wrote:
> Hi,
>
> Can someone clarify HOT updates for me (and perhaps put more detail
> into the docs?). Is this statement correct: the HOT technique is used
> on *any* table so long as no indexed column is affected.
>

Its partially correct. HOT is used on system and user tables. "No index
column change" is a necessary but not sufficient condition for HOT update.
There must be enough free space in the same block where the old tuple
exists. Though we hope that the system will stabilize in terms of availability
of free space in the blocks, it might be worthy to leave free space of at least
one tuple size by using appropriate fill factor at the table creation time.

> create table T (A int, B int);
> create index TB on T (B);
> insert into T (A,B) Values (1,2);
>
> So if I do an update that is identical to the existing row, nothing changes?
> update T set A=1, B=2 where A=1;
>

HOT update *is not* update-in-place. So every update, HOT or COLD, would
generate a new version of the row. The power of HOT comes when the index
column is not changed. This allows us to skip index inserts for the new version
(thus preventing index bloats). Its also far easier to vacuum the dead
HOT tuples
without running VACUUM or VACUUM FULL. This gives us the ability to prevent
heap bloats.

> If I change the non-indexed field, A, then HOT applies and no new tuple needed?
> update T set A=2, B=2 where A=1;
>

HOT applies, but new tuple is needed as described above.

> If I change the indexed field, B, then HOT doesn't apply and a new
> tuple is needed?
>
> update T set A=2,B=3 where A=2;

Right.

>
> Actually, what actually happens when you get an update with redundant
> information, e.g.
>
> update T set A=2,B=4 where A=2;
>
> The value of A hasn't changed, does postgres still write the value?
>

Yes. Every update generates a new version of the row.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com