Re: sequences and "addval('myseq', value)"

pgsql(at)mohawksoft(dot)com writes:

> This is *NOT* a perfect or elegant solution. There is, however, an
> important problem. How do you maintain a value that is visable to the
> database, but does not incure the cost of a huge number of updates or a
> full table scan? I'm talking about systems that need to take thousands of
> inserts per minute?

Hm, the real question here is how does postgres handle a particular
circumstance, namely a table with a small number of very busy rows taking
hundreds or thousands of updates per second, with few rollbacks.

In the actual case the busy records are always read in short transactions
(single-record reads) so there's a small number of active records at any time,
probably usually 1, at any time.

Currently postgres handles this by creating a new version for every update and
then delaying the cleanup of the old version until a vacuum can be done.

Oracle on the other hand creates new versions and stores the old version in a
rollback segment. So only the most recent version is in the heap. This incurs
cleanup overhead in the critical path of the transaction.

I don't think it makes sense to move to the Oracle-style method of course. But
perhaps there's some way to move the cleanup closer to the end of the
transaction.

I'm thinking something along the lines of the bgwriter vacuuming every block
as it's written out. That forces every update to generate a vacuum of that
same block at some point in the near future.

I think something like this has been proposed before, and the objection was
that it would force index pages to be read in the midst of bgwriter trying to
get pages out.

But if we're talking about a small, very busy table then the index will
presumably be entirely in memory anyways. Perhaps every page in memory could
hold an update count and trigger an immediate vacuum if 2 updates have been
performed on the page while still in the shared pool, or some heuristic like
that.

But I think it would be worth experimenting with things like this. If
something like it could relieve a lot of the vacuum related angst then it
might be worth some other angst about extra i/o in bgwriter.

--
greg