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/extensions-toolkit-crypto-and-uuid.md

View on GitHub
# Extensions Toolkit — pgcrypto and uuid-ossp

## pgcrypto — Encryption and Hashing

### Setup

```sql
CREATE EXTENSION IF NOT EXISTS pgcrypto;
```

### gen_random_uuid()

Built-in since PostgreSQL 13 (no pgcrypto needed for PG 13+):

```sql
SELECT gen_random_uuid();  -- 'a3f4c7d2-...'
```

### PGP Symmetric Encryption — Column Encryption

```sql
-- Encrypt on insert
INSERT INTO patients (id, name, ssn_encrypted)
VALUES (
    gen_random_uuid(), 'John Doe',
    pgp_sym_encrypt('123-45-6789', current_setting('app.encryption_key'))
);

-- Decrypt on read
SELECT name, pgp_sym_decrypt(ssn_encrypted, current_setting('app.encryption_key')) AS ssn
FROM patients WHERE id = $1;

-- Set per-connection before queries (never hardcode in SQL)
SET app.encryption_key = 'your-secret-key-from-env';
```

### When to Encrypt in Go vs PostgreSQL

| Factor | Encrypt in Go | Encrypt in PostgreSQL |
|---|---|---|
| Key management | Easier — key never enters DB | Key passed via SQL (risk of query log exposure) |
| Performance | App scales horizontally | DB CPU used |
| Use when | PII, secrets, bulk data | Legacy schemas, multi-app access to same DB |

**Recommendation:** Encrypt sensitive fields in Go before storing. Use `pgp_sym_encrypt` only when multiple services share the same database.

### AES-GCM Encryption in Go

```go
// EncryptField encrypts a plaintext string using AES-GCM. key must be 16, 24, or 32 bytes.
func EncryptField(plaintext string, key []byte) (string, error) {
    block, err := aes.NewCipher(key)
    if err != nil {
        return "", fmt.Errorf("new cipher: %w", err)
    }
    gcm, err := cipher.NewGCM(block)
    if err != nil {
        return "", fmt.Errorf("new gcm: %w", err)
    }
    nonce := make([]byte, gcm.NonceSize())
    if _, err := io.ReadFull(rand.Reader, nonce); err != nil {
        return "", fmt.Errorf("generate nonce: %w", err)
    }
    ciphertext := gcm.Seal(nonce, nonce, []byte(plaintext), nil)
    return base64.StdEncoding.EncodeToString(ciphertext), nil
}

// DecryptField decrypts a base64-encoded AES-GCM ciphertext.
func DecryptField(encoded string, key []byte) (string, error) {
    data, err := base64.StdEncoding.DecodeString(encoded)
    if err != nil {
        return "", fmt.Errorf("decode base64: %w", err)
    }
    block, err := aes.NewCipher(key)
    if err != nil {
        return "", fmt.Errorf("new cipher: %w", err)
    }
    gcm, err := cipher.NewGCM(block)
    if err != nil {
        return "", fmt.Errorf("new gcm: %w", err)
    }
    nonceSize := gcm.NonceSize()
    if len(data) < nonceSize {
        return "", fmt.Errorf("ciphertext too short")
    }
    plaintext, err := gcm.Open(nil, data[:nonceSize], data[nonceSize:], nil)
    if err != nil {
        return "", fmt.Errorf("decrypt: %w", err)
    }
    return string(plaintext), nil
}
```

---

## uuid-ossp — UUID Generation

### Setup

```sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
```

### uuid_generate_v4() vs gen_random_uuid()

| Function | Source | Available |
|---|---|---|
| `uuid_generate_v4()` | uuid-ossp extension | All PostgreSQL versions |
| `gen_random_uuid()` | Built-in | PostgreSQL 13+ |

**Recommendation:** Use `gen_random_uuid()` for PostgreSQL 13+. No extension required.

### Other uuid-ossp Functions

```sql
SELECT uuid_generate_v1();           -- UUID v1: time-based (includes MAC address)
SELECT uuid_generate_v3(uuid_ns_dns(), 'example.com');  -- v3: MD5 hash (deterministic)
SELECT uuid_generate_v5(uuid_ns_url(), 'https://example.com/users/42');  -- v5: SHA-1 hash
```

UUID v5 is useful for deterministic IDs from known inputs (idempotency keys, external ID mapping).

### UUID Primary Key Performance

| Key Type | Insert Pattern | Index Fragmentation | Notes |
|---|---|---|---|
| `BIGINT IDENTITY` | Sequential | Minimal | Best write performance |
| `UUIDv4` | Random | High | Poor for sequential inserts |
| `UUIDv7` | Time-ordered random | Minimal | Best of both worlds |

**For high-insert tables, prefer `BIGINT GENERATED ALWAYS AS IDENTITY` or UUIDv7:**

```go
// Use github.com/google/uuid v1.6+ for UUIDv7
import "github.com/google/uuid"

id, err := uuid.NewV7()
// time-ordered, globally unique, stores as standard UUID column
```
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