# LSN Tracking

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

```{contents} Table of Contents
:depth: 2
:local: true
```

## 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
:emphasize-lines: 10-15, 42-52
:language: python
:lines: 18-99
```

## How It Works

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

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

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

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

**Step 3: Compare** LSNs:

```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

```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