Hacker News Reader

I have a couple of read-heavy >2TB Postgres instances, document-oriented too. You're right that bulk updates can be too slow. Too many times I end up doing the updates incremental (in batches) or even use COPY.

View on HN

You also want to avoid long transactions to avoid lock contention. Every statement is also a transaction, so chunking it up helps a lot on busy databases.

Avoiding long transactions is also about preventing the transaction from holding back vacuuming. Postgres will not vacuum tuples that are still visible to old transactions (visible as the backend_xmin in the pg_stat_activity table).

Long transactions can also cause surprising locks, because many locks taken persist to the end of the transaction, even if the transaction is no longer doing anything. This can block DDL operations as well as things like REINDEX.

Reply on news.ycombinator.com