3

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.

4
  • 2
    Insert performance is mostly dependent on how fast you can flush the WAL buffer; did you move WAL segments for your tests?
    – mustaccio
    Apr 21, 2021 at 13:14
  • I will try to move WAL directory to an SSD disk and then check performance again Apr 21, 2021 at 13:20
  • 1
    Also, generate_series may write temp files. Put temp_tablespaces on the SSD. Apr 21, 2021 at 15:44
  • I moved entire postgres DATA_DIR to an SSD, so now WAL data and temp tables will be written there. I made a separate tablespace on HDD. I ran the same tests as I did before and still the insert and update performance is comparable for both tablespaces Apr 22, 2021 at 7:23

1 Answer 1

2

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.

1
  • I read some articles about using SSD tablespaces for heavy write loads, so I assumed there would be some kind of a difference? Maybe I need to test it on bigger data? Or does it really not matter if I use SSD or HDD? Apr 23, 2021 at 6:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.