We are trying to migrate our PostgreSQL installation (which runs on a dedicated server with mechanical hard disks) to a AWS RDS PostgreSQL instance with SSD.
We migrated our schemas, data etc. and before going to production we started to perform some tests. Unfortunately we noticed that performances are about one half that the ones we where having with mechanical disks.
Our workload is mixed: we do lots of simple reads and writes over hundreds of tables, even though the majority of the workload hits only a very small subset of them (approximately 10 tables).
We tried running pg_bench and comparing the results with the ones obtained on our current dedicated server, but no matter the settings they show very similar tps values, but then again if we run a simple select query over the same table with the same data the AWS instance execution time is double than that of our HDD based machine.
What could be the reason of these bad performances? What should we be looking into?