This doesn't really answer my question. I'm not asking "how can you turn PG into a distributed database", I'm asking "what do I get with that that I don't get with, eg, Cassandra"?
It also allows you to present said information from other stores through a well defined interface, such as via stored proceedures that can be locked down via login/identity/application and other features typically in SQL, while being able to act as a gateway for more optimized data stores for specific types of data. Not to mention that JSON data in pgsql has gotten pretty good... I think with plv8 it gets event better. Where I think pg falls is in terms of replica sets, hot failover and automated recovery options which many nosql servers give you in the box. With PG you pretty much have to pay for a decent HA solution.
That said, I honestly really like document stores, and RethinkDB as an example offers some of the relational features of a typical SQL database while being mainly available as a document store.
And personally I have always thought putting business logic in stored procedures is a truly dumb idea. You forever lock yourself to that database and there is always a point that you can exceed its capabilities and need to implement functionality in the application layer anyway.
Generally if you have exposed your db credentials it's because something has gone very wrong i.e. your entire host has been compromised in which case stored procedures aren't going to save you.
Postgres has been building its SQL coverage for over a decade, and compares favorably to others databases: https://news.ycombinator.com/item?id=9018129
Given local join machinery, a join between 2 distributed tables can be achieved by joining the partitions of the tables locally after ensuring that tables are co-partitioned and partitions are co-located. That is, if performing A JOIN B ON (A.X = B.Y), ensure that A is partitioned by X and B is partitioned by Y with the same partitioning scheme such that every partition in A has a corresponding partition in B with which it can be joined. A distributed join can then be performed (in parallel) by joining each pair of partitions and concatenating the results.
Ideally, tables are co-partitioned and partitions are co-located in advance. The article describes scenarios in which the tables need to be repartitioned during the query, which can cause a lot of network traffic. However, the amount of traffic is limited in practice since filters and projections can be applied before shuffling any data.
The great thing about implementing a distributed database on top of PostgreSQL is that the local machinery already exists, and it is very good.