I'm currently considering using Postgres-XC or Postgres-XL to create a long term data store for around 20 TB of data or more.
I read the information in Postgres-XL website that Postgres-XC is not yet an MPP architecture. But, I can see that it is already has a "Coordinator" that acts as master node and a bunch of data nodes that do not shares data (shared-nothing).
What makes Postgres-XC not an MPP?
I am one of the original developers of both projects.
They both have a Coordinator and Datanodes, that is true. The Coordinator plans and directs execution on both as well.
Perhaps a few examples will help.
Let's say you have three tables, t1, t2 and r1. t1 has columns a1 and a2, and t2 has columns b1 and b2. t1 is distributed (sharded) on a1, and t2 is distributed on b1. r1 has columns c1 and c2 and is replicated, with an exact copy of each row
For simple queries like SELECT * FROM t1, it will be parallelized in both Postgres-XC and Postgres-XL.
Another example:
SELECT * FROM t1 INNER JOIN r1 ON t1.a1 = r1.c1
will also be parallelized in both, with the join being "pushed down" to the data nodes. We can do this because r1 is replicated on each node. This type of query is fine where t1 is a big fact table and r1 is a dimension table.
Let's look at a different case:
SELECT * FROM t1 INNER JOIN t2 ON t1.a1 = t2.b2
Here we are joining on the distribution column of t1, but NOT on the distribution column of b2. In a 4 node cluster, a row on node1 in t1 could potentially need to be joined with rows in t2 on node1, node2, node3 and node4.
Postgres-XC handles this by shipping all of the data that qualifies in the join from each table to the Coordinator, and joins there. In the example, we did not include any WHERE clause qualifiers. So, it will ship the entire contents of t1 and t2 from node1, node2, node3 and node4 over to the Coordinator, and then join there. There would be no join parallelism, and you also have the overhead of shipping all of the data to one place. So in this case, Postgres-XC would actually be slower than native PostgreSQL, much slower if the tables are large.
Postgres-XL would handle this differently. Remember the join condition is t1.a1 = t2.b2. It would recognize that b2 is being equijoined a1, which is the distribution column of t1. That is, if we have the b2 value, we know exactly on which node the data it needs to join with in t1 resides (since we can apply the hash distribution function on the value). As data is produced on each node for t2, it will be consumed by exactly the one datanode that needs it for t1, and do so directly without going through the coordinator.
The datanodes are simultaneously reading from t1 and producing t2 rows for joining with t1, having the data from t2 directly consumed by the datanode that needs it for a particular row.
This direct datanode-to-datanode communication allows for more parallelization for more complicated queries compared to Postgres-XC.
I hope that helps to answer your question.
Postgres-XL also has other performance improvements. Sequences are handled more optimally for one.
