PGCon2016 - 20180510

PGCon 2016
The PostgreSQL Conference

Rahila Syed
Day Talks - Day 2 - 2016-05-20
Room DMS 1160
Start time 11:00
Duration 00:45
ID 947
Event type Lecture
Track Performance
Language used for presentation English

WAL Reduction

Lose WAL size, gain performance

Under heavy write load on the server, writing large amount of WAL to disk can pose to be a bottleneck and slow down rate of transaction commit. Not only that, streaming large amount of WAL and writing it on standby can introduce replication delays resulting in unrecoverable data loss in case of primary server crash. To alleviate this and improve performance, features to reduce WAL size have been introduced in PostgreSQL core. This talk describes such features and scenarios in which they should be used.

This talk focusses on structure of WAL and ways to reduce its size. It describes in detail following features.

  1. Compression of full page writes in WAL This feature compresses full page image written in WAL when fullpagewrites is ON or during a base backup. This feature is disabled by default and can be enabled within a session by setting GUC wal_compression to ON. WAL is decompressed during WAL replay. The compressed WAL eases efforts while writing it on disk and streaming it to standbys thus improving performance. This feature is typically useful for users who want to improve performance by reducing the I/O load on their server and can spare some amount of CPU for the same.

  2. WAL reduction for update operation This feature ensures that when a row is updated, only the part of the new tuple which is not identical to the old tuple is WAL logged. This saves on the amount of WAL written when most of the data of an updated tuple remains same.

This talk covers

1.Structure of WAL 2.Need for WAL reduction 3.Ways to reduce WAL size
4.Features in core- FPW compression, Reduction in WAL for Update operation 5.Scenarios in which each feature is useful. 6.Demonstration of performance improvements with resource usage