Sample Header Ad - 728x90

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

3 votes
1 answer
4800 views
I have a PostgreSQL12 on Ubuntu 20.04 with following tablespaces: enter image description here 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.
Asked by Leon PowaƂka (161 rep)
Apr 21, 2021, 01:02 PM
Last activity: Apr 23, 2021, 02:02 PM