I don’t understand the advantage of having the transport protocol be HTTP/SSE rather than studio especially in this case when it’s literally running locally.

The use case for pg-mcp is server deployment - local running is just for dev purposes. HTTP/SSE enables multiple concurrent connections and network access, which stdio can't provide.