Why does PostgreSQL FDW talk to multiple backend servers for this point query?
PostgreSQL lacks the insight into the "mod" function that it would need to declare that user_id=97 also implies that mod(user_id,4)=1.  If you provide that insight manually, it would likely honor it:

WHERE user_id=$1 and mod(user_id,4)=mod($1,4)


This has nothing to do with FDW.  If all partitions/tables were local, the answer would remain the same.

You could use hash partitioning, then it would generate the insight automatically.