4 ms·
Just out of curiosity, what do you find to be long running? Five minutes, five days? Depends on the volume of other transactions happening simultaneously?
by mjcl 8y ago
Just out of curiosity, what do you find to be long running? Five minutes, five days? Depends on the volume of other transactions happening simultaneously?
- pilif 8y agoAs it's the case very often in our business, the answer is: "it depends". It depends on the write load, the read load, and the write patterns. Think about it this way: for the duration of any transaction, Postgres is basically in append-only mode. If you have cache tables that normally get rewritten entirely, no row will be deleted during the duration of any transaction in the whole cluster (so this even affects databases other than the one the transaction is connected to). In our case, we run some heavy import jobs daily which causes transaction lifetimes in hours, depending on the job. This isn't an issue, or rather, it's an issue, you can throw hardware at in order to circumvent it. The transaction I was referring to in my earlier comment had a lifetime in days and nearly brought down the whole system.
- Arnt 8y agoPractically speaking: A long-running transaction is one that issues BEGIN, locks some rows, then blocks on other network input, and finally COMMIT. Once a day/week/month that input arrives very slowly and the COMMIT happens minutes or hours after the BEGIN.