Don’t skip the discussion on advisory locks. In my experience nearly every nontrivial application that spans multiple machines has concurrency bugs that advisory locks are perfectly suited to fix.
replies(4):
This was MySQL but its advisory locks are pretty similar to Postgres.
It's also nice that the lock is released when the database connection terminates. Really easy to use. If you need exactly one of something running constantly, you can launch however many processes and let all but one spin trying to acquire the lock. When one dies and closes its SQL connection, thus releasing the lock, another will obtain the lock and begin work more or less instantly.
They're infinitely useful!