pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed[RFC][PATCH] Logical Replication/BDR prototype and architecture
19+ messages / 4 participants
[nested] [flat]
* [RFC][PATCH] Logical Replication/BDR prototype and architecture
@ 2012-06-13 11:27 Andres Freund <andres@2ndquadrant.com>
2012-06-13 15:53 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-13 17:11 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Steve Singer <steve@ssinger.info>
2012-06-13 17:52 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-14 16:17 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
0 siblings, 4 replies; 19+ messages in thread
From: Andres Freund @ 2012-06-13 11:27 UTC (permalink / raw)
To: pgsql-hackers
Hi everyone,
This mail contains the highlevel design description of how our prototype of
in-core logical replication works. The individual patches will be posted as
replies to this email. I obviously welcome all sorts of productive comments to
both the individual patches and the architecture.
Unless somebody objects I will add most of the individual marked as RFC to the
current commitfest. I hope that with comments stemming from that round we can
get several of the patches into the first or second commitfest. As soon as the
design is clear/accepted we will try very hard to get the following patches
into the second or third round.
If anybody disaggrees with the procedual way we try do this, please raise a hand
now.
I tried to find the right balance between keeping the description short enough
that anybody will read the design docs and verbose enough that it is
understandable. I can go into much more detail in any part if wanted.
Please, keep in mind that those patches are *RFC* and a prototype and not
intended to be applied as-is. There is a short description of the individual
patches and their relevancy at the end of the email.
Greetings,
Andres
========
=== Design goals for logical replication === :
- in core
- fast
- async
- robust
- multi-master
- modular
- as unintrusive as possible implementation wise
- basis for other technologies (sharding, replication into other DBMSs, ...)
For reasons why we think this is an important set of features please check out
the presentation from the in-core replication summit at pgcon:
http://wiki.postgresql.org/wiki/File:BDR_Presentation_PGCon2012.pdf
While you may argue that most of the above design goals are already provided by
various trigger based replication solutions like Londiste or Slony, we think
that thats not enough for various reasons:
- not in core (and thus less trustworthy)
- duplication of writes due to an additional log
- performance in general (check the end of the above presentation)
- complex to use because there is no native administration interface
We want to emphasize that this proposed architecture is based on the experience
of developing a minimal prototype which we developed with the above goals in
mind. While we obviously hope that a good part of it is reusable for the
community we definitely do *not* expect that the community accepts this
+as-is. It is intended to be the basis upon which we, the community, can build
and design the future logical replication.
=== Basic architecture === :
Very broadly speaking there are several major pieces common to most approaches
to replication:
1. Source data generation
2. Transportation of that data
3. Applying the changes
4. Conflict resolution
1.:
As we need a change stream that contains all required changes in the correct
order, the requirement for this stream to reflect changes across multiple
concurrent backends raises concurrency and scalability issues. Reusing the
WAL stream for this seems a good choice since it is needed anyway and adresses
those issues already, and it further means that we don't incur duplicate
writes. Any other stream generating componenent would introduce additional
scalability issues.
We need a change stream that contains all required changes in the correct order
which thus needs to be synchronized across concurrent backends which introduces
obvious concurrency/scalability issues.
Reusing the WAL stream for this seems a good choice since it is needed anyway
and adresses those issues already, and it further means we don't duplicate the
writes and locks already performance for its maintenance.
Unfortunately, in this case, the WAL is mostly a physical representation of the
changes and thus does not, by itself, contain the necessary information in a
convenient format to create logical changesets.
The biggest problem is, that interpreting tuples in the WAL stream requires an
up-to-date system catalog and needs to be done in a compatible backend and
architecture. The requirement of an up-to-date catalog could be solved by
adding more data to the WAL stream but it seems to be likely that that would
require relatively intrusive & complex changes. Instead we chose to require a
synchronized catalog at the decoding site. That adds some complexity to use
cases like replicating into a different database or cross-version
replication. For those it is relatively straight-forward to develop a proxy pg
instance that only contains the catalog and does the transformation to textual
changes.
This also is the solution to the other big problem, the need to work around
architecture/version specific binary formats. The alternative, producing
cross-version, cross-architecture compatible binary changes or even moreso
textual changes all the time seems to be prohibitively expensive. Both from a
cpu and a storage POV and also from the point of implementation effort.
The catalog on the site where changes originate can *not* be used for the
decoding because at the time we decode the WAL the catalog may have changed
from the state it was in when the WAL was generated. A possible solution for
this would be to have a fully versioned catalog but that again seems to be
rather complex and intrusive.
For some operations (UPDATE, DELETE) and corner-cases (e.g. full page writes)
additional data needs to be logged, but the additional amount of data isn't
that big. Requiring a primary-key for any change but INSERT seems to be a
sensible thing for now. The required changes are fully contained in heapam.c
and are pretty simple so far.
2.:
For transport of the non-decoded data from the originating site to the decoding
site we decided to reuse the infrastructure already provided by
walsender/walreceiver. We introduced a new command that, analogous to
START_REPLICATION, is called START_LOGICAL_REPLICATION that will stream out all
xlog records that pass through a filter.
The on-the-wire format stays the same. The filter currently simply filters out
all record which are not interesting for logical replication (indexes,
freezing, ...) and records that did not originate on the same system.
The requirement of filtering by 'origin' of a wal node comes from the planned
multimaster support. Changes replayed locally that originate from another site
should not replayed again there. If the wal is plainly used without such a
filter that would cause loops. Instead we tag every wal record with the "node
id" of the site that caused the change to happen and changes with a nodes own
"node id" won't get applied again.
Currently filtered records get simply replaced by NOOP records and loads of
zeroes which obviously is not a sensible solution. The difficulty of actually
removing the records is that that would change the LSNs. We currently rely on
those though.
The filtering might very well get expanded to support partial replication and
such in future.
3.:
To sensibly apply changes out of the WAL stream we need to solve two things:
Reassemble transactions and apply them to the target database.
The logical stream from 1. via 2. consists out of individual changes identified
by the relfilenode of the table and the xid of the transaction. Given
(sub)transactions, rollbacks, crash recovery, subtransactions and the like
those changes obviously cannot be individually applied without fully loosing
the pretence of consistency. To solve that we introduced a module, dubbed
ApplyCache which does the reassembling. This module is *independent* of the
data source and of the method of applying changes so it can be reused for
replicating into a foreign system or similar.
Due to the overhead of planner/executor/toast reassembly/type conversion (yes,
we benchmarked!) we decided against statement generation for apply. Even when
using prepared statements the overhead is rather noticeable.
Instead we decided to use relatively lowlevel heapam.h/genam.h accesses to do
the apply. For now we decided to use only one process to do the applying,
parallelizing that seems to be too complex for an introduction of an already
complex feature.
In our tests the apply process could keep up with pgbench -c/j 20+ generating
changes. This will obviously heavily depend on the workload. A fully seek bound
workload will definitely not scale that well.
Just to reiterate: Plugging in another method to do the apply should be a
relatively simple matter of setting up three callbacks to a different function
(begin, apply_change, commit).
Another complexity in this is how to synchronize the catalogs. We plan to use
command/event triggers and the oid preserving features from pg_upgrade to keep
the catalogs in-sync. We did not start working on that.
4.:
While we started to think about conflict resolution/avoidance we did not start
to work on it. We currently *cannot* handle conflicts. We think that the base
features/architecture should be aggreed uppon before starting with it.
Multimaster tests were done with sequences setup with INCREMENT 2 and different
start values on the two nodes.
=== Current Prototype ===
The current prototype consists of a series of patches that are split in
hopefully sensible and coherent parts to make reviewing of individual parts
possible.
Its also available in the 'cabal-rebasing' branch on
git.postgresql.org/users/andresfreund/postgres.git . That branch will modify
history though.
01: wakeup handling: reduces replication lag, not very interesting in this context
02: Add zeroRecPtr: not very interesting either
03: new syscache for relfilenode. This would benefit by some syscache experienced eyes
04: embedded lists: This is a general facility, general review appreciated
05: preliminary bgworker support: This is not ready and just posted as its
preliminary work for the other patches. Simon will post a real patch soon
06: XLogReader: Review definitely appreciated
07: logical data additions for WAL: Review definitely appreciated, I do not expect fundamental changes
08: ApplyCache: Important infrastructure for the patch, review definitely appreciated
09: Wal Decoding: Decode WAL generated with wal_level=logical into an ApplyCache
10: WAL with 'origin node': This is another important base-piece for logical rep
11: WAL segment handling changes: If the basic idea of adding a node_id to the
functions and adding a pg_lcr directory is acceptable the rest of the patch is
fairly boring/mechanical
12: walsender/walreceiver changes: Implement transport/filtering of logical
changes. Very relevant
13: shared memory/crash recovery state handling for logical rep: Very relevant
minus the TODO's in the commit message
14: apply module: review appreciated
15: apply process: somewhat dependent on the preliminary changes in 05, general
direction is visible, loads of detail work needed as soon as some design
decisions are agreed uppon.
16: this document. Not very interesting after youve read it ;)
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
@ 2012-06-13 15:53 ` Robert Haas <robertmhaas@gmail.com>
2012-06-13 16:16 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
3 siblings, 1 reply; 19+ messages in thread
From: Robert Haas @ 2012-06-13 15:53 UTC (permalink / raw)
To: Andres Freund <andres@2ndquadrant.com>; +Cc: pgsql-hackers
On Wed, Jun 13, 2012 at 7:27 AM, Andres Freund <andres@2ndquadrant.com> wrote:
> Unless somebody objects I will add most of the individual marked as RFC to the
> current commitfest. I hope that with comments stemming from that round we can
> get several of the patches into the first or second commitfest. As soon as the
> design is clear/accepted we will try very hard to get the following patches
> into the second or third round.
I made a "logical replication" topic within the CommitFest for this
patch series. I think you should add them all there. I have some
substantive thoughts about the design as well, which I will write up
in a separate email.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-13 15:53 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
@ 2012-06-13 16:16 ` Andres Freund <andres@2ndquadrant.com>
0 siblings, 0 replies; 19+ messages in thread
From: Andres Freund @ 2012-06-13 16:16 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Robert Haas <robertmhaas@gmail.com>
On Wednesday, June 13, 2012 05:53:31 PM Robert Haas wrote:
> On Wed, Jun 13, 2012 at 7:27 AM, Andres Freund <andres@2ndquadrant.com>
wrote:
> > Unless somebody objects I will add most of the individual marked as RFC
> > to the current commitfest. I hope that with comments stemming from that
> > round we can get several of the patches into the first or second
> > commitfest. As soon as the design is clear/accepted we will try very
> > hard to get the following patches into the second or third round.
>
> I made a "logical replication" topic within the CommitFest for this
> patch series. I think you should add them all there.
Thanks. Added all but the bgworker patch (which is not ready) and the
WalSndWakeup (different category) one which is not really relevant to the
topic.
I have the feeling I am due quite some reviewing this round...
> I have some substantive thoughts about the design as well, which I will
> write up in a separate email.
Thanks. Looking forward to it. At least now, before I have read it.
Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
@ 2012-06-13 17:11 ` Steve Singer <steve@ssinger.info>
2012-06-13 17:14 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-13 17:24 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
3 siblings, 2 replies; 19+ messages in thread
From: Steve Singer @ 2012-06-13 17:11 UTC (permalink / raw)
To: Andres Freund <andres@2ndquadrant.com>; +Cc: pgsql-hackers
On 12-06-13 07:27 AM, Andres Freund wrote:
> Its also available in the 'cabal-rebasing' branch on
> git.postgresql.org/users/andresfreund/postgres.git . That branch will modify
> history though.
>
That branch has a merge error in f685a11ce43b9694cbe61ffa42e396c9fbc32b05
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
-Wformat-security -fno-strict-aliasing -fwrapv -I../../../../src/include
-D_GNU_SOURCE -c -o xact.o xact.c
xact.c:4684: error: expected identifier or ‘(’ before ‘<<’ token
xact.c:4690:46: warning: character constant too long for its type
xact.c:4712:46: warning: character constant too long for its type
xact.c:4719: error: expected identifier or ‘(’ before ‘<<’ token
xact.c:4740:46: warning: character constant too long for its type
make[4]: *** [xact.o] Error 1
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-13 17:11 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Steve Singer <steve@ssinger.info>
@ 2012-06-13 17:14 ` Andres Freund <andres@2ndquadrant.com>
1 sibling, 0 replies; 19+ messages in thread
From: Andres Freund @ 2012-06-13 17:14 UTC (permalink / raw)
To: Steve Singer <steve@ssinger.info>; +Cc: pgsql-hackers
On Wednesday, June 13, 2012 07:11:40 PM Steve Singer wrote:
> On 12-06-13 07:27 AM, Andres Freund wrote:
> > Its also available in the 'cabal-rebasing' branch on
> > git.postgresql.org/users/andresfreund/postgres.git . That branch will
> > modify history though.
>
> That branch has a merge error in f685a11ce43b9694cbe61ffa42e396c9fbc32b05
>
>
> gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith
> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
> -Wformat-security -fno-strict-aliasing -fwrapv -I../../../../src/include
> -D_GNU_SOURCE -c -o xact.o xact.c
> xact.c:4684: error: expected identifier or ‘(’ before ‘<<’ token
> xact.c:4690:46: warning: character constant too long for its type
> xact.c:4712:46: warning: character constant too long for its type
> xact.c:4719: error: expected identifier or ‘(’ before ‘<<’ token
> xact.c:4740:46: warning: character constant too long for its type
> make[4]: *** [xact.o] Error 1
Aw crap. Will fix that. Sorry.
Andres
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-13 17:11 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Steve Singer <steve@ssinger.info>
@ 2012-06-13 17:24 ` Andres Freund <andres@2ndquadrant.com>
1 sibling, 0 replies; 19+ messages in thread
From: Andres Freund @ 2012-06-13 17:24 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Steve Singer <steve@ssinger.info>
On Wednesday, June 13, 2012 07:11:40 PM Steve Singer wrote:
> On 12-06-13 07:27 AM, Andres Freund wrote:
> > Its also available in the 'cabal-rebasing' branch on
> > git.postgresql.org/users/andresfreund/postgres.git . That branch will
> > modify history though.
>
> That branch has a merge error in f685a11ce43b9694cbe61ffa42e396c9fbc32b05
>
>
> gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith
> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
> -Wformat-security -fno-strict-aliasing -fwrapv -I../../../../src/include
> -D_GNU_SOURCE -c -o xact.o xact.c
> xact.c:4684: error: expected identifier or ‘(’ before ‘<<’ token
> xact.c:4690:46: warning: character constant too long for its type
> xact.c:4712:46: warning: character constant too long for its type
> xact.c:4719: error: expected identifier or ‘(’ before ‘<<’ token
> xact.c:4740:46: warning: character constant too long for its type
> make[4]: *** [xact.o] Error 1
Hrmpf. I reordered the patch series a last time to be more clear and I somehow
didn't notice this. I compiled & tested the now pushed head
(7e0340a3bef927f79b3d97a11f94ede4b911560c) and will submit an updated patch
[10/16].
Andres
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
@ 2012-06-13 17:52 ` Andres Freund <andres@2ndquadrant.com>
3 siblings, 0 replies; 19+ messages in thread
From: Andres Freund @ 2012-06-13 17:52 UTC (permalink / raw)
To: pgsql-hackers
Hi,
The patch as of yet doesn't contain how you actually can use the prototype...
Obviously at this point its not very convenient:
I have two config files:
Node 1:
port = 5501
wal_level = logical
max_wal_senders = 10
wal_keep_segments = 200
multimaster_conninfo = 'port=5502 host=/tmp'
multimaster_node_id = 1
Node 2:
port = 5502
wal_level = logical
max_wal_senders = 10
wal_keep_segments = 200
multimaster_conninfo = 'port=5501 host=/tmp'
multimaster_node_id = 2
after initdb'ing the first cluster (initdb required):
$ ~/src/postgresql/build/assert/src/backend/postgres -D
~/tmp/postgres/bdr/1/datadir/ -c
config_file=~/tmp/postgres/bdr/1/postgresql.conf -c
hba_file=~/tmp/postgres/bdr/1/pg_hba.conf -c
ident_file=~/tmp/postgres/bdr/1/pg_ident.conf
$ psql -p 5501 -U andres postgres
CREATE TABLE data(id serial primary key, data bigint);
ALTER SEQUENCE data_id_seq INCREMENT 2;
SELECT setval('data_id_seq', 1);
shutdown cluster
$ rsync -raxv --delete /home/andres/tmp/postgres/bdr/1/datadir/*
/home/andres/tmp/postgres/bdr/2/datadir
start both cluster which should sync after some output.
$ psql -p 5501 -U andres postgres
SELECT setval('data_id_seq', 2);
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
@ 2012-06-14 16:17 ` Robert Haas <robertmhaas@gmail.com>
2012-06-14 20:13 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
3 siblings, 1 reply; 19+ messages in thread
From: Robert Haas @ 2012-06-14 16:17 UTC (permalink / raw)
To: Andres Freund <andres@2ndquadrant.com>; +Cc: pgsql-hackers
On Wed, Jun 13, 2012 at 7:27 AM, Andres Freund <andres@2ndquadrant.com> wrote:
> === Design goals for logical replication === :
> - in core
> - fast
> - async
> - robust
> - multi-master
> - modular
> - as unintrusive as possible implementation wise
> - basis for other technologies (sharding, replication into other DBMSs, ...)
I agree with all of these goals except for "multi-master". I am not
sure that there is a need to have a multi-master replication solution
in core. The big tricky part of multi-master replication is conflict
resolution, and that seems like an awful lot of logic to try to build
into core - especially given that we will want it to be extensible.
More generally, I would much rather see us focus on efficiently
extracting changesets from WAL and efficiently applying those
changesets on another server. IMHO, those are the things that are
holding back the not-in-core replication solutions we have today,
particularly the first. If we come up with a better way of applying
change-sets, well, Slony can implement that too; they are already
installing C code. What neither they nor any other non-core solution
can implement is change-set extraction, and therefore I think that
ought to be the focus.
To put all that another way, I think it is a 100% bad idea to try to
kill off Slony, Bucardo, Londiste, or any of the many home-grown
solutions that are out there to do replication. Even if there were no
technical place for third-party replication products (and I think
there is), we will not win many friends by making it harder to extend
and add on to the server. If we build an in-core replication solution
that can be used all by itself, that is fine with me. But I think it
should also be able to expose its constituent parts as a toolkit for
third-party solutions.
> While you may argue that most of the above design goals are already provided by
> various trigger based replication solutions like Londiste or Slony, we think
> that thats not enough for various reasons:
>
> - not in core (and thus less trustworthy)
> - duplication of writes due to an additional log
> - performance in general (check the end of the above presentation)
> - complex to use because there is no native administration interface
I think that your parenthetical note "(and thus less trustworthy)"
gets at another very important point, which is that one of the
standards for inclusion in core is that it must in fact be trustworthy
enough to justify the confidence that users will place in it. It will
NOT benefit the project to have two replication solutions in core, one
of which is crappy. More, even if what we put in core is AS GOOD as
the best third-party solutions that are available, I don't think
that's adequate. It has to be better. If it isn't, there is no
excuse for preempting what's already out there.
I imagine you are thinking along similar lines, but I think that it
bears being explicit about.
> As we need a change stream that contains all required changes in the correct
> order, the requirement for this stream to reflect changes across multiple
> concurrent backends raises concurrency and scalability issues. Reusing the
> WAL stream for this seems a good choice since it is needed anyway and adresses
> those issues already, and it further means that we don't incur duplicate
> writes. Any other stream generating componenent would introduce additional
> scalability issues.
Agreed.
> We need a change stream that contains all required changes in the correct order
> which thus needs to be synchronized across concurrent backends which introduces
> obvious concurrency/scalability issues.
> Reusing the WAL stream for this seems a good choice since it is needed anyway
> and adresses those issues already, and it further means we don't duplicate the
> writes and locks already performance for its maintenance.
Agreed.
> Unfortunately, in this case, the WAL is mostly a physical representation of the
> changes and thus does not, by itself, contain the necessary information in a
> convenient format to create logical changesets.
Agreed.
> The biggest problem is, that interpreting tuples in the WAL stream requires an
> up-to-date system catalog and needs to be done in a compatible backend and
> architecture. The requirement of an up-to-date catalog could be solved by
> adding more data to the WAL stream but it seems to be likely that that would
> require relatively intrusive & complex changes. Instead we chose to require a
> synchronized catalog at the decoding site. That adds some complexity to use
> cases like replicating into a different database or cross-version
> replication. For those it is relatively straight-forward to develop a proxy pg
> instance that only contains the catalog and does the transformation to textual
> changes.
The actual requirement here is more complex than "an up-to-date
catalog". Suppose transaction X begins, adds a column to a table,
inserts a row, and commits. That tuple needs to be interpreted using
the tuple descriptor that transaction X would see (which includes the
new column), NOT the tuple descriptor that some other transaction
would see at the same time (which won't include the new column). In a
more complicated scenario, X might (1) begin, (2) start a
subtransaction that alters the table, (3) release the savepoint or
roll back to the save point, (4) insert a tuple, and (5) commit. Now,
the correct tuple descriptor for interpreting the tuple inserted in
step (4) depends on whether step (3) was a release savepoint or a
rollback-to-savepoint. How are you handling these (and similar but
more complex) cases?
Moreover, we will want in the future to allow some of the DDL changes
that currently require AccessExclusiveLock to be performed with a
lesser lock. It is unclear to me that this will be practical as far
as adding columns goes, but it would be a shame if logical replication
were the thing standing in the way. In that scenario, you might have:
transaction X begins a transaction, and adds a column; transaction Y
inserts a tuple which must be interpreted using the old tuple
descriptor (or maybe it's harmless to use the new one, since the extra
column will be interpreted as NULL anyway); transaction X inserts a
tuple (which MUST be interpreted using the new tuple descriptor); and
then X either commits or rolls back. This might be too hypothetical
to worry about in detail, but it would at least be nice to have the
sense that we're not totally screwed if the locking rules for DDL
change someday.
> This also is the solution to the other big problem, the need to work around
> architecture/version specific binary formats. The alternative, producing
> cross-version, cross-architecture compatible binary changes or even moreso
> textual changes all the time seems to be prohibitively expensive. Both from a
> cpu and a storage POV and also from the point of implementation effort.
I think that if you can't produce a textual record of changes, you're
throwing away 75% of what people will want to do with this. Being
able to replicate across architectures, versions, and even into
heterogeneous databases is the main point of having logical
replication, IMV. Multi-master replication is nice to have, but IME
there is huge demand for a replication solution that doesn't need to
be temporarily replaced with something completely different every time
you want to do a database upgrade.
> The catalog on the site where changes originate can *not* be used for the
> decoding because at the time we decode the WAL the catalog may have changed
> from the state it was in when the WAL was generated. A possible solution for
> this would be to have a fully versioned catalog but that again seems to be
> rather complex and intrusive.
Yes, that seems like a non-starter.
> For some operations (UPDATE, DELETE) and corner-cases (e.g. full page writes)
> additional data needs to be logged, but the additional amount of data isn't
> that big. Requiring a primary-key for any change but INSERT seems to be a
> sensible thing for now. The required changes are fully contained in heapam.c
> and are pretty simple so far.
I think that you can handle the case where there are no primary keys
by simply treating the whole record as a primary key. There might be
duplicates, but I think you can work around that by decreeing that
when you replay an UPDATE or DELETE operation you will update or
delete at most one record. So if there are multiple exactly-identical
records in the target database, then you will just UPDATE or DELETE
exactly one of them (it doesn't matter which). This doesn't even seem
particularly complicated.
> For transport of the non-decoded data from the originating site to the decoding
> site we decided to reuse the infrastructure already provided by
> walsender/walreceiver.
I think this is reasonable. Actually, I think we might want to
generalize this to a bunch of other stuff, too. There could be many
reasons to want a distributed cluster of PostgreSQL servers with some
amount of interconnect. We might want to think about renaming the
processes at some point, but that's probably putting the cart before
the horse just right now.
> We introduced a new command that, analogous to
> START_REPLICATION, is called START_LOGICAL_REPLICATION that will stream out all
> xlog records that pass through a filter.
>
> The on-the-wire format stays the same. The filter currently simply filters out
> all record which are not interesting for logical replication (indexes,
> freezing, ...) and records that did not originate on the same system.
>
> The requirement of filtering by 'origin' of a wal node comes from the planned
> multimaster support. Changes replayed locally that originate from another site
> should not replayed again there. If the wal is plainly used without such a
> filter that would cause loops. Instead we tag every wal record with the "node
> id" of the site that caused the change to happen and changes with a nodes own
> "node id" won't get applied again.
>
> Currently filtered records get simply replaced by NOOP records and loads of
> zeroes which obviously is not a sensible solution. The difficulty of actually
> removing the records is that that would change the LSNs. We currently rely on
> those though.
>
> The filtering might very well get expanded to support partial replication and
> such in future.
This all seems to need a lot more thought.
> To sensibly apply changes out of the WAL stream we need to solve two things:
> Reassemble transactions and apply them to the target database.
>
> The logical stream from 1. via 2. consists out of individual changes identified
> by the relfilenode of the table and the xid of the transaction. Given
> (sub)transactions, rollbacks, crash recovery, subtransactions and the like
> those changes obviously cannot be individually applied without fully loosing
> the pretence of consistency. To solve that we introduced a module, dubbed
> ApplyCache which does the reassembling. This module is *independent* of the
> data source and of the method of applying changes so it can be reused for
> replicating into a foreign system or similar.
>
> Due to the overhead of planner/executor/toast reassembly/type conversion (yes,
> we benchmarked!) we decided against statement generation for apply. Even when
> using prepared statements the overhead is rather noticeable.
>
> Instead we decided to use relatively lowlevel heapam.h/genam.h accesses to do
> the apply. For now we decided to use only one process to do the applying,
> parallelizing that seems to be too complex for an introduction of an already
> complex feature.
> In our tests the apply process could keep up with pgbench -c/j 20+ generating
> changes. This will obviously heavily depend on the workload. A fully seek bound
> workload will definitely not scale that well.
>
> Just to reiterate: Plugging in another method to do the apply should be a
> relatively simple matter of setting up three callbacks to a different function
> (begin, apply_change, commit).
I think it's reasonable to do the apply using these low-level
mechanisms, but surely people will sometimes want to extract tuples as
text and do whatever with them. This goes back to my comment about
feeling that we need a toolkit approach, not a one-size-fits-all
solution.
> Another complexity in this is how to synchronize the catalogs. We plan to use
> command/event triggers and the oid preserving features from pg_upgrade to keep
> the catalogs in-sync. We did not start working on that.
This strikes me as completely unacceptable. People ARE going to want
to replicate data between non-identical schemas on systems with
unsynchronized OIDs. And even if they weren't, relying on triggers to
keep things in sync is exactly the sort of kludge that has inspired
all sorts of frustration with our existing replication solutions.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-14 16:17 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
@ 2012-06-14 20:13 ` Andres Freund <andres@2ndquadrant.com>
2012-06-15 20:03 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Andres Freund @ 2012-06-14 20:13 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-hackers
Hi Robert,
Thanks for your answer.
On Thursday, June 14, 2012 06:17:26 PM Robert Haas wrote:
> On Wed, Jun 13, 2012 at 7:27 AM, Andres Freund <andres@2ndquadrant.com>
wrote:
> > === Design goals for logical replication === :
> > - in core
> > - fast
> > - async
> > - robust
> > - multi-master
> > - modular
> > - as unintrusive as possible implementation wise
> > - basis for other technologies (sharding, replication into other DBMSs,
> > ...)
>
> I agree with all of these goals except for "multi-master". I am not
> sure that there is a need to have a multi-master replication solution
> in core. The big tricky part of multi-master replication is conflict
> resolution, and that seems like an awful lot of logic to try to build
> into core - especially given that we will want it to be extensible.
I don't plan to throw in loads of conflict resolution smarts. The aim is to get
to the place where all the infrastructure is there so that a MM solution can
be built by basically plugging in a conflict resolution mechanism. Maybe
providing a very simple one.
I think without in-core support its really, really hard to build a sensible MM
implementation. Which doesn't mean it has to live entirely in core.
Loads of the use-cases we have seen lately have a relatively small, low-
conflict shared dataset and a far bigger sharded one. While that obviously
isn't the only relevant use case it is a senible important one.
> More generally, I would much rather see us focus on efficiently
> extracting changesets from WAL and efficiently applying those
> changesets on another server. IMHO, those are the things that are
> holding back the not-in-core replication solutions we have today,
> particularly the first. If we come up with a better way of applying
> change-sets, well, Slony can implement that too; they are already
> installing C code. What neither they nor any other non-core solution
> can implement is change-set extraction, and therefore I think that
> ought to be the focus.
It definitely is a very important focus. I don't think it is the only one. But
that doesn't seem to be a problem to me as long as everything is kept fairly
modular (which I tried rather hard to).
> To put all that another way, I think it is a 100% bad idea to try to
> kill off Slony, Bucardo, Londiste, or any of the many home-grown
> solutions that are out there to do replication. Even if there were no
> technical place for third-party replication products (and I think
> there is), we will not win many friends by making it harder to extend
> and add on to the server. If we build an in-core replication solution
> that can be used all by itself, that is fine with me. But I think it
> should also be able to expose its constituent parts as a toolkit for
> third-party solutions.
I agree 100%. Unfortunately I forgot to explictly make that point, but the
plan is definitely is to make the life of other replication solutions easier
not harder. I don't think there will ever be one replication solution that fits
every use-case perfectly.
At pgcon I talked with some of the slony guys and they were definetly
interested in the changeset generation and I have kept that in mind. If some
problems that need resolving indepently of that issue is resolved (namely DDL)
it shouldn't take much generating their output format. The 'apply' code is
fully abstracted and separted.
> > While you may argue that most of the above design goals are already
> > provided by various trigger based replication solutions like Londiste or
> > Slony, we think that thats not enough for various reasons:
> >
> > - not in core (and thus less trustworthy)
> > - duplication of writes due to an additional log
> > - performance in general (check the end of the above presentation)
> > - complex to use because there is no native administration interface
>
> I think that your parenthetical note "(and thus less trustworthy)"
> gets at another very important point, which is that one of the
> standards for inclusion in core is that it must in fact be trustworthy
> enough to justify the confidence that users will place in it. It will
> NOT benefit the project to have two replication solutions in core, one
> of which is crappy. More, even if what we put in core is AS GOOD as
> the best third-party solutions that are available, I don't think
> that's adequate. It has to be better. If it isn't, there is no
> excuse for preempting what's already out there.
I aggree that it has to be very good. *But* I think it is totally acceptable
if it doesn't have all the bells and whistles from the start. That would be a
sure road to disaster. For one implementing all that takes time and for
another the amount of discussions till we are there is rather huge.
> I imagine you are thinking along similar lines, but I think that it
> bears being explicit about.
Seems like were thinking along the same lines, yes.
> > The biggest problem is, that interpreting tuples in the WAL stream
> > requires an up-to-date system catalog and needs to be done in a
> > compatible backend and architecture. The requirement of an up-to-date
> > catalog could be solved by adding more data to the WAL stream but it
> > seems to be likely that that would require relatively intrusive &
> > complex changes. Instead we chose to require a synchronized catalog at
> > the decoding site. That adds some complexity to use cases like
> > replicating into a different database or cross-version replication. For
> > those it is relatively straight-forward to develop a proxy pg instance
> > that only contains the catalog and does the transformation to textual
> > changes.
> The actual requirement here is more complex than "an up-to-date
> catalog". Suppose transaction X begins, adds a column to a table,
> inserts a row, and commits. That tuple needs to be interpreted using
> the tuple descriptor that transaction X would see (which includes the
> new column), NOT the tuple descriptor that some other transaction
> would see at the same time (which won't include the new column). In a
> more complicated scenario, X might (1) begin, (2) start a
> subtransaction that alters the table, (3) release the savepoint or
> roll back to the save point, (4) insert a tuple, and (5) commit. Now,
> the correct tuple descriptor for interpreting the tuple inserted in
> step (4) depends on whether step (3) was a release savepoint or a
> rollback-to-savepoint. How are you handling these (and similar but
> more complex) cases?
I don't handle DDL at all yet. What I posted is a working, but early prototype
;). Building an simple prototype seemed like a good idea to get a feeling for
everything but solving *the* hairy problem without input from -hackers seemed
like a bad idea.
But I have thought about the issue for quite a while... The ApplyCache module
does reassemble wal records from one transaction into on coherent stream with
only changes from that transaction. Aborted transactions are thrown away.
So you can apply half a transaction, detect the changed tupledesc, and then
reapply the rest.
But all that is moot until we agree on how to handle DDL. More about that
further down.
> Moreover, we will want in the future to allow some of the DDL changes
> that currently require AccessExclusiveLock to be performed with a
> lesser lock. It is unclear to me that this will be practical as far
> as adding columns goes, but it would be a shame if logical replication
> were the thing standing in the way.
Well. Should it really come to that, which I don't think it will, I
personally don't have a big problem of making the relaxed rules only available
with a wal_level < WAL_LEVEL_LOGICAL.
> > This also is the solution to the other big problem, the need to work
> > around architecture/version specific binary formats. The alternative,
> > producing cross-version, cross-architecture compatible binary changes or
> > even moreso textual changes all the time seems to be prohibitively
> > expensive. Both from a cpu and a storage POV and also from the point of
> > implementation effort.
> I think that if you can't produce a textual record of changes, you're
> throwing away 75% of what people will want to do with this. Being
> able to replicate across architectures, versions, and even into
> heterogeneous databases is the main point of having logical
> replication, IMV. Multi-master replication is nice to have, but IME
> there is huge demand for a replication solution that doesn't need to
> be temporarily replaced with something completely different every time
> you want to do a database upgrade.
All I am talking about in the above paragraph is saying that we do not want to
change the wal into a cross-architecture, cross-version compatible format.
> > For some operations (UPDATE, DELETE) and corner-cases (e.g. full page
> > writes) additional data needs to be logged, but the additional amount of
> > data isn't that big. Requiring a primary-key for any change but INSERT
> > seems to be a sensible thing for now. The required changes are fully
> > contained in heapam.c and are pretty simple so far.
> I think that you can handle the case where there are no primary keys
> by simply treating the whole record as a primary key. There might be
> duplicates, but I think you can work around that by decreeing that
> when you replay an UPDATE or DELETE operation you will update or
> delete at most one record. So if there are multiple exactly-identical
> records in the target database, then you will just UPDATE or DELETE
> exactly one of them (it doesn't matter which). This doesn't even seem
> particularly complicated.
Hm. Yes, you could do that. But I have to say I don't really see a point.
Maybe the fact that I do envision multimaster systems at some point is
clouding my judgement though as its far less easy in that case.
It also complicates the wal format as you now need to specify whether you
transport a full or a primary-key only tuple...
> > We introduced a new command that, analogous to
> > START_REPLICATION, is called START_LOGICAL_REPLICATION that will stream
> > out all xlog records that pass through a filter.
> >
> > The on-the-wire format stays the same. The filter currently simply
> > filters out all record which are not interesting for logical replication
> > (indexes, freezing, ...) and records that did not originate on the same
> > system.
> >
> > The requirement of filtering by 'origin' of a wal node comes from the
> > planned multimaster support. Changes replayed locally that originate
> > from another site should not replayed again there. If the wal is plainly
> > used without such a filter that would cause loops. Instead we tag every
> > wal record with the "node id" of the site that caused the change to
> > happen and changes with a nodes own "node id" won't get applied again.
> >
> > Currently filtered records get simply replaced by NOOP records and loads
> > of zeroes which obviously is not a sensible solution. The difficulty of
> > actually removing the records is that that would change the LSNs. We
> > currently rely on those though.
> >
> > The filtering might very well get expanded to support partial replication
> > and such in future.
> This all seems to need a lot more thought.
Yes. I didn't want to make any decisions here which would probably be the
wrong ones anyway, so I went for the NOOP option for now.
Unless youre talking about the 'origin_id' concept - that seems to work out
very nicely with minimal amounts of code needed.
> > To sensibly apply changes out of the WAL stream we need to solve two
> > things: Reassemble transactions and apply them to the target database.
> >
> > The logical stream from 1. via 2. consists out of individual changes
> > identified by the relfilenode of the table and the xid of the
> > transaction. Given (sub)transactions, rollbacks, crash recovery,
> > subtransactions and the like those changes obviously cannot be
> > individually applied without fully loosing the pretence of consistency.
> > To solve that we introduced a module, dubbed ApplyCache which does the
> > reassembling. This module is *independent* of the data source and of the
> > method of applying changes so it can be reused for replicating into a
> > foreign system or similar.
> >
> > Due to the overhead of planner/executor/toast reassembly/type conversion
> > (yes, we benchmarked!) we decided against statement generation for
> > apply. Even when using prepared statements the overhead is rather
> > noticeable.
> >
> > Instead we decided to use relatively lowlevel heapam.h/genam.h accesses
> > to do the apply. For now we decided to use only one process to do the
> > applying, parallelizing that seems to be too complex for an introduction
> > of an already complex feature.
> > In our tests the apply process could keep up with pgbench -c/j 20+
> > generating changes. This will obviously heavily depend on the workload.
> > A fully seek bound workload will definitely not scale that well.
> >
> > Just to reiterate: Plugging in another method to do the apply should be a
> > relatively simple matter of setting up three callbacks to a different
> > function (begin, apply_change, commit).
>
> I think it's reasonable to do the apply using these low-level
> mechanisms, but surely people will sometimes want to extract tuples as
> text and do whatever with them. This goes back to my comment about
> feeling that we need a toolkit approach, not a one-size-fits-all
> solution.
Yes. We definitely need a toolkit approach. If you look at the individual
patches, especially the later ones, you can see that I tried very hard to go
that way.
The wal decoding (Patch 09) and transaction reassembly (Patch 08) and the low
level apply (Patch 14) parts are as separate as possible. That is 09 feeds
into an ApplyCache without knowing anything about the side which applies the
changes. As written above, you currently really only need to replace those 3
callbacks to produce text output instead.
That might get slightly more complex with the choice whether toast should be
reassembled in memory or not, but even then it should be pretty simple.
> > Another complexity in this is how to synchronize the catalogs. We plan to
> > use command/event triggers and the oid preserving features from
> > pg_upgrade to keep the catalogs in-sync. We did not start working on
> > that.
> This strikes me as completely unacceptable. People ARE going to want
> to replicate data between non-identical schemas on systems with
> unsynchronized OIDs. And even if they weren't, relying on triggers to
> keep things in sync is exactly the sort of kludge that has inspired
> all sorts of frustration with our existing replication solutions.
Yes. I definitely agree that people will want to do that. Hell, I want to do
that.
The plan for that is (see my mail to merlin about it) to have catalog-only
proxy instances. For those its even possible without much additonal problems
to use a pretty normal HS setup + filtering. Thats the only realistic way I
have found to do the necessary catalog lookups (TupleDescs + output
procedures) and handle the binary compatibility.
I think though that we do not want to enforce that mode of operation for
tightly coupled instances. For those I was thinking of using command triggers
to synchronize the catalogs.
One of the big screwups of the current replication solutions is exactly that
you cannot sensibly do DDL which is not a big problem if you have a huge
system with loads of different databases and very knowledgeable people et al.
but at the beginning it really sucks. I have no problem with making one of the
nodes the "schema master" in that case.
Also I would like to avoid the overhead of the proxy instance for use-cases
where you really want one node replicated as fully as possible with the slight
exception of being able to have summing tables, different indexes et al.
Does that make sense for you?
Greetings,
Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-14 16:17 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-14 20:13 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
@ 2012-06-15 20:03 ` Robert Haas <robertmhaas@gmail.com>
2012-06-15 20:18 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-16 11:43 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-16 19:03 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Steve Singer <steve@ssinger.info>
0 siblings, 3 replies; 19+ messages in thread
From: Robert Haas @ 2012-06-15 20:03 UTC (permalink / raw)
To: Andres Freund <andres@2ndquadrant.com>; +Cc: pgsql-hackers
On Thu, Jun 14, 2012 at 4:13 PM, Andres Freund <andres@2ndquadrant.com> wrote:
> I don't plan to throw in loads of conflict resolution smarts. The aim is to get
> to the place where all the infrastructure is there so that a MM solution can
> be built by basically plugging in a conflict resolution mechanism. Maybe
> providing a very simple one.
> I think without in-core support its really, really hard to build a sensible MM
> implementation. Which doesn't mean it has to live entirely in core.
Of course, several people have already done it, perhaps most notably Bucardo.
Anyway, it would be good to get opinions from more people here. I am
sure I am not the only person with an opinion on the appropriateness
of trying to build a multi-master replication solution in core or,
indeed, the only person with an opinion on any of these other issues.
It is not good for those other opinions to be saved for a later date.
> Hm. Yes, you could do that. But I have to say I don't really see a point.
> Maybe the fact that I do envision multimaster systems at some point is
> clouding my judgement though as its far less easy in that case.
Why? I don't think that particularly changes anything.
> It also complicates the wal format as you now need to specify whether you
> transport a full or a primary-key only tuple...
Why? If the schemas are in sync, the target knows what the PK is
perfectly well. If not, you're probably in trouble anyway.
> I think though that we do not want to enforce that mode of operation for
> tightly coupled instances. For those I was thinking of using command triggers
> to synchronize the catalogs.
> One of the big screwups of the current replication solutions is exactly that
> you cannot sensibly do DDL which is not a big problem if you have a huge
> system with loads of different databases and very knowledgeable people et al.
> but at the beginning it really sucks. I have no problem with making one of the
> nodes the "schema master" in that case.
> Also I would like to avoid the overhead of the proxy instance for use-cases
> where you really want one node replicated as fully as possible with the slight
> exception of being able to have summing tables, different indexes et al.
In my view, a logical replication solution is precisely one in which
the catalogs don't need to be in sync. If the catalogs have to be in
sync, it's not logical replication. ISTM that what you're talking
about is sort of a hybrid between physical replication (pages) and
logical replication (tuples) - you want to ship around raw binary
tuple data, but not entire pages. The problem with that is it's going
to be tough to make robust. Users could easily end up with answers
that are total nonsense, or probably even crash the server.
To step back and talk about DDL more generally, you've mentioned a few
times the idea of using an SR instance that has been filtered down to
just the system catalogs as a means of generating logical change
records. However, as things stand today, there's no reason to suppose
that replicating anything less than the entire cluster is sufficient.
For example, you can't translate enum labels to strings without access
to the pg_enum catalog, which would be there, because enums are
built-in types. But someone could supply a similar user-defined type
that uses a user-defined table to do those lookups, and now you've got
a problem. I think this is a contractual problem, not a technical
one. From the point of view of logical replication, it would be nice
if type output functions were basically guaranteed to look at nothing
but the datum they get passed as an argument, or at the very least
nothing other than the system catalogs, but there is no such
guarantee. And, without such a guarantee, I don't believe that we can
create a high-performance, robust, in-core replication solution.
Now, the nice thing about being the people who make PostgreSQL happen
is we get to decide what the C code that people load into the server
is required to guarantee; we can change the rules. Before, types were
allowed to do X, but now they're not. Unfortunately, in this case, I
don't really find that an acceptable solution. First, it might break
code that has worked with PostgreSQL for many years; but worse, it
won't break it in any obvious way, but rather only if you're using
logical replication, which will doubtless cause people to attribute
the failure to logical replication rather than to their own code.
Even if they do understand that we imposed a rule-change from on high,
there's no really good workaround: an enum type is a good example of
something that you *can't* implement without a side-table. Second, it
flies in the face of our often-stated desire to make the server
extensible. Also, even given the existence of such a restriction, you
still need to run any output function that relies on catalogs with
catalog contents that match what existed at the time that WAL was
generated, and under the correct snapshot, which is not trivial.
These things are problems even for other things that we might need to
do while examining the WAL stream, but they're particularly acute for
any application that wants to run type-output functions to generate
something that can be sent to a server which doesn't necessarily
having matching catalog contents.
But it strikes me that these things, really, are only a problem for a
minority of data types. For text, or int4, or float8, or even
timestamptz, we don't need *any catalog contents at all* to
reconstruct the tuple data. Knowing the correct type alignment and
which C function to call is entirely sufficient. So maybe instead of
trying to cobble together a set of catalog contents that we can use
for decoding any tuple whatsoever, we should instead divide the world
into well-behaved types and poorly-behaved types. Well-behaved types
are those that can be interpreted without the catalogs, provided that
you know what type it is. Poorly-behaved types (records, enums) are
those where you can't. For well-behaved types, we only need a small
amount of additional information in WAL to identify which types we're
trying to decode (not the type OID, which might fail in the presence
of nasty catalog hacks, but something more universal, like a UUID that
means "this is text", or something that identifies the C entrypoint).
And then maybe we handle poorly-behaved types by pushing some of the
work into the foreground task that's generating the WAL: in the worst
case, the process logs a record before each insert/update/delete
containing the text representation of any values that are going to be
hard to decode. In some cases (e.g. records all of whose constituent
fields are well-behaved types) we could instead log enough additional
information about the type to permit blind decoding.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-14 16:17 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-14 20:13 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-15 20:03 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
@ 2012-06-15 20:18 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2 siblings, 0 replies; 19+ messages in thread
From: Kevin Grittner @ 2012-06-15 20:18 UTC (permalink / raw)
To: Andres Freund <andres@2ndquadrant.com>; Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-hackers
Robert Haas <robertmhaas@gmail.com> wrote:
> So maybe instead of trying to cobble together a set of catalog
> contents that we can use for decoding any tuple whatsoever, we
> should instead divide the world into well-behaved types and
> poorly-behaved types. Well-behaved types are those that can be
> interpreted without the catalogs, provided that you know what type
> it is. Poorly-behaved types (records, enums) are those where you
> can't. For well-behaved types, we only need a small amount of
> additional information in WAL to identify which types we're trying
> to decode (not the type OID, which might fail in the presence of
> nasty catalog hacks, but something more universal, like a UUID
> that means "this is text", or something that identifies the C
> entrypoint). And then maybe we handle poorly-behaved types by
> pushing some of the work into the foreground task that's
> generating the WAL: in the worst case, the process logs a record
> before each insert/update/delete containing the text
> representation of any values that are going to be hard to decode.
> In some cases (e.g. records all of whose constituent fields are
> well-behaved types) we could instead log enough additional
> information about the type to permit blind decoding.
What about matching those values up to the correct table name and
the respective columns names?
-Kevin
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-14 16:17 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-14 20:13 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-15 20:03 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
@ 2012-06-16 11:43 ` Andres Freund <andres@2ndquadrant.com>
2012-06-19 01:20 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Andres Freund @ 2012-06-16 11:43 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Robert Haas <robertmhaas@gmail.com>
Hi Robert,
On Friday, June 15, 2012 10:03:38 PM Robert Haas wrote:
> On Thu, Jun 14, 2012 at 4:13 PM, Andres Freund <andres@2ndquadrant.com>
wrote:
> > I don't plan to throw in loads of conflict resolution smarts. The aim is
> > to get to the place where all the infrastructure is there so that a MM
> > solution can be built by basically plugging in a conflict resolution
> > mechanism. Maybe providing a very simple one.
> > I think without in-core support its really, really hard to build a
> > sensible MM implementation. Which doesn't mean it has to live entirely
> > in core.
>
> Of course, several people have already done it, perhaps most notably
> Bucardo.
Bucardo certainly is nice but its not useable for many things just from an
overhead perspective.
> Anyway, it would be good to get opinions from more people here. I am
> sure I am not the only person with an opinion on the appropriateness
> of trying to build a multi-master replication solution in core or,
> indeed, the only person with an opinion on any of these other issues.
> It is not good for those other opinions to be saved for a later date.
Agreed.
> > Hm. Yes, you could do that. But I have to say I don't really see a point.
> > Maybe the fact that I do envision multimaster systems at some point is
> > clouding my judgement though as its far less easy in that case.
> Why? I don't think that particularly changes anything.
Because it makes conflict detection very hard. I also don't think its a
feature worth supporting. Whats the use-case of updating records you cannot
properly identify?
> > It also complicates the wal format as you now need to specify whether you
> > transport a full or a primary-key only tuple...
> Why? If the schemas are in sync, the target knows what the PK is
> perfectly well. If not, you're probably in trouble anyway.
True. There already was the wish (from Kevin) of having the option of
transporting full before/after images anyway, so the wal format might want to
be able to represent that.
> > I think though that we do not want to enforce that mode of operation for
> > tightly coupled instances. For those I was thinking of using command
> > triggers to synchronize the catalogs.
> > One of the big screwups of the current replication solutions is exactly
> > that you cannot sensibly do DDL which is not a big problem if you have a
> > huge system with loads of different databases and very knowledgeable
> > people et al. but at the beginning it really sucks. I have no problem
> > with making one of the nodes the "schema master" in that case.
> > Also I would like to avoid the overhead of the proxy instance for
> > use-cases where you really want one node replicated as fully as possible
> > with the slight exception of being able to have summing tables,
> > different indexes et al.
> In my view, a logical replication solution is precisely one in which
> the catalogs don't need to be in sync. If the catalogs have to be in
> sync, it's not logical replication. ISTM that what you're talking
> about is sort of a hybrid between physical replication (pages) and
> logical replication (tuples) - you want to ship around raw binary
> tuple data, but not entire pages.
Ok, thats a valid point. Simon argued at the cluster summit that everything
thats not physical is logical. Which has some appeal because it seems hard to
agree what exactly logical rep is. So definition by exclusion makes kind of
sense ;)
I think what you categorized as "hybrid logical/physical" rep solves an
important use-case thats very hard to solve at the moment. Before my
2ndquadrant days I had several client which had huge problemsing the trigger
based solutions because their overhead simply was to big a burden on the
master. They couldn't use SR either because every consuming database kept
loads of local data.
I think such scenarios are getting more and more common.
> The problem with that is it's going to be tough to make robust. Users could
> easily end up with answers that are total nonsense, or probably even crash
> the server.
Why?
> To step back and talk about DDL more generally, you've mentioned a few
> times the idea of using an SR instance that has been filtered down to
> just the system catalogs as a means of generating logical change
> records. However, as things stand today, there's no reason to suppose
> that replicating anything less than the entire cluster is sufficient.
> For example, you can't translate enum labels to strings without access
> to the pg_enum catalog, which would be there, because enums are
> built-in types. But someone could supply a similar user-defined type
> that uses a user-defined table to do those lookups, and now you've got
> a problem. I think this is a contractual problem, not a technical
> one. From the point of view of logical replication, it would be nice
> if type output functions were basically guaranteed to look at nothing
> but the datum they get passed as an argument, or at the very least
> nothing other than the system catalogs, but there is no such
> guarantee. And, without such a guarantee, I don't believe that we can
> create a high-performance, robust, in-core replication solution.
I don't think thats a valid argument. Any such solution existing today fails
to work properly with dump/restore and such because it implies dependencies
that they do not know about. The "internal" tables will possibly be restored
later than the tables using the tables and such. So your data format *has* to
deal with loading/outputting data without such anyway.
Some of that can be ameliorated using extensions + configuration tables but
even then you have to be *very* careful and plan your backup/restore
procedures way much more careful then when not.
> Now, the nice thing about being the people who make PostgreSQL happen
> is we get to decide what the C code that people load into the server
> is required to guarantee; we can change the rules. Before, types were
> allowed to do X, but now they're not. Unfortunately, in this case, I
> don't really find that an acceptable solution. First, it might break
> code that has worked with PostgreSQL for many years; but worse, it
> won't break it in any obvious way, but rather only if you're using
> logical replication, which will doubtless cause people to attribute
> the failure to logical replication rather than to their own code.
As I said above, anybody using code like that has to be aware of the problem
anyway. Should there really be real cases of that marking configuration tables
in the catalog as to be shared would be relatively uncomplicated solution.
> Even if they do understand that we imposed a rule-change from on high,
> there's no really good workaround: an enum type is a good example of
> something that you *can't* implement without a side-table.
Enums are a good example of a intrusive feature which breaks features we have
come to rely on (transactional DDL) which could not be implemented outside of
core pg.
And yes, you obviously can implement it without needing side-table for output.
Just as a string which is checked during input.
> Second, it flies in the face of our often-stated desire to make the server
> extensible.
While I generally see that desire as something worthwile I don't see it being
violated in this case. The amount of extensibility youre removing here is
minimal in my opinion and solutions for it aren't that hard. Solving them (aka
marking those tables as some form of secondary system tables) would make that
code actually reliable.
Sorry, I remain highly unconvinced of the above argumentation.
> Also, even given the existence of such a restriction, you
> still need to run any output function that relies on catalogs with
> catalog contents that match what existed at the time that WAL was
> generated, and under the correct snapshot, which is not trivial.
> These things are problems even for other things that we might need to
> do while examining the WAL stream, but they're particularly acute for
> any application that wants to run type-output functions to generate
> something that can be sent to a server which doesn't necessarily
> having matching catalog contents.
I don't think its that hard. And as you say, we need to solve it anyway.
> But it strikes me that these things, really, are only a problem for a
> minority of data types. For text, or int4, or float8, or even
> timestamptz, we don't need *any catalog contents at all* to
> reconstruct the tuple data. Knowing the correct type alignment and
> which C function to call is entirely sufficient. So maybe instead of
> trying to cobble together a set of catalog contents that we can use
> for decoding any tuple whatsoever, we should instead divide the world
> into well-behaved types and poorly-behaved types. Well-behaved types
> are those that can be interpreted without the catalogs, provided that
> you know what type it is. Poorly-behaved types (records, enums) are
> those where you can't. For well-behaved types, we only need a small
> amount of additional information in WAL to identify which types we're
> trying to decode (not the type OID, which might fail in the presence
> of nasty catalog hacks, but something more universal, like a UUID that
> means "this is text", or something that identifies the C entrypoint).
This would essentially double the size of the WAL even for rows containing
only simple types and would mean we would run quite a bit of non-trivial code
additionally in relatively critical parts of the code. Both imnsho is
unacceptable.
You could reduce the space overhead by only adding that information only the
first time after a table has changed (and then regularly after a checkpoint or
so) but doing so seems to be introducing too much complexity.
> And then maybe we handle poorly-behaved types by pushing some of the
> work into the foreground task that's generating the WAL: in the worst
> case, the process logs a record before each insert/update/delete
> containing the text representation of any values that are going to be
> hard to decode. In some cases (e.g. records all of whose constituent
> fields are well-behaved types) we could instead log enough additional
> information about the type to permit blind decoding.
I think this is prohibitively expensive from a development, runtime, space and
maintenance standpoint.
For databases using thing were decoding is rather expensive (e.g. postgis) you
wouldn't really improve much above the old trigger based solutions. Its a
return to "log everything twice".
Sorry if I seem pigheaded here, but I fail to see why all that would buy us
anything but loads of complexity while loosing many potential advantages.
Greetings,
Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-14 16:17 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-14 20:13 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-15 20:03 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-16 11:43 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
@ 2012-06-19 01:20 ` Robert Haas <robertmhaas@gmail.com>
2012-06-19 18:23 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
0 siblings, 1 reply; 19+ messages in thread
From: Robert Haas @ 2012-06-19 01:20 UTC (permalink / raw)
To: Andres Freund <andres@2ndquadrant.com>; +Cc: pgsql-hackers
On Sat, Jun 16, 2012 at 7:43 AM, Andres Freund <andres@2ndquadrant.com> wrote:
>> > Hm. Yes, you could do that. But I have to say I don't really see a point.
>> > Maybe the fact that I do envision multimaster systems at some point is
>> > clouding my judgement though as its far less easy in that case.
>> Why? I don't think that particularly changes anything.
> Because it makes conflict detection very hard. I also don't think its a
> feature worth supporting. Whats the use-case of updating records you cannot
> properly identify?
Don't ask me; I just work here. I think it's something that some
people want, though. I mean, if you don't support replicating a table
without a primary key, then you can't even run pgbench in a
replication environment. Is that an important workload? Well,
objectively, no. But I guarantee you that other people with more
realistic workloads than that will complain if we don't have it.
Absolutely required on day one? Probably not. Completely useless
appendage that no one wants? Not that, either.
>> In my view, a logical replication solution is precisely one in which
>> the catalogs don't need to be in sync. If the catalogs have to be in
>> sync, it's not logical replication. ISTM that what you're talking
>> about is sort of a hybrid between physical replication (pages) and
>> logical replication (tuples) - you want to ship around raw binary
>> tuple data, but not entire pages.
> Ok, thats a valid point. Simon argued at the cluster summit that everything
> thats not physical is logical. Which has some appeal because it seems hard to
> agree what exactly logical rep is. So definition by exclusion makes kind of
> sense ;)
Well, the words are fuzzy, but I would define logical replication to
be something which is independent of the binary format in which stuff
gets stored on disk. If it's not independent of the disk format, then
you can't do heterogenous replication (between versions, or between
products). That precise limitation is the main thing that drives
people to use anything other than SR in the first place, IME.
> I think what you categorized as "hybrid logical/physical" rep solves an
> important use-case thats very hard to solve at the moment. Before my
> 2ndquadrant days I had several client which had huge problemsing the trigger
> based solutions because their overhead simply was to big a burden on the
> master. They couldn't use SR either because every consuming database kept
> loads of local data.
> I think such scenarios are getting more and more common.
I think this is to some extent true, but I also think you're
conflating two different things. Change extraction via triggers
introduces overhead that can be eliminated by reconstructing tuples
from WAL in the background rather than forcing them to be inserted
into a shadow table (and re-WAL-logged!) in the foreground. I will
grant that shipping the tuple as a binary blob rather than as text
eliminates additional overehead on both ends, but it also closes off a
lot of important use cases. As I noted in my previous email, I think
that ought to be a performance optimization that we do, if at all,
when it's been proven safe, not a baked-in part of the design. Even a
solution that decodes WAL to text tuples and ships those around and
reinserts the via pure SQL should be significantly faster than the
replication solutions we have today; if it isn't, something's wrong.
>> The problem with that is it's going to be tough to make robust. Users could
>> easily end up with answers that are total nonsense, or probably even crash
>> the server.
> Why?
Because the routines that decode tuples don't include enough sanity
checks to prevent running off the end of the block, or even the end of
memory completely. Consider a corrupt TOAST pointer that indicates
that there is a gigabyte of data stored in an 8kB block. One of the
common symptoms of corruption IME is TOAST requests for -3 bytes of
memory.
And, of course, even if you could avoid crashing, interpreting what
was originally intended as a series of int4s as a varlena isn't likely
to produce anything terribly meaningful. Tuple data isn't
self-identifying; that's why this is such a hard problem.
>> To step back and talk about DDL more generally, you've mentioned a few
>> times the idea of using an SR instance that has been filtered down to
>> just the system catalogs as a means of generating logical change
>> records. However, as things stand today, there's no reason to suppose
>> that replicating anything less than the entire cluster is sufficient.
>> For example, you can't translate enum labels to strings without access
>> to the pg_enum catalog, which would be there, because enums are
>> built-in types. But someone could supply a similar user-defined type
>> that uses a user-defined table to do those lookups, and now you've got
>> a problem. I think this is a contractual problem, not a technical
>> one. From the point of view of logical replication, it would be nice
>> if type output functions were basically guaranteed to look at nothing
>> but the datum they get passed as an argument, or at the very least
>> nothing other than the system catalogs, but there is no such
>> guarantee. And, without such a guarantee, I don't believe that we can
>> create a high-performance, robust, in-core replication solution.
> I don't think thats a valid argument. Any such solution existing today fails
> to work properly with dump/restore and such because it implies dependencies
> that they do not know about. The "internal" tables will possibly be restored
> later than the tables using the tables and such. So your data format *has* to
> deal with loading/outputting data without such anyway.
Do you know for certain that PostGIS doesn't do anything of this type?
Or what about something like an SE-Linux label cache, where we might
arrange to create labels as they are used and associate them with
integer tags?
> And yes, you obviously can implement it without needing side-table for output.
> Just as a string which is checked during input.
That misses the point - if people wanted labels represented by a
string rather than an integer, they would have just used a string and
stuffed a check or foreign key constraint in there.
> You could reduce the space overhead by only adding that information only the
> first time after a table has changed (and then regularly after a checkpoint or
> so) but doing so seems to be introducing too much complexity.
Well, I dunno: it is complicated, but I'm worried that the design
you've got is awfully complicated, too. Requiring an extra PG
instance with a very specific configuration that furthermore uses an
untested WAL-filtering methodology that excludes everything but the
system catalogs seems like an administrative nightmare, and I remain
unconvinced that it is safe. In fact, I have a strong feeling that it
isn't safe, but if you're not convinced by the argument already laid
out then I'm not sure I can convince you of it right this minute.
What happens if you have a crash on the WAL generation machine?
You'll have to rewind to the most recent restartpoint, and you can't
use the catalogs until you've reached the minimum recovery point. Is
that going to mess you up?
>> And then maybe we handle poorly-behaved types by pushing some of the
>> work into the foreground task that's generating the WAL: in the worst
>> case, the process logs a record before each insert/update/delete
>> containing the text representation of any values that are going to be
>> hard to decode. In some cases (e.g. records all of whose constituent
>> fields are well-behaved types) we could instead log enough additional
>> information about the type to permit blind decoding.
> I think this is prohibitively expensive from a development, runtime, space and
> maintenance standpoint.
> For databases using thing were decoding is rather expensive (e.g. postgis) you
> wouldn't really improve much above the old trigger based solutions. Its a
> return to "log everything twice".
Well, if the PostGIS types are poorly behaved under the definition I
proposed, that implies they won't work at all under your scheme. I
think putting a replication solution into core that won't support
PostGIS is dead on arrival. If they're well-behaved, then there's no
double-logging.
> Sorry if I seem pigheaded here, but I fail to see why all that would buy us
> anything but loads of complexity while loosing many potential advantages.
The current system that you are proposing is very complex and has a
number of holes at present, some of which you've already mentioned in
previous emails. There's a lot of advantage in picking a design that
allows you to put together a working prototype relatively quickly, but
I have a sinking feeling that your chosen design is going to be very
hard to bullet-proof and not very user-friendly. If we could find a
way to run it all inside a single server I think we would be way ahead
on both fronts.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-14 16:17 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-14 20:13 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-15 20:03 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-16 11:43 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-19 01:20 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
@ 2012-06-19 18:23 ` Andres Freund <andres@2ndquadrant.com>
2012-06-19 21:36 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-20 01:08 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
0 siblings, 2 replies; 19+ messages in thread
From: Andres Freund @ 2012-06-19 18:23 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-hackers
Hi,
The most important part, even for people not following my discussion with
Robert is at the bottom where the possible wal decoding strategies are laid
out.
On Tuesday, June 19, 2012 03:20:58 AM Robert Haas wrote:
> On Sat, Jun 16, 2012 at 7:43 AM, Andres Freund <andres@2ndquadrant.com>
wrote:
> >> > Hm. Yes, you could do that. But I have to say I don't really see a
> >> > point. Maybe the fact that I do envision multimaster systems at some
> >> > point is clouding my judgement though as its far less easy in that
> >> > case.
> >>
> >> Why? I don't think that particularly changes anything.
> >
> > Because it makes conflict detection very hard. I also don't think its a
> > feature worth supporting. Whats the use-case of updating records you
> > cannot properly identify?
> Don't ask me; I just work here. I think it's something that some
> people want, though. I mean, if you don't support replicating a table
> without a primary key, then you can't even run pgbench in a
> replication environment.
Well, I have no problem with INSERT only tables not having a PK. And
pgbench_history is the only pgbench table that doesn't have a pkey? And thats
only truncated...
> Is that an important workload? Well,
> objectively, no. But I guarantee you that other people with more
> realistic workloads than that will complain if we don't have it.
> Absolutely required on day one? Probably not. Completely useless
> appendage that no one wants? Not that, either.
Maybe. And I don't really care, so if others see that as important I am happy
to appease them ;).
> >> In my view, a logical replication solution is precisely one in which
> >> the catalogs don't need to be in sync. If the catalogs have to be in
> >> sync, it's not logical replication. ISTM that what you're talking
> >> about is sort of a hybrid between physical replication (pages) and
> >> logical replication (tuples) - you want to ship around raw binary
> >> tuple data, but not entire pages.
> > Ok, thats a valid point. Simon argued at the cluster summit that
> > everything thats not physical is logical. Which has some appeal because
> > it seems hard to agree what exactly logical rep is. So definition by
> > exclusion makes kind of sense ;)
> Well, the words are fuzzy, but I would define logical replication to
> be something which is independent of the binary format in which stuff
> gets stored on disk. If it's not independent of the disk format, then
> you can't do heterogenous replication (between versions, or between
> products). That precise limitation is the main thing that drives
> people to use anything other than SR in the first place, IME.
Not in mine. The main limitation I see is that you cannot write anything on
the standby. Which sucks majorly for many things. Its pretty much impossible
to "fix" that for SR outside of very limited cases.
While many scenarios don't need multimaster *many* need to write outside of
the standby's replication set.
> > I think what you categorized as "hybrid logical/physical" rep solves an
> > important use-case thats very hard to solve at the moment. Before my
> > 2ndquadrant days I had several client which had huge problemsing the
> > trigger based solutions because their overhead simply was to big a
> > burden on the master. They couldn't use SR either because every
> > consuming database kept loads of local data.
> > I think such scenarios are getting more and more common.
> I think this is to some extent true, but I also think you're
> conflating two different things. Change extraction via triggers
> introduces overhead that can be eliminated by reconstructing tuples
> from WAL in the background rather than forcing them to be inserted
> into a shadow table (and re-WAL-logged!) in the foreground. I will
> grant that shipping the tuple as a binary blob rather than as text
> eliminates additional overehead on both ends, but it also closes off a
> lot of important use cases. As I noted in my previous email, I think
> that ought to be a performance optimization that we do, if at all,
> when it's been proven safe, not a baked-in part of the design. Even a
> solution that decodes WAL to text tuples and ships those around and
> reinserts the via pure SQL should be significantly faster than the
> replication solutions we have today; if it isn't, something's wrong.
Its not only the logging side which is a limitation in todays replication
scenarios. The apply side scales even worse because its *very* hard to
distribute it between multiple backends.
> >> The problem with that is it's going to be tough to make robust. Users
> >> could easily end up with answers that are total nonsense, or probably
> >> even crash the server.
> > Why?
> Because the routines that decode tuples don't include enough sanity
> checks to prevent running off the end of the block, or even the end of
> memory completely. Consider a corrupt TOAST pointer that indicates
> that there is a gigabyte of data stored in an 8kB block. One of the
> common symptoms of corruption IME is TOAST requests for -3 bytes of
> memory.
Yes, but we need to put safeguards against that sort of thing anyway. So sure,
we can have bugs but this is not a fundamental limitation.
> And, of course, even if you could avoid crashing, interpreting what
> was originally intended as a series of int4s as a varlena isn't likely
> to produce anything terribly meaningful. Tuple data isn't
> self-identifying; that's why this is such a hard problem.
Yes, sure.
> >> To step back and talk about DDL more generally, you've mentioned a few
> >> times the idea of using an SR instance that has been filtered down to
> >> just the system catalogs as a means of generating logical change
> >> records. However, as things stand today, there's no reason to suppose
> >> that replicating anything less than the entire cluster is sufficient.
> >> For example, you can't translate enum labels to strings without access
> >> to the pg_enum catalog, which would be there, because enums are
> >> built-in types. But someone could supply a similar user-defined type
> >> that uses a user-defined table to do those lookups, and now you've got
> >> a problem. I think this is a contractual problem, not a technical
> >> one. From the point of view of logical replication, it would be nice
> >> if type output functions were basically guaranteed to look at nothing
> >> but the datum they get passed as an argument, or at the very least
> >> nothing other than the system catalogs, but there is no such
> >> guarantee. And, without such a guarantee, I don't believe that we can
> >> create a high-performance, robust, in-core replication solution.
> >
> > I don't think thats a valid argument. Any such solution existing today
> > fails to work properly with dump/restore and such because it implies
> > dependencies that they do not know about. The "internal" tables will
> > possibly be restored later than the tables using the tables and such. So
> > your data format *has* to deal with loading/outputting data without such
> > anyway.
> Do you know for certain that PostGIS doesn't do anything of this type?
> Or what about something like an SE-Linux label cache, where we might
> arrange to create labels as they are used and associate them with
> integer tags?
Postgis uses one information table in a few more complex functions but not in
anything low-level. Evidenced by the fact that it was totally normal for that
to go out of sync before < 2.0.
But even if such a thing would be needed, it wouldn't be problematic to make
extension configuration tables be replicated as well.
> > You could reduce the space overhead by only adding that information only
> > the first time after a table has changed (and then regularly after a
> > checkpoint or so) but doing so seems to be introducing too much
> > complexity.
> Well, I dunno: it is complicated, but I'm worried that the design
> you've got is awfully complicated, too. Requiring an extra PG
> instance with a very specific configuration that furthermore uses an
> untested WAL-filtering methodology that excludes everything but the
> system catalogs seems like an administrative nightmare, and I remain
> unconvinced that it is safe.
Well, I assumed we would have a utility to create such an instance via the
replication protocol which does allt he ncessary thing.
I personally don't want to do this in the very first version of an applied
patch. I think solving this on a binary level, without decoding is the
implementation wise smallest, already usable set of features. Yes, it doesn't
provide many things people want (cross arch, replication into text, ...) but
it provides most of the infrastructure for that. If we try to do everything at
once we will *never* get anywhere.
The wal filtering machinery is a very nice building block to improve the SR
experience as well btw, by removing unneeded full page writes, filtering on
one database and such.
> In fact, I have a strong feeling that it isn't safe, but if you're not
> convinced by the argument already laid out then I'm not sure I can convince
> you of it right this minute.
> What happens if you have a crash on the WAL generation machine?
> You'll have to rewind to the most recent restartpoint, and you can't
> use the catalogs until you've reached the minimum recovery point. Is
> that going to mess you up?
With "Wal generation machine" you mean the catalog-only instance?
If yes: Very good point.
The only real problem I know with that are checkpoints with overflowed subxid
snapshots. Otherwise you always can restart with the last checkpoint and
exactly replay to the former location. The minimum recovery point cannot be
ahead the point you just replayed. Everytime the recovery point is updated you
need to serialize the applycache to disk but thats already required to be
possible because otherwise you would need unbounded amounts of memory.
For the overflowed snapshot problem I had toyed with the idea of simply
serializing the state of the KnownAssignedXids machinery when replaying
checkpoints and at RecoveryPoints to solve that issue after the initial clone
for HS in general.
> >> And then maybe we handle poorly-behaved types by pushing some of the
> >> work into the foreground task that's generating the WAL: in the worst
> >> case, the process logs a record before each insert/update/delete
> >> containing the text representation of any values that are going to be
> >> hard to decode. In some cases (e.g. records all of whose constituent
> >> fields are well-behaved types) we could instead log enough additional
> >> information about the type to permit blind decoding.
> >
> > I think this is prohibitively expensive from a development, runtime,
> > space and maintenance standpoint.
> > For databases using thing were decoding is rather expensive (e.g.
> > postgis) you wouldn't really improve much above the old trigger based
> > solutions. Its a return to "log everything twice".
>
> Well, if the PostGIS types are poorly behaved under the definition I
> proposed, that implies they won't work at all under your scheme. I
> think putting a replication solution into core that won't support
> PostGIS is dead on arrival. If they're well-behaved, then there's no
> double-logging.
I am pretty sure its not bad-behaved. But how should the code know that? You
want each type to explictly say that its unsafe if it is?
In that case you just as well can mark all such tables which would also make
reliable pg_dump/restore possible. Assuming you buy the duplicated catalog
idea. Which you do not ;)
> > Sorry if I seem pigheaded here, but I fail to see why all that would buy
> > us anything but loads of complexity while loosing many potential
> > advantages.
> The current system that you are proposing is very complex and has a
> number of holes at present, some of which you've already mentioned in
> previous emails. There's a lot of advantage in picking a design that
> allows you to put together a working prototype relatively quickly, but
> I have a sinking feeling that your chosen design is going to be very
> hard to bullet-proof and not very user-friendly. If we could find a
> way to run it all inside a single server I think we would be way ahead
> on both fronts.
Don't get me wrong: If I believed another approach would be realistic I would
definitely prefer not going for that. I do find the requirement of keeping
multiple copies of the catalog arround pretty damn ugly.
The problem is just that to support basically arbitrary decoding requirements
you need to provide at least those pieces of information in a transactionally
consistent manner:
* the data
* table names
* column names
* type information
* replication configuration
I have yet to see anybody point out a way to provide all that information
without huge problems with the complexity/space-usage/performance triangle.
I have played with several ideas:
1.)
keep the decoding catalog in sync with command/event triggers, correctly
replicating oids. If those log into some internal event table its easy to keep
the catalog in a correct transactional state because the events from that
table get decoded in the transaction and replayed at exactly the right spot in
there *after* it has been reassembled. The locking on the generating side
takes care of the concurrency aspects.
Advantages:
* minimal overhead (space, performance)
* allows additional tables/indexes/triggers if you take care with oid
allocation
* easy transactionally correct catalog behaviour behaviour with catalogs
* the decoding instance can be used to store all data in a highly efficient
manner (no decoding, no full detoasting, ...)
* the decoding instance is fully writable without problems if you don't
generate conflicts (separate tables, non-overlapping writes, whatever)
* implementable in a pretty unintrusive way
Disadvantes:
* the table structure of replicated tables needs to be *exactly* the same
* the type definition + support procs needs to be similar enough to read the
data
* error checking of the above isn't easy
* full version/architecture compatibility required
* a second instance required even if you want to replicate into some other
system/architecture/verison
2.)
Keep the decoding site up2date by replicating the catalog via normal recovery
mechanisms
Advantages:
* most of the technology is already there
* minimal overhead (space, performance)
* no danger of out of sync catalogs
* no support for command triggers required that can keep a catalog in sync,
including oids
Disadvantages:
* driving the catalog recovery that way requires some somewhat intricate code
as it needs to be done in lockstep with decoding the wal-stream
* requires an additional feature to guarantee HS always has enough information
to be queryable
* some complex logic/low-level fudging required to keep the transactional
behaviour sensible when querying the catalog
* fully version/architecture compatibility required
* the decoding site will always ever be only readable
3.)
Fully versioned catalog
Advantages:
* Decoding is done on the master in an asynchronous fashion
* low overhead during normal DML execution, not much additional code in that
path
* can be very efficient if architecture/version are the same
* version/architecture compatibility can be done transparently by falling back
to textual versions on mismatch
Disadvantages:
* catalog versioning is complex to implement
* space overhead for all users, even without using logical replication
* I can't see -hackers signing off
* decoding has to happen on the master which might not be what people want
performancewise
4.)
Log enough information in the walstream to make decoding possible using only
the walstream.
Advantages:
* Decoding can optionally be done on the master
* No catalog syncing/access required
* its possible to make this architecture independent
Disadvantage:
* high to very high implementation overhead depending on efficiency aims
* high space overhead in the wal because at least all the catalog information
needs to be logged in a transactional manner repeatedly
* misuses wal far more than other methods
* significant new complexity in somewhat cricital code paths (heapam.c)
* insanely high space overhead if the decoding should be possible architecture
independent
5.)
The actually good idea. Yours?
Greetings,
Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-14 16:17 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-14 20:13 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-15 20:03 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-16 11:43 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-19 01:20 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-19 18:23 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
@ 2012-06-19 21:36 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 19+ messages in thread
From: Kevin Grittner @ 2012-06-19 21:36 UTC (permalink / raw)
To: Andres Freund <andres@2ndquadrant.com>; Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-hackers
Andres Freund <andres@2ndquadrant.com> wrote:
> The problem is just that to support basically arbitrary decoding
> requirements you need to provide at least those pieces of
> information in a transactionally consistent manner:
> * the data
> * table names
> * column names
> * type information
> * replication configuration
I'm not sure that the last one needs to be in scope for the WAL
stream, but the others I definitely agree eventually need to be
available to a logical transaction stream consumer. You lay out the
alternative ways to get all of this pretty clearly, and I don't know
what the best answer is; it seems likely that there is not one best
answer. In the long run, more than one of those options might need
to be supported, to support different environments.
As an initial implementation, I'm leaning toward the position that
requiring a hot standby or a catalog-only proxy is acceptable. I
think that should allow an application to be written which emits
everything except the replication configuration. That will allow us
to hook up everything we need at our shop.
-Kevin
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-14 16:17 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-14 20:13 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-15 20:03 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-16 11:43 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-19 01:20 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-19 18:23 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
@ 2012-06-20 01:08 ` Robert Haas <robertmhaas@gmail.com>
2012-06-20 10:15 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
1 sibling, 1 reply; 19+ messages in thread
From: Robert Haas @ 2012-06-20 01:08 UTC (permalink / raw)
To: Andres Freund <andres@2ndquadrant.com>; +Cc: pgsql-hackers
On Tue, Jun 19, 2012 at 2:23 PM, Andres Freund <andres@2ndquadrant.com> wrote:
>> Well, the words are fuzzy, but I would define logical replication to
>> be something which is independent of the binary format in which stuff
>> gets stored on disk. If it's not independent of the disk format, then
>> you can't do heterogenous replication (between versions, or between
>> products). That precise limitation is the main thing that drives
>> people to use anything other than SR in the first place, IME.
> Not in mine. The main limitation I see is that you cannot write anything on
> the standby. Which sucks majorly for many things. Its pretty much impossible
> to "fix" that for SR outside of very limited cases.
> While many scenarios don't need multimaster *many* need to write outside of
> the standby's replication set.
Well, that's certainly a common problem, even if it's not IME the most
common, but I don't think we need to argue about which one is more
common, because I'm not arguing against it. The point, though, is
that if the logical format is independent of the on-disk format, the
things we can do are a strict superset of the things we can do if it
isn't. I don't want to insist that catalogs be the same (or else you
get garbage when you decode tuples). I want to tolerate the fact that
they may very well be different. That will in no way preclude writing
outside the standby's replication set, nor will it prevent
multi-master replication. It will, however, enable heterogenous
replication, which is a very important use case. It will also mean
that innocent mistakes (like somehow ending up with a column that is
text on one server and numeric on another server) produce
comprehensible error messages, rather than garbage.
> Its not only the logging side which is a limitation in todays replication
> scenarios. The apply side scales even worse because its *very* hard to
> distribute it between multiple backends.
I don't think that making LCR format = on-disk format is going to
solve that problem. To solve that problem, we need to track
dependencies between transactions, so that if tuple A is modified by
T1 and T2, in that order, we apply T1 before T2. But if T3 - which
committed after both T1 and T2 - touches none of the same data as T1
or T2 - then we can apply it in parallel, so long as we don't commit
until T1 and T2 have committed (because allowing T3 to commit early
would produce a serialization anomaly from the point of view of a
concurrent reader).
>> Because the routines that decode tuples don't include enough sanity
>> checks to prevent running off the end of the block, or even the end of
>> memory completely. Consider a corrupt TOAST pointer that indicates
>> that there is a gigabyte of data stored in an 8kB block. One of the
>> common symptoms of corruption IME is TOAST requests for -3 bytes of
>> memory.
> Yes, but we need to put safeguards against that sort of thing anyway. So sure,
> we can have bugs but this is not a fundamental limitation.
There's a reason we haven't done that already, though: it's probably
going to stink for performance. If it turns out that it doesn't stink
for performance, great. But if it causes a 5% slowdown on common use
cases, I suspect we're not gonna do it, and I bet I can construct a
case where it's worse than that (think: 400 column table with lots of
varlenas, sorting by column 400 to return column 399). I think it's
treading on dangerous ground to assume we're going to be able to "just
go fix" this.
> Postgis uses one information table in a few more complex functions but not in
> anything low-level. Evidenced by the fact that it was totally normal for that
> to go out of sync before < 2.0.
>
> But even if such a thing would be needed, it wouldn't be problematic to make
> extension configuration tables be replicated as well.
Ugh. That's a hack on top of a hack. Now it all works great if type
X is installed as an extension but if it isn't installed as an
extension then the world blows up.
> I am pretty sure its not bad-behaved. But how should the code know that? You
> want each type to explictly say that its unsafe if it is?
Yes, exactly. Or maybe there are varying degrees of non-safety,
allowing varying degrees of optimization. Like: wire format = binary
format is super-safe. Then having to call an I/O function that
promises not to look at any catalogs is a bit less safe. And then
there's really unsafe.
> I have played with several ideas:
>
> 1.)
> keep the decoding catalog in sync with command/event triggers, correctly
> replicating oids. If those log into some internal event table its easy to keep
> the catalog in a correct transactional state because the events from that
> table get decoded in the transaction and replayed at exactly the right spot in
> there *after* it has been reassembled. The locking on the generating side
> takes care of the concurrency aspects.
I am not following this one completely.
> 2.)
> Keep the decoding site up2date by replicating the catalog via normal recovery
> mechanisms
This surely seems better than #1, since it won't do amazingly weird
things if the user bypasses the event triggers.
> 3.)
> Fully versioned catalog
One possible way of doing this would be to have the LCR generator run
on the primary, but hold back RecentGlobalXmin until it's captured the
information that it needs. It seems like as long as tuples can't get
pruned, the information you need must still be there, as long as you
can figure out which snapshot you need to read it under. But since
you know the commit ordering, it seems like you ought to be able to
figure out what SnapshotNow would have looked like at any given point
in the WAL stream. So you could, at that point in the WAL stream,
read the master's catalogs under what we might call SnapshotThen.
> 4.)
> Log enough information in the walstream to make decoding possible using only
> the walstream.
>
> Advantages:
> * Decoding can optionally be done on the master
> * No catalog syncing/access required
> * its possible to make this architecture independent
>
> Disadvantage:
> * high to very high implementation overhead depending on efficiency aims
> * high space overhead in the wal because at least all the catalog information
> needs to be logged in a transactional manner repeatedly
> * misuses wal far more than other methods
> * significant new complexity in somewhat cricital code paths (heapam.c)
> * insanely high space overhead if the decoding should be possible architecture
> independent
I'm not really convinced that the WAL overhead has to be that much
with this method. Most of the information you need about the catalogs
only needs to be logged when it changes, or once per checkpoint cycle,
or once per transaction, or once per transaction per checkpoint cycle.
I will concede that it looks somewhat complex, but I am not convinced
that it's undoable.
> 5.)
> The actually good idea. Yours?
Hey, look, an elephant!
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-14 16:17 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-14 20:13 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-15 20:03 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-16 11:43 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-19 01:20 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-19 18:23 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-20 01:08 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
@ 2012-06-20 10:15 ` Andres Freund <andres@2ndquadrant.com>
0 siblings, 0 replies; 19+ messages in thread
From: Andres Freund @ 2012-06-20 10:15 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-hackers
Hi Robert, Hi All!
On Wednesday, June 20, 2012 03:08:48 AM Robert Haas wrote:
> On Tue, Jun 19, 2012 at 2:23 PM, Andres Freund <andres@2ndquadrant.com>
wrote:
> >> Well, the words are fuzzy, but I would define logical replication to
> >> be something which is independent of the binary format in which stuff
> >> gets stored on disk. If it's not independent of the disk format, then
> >> you can't do heterogenous replication (between versions, or between
> >> products). That precise limitation is the main thing that drives
> >> people to use anything other than SR in the first place, IME.
> >
> > Not in mine. The main limitation I see is that you cannot write anything
> > on the standby. Which sucks majorly for many things. Its pretty much
> > impossible to "fix" that for SR outside of very limited cases.
> > While many scenarios don't need multimaster *many* need to write outside
> > of the standby's replication set.
> Well, that's certainly a common problem, even if it's not IME the most
> common, but I don't think we need to argue about which one is more
> common, because I'm not arguing against it. The point, though, is
> that if the logical format is independent of the on-disk format, the
> things we can do are a strict superset of the things we can do if it
> isn't. I don't want to insist that catalogs be the same (or else you
> get garbage when you decode tuples). I want to tolerate the fact that
> they may very well be different. That will in no way preclude writing
> outside the standby's replication set, nor will it prevent
> multi-master replication. It will, however, enable heterogenous
> replication, which is a very important use case. It will also mean
> that innocent mistakes (like somehow ending up with a column that is
> text on one server and numeric on another server) produce
> comprehensible error messages, rather than garbage.
I agree with most of that. I think that some parts of the above need to be
optional because you do loose too much for other scenarious.
I *definitely* want to build the *infrastructure* which make it easy to
implement all of the above but I find it a bit much to require that from the
get-go. Its important that everything is reusable for that, yes. Does a
patchset that wants to implement tightly coupled multimaster need to implement
everything for that? No.
If we raise the barrier for anything around this topic so high we will *NEVER*
get anywhere. Its a huge topic with loads of people wanting loads of different
things. And that will hurt people wanting some feature which matches 90% of
the proposed goals *far* more.
> > Its not only the logging side which is a limitation in todays replication
> > scenarios. The apply side scales even worse because its *very* hard to
> > distribute it between multiple backends.
> I don't think that making LCR format = on-disk format is going to
> solve that problem. To solve that problem, we need to track
> dependencies between transactions, so that if tuple A is modified by
> T1 and T2, in that order, we apply T1 before T2. But if T3 - which
> committed after both T1 and T2 - touches none of the same data as T1
> or T2 - then we can apply it in parallel, so long as we don't commit
> until T1 and T2 have committed (because allowing T3 to commit early
> would produce a serialization anomaly from the point of view of a
> concurrent reader).
Well, doing apply on such low level, without reencoding the data increased
throughput nearly threefold even for trivial types. So it pushes of the point
where we need to do the above quite a bit.
> >> Because the routines that decode tuples don't include enough sanity
> >> checks to prevent running off the end of the block, or even the end of
> >> memory completely. Consider a corrupt TOAST pointer that indicates
> >> that there is a gigabyte of data stored in an 8kB block. One of the
> >> common symptoms of corruption IME is TOAST requests for -3 bytes of
> >> memory.
> > Yes, but we need to put safeguards against that sort of thing anyway. So
> > sure, we can have bugs but this is not a fundamental limitation.
> There's a reason we haven't done that already, though: it's probably
> going to stink for performance. If it turns out that it doesn't stink
> for performance, great. But if it causes a 5% slowdown on common use
> cases, I suspect we're not gonna do it, and I bet I can construct a
> case where it's worse than that (think: 400 column table with lots of
> varlenas, sorting by column 400 to return column 399). I think it's
> treading on dangerous ground to assume we're going to be able to "just
> go fix" this.
I am talking about ensuring that the catalog is the same on the decoding site
not about making all decoding totally safe in the face of corrupted
information.
> > Postgis uses one information table in a few more complex functions but
> > not in anything low-level. Evidenced by the fact that it was totally
> > normal for that to go out of sync before < 2.0.
> >
> > But even if such a thing would be needed, it wouldn't be problematic to
> > make extension configuration tables be replicated as well.
> Ugh. That's a hack on top of a hack. Now it all works great if type
> X is installed as an extension but if it isn't installed as an
> extension then the world blows up.
Then introduce a storage attribute (or something similar) which does say the
same.
> > I have played with several ideas:
> >
> > 1.)
> > keep the decoding catalog in sync with command/event triggers, correctly
> > replicating oids. If those log into some internal event table its easy to
> > keep the catalog in a correct transactional state because the events
> > from that table get decoded in the transaction and replayed at exactly
> > the right spot in there *after* it has been reassembled. The locking on
> > the generating side takes care of the concurrency aspects.
> I am not following this one completely.
If (and yes, thats a somewhat big if) we had event triggers which can
reconstruct equivalent DDL statements with some additions to preserve oids you
can keep a 2nd catalog in sync. That catalog can be part of a full database or
just a decoding instance.
If those event triggers log into some system table the wal entries of those
INSERTS will be at the exactly right point in the wal stream *and* at the
right point in the transaction to apply that change exactly when you decode
(or apply) the wal contents after reassembling transactions.
That makes most (all?) of the syscache/snapshot catalog consistency problems
go away.
> > 2.)
> > Keep the decoding site up2date by replicating the catalog via normal
> > recovery mechanisms
> This surely seems better than #1, since it won't do amazingly weird
> things if the user bypasses the event triggers.
It has the disadvantage that it cannot be used to keep tightly coupled
instances in sync without a proxy instance inbetween.
Why should the user be able to bypass event triggers? If we design event
triggers to be bypassable by anything but eplicit actions from a superuser we
have made grave design errors.
Sure, a superuser can screw that, but then, he already has *loads* of things
he can do to corrupt an instance.
> > 3.)
> > Fully versioned catalog
> One possible way of doing this would be to have the LCR generator run
> on the primary, but hold back RecentGlobalXmin until it's captured the
> information that it needs. It seems like as long as tuples can't get
> pruned, the information you need must still be there, as long as you
> can figure out which snapshot you need to read it under. But since
> you know the commit ordering, it seems like you ought to be able to
> figure out what SnapshotNow would have looked like at any given point
> in the WAL stream. So you could, at that point in the WAL stream,
> read the master's catalogs under what we might call SnapshotThen.
Yes, I considered it before but never got comfortable enough with the idea.
Sounds like it would involve some trickery but it might be possible. I am
happy to go that route if people tentatively agree that the resulting
ugliness/intricate code is acceptable. Sounds like fun.
> > 4.)
> > Log enough information in the walstream to make decoding possible using
> > only the walstream.
> >
> > Advantages:
> > * Decoding can optionally be done on the master
> > * No catalog syncing/access required
> > * its possible to make this architecture independent
> >
> > Disadvantage:
> > * high to very high implementation overhead depending on efficiency aims
> > * high space overhead in the wal because at least all the catalog
> > information needs to be logged in a transactional manner repeatedly
> > * misuses wal far more than other methods
> > * significant new complexity in somewhat cricital code paths (heapam.c)
> > * insanely high space overhead if the decoding should be possible
> > architecture independent
>
> I'm not really convinced that the WAL overhead has to be that much
> with this method. Most of the information you need about the catalogs
> only needs to be logged when it changes, or once per checkpoint cycle,
> or once per transaction, or once per transaction per checkpoint cycle.
> I will concede that it looks somewhat complex, but I am not convinced
> that it's undoable.
I am not saying its impossible to achieve only moderate space overhead , but I
have a hard time believing its possible to do this in a manner thats
realistically implementable *and* acceptable for Tom.
I think I am more worried about the complexities introduced than the space
overhead...
> > 5.)
> > The actually good idea. Yours?
> Hey, look, an elephant!
One can dream...
Andres
--
Andres Freund http://www.2ndQuadrant.com/ PostgreSQL
Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-14 16:17 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-14 20:13 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-15 20:03 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
@ 2012-06-16 19:03 ` Steve Singer <steve@ssinger.info>
2012-06-19 00:52 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Steve Singer @ 2012-06-16 19:03 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Andres Freund <andres@2ndquadrant.com>; pgsql-hackers
On 12-06-15 04:03 PM, Robert Haas wrote:
> On Thu, Jun 14, 2012 at 4:13 PM, Andres Freund<andres@2ndquadrant.com> wrote:
>> I don't plan to throw in loads of conflict resolution smarts. The aim is to get
>> to the place where all the infrastructure is there so that a MM solution can
>> be built by basically plugging in a conflict resolution mechanism. Maybe
>> providing a very simple one.
>> I think without in-core support its really, really hard to build a sensible MM
>> implementation. Which doesn't mean it has to live entirely in core.
> Of course, several people have already done it, perhaps most notably Bucardo.
>
> Anyway, it would be good to get opinions from more people here. I am
> sure I am not the only person with an opinion on the appropriateness
> of trying to build a multi-master replication solution in core or,
> indeed, the only person with an opinion on any of these other issues.
This sounds like a good place for me to chime in.
I feel that in-core support to capture changes and turn them into change
records that can be replayed on other databases, without relying on
triggers and log tables, would be good to have.
I think we want some flexible enough that people write consumers of the
LCRs to do conflict resolution for multi-master but I am not sure that
the conflict resolution support actually belongs in core.
Most of the complexity of slony (both in terms of lines of code, and
issues people encounter using it) comes not from the log triggers or
replay of the logged data but comes from the configuration of the cluster.
Controlling things like
* Which tables replicate from a node to which other nodes
* How do you change the cluster configuration on a running system
(adding nodes, removing nodes, moving the origin of a table, adding
tables to replication etc...)
This is the harder part of the problem, I think we need to first get the
infrastructure committed (that the current patch set deals with) to
capturing, transporting and translating the LCR's into the system before
get too caught up in the configuration aspects. I think we will have a
hard time agreeing on behaviours for some of that other stuff that are
both flexible for enough use cases and simple enough for
administrators. I'd like to see in-core support for a lot of that stuff
but I'm not holding my breath.
> It is not good for those other opinions to be saved for a later date.
>
>> Hm. Yes, you could do that. But I have to say I don't really see a point.
>> Maybe the fact that I do envision multimaster systems at some point is
>> clouding my judgement though as its far less easy in that case.
> Why? I don't think that particularly changes anything.
>
>> It also complicates the wal format as you now need to specify whether you
>> transport a full or a primary-key only tuple...
> Why? If the schemas are in sync, the target knows what the PK is
> perfectly well. If not, you're probably in trouble anyway.
>
>> I think though that we do not want to enforce that mode of operation for
>> tightly coupled instances. For those I was thinking of using command triggers
>> to synchronize the catalogs.
>> One of the big screwups of the current replication solutions is exactly that
>> you cannot sensibly do DDL which is not a big problem if you have a huge
>> system with loads of different databases and very knowledgeable people et al.
>> but at the beginning it really sucks. I have no problem with making one of the
>> nodes the "schema master" in that case.
>> Also I would like to avoid the overhead of the proxy instance for use-cases
>> where you really want one node replicated as fully as possible with the slight
>> exception of being able to have summing tables, different indexes et al.
> In my view, a logical replication solution is precisely one in which
> the catalogs don't need to be in sync. If the catalogs have to be in
> sync, it's not logical replication. ISTM that what you're talking
> about is sort of a hybrid between physical replication (pages) and
> logical replication (tuples) - you want to ship around raw binary
> tuple data, but not entire pages. The problem with that is it's going
> to be tough to make robust. Users could easily end up with answers
> that are total nonsense, or probably even crash the server.
>
I see three catalogs in play here.
1. The catalog on the origin
2. The catalog on the proxy system (this is the catalog used to
translate the WAL records to LCR's). The proxy system will need
essentially the same pgsql binaries (same architecture, important
complie flags etc..) as the origin
3. The catalog on the destination system(s).
The catalog 2 must be in sync with catalog 1, catalog 3 shouldn't need
to be in-sync with catalog 1. I think catalogs 2 and 3 are combined in
the current patch set (though I haven't yet looked at the code
closely). I think the performance optimizations Andres has implemented
to update tuples through low-level functions should be left for later
and that we should be generating SQL in the apply cache so we don't
start assuming much about catalog 3.
> guarantee. And, without such a guarantee, I don't believe that we can
> create a high-performance, robust, in-core replication solution.
>
>
Part of what people expect from a robust in-core solution is that it
should work with the the other in-core features. If we have to list a
bunch of in-core type as being incompatible with logical replication
then people will look at logical replication with the same 'there be
dragons here' attitude that scare many people away from the existing
third party replication solutions. Non-core or third party user
defined types are a slightly different matter because we can't control
what they do.
Steve
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-14 16:17 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-14 20:13 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-15 20:03 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Robert Haas <robertmhaas@gmail.com>
2012-06-16 19:03 ` Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture Steve Singer <steve@ssinger.info>
@ 2012-06-19 00:52 ` Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Robert Haas @ 2012-06-19 00:52 UTC (permalink / raw)
To: Steve Singer <steve@ssinger.info>; +Cc: Andres Freund <andres@2ndquadrant.com>; pgsql-hackers
On Sat, Jun 16, 2012 at 3:03 PM, Steve Singer <steve@ssinger.info> wrote:
> I feel that in-core support to capture changes and turn them into change
> records that can be replayed on other databases, without relying on triggers
> and log tables, would be good to have.
>
> I think we want some flexible enough that people write consumers of the LCRs
> to do conflict resolution for multi-master but I am not sure that the
> conflict resolution support actually belongs in core.
I agree, on both counts. Anyone else want to chime in here?
> Most of the complexity of slony (both in terms of lines of code, and issues
> people encounter using it) comes not from the log triggers or replay of the
> logged data but comes from the configuration of the cluster.
> Controlling things like
>
> * Which tables replicate from a node to which other nodes
> * How do you change the cluster configuration on a running system (adding
> nodes, removing nodes, moving the origin of a table, adding tables to
> replication etc...)
Not being as familiar with Slony as I probably ought to be, I hadn't
given this much thought, but it's an interesting point. The number of
logical replication policies that someone might want to implement, and
the ways in which they might want to change them as the situation
develops, is very large. Whole cluster, whole database, one or
several schemas, individual tables, perhaps even more fine-grained
than per-table. Trying to figure all of that out is going to require
a lot of work and, frankly, I question the value of having that stuff
in core anyway.
> I see three catalogs in play here.
> 1. The catalog on the origin
> 2. The catalog on the proxy system (this is the catalog used to translate
> the WAL records to LCR's). The proxy system will need essentially the same
> pgsql binaries (same architecture, important complie flags etc..) as the
> origin
> 3. The catalog on the destination system(s).
>
> The catalog 2 must be in sync with catalog 1, catalog 3 shouldn't need to be
> in-sync with catalog 1. I think catalogs 2 and 3 are combined in the
> current patch set (though I haven't yet looked at the code closely). I
> think the performance optimizations Andres has implemented to update tuples
> through low-level functions should be left for later and that we should be
> generating SQL in the apply cache so we don't start assuming much about
> catalog 3.
+1. Although there is a lot of performance benefit to be had there,
it seems better to me to get the basics working and then do
performance optimization later. That is, if we can detect that the
catalogs are in sync, then by all means ship around the binary tuple
to make things faster. But requiring that (without having any way to
know whether it actually holds) strikes me as a mess.
> Part of what people expect from a robust in-core solution is that it should
> work with the the other in-core features. If we have to list a bunch of
> in-core type as being incompatible with logical replication then people will
> look at logical replication with the same 'there be dragons here' attitude
> that scare many people away from the existing third party replication
> solutions. Non-core or third party user defined types are a slightly
> different matter because we can't control what they do.
I agree, although I don't think either Andres or I are saying anything else.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2012-06-20 10:15 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-13 11:27 [RFC][PATCH] Logical Replication/BDR prototype and architecture Andres Freund <andres@2ndquadrant.com>
2012-06-13 15:53 ` Robert Haas <robertmhaas@gmail.com>
2012-06-13 16:16 ` Andres Freund <andres@2ndquadrant.com>
2012-06-13 17:11 ` Steve Singer <steve@ssinger.info>
2012-06-13 17:14 ` Andres Freund <andres@2ndquadrant.com>
2012-06-13 17:24 ` Andres Freund <andres@2ndquadrant.com>
2012-06-13 17:52 ` Andres Freund <andres@2ndquadrant.com>
2012-06-14 16:17 ` Robert Haas <robertmhaas@gmail.com>
2012-06-14 20:13 ` Andres Freund <andres@2ndquadrant.com>
2012-06-15 20:03 ` Robert Haas <robertmhaas@gmail.com>
2012-06-15 20:18 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-16 11:43 ` Andres Freund <andres@2ndquadrant.com>
2012-06-19 01:20 ` Robert Haas <robertmhaas@gmail.com>
2012-06-19 18:23 ` Andres Freund <andres@2ndquadrant.com>
2012-06-19 21:36 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-20 01:08 ` Robert Haas <robertmhaas@gmail.com>
2012-06-20 10:15 ` Andres Freund <andres@2ndquadrant.com>
2012-06-16 19:03 ` Steve Singer <steve@ssinger.info>
2012-06-19 00:52 ` Robert Haas <robertmhaas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox