I think use-case for Google Cloud SQL, RDS, Heroku, etc is a bit different from Citus and other distributed databases. It seems that Cloud SQL has very limited scalibility (32 processors, 200GB of RAM), so it might not be very good at usecases that your working dataset is in order of terabytes or more. Citus on the other hand has horizontal scalability and you can add more CPU power and RAM by adding another machine to your machines.

If my data were at order of 10GBs, I would choose Cloud SQL, RDS, etc. At order of 100GBs, I would try both Cloud SQL, RDS, etc. and Citus, etc. to see which one fits my usecase. At order of terabytes, I would choose Citus or some other distributed database.

(I'm a former Citus employee and Current Googler in a non-Cloud SQL team)