Reduce logging during prepared transaction recovery
Hi,
RecoverPreparedTransactions() currently emits one LOG message for
every prepared transaction restored at the end of recovery.
With many unresolved prepared transactions, this produces substantial
log output during promotion. Writing these messages can also delay
completion of recovery.
This patch moves the per-transaction message from LOG to DEBUG1. The
transaction ID and epoch remain available when detailed logging is
enabled. After recovery, one LOG message reports the total number of
successfully recovered prepared transactions.
For example:
LOG: recovered 50000 prepared transactions
I performed preliminary tests using a physical standby and empty
prepared transactions on my local VM and here are the results that I
could see:
Prepared xacts Without patch With patch Time saved
--------------- ------------ ----------------------- ----------
10,000 1.544 s 0.615 s 0.929 s
25,000 3.936 s 2.433 s 1.503 s
50,000 9.788 s 6.263 s 3.525 s
The patched version reduced recovery time in both tests while avoiding
thousands of repetitive LOG messages.
The patch does not change prepared transaction recovery or on disk state.
Transaction level diagnostics remain available with log_min_messages = debug1.
Please take a look at the attached patch and let me know your
thoughts. I have also attached the test-script I used to create
prepared transactions for the test results shown above.
The test scripts can be used as follows:
./create_prepared_transactions.sh <prepared_transactions_count> <primary_port>
--
With Regards,
Ashutosh Sharma.
On Wed, Oct 7, 2026 at 11:53 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
Hi,
RecoverPreparedTransactions() currently emits one LOG message for
every prepared transaction restored at the end of recovery.With many unresolved prepared transactions, this produces substantial
log output during promotion. Writing these messages can also delay
completion of recovery.This patch moves the per-transaction message from LOG to DEBUG1. The
transaction ID and epoch remain available when detailed logging is
enabled. After recovery, one LOG message reports the total number of
successfully recovered prepared transactions.For example:
LOG: recovered 50000 prepared transactions
I performed preliminary tests using a physical standby and empty
prepared transactions on my local VM and here are the results that I
could see:Prepared xacts Without patch With patch Time saved
--------------- ------------ ----------------------- ----------
10,000 1.544 s 0.615 s 0.929 s
25,000 3.936 s 2.433 s 1.503 s
50,000 9.788 s 6.263 s 3.525 sThe patched version reduced recovery time in both tests while avoiding
thousands of repetitive LOG messages.The patch does not change prepared transaction recovery or on disk state.
Transaction level diagnostics remain available with log_min_messages = debug1.
Please take a look at the attached patch and let me know your
thoughts. I have also attached the test-script I used to create
prepared transactions for the test results shown above.The test scripts can be used as follows:
./create_prepared_transactions.sh <prepared_transactions_count> <primary_port>
Hi Ashutosh,
1. I've always also wondered too why we are spamming the log with per-2pc
recovered transaction details too back in the day. It's hard to read
in real production recovery scenarios.
2. Sadly I couldn't repro the performance benefit. I've tried even with
syslog, so perphas this is something with the VM on your side? What's the
profile/reason/specs that cause lack of ereport() to causes such a big
boost?
In my case even (lack of) 50k rows like that, each taking ~111 bytes (
datetime+tz+pid+"LOG: recovering prepared transaction 29635 of epoch 0 from
shared memory" is something like just ~5MB happily accepted by writeback/
dirty mem, so technically it should not cause any I/O performance issues.
I've tried to benchmark this even with local rsyslogd and also got not
improvement. So at least in my case, it's cheap?
3. Please register in the commitfest, so the email doesn't get lost, or at
least I couldn't find it.
So +1 from to avoid log clutter (log readability), but not as performance
enhacement as I couldn't repro the benefit.
-J.
Hi,
On Mon, Oct 12, 2026 at 2:13 PM Jakub Wartak
<jakub.wartak@enterprisedb.com> wrote:
On Wed, Oct 7, 2026 at 11:53 AM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
Please take a look at the attached patch and let me know your
thoughts. I have also attached the test-script I used to create
prepared transactions for the test results shown above.The test scripts can be used as follows:
./create_prepared_transactions.sh <prepared_transactions_count> <primary_port>
Hi Ashutosh,
1. I've always also wondered too why we are spamming the log with per-2pc
recovered transaction details too back in the day. It's hard to read
in real production recovery scenarios.2. Sadly I couldn't repro the performance benefit. I've tried even with
syslog, so perphas this is something with the VM on your side? What's the
profile/reason/specs that cause lack of ereport() to causes such a big
boost?
In my case even (lack of) 50k rows like that, each taking ~111 bytes (
datetime+tz+pid+"LOG: recovering prepared transaction 29635 of epoch 0 from
shared memory" is something like just ~5MB happily accepted by writeback/
dirty mem, so technically it should not cause any I/O performance issues.
I've tried to benchmark this even with local rsyslogd and also got not
improvement. So at least in my case, it's cheap?3. Please register in the commitfest, so the email doesn't get lost, or at
least I couldn't find it.So +1 from to avoid log clutter (log readability), but not as performance
enhacement as I couldn't repro the benefit.
The primary motivation for this change is to reduce log clutter. In
our environment, these logs are forwarded to remote log storage, so
emitting one message per recovered prepared transaction also creates
unnecessary log volume and downstream processing.
I agree that any performance improvement is secondary and
environment-dependent. The preliminary local testing I mentioned
showed a minor improvement, but since it has not been reproducible
elsewhere, I will avoid presenting performance as a justification for
the change.
I’ve also added the patch to the commitfest. Thanks for the reminder.
--
With Regards,
Ashutosh Sharma.
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not tested
Hello,
This patch looks good to me.
Before patching, I get after standby promotion:
2026-10-17 15:04:49.035 CEST [142400] LOG: recovering prepared transaction 37020 of epoch 0 from shared memory
2026-10-17 15:04:49.037 CEST [142400] LOG: recovering prepared transaction 37021 of epoch 0 from shared memory
2026-10-17 15:04:49.039 CEST [142400] LOG: recovering prepared transaction 37022 of epoch 0 from shared memory
2026-10-17 15:04:49.041 CEST [142400] LOG: recovering prepared transaction 37023 of epoch 0 from shared memory
2026-10-17 15:04:49.043 CEST [142400] LOG: recovering prepared transaction 37024 of epoch 0 from shared memory
2026-10-17 15:04:49.044 CEST [142400] LOG: recovering prepared transaction 37025 of epoch 0 from shared memory
2026-10-17 15:04:49.046 CEST [142400] LOG: recovering prepared transaction 37026 of epoch 0 from shared memory
2026-10-17 15:04:49.048 CEST [142400] LOG: recovering prepared transaction 37027 of epoch 0 from shared memory
2026-10-17 15:04:49.049 CEST [142400] LOG: recovering prepared transaction 37028 of epoch 0 from shared memory
2026-10-17 15:04:49.051 CEST [142400] LOG: recovering prepared transaction 37029 of epoch 0 from shared memory
2026-10-17 15:04:49.063 CEST [142398] LOG: checkpoint starting: force
2026-10-17 15:04:49.089 CEST [142398] LOG: 10 two-phase state files were written for long-running prepared transactions
2026-10-17 15:04:49.091 CEST [142394] LOG: database system is ready to accept connections
After patching I get after standby promotion:
2026-10-17 16:30:19.983 CEST [234528] LOG: recovered 10 prepared transactions
2026-10-17 16:30:19.993 CEST [234526] LOG: checkpoint starting: force
2026-10-17 16:30:20.019 CEST [234522] LOG: database system is ready to accept connections
2026-10-17 16:30:20.122 CEST [234526] LOG: 10 two-phase state files were written for long-running prepared transactions
I don't think there is need to test spec. compliance or to update the documentation.
Regards,
PF
The new status of this patch is: Ready for Committer
On Sat, Oct 17, 2026 at 11:32 PM Pierre Forstmann
<pierre.forstmann@gmail.com> wrote:
The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not testedHello,
This patch looks good to me.
+1. I have one small suggestion, though.
The existing TAP test 009_twophase.pl already tests server startup with
prepared transactions. So, isn't it better to add the log checks there,
as in the attached patch?
Regards,
--
Fujii Masao
Attachments:
nocfbot-v1-0002-Test-prepared-transaction-recovery-logging.patchapplication/octet-stream; name=nocfbot-v1-0002-Test-prepared-transaction-recovery-logging.patchDownload+13-3
Hi Fujii-san,
On Mon, Oct 19, 2026 at 8:55 AM Fujii Masao <masao.fujii@gmail.com> wrote:
On Sat, Oct 17, 2026 at 11:32 PM Pierre Forstmann
<pierre.forstmann@gmail.com> wrote:The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: not testedHello,
This patch looks good to me.
+1. I have one small suggestion, though.
The existing TAP test 009_twophase.pl already tests server startup with
prepared transactions. So, isn't it better to add the log checks there,
as in the attached patch?
Thanks for the review and suggestions. I agree that we can verify
these changes by adding log checks to the existing TAP test for
two-phase transactions. I’ve done that in the attached patch, which is
based on the changes suggested in your earlier patch. Please take a
look and let me know if you have any other comments.
--
With Regards,
Ashutosh Sharma.
On Wed, Oct 28, 2026 at 7:15 PM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
Thanks for the review and suggestions. I agree that we can verify
these changes by adding log checks to the existing TAP test for
two-phase transactions. I’ve done that in the attached patch, which is
based on the changes suggested in your earlier patch. Please take a
look and let me know if you have any other comments.
Thanks for updating the patch!
- ereport(LOG,
+ ereport(DEBUG1,
Do we want to keep this debug message translatable? If not, we could
simplify this to elog().
Also, DEBUG2 might be more consistent with per-transaction messages
in PrepareRedoAdd() and PrepareRedoRemoveFull()? Those use elog(DEBUG2, ...).
This would also avoid flooding the log at DEBUG1.
Regards,
--
Fujii Masao
On Oct 28, 2026, at 19:45, Fujii Masao <masao.fujii@gmail.com> wrote:
On Wed, Oct 28, 2026 at 7:15 PM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
Thanks for the review and suggestions. I agree that we can verify
these changes by adding log checks to the existing TAP test for
two-phase transactions. I’ve done that in the attached patch, which is
based on the changes suggested in your earlier patch. Please take a
look and let me know if you have any other comments.Thanks for updating the patch!
- ereport(LOG, + ereport(DEBUG1,Do we want to keep this debug message translatable? If not, we could
simplify this to elog().
Ah, that’s a good point.
Also, DEBUG2 might be more consistent with per-transaction messages
in PrepareRedoAdd() and PrepareRedoRemoveFull()? Those use elog(DEBUG2, ...).
This would also avoid flooding the log at DEBUG1.
+1
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Hi,
Thanks for your review comments/suggestions.
On Wed, Oct 28, 2026 at 5:15 PM Fujii Masao <masao.fujii@gmail.com> wrote:
On Wed, Oct 28, 2026 at 7:15 PM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
Thanks for the review and suggestions. I agree that we can verify
these changes by adding log checks to the existing TAP test for
two-phase transactions. I’ve done that in the attached patch, which is
based on the changes suggested in your earlier patch. Please take a
look and let me know if you have any other comments.Thanks for updating the patch!
- ereport(LOG, + ereport(DEBUG1,Do we want to keep this debug message translatable? If not, we could
simplify this to elog().Also, DEBUG2 might be more consistent with per-transaction messages
in PrepareRedoAdd() and PrepareRedoRemoveFull()? Those use elog(DEBUG2, ...).
This would also avoid flooding the log at DEBUG1.
It's a developer-facing message, so I agree that it doesn't need to be
translatable. I've changed it to elog(DEBUG2, ...) in the attached
patch, consistent with PrepareRedoAdd() and PrepareRedoRemoveFull().
--
With Regards,
Ashutosh Sharma.
On Thu, Oct 29, 2026 at 2:30 PM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
It's a developer-facing message, so I agree that it doesn't need to be
translatable. I've changed it to elog(DEBUG2, ...) in the attached
patch, consistent with PrepareRedoAdd() and PrepareRedoRemoveFull().
Thanks for updating the patch! LGTM.
I've pushed it. Thanks!
Regards,
--
Fujii Masao
Fujii Masao <masao.fujii@gmail.com> writes:
Thanks for updating the patch! LGTM.
I've pushed it. Thanks!
BF member prion has been failing since this went in. It looks like
the cause is that it's configured for log_error_verbosity = verbose,
so that what it prints is like
# 2026-10-29 20:10:30.831 UTC [501077:43] LOG: 00000: recovered 1 prepared transaction
# 2026-10-29 20:10:30.831 UTC [501077:44] LOCATION: RecoverPreparedTransactions, twophase.c:2212
but the new test cases are not expecting a SQLSTATE in there:
log_like => [qr/LOG: recovered 1 prepared transaction\r?$/m],
regards, tom lane
On Fri, Oct 30, 2026 at 8:45 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Fujii Masao <masao.fujii@gmail.com> writes:
Thanks for updating the patch! LGTM.
I've pushed it. Thanks!BF member prion has been failing since this went in. It looks like
the cause is that it's configured for log_error_verbosity = verbose,
so that what it prints is like
Thanks for the report! I've pushed the attached patch to fix this issue.
Regards,
--
Fujii Masao