Skip to content

Optimize reconcileMetadata: eliminate N+1 DB queries in loops #64

@VGSML

Description

@VGSML

Problem

reconcileMetadata in pkg/catalog/db/reconcile.go has multiple N+1 query patterns where individual DB queries are executed inside loops:

  1. 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.

  2. Module upserts (line ~153): Each module record is inserted/updated with an individual INSERT ... ON CONFLICT query inside a loop.

  3. Dependency inserts (line ~183): Each dependency catalog is inserted with an individual INSERT ... ON CONFLICT inside a loop.

  4. Data object inserts (line ~199): Each data object is inserted individually inside a loop.

  5. 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.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions