Pre-fetching may be interesting if the first SQL query using prefetched data picks a small set of data stored on a disk plate here, then another one stored far away (distant cylinder), then back, then again far away, and so on... On a mechanical HDD the implied head movements may considerably slow this first query down.
Is there another case justifying a pre-fetching?
If I'm right pg_prewarm if not adequate with a SSD storage device, moreover "makes most sense if your database and your RAM are really really large" is sound as the amount of head moves is (typically) proportional to the DB size.
IMHO the most significant overall performance indicator here is the ratio R/D, where R is the size of the RAM available to PostgreSQL (mainly shared_buffers and the kernel buffercache), and D stands for the size of often used database objects (data, indices...).
Multiple runs of a benchmark in contexts where this ratio is 1, then 0.5, then 0.01... often produces interesting results.
If you've got a database you can fit in memory that's got very sporadic access pattern it could be a nice boost even on SSDs. My biggest personal project has a 15GB database on disk, all of it a single table of 7.5M comments and a PGroonga full text search index. The queries are very disproportionately searching for low frequency terms so the index will largely cached, but most of the rows will require disk access. Since I can spare the ram to get the entire db in memory it's a clear win for me even if it's decent speed without. My users are basically a font of random accesses and being able to treat PostgreSQL as an in-memory db is convenient.
Pre-fetching may be interesting if the first SQL query using prefetched data picks a small set of data stored on a disk plate here, then another one stored far away (distant cylinder), then back, then again far away, and so on... On a mechanical HDD the implied head movements may considerably slow this first query down.
That's extremely common for index based accesses. Which in turn are a large fraction of queries in most use cases.
If I'm right pg_prewarm if not adequate with a SSD storage device,
For SSDs random accesses and small accesses are still substantially slower than bulk reading data into memory.
It also allows you to prewarm a read replica before sending queries to it. You can also do this by slowly ramping up traffic to it (and may want to do that anyway for other reasons) but depending on the app and query patterns, that by itself may not be enough especially if you are worried about your p99 latencies.
It can also be helpful when you have network attached storage (eg. EBS volume in AWS) since even if it is SSD you still have the network latency for each IO.
Filling terabytes of memory from non-local storage (even if it is SSD) can take some time and benefits from parallel sequential readaheads instead of doing many small, random reads.
Comments
Pre-fetching may be interesting if the first SQL query using prefetched data picks a small set of data stored on a disk plate here, then another one stored far away (distant cylinder), then back, then again far away, and so on... On a mechanical HDD the implied head movements may considerably slow this first query down.
Is there another case justifying a pre-fetching?
If I'm right pg_prewarm if not adequate with a SSD storage device, moreover "makes most sense if your database and your RAM are really really large" is sound as the amount of head moves is (typically) proportional to the DB size.
IMHO the most significant overall performance indicator here is the ratio R/D, where R is the size of the RAM available to PostgreSQL (mainly shared_buffers and the kernel buffercache), and D stands for the size of often used database objects (data, indices...).
Multiple runs of a benchmark in contexts where this ratio is 1, then 0.5, then 0.01... often produces interesting results.
If you've got a database you can fit in memory that's got very sporadic access pattern it could be a nice boost even on SSDs. My biggest personal project has a 15GB database on disk, all of it a single table of 7.5M comments and a PGroonga full text search index. The queries are very disproportionately searching for low frequency terms so the index will largely cached, but most of the rows will require disk access. Since I can spare the ram to get the entire db in memory it's a clear win for me even if it's decent speed without. My users are basically a font of random accesses and being able to treat PostgreSQL as an in-memory db is convenient.
That's extremely common for index based accesses. Which in turn are a large fraction of queries in most use cases.
For SSDs random accesses and small accesses are still substantially slower than bulk reading data into memory.
It also allows you to prewarm a read replica before sending queries to it. You can also do this by slowly ramping up traffic to it (and may want to do that anyway for other reasons) but depending on the app and query patterns, that by itself may not be enough especially if you are worried about your p99 latencies.
It can also be helpful when you have network attached storage (eg. EBS volume in AWS) since even if it is SSD you still have the network latency for each IO.
Filling terabytes of memory from non-local storage (even if it is SSD) can take some time and benefits from parallel sequential readaheads instead of doing many small, random reads.