High write loads can have different patterns. For example, this can be a result of writing events to PostgreSQL or it can be a result of a bulk load from a database dump or an ETL job. One can tune PostgreSQL for high write loads by doing the following:
- Hardware configuration:
- One should use RAID 1+0 instead of RAID 5 or 6. RAID 10 has much better performance for heavy writes. Also it is better to store transaction logs (
pg_xlog
) on a separate hard disk. - One can use SSD hard disks with Write-back cache (WBC), which significantly increases write performance. Also make sure your SSDs can persist cached data on power failure.
- One should use RAID 1+0 instead of RAID 5 or 6. RAID 10 has much better performance for heavy writes. Also it is better to store transaction logs (
- PostgreSQL server setting:
fsync
: By default,fsync
is on. This parameter makes sure that the database can be recovered in the event of a hardware crash.fsync
makes sure that the data is actually written on the hard disk. You can disable this option if you can trust your hardware. Hardware failure might lead to corrupt data if this option is...