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/postgis-go-integration-and-performance.md

View on GitHub
# PostGIS — Go Integration and Performance Tips

## Go Integration with sqlx

PostGIS returns spatial data as WKB or WKT. The simplest approach is to read coordinates via `ST_X`/`ST_Y` rather than parsing raw WKB in Go.

### Domain Type

```go
// internal/domain/location.go
package domain

type Point struct {
    Lat float64
    Lng float64
}

type Store struct {
    ID       int64   `db:"id"`
    Name     string  `db:"name"`
    Lat      float64 `db:"lat"`
    Lng      float64 `db:"lng"`
    Distance float64 `db:"distance_metres"`
}
```

### Inserting Spatial Data

```go
// internal/repository/store_repository.go
package repository

import (
    "context"
    "fmt"
    "log/slog"

    "github.com/jmoiron/sqlx"
    "yourmodule/internal/domain"
)

type StoreRepository struct {
    db     *sqlx.DB
    logger *slog.Logger
}

func NewStoreRepository(db *sqlx.DB, logger *slog.Logger) *StoreRepository {
    return &StoreRepository{db: db, logger: logger}
}

func (r *StoreRepository) Create(ctx context.Context, name string, p domain.Point) (int64, error) {
    const q = `
        INSERT INTO stores (name, location)
        VALUES ($1, ST_MakePoint($2, $3)::geography)
        RETURNING id`

    var id int64
    // Note: ST_MakePoint(lng, lat) — longitude first
    if err := r.db.QueryRowContext(ctx, q, name, p.Lng, p.Lat).Scan(&id); err != nil {
        return 0, fmt.Errorf("StoreRepository.Create: %w", err)
    }
    r.logger.InfoContext(ctx, "store created", "id", id, "name", name)
    return id, nil
}
```

### Scanning Results — extract coordinates in SELECT

```go
func (r *StoreRepository) FindNearby(
    ctx context.Context, center domain.Point, radiusMeters float64, limit int,
) ([]domain.Store, error) {
    const q = `
        SELECT
            id, name,
            ST_Y(location::geometry)                               AS lat,
            ST_X(location::geometry)                               AS lng,
            ST_Distance(location, ST_MakePoint($2, $1)::geography) AS distance_metres
        FROM stores
        WHERE ST_DWithin(location, ST_MakePoint($2, $1)::geography, $3)
        ORDER BY distance_metres
        LIMIT $4`

    // $1=lat, $2=lng — ST_MakePoint(lng, lat)
    var stores []domain.Store
    if err := r.db.SelectContext(ctx, &stores, q,
        center.Lat, center.Lng, radiusMeters, limit,
    ); err != nil {
        return nil, fmt.Errorf("StoreRepository.FindNearby: %w", err)
    }
    return stores, nil
}
```

---

---

## Performance Tips

| Tip | Why |
|---|---|
| Use `ST_DWithin` — never `ST_Distance < X` | `ST_DWithin` is index-aware; `ST_Distance` computes for every row |
| Create GiST index before loading data | Bulk insert then index is faster than incremental updates |
| Use `CREATE INDEX CONCURRENTLY` on live tables | Avoids `AccessExclusiveLock` |
| Cast to `geometry` only for bounding-box ops | `geography` spherical math is slower |
| Cluster table by spatial index | `CLUSTER stores USING idx_stores_location` — co-locates nearby rows |
| Simplify complex polygons before indexing | `ST_Simplify(boundary, 0.0001)` reduces vertex count |
| Partition large tables by grid cell | Combine with bounding-box filters for >50M points |
| Set `work_mem` for sort-heavy spatial joins | `SET work_mem = '64MB'` per session |

**Diagnostic query — check index usage:**

```sql
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE indexname LIKE '%location%'
ORDER BY idx_scan DESC;
```

`idx_scan = 0` after normal traffic means the planner is ignoring the index — run `ANALYZE stores;` then re-check.

*Cross-skill references:*
- *Docker/Kubernetes deployment: **golang-gin-deploy** skill*
- *sqlx connection setup: **golang-gin-database** skill → [sqlx-patterns.md](../../golang-gin-database/references/sqlx-patterns.md)*
- *Testing spatial queries with testcontainers-go: **golang-gin-testing** skill*
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