Since psql performance was not really up to speed at the server I am currently using, I had a look at how to measure and optimize its performance. PostgreSQL comes with a neat benchmarking tool to help you tweak the performance settings to your environment.
Settings can be changed in your postgresql.conf file, the easiest way to change these is via the Backend Configuration Editor in PgAdmin. To see the currently used setting, use for example
SELECT name, setting, unit FROM pg_settings WHERE name = 'shared_buffers';
In order to reload the settings after a change without restarting the server, you can use the command
Here is how I used pgbench on the Windows Enterprise Server to get a sense of the PostgreSQL performance.
1. Create a new benchmarking database in PgAdmin. I called it "pgbench".
2. Create benchmarking tables using pgbench -i. Here's the command I used in Windows:
./pgbench.exe -i -s 1 -h [servername] -U [username] pgbench
The command line argument -s is the scaling factor used. I ran the test for a number of scaling factors between 1 and 1000 to see where there are drops in performance and how they relate to the database size.
3. In order to check the database size, use the following command:
select datname,pg_size_pretty(pg_database_size(oid)) from pg_database where datname = 'gpbench';
4. Run benchmarking
./pgbench.exe -t 2000 -c 8 -S -h [servername] -U [username] pgbench
The final line in the output is the number of transactions per second, which is what we use to measure performance.