Re: pglogical - logical replication contrib module - Mailing list pgsql-hackers

The following review has been posted through the commitfest application:
make installcheck-world:  not tested
Implements feature:       not tested
Spec compliant:           not tested
Documentation:            not tested


This reply will covers a 10,000 foot level review of the feature (some of my other replies to the thread cover
specificsthat came up in testing and code level review will come later)
 

1) Do we want logical replication in core/contrib

10 year ago a popular feeling in the postgresql project was that replication didn't belong in core because there were
toomany different styles.  People then went on to complain that it were too many replication projects to choose from
andthat they were hard to use and had lots of corner cases.   The evolution of WAL based replication showed us how
popularin-core replication is.  Users like being able to use in-core features and our community process tends to
producebetter quality in-core solutions than external projects.
 

I am of the opinion that if we can come up with a solution that meets some common use cases then it would be good to
havethose features in core/contrib.  At this stage I am not going to get into a discussion of a contrib extension
versusbuilt in as not an extension.   I don't think a single replication solution
 
will ever meet all use-cases.   I feel that the extensible infrastructure we have so far built for logical replication
meansthat people who want to develop solutions for use-cases not covered will be in a good position.  This doesn't mean
wecan't or shouldn't try to cover some use cases in core.
 


2) Does this patch provide a set of logical replication features that meet many popular use-cases

Below I will review some use-cases and try to assess how pglogical meets them.
** Streaming Postgresql Upgrade

pg_upgrade is great for many situations but sometimes you don't want an in place upgrade but you want a streaming
upgrade. Possibly because you don't want application downtime but instead you just want to point your applications at
theupgraded database server in a controlled manner.   Othertimes you
 
might want an option of upgrading to a newer version of PG but maintain the option of having to rollback to the older
versionif things go badly.
 

I think pglogical should be able to handle this use case pretty well (assuming the source version of PG is actually new
enoughto include pglogical). 
 
Support for replicating sequences would need to be added before this is as smooth but once sequence support was added I
thinkthis would work well.
 
I also don't see any reason why you couldn't replicate from 9.7 -> 9.6 thought since the wire format is abstracted from
theinternal representation.  This is of course dependent not the application not doing anything that is inherently
in-compatiblebetween the two versions 
 


** Query only replicas (with temp tables or additional indexes)

Sometimes you want a replica for long running or heavy queries.  Requirements for temp tables, additional indexes or
maybethe effect on vacuum means that our existing WAL based replicas are unsuitable. 
 

I think pglogical should be able to handle this use case pretty well with the caveat being that your replica is an
asynchronousreplica and will always lag
 
the origin by some amount.

** Replicating a subset of tables into a different database

Sometimes you wan to replicate a handful of tables from one database to another database.  Maybe the first database is
thesystem of record for the data and the second database needs an up to date copy for querying.
 

Pglogical should meet this use case pretty well, it has flexible support for selecting which tables get replicated from
whichsource.  Pglogical doesn't have any facilities to rename the tables between the origin and replica but they could
beadded later.
 

** Sharding

Systems that do application level sharding (or even sharding with a fdw) often have non-sharded tables that need to be
availableon all shards for relational integrity or joins.   Logical replication is one way to make sure that the
replicateddata gets to all the shards.  Sharding systems also sometimes want
 
to take the data from individual shards and replicate it to a consolidation server for reporting purposes.

Pglogical seems to meet this use case, I guess you would have a designated origin for the shared data/global data that
allshards would subscribe to
 
with a set containing the designated data.  For the consolidation use case you would have the consolidation server
subscribeto all shards
 

I am less clear about how someone would want DDL changes to work for these cases.  The DDL support in the patch is
prettylimited so I am not going to think much now about how we would want DDL to work.
 


** Schema changes involving rewriting big tables

Sometimes you have a DDL change on a large table that will involve a table rewrite and the best way of deploying the
changeis to make the DDL change
 
on a replicate then once it is finished promote the replica to the origin in some controlled fashion.  This avoids
havingto lock the table on the origin
 
for hours.

pglogical seems to allow minor schema changes on the replica such as changing a type but it doesn't seem to allow a DO
INSTEADtrigger on the replica.  I don't think pglogical currently meets this use case particularly well
 



** Failover

