Re: vacuum locking

Rob Nagler <nagler(at)bivio(dot)biz> writes:

> 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.

Note that pctfree/pctused are a big performance drain on the usual case. Try
setting them to 0/100 on a table that doesn't get updates (like a many-many
relation table) and see how much faster it is to insert and scan.

> > 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.

Judging by the number of FAQ lists out there that explain various quirks of
rollback segment configuration I wouldn't say it's so easily configured.

> > (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.

Both DSS style and OLTP style databases can be accomodated with rollback
segments though it seems to me that DSS style databases lose most of the
advantage of rollback segments and optimistic commit.

The biggest problem is on systems where there's a combination of both users.
You need tremendous rollback segments to deal with the huge volume of oltp
transactions that can occur during a single DSS query. And the DSS query
performance is terrible as it has to check the rollback segments for a large
portion of the blocks it reads.

> Oracle seems to make the assumption that data changes,

Arguably it's the other way around. Postgres's approach wins whenever most of
the tuples in a table have been updated, in that case it just has to scan the
whole table ignoring old records not visible to the transaction. Oracle has to
consult the rollback segment for any recently updated tuple. Oracle's wins in
the case where most of the tuples haven't changed so it can just scan the
table without consulting lots of rollback segments.

--
greg