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)?
It is quite obvious, is it not?
If the IO side of the database is the bottleneck, then having a better IO side will speed things up.
Now, most of the time - outside of data warehouses - IO should not bottleneck that bad once you leave the really low IOPS side of Hard discs. A proper database server will have enough RAM (again, outside of data warehouses) hat a lot is cached - which means that no, you may not really need THAT much database bandwidth and it will likely NOT get you 4x the throughput.
But if you have like 5gb RAM and a 100GB table and you make a table scan, then yes, faster IO means faster query processing. Obviously.
Also Note: NVM does not necessarily mean the SSD is also faster. Not sure they are any, but you better make sure your NVM SSD is up to the task. There are some really cheap ones that are a: not optimized for the write performance needed and b: just too slow to use up the NVM ports.
I am in the process of provisioning for an NVME RAID. Before committing to purchase, I wanted to try and benchmark how queries requiring a scan would benefit from better transfer speeds.
I currently have an SSD RAID with read speeds in the neighbourhood of 900MBps, and an HDD RAID with read speeds in the neighbourhood of 100MBps. Wanting a quick and dirty metric, I've tested on only 2 tasks:
(1) pulling all records (less than 100) matching an unindexed column (2) pulling one record offset by 100 000 000.
Time to execute (1): 82s on SSD vs 780s on HDD
Time to execute (2): 49s on SSD vs 480s on HDD
Differences in time to execute are very close to read speed differences. I'll update with results on NVME RAID when it is operational
I've been able to run similar queries on an NVME system (2100 MBps vs 900 MBPS on our SSD array). Queries execute 30% faster, not twice as fast. Clear signs that CPU is becoming an issue.