I am currently trying to benchmark the performance of PostgreSQL when:
- the tablespace is set to a folder on an HDD partition
- the tablespace is set to a folder on an SSD partition
I am using the pgbench tool to perform the benchmarking, and am following the procedure according to this tutorial. So basically, I am looking at the tps (avg. transactions per second) at the end of the run as the metric for comparison.
Note: The size of my database is close to 10 GB so there is sufficient load for a stress test.
Before each run, I am manually changing the tablespace to hdd_tbspc (tablespace on HDD parition) and ssd_tbspc (tablespace on SSD parition), using pgAdmin.
However the value that I am getting at the end of each of the runs is more or less the same, i.e., I can't observe any significant performance improvements when I switch to an SSD. Is there any reason for this?
Also, please let me know if the benchmarking protocol I am following makes sense.
Here are the commands that I am using:
To initialize and scale the DB:
$: pgbench -U postgres -i -s 50 db-name
The above command is run only once. The two queries shown below are run on the same database with different tablespaces to contrast the performances.
Using the number of transactions as a metric:
$: pgbench -U postgres -c 10 -j 2 -t 10000 db-name
Using time as a metric:
$: pgbench -U postgres -c 10 -T 600 db-name