pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19041: Logical replication locks wal processing
5+ messages / 4 participants
[nested] [flat]
* BUG #19041: Logical replication locks wal processing
@ 2025-09-03 10:19 PG Bug reporting form <noreply@postgresql.org>
2025-09-03 11:55 ` Re: BUG #19041: Logical replication locks wal processing Dilip Kumar <dilipbalaut@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2025-09-03 10:19 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: sergey.belyashov@gmail.com
The following bug has been logged on the website:
Bug reference: 19041
Logged by: Sergey Belyashov
Email address: sergey.belyashov@gmail.com
PostgreSQL version: 17.6
Operating system: Debian bookworm x86_64
Description:
I have few Postgresql servers: A, B, C, D... Each servers has tables: users,
settings and t with partitions t1, t2, t3... Server A publish tables users
and settings, and other servers are subscribed on them using one
subscription (logical replication is used). Other servers publish tables t
and server A subscribed on it (it does not matter, I think). When I create
table s (without indexes) on server A and copy huge about of rows (30M+) all
workers which do replication users,settings tables loads cpu cores by 35-80%
for a long time (5+ hours, I do not wait more). WAL is raising at this time
(37GB+). When I drop subscriptions to these tables from servers B, C... then
WAL is processed very fast. When I keep only one subscription undropped then
WAL is processed (reduced to 2-3 GB) in a hour and only one process eats
35-50% of CPU core time until WAL is not processed. Tables users and
settings are not changed or using (there are not foreign keys in the table
s) during the issued case. So the impact on their publication/subscriptions
is unexpected.
server_a# create table users (user_id integer, name text);
server_a# create table settings (user_id integer, name text, value text);
server_a# create publication users_pub for table users WITH (publish =
'insert');
server_a# create publication settings_pub for table settings;
...
server_b# create table users (user_id integer, name text);
server_b# create table settings (user_id integer, name text, value text);
server_b# create subscription usr_stt_sub connection '...' publication
users_pub, settings_pub;
...
server_a# create table s (trx_id integer, amount numeric);
server_a# COPY s FROM ...;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19041: Logical replication locks wal processing
2025-09-03 10:19 BUG #19041: Logical replication locks wal processing PG Bug reporting form <noreply@postgresql.org>
@ 2025-09-03 11:55 ` Dilip Kumar <dilipbalaut@gmail.com>
2025-09-03 12:10 ` Re: BUG #19041: Logical replication locks wal processing Sergey Belyashov <sergey.belyashov@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Dilip Kumar @ 2025-09-03 11:55 UTC (permalink / raw)
To: sergey.belyashov@gmail.com; pgsql-bugs@lists.postgresql.org
On Wed, Sep 3, 2025 at 4:55 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19041
> Logged by: Sergey Belyashov
> Email address: sergey.belyashov@gmail.com
> PostgreSQL version: 17.6
> Operating system: Debian bookworm x86_64
> Description:
>
> I have few Postgresql servers: A, B, C, D... Each servers has tables: users,
> settings and t with partitions t1, t2, t3... Server A publish tables users
> and settings, and other servers are subscribed on them using one
> subscription (logical replication is used). Other servers publish tables t
> and server A subscribed on it (it does not matter, I think). When I create
> table s (without indexes) on server A and copy huge about of rows (30M+) all
> workers which do replication users,settings tables loads cpu cores by 35-80%
> for a long time (5+ hours, I do not wait more). WAL is raising at this time
> (37GB+). When I drop subscriptions to these tables from servers B, C... then
> WAL is processed very fast. When I keep only one subscription undropped then
> WAL is processed (reduced to 2-3 GB) in a hour and only one process eats
> 35-50% of CPU core time until WAL is not processed. Tables users and
> settings are not changed or using (there are not foreign keys in the table
> s) during the issued case. So the impact on their publication/subscriptions
> is unexpected.
Just to give some context, a separate walsender process will be
created for each subscription and every walsender will decode(process)
all the WALs, so that's the reason that when you have multiple
subscriber there would be multiple walsender process will be their to
decode the WAL and each will consume CPU for decoding OTOH if you if
you only keep one subscription then there will only be one walsender
for decoding the WAL. And WAL can not be removed until all the data
are streamed to all the subscribers and if you drop all the
subscribers WAL can be removed by the checkpoint if it is not required
by any other purpose like hot standby or wal archiver.
--
Regards,
Dilip Kumar
Google
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19041: Logical replication locks wal processing
2025-09-03 10:19 BUG #19041: Logical replication locks wal processing PG Bug reporting form <noreply@postgresql.org>
2025-09-03 11:55 ` Re: BUG #19041: Logical replication locks wal processing Dilip Kumar <dilipbalaut@gmail.com>
@ 2025-09-03 12:10 ` Sergey Belyashov <sergey.belyashov@gmail.com>
2025-09-03 12:45 ` Re: BUG #19041: Logical replication locks wal processing Dilip Kumar <dilipbalaut@gmail.com>
2025-09-05 02:28 ` RE: BUG #19041: Logical replication locks wal processing Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
0 siblings, 2 replies; 5+ messages in thread
From: Sergey Belyashov @ 2025-09-03 12:10 UTC (permalink / raw)
To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Thank you for your explanation. It is what I'm about.
Is it possible to optimize something to prevent decoding the whole WAL
if there are no affected tables there? For example, just skip a lot of
WAL blocks with unpublished tables. And/or combine WAL decoding in one
separate process for each publication which works for all active
affected subscriptions, if subscription is not active (server down or
too busy) then it is switched to legacy selfdecoding.
Best regards,
Sergey Belyashov
ср, 3 сент. 2025 г. в 14:56, Dilip Kumar <dilipbalaut@gmail.com>:
>
> On Wed, Sep 3, 2025 at 4:55 PM PG Bug reporting form
> <noreply@postgresql.org> wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference: 19041
> > Logged by: Sergey Belyashov
> > Email address: sergey.belyashov@gmail.com
> > PostgreSQL version: 17.6
> > Operating system: Debian bookworm x86_64
> > Description:
> >
> > I have few Postgresql servers: A, B, C, D... Each servers has tables: users,
> > settings and t with partitions t1, t2, t3... Server A publish tables users
> > and settings, and other servers are subscribed on them using one
> > subscription (logical replication is used). Other servers publish tables t
> > and server A subscribed on it (it does not matter, I think). When I create
> > table s (without indexes) on server A and copy huge about of rows (30M+) all
> > workers which do replication users,settings tables loads cpu cores by 35-80%
> > for a long time (5+ hours, I do not wait more). WAL is raising at this time
> > (37GB+). When I drop subscriptions to these tables from servers B, C... then
> > WAL is processed very fast. When I keep only one subscription undropped then
> > WAL is processed (reduced to 2-3 GB) in a hour and only one process eats
> > 35-50% of CPU core time until WAL is not processed. Tables users and
> > settings are not changed or using (there are not foreign keys in the table
> > s) during the issued case. So the impact on their publication/subscriptions
> > is unexpected.
>
> Just to give some context, a separate walsender process will be
> created for each subscription and every walsender will decode(process)
> all the WALs, so that's the reason that when you have multiple
> subscriber there would be multiple walsender process will be their to
> decode the WAL and each will consume CPU for decoding OTOH if you if
> you only keep one subscription then there will only be one walsender
> for decoding the WAL. And WAL can not be removed until all the data
> are streamed to all the subscribers and if you drop all the
> subscribers WAL can be removed by the checkpoint if it is not required
> by any other purpose like hot standby or wal archiver.
>
> --
> Regards,
> Dilip Kumar
> Google
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19041: Logical replication locks wal processing
2025-09-03 10:19 BUG #19041: Logical replication locks wal processing PG Bug reporting form <noreply@postgresql.org>
2025-09-03 11:55 ` Re: BUG #19041: Logical replication locks wal processing Dilip Kumar <dilipbalaut@gmail.com>
2025-09-03 12:10 ` Re: BUG #19041: Logical replication locks wal processing Sergey Belyashov <sergey.belyashov@gmail.com>
@ 2025-09-03 12:45 ` Dilip Kumar <dilipbalaut@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Dilip Kumar @ 2025-09-03 12:45 UTC (permalink / raw)
To: Sergey Belyashov <sergey.belyashov@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Wed, Sep 3, 2025 at 5:40 PM Sergey Belyashov
<sergey.belyashov@gmail.com> wrote:
>
> Thank you for your explanation. It is what I'm about.
> Is it possible to optimize something to prevent decoding the whole WAL
> if there are no affected tables there? For example, just skip a lot of
> WAL blocks with unpublished tables. And/or combine WAL decoding in one
> separate process for each publication which works for all active
> affected subscriptions, if subscription is not active (server down or
> too busy) then it is switched to legacy selfdecoding.
IMHO this is a valid optimization to have a single decoding worker and
all the walsender can get the required WALs decoded by a single
worker. I think it's been discussed in the past as well and we might
do it sometime in the future but I don't see anything is in progress
for this as of now.
--
Regards,
Dilip Kumar
Google
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: BUG #19041: Logical replication locks wal processing
2025-09-03 10:19 BUG #19041: Logical replication locks wal processing PG Bug reporting form <noreply@postgresql.org>
2025-09-03 11:55 ` Re: BUG #19041: Logical replication locks wal processing Dilip Kumar <dilipbalaut@gmail.com>
2025-09-03 12:10 ` Re: BUG #19041: Logical replication locks wal processing Sergey Belyashov <sergey.belyashov@gmail.com>
@ 2025-09-05 02:28 ` Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>
1 sibling, 0 replies; 5+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2025-09-05 02:28 UTC (permalink / raw)
To: Sergey Belyashov <sergey.belyashov@gmail.com>; Dilip Kumar <dilipbalaut@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Dear Sergey,
> Thank you for your explanation. It is what I'm about.
> Is it possible to optimize something to prevent decoding the whole WAL
> if there are no affected tables there?
I recalled there was a proposal which skips decoding changes for unpublished
tables, but it was withdrawn [1]. One of reasons was that a new transaction must
be started to check whether the table must be published or not, thus the
performance can be degraded in some cases.
I have also considered Dilip's idea [2] once, but it needs some architectural
changes. I cannot find on-going thread neither.
[1]: https://commitfest.postgresql.org/patch/5585/
[2]: https://www.postgresql.org/message-id/CAFiTN-uwgQ%3DHVFRdz2%3DJUVgQHCPubatKrK14xD-0Ov71HRpbTQ%40mail...
Best regards,
Hayato Kuroda
FUJITSU LIMITED
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-09-05 02:28 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-03 10:19 BUG #19041: Logical replication locks wal processing PG Bug reporting form <noreply@postgresql.org>
2025-09-03 11:55 ` Dilip Kumar <dilipbalaut@gmail.com>
2025-09-03 12:10 ` Sergey Belyashov <sergey.belyashov@gmail.com>
2025-09-03 12:45 ` Dilip Kumar <dilipbalaut@gmail.com>
2025-09-05 02:28 ` Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.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