Maybe there's a niche for "PostgreNoSQL", a layer atop Postgres that you start using like a NoSQL solution. (Perhaps, it's string keys and JSON blob values.) It's not very efficient, except for simple keyed lookups, but it works enough for a quick start.
Then, as you use it, the system optimizes itself (or makes suggestions) based on actual access patterns. A subset of objects could be a formal, indexed table? Have it happen automatically or offer the SQL as a suggestion.
Conversely, you could have a NoSQL layer below Postgres, where PG stores and indexes metadata which tells it which, of many, small NoSQL dbs to find the actual data in. These data dbs then can be sharded/replicated across physical systems as you like. You loose some raw speed on reads, but avoid a global write lock and the system scales quite well. I've started playing around with such a system with https://github.com/cloudflare/SortaSQL
IIRC, there are people talking directly to InnoDB (MySQL backend) using it as a NoSQL style DB. You don't however get SQL analysis, you're bypassing the SQL side of things.
Then, as you use it, the system optimizes itself (or makes suggestions) based on actual access patterns. A subset of objects could be a formal, indexed table? Have it happen automatically or offer the SQL as a suggestion.