LSN Tracking
=============

LSN (Log Sequence Number) tracking provides precise knowledge of PostgreSQL replication state, enabling smart cache management.

.. contents:: Table of Contents
   :local:
   :depth: 2

What is LSN?
------------

**LSN** = PostgreSQL Write-Ahead Log sequence number

**Format**: ``file_offset/byte_offset`` (e.g., ``0/12345678``)

**Properties**:

* Monotonically increasing
* Unique per WAL record
* Same across main and replicas
* Indicates exact replication position

Core Implementation
-------------------

Key code from ``lsn_tracker.py``:

.. literalinclude:: ../../../ccat_ops_db_api/transaction_buffering/lsn_tracker.py
   :language: python
   :lines: 18-99
   :emphasize-lines: 10-15, 42-52

How It Works
------------

**Step 1: Capture LSN** after main DB commit:

.. code-block:: sql

   SELECT pg_current_wal_lsn();
   -- Returns: 0/12345678

**Step 2: Poll replica** for replay LSN:

.. code-block:: sql

   SELECT pg_last_wal_replay_lsn();
   -- Returns: 0/12345600 (behind)

**Step 3: Compare** LSNs:

.. code-block:: python

   if replica_lsn >= main_lsn:
       # Replicated!
       await cleanup_cache()
   else:
       # Still replicating
       await extend_cache_ttl()

Why LSN Tracking Matters
-------------------------

**Without LSN** (guessing):

* Hard-coded delays (5 seconds? 60 seconds?)
* Wasted time or missed replication
* No way to know actual state

**With LSN** (precision):

* Know exactly when replicated
* Cleanup caches at right time
* Monitor and alert on lag

Configuration
-------------

.. code-block:: bash

   LSN_TRACKING_ENABLED=true
   LSN_CHECK_INTERVAL=0.1  # seconds between checks
   LSN_TIMEOUT=30  # seconds before giving up

Next Steps
----------

* :doc:`smart-query-manager` - Using LSN for cache decisions
* :doc:`../../architecture/database-topology` - Replication details