How Bitbucket Achieved Read-After-Write Consistency and Reduced Master Database Load by 50%

Ensuring data consistency in distributed systems, especially in master-replica database setups, presents unique challenges. One such critical problem is read-after-write consistency, where a user expects to immediately see the data they just wrote. This article dissects how Atlassian’s Bitbucket tackled this problem, significantly reducing their master database load by 50% while guaranteeing read-after-write consistency.

The Challenge of Read-After-Write Consistency

In a typical database architecture with a master instance handling writes and critical reads, and multiple replicas serving less critical reads, achieving read-after-write consistency is non-trivial. The core issue stems from replication lag.

When a user performs a write operation, it goes to the master. If a subsequent read operation from the same user is routed to a replica, there’s a possibility that the replica has not yet received the latest data due to replication delays. This leads to the user seeing stale data, which is an unacceptable user experience for their own recent changes.

Read-After-Write vs. Strong Consistency

It’s important to distinguish read-after-write consistency from strong consistency:

• Strong Consistency: Guarantees that any read, by any user, will always return the most recent committed value. This is the strictest form of consistency.
• Read-After-Write Consistency: A more relaxed model. It ensures that the user who performed the write will always see their own latest data on subsequent reads. Other users, however, might still see stale data until replication catches up. Bitbucket aimed for this specific, relaxed guarantee to balance consistency with performance.

Bitbucket’s Initial Database Architecture

Bitbucket, a code revisioning tool similar to GitHub, operates with a standard master-replica PostgreSQL setup:

• Master Instance: Handles all write operations and critical reads.
• Replica Instances: Serve reads where staleness is acceptable.
• Application Layer: Two main services, built with the Django framework, expose a REST API and the Bitbucket web UI. These services handle millions of requests per hour.
• PostgreSQL Specifics: PostgreSQL typically spins up a new process (rather than a thread) for each client connection, making connections expensive. To mitigate this, a connection pool is employed in front of the database.
• High Database Load: A single user request often translates to 10 or more database queries, contributing to significant load on the master database.

This architecture, while common, inherently struggles with read-after-write consistency due to replication lag, and the master becomes a bottleneck under heavy read traffic.

PostgreSQL Replication Fundamentals

To understand Bitbucket’s solution, a brief overview of PostgreSQL replication is essential:

• Pull-Based Replication: Replicas actively pull data changes from the master.
• Write-Ahead Log (WAL): When a write occurs on the master, the changes are first applied to the master’s data copy and then flushed to the WAL file. The WAL is an append-only log containing entries that describe database changes (e.g., SQL queries or other formats).
• Log Sequence Number (LSN): Each entry in the WAL is prepended with a monotonically increasing LSN. This number uniquely identifies a specific point in the WAL, indicating the order of changes. LSNs are 64-bit numbers.
• Replication Process: Replicas continuously pull these WAL entries, identified by their LSNs, and apply them to their own copies of the data. While fast, this process is not instantaneous, leading to replication lag.

Bitbucket’s Solution: Leveraging LSNs for Consistent Reads

Bitbucket’s innovative approach focuses on directing reads after writes to a caught-up replica rather than always hitting the master. This strategy effectively offloads the master while ensuring the writing user sees their latest data.

The Core Idea: Directing Reads to Caught-Up Replicas

The fundamental principle is to track the LSN of a user’s last write and then, for subsequent reads from that user, identify a replica whose LSN indicates it has processed at least that specific write. If such a replica exists, the read is routed there; otherwise, it would fall back to the master (though the goal is to avoid this).

Step-by-Step Implementation Flow

Here’s how Bitbucket implemented this using Django middleware, Redis, and PostgreSQL’s LSN capabilities:

1. User Initiates a Write: A user performs an action that results in a database write (e.g., updating a file, commenting).

2. Capture LSN on Master Commit: After the write is committed to the PostgreSQL master, the Django middleware executes a SQL query to retrieve the LSN of that specific commit. This LSN represents the point in time (or log sequence) up to which the master is consistent with the user’s change.

   -- Example query to get the current LSN on PostgreSQL
   SELECT pg_current_wal_lsn();

3. Store User’s LSN: The retrieved LSN is then stored in a Redis instance, acting as an