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
