Command Prompt, Inc.

1. That high IO latency is an acceptable business requirement.
2. That you are willing to spend a lot of money to get performance you can get for less money using bare metal: rented or not. Note: This is a cloud issue not an AWS issue.

Using the following base configuration (see adjustments for each configuration after the graphic):

port = 5432                             
max_connections = 500                   
ssl = true                              
shared_buffers = 4GB                    
temp_buffers = 8MB                      
work_mem = 47MB                         
maintenance_work_mem = 512MB            
wal_level = hot_standby                 
synchronous_commit = on         
commit_delay = 0                        
commit_siblings = 5                     
checkpoint_segments = 30               
checkpoint_timeout = 10min              
checkpoint_completion_target = 0.9      
random_page_cost = 1.0                  
effective_cache_size = 26GB

Each test was run using pgbench against 9.1 except for configuration 9 which was 9.3:

pgbench -F 100 -s 100 postgres -c 500 -j10 -t1000 -p5433

Here are some of our latest findings:

The AWS configuration is:

16 Cores
30G of memory (free -h reports 29G)
(2) PIOPS volumes at 2000 IOPS a piece.
The PIOPS volumes are not in A RAID and are mounted separately.
The PIOPS volumes are formatted with xfs and default options
The PIOPS volumes were warmed.

1. Configuration 1:

   $PGDATA and pg_xlog on the same partition
   synchronous_commit = on
   

2. Configuration 2:

   $PGDATA and pg_xlog on the same partition
   synchronous_commit = off
   

3. Configuration 3:

   $PGDATA and pg_xlog on the same partition
   synchronous_commit = off
   commit_delay = 100000
   commit_siblings = 50
   

4. Configuration 4:

   $PGDATA and pg_xlog on the same partition
   synchronous_commit = off
   commit_delay = 100000
   commit_siblings = 500
   

5. Configuration 5:

   $PGDATA and pg_xlog on different partitions
   synchronous_commit = off
   commit_delay = 100000
   commit_siblings = 500
   

6. Configuration 6:

   $PGDATA and pg_xlog on different partitions
   synchronous_commit = on
   commit_delay = 100000
   commit_siblings = 500
   

7. Configuration 7:

   $PGDATA and pg_xlog on different partitions
   synchronous_commit = on
   commit_delay = 0
   commit_siblings = 5
   

8. Configuration 8:

   $PGDATA and pg_xlog on different partitions
   synchronous_commit = on
   checkpoint_segments = 300
   checkpoint_timeout = 60min
   

9. Configuration 9:

   $PGDATA and pg_xlog on different partitions
   PostgreSQL 9.3
   synchronous_commit = on
   checkpoint_segments = 300
   checkpoint_timeout = 60min