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/timescaledb-setup-and-hypertables.md

View on GitHub
# TimescaleDB — Setup and Hypertables

TimescaleDB turns ordinary PostgreSQL tables into hypertables — automatically partitioned by time — with first-class time-series functions. Transparent to standard SQL: every driver and ORM that works with PostgreSQL works unchanged.

> **Architectural note:** TimescaleDB patterns are PostgreSQL DBA decisions, not part of the Gin framework API. All Go examples use sqlx with raw SQL.

## Overview

| Feature | TimescaleDB | Plain PostgreSQL partitioning |
|---|---|---|
| Partition creation | Automatic, by time interval | Manual `CREATE TABLE ... PARTITION OF` |
| Time aggregation | `time_bucket()` built-in | Custom expressions |
| Materialized aggregates | Continuous aggregates with auto-refresh | Manual `REFRESH MATERIALIZED VIEW` |
| Compression | Columnar per-chunk, 10–20x ratio | Not available |
| Retention | `add_retention_policy()` — drops chunks | Manual `DROP TABLE partition_name` |

**When to choose TimescaleDB:** table is append-dominant ordered by time, queries always include a time range filter, need time-bucketed aggregations or downsampling.

## Setup

Use Docker image `timescale/timescaledb:latest-pg16` (or `timescale/timescaledb-ha:pg16-latest` for production with PostGIS).

```sql
-- 000001_enable_timescaledb.sql
CREATE EXTENSION IF NOT EXISTS timescaledb;
```

## Hypertables

A hypertable is a regular PostgreSQL table with automatic time-based partitioning into *chunks*. Each chunk covers a time interval (default: 7 days).

```sql
CREATE TABLE metrics (
    time        TIMESTAMPTZ        NOT NULL,
    device_id   TEXT               NOT NULL,
    metric      TEXT               NOT NULL,
    value       DOUBLE PRECISION   NOT NULL,
    tags        JSONB
);

-- Convert to hypertable — partitioned by 'time', 7-day chunks (default)
SELECT create_hypertable('metrics', by_range('time'));

-- Custom chunk interval: 1 day (use for high-volume tables)
SELECT create_hypertable('metrics', by_range('time', INTERVAL '1 day'));
```

Indexes created on the parent propagate to all chunks:

```sql
CREATE INDEX idx_metrics_device_time ON metrics (device_id, time DESC);
CREATE INDEX idx_metrics_metric_time ON metrics (metric, time DESC);
```

Space partitioning for very high cardinality:

```sql
-- Spread across 4 hash buckets by device_id
SELECT add_dimension('metrics', by_hash('device_id', 4));
```

### API Metrics Table DDL

```sql
-- migrations/000002_create_api_metrics.sql
CREATE TABLE api_metrics (
    time        TIMESTAMPTZ      NOT NULL DEFAULT now(),
    endpoint    TEXT             NOT NULL,
    method      TEXT             NOT NULL,
    status      INTEGER          NOT NULL,
    duration_ms DOUBLE PRECISION NOT NULL,
    user_id     TEXT,
    ip          TEXT
);

SELECT create_hypertable('api_metrics', by_range('time', INTERVAL '1 day'));

CREATE INDEX idx_api_metrics_endpoint ON api_metrics (endpoint, time DESC);
CREATE INDEX idx_api_metrics_status   ON api_metrics (status, time DESC);
```

Converting an existing table with data:

```sql
SELECT create_hypertable('events', by_range('created_at'), migrate_data => true);
```

## time_bucket — Time-Based Aggregation

`time_bucket` truncates a timestamp to a fixed interval — the core function for time-series aggregations.

```sql
-- Per-minute request count
SELECT time_bucket('1 minute', time) AS bucket, COUNT(*) AS requests
FROM api_metrics
WHERE time >= now() - INTERVAL '1 hour'
GROUP BY bucket ORDER BY bucket DESC;

-- Per-hour average duration by endpoint
SELECT time_bucket('1 hour', time) AS bucket, endpoint,
       AVG(duration_ms) AS avg_ms, MAX(duration_ms) AS max_ms, COUNT(*) AS total
FROM api_metrics
WHERE time >= now() - INTERVAL '24 hours'
GROUP BY bucket, endpoint ORDER BY bucket DESC, total DESC;

-- Per-day error rate
SELECT time_bucket('1 day', time) AS bucket,
       COUNT(*) FILTER (WHERE status >= 500) AS errors,
       ROUND(100.0 * COUNT(*) FILTER (WHERE status >= 500) / NULLIF(COUNT(*), 0), 2) AS error_pct
FROM api_metrics
WHERE time >= now() - INTERVAL '30 days'
GROUP BY bucket ORDER BY bucket DESC;
```

### Go Query for Hourly Stats

```go
type HourlyStat struct {
    Bucket   time.Time `db:"bucket"`
    Requests int64     `db:"requests"`
    AvgMs    float64   `db:"avg_ms"`
    Errors   int64     `db:"errors"`
}

func (r *MetricsRepository) GetHourlyStats(ctx context.Context, start, end time.Time) ([]HourlyStat, error) {
    const q = `
        SELECT time_bucket('1 hour', time) AS bucket,
               COUNT(*) AS requests, AVG(duration_ms) AS avg_ms,
               COUNT(*) FILTER (WHERE status >= 500) AS errors
        FROM api_metrics
        WHERE time >= $1 AND time < $2
        GROUP BY bucket ORDER BY bucket DESC`
    var rows []HourlyStat
    if err := r.db.SelectContext(ctx, &rows, q, start, end); err != nil {
        return nil, fmt.Errorf("GetHourlyStats: %w", err)
    }
    return rows, 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