golang-gin-psql-dba — PostgreSQL DBA / Architect

For complete schema design patterns (normalization, multi-tenancy, audit trails): see references/schema-design.md.

Index Selection Decision Tree

Rules of thumb:

• Default to B-tree. Only switch when B-tree cannot serve the query pattern.
• Partial indexes (WHERE active = true) reduce size and maintenance for filtered queries.
• Covering indexes (INCLUDE (col)) let the planner do index-only scans.
• Never index columns with very low cardinality (e.g., boolean) alone — combine in composite indexes.

For deep dive on each index type with EXPLAIN ANALYZE: see references/index-strategy.md.

Migration Safety Quick Guide

Every ALTER TABLE acquires a lock. The lock level determines whether reads and writes are blocked.

Zero-downtime pattern for NOT NULL:

-- Step 1: Add constraint as NOT VALID (fast, no scan)
ALTER TABLE users ADD CONSTRAINT users_email_not_null
    CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: Validate in a separate transaction (scans, but allows writes)
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

Always set lock_timeout in migration scripts:

SET lock_timeout = '5s';
-- If the lock can't be acquired in 5s, the migration fails instead of blocking all traffic.

For complete zero-downtime patterns (column rename, type change, backfill): see references/migration-impact-analysis.md.

Extension Selection Guide

Decision shortcuts:

• Need search? Start with PostgreSQL built-in tsvector + GIN. If you need BM25 ranking, fuzzy, or hybrid search → ParadeDB.
• Need AI/ML embeddings? → pgvector with HNSW index.
• Need lat/lng distance queries? → PostGIS. Use geography type for global lat/lng, geometry for local projected data.
• Need IoT / metrics / time-bucketed aggregation? → TimescaleDB.

EXPLAIN ANALYZE Cheat Sheet

// helper: run EXPLAIN ANALYZE from Go and log the plan
func ExplainQuery(ctx context.Context, db *sqlx.DB, query string, args ...any) (string, error) {
    row := db.QueryRowContext(ctx, "EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) "+query, args...)
    var plan string
    if err := row.Scan(&plan); err != nil {
        return "", fmt.Errorf("explain: %w", err)
    }
    return plan, nil
}

5 things to look for in the output:

For full performance tuning (pg_stat_statements, autovacuum, bloat, monitoring): see references/query-performance.md.

Connection Pool Sizing

Formula (per backend process):

max_connections = (core_count * 2) + effective_spindle_count

For SSDs: core_count * 2 + 1 is a practical starting point (e.g., 4 cores → 9 connections).

Go application pool settings:

sqlDB.SetMaxOpenConns(25)          // Match PgBouncer pool_size or PostgreSQL max_connections budget
sqlDB.SetMaxIdleConns(5)           // Keep some warm connections ready
sqlDB.SetConnMaxLifetime(5 * time.Minute)  // Recycle to balance load across replicas
sqlDB.SetConnMaxIdleTime(1 * time.Minute)  // Close idle connections faster in low-traffic periods

PgBouncer guidance:

• Use transaction pooling mode (default) — releases connection back to pool after each transaction
• Set pool_size per database to match your PostgreSQL max_connections budget for that app
• Set reserve_pool_size = 5 for burst handling
• Don't use session pooling with connection-pool-aware Go drivers (pgx already pools)

Partitioning Strategy

Rules:

• Partition when a single table exceeds ~50–100M rows or you need fast bulk deletes (drop partition)
• Always include the partition key in WHERE clauses — otherwise the planner scans all partitions
• Use pg_partman for automated partition creation and retention
• Partitioned tables support indexes, constraints, and foreign keys (PostgreSQL 12+)

CREATE TABLE events (
    id         BIGINT GENERATED ALWAYS AS IDENTITY,
    tenant_id  UUID NOT NULL,
    event_type TEXT NOT NULL,
    payload    JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

Reference Files

Load these for deeper detail:

• references/schema-design.md — Naming conventions, data type selection, normalization, constraints, soft delete patterns, multi-tenancy, audit trails, complete schema example
• references/migration-impact-analysis.md — Lock hierarchy, safe vs unsafe ALTER TABLE, zero-downtime patterns for every operation, batched backfill, NOT VALID constraints, lock_timeout strategy, migration checklist
• references/index-strategy.md — B-tree, GIN, GiST, BRIN, SP-GiST, partial, expression, covering indexes, index maintenance, EXPLAIN ANALYZE deep dive, complete example
• references/query-performance.md — pg_stat_statements setup, autovacuum tuning, bloat detection, planner statistics, work_mem, connection pool sizing, read replicas, monitoring Go helper
• references/extensions-toolkit.md — pg_cron, pg_partman, pg_trgm, pg_stat_statements, pgcrypto, uuid-ossp, pgAudit, extension management in migrations
• references/paradedb-full-text-search.md — BM25 search, pg_search setup, queries, fuzzy/autocomplete, hybrid search with pgvector, analytics, Go integration
• references/pgvector-embeddings.md — Vector types, IVFFlat vs HNSW, similarity queries, Go integration with pgvector-go, capacity planning
• references/postgis-geospatial.md — geometry vs geography, spatial types, GiST indexes, distance/KNN/bounding box/point-in-polygon queries, Go integration
• references/timescaledb-time-series.md — Hypertables, time_bucket, continuous aggregates, compression, retention, downsampling, Go integration
• references/row-level-security.md — RLS setup, policy types, PERMISSIVE vs RESTRICTIVE, multi-tenant session variable pattern, Gin middleware, Go repository integration, performance, testing with testcontainers, common pitfalls
• references/backup-and-recovery.md — pg_dump/pg_restore, pg_basebackup, WAL archiving, PITR, backup validation, managed DB backups, disaster recovery checklist
• references/replication-and-ha.md — Streaming replication, logical replication, read/write splitting in Go, replication lag monitoring, Patroni HA, pg_auto_failover, managed HA, application resilience

Cross-Skill References

• For GORM/sqlx repository implementations and migrations tooling: see the golang-gin-database skill
• For testing database queries with testcontainers: see the golang-gin-testing skill
• For PostgreSQL Docker setup and Kubernetes StatefulSets: see the golang-gin-deploy skill
• For handler patterns that call repository methods: see the golang-gin-api skill