How tight should cycling shoes be?
In my experience you mainly want them to be comfortable.

But there are several considerations:

If the shoes are too short your toes will bump the ends, and this can become torturous 75 mile into a centurion.

On the other hand, if the shoes are loose your feet will slide back and forth.  Not only can this be painful, but it means that you will not have a steady foot on the pedal, but rather the relationship between the ball of your foot and the pedal's center will be constantly changing, resulting in poor pedaling (and some additional pain in the foot).