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-readwrite.md

View on GitHub
# Replication and HA — Read/Write Splitting in Go

## Connection Struct

```go
// internal/db/connections.go
type Connections struct {
    primary *sqlx.DB
    replica *sqlx.DB
    logger  *slog.Logger
}

func (c *Connections) Primary() *sqlx.DB { return c.primary }
func (c *Connections) Replica() *sqlx.DB { return c.replica }
func (c *Connections) Close()            { c.primary.Close(); c.replica.Close() }

func NewConnections(primaryDSN, replicaDSN string, logger *slog.Logger) (*Connections, error) {
    primary, err := sqlx.Connect("postgres", primaryDSN)
    if err != nil { return nil, fmt.Errorf("primary connect: %w", err) }
    primary.SetMaxOpenConns(20); primary.SetMaxIdleConns(5)
    primary.SetConnMaxLifetime(5 * time.Minute); primary.SetConnMaxIdleTime(1 * time.Minute)

    replica, err := sqlx.Connect("postgres", replicaDSN)
    if err != nil { primary.Close(); return nil, fmt.Errorf("replica connect: %w", err) }
    replica.SetMaxOpenConns(40); replica.SetMaxIdleConns(10)  // replicas absorb more reads
    replica.SetConnMaxLifetime(5 * time.Minute); replica.SetConnMaxIdleTime(1 * time.Minute)

    return &Connections{primary: primary, replica: replica, logger: logger}, nil
}
```

## Repository Pattern: Routing by Operation

```go
// internal/repository/order_repo.go
package repository

import (
    "context"
    "fmt"

    "github.com/jmoiron/sqlx"
    appdb "myapp/internal/db"
)

type OrderRepo struct {
    conns *appdb.Connections
}

func NewOrderRepo(conns *appdb.Connections) *OrderRepo {
    return &OrderRepo{conns: conns}
}

// Create writes to primary — always consistent.
func (r *OrderRepo) Create(ctx context.Context, o *Order) error {
    const q = `
        INSERT INTO orders (id, user_id, total, status, created_at)
        VALUES (:id, :user_id, :total, :status, now())
        RETURNING created_at`
    rows, err := r.conns.Primary().NamedQueryContext(ctx, q, o)
    if err != nil {
        return fmt.Errorf("order create: %w", err)
    }
    defer rows.Close()
    if rows.Next() {
        return rows.Scan(&o.CreatedAt)
    }
    return nil
}

// List reads from replica — acceptable lag for list views.
func (r *OrderRepo) List(ctx context.Context, userID string, limit int) ([]Order, error) {
    const q = `
        SELECT id, user_id, total, status, created_at
        FROM orders
        WHERE user_id = $1
        ORDER BY created_at DESC
        LIMIT $2`
    var orders []Order
    if err := r.conns.Replica().SelectContext(ctx, &orders, q, userID, limit); err != nil {
        return nil, fmt.Errorf("order list: %w", err)
    }
    return orders, nil
}

// GetByID supports read-your-writes: pass strong=true after a write
// to ensure the caller sees what they just created.
func (r *OrderRepo) GetByID(ctx context.Context, id string, strong bool) (*Order, error) {
    db := r.conns.Replica()
    if strong {
        db = r.conns.Primary()
    }
    var o Order
    if err := db.GetContext(ctx, &o, `SELECT * FROM orders WHERE id = $1`, id); err != nil {
        return nil, fmt.Errorf("order get %s: %w", id, err)
    }
    return &o, nil
}
```

## Read-Your-Writes via Context Flag

Pass a context key so any layer can signal "use primary for this request":

```go
// internal/db/ctx.go
package db

import "context"

type ctxKey struct{}

// WithStrongRead marks the context to force primary reads.
func WithStrongRead(ctx context.Context) context.Context {
    return context.WithValue(ctx, ctxKey{}, true)
}

// IsStrongRead returns true if the context requires primary read.
func IsStrongRead(ctx context.Context) bool {
    v, _ := ctx.Value(ctxKey{}).(bool)
    return v
}
```

In the repository, replace the `strong bool` parameter:

```go
func (r *OrderRepo) pickDB(ctx context.Context) *sqlx.DB {
    if appdb.IsStrongRead(ctx) {
        return r.conns.Primary()
    }
    return r.conns.Replica()
}
```

In the handler, after a POST, tag the redirect context:

```go
ctx := appdb.WithStrongRead(c.Request.Context())
order, err := repo.GetByID(ctx, newOrderID, 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