postgres / generated views

updated October 07, 2026

One of my apps filters companies and people on about a hundred fields. A filter reads a wide cache table, one row per company, so a query needs no joins. A plain view, cache_companies_source, defines the cache. A job copies the view into the table every minute.

Go code generates the view's SQL from a list of field definitions, because the same list drives the filter UI. Before, a command wrote that SQL into a migration. Each migration restated the whole view, about 800 lines, to move one column. Three of them landed in nine days, and a reviewer read all of each to find the change.

Now the Go definition is the only definition. The migrate command compares it with the view in the database, and rebuilds the view and its table when they differ in meaning.

The definition

Each field names its column, its type, and the CTE that computes it. A function builds one SELECT from the list: the parent table, a LEFT JOIN for each CTE on parent_id, and one column for each field.

type cacheView struct {
	Kind        string
	Schema      []FieldSchema
	Ctes        map[string]string
	ParentTable string
}

func (v cacheView) Table() string  { return "cache_" + v.Kind }
func (v cacheView) Source() string { return v.Table() + "_source" }
func (v cacheView) SQL() string    { return MakeViewSQL(v.Schema, v.Ctes, v.ParentTable) }

A field change is a Go diff of a few lines. A reviewer reads it as a Go diff.

Sync on migrate

The migrate command runs the pending migrations first, then syncs each view:

func SyncCacheViews(ctx context.Context, db *pgdb.DB) ([]string, error) {
	var rebuilt []string
	for _, v := range cacheViews() {
		changed, err := syncCacheView(ctx, db, v)
		if err != nil {
			return rebuilt, err
		}
		if changed {
			rebuilt = append(rebuilt, v.Kind)
		}
	}
	return rebuilt, nil
}

The deploy already runs the migrate command before it starts the new release. So a field change reaches production with no new step, and the migrate command is the only way a view changes.

Each sync runs in one transaction:

func syncCacheView(ctx context.Context, db *pgdb.DB, v cacheView) (bool, error) {
	sql := v.SQL()
	changed := false
	err := db.Transaction(ctx, func(tx *pgdb.DB) error {
		if err := tx.Exec(ctx, "EXPLAIN "+sql); err != nil {
			return fmt.Errorf("the generated %s view SQL is invalid against the current schema: %w", v.Kind, err)
		}
		current, err := cacheViewMatches(ctx, tx, v, sql)
		if err != nil {
			return err
		}
		if current {
			return nil
		}
		changed = true
		return RebuildTable(ctx, tx, v.Table(), sql)
	})
	return changed, err
}

EXPLAIN plans the generated SQL against the live schema without running it. A Go field that reads a column no migration added fails there, before the rebuild starts. A column rename takes two steps: a migration adds the new column, and then the Go definition reads it.

Compare parse trees

A text comparison does not work. Postgres stores a view as a parse tree and renders it back with pg_get_viewdef. The rendering changes the indentation and the letter case. It rewrites x IN ('a', 'b') as x = ANY (ARRAY['a'::text, 'b'::text]).

So I put both sides through the parser. The Go SQL goes into a temporary view, and so does the installed definition. I read each one back with pg_get_viewdef, and compare the two renderings:

func settleViewDef(ctx context.Context, tx *pgdb.DB, name, sql string) (string, error) {
	defer tx.Exec(ctx, "DROP VIEW IF EXISTS pg_temp."+name)
	for range 10 {
		if err := tx.Exec(ctx, "CREATE OR REPLACE TEMP VIEW "+name+" AS "+sql); err != nil {
			return "", fmt.Errorf("reparse %s: %w", name, err)
		}
		var next string
		if err := tx.QueryRow(ctx,
			"SELECT pg_get_viewdef(to_regclass($1), TRUE)", "pg_temp."+name).Scan(&next); err != nil {
			return "", err
		}
		if next == sql {
			return next, nil
		}
		sql = next
	}
	return "", fmt.Errorf("%s did not settle after 10 parser passes", name)
}

One pass can leave a difference. A view read out of the database has been through the parser more times than SQL built in Go. On the next parse, Postgres moves an array's cast into each element, so the same meaning renders two ways. The loop parses until the rendering stops changing. Today that takes one or two passes. The bound of ten catches a rendering that cycles, so the command reports it and does not loop.

The temporary views are session-scoped, which is one reason the comparison and the rebuild share a transaction.

A test runs the same comparison for each view against the test database. It fails when the Go definition and the installed view differ in meaning. That test replaced the migration as the guard.

Build beside, then swap

The job that copies the view every minute builds a new table beside the live one, fills it, indexes it, and swaps it in. A rebuild for a definition change uses the same steps. It also replaces the view, and it takes its columns from the new view:

func RebuildTable(ctx context.Context, tx *pgdb.DB, name, sql string) error {
	build, source, buildSource := name+"_bld", name+"_source", name+"_source_bld"

	// The refresh job takes the same lock, so the two run one at a time.
	if err := tx.Exec(ctx, "SELECT pg_advisory_xact_lock(hashtextextended($1::text, 0))", name); err != nil {
		return err
	}
	indexes, err := tableIndexes(ctx, tx, name)
	if err != nil {
		return err
	}

	// Build the new pair beside the live one. Readers keep the live table.
	tx.Exec(ctx, fmt.Sprintf("CREATE VIEW %s AS %s", buildSource, sql))
	tx.Exec(ctx, fmt.Sprintf("CREATE TABLE %s AS SELECT * FROM %s", build, buildSource))
	// ... create each index on the build table, then ANALYZE it

	// The swap.
	tx.Exec(ctx, "SELECT set_config('lock_timeout', '10s', true)")
	tx.Exec(ctx, "DROP VIEW IF EXISTS "+source)
	tx.Exec(ctx, fmt.Sprintf("ALTER VIEW %s RENAME TO %s", buildSource, source))
	tx.Exec(ctx, "DROP TABLE "+name)
	tx.Exec(ctx, fmt.Sprintf("ALTER TABLE %s RENAME TO %s", build, name))
	// ... rename each build index to its live name
	return nil
}

The real code checks each error. I left those checks out here.

The advisory lock matters. Without it, a refresh could copy the old view, wait, and swap after the rebuild committed. It would then drop the table that the rebuild built. With the lock, the two run one after the other, and a refresh that waited copies the new view.

Readers keep the live table until the DROP TABLE. The swap waits at most 10 seconds for its lock, the same lock_timeout the migration runner sets. The refresh job waits one second and tries again a minute later. A rebuild has no later run, so a timeout fails the deploy, and the next deploy runs it again.

Who owns what

A migration owns the cache table's indexes. The Go definition owns its columns. So the rebuild copies each live index onto the build table. When an index reads a column that the new view does not project, the rebuild fails with a message such as:

index_cache_companies_on_city reads a column the new definition of cache_companies does not project.
Dropping an indexed field takes two steps: drop the index in a migration,
then drop the field from the Go definition.

It does not drop the index quietly. A missing index shows up later as a slow query, far from the change that caused it.

What it replaced

I also looked at a SQL file for each view, checked in beside the Go code. That would hold about 1,300 lines of SQL for good, to save about 900 lines of migration each time a field moved. The repo had a target to shrink its SQL, so the Go source stays the one definition.

A migration still writes a hand-written view, such as a search view that no Go list generates. The sync reads only the views that Go generates.

← All articles