> Now the question... why would you do that instead of doing everything in one big transaction on Oracle? I guess performance is one reason. Oracle's (at least with 8/8i) performance seems to tank very quickly on deletes as you increase the 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 your transaction 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 important of the two. If there could be a performance gain in Postgresql with "delete from X where Y limit Z" vs "delete from 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.
