If the database data will be on NVME SSD which let's assume is 4x faster than an ordinary SSD will queries like SELECT something OFFSET 100000
and the like typically also be much faster (of course when they are not cached/buffered etc)?
Since I/O is the dominant factor in how long it takes an SQL query to run, then it should help.
Some pros and cons, all mixed up:
OFFSET 100000
should be avoided, regardless of the I/O.