Subject: RE: Time delayed LR (WAS Re: logical replication restrictions)

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Fri, 28 Apr 2023 09:05:12 +0000

Dear hackers,

I rebased and refined my PoC. Followings are the changes:

* Added support for ALTER SUBSCRIPTION .. SKIP LSN. The skip operation is done when
the application starts. User must indicate the commit_lsn of the transaction to
skip the transaction. If the apply worker faces ERROR, it will output the commit_lsn.
Apart from non-delayed transactions, the prepared but not committed transaction
cannot be skipped. This is because currently the prepare_lsn is not recorded to
the file.

* Added integrity checks. When the debug build is enabled, each messages written in
the files has the CRC checksums. When the message is read by apply worker, the
worker checks it and raise PANIC if the process fails to compare. I'm not sure
the performancec degradation can be accepted, so I added it only when
USE_ASSERT_CHECKING is on.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: Amit Kapila <[email-masked]>
Date: Thu, 11 May 2023 10:34:44 +0530

> I rebased and refined my PoC. Followings are the changes:

1. Is my understanding correct that this patch creates the delay files
for each transaction? If so, did you consider other approaches such as
using one file to avoid creating many files?
2. For streaming transactions, first the changes are written in the
temp file and then moved to the delay file. It seems like there is a
double work. Is it possible to unify it such that when min_apply_delay
is specified, we just use the delay file without sacrificing the
advantages like stream sub-abort can truncate the changes?
3. Ideally, there shouldn't be a performance impact of this feature on
regular transactions because the delay file is created only when
min_apply_delay is active but better to do some testing of the same.

Overall, I think such an approach can address comments by Sawada-San
[1] but not sure if Sawada-San or others have any better ideas to
achieve this feature. It would be good to see what others think of
this approach.

[1] - https://www.postgresql.org/message-id/CAD21AoAeG2%2BRsUYD9%2BmEwr8-rrt8R1bqpe56T2D%3DeuO-Qs-GAg%40mail.gmail.com

-- 
With Regards,
Amit Kapila.

---

From: Masahiko Sawada <[email-masked]>
Date: Fri, 12 May 2023 11:07:38 +0900

> min_apply_delay is active but better to do some testing of the same.

In addition to the points Amit raised, if the 'required_schema' option
is specified in START_REPLICATION, the publisher sends schema
information for every change. I think it leads to significant
overhead. Did you consider alternative approaches such as sending the
schema information for every transaction or the subscriber requests
the publisher to send it?

> achieve this feature. It would be good to see what others think of

I agree with this approach.

When it comes to the idea of writing logical changes to permanent
files, I think it would also be a good idea (and perhaps could be a
building block of this feature) that we write streamed changes to a
permanent file so that the apply worker can retry to apply them
without retrieving the same changes again from the publisher.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Fri, 12 May 2023 03:48:12 +0000

Dear Amit, Sawada-san,

Thank you for replying!

> the publisher to send it?

Thanks for giving your opinions. Except for suggestion 2, I have never considered.
I will analyze them and share my opinion later.
About 2, I chose the style in order to simplify the source code, but I'm now planning
to follow suggestions.

> without retrieving the same changes again from the publisher.

I'm very relieved to hear that.
One question: did you mean to say that serializing changes into the permanent files
can be extend to the non-delay case, right? I think once I will treat for delayed
replication, and then we can consider later.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: Masahiko Sawada <[email-masked]>
Date: Fri, 12 May 2023 13:45:56 +0900

> replication, and then we can consider later.

What I was thinking of is that we implement non-delay cases (only for
streamed transactions) and then extend it to delay cases (i.e. adding
non-streamed transaction support and the delay mechanism). It might be
helpful if this patch becomes large and this approach can enable us to
reduce the complexity or divide the patch. That being said, I've not
considered this approach enough yet and it's just an idea. Extending
this feature to non-delay cases later also makes sense to me.

Regards,

---

From: Amit Kapila <[email-masked]>
Date: Fri, 12 May 2023 10:33:19 +0530

> the publisher to send it?

Why do we need this new flag? I can't see any comments in the related
code which explain its need.

> without retrieving the same changes again from the publisher.

I think we anyway won't be able to send confirmation till we write or
process the commit. If it gets interrupted anytime in between we need
to get all the changes again. I think using Fileset with temp files
has quite a few advantages for streaming as are noted in the header
comments of worker.c. We can investigate to replace that with
permanent files but I don't see that the advantages outweigh the
change. Also, after parallel apply, I am expecting, most users would
prefer that mode for large transactions, so making changes in the
serialized path doesn't seem like a good idea to me.

Having said that, I also thought that it would be a good idea if both
streaming and time-delayed can use the same code path in some way
w.r.t writing to files but couldn't come up with any good idea without
more downsides. I see that Kuroda-San has tried to keep the code path
isolated for this feature but still see that one can question the
implementation approach.

-- 
With Regards,
Amit Kapila.

---

From: Amit Kapila <[email-masked]>
Date: Fri, 12 May 2023 10:47:03 +0530

> code which explain its need.

So as per the email [1], this would be required after the subscriber
restart. I guess we ideally need it once per delay file (considering
that we have one file for all delayed xacts). In the worst case, we
can have it per transaction as suggested by Sawada-San.

[1] - https://www.postgresql.org/message-id/TYAPR01MB5866568A5C1E71338328B20CF5629%40TYAPR01MB5866.jpnprd01.prod.outlook.com

-- 
With Regards,
Amit Kapila.

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Mon, 22 May 2023 11:49:12 +0000

Dear Amit,

Thank you for giving suggestions.

> using one file to avoid creating many files?

I have been analyzing the approach which uses only one file per subscription, per
your suggestion. Currently I'm not sure whether it is good approach or not, so could
you please give me any feedbacks?

