Chapter 12: Go: No Fuss, No Nonsense

Go's approach to PostgreSQL is, if I may say so, the most honest in this book. You write SQL. You scan rows into structs. The compiler checks the types. There is no abstraction to pierce, no magic to debug, and no ORM to fight.

This chapter is the shortest in Part III. This is not because Go deserves less attention — it is because Go requires less explanation. A materialized view is a SQL object. Go writes SQL. The integration requires no workaround, no package, and no annotation. pgx delivers the queries. sqlc validates them at compile time. A goroutine refreshes the view on a schedule. That is the entire story, and I see no reason to pad it.

pgx: The Direct Approach

The pgx driver by Jack Christensen (jackc/pgx v5) is the dominant PostgreSQL driver for Go — over 8,000 packages import its connection pool alone. It provides a native PostgreSQL interface with binary protocol support, LISTEN/NOTIFY, COPY, and connection pooling via the pgxpool package.

The connection pool

package main

import (
    "context"
    "log"
    "os"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

func NewPool(ctx context.Context) (*pgxpool.Pool, error) {
    config, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
    if err != nil {
        return nil, err
    }
    config.MaxConns = 20
    config.MinConns = 5
    config.MaxConnLifetime = time.Hour
    config.MaxConnIdleTime = 30 * time.Minute

    pool, err := pgxpool.NewWithConfig(ctx, config)
    if err != nil {
        return nil, err
    }

    if err := pool.Ping(ctx); err != nil {
        pool.Close()
        return nil, err
    }
    return pool, nil
}

pgxpool is concurrency-safe. Pass the pool to HTTP handlers, background goroutines, and service layers without coordination. No mutex needed. The pool manages connection acquisition, release, health checking, and lifetime rotation internally.

The migration

Using golang-migrate, the standard file-based migration tool for Go:

-- migrations/000001_create_events.up.sql
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    event_type TEXT NOT NULL,
    amount NUMERIC(13,2) DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- migrations/000002_create_mv_dashboard.up.sql
CREATE MATERIALIZED VIEW mv_dashboard AS
SELECT
    date_trunc('day', created_at)::date AS day,
    COUNT(*)::integer AS events,
    COUNT(DISTINCT user_id)::integer AS unique_users,
    SUM(CASE WHEN event_type = 'purchase'
        THEN amount ELSE 0 END)::numeric(13,2) AS revenue
FROM events
WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
WITH DATA;

CREATE UNIQUE INDEX idx_mv_dashboard_day ON mv_dashboard (day);

-- migrations/000002_create_mv_dashboard.down.sql
DROP MATERIALIZED VIEW IF EXISTS mv_dashboard CASCADE;

Run with migrate -path migrations/ -database $DATABASE_URL up. The materialized view is created after the base tables, indexed for concurrent refresh, and reversible with migrate down.

The struct

type DashboardMetrics struct {
    Day         time.Time      `db:"day"`
    Events      int32          `db:"events"`
    UniqueUsers int32          `db:"unique_users"`
    Revenue     pgtype.Numeric `db:"revenue"`
}

A plain Go struct. No base class. No interface implementation. No annotations beyond struct tags for column mapping. The pgtype.Numeric type handles PostgreSQL's NUMERIC with arbitrary precision — more faithful than a float64, which would introduce rounding.

Query

func GetDashboardMetrics(ctx context.Context, pool *pgxpool.Pool, days int) ([]DashboardMetrics, error) {
    rows, err := pool.Query(ctx,
        `SELECT day, events, unique_users, revenue
         FROM mv_dashboard
         WHERE day >= CURRENT_DATE - $1::integer
         ORDER BY day DESC`,
        days,
    )
    if err != nil {
        return nil, err
    }
    return pgx.CollectRows(rows, pgx.RowToStructByName[DashboardMetrics])
}

pgx.CollectRows with pgx.RowToStructByName is pgx v5's generic scanning — it maps result columns to struct fields by matching the db tag to the column name. Type-safe at compile time. No manual rows.Next() / rows.Scan() loop. The function returns a typed slice and an error. That is all.

Refresh

func RefreshDashboard(ctx context.Context, pool *pgxpool.Pool) error {
    _, err := pool.Exec(ctx,
        "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard",
    )
    return err
}

One function. One SQL statement. One return value.

I could, I suppose, wrap this in a service struct with an interface and a constructor and a configuration object. I will not. The function does what it says and says what it does. Go developers will understand why I have left it this way.

sqlc: Compile-Time Safety

sqlc takes Go's SQL-first philosophy one step further: you write SQL in .sql files, and sqlc generates type-safe Go code at compile time. If your SQL references a column that doesn't exist, sqlc generate fails — not your application at runtime.

Configuration

# sqlc.yaml
version: "2"
sql:
  - engine: "postgresql"
    schema: "schema.sql"
    queries: "query.sql"
    gen:
      go:
        package: "db"
        out: "db"
        sql_package: "pgx/v5"

Schema

-- schema.sql
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    event_type TEXT NOT NULL,
    amount NUMERIC(13,2) DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE MATERIALIZED VIEW mv_dashboard AS
SELECT
    date_trunc('day', created_at)::date AS day,
    COUNT(*)::integer AS events,
    COUNT(DISTINCT user_id)::integer AS unique_users
FROM events
GROUP BY 1;

CREATE UNIQUE INDEX idx_mv_dashboard_day ON mv_dashboard (day);

Queries

-- query.sql

-- name: GetDashboardMetrics :many
SELECT * FROM mv_dashboard ORDER BY day DESC LIMIT $1;

-- name: RefreshDashboard :exec
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard;

Generated code

Running sqlc generate produces three files in the db/ directory: db.go (the connection interface), models.go (structs matching your schema), and query.sql.go (typed functions for each named query).

The generated MvDashboard struct matches the materialized view's columns. The generated GetDashboardMetrics function accepts a context and a limit parameter, returns []MvDashboard, and handles all row scanning internally. The generated RefreshDashboard function executes the REFRESH statement and returns an error.

Usage:

queries := db.New(pool)

metrics, err := queries.GetDashboardMetrics(ctx, 30)
if err != nil {
    return err
}
// metrics is []db.MvDashboard — typed at compile time

err = queries.RefreshDashboard(ctx)

Two lines for the query, one line for the refresh. The types are correct. The SQL is validated against the schema. If someone renames a column in the materialized view definition and forgets to update the query, sqlc generate catches it before the code compiles.

A note on materialized view support: sqlc's offline SQL parser historically had difficulty recognizing materialized views — GitHub issues #831 and #1021 document "relation does not exist" errors during code generation. In recent versions, sqlc's database-backed analyzer resolves this by gathering metadata from an actual PostgreSQL instance. If you encounter these errors, add a database block with a connection URI to your sqlc.yaml. For most current projects this is unnecessary, but the workaround is worth knowing.

When to use sqlc versus raw pgx: sqlc provides compile-time validation that your SQL matches your schema, which is valuable when multiple developers write queries against multiple materialized views. For a single materialized view with two queries — one SELECT, one REFRESH — raw pgx is simpler and has no code generation step.

Scheduling the Refresh

time.Ticker: standard library

func StartRefreshLoop(ctx context.Context, pool *pgxpool.Pool) {
    ticker := time.NewTicker(15 * time.Minute)
    defer ticker.Stop()

    for {
        select {
        case <-ticker.C:
            if err := RefreshDashboard(ctx, pool); err != nil {
                log.Printf("Dashboard MV refresh failed: %v", err)
            } else {
                log.Println("Dashboard MV refreshed")
            }
        case <-ctx.Done():
            log.Println("Refresh loop shutting down")
            return
        }
    }
}

// In main()
ctx, cancel := context.WithCancel(context.Background())
defer cancel()
go StartRefreshLoop(ctx, pool)

A goroutine, a ticker, and a context. The select statement handles both the periodic tick and the shutdown signal. When the application's context is cancelled — during a graceful shutdown triggered by SIGTERM, for instance — the goroutine exits cleanly. No external library. No dependency. The standard library provides everything needed.

robfig/cron: for cron syntax

import "github.com/robfig/cron/v3"

c := cron.New()
c.AddFunc("*/15 * * * *", func() {
    if err := RefreshDashboard(context.Background(), pool); err != nil {
        log.Printf("Refresh failed: %v", err)
    }
})
c.Start()
defer c.Stop()

For teams managing multiple materialized views on different schedules — the dashboard every 15 minutes, the weekly rollup once per hour, the search index every 5 minutes — cron syntax is more expressive than multiple tickers. The robfig/cron library is the standard choice.

LISTEN/NOTIFY: event-driven refresh

func ListenForRefresh(ctx context.Context, pool *pgxpool.Pool) error {
    conn, err := pool.Acquire(ctx)
    if err != nil {
        return err
    }
    defer conn.Release()

    _, err = conn.Exec(ctx, "LISTEN mv_refresh")
    if err != nil {
        return err
    }

    var debounceTimer *time.Timer

    for {
        notification, err := conn.Conn().WaitForNotification(ctx)
        if err != nil {
            return err
        }

        if debounceTimer != nil {
            debounceTimer.Stop()
        }
        debounceTimer = time.AfterFunc(5*time.Second, func() {
            if err := RefreshDashboard(ctx, pool); err != nil {
                log.Printf("Refresh of %s failed: %v",
                    notification.Payload, err)
            }
        })
    }
}

The acquired connection stays in LISTEN mode permanently — it must not be released back to the pool until you are done listening. WaitForNotification blocks until a notification arrives. The 5-second debounce timer, as in Chapter 5, prevents rapid writes from triggering rapid refreshes.

Multi-instance: advisory lock

func RefreshWithLock(ctx context.Context, pool *pgxpool.Pool) error {
    conn, err := pool.Acquire(ctx)
    if err != nil {
        return err
    }
    defer conn.Release()

    var acquired bool
    err = conn.QueryRow(ctx,
        "SELECT pg_try_advisory_lock(hashtext('mv_dashboard'))",
    ).Scan(&acquired)
    if err != nil {
        return err
    }
    if !acquired {
        return nil // another instance is refreshing
    }
    defer conn.Exec(ctx,
        "SELECT pg_advisory_unlock(hashtext('mv_dashboard'))",
    )
    _, err = conn.Exec(ctx,
        "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard",
    )
    return err
}

pool.Acquire is deliberate — session-level advisory locks are bound to the connection, so the lock, the refresh, and the unlock must execute on the same one. Multiple Kubernetes pods, multiple ECS tasks, multiple instances behind a load balancer — each schedules the same refresh. The advisory lock ensures one executes. The others return nil and wait for the next tick.

And pg_cron, as always, eliminates the Go code entirely.

Go required the fewest words in this chapter and the fewest abstractions in the code. The materialized view is SQL. Go writes SQL. pgx delivers it. sqlc validates it at compile time. A goroutine refreshes it. There is nothing to configure, nothing to annotate, and nothing to fight.

In the next chapter, we attend to .NET — where Entity Framework provides a rather more opinionated experience. The materialized view, characteristically, does not have an opinion.