Look at it this way - You will be able to play all games out currently, even Doom3, at good resolutions and with AA/AF on. Future games, like in a year or 2, will start to show differences between 128MB and 256MB ones, but that will be a while. In order to get a card that will play games well now and have 256MB, you'd be looking at a X800SE or a 256MB 6800 (rare but evga hase one). Both of these cards are running around $350, so it'd be wiser to just get a GT in that case for $25 more. Either way, you'd end up spending $150-200 more getting one of these cards, or almost double the cost of a 6600GT. So by the time you really start to notice the speed lag, you can go out and get a brand new midtrange card (7700GT or whatever) with 256MB or 512MB memory, basically with the money you saved by not buying the 6800GT.
I feel that the 6600GT is perfectly balanced, in the sense that the performance will be considered slow around the same time that 128MB memory starts to become inadequate. This is a very good situation, since you are not "wasting" anything. A 6800's speed might hold up for longer than the 6600GT, but it doesn't do much good if the memory is too low. On the other side, getting a 6600 256MB will have enough memory for Doom 3 and future games, but if they only run at 10FPS, what good is that? I personally think that the 6600GT is the best move by nvidia since the Ti4200, and wouldn't be surprised if they sell like absolute hotcakes after all the PCIe AMD chipsets hit shelves. One of these is going into my system for sure, maybe 2 eventually if the Asus SLI board isn't too expensive.