Melange v0.8.3

Melange v0.8.3 lands a performance pass on generated check functions — branches now evaluate cheap-first and recursive callees carry an explicit COST hint — plus a schema-derived index recommender that surfaces the composite indexes your melange_tuples view actually needs through melange doctor.

Performance

Cheap-First Branch Ordering + COST Hints

PostgreSQL evaluates PL/pgSQL IF blocks in source order, and treats every STABLE function as cost-100 unless told otherwise. That left generated check functions doing two avoidable things on schemas with mixed-cost access paths:

1. Expensive callees evaluated before cheap EXISTS branches. When a relation like viewer: cheap_grant or via_complex_userset or via_simple_userset resolved through the recursive renderer, the OR chain ran in source order, paying for check_permission_internal calls when the cheap simple-userset path would have answered the same question in a single tuple JOIN.

2. The planner had no signal that recursive function calls were costlier than the surrounding EXISTS. With every function defaulting to cost-100, predicate ordering in the few places PG could legally reorder ended up source-order anyway.

This release adds compile-time complexity scoring and uses it at codegen to stable-sort:

• UsersetPattern checks — simple JOIN-resolvable patterns precede recursive check_permission_internal invocations inside the userset OR.
• ImpliedFunctionCalls — cheaper specialized callees come first in OR chains.
• ParentRelationBlocks — sequential IF chain in recursive renderers tries cheap parents first.
• Intersection AND-parts — inline EXISTS branches evaluate before function calls so the AND short-circuits earlier.

Functions whose body invokes check_permission_internal (directly, or transitively via a complex closure relation) are emitted with COST 1000. Scores propagate along ComplexClosureRelations to a fixed point, so a wrapper like a: b where b: c and c is recursive inherits c’s tier and never gets sorted ahead of a non-recursive alternative — which avoided an M2002 hazard at the depth-25 boundary that an earlier draft of this work introduced.

Measured on five new testdata/mixed_complexity.yaml schemas (Apple M2 Pro, Postgres 18-alpine, -benchtime=3s -count=3):

At 100K tuples on a GitHub-style schema, all deltas land within ±5% of the prior baseline (the noise band). The cheap-first ordering’s wins are concentrated on workloads that genuinely have a cheap path the planner would have taken last. The public BuildCheckPlan signature stays compatible — opt-in to ordering-aware planning is through the new BuildCheckPlanWithOrdering(..., complexityByRelation) form.

Tooling

Schema-Derived Index Recommendations Through melange doctor

melange doctor previously suggested two hardcoded composite indexes for melange_tuples. The hardcoded list_objects recommendation used (object_type, relation, subject_type, subject_id, object_id) — but generated list_*_obj functions filter by (subject_type, subject_id, …) first. PostgreSQL would seq-scan or pick a different index entirely; the recommendation never served its claimed access pattern. Worse, the table-only code path meant the common production setup (a melange_tuples view over source tables) got no index guidance at all.

Doctor now derives its recommendations from sqlgen.RecommendIndexes() against your actual schema. Three index families are emitted per generatable relation:

• Object-keyed (object_type, object_id, relation, subject_type, subject_id) — covers check_* and list_*_sub.
• Subject-keyed (subject_type, subject_id, relation, object_type, object_id) — covers list_*_obj. The correct order for the access pattern the generated SQL actually uses.
• Wildcard partial (object_type, object_id, relation) WHERE subject_id = '*' — only emitted for relations using [type:*] so wildcard membership lookups don’t scan the full subject_id space.

Recommendations are deduplicated across relations: when several functions share an access pattern, one entry lists all benefiting function names. DDL is rendered as CREATE INDEX IF NOT EXISTS … ready to copy-paste.

The recommendations surface in two doctor flows:

• Table-backed melange_tuples (rare in production): missing indexes are flagged as warnings or fails (severity tracks the table’s row count) with the exact CREATE INDEX statement as the FixHint.
• View-backed melange_tuples (typical production): a new source_table_indexes_advisory check emits all recommendations as advisory output with full DDL visible via melange doctor --verbose. Users translate the index target from melange_tuples to whichever source tables back their view’s UNION ALL branches.

The recommender is exposed as a public API on GeneratedSQL.IndexRecommendations for tooling integrations that want to consume the data directly.

Doctor Partial-Index Parsing

Detecting whether a recommended partial index already exists requires reading the WHERE clause of existing indexes from pg_indexes. The pre-existing parseIndexColumns helper used strings.LastIndex(")") to find the column-list boundary — which grabs the predicate’s closing paren on partial indexes (… (cols) WHERE (pred)), discarding the actual column list. A new splitIndexKeysAndPredicate helper splits at the ) WHERE boundary so partial-index column extraction works correctly, and a predicatesEquivalent helper canonicalises PG’s (subject_id = '*'::text) rendering against the recommendation’s subject_id = '*' for matching.

Features

Opt-in AS MATERIALIZED Hints for Multi-Referenced CTEs

PostgreSQL 12 changed the default behaviour for non-recursive CTEs to inline them at each reference site rather than materialise them once. For some list_* shapes — paged and returned in the pagination wrappers, base_results in recursive and self-ref list_subjects — this means the inner expansion is computed twice.

This release adds GenerateSQLOptions.EnableMaterializedCTEs so callers can force AS MATERIALIZED on multi-referenced CTEs in generated list functions.

It is off by default, and intentionally so. The first draft of this work shipped with materialisation on by default. Scale benchmarks at 100K tuples on a GitHub-style schema showed a consistent ~10% regression on heavy list_objects queries — PG’s own inlining decision was already better than the hint on that workload. The infrastructure is preserved behind the flag for users who profile a workload where forced materialisation wins; for everyone else, leaving PG to decide is now the verified default.

The opt-in is surfaced through new GenerateSQLWithOptions / GenerateListSQLWithOptions variants. The original GenerateSQL / GenerateListSQL signatures are kept and delegate with zero-value options.

Migration Notes

From v0.8.2

No breaking changes. Upgrade and run migrations to pick up the regenerated SQL functions for the cheap-first ordering and COST hints:

melange migrate

If you use melange generate migration, regenerate your migration files to pick up the new ordering:

melange generate migration \
  --schema melange/schema.fga \
  --output db/migrations \
  --git-ref main

To act on the new index recommendations, run melange doctor --verbose and apply the suggested CREATE INDEX statements to whichever source tables back your melange_tuples view. The recommendations are tuned to the access patterns your specific schema generates — they will change if you add relations using [user:*] patterns or new userset references.

Try It Out

# Install / upgrade CLI
brew install pthm/melange/melange

# Or pull the container image
docker pull ghcr.io/pthm/melange:v0.8.3

# Or install the .deb / .rpm package from the GitHub release

# Apply migrations
melange migrate

# See recommended indexes for your schema
melange doctor --verbose

# Go runtime
go get github.com/pthm/melange/melange@v0.8.3

# TypeScript runtime
npm install @pthm/melange

Feedback

We welcome feedback and bug reports. Please open an issue with questions or feature requests.