Are long high volume transactions bad

Hallo,

I am writing a database application that does a lot of inserts and updates with fake serialisable isolation level (snapshot isolation).

To not do tonnes of network roundtrips I'm batching inserts and updates in one transaction with PreparedStatements. They should fail very seldom because the inserts are prechecked and nearly conflict free to other transactions, so rollbacks don't occur often.

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 positive 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 local snapshots are merged back into the real database? The database will have to compare all write sets of possible conflicts (parallel transaction). Or does it do some high speed shortcut? Or is that quite cheap anyways?

[EDIT] It might be interesting if someone could bring some clarity into how a snapshot isolation database checks if transaction, which have overlapping parts on the timeline, are checked for disjunct write sets. Because that's what fake serializable开发者_运维问答 isolation level is all about.

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.