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

View on GitHub
# pgvector — Setup, Schema Design, and Index Types

pgvector is an open-source PostgreSQL extension that adds a native `vector` column type and similarity-search operators, turning a standard PostgreSQL database into a vector store. Powers semantic search, RAG, recommendation engines, and image similarity.

## pgvector Overview

**Extension:** `pgvector` — https://github.com/pgvector/pgvector

**What it provides:**
- `vector(N)` column type storing dense float32 vectors of N dimensions
- Distance operators: `<=>` (cosine), `<->` (L2), `<#>` (inner product)
- Approximate nearest-neighbor indexes: HNSW and IVFFlat
- Exact KNN with sequential scan (no index, small datasets)

**Common use cases:**

| Use Case | Model Example | Dimensions |
|---|---|---|
| Semantic text search / RAG | OpenAI `text-embedding-ada-002` | 1536 |
| Sentence similarity | `sentence-transformers/all-MiniLM-L6-v2` | 384 |
| General purpose | `text-embedding-3-small` | 1536 |
| Image embeddings | CLIP ViT-B/32 | 512 |
| Multilingual | `paraphrase-multilingual-mpnet-base-v2` | 768 |

**Key principle:** Every vector in a column must have the same number of dimensions.

---

## Setup

### Docker — quickest start

Use image `pgvector/pgvector:pg16` — pgvector is pre-installed.

### Install on existing PostgreSQL image

```dockerfile
FROM postgres:16-bookworm
RUN apt-get update && apt-get install -y postgresql-16-pgvector && rm -rf /var/lib/apt/lists/*
```

### Enable the extension

```sql
-- 0001_enable_pgvector.up.sql
CREATE EXTENSION IF NOT EXISTS vector;
```

---

## Table Design

### When to normalize vectors

| Distance metric | Normalization needed? |
|---|---|
| Cosine (`<=>`) | No — operator normalizes implicitly |
| Inner product (`<#>`) | **Yes** — vectors must be pre-normalized |
| L2 (`<->`) | No |

### Complete DDL — documents table

```sql
CREATE TABLE documents (
    id           UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
    tenant_id    UUID        NOT NULL,
    source_url   TEXT,
    title        TEXT        NOT NULL,
    content      TEXT        NOT NULL,
    metadata     JSONB       NOT NULL DEFAULT '{}',
    embedding    vector(1536) NOT NULL,
    model        TEXT        NOT NULL DEFAULT 'text-embedding-3-small',
    token_count  INT,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_documents_tenant   ON documents (tenant_id);
CREATE INDEX idx_documents_metadata ON documents USING gin (metadata jsonb_path_ops);

-- Vector index (HNSW for production)
CREATE INDEX idx_documents_embedding ON documents
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);
```

**Design rules:**
- Store the model name alongside the embedding — re-embed when switching models
- Store `token_count` for cost tracking or chunking logic
- Keep raw `content` in the same table; avoids joins on retrieval
- Use `JSONB metadata` with gin index for fast containment queries

---

## Index Types Comparison

### IVFFlat

```sql
CREATE INDEX idx_documents_embedding_ivf ON documents
    USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);
-- Rule of thumb: lists = sqrt(row_count). For 1M rows: lists = 1000
```

**IVFFlat requires data before indexing.** Build after loading at least `lists * 16` rows.

### HNSW

```sql
CREATE INDEX idx_documents_embedding_hnsw ON documents
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);
-- m: connections per node (higher = better recall, more memory)
-- ef_construction: search depth during build (higher = better quality, slower)
```

### Comparison Table

| Attribute | IVFFlat | HNSW |
|---|---|---|
| Build time | Fast | Slow (2-5x IVFFlat) |
| Query time | Moderate | Fast |
| Recall quality | Good (80-95%) | Excellent (95-99%) |
| Memory usage | Low | High (~2-3x vector data) |
| Requires pre-loaded data | Yes | No |
| Tuning parameter | `probes` (query) | `ef_search` (query) |
| Best for | Batch-built, large datasets | Production, incremental inserts |

**HNSW**: default for production (incremental inserts). **IVFFlat**: fixed large dataset, tight memory. **No index**: exact KNN under ~100K rows.

---

## Distance Functions

| Operator | Distance Type | SQL Example |
|---|---|---|
| `<=>` | Cosine distance | `embedding <=> $1` |
| `<->` | L2 (Euclidean) distance | `embedding <-> $1` |
| `<#>` | Negative inner product | `embedding <#> $1` |

Operators return **distance** (lower = more similar). For similarity score: `1 - (embedding <=> $1)`.

| Embedding type | Recommended operator | Reason |
|---|---|---|
| Text (OpenAI, sentence-transformers) | `<=>` cosine | Magnitude varies; direction encodes meaning |
| Pre-normalized vectors | `<#>` inner product | Equivalent to cosine but ~15% faster |
| Image / spatial | `<->` L2 | Magnitude carries information |

The index operator class **must match** the query operator.
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