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)
I feel like this is going to be an increasingly hard question to answer as more and more cloud providers with interchangeable services become available. It's like buying a car. You will run into someone that has used 2 or 3 different providers of the same service, but some one with hands on experience with them all, unlikely.
Both RDS and Heroku are aimed more at more modestly sized database in that you don't really scale beyond the vertical capacity of a single node. The prices are relatively comparable with RDS starts at around $20-30 a month depending on the instance type and Heroku Postgres at $50/month. Both services will get you an HA feature that allows for better uptime through automatic node failover.
Overall Heroku Postgres will probably feel a little like the Heroku platform: a little more polished and a little more "managed", but with fewer knobs to tweak, which can be both good and bad depending on your situation.
It's too early to say how Google's offering will shape up, but it'll likely be in the same vein as these first two. Lack of HA probably means that you should limit your production use of it, although it seems that the team intends to implement that eventually based off the service's documentation and other comments here.
Citus and Aurora come into play when you're looking to scale beyond a single node. Citus Cloud starts at $990 a month so you're not likely to come into it without some non-trivial requirements. Aurora is similar idea.
Citus' killer feature over something like Aurora is that instead of going ahead and forking Postgres wholesale, the product runs as an extension, which means that you're likely to get better compatibility going forward with new Postgres features.
Aurora is "compatible" which means that you'll be able to use psql and get access to common functionality, but are likely to see a divergence in support features. A similar situation is Redshift, which deviated around Postgres 8.0.2 [1], and at this point it's safe to say that it will never catch up.
Aurora isn't substantially more expensive than RDS Postgres. Caveat is that you can't run it on the really tiny instance sizes right now. On an r3.large with no reserve pricing you're looking at ~$200pm, or ~115 with reserve.
With so many DBaaS tools available, I'd like to know the best options for things like pricing, availability, features, tooling, monitoring, etc...