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

| Rule | Do | Don't | |---|---|---| | Primary keys | id UUID DEFAULT gen_random_uuid() or id BIGINT GENERATED ALWAYS AS IDENTITY | SERIAL (legacy) | | Timestamps | TIMESTAMPTZ with DEFAULT now() | TIMESTAMP (no timezone) | | Booleans | NOT NULL DEFAULT false | Nullable booleans (three-valued logic) | | Money | NUMERIC(19,4) | FLOAT, REAL, DOUBLE PRECISION | | Status/enum | TEXT + CHECK constraint, or PostgreSQL ENUM type | Free-text strings | | Naming | snake_case, plural table names (users), singular columns | camelCase, PascalCase | | Soft delete | deleted_at TIMESTAMPTZ (nullable) | Boolean is_deleted | | Foreign keys | Always ON DELETE clause (CASCADE, SET NULL, or RESTRICT) | Omitting ON DELETE |

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

Index Selection Decision Tree

| Query Pattern | Index Type | Example | |---|---|---| | Equality (=), range (<, >, BETWEEN) | B-tree (default) | CREATE INDEX idx_users_email ON users (email) | | Full-text search (@@, tsvector) | GIN | CREATE INDEX idx_posts_search ON posts USING gin (search_vector) | | JSONB containment (@>), array overlap (&&) | GIN | CREATE INDEX idx_meta ON products USING gin (metadata jsonb_path_ops) | | Geometric / spatial / range types | GiST | CREATE INDEX idx_loc ON stores USING gist (location) | | Large table, monotonic column (timestamp, id) | BRIN | CREATE INDEX idx_events_ts ON events USING brin (created_at) | | Trigram similarity (%, LIKE '%foo%') | GIN + pg_trgm | CREATE INDEX idx_name_trgm ON users USING gin (name gin_trgm_ops) | | High-dimensional vectors (embeddings) | HNSW or IVFFlat (pgvector) | CREATE INDEX idx_embed ON items USING hnsw (embedding vector_cosine_ops) | | IP ranges, text ranges | SP-GiST | CREATE INDEX idx_ip ON logs USING spgist (ip_range) |

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.

| Operation | Lock Level | Safe Online? | Notes | |---|---|---|---| | ADD COLUMN (nullable, no default) | AccessExclusiveLock | Yes — fast metadata change | Safest column addition | | ADD COLUMN ... DEFAULT x (PG 11+) | AccessExclusiveLock | Yes — fast since PG 11 | Pre-PG11: rewrites table | | ADD COLUMN ... NOT NULL DEFAULT x (PG 11+) | AccessExclusiveLock | Yes — fast since PG 11 | Same as above | | DROP COLUMN | AccessExclusiveLock | Yes — fast metadata mark | Data remains until VACUUM | | ALTER COLUMN SET NOT NULL | AccessExclusiveLock | Slow — full table scan | Use NOT VALID constraint instead | | ALTER COLUMN TYPE | AccessExclusiveLock | Slow — full table rewrite | Create new column + backfill instead | | ADD CONSTRAINT ... FOREIGN KEY | ShareRowExclusiveLock | Slow — validates all rows | Use NOT VALID then VALIDATE separately | | CREATE INDEX | ShareLock | Blocks writes | Use CONCURRENTLY instead | | CREATE INDEX CONCURRENTLY | ShareUpdateExclusiveLock | Yes | Takes longer but doesn't block | | DROP INDEX | AccessExclusiveLock | Blocks all | Use CONCURRENTLY |

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

| Need | Extension | Maturity | Reference | |---|---|---|---| | Full-text BM25 search | ParadeDB (pg_search) | Growing (production-ready) | paradedb-full-text-search.md | | Vector similarity (embeddings) | pgvector | Mature | pgvector-embeddings.md | | Geospatial queries | PostGIS | Very mature | postgis-geospatial.md | | Time-series data | TimescaleDB | Mature | timescaledb-time-series.md | | Cron jobs inside PostgreSQL | pg_cron | Mature | extensions-toolkit.md | | Table partitioning management | pg_partman | Mature | extensions-toolkit.md | | Fuzzy string matching (LIKE '%x%') | pg_trgm | Core contrib | extensions-toolkit.md | | Encryption / hashing | pgcrypto | Core contrib | extensions-toolkit.md | | Audit logging | pgAudit | Mature | extensions-toolkit.md |

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:

| Signal | What It Means | Fix | |---|---|---| | Seq Scan on large table | Missing index or planner ignoring it | Add index; check random_page_cost | | actual rows= ≫ rows= (estimated) | Stale statistics | ANALYZE tablename; | | Buffers: shared hit=0 read=N | Cold cache, high I/O | Increase shared_buffers; warm cache | | Sort Method: external merge | work_mem too low for sort | Increase work_mem for session | | Nested Loop with large outer | Planner underestimating join size | Check stats; consider SET enable_nestloop = off to test |

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

| Strategy | When to Use | Example | |---|---|---| | RANGE | Time-series, date-based queries | PARTITION BY RANGE (created_at) | | LIST | Known, fixed categories | PARTITION BY LIST (region) | | HASH | Even distribution, no natural range | PARTITION BY HASH (user_id) |

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