The possibility of John as an alcoholic is first raised by Sam in the pilot. When Dean tells Sam their father is missing, Sam retorts, "So he's working overtime on a Miller Time shift. He'll stumble back in sooner or later."
It's not the last reference Sam makes to John being an alcoholic. In Dark Side of the Moon, Sam recalls their Thanksgivings: "We had a bucket of extra-crispy and Dad passed out on the couch." In the flashback of The Girl Next Door, Sam tells Amy that his father has a temper: "You don't want to see him when he's drinking."
Yet, in Nightmare, Sam seems to say the exact opposite: "Well, it coulda gone a whole other way after Mom. A little more tequila and a little less demon hunting and we woulda had Max's childhood. All things considered, we turned out ok. Thanks to him."
So what's going on here?
Here is my theory. It lies in a scene in Playthings. Sam gets drunk, and Dean is appalled: "Dude, what are you thinking? We're working a case."
Clearly, the brothers had a no-getting-drunk-while-on-a-hunt rule. Now, Sam and Dean have been hunting on their own for well over a year at this point. It's possible they came up with the no drinking rule on their own. But I think it's more likely that it was John's rule, and one John himself honored. He got drunk on Thanksgiving because he was not working a hunt. But when he was hunting, which was often, he didn't drink. Sam states in Nightmare that if John had gotten drunk more often, they would have had Max's childhood - while implies that John was, indeed, a nasty drunk when he did drink. I would hazard a guess that John didn't get drunk too often, but when he did, it was very memorable.