Re: Introducing an advanced Frequent Update Optimization

"Simon Riggs" <simon(at)2ndquadrant(dot)com> wrote:

> EnterpriseDB has been running a research project to improve the
> performance of heavily updated tables. We have a number of approaches
> prototyped and we'd like to discuss the best of these now on -hackers
> for community input and patch submission to PostgreSQL core.

I'm very interested in your proposal! NTT is also working for OLTP workloads,
especially on improvements of VACUUM. Maybe we have similar problems.

> - VACUUM can remove heap blocks easily, but performs much worse on
> indexes, making VACUUM a less good solution. We have now been able to
> speed up index VACUUM, but this require us to scan the whole index for
> correct locking. VACUUM scans the whole table, whereas dead rows may
> well be localised. Heap-needs-vacuum-bitmap has been proposed here, but
> no solution currently exists for vacuuming only parts of indexes and so
> proposals for concurrent vacuums are now being considered.
>
> The proposal about to be made takes a more radical approach and
> re-examines the architecture of the heap, to allow us to consider much
> faster designs for heavy UPDATEs. Although initially radical, the
> proposal appears to be fully MVCC correct, crash safe as well as being
> much faster under heavy updates, while approximately neutral in other
> cases with no major downsides.

I made a prototypes of Heap-needs-vacuum-bitmap and per-entry-index-deletion.
The test result shows that it saves vacuuming time. I'm refining and making
it robust now.

We can make use of the present structures with the approach, so I have
thought it is a relatively good direction. However, you seem to propose
a whole new storage engine or on-disk-structure. Do you have any viewpoints
that some kinds of extending-VACUUM approach are not enough?
It would be very nice if you could give us some more background.

> - discuss various other approaches to the problem, and why we are now
> proposing one specific approach and receive "why dont we..." feedback
> and additional ideas (Simon)

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center