Golang Database Resolver with PostgreSQL LSN-based causal consistency for any multiple database connections topology, eg. master-slave replication database, cross-region application.
- 🔄 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
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 ❌
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
- ✅ 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
go get -u github.com/alfari16/go-pgrouterClick to Expand
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
}
}Click to Expand
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...
}Click to Expand
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
}db := dbresolver.New(
dbresolver.WithPrimaryDBs(primaryDB),
dbresolver.WithReplicaDBs(replicaDB1, replicaDB2),
dbresolver.WithLoadBalancer(dbresolver.RoundRobinLB),
dbresolver.WithMaxReplicationLag(1024*1024), // 1MB max lag
)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),
)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
)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]
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
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
Based on our internal testing with PostgreSQL 14:
| Operation | Without LSN | With LSN (Replica Hit) | With LSN (Master Fallback) |
|---|---|---|---|
| Simple SELECT | 0.5ms | 0.6ms (+20%) | 0.8ms (+60%) |
| INSERT + SELECT | 1.2ms | 1.5ms (+25%) | 1.6ms (+33%) |
| Batch Read (100 rows) | 15ms | 16ms (+6%) | 18ms (+20%) |
- LSN Context: ~64 bytes per request
- Replica Status: ~256 bytes per replica
- Cookie Storage: ~16 bytes
- 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
Primary Database is used when you call these functions:
Exec,ExecContextBegin,BeginTx- Queries with
"RETURNING"clause:Query,QueryContextQueryRow,QueryRowContext
Replica Databases will be used when you call these functions:
Query,QueryContextQueryRow,QueryRowContext
- 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
// 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())
}// 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)
}
}- Monitor Replica Lag: Set up alerts for high replication lag
- Track Master Fallbacks: Monitor how often queries fall back to master
- LSN Query Performance: Watch for slow LSN queries
- Connection Pools: Ensure adequate connection pool sizes
- Cookie Security: Enable Secure/HttpOnly cookies in production
- No Breaking Changes: Existing code continues to work
- Optional Features: LSN is opt-in only
- 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,
}),
)// 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),Click to Expand
- You have your application deployed
- Your application is heavy on read operations
- Your DBs replicated to multiple replicas for faster queries
- You separate the connections for optimized query
Click to Expand
- Your application deployed to multi regions.
- You have your Databases configured globally.
Click to Expand
- You're using a Multi-Master database topology eg, Aurora Multi-Master
Click to Expand
- E-commerce platforms where users need to see their orders immediately
- Social media feeds where posts appear instantly to the author
- Banking applications where transaction history must be up-to-date
- Any application where user experience depends on seeing recent writes
You can file an Issue. See documentation in Go.Dev
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