Search code examples
mysqlpostgresqlsqlperformancepostgresql-performancenvme

Will NVME SSD speed up heavy queries, e.g. selects at large offsets / table scans etc?

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)?

Solution

  • 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:

    • Well designed schema and well written queries avoid I/O. OFFSET 100000 should be avoided, regardless of the I/O.
    • UUIDs are notoriously random.
    • The I/O speed has very little effect when everything that you need is cached.
    • It won't be 4x. By decreasing the I/O bottleneck, CPU time becomes more prevalent.