Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1VER-009KSb-5z for pgsql-hackers@arkaria.postgresql.org; Wed, 24 Sep 2025 19:32:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v1VEO-00FPio-CP for pgsql-hackers@arkaria.postgresql.org; Wed, 24 Sep 2025 19:32:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1VEN-00FPiX-SR for pgsql-hackers@lists.postgresql.org; Wed, 24 Sep 2025 19:32:48 +0000 Received: from mail-io1-xd29.google.com ([2607:f8b0:4864:20::d29]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1VEL-002EjF-0j for pgsql-hackers@lists.postgresql.org; Wed, 24 Sep 2025 19:32:46 +0000 Received: by mail-io1-xd29.google.com with SMTP id ca18e2360f4ac-8e8f45829d7so15599939f.1 for ; Wed, 24 Sep 2025 12:32:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758742364; x=1759347164; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=rfJLS5WRrQa81PPDKyCVT0ispNUGaxCeYL5g51KYFK4=; b=lKU9iSTWnFU2uNd+5jC6aLsf5/FBUK/vnc0IAvzcfS4/B+K6OO+nGHhuYMunNd96K0 SzoozZOeKc/92v22L/mkxgddvj1aDth8EsprUd1gkvtp6/T6PWqgXeIEzI3z0NFyNMyo /3AfIcAuXNVay3sIDfr9ZZtJkqEhV1wOPNlnDRY7dxtvuB8GhNgjPut4Z0j88ZNK+MQK gTqmjuN1YfNL+EOb1TtmsT5lx9/AsiMqgxgEPrXU0Al36i+m3vfeCVXh1S7rXUvAvrrt i9BE5+NeAZwnfmcIYCqqB8c/oGjSkPmYvbmKoQSNe3trKFtUMuwwIFJQvt5Uo6pjbRox KJZA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758742364; x=1759347164; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=rfJLS5WRrQa81PPDKyCVT0ispNUGaxCeYL5g51KYFK4=; b=pLK/MiCOz+8SwB+koit86rfKNr9wgDzQK5ZFRIHvdSSx6bcMRTtJOir5vVnkH+DBRS xXUN9cv/ktZFYrFgoyjw9ba68DUc37qrQ7hp+AEqCCwZrZzTMeYmvSzbB0jWsV2fR0Eg g7MMAVzT5JU7/uBhVVz/lbcL0/ZDdEUeY+gkWtJOt4rMxz7QPN5Q2q5pqHOE95tQdbyI yOw1Q1laZCt0lH1n1HI+BE5amZmsdxgHY4rdr04xwDeFKvRdT2MmL5JAWajV7qQ7fPCs ZjlpTd9uQDtxpUtrDTebWH5+caiYq2HufFxVNSkYUDIHfdt7g4Y6WmTfHN9wJ/jQo5qn dxKA== X-Forwarded-Encrypted: i=1; AJvYcCUGaa789BS0wcDzz1MWQE7vlCthUCAY/5zYeNBjCthB/68fypg9lT0ImjkWOOmRf8+UOfwrRRlKehzC3CjZ@lists.postgresql.org X-Gm-Message-State: AOJu0YyCZRaMrlK1f0DZcWcbp2iMRRJwKQTCWEGoPZo9oxZP1O54USUH 31dN4V3kd/eARf33aU2EX0KieiHxydg1PrC20sf3HDJf0nqGNNTkZ/qE0ZgY6Hhpc96LC22zUjN 2XoaKTQf3zAPsc5+eiBuLKAHVcwGPWWpynSKm X-Gm-Gg: ASbGncsz+wIa+1SEnY8bovgpV2Ib3yJx8aPQjbWh5srb8oT1mLpa63/MdqB/OipHl0W qi/Gjoux0y2RkGwiDxregm58JqLFN6/XqzEEebwOfYBXRYsSVM2hE3Qs9Fn+GrUXZFjKM4b4VpY eatrDUkcrZrnHV7I3tr9hWEqN++6fxUfxC0CV5575GsMKx5ktV+LD23CK4fNJJ5AGV9/up/lXXQ 3H+dw== X-Google-Smtp-Source: AGHT+IG672jWExAIg/+kwbnJTLbk7fHqlDcujnjBdfXJ671ii5oHS+qR4o0keskiroCCXoc2ua5b/2H5WLPnBn8i8XQ= X-Received: by 2002:a92:c885:0:b0:40c:c955:b67d with SMTP id e9e14a558f8ab-42595668993mr9382135ab.28.1758742364098; Wed, 24 Sep 2025 12:32:44 -0700 (PDT) MIME-Version: 1.0 References: <783426a05a756b29b5b1ef4d5640f22d999fdec8.camel@j-davis.com> In-Reply-To: <783426a05a756b29b5b1ef4d5640f22d999fdec8.camel@j-davis.com> From: Bharath Rupireddy Date: Wed, 24 Sep 2025 12:32:33 -0700 X-Gm-Features: AS18NWDwHWMBtN4VtzGwb_dUPOvRq27Jj_oQH2jkNTT1UcfY103kBBxW1YLT_2g Message-ID: Subject: Re: Use WALReadFromBuffers in more places To: Jeff Davis Cc: Jingtang Zhang , pgsql-hackers@lists.postgresql.org, Nitin Jadhav Content-Type: multipart/alternative; boundary="000000000000dd14c6063f911dac" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000dd14c6063f911dac Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, On Wed, Sep 24, 2025 at 11:27=E2=80=AFAM Jeff Davis wro= te: > > On Wed, 2025-09-24 at 07:26 -0700, Bharath Rupireddy wrote: > > Right. Reading unflushed WAL buffers for replication was one of the > > motivations. But, in general, WALReadFromBuffers has more benefits > > since it lets WAL buffers act as a cache for reads, avoiding the need > > to re-read WAL from disk for (both physical and logical) replication. > > For example, it makes the use of direct I/O for WAL more realistic > > and > > can provide significant performance benefits [1]. > > Is it possible to do a POC that shows the potential benefit, or are we > still too far away? Thanks for looking into this. I did performance analysis with WAL directo I/O to see how reading from WAL buffers affects walsenders: https://www.postgresql.org/message-id/CALj2ACV6rS%2B7iZx5%2BoAvyXJaN4AG-djA= QeM1mrM%3DYSDkVrUs7g%40mail.gmail.com. Following is from that thread. Please let me know if you have any specific cases in mind. I'm happy to run the same test for logical replication. It helps WAL DIO; since there's no OS page cache, using WAL buffers as read cache helps a lot. It is clearly evident from my experiment with WAL DIO patch [1], see the results [2] and attached graph. As expected, WAL DIO brings down the TPS, whereas WAL buffers read i.e. this patch brings it up. [2] Test case is an insert pgbench workload. clients HEAD | WAL DIO | WAL DIO & WAL BUFFERS READ | WAL BUFFERS READ 1 1404 1070 1424 1375 2 1487 796 1454 1517 4 3064 1743 3011 3019 8 6114 3556 6026 5954 16 11560 7051 12216 12132 32 23181 13079 23449 23561 64 43607 26983 43997 45636 128 80723 45169 81515 81911 256 110925 90185 107332 114046 512 119354 109817 110287 117506 768 112435 105795 106853 111605 1024 107554 105541 105942 109370 2048 88552 79024 80699 90555 4096 61323 54814 58704 61743 --=20 Bharath Rupireddy PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com --000000000000dd14c6063f911dac Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,

On Wed, Sep 24, 2025 at 11:27=E2=80=AFAM Jeff D= avis <pgsql@j-davis.com> wro= te:
>
> On Wed, 2025-09-24 at 07:26 -0700, Bharath Rupireddy wr= ote:
> > Right. Reading unflushed WAL buffers for replication was = one of the
> > motivations. But, in general, WALReadFromBuffers ha= s more benefits
> > since it lets WAL buffers act as a cache for r= eads, avoiding the need
> > to re-read WAL from disk for (both phy= sical and logical) replication.
> > For example, it makes the use = of direct I/O for WAL more realistic
> > and
> > can prov= ide significant performance benefits [1].
>
> Is it possible to= do a POC that shows the potential benefit, or are we
> still too far= away?

Thanks for looking into this. I did performance analysis with= WAL directo I/O to see how reading from WAL buffers affects walsenders: https://www.postgresql.org/= message-id/CALj2ACV6rS%2B7iZx5%2BoAvyXJaN4AG-djAQeM1mrM%3DYSDkVrUs7g%40mail= .gmail.com. Following is from that thread.=C2=A0Please let me know if y= ou have any specific cases in mind. I'm happy to run the same test for = logical replication.

It helps WAL DIO; since there's no OS
page cache, using WAL buffers as = read cache helps a lot. It is clearly
evident from my experiment with WA= L DIO patch [1], see the results [2]
and attached graph. As expected, WA= L DIO brings down the TPS, whereas
WAL buffers read i.e. this patch brin= gs it up.

[2] Test case is an insert= pgbench workload.
clients HEAD | WAL DIO=C2=A0|=C2=A0WAL DIO & WA= L BUFFERS READ=C2=A0|=C2=A0WAL BUFFERS READ
1 1404 1070 1424 1375
2= 1487 796 1454 1517
4 3064 1743 3011 3019
8 6114 3556 6026 5954
16= 11560 7051 12216 12132
32 23181 13079 23449 23561
64 43607 26983 439= 97 45636
128 80723 45169 81515 81911
256 110925 90185 107332 114046512 119354 109817 110287 117506
768 112435 105795 106853 111605
102= 4 107554 105541 105942 109370
2048 88552 79024 80699 90555
4096 61323= 54814 58704 61743

--
Bharath Rupireddy
PostgreSQL Con= tributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
--000000000000dd14c6063f911dac--