Skip to content

henriqueatila/golang-gin-best-practices / skills/golang-gin-psql-dba

golang-gin-psql-dba

Flagged

PostgreSQL DBA for Go Gin APIs. Use when designing schemas, analyzing migrations, choosing indexes, optimizing queries, or selecting extensions (pgvector, PostGIS, TimescaleDB).

FileKindSize
SKILL.mdskill md9.9 KB
metadata.jsonconfig668 B
references/backup-and-recovery-managed-and-schedules.mdmarkdown4.3 KB
references/backup-and-recovery-strategies.mdmarkdown4.8 KB
references/backup-and-recovery-wal-and-validation.mdmarkdown2.7 KB
references/extensions-toolkit-crypto-and-uuid.mdmarkdown4.1 KB
references/extensions-toolkit-partman-and-trgm.mdmarkdown4.5 KB
references/extensions-toolkit-pgaudit-and-stats.mdmarkdown4.3 KB
references/extensions-toolkit-setup-and-cron.mdmarkdown3.8 KB
references/index-strategy-maintenance-and-explain.mdmarkdown4.4 KB
references/index-strategy-types-and-variants.mdmarkdown3.8 KB
references/migration-impact-backfill-and-lock-timeout.mdmarkdown3.5 KB
references/migration-impact-lock-timeout.mdmarkdown4.6 KB
references/migration-impact-locks-and-operations.mdmarkdown6.4 KB
references/paradedb-advanced-search-and-hybrid.mdmarkdown4.7 KB
references/paradedb-go-integration-and-tips.mdmarkdown4.9 KB
references/paradedb-setup-and-basic-search.mdmarkdown4.5 KB
references/pgvector-capacity-and-performance.mdmarkdown4.3 KB
references/pgvector-queries-and-go-integration.mdmarkdown4.4 KB
references/pgvector-setup-and-schema.mdmarkdown4.9 KB
references/postgis-go-integration-and-performance.mdmarkdown3.8 KB
references/postgis-setup-types-and-indexes.mdmarkdown4.3 KB
references/query-performance-pool-and-replicas.mdmarkdown4.6 KB
references/query-performance-stats-and-vacuum.mdmarkdown4.2 KB
references/replication-and-ha-clusters.mdmarkdown3.4 KB
references/replication-and-ha-go-readwrite.mdmarkdown4.0 KB
references/replication-and-ha-go-resilience.mdmarkdown4.8 KB
references/replication-and-ha-lag-monitoring.mdmarkdown3.3 KB
references/replication-and-ha-logical.mdmarkdown2.2 KB
references/replication-and-ha-managed-services.mdmarkdown2.0 KB
references/replication-and-ha-overview.mdmarkdown3.1 KB
references/replication-and-ha-streaming-setup.mdmarkdown3.6 KB
references/row-level-security-go-integration-and-pitfalls.mdmarkdown5.5 KB
references/row-level-security-setup-and-policies.mdmarkdown5.8 KB
references/schema-design-complete-example.mdmarkdown6.9 KB
references/schema-design-constraints-and-normalization.mdmarkdown4.2 KB
references/schema-design-naming-and-types.mdmarkdown3.9 KB
references/schema-design-soft-delete-and-tenancy.mdmarkdown5.1 KB
references/timescaledb-aggregates-compression-retention.mdmarkdown5.7 KB
references/timescaledb-go-integration.mdmarkdown3.5 KB
references/timescaledb-setup-and-hypertables.mdmarkdown4.8 KB

references/replication-and-ha-go-resilience.md

View on GitHub
# Replication and HA — Go Application Resilience

## Connection Retry with Exponential Backoff

