Earlier quoted context omitted.
'Upstreamable' means that they send Linus a pull request, and Linus merges it into the kernel. It would then become part of Linux. It's a negative that it's not upstreamable. I wouldn't go so far as to say it's bad, but it's less than good. That means you'll continue to maintain a separate kernel module that's rebuilt for each kernel. It would need to be modified to match the kernel's coding standards and whatnot. Pa…
What would the practical effect be for me as a user, say I render in Blender, or I play a few intensive 3d games?
If the drivers aren't upstream, a future kernel can break them. So if you update your OS, it might no longer support your GPU without installing an alternative. Nvidia are responsible for getting this fixed in a timely manner.
If the drivers are upstream, then people aren't allowed to break them when they make changes to the kernel. There should never be a kernel or OS upgrade that causes the drivers to no longer work, the people who made the breaking change are responsible for fixing it before it gets released to you.