> Now the question... why would you do that instead of doing everything in one big transaction on Oracle? I guess
performanceis one reason. Oracle's (at least with 8/8i) performance seems to tank very quickly on deletes as you
increasethe number of records you delete in a single transaction. The other (at least with my understanding of Oracle
internals)is that using smaller transactions will mean less rollback segment space used which reduces the likelyhood of
yourtransaction getting killed due to Oracle running out of rollback space on a database that has heavy usage.
Running out of rollback segments is the answer in our case. It happened
more than once...
> Since Postgresql's MVCC system doesn't require rollback segments I guess the performance question is the more
importantof the two. If there could be a performance gain in Postgresql with "delete from X where Y limit Z" vs
"deletefrom X where Y in (some subselect limit Z)" I'd think Csaba suggestion has some merit.
But postgres has in turn the problem of not functional vacuum when you
have long running transactions. That is a problem for heavily recycled
tables like queue tables.
Now recently I have solved the vacuum problem by regularly CLUSTER-ing
our most heavily used queue table, so long running transactions are not
anymore such a huge problem for us, but we still have the case of some
user triggered operations which time out on the web before finishing on
the DB. Some of those would make perfect sense to be done partially and
the user then can restart the operation from where it left... now we
often have 5 minutes of updates rolled back and leaving nothing useful
but only dead rows.
And yes, not everything we do must be perfectly transactional...
sometimes the partial work worth more than partial work rolled back.
Cheers,
Csaba.