3 ms·
Hacks like this work at first, but long running transactions and postgres don't do well together. After a few weeks of running on a multi-TB table, you'll find
by londons_explore 7y ago
Hacks like this work at first, but long running transactions and postgres don't do well together.
After a few weeks of running on a multi-TB table, you'll find the dead tuples massively outnumber the live tuples, and database performance is dropping faster than the Vacuum can keep up. Vacuum is inherently single-threaded, while your processes making dead tuples as part of queries are multi-threaded, so it's obviously the vacuum that fails first if most queries are long running and touch most rows. Your statistics will get old because they're also generated by the vacuum process, making everything yet slower.
Even if you can live with gradually dropping performance, eventually the whole thing will fail when your txids wrap around and the whole database goes read-only.
- ansible 7y agoLong running transactions? I didn't think the article implied doing something like that. The job state changes shouldn't take long to execute.