Delaying commits in PostgreSQL

If you are a parent, you might be familiar with the nursery rhyme “The Five Little Monkeys”.

The song starts with five monkeys jumping on a bed, one by one they fall off the bed and bump their heads. Each time a monkey falls, the mom calls the doctor to ensure its safe. This keeps up until all monkeys fall. I suppose the rhyme repetition improves babies cognitive ability and speech.

If you inspect the rhyme, this clearly looks like a typical software engineering problem. On one hand, there is an obvious I/O inefficiency where the mom calls five times and the doctor charges for the five calls. On the other hand, a potential solution to reduce I/O is by batching the calls.

While batching does save I/O, there is trade-off I want to explore in this article using the commit_delay configuration in PostgreSQL as an example.

What is Batching?

To save cost, mom can wait for all monkeys to fall off and only call the doctor once. The single call can report the incident of all the monkeys at once, a method known as batching.

There are two problems with using batching as a solution to reduce the calls.

The first problem is latency, the mom can’t wait; She is worried, she needs the doctor to immediately ensure the first monkey’s safety.