The size limitation is actually just one category of demoscene productions, the so called "intros", which come in 4k, 40k or 64kb variety. Demos on newer platforms can be more than 10mb in size.

The size limitation on C64 comes naturally as the disks are only able to store 170kb per side.