Tangential: does anybody know how dependable is pg_repack[1]? I just discovered it and the promise is great - being able to do a VACUUM FULL, and even move tables around, without locking them and thus without downtime - but I'm not sure what are the risks in using it in production.
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.
Out of curiosity, as you apparently have some real data and real use-cases - have you benchmarked SSD vs disk for your use-case? In particular, do you still see a (comparable, or not) speed-up from better clustering of rows on SSD? (I would guess not, unless you have a lot of fragmentation at less than SSD write-size (typically 4k?) - and AFAIK postgres should already be trying to fix that for you, by how it lays out data on disk. But SSD do have somewhat better sequential read than random-read, even if not as dramatic as spinning disks).
There's still a speedup, but it's dwarfed by the magnetic->SSD boost.
We do have fragmentation within a block. Our records on the heap are about 150 bytes, so maybe 30 rows per 4K page. In the pathological case where there is 1 row per page due to fragmentation, queries have to fetch 30x the number of blocks, which is a killer on a spinny disk.
I have to deal with a couple of plattered databases. It's not optimal, but there's usually some cost constraint, or there are only 100 users on it a week. Or both, in which case it's no big deal and it's very very cheap.
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.
I love Datomic because of the way it removes the need to understand these locks completely. With datomic you can reason far more easily about a set of concurrent updates than dealing with SQL locking levels.
The trade is you are essentially always running with your database as fully serialized writes.
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 ;)
Postgres transactions can also take as long as they want and the results will never be inconsistent.
The downside to SERIALIZABLE is that conflicts become visible failures to the client, so you have to handle them and retry. Datomic solves this by serialising through the function you pass, but at great latency cost.
These locks can be non-trivial to reason about at times. On 9.3 I've been bitten numerous times by concurrent transactions attempting to escalate share locks to exclusive lock because of a FK and getting stuck forever, without the db being able to break up that deadlock. If you intentionally limit the # of connections to the db (which you should), that can lead to downtime. Needed to learn about SELECT FOR UPDATE pretty quickly after that. It seems like that's somewhat ameliorated in 9.4+, although it's still possible in a few corner cases, if I recall correctly from talking to RhodiumToad.
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.
I notice they mention advisory locks, which aren't used much. MySQL has them, too. They're useful for such things as preventing two copies of the same application from running. This works across machine boundaries, and if you lose the database connection or the client machine or the host machine, the lock is released, so you don't have all that nonsense about Linux/UNIX lock files hanging around.
I use those for tasks that should only have one copy running on a cluster, but any machine in the cluster can run.
note: there are also "skip locked" and "nowait" clauses in Postgres 9.5 for more flexibility when dealing with row level locking. This gives you the option of failing immediately (nowait) or just getting back the set of rows that aren't locked (skip locked).
[1] http://reorg.github.io/pg_repack/