WAL replication is probably a better choice for someone just looking for failover support from replication.  Someone
whois looking at pglogical for failover related use cases probably has one or more of the other uses cases I mentioned
andwants a logical node to
 
take over for a failed origin.   If a node fails you can take some of the remaining subscribers and have them
resubscribeto one of the remaining nodes but there is no support for a) Figuring out which of the remaining nodes is
mostahead b)  Letting the subscribers figure out which updates from the old origin that are missing and getting them
froma surviving node (they can truncate and re-copy the data but that might be very expensive)
 

I am not sure what would be involved in taking a streaming WAL replica and have it stand it replace the failed node.

Lack of replicating sequences would also make failing over to a pglogical replica awkward.

** Geographically  distributed applications

Sometimes people have database in different geographical locations and they want to perform queries and writes locally
butreplicate all the data to all the other locations.   This is a multi-master eventually consistent use case.
 

The lack of sequence support would be an issue for these use cases.  I think you could also only configure the cluster
ina fully connected grid (with forward_origins='none').  A lot of deployments you would want some amount of cascading
andstructure which isn't yet supported.   I also suspect that managing a grid cluster with more than a handful of nodes
willbe unwieldy (particularly compared to some of the eventual consistent nosql alternatives)
 

The features BDR has that were removed for pglogical are probably really useful for this use-case (which I think was
theoriginal BDR use-case)
 

** Scaling across multiple machines

Sometimes people ask for replication systems that let them support  more load than a single database server supports
butwith consistency.
 
Other use-case applies if you want 'eventually consistent'  this use case is for situations where you want something
otherthan eventual consistent.
 

I don't think pglogical is intended to address this.



3) Do we like the design of pglogical

I like the fact that background workers are used instead of external daemon processes
I like the fact that you can configure almost everything through SQL
I like that the output plugin is separate because this patch is big enough as it is and I can see uses for it other
thanpglogical.
 

The core abstractions are* Nodes-, every database in the pglogical cluster is a node* Sets - A collection of tables
thatbehave similarly* Subscriptions - A link between a provider and a replica. There can only be one subscription
betweena provider and replica
 

Metadata is not transferred between nodes.  What I mean by this is that nodes don't have a global view of the cluster
theyknow about their own subscriptions but nothing else.  This is different than a system like slony where sl_node and
sl_subscriptioncontain a global view of your cluster state.  Not sending metadata to all nodes in the cluster
simplifiesa bunch of things (you don't have to worry about sending metadata around and if a given piece of metadata is
stale)but the downside is that I think the tooling to perform a lot of cluster reconfigure operations will need to be a
lotsmarter.
 

Petr, and Craig have you thought about how you might support getting the cluster back into a sane state after a node
failswith minimal pain.
 
A lot of the reason why slony needs all this metadata is to support that kind of thing. I don't think we need this for
thefirst version but it would be nice to know that the design could accommodate such a thing.
 


4) Do we like the syntax

I think the big debate about syntax is do we want functions or pure SQL (ie CREATE SUBSCRIPTION default1
provider_dsn=...).If we want this as an extension
 
then it needs to be functions.  I support this decision I think the benefits of keeping pglogical as an extension is
theright tradeoff.  In a few releases we can always add SQL syntax if we want it to no longer be an extension
 

If I have any comments have on the names or arguments of individual functions I will send them later.


5) Is the maintenance burden of this patch too high

The contrib module is big, significantly bigger than most (all?) of the other contrib modules and that doesn't include
theoutput plugin.  I see a lot of potential use-cases that I think pglogical can (or will eventually) be able to handle
andI think that justifies the maintenance burden.  If others disagree they should speak up.
 

I am concerned about testing, I don't think the .sql based regression tests are going to adequately test a replication
systemthat supports concurrent activity on different databases/servers.  I remember hearing talk about a  python based
testsuite that was rejected in another thread.  Having perl tests that use -DBI has also been rejected.
 

The other day Tom made this comment as part of the 'Releasing in September' thread:

---
I do not think we should necessarily try to include every testing tool
in the core distribution.  What is important is that they be readily
available: easy to find, easy to use, documented, portable.
-----

The standard make contrib check tests need to do some testing of pglogical but I think it will require testing much
morethorough than what we can get with in core test tooling.  I think this is a good case to look at test tooling that
doesn'tlive in core.
 

Overall I am very impressed with pglogical and see a lot of potential