Horton

Re: Some read stream improvements

Thomas Munro <thomas.munro@gmail.com>

View thread View on postgr.es
From: Thomas Munro <thomas.munro@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-03-14T09:03:15Z
Lists: pgsql-hackers

Attachments

I have pushed the new pin limit patches, after some more testing and
copy editing. I dropped an unnecessary hunk (in read_stream_reset(), a
change I'd like to make but it didn't belong in this commit) and
dropped the word "Soft" from GetSoftPinLimit() as it wasn't helping
comprehension and isn't even true for the local buffer version (which
I still think might be an issue, will come back to that, but again it
seemed independent).

For the record, here's a way to see 92fc6856^ or v17 misbehave and pin
too many buffers without involving any other proposed patches, only
v17 streaming seq scan: with shared_buffers=16MB, max_connections=4,
which gives MaxProportionalBuffers == 44, the attached shows three
cursors each pinning io_combine_limit = 32 buffers for a total of 96
at peak.  That's because each cursor starts a stream and sees (the
only time it would check) that it is allowed 44, and then we fetch in
round-robin order until they all ramp up to full I/O size.  In v17 we
can't really do much worse than that and it requires pretty contrived
settings and workload for it to be a real issue AFAIK but obviously
and hopefully we soon will eg BHS and more.

Commits

  1. read_stream: Fix overflow hazard with large shared buffers

  2. Support buffer forwarding in StartReadBuffers().

  3. Support buffer forwarding in read_stream.c.

  4. Simplify distance heuristics in read_stream.c.

  5. Improve read_stream.c advice for dense streams.

  6. Respect changing pin limits in read_stream.c.

  7. Improve buffer manager API for backend pin limits.

  8. Remove arbitrary cap on read_stream.c buffer queue.