One downsides I didn't like though is no incremental backups unless enterprise. There's PG dump but not sure if it'd work on a bunch of threads and stuff. If using MySQL there's the MyDumper project that looked interesting.
Then there's no full text search is a downside, but I know it seems people recommend running a separate search solution anyways so that can be a little slow to get started. Easier if you could just run it in one system in the beginning, and who knows maybe search isn't a bottleneck anyways depending on the implementation of the database or you want to be able to still full text search while debugging maybe.
Then no geospatial can be a downside for some types of businesses or use cases, say a place lookup, factoring it into a game matching algo, etc. I've been really interested in MongoDB lately though, but was playing with a few newer SQL based databases for what to use on my own project. Then I know some companies actually run multiple databases too, some stuff might be noSQL while other workloads are SQL databases too, but I know some rather just one a single database so only one source of truth. Then I know PostgreSQL has the idea of foreign data wrappers [0], so I guess then you could treat a MongoDB collection as a table. So maybe you had a users collection, you could join on it I think using this but never played with it. However this seems mostly Postgre itself, other Postgre compatible databases don't implement this as far as I can tell.
However I believe even MongoDB with it sharding can't handle GEO unless you separate that data in a unsharded collection, which is then placed on any replica set in the cluster. So maybe you could split geo results up by city level in different collections but then if you wanted to find near by things that could go into other cities based on the radius, you'd have to write your own logic. Unsure how larger sites like say Yelp handles GEO lookups.
Only big downside I see with MongoDB though is no joins, so say you had a social networking site and wanted to hide posts by users suspended from the results, you'd have to write your own logic to do so. I think it'd be so cool if you could like symlink a value from one document to another. I think OrientDB has something like that though. I was asking a friend who uses Mongo for advice, apparently in that case you'd just update every single post when you suspend that user - not sure if that's really efficient or not but I doubt a single user would have millions of posts... So probably less than 10K posts at the most maybe. However I guess with social feeds themselves, seems it recommend to fan out on write instead of fanning out on read, so in a way you can sometimes precompute things to help the database. All about making decisions and tradeoffs I guess.