Notes on PostgreSQL performance optimization on RDS

postgresql-on-aws-tips.md

Deep Dive: PostgreSQL on AWS

When loading data

• disable backups (backup_retention=0)
• disable multi-AZ and autovacuum
• pg_dump -Fc (compressed) and pg_restore -j (parallel)
• Increase maintenance_work_mem
• Increase checkpoint_segments & checkpoint_timeout
• Even though the entire internet tells you to do this: do not disable fsync as it's dangerous (you don't want to disable fsync on the WAL segments for example) and you don't get the huge performance increases you think you'll get.. Instead:
  • Disable synchronous_commit -- it's even faster than fsync=0
  • Slight (4%) performance increase if you have both off, but you're putting the whole DB at risk as previously mentioned

You need to test the parameters. Sometimes maintenance_work_mem being high is not necessarily better (speaker showed a workload of setting it to 256 was actually faster than 512 or 1024)

When to vacuum?

• PostgresSQL will autovacuum when autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pgclass.reltuples

How hard does autovacuum work?

• autovacuum_max_workers
• autovacuum_nap_time
• autovacuum_cost_limit and autovacuum_cost_delay

Some cool extensions: with PostgreSQL 9.3.5 that came out on AWS this past week

• postgres_fdw (foreign data wrappers) with Amazon RedShift (Amazon's data warehouse-in-the-cloud product, based on a PostgreSQL-like interface)

SET session_replication_role

• don't fire triggers depending on what role your machine is set to, to avoid trigger loops between a primary and a replica (useful when you're using tools like Londiste or Bucardo)

Scale Availability

• read replicas announced on Monday
• synchronous replication is the multi-AZ
• some customers want higher read availability with PostgreSQL streaming asynchronous replication, so that's what they have now
• replication parameters -- wal_keep_segments. since the WALs are kept in the cloud by RDS, it's ok if PostgreSQL blows them away during a replication unavailable event

New in 9.3.5 on RDS

• PLv8 - v8 javascript engine
• test_parser extension
• new PostGIS version
• preload_statements

Burst mode RDS instance types: gp2 and t2

• GP2 storage type - SSD-based Amazon EBS storage

  • instead of specifying IOPs you specify storage volume
  • 3 IOPS per GB base performance
  • Earn credits when usage below base
  • Burst to 3K IOPS

• T2 - Amazon EC2 instance with burst

  • base performance w/ burst
  • earn credits per hour when below base performance
  • store up to 24h of credits