Best unofficial Apache Server developers community |
Are long high volumn transactions bad
|
Hallo, I am writing a database application that does alot of inserts and updates with fake To not do tonnes of network roundtrips I'm batching inserts and updates in one transaction with Having big transactions should be good for WAL, because it can flush big chunks and doesn't have to flush for mini transactions. 1.) I can only see positiv effects of a big transaction. But I often read that they are bad. Why could they be bad in my use case? 2.) Is the checking for conflicts so expensive when the
posted via StackOverflow
|
2 Answers
|
|
The real issues here are two fold. The first possible problem is bloat. Large transactions can result in a lot of dead tuples showing up at once. The other possible problem is from long running transactions. As long as a long running transaction is running, the tables it's touching can't be vacuumed so can collect lots of dead tuples as well. I'd say just use check_postgresql.pl to check for bloating issues. As long as you don't see a lot of table bloat after your long transactions you're ok. |
|
|
1) Manual says that it is good: http://www.postgresql.org/docs/current/interactive/populate.html I can recommend also to Use COPY, Remove Indexes (but first test), Increase maintenance_work_mem, Increase checkpoint_segments, Run ANALYZE (or VACUUM ANALYZE) Afterwards. I will not recommed if you are not sure: Remove Foreign Key Constraints, Disable WAL archival and streaming replication. 2) Always data are merged on commit but there is no checks, data are just written. Read again: http://www.postgresql.org/docs/current/interactive/transaction-iso.html If your inserts/updates does not depend on other inserts/updates you don't need "wholly consistent view". You may use read committed and transaction will never fail. |
