Re: 7.3.4 on Linux: UPDATE .. foo=foo+1 degrades massivly
От | scott.marlowe |
---|---|
Тема | Re: 7.3.4 on Linux: UPDATE .. foo=foo+1 degrades massivly |
Дата | |
Msg-id | Pine.LNX.4.33.0404220852220.24463-100000@css120.ihs.com обсуждение исходный текст |
Ответ на | Re: 7.3.4 on Linux: UPDATE .. foo=foo+1 degrades massivly over time ("Dann Corbit" <DCorbit@connx.com>) |
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: