schema

Documentation

Overview

Package schema introspects PostgreSQL system catalogs and exposes a typed, JSON-serialisable snapshot of the tables, indexes, and foreign keys visible to the current user. Rules use the snapshot via the lookup helpers to avoid suggesting indexes that already exist and to spot missing-index coverage on foreign keys.

Index

• Constants
• Variables
• type Column
• type FKWithoutIndex
• type ForeignKey
• type Index
  • func (i *Index) FQN() string
• type Meta
• type Schema
  • func Fetch(ctx context.Context, pool *pgxpool.Pool) (*Schema, error)
  • func LoadJSON(r io.Reader) (*Schema, error)
  • func (s *Schema) DuplicateIndexes() [][]*Index
  • func (s *Schema) FKColumnsWithoutIndex() []FKWithoutIndex
  • func (s *Schema) FindIndexPrefixing(tableFQN string, cols []string) *Index
  • func (s *Schema) FindTable(name string) *Table
  • func (s *Schema) ForeignKeysOn(tableFQN string) []ForeignKey
  • func (s *Schema) HasIndexOn(tableFQN string, cols []string) bool
  • func (s *Schema) IndexesOn(tableFQN string) []*Index
  • func (s *Schema) IsStale() bool
  • func (s *Schema) StaleAge() time.Duration
  • func (s *Schema) WriteJSON(w io.Writer) error
• type Table
  • func (t *Table) FQN() string

Constants

const StalenessThreshold = 24 * time.Hour

StalenessThreshold is the age above which a loaded Schema is considered out of date. 24 hours is a compromise: schemas rarely change hourly but often change daily in active projects, so anything older gets a warning without being a hard error.

Variables

var ErrStaleSchema = errors.New("schema export is older than " + StalenessThreshold.String())

ErrStaleSchema is returned by LoadJSON (wrapped) when the schema export is older than StalenessThreshold. Callers can use errors.Is to detect it and surface a warning while still using the returned Schema.

Types

type Column

type Column struct {
	Name     string `json:"name"`
	DataType string `json:"data_type"`
	NotNull  bool   `json:"not_null"`
	Default  string `json:"default,omitempty"`
	Position int    `json:"position"`
}

Column describes one attribute of a Table. Position is 1-based to match pg_attribute.attnum.

type FKWithoutIndex

type FKWithoutIndex struct {
	FK      ForeignKey
	Missing []string
}

FKWithoutIndex pairs a foreign key with the columns that lack a supporting index. Missing is always non-empty.

type ForeignKey

type ForeignKey struct {
	Name              string   `json:"name"`
	Schema            string   `json:"schema"`
	Table             string   `json:"table"`
	Columns           []string `json:"columns"`
	ReferencedSchema  string   `json:"referenced_schema"`
	ReferencedTable   string   `json:"referenced_table"`
	ReferencedColumns []string `json:"referenced_columns"`
}

ForeignKey is a single FOREIGN KEY constraint.

type Index

type Index struct {
	Schema     string   `json:"schema"`
	Name       string   `json:"name"`
	Table      string   `json:"table"` // schema-qualified: "public.orders"
	Columns    []string `json:"columns"`
	IsUnique   bool     `json:"is_unique"`
	IsPrimary  bool     `json:"is_primary"`
	IsPartial  bool     `json:"is_partial"`
	WhereExpr  string   `json:"where_expr,omitempty"`
	Method     string   `json:"method"`
	SizeBytes  int64    `json:"size_bytes"`
	Definition string   `json:"definition"`

	// Scans comes from pg_stat_user_indexes.idx_scan and counts how
	// many times the planner picked this index since stats last
	// reset. A value of 0 can mean "never used" OR "stats were reset
	// recently"; the unused_index_hint rule surfaces the ambiguity
	// in its message. Old schema JSON exports without this field
	// load with Scans = 0, which effectively mutes the rule rather
	// than producing false positives.
	Scans int64 `json:"scans,omitempty"`
}

Index describes a single index on a table. Columns is ordered as the index itself sees them; callers must not re-sort. For expression-based entries the introspection layer filters the NULL attnames out, so Columns contains only direct column references.

func (*Index) FQN

func (i *Index) FQN() string

FQN returns "schema.name" for the index. See Table.FQN for quoting rules.

type Meta

type Meta struct {
	ExportedAt     time.Time `json:"exported_at"`
	Database       string    `json:"database"`
	ServerVersion  string    `json:"server_version"`
	DBAgentVersion string    `json:"dbagent_version"`
}

Meta records provenance for a schema snapshot. Without it, a JSON export is ambiguous about when it was taken and against which database.

type Schema

type Schema struct {
	Meta    Meta              `json:"meta"`
	Tables  map[string]*Table `json:"tables"`
	Indexes map[string]*Index `json:"indexes"`
	FKeys   []ForeignKey      `json:"foreign_keys"`
}

Schema is the top-level introspection result. Tables and Indexes are keyed by fully qualified names ("schema.name") for O(1) lookup.

func Fetch

func Fetch(ctx context.Context, pool *pgxpool.Pool) (*Schema, error)

Fetch runs a read-only transaction against pool and returns a populated Schema. All queries target pg_catalog directly (not information_schema) and exclude system schemas. No user tables are read, only metadata.

func LoadJSON

func LoadJSON(r io.Reader) (*Schema, error)

LoadJSON parses a schema JSON document from r. Returns the parsed Schema AND ErrStaleSchema (wrapped) when the export is older than the threshold — callers must surface the warning but should not refuse to proceed. For any other parse error, the returned Schema is nil.

func (*Schema) DuplicateIndexes

func (s *Schema) DuplicateIndexes() [][]*Index

DuplicateIndexes returns groups of indexes on the same table that share identical column lists (order-sensitive). Each group has two or more entries; within a group, indexes are sorted by name for stable output.

"Duplicate" here is strict: indexes on (a, b) and (b, a) are NOT duplicates — they support different query shapes. Partial indexes are excluded because their WHERE clauses make them semantically distinct even when columns match. Primary-key and unique indexes are kept in the output but rule consumers should prefer dropping the non-constraint variant.

func (*Schema) FKColumnsWithoutIndex

func (s *Schema) FKColumnsWithoutIndex() []FKWithoutIndex

FKColumnsWithoutIndex returns every foreign key whose local columns are not covered by a leading-column btree index. The list is sorted by (table FQN, fk name) for deterministic output regardless of how the schema was constructed (live fetch, JSON load, hand- built test).

Coverage check: we look for an index whose leading columns equal the FK's column list (ordering sensitive). An index on (a, b, c) covers an FK on (a, b) because a query "WHERE a = ? AND b = ?" can use the leading-prefix range. An FK on (b, a) is NOT covered by an index on (a, b).

func (*Schema) FindIndexPrefixing

func (s *Schema) FindIndexPrefixing(tableFQN string, cols []string) *Index

FindIndexPrefixing returns an existing index on tableFQN whose column list is a strict prefix of cols — in other words, an index that partially covers the request and could be extended to cover the rest. The return value is a candidate for a DROP+CREATE rewrite, so primary-key and unique indexes are deliberately excluded: dropping them would remove the underlying constraint, which this helper can't justify without knowing more than it does.

Examples with request [a, b, c]:

idx cols: [a]       → candidate (shorter, leading columns match)
idx cols: [a, b]    → better candidate (longer prefix)
idx cols: [a, b, c] → NOT returned (already an exact match; use HasIndexOn)
idx cols: [b, a]    → NOT returned (ordering differs)
idx cols: [a, b, d] → NOT returned (diverges at position 2)
idx cols: [a]  pkey → NOT returned (would require dropping the pkey)

When multiple candidates exist, the longest prefix wins; ties are broken by ascending FQN so the result is deterministic across map iteration orders.

func (*Schema) FindTable

func (s *Schema) FindTable(name string) *Table

FindTable returns a table by fully qualified or bare name. Bare names prefer "public" and fall back to any single match across schemas; an ambiguous name (multiple matches) returns nil.

func (*Schema) ForeignKeysOn

func (s *Schema) ForeignKeysOn(tableFQN string) []ForeignKey

ForeignKeysOn returns every foreign key declared on tableFQN.

func (*Schema) HasIndexOn

func (s *Schema) HasIndexOn(tableFQN string, cols []string) bool

HasIndexOn reports whether tableFQN has a btree index whose column list starts with cols. A longer index such as (a, b, c) covers cols [a, b] because the leading columns are the same. Partial indexes and non-btree methods are skipped because they don't deliver the same coverage as a plain btree.

Examples:

idx cols: [a, b, c],   request: [a]       → true  (prefix match)
idx cols: [a, b, c],   request: [a, b]    → true
idx cols: [a, b, c],   request: [a, b, c] → true
idx cols: [a, b, c],   request: [a, b, d] → false (fourth column mismatches)
idx cols: [b, a],      request: [a]       → false (ordering matters)
idx cols: [a] partial, request: [a]       → false (partial indexes don't cover all rows)
idx cols: [a] GIN,     request: [a]       → false (non-btree is special-purpose)

func (*Schema) IndexesOn

func (s *Schema) IndexesOn(tableFQN string) []*Index

IndexesOn returns every index declared on tableFQN. The returned slice is freshly allocated; callers may sort it.

func (*Schema) IsStale

func (s *Schema) IsStale() bool

IsStale reports whether the schema was exported more than StalenessThreshold ago. Returns false if ExportedAt is the zero value (the JSON predates versioning).

func (*Schema) StaleAge

func (s *Schema) StaleAge() time.Duration

StaleAge returns how long ago the schema was exported. Returns 0 when ExportedAt is zero.

func (*Schema) WriteJSON

func (s *Schema) WriteJSON(w io.Writer) error

WriteJSON writes s to w as pretty-printed JSON (two-space indent). Meta is expected to be populated by the caller; WriteJSON does not modify it.

type Table

type Table struct {
	Schema       string    `json:"schema"`
	Name         string    `json:"name"`
	Columns      []Column  `json:"columns"`
	RowEstimate  int64     `json:"row_estimate"`
	SizeBytes    int64     `json:"size_bytes"`
	LastAnalyzed time.Time `json:"last_analyzed,omitempty"`
	LastVacuumed time.Time `json:"last_vacuumed,omitempty"`
}

Table is a single relation with its columns and maintenance stats. RowEstimate comes from pg_class.reltuples and is intentionally an estimate — callers that want exact counts must query the table themselves.

func (*Table) FQN

func (t *Table) FQN() string

FQN returns "schema.name". Identifiers are double-quoted when they contain characters outside the safe set, matching PostgreSQL's own rules so the FQN can be pasted straight into SQL.