Why can MySQL handle multiple updates concurrently and PostgreSQL can't? Let's imagine you have a table with this definition:

CREATE TABLE public.positions
(
   id serial,
   latitude numeric(18,12),
   longitude numeric(18,12),
   updated_at timestamp without time zone
)


And you have 50,000 rows in this table. Now for testing purposes you will run an update like this:

update positions
set  updated_at = now()
where latitude between 234.12 and 235.00;


That statement will update 1,000 rows from the 50,000 (in this specific dataset).

If you run such a query in 30 different threads*, MySQL innodb will succeed and PostgreSQL will fail with lots of deadlocks.

Why?



* I'm comparing the latest version of MySQL innodb vs Postgres, this is a concurrent update case. Production cases: imagine 5000 stocks being updated with the latest price available constantly.
Rolando already more or less described the reason why the deadlocks happen.  Let me add that PostgreSQL has to take locks on the rows to be updated, in order to maintain consistency.  (now() is fixed to the beginning of the transaction, meaning that two concurrent updates will want to update a given row to a different timestamp, which has to be resolved somehow - taking locks on the rows is the PostgreSQL solution.)

The locks are assigned to the transaction on a first come - first served basis.  Deadlocks occur when two transactions try to obtain locks on the same rows in different order.  The way to do this is - now not surprisingly - requesting the locks in a predictable order.  This is done by

SELECT id -- could be any column, even a constant
FROM position 
WHERE latitude between 234.12 and 235.00
ORDER BY id 
FOR UPDATE;


Here the crucial part is ORDER BY id and FOR UPDATE.  The latter takes the necessary locks of the same type the subsequent UPDATE needs.  

The SELECT ... FOR UPDATE statement might looks superfluous otherwise (especially when you don't need the selected data), and it also means disk writes even before actually changing the data.  This has to be considered when using it.