Re: Should we update the random_page_cost default value?

On Mon, Oct  6, 2025 at 01:06:21PM -0400, Andres Freund wrote:
> Hi,
> 
> On 2025-10-06 12:57:20 -0400, Bruce Momjian wrote:
> > On Mon, Oct  6, 2025 at 11:14:13AM -0400, Andres Freund wrote:
> > > > It obviously contradicts the advice to set the value closer to 1.0. But
> > > > why is that? SSDs are certainly better with random I/0, even if the I/O
> > > > is not concurrent and the SSD is not fully utilized. So the 4.0 seems
> > > > off, the value should be higher than what we got for SSDs ...
> > > 
> > > I'd guess that the *vast* majority of PG workloads these days run on networked
> > > block storage. For those typically the actual latency at the storage level is
> > > a rather small fraction of the overall IO latency, which is instead dominated
> > > by network and other related cost (like the indirection to which storage
> > > system to go to and crossing VM/host boundaries).  Because the majority of the
> > > IO latency is not affected by the storage latency, but by network lotency, the
> > > random IO/non-random IO difference will play less of a role.
> > 
> > Yes, the last time we discussed changing the default random page cost,
> > September 2024, the argument was that while SSDs should be < 4, cloud
> > storage might be > 4, so 4 was still a good value:
> > 
> >     https://www.postgresql.org/message-id/flat/877caxaxt6.fsf%40wibble.ilmari.org#8a10b7b8cf05410291d076f8def58c29
> 
> I think it's exactly the other way round. The difference between random and
> sequential IO is *smaller* on cloud storage than on local storage, due to
> network IO being the biggest component of IO latency on cloud storage - and
> network latency is the same for random and sequential IO.
> 
> > Add in cache effects for all of these storage devices as outlined in our
> > docs.
> 
> As discussed in [1], the cache effect related comments in the docs seem pretty
> bogus. We'd be much better off just removing them, they really don't make much
> sense.

Fine, but without the doc comments, we have _no_ logic for why the value
is so small.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.