Iirc any 3D card capable of running Q2 (ie you weren’t rendering it on your CPU) would display the transparent water (unlike Quake 1), so something like Riva 128 or Voodoo 1 at the time Quake 2 launched.

Later cards (Voodoo 2 and onwards) gave you the advantage of a significantly higher frame rate and resolution.