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-lag-monitoring.md

View on GitHub
# Replication and HA — Lag Monitoring

## Query on Primary: pg_stat_replication

```sql
-- Shows lag per connected standby
SELECT
    application_name,
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag_bytes
FROM pg_stat_replication
ORDER BY replay_lag DESC NULLS LAST;
```

`replay_lag` is wall-clock time since the primary last confirmed the standby replayed that WAL segment. Alert when it exceeds 30 seconds.

## Query on Replica

```sql
-- Transaction replay lag (most human-readable)
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

-- LSN-based lag in bytes (run on replica, compare to primary sent_lsn)
SELECT
    pg_last_wal_receive_lsn()  AS received_lsn,
    pg_last_wal_replay_lsn()   AS replayed_lsn,
    pg_wal_lsn_diff(
        pg_last_wal_receive_lsn(),
        pg_last_wal_replay_lsn()
    )                           AS receive_vs_replay_bytes,
    pg_is_in_recovery()        AS is_replica;
```

## Go Lag Monitor

```go
// internal/db/replication_monitor.go
package db

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

    "github.com/jmoiron/sqlx"
)

const lagWarningThreshold = 30 * time.Second

// CheckReplicationLag queries pg_stat_replication on the primary and logs
// a warning for any standby that exceeds the threshold.
func CheckReplicationLag(ctx context.Context, primary *sqlx.DB, logger *slog.Logger) error {
    const q = `
        SELECT
            application_name,
            state,
            EXTRACT(EPOCH FROM replay_lag)::bigint  AS replay_lag_seconds,
            pg_wal_lsn_diff(sent_lsn, replay_lsn)  AS lag_bytes
        FROM pg_stat_replication`

    type row struct {
        ApplicationName  string `db:"application_name"`
        State            string `db:"state"`
        ReplayLagSeconds int64  `db:"replay_lag_seconds"`
        LagBytes         int64  `db:"lag_bytes"`
    }

    var rows []row
    if err := primary.SelectContext(ctx, &rows, q); err != nil {
        return fmt.Errorf("pg_stat_replication query: %w", err)
    }

    if len(rows) == 0 {
        logger.WarnContext(ctx, "no standbys connected to primary")
        return nil
    }

    for _, r := range rows {
        lag := time.Duration(r.ReplayLagSeconds) * time.Second
        if lag > lagWarningThreshold {
            logger.WarnContext(ctx, "replication lag exceeds threshold",
                "standby", r.ApplicationName,
                "state", r.State,
                "lag", lag.String(),
                "lag_bytes", r.LagBytes,
            )
        } else {
            logger.InfoContext(ctx, "replication healthy",
                "standby", r.ApplicationName,
                "lag", lag.String(),
            )
        }
    }
    return nil
}
```

When `ReplayLagSeconds` exceeds the threshold, route reads to primary until lag recovers:

```go
// ShouldUsePrimary returns true when replica lag is too high.
func ShouldUsePrimary(ctx context.Context, replica *sqlx.DB) bool {
    var lagSeconds float64
    err := replica.QueryRowContext(ctx,
        `SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))`,
    ).Scan(&lagSeconds)
    if err != nil || lagSeconds > 30 {
        return true
    }
    return false
}
```
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