Re: Logical replication performance

--0000000000003da36705994cf669
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Nov 29, 2019 at 11:06 AM Florian Philippon <
[email protected]> wrote:

>
> We tried another solution: we loaded a minimal schema (without indexes an=
d
> constraints) on the subscriber and created the subscription. The initial
> copy phase was way faster (a few hours). Then we created indexes and
> constraints. Is this a suitable solution for production?
>

This is probably not suitable for production.  Once the COPY is finished,
it still has to replicate row-by-row changes to the table rows which
occurred since the starting COPY snapshot.  UPDATEs and DELETEs will
probably fail due to the lack of indexes on the =E2=80=9Creplica identity=
=E2=80=9D
columns.  This failure will make the entire transaction, including the
COPY, roll back to beginning.  So you there will be no point at which you
can build the missing indexes without first losing all the work that was
done.  If the master was quiescent (at least in regards to UPDATEs and
DELETEs) then it there will be no row-by-row changes to apply between the
start of the COPY and the start of transactional replication.  In that
case, the COPY will have committed before the system discovers the problem
with the =E2=80=9Creplica identity=E2=80=9D, giving you an opportunity to g=
o build the
index without losing all of the work.



> Will the logical replication flow be buffered by the replication slots
> during index creation and get in sync afterwards or will it conflict due =
to
> locking issues?
>

It can't buffer in the middle of the transaction which includes the initial
COPY.

Cheers,

Jeff

--0000000000003da36705994cf669
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Nov 29, 2019 at 11:06 AM Florian =
Philippon &lt;<a href=3D"mailto:[email protected]">florian.phi=
[email protected]</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left=
:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div><br></d=
iv><div>We tried another solution: we loaded a minimal schema (without inde=
xes and constraints) on the subscriber and created the subscription. The in=
itial copy phase was way faster (a few hours). Then we created indexes and =
constraints. Is this a suitable solution for production? </div></div></bloc=
kquote><div><br></div><div>This is probably not suitable=C2=A0for productio=
n.=C2=A0 Once the COPY is finished, it still has to replicate row-by-row ch=
anges to the table rows which occurred since the starting COPY snapshot.=C2=
=A0 UPDATEs and DELETEs will probably=C2=A0fail due to the lack of indexes =
on the =E2=80=9Creplica identity=E2=80=9D columns.=C2=A0 This failure will =
make the entire transaction, including the COPY, roll back to beginning.=C2=
=A0 So you there will be no point at which you can build the missing indexe=
s without first losing all the work that was done.=C2=A0 If the master was =
quiescent (at least in regards to UPDATEs and DELETEs) then it there will b=
e no row-by-row changes to apply between the start of the COPY and the star=
t of transactional replication.=C2=A0 In that case, the COPY will have comm=
itted before the system discovers the problem with the=C2=A0=E2=80=9Creplic=
a identity=E2=80=9D, giving you an opportunity to go build the index withou=
t losing all of the work.</div><div><br></div><div>=C2=A0</div><blockquote =
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px sol=
id rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>Will the logica=
l replication flow be buffered by the replication slots during index creati=
on and get in sync afterwards or will it conflict due to locking issues?</d=
iv></div></blockquote><div><br></div><div>It can&#39;t buffer in the middle=
 of the transaction which includes the initial COPY.</div><div><br></div><d=
iv>Cheers,</div><div><br></div><div>Jeff</div></div></div>

--0000000000003da36705994cf669--