Write path in OLTP workloads (PostgreSQL)

🎯 Target audience

• Engineers who work with PostgreSQL or other OLTP databases and want to understand what happens during writes
• Those looking to reason about durability, performance, and isolation trade-offs in production systems

📘 Workshop overview

This workshop uses PostgreSQL as a concrete example to explore the full write path in OLTP workloads — from durability guarantees to high availability. Each section builds on the last: once you have durability, how do you get performance? Once you have both, how do you handle concurrent writes? And once all of that works, how do you keep it running when a node goes down?

The workshop pairs internals with observability — what to measure, how to debug, and how to tune — at each stage.

✅ Learning outcomes

By the end of the workshop, participants will be able to understand:

• How PostgreSQL implements durability using WAL, fsync, and group commit
• How the background writer and checkpointer manage write throughput
• How MVCC provides concurrent readers a consistent view during writes
• How visibility checks, hint bits, and hot row contention show up in practice
• How streaming replication extends the write path to standby nodes
• Operational lessons from running PostgreSQL in production for write-heavy workloads

🧩 Workshop modules

1. Write durability guarantees
How PostgreSQL ensures committed transactions survive crashes. Covers WAL record structure, the lifecycle of a WAL record from generation to flush, fsync and group commit, and parameters like synchronous_commit, wal_sync_method, and commit_delay. Storage characteristics (SSD vs HDD, write cache) and their effect on durability expectations.

2. Write performance
How to sustain high write throughput without compromising guarantees. Covers how WAL bounds maximum throughput, group commit under concurrent workloads, batching and transaction sizing, and how the background writer and checkpointer manage dirty buffers. Observability via pg_stat_bgwriter.

3. Concurrency control
How PostgreSQL handles concurrent writes correctly. Covers SQL isolation levels, MVCC — how row versions are created, visibility rules, and where WAL fits in — visibility checks using transaction snapshots, hint bits and clog lookups, hot row contention, lock waits via pg_locks and pg_stat_activity, and preventing lost updates with SELECT FOR UPDATE and optimistic concurrency control.

4. High availability
How the write path extends into replication and failover. Covers streaming replication, physical vs logical replication, synchronous vs asynchronous modes, replication slots, WAL retention, and operational pitfalls like disk bloat from inactive slots.

5. Operational lessons from production
War stories and concrete scenarios from running PostgreSQL in production for write-heavy OLTP workloads. Covers WAL volume and checkpoint tuning, bulk ingest and bursty workload patterns, write contention from centralized counters and hot partitions, and mitigation strategies including schema changes, sharding, and query rewrites.

🛠 Tooling & prerequisites

To be shared by the instructor.

About the instructor

Amit Prabhudesai, Engineering Manager at Microsoft.

How to attend

This workshop is open for Rootconf members and for Rootconf Data Edition ticket buyers

This workshop is open to 30 participants (in-person) & hybrid access for remote attendees. Seats for in-person participants will be available on first-come-first-served basis. 🎟️

Contact information ☎️

For inquiries about the workshop, contact +91-7676332020 or write to info@hasgeek.com.