As 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.

Practically 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.