Timezone Drift: Replica Lag Shifts Seed Windows

Your seed pipeline runs clean at 11:45 PM UTC. At 00:05 AM UTC it starts generating records that belong to "yesterday" by one timezone and "today" by another — and your windowed assertions start failing in ways that look like race conditions. They're not. The replica your seed factory reads for reference data is 90 seconds behind primary, and that lag just straddled a day boundary. The bug isn't in your application code; it's in the implicit assumption that your seed clock and your replica clock agree on what window they're in.

This problem compounds in multi-region setups where a single seed job fans out to replicas in us-east-1, eu-west-1, and ap-southeast-1. Each replica carries its own lag offset, and each region interprets UTC wall-clock differently once you factor in local business-day logic baked into the seed factory. The result: three replicas, three window boundaries, one broken dataset.

By the end of this article you'll know how to instrument your seed pipeline to detect replica-lag-induced window drift, how to pin seed clocks to a lag-corrected reference time, and how to write assertions that survive the boundary crossing without becoming timing-dependent.

Why Replica Lag Becomes a Timezone Problem

A seed window boundary is any time range your factory uses to scope generated records — order dates within the current billing cycle, events within today's audit window, sessions within a rolling 24-hour period. When your factory reads a reference timestamp from a replica (e.g., SELECT MAX(created_at) FROM orders to anchor relative offsets), it gets a value that lags primary by however many seconds replication is behind. In Postgres streaming replication under moderate write load, that lag is typically 1–120 seconds but can spike to 10+ minutes during a vacuum or a large transaction. At a day or DST boundary, even 60 seconds is enough to land generated records in the wrong window.

This is structurally different from a simple clock-skew bug. The replica isn't wrong — it's consistently behind. The seed factory treats the replica's MAX(created_at) as "now," which means every relative timestamp it generates inherits the lag offset. If your downstream assertions compare those records against the application's wall clock (which reads primary), the window mismatch surfaces as intermittent failures that disappear on re-run once replication catches up. Teams frequently misdiagnose this as a flaky test and add a time.sleep(2). That fixes nothing; it just shifts the failure window by two seconds. For related patterns around how naive seed clocks break sequence logic, see how timezone-naive seed clocks break sequence window assertions.

Pinning Seed Clocks to a Lag-Corrected Reference Time

The fix starts with measuring lag explicitly and injecting a corrected reference time into your factory. In Postgres, pg_stat_replication gives you replay_lag on primary; on the replica side, pg_last_xact_replay_timestamp() is your anchor. Build a small utility that reads both and returns a lag-corrected "seed now":

import psycopg2
from datetime import timezone
from zoneinfo import ZoneInfo

