PostgreSQL Locking Revealed

We use it on medium size tables (around ~100M rows and ~100GB each). No problems. The perf boost we get from clustering rows in the right spot on the disk is huge.

We've used it occasionally on a ~400GB table and it's worked perfectly for us too. We run it on EC2 i2.8xlarge instances and it finishes in under 2 hours. But it doesn't use very much CPU or I/O, so we could probably run it just as well on a smaller instance like an i2.2xlarge.

One downside is that you need enough free space on your disk to hold the packed copy of the table, so you need to run it before your disk gets too full. The last couple of times we've waited too long, so we've had to move the data to a larger host before we could repack it.

Yeah, pg_repack works great. I've never had an issue with it.

I'd like to make more use of it but it can't "repack" tables that have gist indices (e.g., postgis spatial indices, etc).

Concurrent updates get interesting when they are based on reads that happened earlier. Then applying updates atomically doesn't buy you anything - you still need to either lock the object so that noone modifies its state between the read and the update (pessimistic locking), or be prepared to abort an update if a concurrent change to the object is detected (optimistic locking), or to append the update instead of overwriting previous state and eventually resolve / fix later, which is not always possible and may be just as hard as getting the locking right. Additionally, being too optimistic about shared resources management may lead to really bad user experience - like being kicked out at the gate because the flight is overbooked or like being fined for overdrawing your debit-card by offline transactions. You just need to pick your poison ;)

Can that be achieved by setting all writes in postgres to isolation level serializable?

I believe that if you set the foreign key constraint as deferred, the lock won't be attempted until commit time (and then only briefly), which should help a lot -- assuming your problem is with long-running transactions that hold locks while they are open.