Sorry, but based on this plan it is possible to make a conclusion that there are only two possible cluster solutions for Postgres: XC/XL and FDW-based. From my point of view there are much more possible alternatives. Our main idea with XTM (eXtensible Transaction Manager API) was to make it possible to develop cluster solutions for Postgres as extensions without patching code of Postgres core. And FDW is one of the mechanism which makes it possible to reach this goal.

IMHO it will be hard to implement efficient execution of complex OLAP queries (including cross-node joins and aggregation) within FDW paradigm. It will be necessary to build distributed query execution plan and coordinate it execution at cluster nodes. And definitely we need specialized optimizer for distributed queries. Right now solution of the problem are provided by XL and Greenplum, but both are forks of Posrgres with a lot of changes in Postgres core. The challenge is to provide the similar functionality, but at extension level (using custom nodes, pluggable transaction manager, ...).


But, as you noticed, complex OLAP is just one of the scenarios and this is not the only possible way of using clusters. In some cases FDW-based sharding can be quite efficient. Or pg_shard approach which also adds sharding at extension level and in some aspects is more flexible than FDW-based solution. Not all scenarios require global transaction manager. But if one need global consistency, then XTM API allows to provide ACID for both approaches (and not only for them).

We currently added to commitfest our XTM patch together with postgres_fdw patch integrating timestamp-based DTM implementation in postgres_fdw. It illustrates how global consistency canbe reached for FDW-based sharding. If this XTM patch will be committed, then in 9.6 we will have wide flexibility to play with different distributed transaction managers. And it can be used for many cluster solutions.

IMHO it will be very useful to extend your classification of cluster use cases, more precisely formulate demands in all cases, investigate how them can be covered by existed cluster solutions for Postgres and which niches are still vacant. We are currently continue work on "multimaster" - some more convenient alternative to hot-standby replication. Looks like PostgreSQL is missing some product providing functionality similar to Oracle RAC or MySQL Gallera. It is yet another direction of cluster development for PostgreSQL. Let's be more open and flexible.


On 23.02.2016 19:43, Bruce Momjian wrote:
There was discussion at the FOSDEM/PGDay Developer Meeting
(https://wiki.postgresql.org/wiki/FOSDEM/PGDay_2016_Developer_Meeting)
about sharding so I wanted to outline where I think we are going with
sharding and FDWs.

First, let me point out that, unlike pg_upgrade and the Windows port,
which either worked or didn't work, sharding is going be implemented and
useful in stages.  It will take several years to complete, similar to
parallelism, streaming replication, and logical replication.

Second, as part of this staged implementation, there are several use
cases that will be shardable at first, and then only later, more complex
ones.  For example, here are some use cases and the technology they
require:

1. Cross-node read-only queries on read-only shards using aggregate
queries, e.g. data warehouse:

This is the simplest to implement as it doesn't require a global
transaction manager, global snapshot manager, and the number of rows
returned from the shards is minimal because of the aggregates.

2. Cross-node read-only queries on read-only shards using non-aggregate
queries:

This will stress the coordinator to collect and process many returned
rows, and will show how well the FDW transfer mechanism scales.

3. Cross-node read-only queries on read/write shards:

This will require a global snapshot manager to make sure the shards
return consistent data.

4. Cross-node read-write queries:

This will require a global snapshot manager and global snapshot manager.

In 9.6, we will have FDW join and sort pushdown
(http://thombrown.blogspot.com/2016/02/postgresql-96-part-1-horizontal-s
calability.html).  Unfortunately I don't think we will have aggregate
pushdown, so we can't test #1, but we might be able to test #2, even in
9.5.  Also, we might have better partitioning syntax in 9.6.

We need things like parallel partition access and replicated lookup
tables for more join pushdown.

In a way, because these enhancements are useful independent of sharding,
we have not tested to see how well an FDW sharding setup will work and
for which workloads.

We know Postgres XC/XL works, and scales, but we also know they require
too many code changes to be merged into Postgres (at least based on
previous discussions).  The FDW sharding approach is to enhance the
existing features of Postgres to allow as much sharding as possible.

Once that is done, we can see what workloads it covers and
decide if we are willing to copy the volume of code necessary
to implement all supported Postgres XC or XL workloads.
(The Postgres XL license now matches the Postgres license,
http://www.postgres-xl.org/2015/07/license-change-and-9-5-merge/.
Postgres XC has always used the Postgres license.)

If we are not willing to add code for the missing Postgres XC/XL
features, Postgres XC/XL will probably remain a separate fork of
Postgres.  I don't think anyone knows the answer to this question, and I
don't know how to find the answer except to keep going with our current
FDW sharding approach.


--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to