TL;DR: rotating segment files like WALs may be used, but there are several issues.

# Assumption 

* Streamed txns are also serialized to the same permanent file, in the received order.
* No additional sorting is considered.

# Considerations

As a premise, applied txns must be removed from files, otherwise the disk becomes
full in some day and it leads PANIC.

## Naive approach - serialize all the changes to one large file

If workers continue to write received changes from the head naively, it may be
difficult to purge applied txns because there seems not to have a good way to
truncate the first part of the file. I could not find related functions in fd.h.

## Alternative approach - separate the file into segments

Alternative approach I came up with is that the file is divided into some segments
- like WAL - and remove it if all written txns are applied. It may work well in
non-streaming, 1pc case, but may not in other cases.

### Regarding the PREPARE transactions

At that time it is more likely to occur that the segment which contains the
actual txn is differ from the segment where COMMIT PREPARED. Hence the worker
must check all the remained segments to find the actual messages from them. Isn't
it inefficient? There is another approach that workers apply the PREPARE
immediately and spill to file only COMMIT PREPARED, but in this case the worker
have been acquiring the lock and never released it till delay is done.

### Regarding the streamed transactions

As for streaming case, chunks of txns are separated into several segments.
Hence the worker must check all the remained segments to find chunks messages
from them, same as above. Isn't it inefficient too?

Additionally, segments which have prepared or streamed transactions cannot be
removed, so even if the case many files may be generated and remained.

Anyway, it may be difficult to accept to stream in-progress transactions while
delaying the application. IIUC the motivation of steaming is to reduce the lag
between nodes, and it is opposite of this feature. So it might be okay, not sure.

### Regarding the publisher - timing to send schema may be fuzzy

Another issue is that the timing when publisher sends the schema information
cannot be determined on publisher itself. As discussed on hackers, publisher
must send schema information once per segment file, but it is controlled on
subscriber side.
I'm thinking that the walsender cannot recognize the changing of segments and
understand the timing to send them.

That's it. I'm very happy to get idea. 

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Mon, 12 Jun 2023 11:39:43 +0000

Dear hackers,

At PGcon and other places we have discussed the time-delayed logical replication,
but now we have understood that there are no easy ways. Followings are our analysis.

# Abstract

To implement the time-dealyed logical replication for more proper approach,
the worker must serialize all the received messages into permanent files.
But PostgreSQL does not have good infrastructures for the purpose so huge engineering is needed.

## Review: problem of without-file approach

In the without-file approach, the  apply worker process sleeps while delaying the application.
This approach is chosen in earlier versions like [1], but it contains problems which was
shared by Sawada-san [2]. They lead the PANIC error due to the disk full.

 A) WALs cannot be recycled on publisher because they are not flushed on subscriber.
 B) Moreover, vacuuming cannot remove dead tuples on publisher.

## Alternative approach: serializing messages to files

To prevent any potential issues, the worker should serialize all incoming messages
to a permanent file, like what the physical walreceiver does.
Here, messages are first written into files at the beginning of transactions and then flushed at the end.
This approach could slove problem a), b), but it still has many considerations and difficulties.

### How to separate messages into files?

There are two possibilities for dividing messages into files, but neither of them is ideal.

1. Create a file per received transaction. 
 
In this case files will be removed after the delay-period is exceeded and it is applied.
This is the simplest approach, but the number of files is bloat.

2. Use one large file or segmented file (like WAL). 

This can reduce the number of files, but we must consider further things:

 A) Purge – We must purge the applied transaction, but we do not have a good way
 to remove one transaction from the large file.

 B) 2PC – It is more likely to occur that the segment which contains the actual
 transaction differs from the segment where COMMIT PREPARED.
 Hence the worker must check all the segments to find the actual messages from them.

 C) Streamed in-progress transactions - chunks of transactions are separated
 into several segments. Hence the worker must check all the segments to find
 chunks messages from them, same as above.

### Handle the case when the file exceeds the limitation 

Regardless of the option chosen from the ones mentioned above, there is a possibility
that the file size could exceed the file system's limit. This can occur as the
publisher can send transactions of any length.
PostgreSQL provides a mechanism for working with such large files - BufFile data structure,
but it could not be used as-is for several reasons:

 A) It only supports the buffered-I/O. A read or write of the low-level File
 occurs only when the buffer is filled or emptied. So, we cannot control when it is persisted.

 B) It can be used only for temporary purpose. Internally the BufFile creates
 some physical files into $PGDATA/base/pgsql_tmp directories, and files in the
 subdirectory will be removed when postmaster restarts.

 C) It does not have mechanisms for restoring information after the restart.
 BufFile contains virtual positions such as file index and offset, but these
 fields are stored in a memory structure, so the BufFile will forget the ordering
 of files and its initial/final position after restarts.

 D) It cannot remove a part of virtual file. Even if a large file is separated
 into multiple physical files and all transactions in a physical file are already
 applied, BufFile cannot remove only one part.

[1]: https://www.postgresql.org/message-id/f026292b-c9ee-472e-beaa-d32c5c3a2ced%40www.fastmail.com
[2]: https://www.postgresql.org/message-id/CAD21AoAeG2+RsUYD9+mEwr8-rrt8R1bqpe56T2D=[email-masked]

Acknowledgement:

Amit, Peter, Sawada-san
Thank you for discussing with me off-list.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

---

From: "Hayato Kuroda (Fujitsu)" <[email-masked]>
Date: Tue, 13 Jun 2023 02:59:55 +0000

Dear hackers,

> but now we have understood that there are no easy ways. Followings are our

At this point, I have not planned to develop the PoC anymore, unless better idea
or infrastructure will come.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED