3 ms·
"Letting the database handle it" for truly large tables can have some potentially tricky issues. The most common I've seen relate to replication. If your datab
by simonw 4y ago
"Letting the database handle it" for truly large tables can have some potentially tricky issues.
The most common I've seen relate to replication. If your database has replicas and uses row-based replication (as opposed to statement-based replication) running "update huge_table set title = upper(title)" could create a billion updated rows, which then need to be copied to all of those replicas. This can cause big problems.
In those cases, it's better to update 1,000 rows at a time using some kind of batching mechanism, similar to the one described in the article.
- jaggederest 4y agoI agree, but I would do the batching in the database rather than in a job, using something like a function that counts up or pg_sleep between queries with a subset of the range of existing rows.
- whakim 4y agoRight, but how do you do this concurrently? This sounds like you plan to sequentially execute these batches, which could be painfully slow.
- jaggederest 4y agoYou can either limit the load on the database by doing it "painfully slow", or run it all at once, there's no third option. The batches can be arbitrarily large per-query - 5000; 50,000; 500,000 rows per sequential query; depending on how much your database can handle at once without being unacceptably overloaded. There's zero need for concurrency in this context - let the database handle the concurrency internally to the storage engine, and just specify the logical batch size. N.B. postgresql can use parallel execution of workers in batches above a certain size, Depending On Your Settings™ and version
- whakim 4y agoI have found that in most real-world cases if you parallelize SQL commands you can significantly increase CPU utilization and thus database throughput.