```go
// internal/db/wait.go
package db

import (
    "context"
    "fmt"
    "log/slog"
    "time"

    "github.com/jmoiron/sqlx"
    _ "github.com/lib/pq"
)

// WaitForConnection attempts to connect to dsn with exponential backoff.
// Returns the connected *sqlx.DB or an error after maxAttempts.
func WaitForConnection(ctx context.Context, dsn string, maxAttempts int, logger *slog.Logger) (*sqlx.DB, error) {
    var db *sqlx.DB
    var err error
    delay := 500 * time.Millisecond

    for attempt := 1; attempt <= maxAttempts; attempt++ {
        db, err = sqlx.ConnectContext(ctx, "postgres", dsn)
        if err == nil {
            logger.InfoContext(ctx, "database connected", "attempt", attempt)
            return db, nil
        }
        logger.WarnContext(ctx, "database connection failed",
            "attempt", attempt, "max", maxAttempts,
            "delay", delay.String(), "err", err,
        )
        select {
        case <-ctx.Done():
            return nil, fmt.Errorf("context cancelled waiting for db: %w", ctx.Err())
        case <-time.After(delay):
        }
        delay *= 2
        if delay > 30*time.Second {
            delay = 30 * time.Second
        }
    }
    return nil, fmt.Errorf("database unavailable after %d attempts: %w", maxAttempts, err)
}
```

## Circuit Breaker for Replica Unavailability

```go
// internal/db/circuit.go
package db

import (
    "context"
    "log/slog"
    "sync/atomic"
    "time"

    "github.com/jmoiron/sqlx"
)

// ReplicaCircuit tracks replica availability. When tripped, reads fall back to primary.
type ReplicaCircuit struct {
    tripped    atomic.Bool
    primary    *sqlx.DB
    replica    *sqlx.DB
    resetAfter time.Duration
    logger     *slog.Logger
}

func NewReplicaCircuit(primary, replica *sqlx.DB, resetAfter time.Duration, logger *slog.Logger) *ReplicaCircuit {
    return &ReplicaCircuit{primary: primary, replica: replica, resetAfter: resetAfter, logger: logger}
}

// DB returns the replica if healthy, or primary if the circuit is tripped.
func (rc *ReplicaCircuit) DB(ctx context.Context) *sqlx.DB {
    if rc.tripped.Load() {
        return rc.primary
    }
    return rc.replica
}

// RecordFailure trips the circuit and schedules an automatic reset.
func (rc *ReplicaCircuit) RecordFailure(ctx context.Context) {
    if rc.tripped.CompareAndSwap(false, true) {
        rc.logger.WarnContext(ctx, "replica circuit tripped — routing reads to primary",
            "reset_after", rc.resetAfter.String(),
        )
        go func() {
            time.Sleep(rc.resetAfter)
            if err := rc.replica.PingContext(context.Background()); err == nil {
                rc.tripped.Store(false)
                rc.logger.Info("replica circuit reset — replica is healthy again")
            }
        }()
    }
}
```

Usage in a repository:

```go
func (r *OrderRepo) ListWithCircuit(ctx context.Context, userID string) ([]Order, error) {
    db := r.circuit.DB(ctx)
    var orders []Order
    if err := db.SelectContext(ctx, &orders,
        `SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 100`, userID,
    ); err != nil {
        r.circuit.RecordFailure(ctx)
        if err2 := r.conns.Primary().SelectContext(ctx, &orders,
            `SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 100`, userID,
        ); err2 != nil {
            return nil, fmt.Errorf("order list fallback: %w", err2)
        }
    }
    return orders, nil
}
```

## Graceful Handling of Failover

During a Patroni or RDS failover, existing connections receive `driver: bad connection`. The standard `database/sql` pool retries transparently, but long-lived idle connections may need eviction:

```go
// ForceReconnect evicts idle connections to force re-dial against the new primary.
func ForceReconnect(db *sqlx.DB) {
    db.SetMaxIdleConns(0)               // evict all idle connections immediately
    time.Sleep(100 * time.Millisecond)
    db.SetMaxIdleConns(5)               // restore normal idle pool
}
```

Set `ConnMaxLifetime` shorter than the failover window (1–2 minutes) so connections naturally recycle.

## Checklist

- [ ] Streaming replication configured: `wal_level = replica`, `max_wal_senders` set, `replicator` role created
- [ ] Standby verified with `pg_stat_replication` showing `streaming` state
- [ ] Replication lag monitored: alert threshold at 30 seconds (`replay_lag`)
- [ ] Go application uses separate `primary` and `replica` connection pools
- [ ] Read-your-writes pattern implemented for post-write reads
- [ ] Failover tested: promote standby manually, verify app reconnects within `ConnMaxLifetime`
- [ ] Circuit breaker or fallback routes reads to primary when replica unreachable
- [ ] WAL archiving enabled if PITR required (`archive_mode = on`, `archive_command`)
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall