Re: Should we update the random_page_cost default value?

Hi,

On 2025-10-06 13:22:59 -0400, Bruce Momjian wrote:
> On Mon, Oct 6, 2025 at 01:06:21PM -0400, Andres Freund wrote:
> > On 2025-10-06 12:57:20 -0400, Bruce Momjian wrote:
> > > 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.

That seems better than having a comment that is basically ahistorical and
conflates different things. We certainly shouldn't mention spinning disks,
they have not mattered as the storage for live database storage for at least a
decade.

*If* we do want to add some explanation, I think we'd be better off adding a
paragraph mentioning a few things:

- the default value of random_page_cost is a compromise between the latency of
random IO on local storage and cloud storage, where the latter has a smaller
difference between sequential and random IO

- some workloads are IOPS rather than latency or bandwidth bound, in which
case it can be better to use sequential IO

- it can be advisable to favor indexed accesses over sequential access, even
if sequential access is faster in some cases, as indexed accesses "degrade"
somewhat gradually in performance with decreased selectivity, but the switch
to sequential scan will have completely different performance
characteristics.

Greetings,

Andres Freund