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

Make PostgreSQL architecture decisions for Go Gin APIs. Schema design, migration safety, index strategy, query optimization, and extension selection. Uses raw SQL via sqlx — for ORM patterns, see the golang-gin-database skill.

When to Use

• Designing a new PostgreSQL schema (tables, types, constraints, naming)
• Evaluating whether an ALTER TABLE is safe to run on a live database
• Choosing the right index type for a query pattern
• Reading EXPLAIN ANALYZE output and fixing slow queries
• Sizing connection pools or tuning autovacuum
• Selecting a PostgreSQL extension (search, vectors, geospatial, time-series)
• Deciding on partitioning strategy

golang-gin-psql-dba vs golang-gin-database: golang-gin-database covers GORM/sqlx wiring, repository pattern, and migrations tooling (golang-migrate). golang-gin-psql-dba covers the PostgreSQL decisions behind those patterns — what data types to pick, which index to create, how to ALTER TABLE safely, and when to reach for an extension.

Schema Design Quick Rules

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