Ayub,
Ideally when i have to deal with this,
i run a pgbench stress test locally on the db server on lo interface which does not suffer mtu / bandwidth saturation issues.
then run the same pgbench from a remote server in the same subnet as the app and record the results and compare.
that helps me get rid of any non standard client issues or network latency issues.
A typical case where people above are pointing to is
1) for ex. When I am in India and query a server in the US across WAN on a client like pgadmin (which may not handle loading million rows efficiently), I have a high chance of getting ClientWrite ,ClientRead wait events. ( Read client and or network issues )
Of course this is much worse than ec2 and db in the same region, but you get the point that you have to rule out sketchy networks between the servers.
Ideally an iperf like stress test can help to test bandwidth.
So if you can run pgbench from across some test servers and get consistent results, then you can come back with a reply that more people can help with.
using a custom script
postgres@go:~/pgbench_example$ more pgbench.script
\set r1 random(0, 10000) -- you can use them below in queries as params like col = :r1
\set r2 random(0, 8000)
begin;
select random();
end;
-- put in any query that you use in jmeter between begin/end like above
-- select * from foo where (u1 = :r1 and u2 = :r2);
-- insert into foo values (:u1v, :u2v) on conflict do nothing;
-- update foo set u1 = :u1v where u2 = 100;
-- select pg_sleep(1);
and then run pgbench with the custom script
postgres@go:~/pgbench_example$ pgbench -c 10 -f ./pgbench.script -j 10 -n -T 30
transaction type: ./pgbench.script
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
duration: 30 s
number of transactions actually processed: 528984
latency average = 0.567 ms
tps = 17631.650584 (including connections establishing)
tps = 17642.174229 (excluding connections establishing)
