Per-session/transaction commit delay

postgresql_per_session_transaction_commit_delay.md

Notes for a proposal to add per-session and/or per-transaction commit delay to postgresql.

Per session/transaction commit delay

Currently postgres has the commit_delay and commit_sibling global configurations.

To better support mixed workloads it would be significantly easier if a bit more flexibility was permitted, e.g. by allowing individual sessions or transactions to specify different acceptable commit delays, while still providing all required guarantees.

Proposal

Global configuration

Add the following global configurations:

commit_delay_min (integer)

Minimum allowed value in microseconds for the group commit delay set in a session. If a session specifies a value lower than commit_delay_min it is silently capped to commit_delay_min. Changing this at runtime requires the same privileges as changing commit_delay.

Must be 0 ≤ commit_delay_min ≤ commit_delay. Default is commit_delay.

commit_delay_max (integer)

Maximum allowed value in microseconds for the group commit delay set in a session. If a session specifies a value higher than commit_delay_max it is silently capped to commit_delay_max. Changing this at runtime requires the same privileges as changing commit_delay.

Must be MAX_INT ≥ commit_delay_max ≥ commit_delay. Default is commit_delay.

Session/local configuration

Add the following session/local configuration:

commit_delay_hint (integer)

Maximum desired commit delay that this session/transaction expects, in microseconds. The actual commit delay can be lower or higher than the hint, but postgres will attempt to keep it below commit_delay_hint. See the group commit delay documentation for details and usage.

Capped to the range [commit_delay_min, commit_delay_max]. Default is commit_delay.

Implementation

When a session starts:

• set commit_delay_hint_clamped = commit_delay

When SET commit_delay = ?, SET commit_delay_min = ?, SET commit_delay_max = ? are executed

• if all conditions (commit_delay_min <= commit_delay <= commit_delay_max, commit_delay_min >= 0, commit_delay_max <= MAX_INT) are satisfied by the new value(s)
  • set the corresponding variable(s)
• otherwise
  • raise an error

When SET [LOCAL] commit_delay_hint = ? is executed:

• set commit_delay_hint_clamped = clamp(?, commit_delay_min, commit_delay_max) in the appropriate scope

When a commit becomes ready to be flushed to the WAL:

• commit_flush_deadline = now() + commit_delay_hint_clamped
• if there is no active commit group
  • if there are less than commit_sibling active transactions
    • flush to WAL immediately
  • if there are at least commit_sibling active transactions
    • start a new commit group
    • set commit_group_flush_deadline = commit_flush_deadline
    • flush to WAL once deadline is reached (note: deadline may be shortened by additional commits that arrive later)
• if there is an active commit group
  • add commit to active commit group
  • if commit_group_flush_deadline > commit_flush_deadline
    • set commit_group_flush_deadline = commit_flush_deadline

Notes

• In a workload with mixed commit_delay_hint_clamped values, queries with lower commit_delay_hint_clamped may have slightly higher tail latencies, even though the average latency would be lower, than in the corresponding fixed commit_delay case because more commits may be pending to be flushed once the deadline is reached.