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/migration-impact-backfill-and-lock-timeout.md

View on GitHub
# Migration Impact — Batched Backfill, NOT VALID, lock_timeout, and Checklists

## Batched Backfill Pattern in Go

Never backfill millions of rows in a single `UPDATE`. Use cursor-based batches — O(batch_size) per batch regardless of position.

```sql
-- Cursor-based: uses PK as cursor to avoid OFFSET penalty
UPDATE users
SET    status = 'active'
WHERE  id IN (
    SELECT id FROM users
    WHERE  status IS NULL
    AND    id > $1
    ORDER  BY id
    LIMIT  $2
)
RETURNING id;
```

```go
// internal/migration/backfill.go
package migration

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

    "github.com/jmoiron/sqlx"
)

const defaultBatchSize = 1000

func BackfillUsersStatus(ctx context.Context, db *sqlx.DB, logger *slog.Logger) error {
    const batchSQL = `
        UPDATE users
        SET    status = 'active'
        WHERE  id IN (
            SELECT id FROM users
            WHERE  status IS NULL AND id > $1
            ORDER  BY id LIMIT $2
        )
        RETURNING id`

    var cursor int64
    var total, batch int

    for {
        select {
        case <-ctx.Done():
            return fmt.Errorf("backfill cancelled after %d rows: %w", total, ctx.Err())
        default:
        }

        rows, err := db.QueryContext(ctx, batchSQL, cursor, defaultBatchSize)
        if err != nil {
            return fmt.Errorf("backfill batch %d: %w", batch, err)
        }

        var ids []int64
        for rows.Next() {
            var id int64
            if err := rows.Scan(&id); err != nil {
                rows.Close()
                return fmt.Errorf("scan id batch %d: %w", batch, err)
            }
            ids = append(ids, id)
            if id > cursor {
                cursor = id
            }
        }
        rows.Close()
        if err := rows.Err(); err != nil {
            return fmt.Errorf("rows error batch %d: %w", batch, err)
        }

        count := len(ids)
        if count == 0 {
            break
        }
        total += count
        batch++
        logger.Info("backfill progress",
            slog.Int("batch", batch), slog.Int("total_rows", total), slog.Int64("cursor", cursor))
        time.Sleep(10 * time.Millisecond)
    }

    logger.Info("backfill complete", slog.Int("total_rows", total), slog.Int("batches", batch))
    return nil
}
```

**Design notes:** `ctx.Done()` respects graceful shutdown. `RETURNING id` advances cursor without a second query. Each batch commits independently — resumable on failure.

---

## NOT VALID Constraint Pattern

`NOT VALID` adds a constraint without scanning existing rows. Enforced immediately on new inserts/updates.

**Phase 1 — Add constraint (fast, metadata-level lock):**

```sql
ALTER TABLE orders
    ADD CONSTRAINT chk_orders_amount_positive CHECK (amount > 0) NOT VALID;

ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_orders
    FOREIGN KEY (order_id) REFERENCES orders (id) NOT VALID;
```

**Phase 2 — Validate (SHARE UPDATE EXCLUSIVE — allows concurrent writes):**

```sql
ALTER TABLE orders VALIDATE CONSTRAINT chk_orders_amount_positive;
ALTER TABLE order_items VALIDATE CONSTRAINT fk_order_items_orders;
```

**Check for unvalidated constraints:**

```sql
SELECT conname, conrelid::regclass AS table_name, contype
FROM   pg_constraint
WHERE  NOT convalidated
ORDER  BY conrelid::regclass::text, conname;
```

Once validated, PostgreSQL uses the constraint for query optimization.


> For lock_timeout strategy, Go migration runner with retry, checklist, and common mistakes: see [migration-impact-lock-timeout.md](migration-impact-lock-timeout.md).
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