dbresolver

Golang PostgreSQL HA Router

Golang Database Resolver with PostgreSQL LSN-based causal consistency for any multiple database connections topology, eg. master-slave replication database, cross-region application.

✨ Features

• 🔄 Intelligent Query Routing - Automatic read/write splitting with support for multiple load balancing strategies
• ⚡ PostgreSQL LSN Support - Read-Your-Writes consistency using Log Sequence Numbers (LSN)
• 🔧 Multiple Topologies - Master-slave, multi-master, cross-region configurations
• 🚀 Zero-Stale Reads - Eliminates stale reads after writes with transparent fallback
• 🌐 HTTP Middleware - Automatic LSN cookie management for stateless applications
• 📊 Real-time Monitoring - Built-in replica health monitoring and lag tracking
• 🔒 Production Ready - 100% backward compatible, graceful degradation, connection pooling
• ⚙️ Highly Configurable - Extensive configuration options for all use cases

🚀 PostgreSQL LSN-Based Causal Consistency

What Problem Does LSN Solve?

In typical master-replica setups, writes go to the master and reads go to replicas. However, replication lag can cause users to see stale data immediately after making writes:

User writes to master → Data replicates to replica (delayed)
User immediately reads → Gets stale data from replica ❌

Our LSN Solution

dbresolver uses PostgreSQL's Log Sequence Numbers (LSN) to ensure you always see your writes:

graph TD
    A[Write Operation] --> B[Get Master LSN]
    B --> C[Store LSN in Context/Cookie]
    D[Read Operation] --> E[Check Required LSN]
    E --> F{Replica caught up?}
    F -->|Yes| G[Use Replica]
    F -->|No| H[Use Master]
    C --> E

Key Benefits

• ✅ Read-Your-Writes Consistency - Always see your own writes
• ⚡ Automatic Performance Optimization - Uses replicas when safe
• 🔄 Transparent Fallback - Falls back to master when replicas lag
• 🌐 HTTP Integration - Automatic cookie-based LSN tracking
• 📊 Health Monitoring - Real-time replica health and lag metrics

📦 Installation

go get -u github.com/alfari16/go-pgrouter

⚡ Quick Start

package main

import (
	"context"
	"database/sql"
	"log"
	"time"

	"github.com/alfari16/go-pgrouter"
	_ "github.com/lib/pq"
)

