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.
Best Answer
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.