Re: 7.3.4 on Linux: UPDATE .. foo=foo+1 degrades massivly

On Wed, 21 Apr 2004, Dann Corbit wrote:
> Shouldn't the Database server be the entity that decides when vacuum is
> needed?

when the autovacuum daemon is finished and integrated into the backend, it
will. til then, you, the administrator decide when to run it. Note that
the autovacuum daemon is quite usable as it right now, it's just not
integrated.

> Something is very, very strange about the whole PostgreSQL maintenance
> model.

Yes, it is. It's quite different from other databases in that it lets you
decide if vacuum should happen now or later when there's less load. this
isn't bad or good, just different.

> Oracle uses MVCC and I do not have to UPDATE STATISTICS constantly to
> keep the system from going into the toilet.

That's because Oracle's MVCC works in a completely different way. Oracle
uses roll back segments to maintain it's MVCC while postgresql does it
directly in the data store. That means Oracle is limited to transactions
that can fit in the roll back segment. If you want a bigger transaction,
you have to diddle the size of said rollback segment. no such limit
exists in PostgreSQL except for the size of your data storage.

> Also, I should be able to do an update on every row in a database table
> without causing severe problems. Every other database system I know of
> does not have this problem.

PostgreSQL doesn't either. As long as you vacuum up after yourself. It's
a trade off. Neither better nor worse really, than the limitations of
Oracles Roll back segment. Each database has limits / quirks due to the
design choice. PostgreSQL's limit is that cleaning up after
updates/deletes is handled by vacuuming.

> If I have a million row table with a column called is_current, and I do
> this:
> UPDATE tname SET is_current = 0;
> Horrible things happen.

OK, imagine you're in Oracle and your rollback segment isn't big enough to
hold all this. What happens? The whole operation rolls back. That's not
a good thing either. And Oracle DBAs know that they need a big enough
roll back segment to do such things.

On PostgreSQL, instead of worrying about running out of room in your
rollback segment, you have to worry about running out of room on your
storage subsystem. and you have to vacuum.

Different, not worse, not better, different.

> Just an idea:
> Why not recognize that more rows will be modified than the row setting
> can support and actually break the command into batches internally?

Huh? How would that possibly help? You'd still have to vacuum away the
dead tuples at the end, whether you did them all at once or one at a time.
And since we don't use rollback segments, you don't have to worry about
your transaction being too big for it, just for your data store.