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

View on GitHub
# Extensions Toolkit — Setup, pg_cron

## Extension Management in Migrations

Always use `IF NOT EXISTS` — migrations must be idempotent:

```sql
-- db/migrations/000001_setup_extensions.up.sql
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
```

```sql
-- db/migrations/000001_setup_extensions.down.sql
DROP EXTENSION IF EXISTS "pg_trgm";
DROP EXTENSION IF EXISTS "uuid-ossp";
DROP EXTENSION IF EXISTS "pgcrypto";
```

Rules:
- Install extensions in the earliest migration possible — other migrations may depend on them.
- Extensions owned by `superuser` — in managed DBs (RDS, Cloud SQL), use the platform's pre-installed list.

### Docker: Installing Extensions

```dockerfile
FROM postgres:16

RUN apt-get update && apt-get install -y \
    postgresql-16-cron \
    postgresql-16-partman \
    postgresql-16-audit \
    && rm -rf /var/lib/apt/lists/*

# pg_cron requires shared_preload_libraries
CMD ["postgres", "-c", "shared_preload_libraries=pg_cron,pg_stat_statements"]
```

### golang-migrate Example

```sql
-- db/migrations/000001_extensions.up.sql
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";
CREATE EXTENSION IF NOT EXISTS "pg_partman" SCHEMA partman;
-- CREATE EXTENSION IF NOT EXISTS "pg_cron";  -- only after shared_preload_libraries is set
```

---

## pg_cron — Scheduled Jobs

### Setup

```ini
# postgresql.conf
shared_preload_libraries = 'pg_cron'
cron.database_name = 'myapp'   # database where cron.job table lives
```

After restart:

```sql
CREATE EXTENSION IF NOT EXISTS pg_cron;
GRANT USAGE ON SCHEMA cron TO myapp_user;
```

### Schedule Syntax

| Expression | Meaning |
|---|---|
| `0 3 * * *` | Every day at 03:00 |
| `*/15 * * * *` | Every 15 minutes |
| `0 0 * * 0` | Every Sunday at midnight |
| `@daily` | Alias for `0 0 * * *` |

### Examples

```sql
-- Nightly cleanup of soft-deleted records
SELECT cron.schedule('nightly-cleanup', '0 3 * * *', $$
    DELETE FROM users WHERE deleted_at < NOW() - INTERVAL '90 days';
$$);

-- Hourly aggregation into a stats table
SELECT cron.schedule('hourly-stats', '0 * * * *', $$
    INSERT INTO hourly_event_counts (hour, event_type, count)
    SELECT date_trunc('hour', created_at), event_type, COUNT(*)
    FROM events
    WHERE created_at >= NOW() - INTERVAL '2 hours'
      AND created_at < date_trunc('hour', NOW())
    GROUP BY 1, 2
    ON CONFLICT (hour, event_type) DO UPDATE SET count = EXCLUDED.count;
$$);

-- Partition maintenance
SELECT cron.schedule('partman-maintenance', '*/30 * * * *', 'CALL partman.run_maintenance_proc()');

-- Manage jobs
SELECT jobid, jobname, schedule, command, active FROM cron.job;
UPDATE cron.job SET active = false WHERE jobname = 'hourly-stats';
SELECT cron.unschedule('nightly-cleanup');
```

### Monitoring Job Runs

```sql
-- Failed jobs in the last 24 hours
SELECT jobname, start_time, return_message
FROM cron.job_run_details r
JOIN cron.job j ON j.jobid = r.jobid
WHERE r.status = 'failed'
  AND r.start_time > NOW() - INTERVAL '24 hours';
```

### Go Helper — Manage Cron Jobs via SQL

```go
// UpsertCronJob ensures a named cron job exists with the given schedule and command.
func UpsertCronJob(ctx context.Context, db *sqlx.DB, name, schedule, command string, logger *slog.Logger) error {
    _, err := db.ExecContext(ctx, `SELECT cron.unschedule(jobname) FROM cron.job WHERE jobname = $1`, name)
    if err != nil {
        return fmt.Errorf("unschedule %q: %w", name, err)
    }
    _, err = db.ExecContext(ctx, `SELECT cron.schedule($1, $2, $3)`, name, schedule, command)
    if err != nil {
        return fmt.Errorf("schedule %q: %w", name, err)
    }
    logger.Info("cron job registered", "name", name, "schedule", schedule)
    return 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