def get_lag_corrected_now(replica_dsn: str) -> "datetime":
    """
    Returns UTC now minus measured replica lag.
    Use this as the seed clock anchor — never datetime.utcnow() directly.
    """
    with psycopg2.connect(replica_dsn) as conn:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT
                    NOW() AT TIME ZONE 'UTC'                        AS wall_utc,
                    pg_last_xact_replay_timestamp() AT TIME ZONE 'UTC' AS replay_utc
            """)
            row = cur.fetchone()
    wall, replay = row
    lag = wall - replay          # timedelta; typically 1s–120s, can spike
    # Subtract lag so generated records land inside the window primary sees
    return (wall - lag).replace(tzinfo=timezone.utc)

Pass this value into your factory rather than calling datetime.utcnow() or datetime.now(tz=timezone.utc) inline. With factory_boy, override the _create classmethod or use a LazyAttribute that accepts an injected clock:

import factory
from factory.django import DjangoModelFactory

class OrderFactory(DjangoModelFactory):
    class Meta:
        model = Order

    class Params:
        seed_now = factory.LazyFunction(lambda: None)  # injected externally

    created_at = factory.LazyAttribute(
        lambda o: o.seed_now or get_lag_corrected_now(REPLICA_DSN)
    )
    window_date = factory.LazyAttribute(
        lambda o: o.created_at.astimezone(ZoneInfo("America/New_York")).date()
    )

The window_date derivation is the critical step: always compute it from the lag-corrected created_at, never from a separate date.today() call that reads the system clock. Before this change, a 90-second replica lag at 23:59:30 UTC was causing roughly 1-in-50 nightly seed runs to populate window_date as the next calendar day while created_at remained in the prior day — a silent inconsistency that only surfaced in billing-cycle aggregation tests 6–8 hours later.

For CI pipelines, bake the lag check into a pre-seed health gate. If lag exceeds your threshold (e.g., 300 seconds), fail fast rather than generating a corrupt dataset. This is especially important when your seed pipeline spans multiple replicas — a pattern explored in depth in the context of timezone cascade failures across replica-lagged seed pipelines. A GitHub Actions step that gates on lag looks like:

# .github/workflows/seed.yml
- name: Check replica lag
  run: |
    python - <<'EOF'
    import psycopg2, sys
    conn = psycopg2.connect("${{ secrets.REPLICA_DSN }}")
    cur = conn.cursor()
    cur.execute("""
      SELECT EXTRACT(EPOCH FROM (
        NOW() - pg_last_xact_replay_timestamp()
      ))::int AS lag_seconds
    """)
    lag = cur.fetchone()[0]
    print(f"Replica lag: {lag}s")
    if lag > 300:
        print("ERROR: lag exceeds threshold; aborting seed run")
        sys.exit(1)
    EOF

Where Senior Engineers Still Get Burned

Reading lag once at pipeline start and treating it as constant is the most common mistake. Lag is a point-in-time measurement; it changes as the pipeline runs. A seed job that takes 8 minutes to populate a large dataset can start with 2-second lag and end with 45-second lag if a long transaction hits primary mid-run. The fix is to re-sample lag at each factory batch boundary, not just at job startup. Alternatively, snapshot the primary's pg_current_wal_lsn() at start and wait for the replica's pg_last_wal_replay_lsn() to catch up before beginning generation — this is more expensive but removes the variable entirely.

Using CURRENT_TIMESTAMP inside seed SQL scripts is the second trap. When you generate records via INSERT INTO orders (created_at) VALUES (CURRENT_TIMESTAMP - INTERVAL '1 hour') executed against the replica, CURRENT_TIMESTAMP reflects the replica's clock, which may itself drift from primary's system time independently of replication lag. Always compute timestamps in application code with an explicit timezone.utc anchor, then pass them as bind parameters. This also applies to cloned-schema test databases where schema and data drift compound each other.

Myths That Keep This Bug Alive

"Our replica lag is always under 5 seconds, so it can't affect daily windows." Five seconds of lag at 23:59:57 UTC is enough to push a record across a day boundary. The failure rate is low — maybe 1 in 1440 daily runs — which is exactly why it survives for months before anyone connects the dots. Boundary conditions need explicit guards, not statistical comfort from average-case lag numbers. If your seed factory generates any timestamp relative to "now," it is exposed to this failure mode regardless of typical lag magnitude. The same reasoning applies to DST crossings, where even a 1-second offset can land a record in the wrong offset epoch — a pattern documented for seed factories that need to survive DST boundary crossings.

"We use UTC everywhere, so timezone drift isn't our problem." UTC eliminates offset ambiguity but does nothing about window boundary semantics. If your application defines "today's orders" as created_at::date = CURRENT_DATE AT TIME ZONE 'America/Chicago', then a UTC-anchored seed timestamp that's 90 seconds stale can still land in yesterday's Chicago date. UTC is a necessary condition for correctness, not a sufficient one. Audit every window predicate in your application and confirm which timezone governs the boundary — then make your seed factory use that same timezone for its window derivation.

Replica lag is infrastructure noise that becomes a data correctness problem the moment your seed factory treats a lagged timestamp as authoritative. Instrument lag measurement into your pipeline, inject a corrected clock into your factories, and gate CI runs on a lag threshold. The next concrete step: run pg_last_xact_replay_timestamp() against your staging replica right now and check how far it trails NOW() at your nightly seed window boundary. The number will surprise you.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.