I recall Bill Coore, then up for ASGCA membership, with me, President of ASGCA in the audience......I wasn't there to intimidate, but was interested in asking the question about how they restored the greens at Riv. I think Geoff Shack's book had just come out, showing the wide variety of green sizes and shapes at the original Riv, and how they had morphed into 4000 SF ovals for the most part. He answered, tentatively, that yes, they felt it best to restore the greens to what the members thought they always were, not what they always were. And, a large part of that was the thought that Riv would no longer be a championship course if the greens were too easy to hit.
As to the basic thread premise, like Ally, I tend to strive for a variety of green sizes. I grant that there are many great courses that have a theme of big or small greens, and fewer eclectic ones that have great variety in green size, so I don't think about in terms of greatness. And, I think about it more to distinguish holes when working on an average site to make it closer to good than average.
The general thought is a 5000 green will stick out after a 10,000 SF as different, so I often place them close together, if not one right after the other, to enhance the look. All this under the bigger design idea that every hole ought to have something unique about it and be memorable after the round. Obviously, the greens must fit the site, the hole length and strategy, etc., but its usually not hard to arbitrarily decide one should be big (takes a big site) whereas small ones usually present themselves by virtue of a small green site. That can't be small because of an opening in the trees, because if anything, you need to clear the trees more to give better agronomic conditions. It has to be a naturally small knob site, or maybe a narrow site and the green is longer, but very skinny in one direction.