You have asked a very good question, and obviously many will differ in their thinking on this story. For me, however, the climax of this story is John's discovery of the "dead god" in the last few paragraphs of the tale. Following the amazing vision that John has of "the gods" and their lives before the "Great Burning", John describes himself as "confused" - he is not able to understand the reason for the destruction. John's discovery of the "god" and his resulting conclusion that he was a man like him represents the end of John's quest for knowledge and self-discovery, his journey to find his own identity and that of his people. It is clear however, that the way the "dead god" is described in his posture and the way that he would have watched his people die gives John vital truth about the nature - and dangers - of knowledge, and hopefully points our fictional descendants in a different path from the abuse of knowledge leading to the destruction of mankind.
I've seen support for two possible climax locations in "By the Waters of Babylon." They happen just about at the same time chronologically, so I don't feel that there is much difference between the two moments.
The first possible climax location is when John is having his dream or vision about what New York looked like before it was destroyed. He sees it bustling with people/gods. The way that this part of the story is told feels rapid and exciting, which helps convey the idea that John is experiencing a sensory overload. This part of the story makes sense as a climax because of that fast pacing.
There was a roaring in my ears like the rushing of rivers. Then my eyes grew used to the light and my ears to the sound. I knew that I was seeing the city as it had been when the gods were alive. . . Restless, restless, were the gods and always in motion!
The second possible climax to this story happens moments after John's vision of the past. It is when he sees the body of the god and fully realizes that the gods were humans.
Then I saw the dead god. He was sitting in his chair, by the window, in a room I had not entered before and, for the first moment, I thought that he was alive. . .That is all of my story, for then I knew he was a man—I knew then that they had been men, neither gods nor demons.
I like this moment as a climax more than the other moment. A climax is a major turning point. I feel that John's dream is still rising action because he doesn't know for sure that the gods were people after the dream. He suspects it, but the awe of what he envisioned supports the notion that the former inhabitants were gods. When John sees the dead human body, he fully realizes that the gods were not gods. They were humans with lots of learning and technology. That moment completely changes everything that John has grown up knowing about the "gods."
The most exciting part of this story is when the main character is seeing visions of the city being destroyed and its inhabitants being chased out and killed. That is the part when I realized that it was people that lived in the city. The picture in the book kinda helped a little too. lol