Mortar — Postgres read replicas

Status: the primitive is planned; feature DAOs adopt it case-by-case as read pressure on the primary surfaces in pg_stat_statements.

Mortar’s primary Postgres handles every write + most reads. As traffic grows, read-heavy paths (analytics dashboards, list views, reporting) move to one or more streaming-replication followers via Store.ReadOnly(). Writes always go through the primary.

When to use ReadOnly()

Use Store.ReadOnly() for queries where stale data is acceptable:

• /v1/{tenant}/usage/me — credit-ledger aggregates. Late by replica lag, not user-visible (dashboard refreshes anyway).
• Admin analytics dashboards (per-tenant compute usage, file-size histograms, etc.).
• Reporting / billing exports.
• GET /v1/{tenant}/db/tables/{table}/rows when the caller explicitly marks the request as read-only (planned SDK opt-in).

Do NOT use ReadOnly() for:

• Authentication flows. A freshly-rotated API key may not yet exist on the replica; reading it through the replica would 401 a valid request.
• Read-after-write within the same request. Caller wrote to primary, replica hasn’t caught up yet (replica lag, see below).
• Anything participating in Store.WithTenant / Store.WithoutTenant transactions — those run against the primary by design.
• Webhooks / payment verification — race conditions cost money.

Rule of thumb: if the worst case of “your data is 5 seconds stale” breaks the feature, don’t use ReadOnly().

Replica lag

PostgreSQL streaming replication is asynchronous by default. Reads through ReadOnly() may be:

Monitor via:

SELECT pid, client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
  FROM pg_stat_replication;

When lag_bytes exceeds ~1 GB on any replica, route ALL reads to the primary until it catches up (planned: health check + automatic failover).

Failure modes

Replica down: ReadOnly() continues returning the dead replica until the operator removes it from MORTAR_DB_REPLICA_URLS and redeploys. Callers see query errors.

Planned enhancement: add a per-replica health-check goroutine that pings every 5s and removes unhealthy replicas from the rotation.

Workaround today: callers should handle database errors from ReadOnly() by falling back to *Store.DB (primary). Example:

db := s.ReadOnly()
var rows []Row
if err := db.Find(&rows).Error; err != nil {
    // Replica might be down; fall back to primary.
    if err := s.DB.Find(&rows).Error; err != nil {
        return nil, err
    }
}

All replicas down: ReadOnly() keeps returning replicas (we don’t remove on per-query error). Caller must fall back as above. A planned enhancement removes dead replicas from the rotation automatically.

Stale replica accepted as primary: never happens. ReadOnly() ONLY routes reads to replicas; writes always go through *Store.DB.

Config

# Comma-separated list of read-replica DSNs.  Same shape as MORTAR_DB_URL.
MORTAR_DB_REPLICA_URLS=postgres://reader:pw@replica1.internal:5432/mortar,postgres://reader:pw@replica2.internal:5432/mortar

Each replica is dialed + pinged at boot; misconfig fails fast. Empty / unset → ReadOnly() returns the primary (no behaviour change vs deployments without replicas configured).

Replica credentials should be a read-only Postgres role — RLS policies still apply, but write attempts would be wasted. Create once per cluster:

CREATE USER mortar_ro WITH PASSWORD '...' NOSUPERUSER NOBYPASSRLS;
GRANT CONNECT ON DATABASE mortar TO mortar_ro;
GRANT USAGE ON SCHEMA public TO mortar_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mortar_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mortar_ro;

Then point the replica DSN at the read-only host using the mortar_ro credentials.

Implementation

primitive/database.Store.ReadOnly() round-robins via sync/atomic.Uint64. No per-query routing logic; callers explicitly opt in.

The rotation is process-local: each Mortar instance has its own counter. This is fine — replica selection only needs balance across the cluster, not strict round-robin across instances.

func (s *Store) ReadOnly() *gorm.DB {
    if len(s.Replicas) == 0 {
        return s.DB // primary fallback
    }
    idx := s.rrCounter.Add(1) - 1
    return s.Replicas[idx%uint64(len(s.Replicas))]
}

Roadmap

• Per-replica health goroutine + automatic failover.
• Per-replica weighted routing (e.g. one huge analytics replica + two small dashboards replicas).
• Cross-region replica routing (paired with multi-region deploy; see multi-region.md).
• Optional read-your-own-writes: stash last write LSN in the user’s session and refuse to route to replicas behind that LSN.