Pixels are absolutely visible when using a composite connection, however they are distinctly more fuzzy than if you use S-video or RGB or component video. The horizontal bleed is especially noticeable, which was deliberately used for transparency effects on consoles like the Sega Megadrive/Genesis, which couldn't do proper transparency in hardware.
I had several consoles hooked up to my composite/S-video/RGB capable CRT TV (in 240p, too), including a RetroPi hooked up via a HDMI->composite/S-video converter (before I got a proper SCART RGB module for my Pi3). The difference when switching the converter box from composite to S-video was profound, a very clear difference in sharpness and color quality. The difference from S-video to RGB was much smaller, but still noticeable.
These days I mostly play games on emulators on my PC, I don't really have room for a CRT TV anymore. I absolutely abhor all the pixel art scaling algorithms, I much prefer a gentle CRT-emulating fuzz (similar to a good RGB signal) and subtle scanlines, which seems to best replicate what the games look like on the actual CRT.
I was also there for the console boom from the C64 all the way up to the Xbox360, at which point I mostly lost interest. I have a background partially in electronics, including CRT TVs, and I used to work at a TV manufacturer during the CRT to LCD transition. So I'd like to think my credentials are in order.