Re: Frequent Update Project: Design Overview ofHOTUpdates

On Fri, 2006-11-17 at 09:25 -0600, Kevin Grittner wrote:

> Like Hannu, we do use conditional indexes with high updates on columns
> in the WHERE clause, although these columns are not part of the index
> sequence. For example, we have a receivables table which contains a
> balance due. For audit trail purposes these rows remain for many
> years after the balance hits zero, but they're not something you want
> to look at when someone is standing at the counter with their
> checkbook. We index by name where the balance is non-zero. The
> balance is updated frequently, with most eventually hitting zero.
> (The reason for the frequent updates is that the receivable is
> maintained by triggers from the supporting assessment detail, so a
> receivable will be initially added with a zero balance and may
> immediately be updated dozens of times as the assessment detail is
> added.) Infrequently, the balance may hit zero and subsequently
> become non-zero again.

In that case its clear that you would remove the index WHERE clause and
utilise the HOT option. That would gain you considerably on most of the
UPDATEs at the slight expense of holding some additional rows in the
index. If that is a problem, move the balance==zero rows to an older
history table, perhaps using partitioning to keep them together. That
way you've reduced the size of all indexes without using partials.

So, HOT changes the way we'd think about indexes, yet would provide
considerable benefit in this case.

> I hope this is helpful.

I think it has been very helpful. I guess I'm interested in your overall
assessment of whether HOT would be beneficial for you, or not, given
what's been discussed. Remembering that you can turn it on for specific
tables at least as easily as you can set up autovacuum for them.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com