Re: vacuum locking

Tom Lane writes:
> ... if all tuples are the same size, and if you never have any

Incorrect. If the tuples smaller, Oracle does the right thing. If
there's enough space in the page, it shifts the tuples to make room.
That's what pctfree, pctused and pctincrease allow you to control.
It's all in memory so its fast, and I don't think it has to update any
indices.

> transactions that touch enough tuples to overflow your undo segment

That's easily configured, and hasn't been a problem in the databases
I've managed.

> (or even just sit there for a long time, preventing you from recycling

That's probably bad software or a batch system--which is tuned
differently. Any OLTP system has to be able to partition its problems
to keep transactions short and small. If it doesn't, it will not be
usable.

> undo-log space; this is the dual of the VACUUM-can't-reclaim-dead-tuple
> problem). And a few other problems that any Oracle DBA can tell you
> about. I prefer our system.

Oracle seems to make the assumption that data changes, which is why it
manages free space within each page as well as within free lists. The
database will be bigger but you get much better performance on DML.
It is very good at caching so reads are fast.

Postgres seems to make the assumption that updates and deletes are
rare. A delete/insert policy for updates means that a highly indexed
table requires lots of disk I/O when the update happens and the
concomitant garbage collection when vacuum runs. But then MVCC makes
the assumption that there's lots of DML. I don't understand the
philosphical split here.

I guess I don't understand what application profiles/statistics makes
you prefer Postgres' approach over Oracle's.

> The increased I/O activity is certainly to be expected, but what I find
> striking here is that you've got substantial swap activity in the second
> trace. What is causing that? Not VACUUM I don't think. It doesn't have
> any huge memory demand. But swapping out processes could account for
> the perceived slowdown in interactive response.

The box is a bit memory starved, and we'll be addressing that
shortly. I don't think it accounts for 3 minute queries, but perhaps
it might. vacuum_mem is 32mb, btw.

Rob