How can I optimise an Update on a large table?
Is the process I/O bound (there is lots of disk activity) or CPU bound (which is possible if the functions are complex)? It is most likely the former and if so then if there are indexes covering those columns it is usually better to drop them before operations that update every row and recreate them afterwards (don't drop the PK and/or clustered index though). "Usually" is an important word here: check it out in a test environment to confirm for your data.


  Would it be faster to create a new table rather then update it?


Probably not, unless the new table is on physically separate media so reading from the old and writing to the new does not cause head-thrashing on the physical disks.


  Would it be better to create a loop and update 100000 records each time and commit after each iteration?


If you and your users can cope with the data being partially updated at any given time then this (probably with a smaller number of rows then 100,000) gives you the advantage of not blocking your application for other uses as you don't have an update lock on the entire table for the length of time it takes to update all the rows. I've not used postgres much but assuming it behaves similarly to MS SQL Server (in simple recovery mode) using smaller updates inside explicit transactions can greatly reduce the amount of log file growth but is unlikely to significantly affect overall run-time.

If running the update in batches and allowing normal operations to continue on the application then obviously you shouldn't follow the first recommendation to drop indexes at the start &amp; recreate them at the end - those indexes are likely to be essential to reasonable performance of the application.