How tight are climbing shoes supposed to be?

Climbing shoes should be tight, but not painful. It is important to also differentiate between pain and discomfort, as climbing shoes are not comfortable, but should also not cause pain.

With that said, there can be slight amounts of pain when climbing in new climbing shoes, especially for new climbers. This is because climbing shoes tend to be pretty stiff out of the box, and it takes a while for them to break in, meaning, for them to get a little bit softer and to stretch a little bit.
For new climbers this will be felt a little bit more since they are not used to climbing shoes in general and to the tightness of them.

To know how tight your shoes should be, just keep in mind the reason the shoes need to be tight in the first place, which is to help improve your performance.

If you are a beginner climber, this means that you do not need very tight shoes, since the routes you will be climbing do not require a high level of precision. You will most likely not be standing on tiny foot holds that require you to be precise and to trust your feet. You will also not be using your heel to the extent that it must be very tight.

If you are a more advanced climber, then understand what you are expecting from your shoes. They should be tight enough to be able to allow you to stand on tiny foot holds, to trust your feet, and for your heel to not move inside the shoe. This means that you will need a tighter climbing shoe than a beginner climber would need. Though, this does not mean that the shoe must be painful, it just needs to be a good fit for the shape of your foot, and tight enough that it will give you all of its benefits, but none of the pain (or as little as possible).

It is also important to understand that not all shoes fit all feet. Some climbing shoes, even when trying on larger sized shoes, will not fit your feet and will cause pain regardless of the shoe size. This is because climbing shoes have different shapes and features, so it is important to try on different shoes and to find the ones that fit the shape of your foot the best, and then to choose the correct size that will be tight enough for your needs, but also not so tight as it will cause you pain, preventing you from even climbing in them.

Another point worth mentioning: Don't size down just to size down!
Many climbers think that climbing shoes must be a certain size below their street shoe size, so they buy climbing shoes that are 2 or 4 sizes smaller (some even more!), and just climb through the pain. This is very bad practice as it can cause you to not only not want to climb due to the pain, but can also be very dangerous for your feet! Don't think about sizing down when trying on climbing shoes, just get the shoe that you feel the most comfortable in and that feels right. Some people go up in climbing shoe size from their street shoe size because it just feels the best for them and gives them the performance they are looking for.