Re: Make ringbuffer threshold and ringbuffer sizes configurable?

On Wed, Feb 05, 2020 at 08:00:26PM -0800, Andres Freund wrote:
> I think it would make sense to have seqscan_ringbuffer_threshold,
> {bulkread,bulkwrite,vacuum}_ringbuffer_size.

I suggest the possibility of somehow forcing a ringbuffer for nonbulk writes
for the current session.

In our use-case, we have loader processes INSERTing data using prepared
statements, UPSERT, and/or multiple VALUES(),() lists. Some of that data will
be accessed in the near future (15min-24hr) but some parts (large parts, even)
may never be accessed. I imagine most of the buffer pages never get
usagecount > 0 before being evicted.

I think it'd still be desirable to make the backend do write() its own dirty
buffers to the OS, rather than leaving behind large numbers of dirty buffers
for another backend to deal with, since that *could* be a customer facing
report. I'd prefer the report run 10% faster due to rarely hitting dirty
buffer (by avoiding the need to write out lots of someone elses data), than the
loaders to run 25% slower, due to constantly writing to the OS.

The speed of loaders is not something our customers would be concerned with.
It's okay if they are slower than they might be. They need to keep up with
incoming data, but it'd rarely matter if we load a 15min interval of data in
5min instead of in 4.

We would use copy if we could, to get ring buffer during writes. But cannot
due to UPSERT (and maybe other reasons).

I have considered the possibility of loading data into a separate instance with
small (or in any case separate) shared_buffers and then tranferring its data to
a customer-facing report instance using pg_restore (COPY)...but the overhead to
maintain that would be significant for us (me).

--
Justin