How Does Your Replica Know Where It Left Off?

Every replica needs to track its progress and know where to resume if the stream is interrupted. Postgres and MySQL handle this differently, and those differences matter for failover, recovery, and change-data capture. Here's how Postgres LSNs, MySQL binlog positions, and GTIDs compare, and what those differences mean.

Postgres: the LSN

Postgres writes every change to its write-ahead log (WAL) before applying it. A Log Sequence Number (LSN) is a 64 bit integer that identifies a byte position in that log, written like 16/B374D848. Subtracting two LSNs gives the bytes of WAL between them, letting you measure how far behind a replica is in bytes. A replica reports the last LSN it has received, written, flushed, and replayed. Those distinguish changes it has stored from changes it has applied. After failover, timeline history matters too: the same LSN can refer to different changes on diverging timelines.

MySQL: the binlog position and GTIDs

MySQL's equivalent log is the binary log, traditionally tracked by filename and byte offset, like mysql-bin.000001:73. A replica records its progress using these coordinates so it can resume after interruption.

The catch here is failover because binlog coordinates are local to each server. The old primary's position doesn't identify the same point on the new primary. Translating the old coordinate into the right position requires additional tracking or tooling, which has historically made MySQL failover delicate.

The Global Transaction Identifier (GTIDs) exists to fix exactly that issue by identifying transactions instead of file locations. With GTIDs enabled, each transaction gets a unique ID that stays the same across servers. In Oracle MySQL, it's formatted as a source identifier (server uuid) and an ever-increasing transaction number, like 3E11FA47-71CA-11E1-9E33-C80AA9429562:23, identifying transaction 23 from that originating server.

With GTID auto-positioning, a replica can tell a new primary exactly which transactions it already has, and the new primary knows where to continue. That simplifies failover, provided the new primary still has the required transactions in its binlog. For a deeper look, Marcelo Altmann's 11-part MySQL binary log series decodes real binlog files byte by byte, starting here.

From replication to CDC

LSNs and binlog positions identify log locations; GTIDs identify transactions. That distinction changes how you measure progress: LSN differences show bytes of WAL remaining, while comparing GTID sets reveals missing transactions. Neither guarantees recovery on its own: you still need the right history and access to the required logs.

Change-data capture (CDC) uses this tracking to feed database changes to downstream systems without repeatedly scanning tables. Tracking what has been processed lets those systems resume after interruptions without skipping or duplicating changes. Readyset is one example: it follows the change stream to incrementally update cached query results, without application code managing invalidation.

🔍 What We're Watching in the Database Ecosystem

I timed 179 index recommendations on real data. 18% made the query slower.

Prateek Arora tested 179 index recommendations from his own tool on real IMDb data. Of those, 128 made queries at least 15% faster, 32 made them at least 5% slower, and 7 more than doubled in runtime. Hypothetical index validation missed regressions because it relied on the planner's incorrect row estimates. As agents take on more database tuning, the lesson matters: recommendations need actual execution context, and fixes still need measured results.

The Vector That Lied About Its Dimensions

pgvector 0.8.7 fixes an out-of-bounds write that can reach arbitrary code execution, and every version through 0.8.6 is affected. The IVFFlat build took its loop bound from each vector's own header rather than the column's declared dimensions, so a mismatched vector wrote past the end of the array. Christophe Pettus notes the upgrade alone isn't enough, since backends that loaded the old shared library keep running the old code until they exit.

Nobody Patches the Pooler

PgBouncer 1.26.0 fixes three CVEs, two reachable without any credentials. One is an integer overflow that hangs the process rather than crashing it, and since PgBouncer is single-threaded, that freezes every client behind it.

Are we reverting patches because of bugs found by AI?

Several major features came out of Postgres 19 over the past month, pushing the release roughly a month past its original date. Tomas Vondra pushes back on the explanation circulating in recent posts that AI found bugs too complex to fix this late, and argues the real reasons look different.

In Case You Missed It

New on our blog:

What Is a Wire-Compatible SQL Caching Proxy?: Your driver, your ORM, and your queries stay as they are, and the connection string points somewhere new. A walkthrough of what a proxy has to get right to pull that off, down to the MySQL handshake.

We're getting more social, catch the latest on Bluesky, X, dev.to, and Medium too.

Know a database aficionado who'd appreciate this? Forward it their way.