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?
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.
generate_seriesmay write temp files. Put
temp_tablespaceson the SSD.