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-setup-and-basic-search.md

View on GitHub
# ParadeDB — Setup, BM25 Index, and Basic Queries

ParadeDB brings Lucene-quality full-text search into PostgreSQL via the `pg_search` extension. BM25 ranking, fuzzy matching, phrase search, field boosting, and highlighting — all through standard SQL. No external search cluster required.

## ParadeDB vs tsvector

| Feature | `tsvector` + GIN | ParadeDB `pg_search` |
|---|---|---|
| Ranking algorithm | TF-IDF (basic) | **BM25** (Lucene-quality) |
| Fuzzy matching | No (pg_trgm workaround) | Native, configurable edit distance |
| Field boosting | No | Yes |
| Snippet highlighting | No | Yes |
| Hybrid BM25 + vector | No | Yes (with pgvector) |
| Setup complexity | Zero (built-in) | Requires ParadeDB image/extension |

**Rule:** Start with `tsvector` + GIN for simple keyword search. Switch to ParadeDB when you need BM25 relevance, fuzzy tolerance, highlighting, or hybrid semantic search.

## Docker Setup

```yaml
# docker-compose.yml (development)
services:
  db:
    image: paradedb/paradedb:latest
    restart: unless-stopped
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: secret
      POSTGRES_DB: appdb
      PARADEDB_TELEMETRY: false
    ports: ["5432:5432"]
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./migrations:/docker-entrypoint-initdb.d
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d appdb"]
      interval: 10s
      timeout: 5s
      retries: 5
volumes:
  pgdata:
```

## pg_search Setup

```sql
-- Run once per database
CREATE EXTENSION IF NOT EXISTS pg_search;
```

### Products Table Schema

```sql
CREATE TABLE products (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT        NOT NULL,
    description TEXT,
    category    TEXT        NOT NULL,
    price       NUMERIC(10, 2) NOT NULL,
    in_stock    BOOLEAN     NOT NULL DEFAULT true,
    rating      REAL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

### Creating a BM25 Index

```sql
CALL paradedb.create_bm25_index(
    index_name     => 'products_search_idx',
    table_name     => 'products',
    key_field      => 'id',
    text_fields    => paradedb.field('name',        tokenizer => paradedb.tokenizer('en_stem'), boost => 3.0)
                   || paradedb.field('description', tokenizer => paradedb.tokenizer('en_stem'), boost => 1.0)
                   || paradedb.field('category',    tokenizer => paradedb.tokenizer('keyword')),
    numeric_fields => paradedb.field('price') || paradedb.field('rating'),
    boolean_fields => paradedb.field('in_stock')
);

-- Drop before recreating after schema changes
CALL paradedb.drop_bm25_index('products_search_idx');
```

| Parameter | Values | Notes |
|---|---|---|
| `tokenizer` | `en_stem`, `lowercase`, `keyword`, `whitespace`, `raw` | `en_stem` for natural language; `keyword` for exact category values |
| `boost` | `REAL` (default `1.0`) | Higher = more weight in BM25 score |
| `stored` | `true` / `false` | `true` enables snippet highlighting |

## Basic BM25 Queries

```sql
-- Simple text search
SELECT id, name, category, price
FROM products
WHERE name @@@ 'wireless headphones'
ORDER BY paradedb.score(id) DESC LIMIT 20;

-- Multi-field search with score
SELECT id, name, category, price, paradedb.score(id) AS relevance_score
FROM products
WHERE products @@@ paradedb.parse('name:headphones OR description:headphones')
ORDER BY relevance_score DESC LIMIT 20;

-- Combine BM25 with filters
SELECT id, name, price, paradedb.score(id) AS score
FROM products
WHERE products @@@ paradedb.boolean(
    must   => ARRAY[paradedb.parse('name:headphones OR description:headphones')],
    filter => ARRAY[
        paradedb.term('in_stock', true),
        paradedb.range('price', upper => 200.0, upper_inclusive => true)
    ]
)
ORDER BY score DESC LIMIT 20;
```

### Go Repository

```go
func (r *searchRepository) BasicSearch(ctx context.Context, query string, limit, offset int) ([]domain.ProductResult, error) {
    const sql = `
        SELECT id, name, category, price, paradedb.score(id) AS score
        FROM products
        WHERE name @@@ $1 OR description @@@ $1
        ORDER BY score DESC, id DESC
        LIMIT $2 OFFSET $3`
    var rows []productSearchRow
    if err := r.db.SelectContext(ctx, &rows, sql, query, limit, offset); err != nil {
        return nil, fmt.Errorf("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.Score}
    }
    return results, nil
}
```
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