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/pgvector-queries-and-go-integration.md

View on GitHub
# pgvector — Similarity Queries and Go Integration

## Similarity Queries

### K-nearest neighbors (KNN)

```sql
SELECT id, title, 1 - (embedding <=> $1) AS similarity
FROM documents
ORDER BY embedding <=> $1
LIMIT $2;
```

### Filtered similarity — tenant-scoped search

```sql
SELECT id, title, content, 1 - (embedding <=> $1) AS similarity
FROM documents
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 10;
```

**Important:** If the WHERE clause is highly selective, the planner may choose a sequential scan over the vector index. This is often correct. Use `EXPLAIN ANALYZE` to verify.

### Partial index for filtered search

```sql
-- Only indexes active documents
CREATE INDEX idx_docs_active_embedding ON documents
    USING hnsw (embedding vector_cosine_ops)
    WHERE deleted_at IS NULL;
```

### Similarity threshold

```sql
SELECT id, title, 1 - (embedding <=> $1) AS similarity
FROM documents
WHERE tenant_id = $2
  AND 1 - (embedding <=> $1) > 0.75
ORDER BY embedding <=> $1
LIMIT 20;
```

### Pagination with vector search

Standard OFFSET pagination works but is not efficient for deep pages. For most RAG use cases, only the top-K results are needed.

```sql
-- Keyset pagination: track last seen distance
SELECT id, title, embedding <=> $1 AS distance
FROM documents
WHERE tenant_id = $2
  AND embedding <=> $1 > $3  -- $3 = last seen distance from previous page
ORDER BY embedding <=> $1
LIMIT 10;
```

---

## Go Integration with pgvector-go

### Install

```bash
go get github.com/pgvector/pgvector-go
```

`pgvector.Vector` implements `driver.Valuer` and `sql.Scanner` — serializes/deserializes the PostgreSQL `vector` wire format automatically with `sqlx`.

### Row struct with embedding

```go
// internal/repository/document_row.go
package repository

import (
    "time"
    "github.com/pgvector/pgvector-go"
)

type documentRow struct {
    ID         string          `db:"id"`
    TenantID   string          `db:"tenant_id"`
    Title      string          `db:"title"`
    Content    string          `db:"content"`
    Metadata   []byte          `db:"metadata"`
    Embedding  pgvector.Vector `db:"embedding"`
    Model      string          `db:"model"`
    TokenCount *int            `db:"token_count"`
    CreatedAt  time.Time       `db:"created_at"`
    UpdatedAt  time.Time       `db:"updated_at"`
}
```

### Repository: store, search, delete

```go
type documentRepository struct{ db *sqlx.DB }

func (r *documentRepository) Store(ctx context.Context, doc *domain.Document) error {
    const q = `INSERT INTO documents (id, tenant_id, title, content, metadata, embedding, model, token_count)
        VALUES (:id, :tenant_id, :title, :content, :metadata, :embedding, :model, :token_count)`
    row := documentInsertRow{
        ID: doc.ID, TenantID: doc.TenantID, Title: doc.Title,
        Content: doc.Content, Metadata: doc.MetadataJSON,
        Embedding: pgvector.NewVector(doc.Embedding), Model: doc.Model, TokenCount: doc.TokenCount,
    }
    if _, err := r.db.NamedExecContext(ctx, q, row); err != nil {
        return fmt.Errorf("document.Store: %w", err)
    }
    return nil
}

func (r *documentRepository) Search(ctx context.Context, tenantID string, queryEmbedding []float32, topK int) ([]domain.SearchResult, error) {
    const q = `SELECT id, title, content, 1 - (embedding <=> $1) AS similarity
        FROM documents WHERE tenant_id = $2 ORDER BY embedding <=> $1 LIMIT $3`
    var rows []struct {
        ID string `db:"id"`; Title string `db:"title"`; Content string `db:"content"`; Similarity float64 `db:"similarity"`
    }
    if err := r.db.SelectContext(ctx, &rows, q, pgvector.NewVector(queryEmbedding), tenantID, topK); err != nil {
        return nil, fmt.Errorf("document.Search: %w", err)
    }
    results := make([]domain.SearchResult, len(rows))
    for i, row := range rows {
        results[i] = domain.SearchResult{ID: row.ID, Title: row.Title, Content: row.Content, Similarity: row.Similarity}
    }
    return results, nil
}

func (r *documentRepository) Delete(ctx context.Context, id, tenantID string) error {
    result, err := r.db.ExecContext(ctx, `DELETE FROM documents WHERE id = $1 AND tenant_id = $2`, id, tenantID)
    if err != nil { return fmt.Errorf("document.Delete: %w", err) }
    if n, _ := result.RowsAffected(); n == 0 { return domain.ErrNotFound }
    return nil
}
```


> For embedding generation with OpenAI and batch processing patterns, see [pgvector-capacity-and-performance.md](pgvector-capacity-and-performance.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