I wonder what's the standard of "big" in these context--for example I always thought that some million rows is a very big, but only recently learned that it's a size that RDBMS such as PostgreSQL can handle it with no problem.
There's also the threshold where your indexes don't fit into RAM. And the threshold where your data no longer fits into PCIe SSDs on a single server. (The combined bandwidth of the SSDs will rival the RAM, but with more latency.)
Qualitatively, I think it becomes “big” when you have to leave the space of generic “it just works” technologies and you have to start doing bespoke optimizations. It’s amazing how far you can get these days without having to go custom.
I think a good definition of "large" is "several times larger than will fit on a practical modern server". Servers with a petabyte or more of fast attached storage are increasingly common, so that threshold is pretty high. Machine-generated data models (sensing, telemetry, et al) routinely exceed this threshold though.
create and subsequently update index on trillion rows is very untrivial task performance wise.. Which DB you would suggest to use for this?
this can mean building some untrivial infra, so the task is much more complicated than just using cloud vm.
My point is that bottle neck and why you need cluster and not single VM is likely CPU and iops, and not data storage.
As someone that does it all the time on 10b+ record tables, not really? if you don't have extra resources to build the occasional index your DB is under-provisioned and you're close to falling over, cluster or not.