PostgreSQL SSD vs HDD - why is there no difference in insert performance?

Question

I have a PostgreSQL12 on Ubuntu 20.04 with following tablespaces:

The default uses a HDD and there is also another tablespace on an SSD.

Then I run a test to see the differences in insert/update speed for tables placed in different tablespaces:

create table test_hdd
(v int);

create table test_ssd
(v int)
tablespace ssd1;

insert into test_hdd
select * from generate_series(1, 10000000); --~10sec

insert into test_ssd
select * from generate_series(1, 10000000); --~10sec


update test_hdd set v = 1; --~25sec
update test_ssd set v = 1; --~25sec

The expected outcome is that the operations on table test_ssd should be faster, but that is not the case. All the operations on both tables take almost exactly the same amount of time to complete.

My question is: why? How do I get a write speed boost from using an SSD?

Bonus:

I compared the disk write speed using dd to make sure that the disk for /dev is faster than the HDD disk, and it indeed is. The HDD write speed was around 224 MB/s, and for SSD it was around 1.3 GB/s.

Answer 1

I would not expect disk to be the bottleneck for this task on a reasonable machine. All writes but one (the final commit) should be either buffered, or done in the background. The bottleneck would be CPU.

If each row was inserted in a separate transaction and your HDD lacks a non-volatile write cache and your system is configured properly for durability, then having WAL on the SSD rather than HDD should be a lot faster due to the fsync speed.

Even with your single-statement INSERT, if you increased the size to the point that the temp files used for generate_series actually had to reach disk and be evicted from RAM, and then be read back in, that could also highlight the difference between SSD AND HDD.