func main() {
	// Open database connections
	primaryDB, err := sql.Open("postgres",
		"host=localhost port=5432 user=postgresrw dbname=mydb sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}

	replicaDB, err := sql.Open("postgres",
		"host=localhost port=5433 user=postgresro dbname=mydb sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}

	// Configure LSN-based causal consistency
	ccConfig := &dbresolver.CausalConsistencyConfig{
		Enabled:          true,                      // Enable LSN features
		Level:            dbresolver.ReadYourWrites, // Consistency level
		FallbackToMaster: true,                      // Fall back to master when needed
		RequireCookie:    false,                     // Don't require cookies for this example
		Timeout:          3 * time.Second,           // LSN query timeout
	}

	// Create resolver with LSN support
	db := dbresolver.New(
		dbresolver.WithPrimaryDBs(primaryDB),
		dbresolver.WithReplicaDBs(replicaDB),
		dbresolver.WithCausalConsistency(ccConfig),
		dbresolver.WithLSNQueryTimeout(3*time.Second),
		dbresolver.WithLoadBalancer(dbresolver.RoundRobinLB),
	)
	defer db.Close()

	// Check if LSN is enabled
	if db.IsCausalConsistencyEnabled() {
		log.Println("✅ LSN-based causal consistency is active")
	}

	// Write operation - updates LSN tracking
	result, err := db.ExecContext(context.Background(),
		"INSERT INTO users (name) VALUES ($1)", "Alice")
	if err != nil {
		log.Fatal(err)
	}

	// This read will intelligently choose replica or master
	// based on whether the replica has caught up
	var user User
	err = db.QueryRowContext(context.Background(),
		"SELECT * FROM users WHERE name = $1", "Alice").
		Scan(&user.ID, &user.Name, &user.Email)

	if err == nil {
		log.Printf("✅ Successfully read user: %+v", user)
		// The query automatically used:
		// - Replica if it has caught up to the write
		// - Master if the replica is lagging behind
	}
}

📚 Examples

HTTP Middleware Integration

package main

import (
	"net/http"
	"time"

	"github.com/alfari16/go-pgrouter"
)

func main() {
	// Setup your database resolver with LSN
	db := setupLSNResolver() // Same as LSN-Enabled Setup example

	// Create causal router for middleware
	router := dbresolver.NewCausalRouter(
		db.GetPrimaryDB(),
		db.GetReplicaDBs(),
		&dbresolver.CausalConsistencyConfig{
			Enabled:       true,
			Level:         dbresolver.ReadYourWrites,
			RequireCookie: true,
			CookieName:    "pg_min_lsn",
			CookieMaxAge:  5 * time.Minute,
		},
	)

	// Create HTTP middleware
	middleware := dbresolver.NewHTTPMiddleware(
		router,        // LSN-aware router
		"pg_min_lsn",  // Cookie name
		5*time.Minute, // Cookie max age
		true,          // Secure cookie (HTTPS only)
		true,          // HttpOnly cookie
	)

	// Apply to your handlers
	http.Handle("/users", middleware(http.HandlerFunc(getUsers)))
	http.Handle("/users/create", middleware(http.HandlerFunc(createUser)))

	http.ListenAndServe(":8080", nil)
}

// createUser handler - writes set LSN cookie automatically
func createUser(w http.ResponseWriter, r *http.Request) {
	db := getDBFromContext(r.Context())

	_, err := db.ExecContext(r.Context(),
		"INSERT INTO users (name) VALUES ($1)", r.FormValue("name"))

	// LSN cookie is automatically set by the middleware
	// with the master's current LSN
}

// getUsers handler - reads use LSN cookie if present
func getUsers(w http.ResponseWriter, r *http.Request) {
	db := getDBFromContext(r.Context())

	// Query automatically routes:
	// - To replica if it has caught up to cookie LSN
	// - To master if replica is lagging
	rows, err := db.QueryContext(r.Context(),
		"SELECT * FROM users")

	// Process results...
}

Manual LSN Control

package main

import (
	"context"
	"database/sql"

	"github.com/alfari16/go-pgrouter"
)

func advancedLSNUsage(db dbresolver.DB) error {
	ctx := context.Background()

	// Perform a write operation
	result, err := db.ExecContext(ctx,
		"INSERT INTO products (name, price) VALUES ($1, $2)",
		"Laptop", 999.99)
	if err != nil {
		return err
	}

	// Explicitly update LSN after write
	lsn, err := db.UpdateLSNAfterWrite(ctx)
	if err != nil {
		return err
	}

	// Store this LSN for future operations
	lastWriteLSN := lsn

	// Create LSN context for strict consistency
	lsnCtx := &dbresolver.LSNContext{
		RequiredLSN: lastWriteLSN,
		Level:       dbresolver.ReadYourWrites,
	}
	ctx = dbresolver.WithLSNContext(ctx, lsnCtx)

	// This query will ALWAYS use master until replica catches up
	var product Product
	err = db.QueryRowContext(ctx,
		"SELECT * FROM products WHERE name = $1", "Laptop").
		Scan(&product.ID, &product.Name, &product.Price)

	// Force master usage regardless of LSN
	forceMasterCtx := &dbresolver.LSNContext{
		ForceMaster: true,
	}
	ctx = dbresolver.WithLSNContext(ctx, forceMasterCtx)

	// This query will always use primary
	var count int
	err = db.QueryRowContext(ctx,
		"SELECT COUNT(*) FROM products").Scan(&count)

	return nil
}

⚙️ Configuration

Basic Options

db := dbresolver.New(
dbresolver.WithPrimaryDBs(primaryDB),
dbresolver.WithReplicaDBs(replicaDB1, replicaDB2),
dbresolver.WithLoadBalancer(dbresolver.RoundRobinLB),
dbresolver.WithMaxReplicationLag(1024*1024), // 1MB max lag
)

LSN Configuration

ccConfig := &dbresolver.CausalConsistencyConfig{
Enabled:          true, // Enable LSN features
Level:            dbresolver.ReadYourWrites, // Consistency level
RequireCookie:    false,                     // Require LSN cookie for reads
CookieName:       "pg_min_lsn",              // HTTP cookie name
CookieMaxAge:     5 * time.Minute, // Cookie lifetime
FallbackToMaster: true,                    // Fall back to master when needed
Timeout:          5 * time.Second, // LSN query timeout
}

db := dbresolver.New(
dbresolver.WithPrimaryDBs(primaryDB),
dbresolver.WithReplicaDBs(replicaDB),
dbresolver.WithCausalConsistency(ccConfig),
dbresolver.WithLSNQueryTimeout(3*time.Second),
dbresolver.WithLSNThrottleTime(100*time.Millisecond),
)

Performance Tuning

db := dbresolver.New(
// ... other options ...
dbresolver.WithMaxReplicationLag(512*1024), // Max 512KB lag
dbresolver.WithLSNQueryTimeout(2*time.Second), // Faster LSN queries
dbresolver.WithLSNThrottleTime(50*time.Millisecond), // More frequent checks
EnableLSNMonitoring(), // Enable background monitoring
)

🏗️ Architecture

Basic Routing Flow

graph LR
    A[Query] --> B{Write Query?}
    B -->|Yes| C[Primary Master]
    B -->|No| D[Load Balancer]
    D --> E[Replica 1]
    D --> F[Replica 2]
    D --> G[Replica N]

LSN-Aware Routing

graph TD
    A[Read Query] --> B[Get Required LSN]
    B --> C{Has LSN Requirement?}
    C -->|No| D[Use Load Balancer]
    C -->|Yes| E[Check Replica LSNs]
    E --> F{Replica ≥ Required LSN?}
    F -->|Yes| G[Use Healthy Replica]
    F -->|No| H[Use Primary Master]
    D --> I[Any Healthy Replica]
    I --> J[Execute Query]
    G --> J
    H --> J

HTTP Middleware Flow

sequenceDiagram
    participant Client
    participant Middleware
    participant DBResolver
    participant Master
    participant Replica

    Note over Client,Master: Write Operation
    Client->>Middleware: POST /users
    Middleware->>DBResolver: Execute write
    DBResolver->>Master: INSERT INTO users...
    Master-->>DBResolver: Success + LSN
    DBResolver-->>Middleware: Response
    Middleware->>Client: Response + Set-Cookie: pg_min_lsn=...

    Note over Client,Master: Read Operation
    Client->>Middleware: GET /users with Cookie
    Middleware->>DBResolver: Execute read with LSN
    DBResolver->>Replica: Check LSN
    Replica-->>DBResolver: LSN status
    alt Replica caught up
        DBResolver->>Replica: SELECT * FROM users...
    else Replica lagging
        DBResolver->>Master: SELECT * FROM users...
    end
    DBResolver-->>Middleware: Results
    Middleware-->>Client: Response

📊 Performance Benchmarks

Based on our internal testing with PostgreSQL 14:

Memory Overhead

• LSN Context: ~64 bytes per request
• Replica Status: ~256 bytes per replica
• Cookie Storage: ~16 bytes

Performance Characteristics

• LSN Query Overhead: < 1ms (throttled)
• Context Creation: Negligible (< 0.01ms)
• Replica Selection: O(n) where n = number of replicas
• Memory Usage: Minimal impact on connection pools

📝 Important Notes

Primary Database Usage

Primary Database is used when you call these functions:

• Exec, ExecContext
• Begin, BeginTx
• Queries with "RETURNING" clause:
  • Query, QueryContext
  • QueryRow, QueryRowContext

Replica Database Usage

Replica Databases will be used when you call these functions:

• Query, QueryContext
• QueryRow, QueryRowContext

LSN-Specific Behavior

• Write Operations: Always update the tracked LSN
• Read Operations: Use replica only if caught up to required LSN
• No LSN Requirement: Can use any healthy replica
• Cookie Present: Automatically enforces read-your-writes
• Master Fallback: Transparent when replicas are lagging

🔍 Monitoring

Check LSN Status

// Check if LSN features are enabled
if db.IsCausalConsistencyEnabled() {
fmt.Println("LSN-based routing is active")
}

// Get current master LSN
masterLSN, err := db.GetCurrentMasterLSN(ctx)
if err == nil {
fmt.Printf("Master LSN: %s\n", masterLSN.String())
}

// Get last known master LSN (cached)
lastKnown := db.GetLastKnownMasterLSN()
if lastKnown != nil {
fmt.Printf("Last known LSN: %s\n", lastKnown.String())
}

Monitor Replica Health

// Get status of all replicas
statuses := db.GetReplicaStatus()
for i, status := range statuses {
fmt.Printf("Replica %d:\n", i+1)
fmt.Printf("  Healthy: %t\n", status.IsHealthy)
fmt.Printf("  Lag: %d bytes\n", status.LagBytes)
fmt.Printf("  Last Check: %s\n", status.LastCheck.Format(time.RFC3339))

if status.LastLSN != nil {
fmt.Printf("  LSN: %s\n", status.LastLSN.String())
}

if status.LastError != nil {
fmt.Printf("  Error: %v\n", status.LastError)
}
}

Best Practices

1. Monitor Replica Lag: Set up alerts for high replication lag
2. Track Master Fallbacks: Monitor how often queries fall back to master
3. LSN Query Performance: Watch for slow LSN queries
4. Connection Pools: Ensure adequate connection pool sizes
5. Cookie Security: Enable Secure/HttpOnly cookies in production

🔄 Migration Guide

From Basic to LSN-Enabled

1. No Breaking Changes: Existing code continues to work
2. Optional Features: LSN is opt-in only
3. Gradual Migration: Enable LSN per application or endpoint

// Before: Basic resolver
db := dbresolver.New(
dbresolver.WithPrimaryDBs(primaryDB),
dbresolver.WithReplicaDBs(replicaDB),
)

// After: Add LSN support
db := dbresolver.New(
dbresolver.WithPrimaryDBs(primaryDB),
dbresolver.WithReplicaDBs(replicaDB),
dbresolver.WithCausalConsistency(&dbresolver.CausalConsistencyConfig{
Enabled: true,
Level:   dbresolver.ReadYourWrites,
}),
)

Configuration Migration

// Old config
dbresolver.WithLoadBalancer(dbresolver.RoundRobinLB)

// New config with LSN
dbresolver.WithLoadBalancer(dbresolver.RoundRobinLB),
dbresolver.WithCausalConsistency(&dbresolver.CausalConsistencyConfig{
Enabled:          true,
Level:            dbresolver.ReadYourWrites,
FallbackToMaster: true,
}),
dbresolver.WithLSNQueryTimeout(3*time.Second),

Use Cases

Usecase 1: Separated RW and RO Database connection

Usecase 2: Cross Region Database

Usecase 3: Multi-Master (Multi-Primary) Database

Usecase 4: High-Transaction Web Applications

Support

You can file an Issue. See documentation in Go.Dev

Contribution

To contrib to this project, you can open a PR or an issue.

When contributing:

• Ensure backward compatibility
• Add tests for new features
• Update documentation
• Consider LSN implications for read/write splitting