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/paradedb-advanced-search-and-hybrid.md

View on GitHub
# ParadeDB — Advanced Search, Autocomplete, and Hybrid Search

## Advanced Search Features

### Fuzzy, Phrase, Boolean, Range Queries

```sql
-- Fuzzy: edit-distance matching
WHERE name @@@ paradedb.fuzzy_term('name', 'headphons', distance => 1)

-- Phrase: exact phrase
WHERE description @@@ paradedb.phrase('description', ARRAY['noise', 'cancelling'])

-- Boolean: must/should/must_not
WHERE products @@@ paradedb.boolean(
    must     => ARRAY[paradedb.term('category', 'electronics')],
    should   => ARRAY[paradedb.parse('name:wireless'), paradedb.parse('name:bluetooth')],
    must_not => ARRAY[paradedb.term('name', 'refurbished')])

-- Range: combine with boolean
WHERE products @@@ paradedb.boolean(must => ARRAY[
    paradedb.parse('name:headphones'),
    paradedb.range('rating', lower => 4.0, lower_inclusive => true),
    paradedb.range('price', upper => 150.0, upper_inclusive => true)])
```

### Snippet Highlighting

```sql
SELECT id, name,
    paradedb.snippet(id, field => 'description', max_num_chars => 200) AS excerpt
FROM products
WHERE products @@@ paradedb.parse('description:noise cancelling')
ORDER BY paradedb.score(id) DESC LIMIT 10;
-- excerpt: "...premium <b>noise</b> <b>cancelling</b> technology with..."
```

```go
func (r *searchRepository) SearchWithHighlight(ctx context.Context, query string, limit int) ([]productSnippetRow, error) {
    const sql = `SELECT id, name, paradedb.snippet(id, field => 'description', max_num_chars => 150) AS excerpt,
        paradedb.score(id) AS score FROM products WHERE products @@@ paradedb.parse($1) ORDER BY score DESC LIMIT $2`
    var rows []productSnippetRow
    if err := r.db.SelectContext(ctx, &rows, sql, query, limit); err != nil {
        return nil, fmt.Errorf("search with highlight: %w", err)
    }
    return rows, nil
}
```

---

## Autocomplete and Typeahead

```sql
-- Prefix matching
SELECT DISTINCT name FROM products
WHERE name @@@ paradedb.prefix('name', 'wire')
ORDER BY name LIMIT 10;

-- Fuzzy prefix (typo-tolerant typeahead)
SELECT DISTINCT name FROM products
WHERE name @@@ paradedb.fuzzy_phrase('name', 'wirelss head', distance => 1, prefix => true)
ORDER BY name LIMIT 10;
```

```go
func (r *searchRepository) Autocomplete(ctx context.Context, prefix string, limit int) ([]string, error) {
    if limit <= 0 || limit > 20 { limit = 10 }
    const sql = `SELECT DISTINCT name FROM products WHERE name @@@ paradedb.prefix('name', $1) ORDER BY name LIMIT $2`
    var suggestions []string
    if err := r.db.SelectContext(ctx, &suggestions, sql, prefix, limit); err != nil {
        return nil, fmt.Errorf("autocomplete: %w", err)
    }
    return suggestions, nil
}
```

---

## Hybrid Search (BM25 + pgvector)

Combines BM25 keyword relevance with vector semantic similarity using Reciprocal Rank Fusion (RRF).

**RRF formula:** `rrf_score = 1/(k + rank_bm25) + 1/(k + rank_vector)` where `k = 60`.

### Schema Requirements

```sql
ALTER TABLE products ADD COLUMN embedding VECTOR(1536);
CREATE INDEX products_embedding_hnsw_idx
    ON products USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);
```

### Hybrid SQL with RRF

```sql
WITH bm25_results AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY paradedb.score(id) DESC) AS bm25_rank
    FROM products WHERE products @@@ paradedb.parse($1) LIMIT 60
),
vector_results AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $2) AS vector_rank
    FROM products WHERE embedding IS NOT NULL
    ORDER BY embedding <=> $2 LIMIT 60
),
rrf AS (
    SELECT COALESCE(b.id, v.id) AS id,
        (1.0 / (60 + COALESCE(b.bm25_rank, 61))) +
        (1.0 / (60 + COALESCE(v.vector_rank, 61))) AS rrf_score
    FROM bm25_results b FULL OUTER JOIN vector_results v USING (id)
)
SELECT p.id, p.name, p.category, p.price, r.rrf_score
FROM rrf r JOIN products p USING (id)
ORDER BY r.rrf_score DESC LIMIT $3;
```

### Go Implementation

```go
func (r *searchRepository) HybridSearch(ctx context.Context, textQuery string, embedding []float32, limit int) ([]domain.ProductResult, error) {
    // SQL: use the WITH bm25_results / vector_results / rrf CTE shown above, params: $1=textQuery $2=vec $3=limit
    vec := pgvector.NewVector(embedding)
    var rows []hybridResultRow
    if err := r.db.SelectContext(ctx, &rows, hybridSearchSQL, textQuery, vec, limit); err != nil {
        return nil, fmt.Errorf("hybrid search: %w", err)
    }
    results := make([]domain.ProductResult, len(rows))
    for i, row := range rows {
        results[i] = domain.ProductResult{ID: row.ID, Name: row.Name, Category: row.Category, Price: row.Price, Score: row.RRFScore}
    }
    return results, nil
}
```

> For embedding generation and pgvector setup: see [pgvector-embeddings.md](pgvector-embeddings.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