MIME-Version: 1.0
References: <20230111182720.ejifsclfwymw2reb@awork3.anarazel.de>
 <CAH2-Wzmv-k4QWjUhasa2UM8s82ZxOzuJGj_PKq1uOu4a7bsTTw@mail.gmail.com>
 <20230111185854.5srlvequbxbg5p6c@awork3.anarazel.de> <CAH2-Wzkwr2E0KvLtaFWUxMp8qow5nqUQMSQsVUa3KLabXJQPyA@mail.gmail.com>
 <20230111191842.5ywh7twkis42kxad@awork3.anarazel.de> <CAH2-Wz=Ur6J+A2mXB1wZm-QmZWTMWiVk0sn4f-0mbQTyFXQhHg@mail.gmail.com>
 <669645.1673471914@sss.pgh.pa.us> <20230111213905.4ljixbm2oyqzvzzx@awork3.anarazel.de>
 <CAKFQuwau7X3J5qB-J8WdTwDCa_jkWyVOETLjGnu468_iOXnxFA@mail.gmail.com> <20230112003638.bdigosdroqn7vp5h@awork3.anarazel.de>
In-Reply-To: <20230112003638.bdigosdroqn7vp5h@awork3.anarazel.de>
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Tue, 28 Feb 2023 13:46:32 +0530
Message-ID: <CALj2ACVisMie0=g4Uzigxv4eNdAuPuW+eVTAM7XZ=Y2i69mbNA@mail.gmail.com>
Subject: Re: Option to not use ringbuffer in VACUUM, using it in failsafe mode
To: Andres Freund <andres@anarazel.de>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, 
	Peter Geoghegan <pg@bowt.ie>, pgsql-hackers@postgresql.org, 
	Robert Haas <robertmhaas@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>, 
	David Rowley <dgrowleyml@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CALj2ACVisMie0%3Dg4Uzigxv4eNdAuPuW%2BeVTAM7XZ%3DY2i69mbNA%40mail.gmail.com>
Precedence: bulk

On Thu, Jan 12, 2023 at 6:06=E2=80=AFAM Andres Freund <andres@anarazel.de> =
wrote:
>
> On 2023-01-11 17:26:19 -0700, David G. Johnston wrote:
> > Should we just add "ring_buffers" to the existing "shared_buffers" and
> > "temp_buffers" settings?
>
> The different types of ring buffers have different sizes, for good reason=
s. So
> I don't see that working well. I also think it'd be more often useful to
> control this on a statement basis - if you have a parallel import tool th=
at
> starts NCPU COPYs you'd want a smaller buffer than a single threaded COPY=
. Of
> course each session can change the ring buffer settings, but still.

How about having GUCs for each ring buffer (bulk_read_ring_buffers,
bulk_write_ring_buffers, vacuum_ring_buffers - ah, 3 more new GUCs)?
These options can help especially when statement level controls aren't
easy to add (COPY, CREATE TABLE AS/CTAS, REFRESH MAT VIEW/RMV)? If
needed users can also set them at the system level. For instance, one
can set bulk_write_ring_buffers to other than 16MB or -1 to disable
the ring buffer to use shared_buffers and run a bunch of bulk write
queries.

Although I'm not quite opposing the idea of statement level controls
(like the VACUUM one proposed here), it is better to make these ring
buffer sizes configurable across the system to help with the other
similar cases e.g., a CTAS or RMV can help subsequent reads from
shared buffers if ring buffer is skipped.

Thoughts?

--=20
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com