AWS performance: Results included
I am not a big fan of AWS. It is a closed platform. It is designed to be the Apple of the Cloud to the Eve of Postgres users. That said, customers drive business and some of our customers use AWS, even if begrudgingly. Because of these factors we are getting very good at getting PostgreSQL to perform on AWS/EBS, albeit with some disclosures:
  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