you will definitely hit some limits if you go hog-wild. There are two types of limits:
1. soft limits: high complexity can trigger bugs in the compilers and/or extend vis times exponentially.
Aguirre's tools have fixed a lot of the bugs, but the exponential vis times are inherent in the way quake vis is calculated.
2. hard limits: there are various numeric limits (number of leafs, nodes, faces, etc.) which can prevent the map from loading in a stock engine. However, many of these limits have been lifted by some of the popular engines, which means there is a viable audience for limits-breaking maps. (but it's still a smaller audience than if you were compatible with all quake engines)
Engines that support high limits (As far as I know):
Fitzquake -
http://celephais.net/fitzquake/
AguirRe's engines -
http://user.tninet.se/~xir870k/
Darkplaces -
http://icculus.org/twilight/darkplaces/
DirectQ -
http://mhquake.blogspot.com/
Check out some popular recent SP maps to see what is possible, that might help guide your expectations since you are used to Quake 3 mapping. APSP2, Marcher Fortress, Day of the Lords, ne_tower, A Roman Wilderness of Pain, etc. (check the news archive on this site for map releases)