PGCon2011 - Add 4 Video (2015.09.18)

PGCon 2011
The PostgreSQL Conference

Greg Smith
Day Talks - 2 - 2011-05-20
Room DMS 1140
Start time 15:00
Duration 01:00
ID 337
Event type Lecture
Track Performance
Language used for presentation English

The Write Stuff

Reaching peak write performance under heavy loads

One of the most difficult things to do with a database system is handle a high volume of writes, whether it's from a single user or thousands. The steps a database like PostgreSQL takes to protect your data are expensive, and it's hard to split them into independent pieces. Recent changes available in the upcoming PostgreSQL 9.1 have given new insight into exactly how PostgreSQL systems that write constantly break down under serious loads.

The replication features in PostgreSQL 9.0 make it easier than ever to scale your application performance up by adding more read-only nodes with additional copies of the data. But that also makes it easier than ever before to end up running into a harder to resolve write bottleneck as a result.

This talk will cover the major approaches and trade-offs in tuning for high write performance on a PostgreSQL server. The write-ahead log, checkpoint timing, and buffer management policies of the database all interact to limit just fast data can be shuffled in and out of the server. Some recently added features to PostgreSQL 9.1 will also be introduced. These give better insight than was ever available before into exactly what caused the kind of checkpoint write "spikes" that have traditionally plagued write-heavy PostgreSQL databases. The trade-offs between optimizing for latency and throughput are highlighted when watching operations at this level.

Understanding these problems can help you make the right long-term choices about how to tune system, or provide ammunition for why you need to plan an upgrade to 9.1 when it becomes available in the future.