Problem
reconcileMetadata in pkg/catalog/db/reconcile.go has multiple N+1 query patterns where individual DB queries are executed inside loops:
-
collectFieldModuleCatalogs (line ~130): When a module is not yet in moduleMap, it does a per-module SELECT query_root, mutation_root, function_root, mut_function_root FROM _schema_modules WHERE name = $1 inside a loop. If there are M modules, this is M extra queries.
-
Module upserts (line ~153): Each module record is inserted/updated with an individual INSERT ... ON CONFLICT query inside a loop.
-
Dependency inserts (line ~183): Each dependency catalog is inserted with an individual INSERT ... ON CONFLICT inside a loop.
-
Data object inserts (line ~199): Each data object is inserted individually inside a loop.
-
Reverse dependency inserts (line ~215): Same pattern.
Proposed fix
- Load
_schema_modules table once into a map at the start, instead of querying per-module in the loop.
- Batch all INSERT operations using multi-row INSERT statements (similar to how
batchInsertModuleTypeCatalogs already works).
- Consider combining related queries into fewer roundtrips.
Context
This was noticed during work on module catalog-agnostic types. The current code works correctly but is inefficient for catalogs with many modules/dependencies/data objects.
Problem
reconcileMetadatainpkg/catalog/db/reconcile.gohas multiple N+1 query patterns where individual DB queries are executed inside loops:collectFieldModuleCatalogs(line ~130): When a module is not yet inmoduleMap, it does a per-moduleSELECT query_root, mutation_root, function_root, mut_function_root FROM _schema_modules WHERE name = $1inside a loop. If there are M modules, this is M extra queries.Module upserts (line ~153): Each module record is inserted/updated with an individual
INSERT ... ON CONFLICTquery inside a loop.Dependency inserts (line ~183): Each dependency catalog is inserted with an individual
INSERT ... ON CONFLICTinside a loop.Data object inserts (line ~199): Each data object is inserted individually inside a loop.
Reverse dependency inserts (line ~215): Same pattern.
Proposed fix
_schema_modulestable once into a map at the start, instead of querying per-module in the loop.batchInsertModuleTypeCatalogsalready works).Context
This was noticed during work on module catalog-agnostic types. The current code works correctly but is inefficient for catalogs with many modules/dependencies/data objects.