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/query-performance-stats-and-vacuum.md

View on GitHub
# Query Performance — pg_stat_statements, Autovacuum, and Bloat

## pg_stat_statements Setup

```ini
# postgresql.conf (requires server restart)
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
```

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

### Key Queries

```sql
-- Top slow queries by mean time
SELECT left(query, 120) AS query_preview, calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(total_exec_time::numeric, 2) AS total_ms, rows
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC LIMIT 20;

-- Top I/O queries (disk reads)
SELECT left(query, 120) AS query_preview, calls, shared_blks_read,
    round(shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0) * 100, 2) AS cache_hit_pct
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY shared_blks_read DESC LIMIT 20;
```

### Go Helper: Log Slow Queries

```go
func LogSlowQueries(ctx context.Context, db *sqlx.DB, thresholdMs float64, logger *slog.Logger) error {
    const q = `SELECT left(query, 120) AS query_preview, calls,
        round(mean_exec_time::numeric, 2) AS mean_ms, round(total_exec_time::numeric, 2) AS total_ms
        FROM pg_stat_statements WHERE calls > 5 AND mean_exec_time > $1 ORDER BY mean_exec_time DESC LIMIT 10`
    var rows []struct{ QueryPreview string `db:"query_preview"`; Calls int64 `db:"calls"`; MeanMs float64 `db:"mean_ms"` }
    if err := db.SelectContext(ctx, &rows, q, thresholdMs); err != nil {
        return fmt.Errorf("pg_stat_statements query: %w", err)
    }
    for _, r := range rows {
        logger.WarnContext(ctx, "slow query detected", "mean_ms", r.MeanMs, "calls", r.Calls, "query", r.QueryPreview)
    }
    return nil
}
```

---

## Autovacuum Tuning

PostgreSQL uses MVCC — every UPDATE/DELETE leaves dead tuples. Autovacuum reclaims them and updates planner statistics.

A table with 10M rows needs 2,000,050 dead tuples before default autovacuum fires. Too infrequent for hot tables.

### Per-Table Tuning

```sql
-- orders: 1% dead tuple threshold instead of default 20%
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor  = 0.01,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay    = 2
);

-- job_queue: vacuum after every 100 dead rows
ALTER TABLE job_queue SET (
    autovacuum_vacuum_scale_factor  = 0.0,
    autovacuum_vacuum_threshold     = 100,
    autovacuum_analyze_scale_factor = 0.0,
    autovacuum_analyze_threshold    = 100
);
```

### Monitoring

```sql
SELECT relname AS table_name, n_dead_tup, n_live_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
    last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;

-- Wraparound risk (alert when xid_age > 150,000,000)
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class WHERE relkind = 'r'
ORDER BY xid_age DESC LIMIT 10;
```

---

## Table Bloat Detection

### Detection

```sql
SELECT relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    n_dead_tup, n_live_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_ratio_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_dead_tup DESC LIMIT 20;
```

Accurate measurement with pgstattuple (scans the full table):

```sql
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT table_len, dead_tuple_count, dead_tuple_percent, free_percent
FROM pgstattuple('orders');
```

### Fix Options

| Method | Downside | When to Use |
|---|---|---|
| `VACUUM tablename` | Doesn't shrink file on disk | Regular maintenance |
| `VACUUM FULL tablename` | Full `AccessExclusiveLock` — blocks reads/writes | Small tables, maintenance windows |
| `pg_repack` (extension) | Online rewrite — no long lock | Production tables > 1 GB |

## Planner Statistics

```sql
ANALYZE orders;  -- update statistics for one table

-- Per-column override for skewed distributions
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ALTER TABLE events ALTER COLUMN user_id SET STATISTICS 1000;
ANALYZE orders; ANALYZE events;

-- Extended stats for correlated columns
CREATE STATISTICS orders_status_region ON status, region FROM orders;
ANALYZE orders;
```
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