Description
Entity-driven schema sync (db.get_schema_registry(...) + registry.sync()) spends 30–40 minutes in schema discovery on PostgreSQL 18.
Setup: a single PostgreSQL database shared by 3 applications, each in its own schema with its own database user. Each application runs sea-orm schema sync against its own schema on deploy. All 3 are affected.
The time goes into sea-schema's constraint discovery query (SchemaQueryBuilder::query_table_constraints — the information_schema.table_constraints join with referential_constraints_subquery):
-
each execution takes 60–96 seconds while returning only 9–14 rows (e.g. elapsed=96.377s rows_returned=14 in our slow-statement log),
-
SchemaDiscovery::discover() re-runs it once per table, sequentially, so total sync time ≈ 90 s × table count.
The catalog is small and healthy, so this is not a data-size problem: pg_constraint has 1017 rows total, pg_inherits has 15, and there is no catalog bloat. pg_stat_activity shows wait_event = NULL for the whole duration — the query is burning CPU inside the information_schema views, not waiting on locks.
Two things in the generated query make it structurally expensive in a multi-schema database: the constraint_column_usage join has no schema qualifier (only constraint_name), and the OR'd join condition on position_in_unique_constraint prevents hash/merge joins, forcing a nested loop over the view results.
Steps to Reproduce
-
PostgreSQL 18; a single database containing multiple schemas owned by different users (~1000 rows in pg_constraint overall).
-
Run entity schema sync: db.get_schema_registry("crate::*") then registry.sync(&db), with entities that have FK relations.
-
Observe statement timings (e.g. sqlx slow-statement logging with a 1s threshold).
Expected Behavior
Discovery queries against a ~1000-constraint catalog complete in milliseconds; syncing an already-converged schema takes seconds.
Actual Behavior
Every per-table constraint discovery query takes 60–96 seconds. With a few dozen tables per schema, each deploy spends 30–40 minutes in registry.sync().
Reproduces How Often
Always — every sync, every table, on all 3 applications sharing the database.
Workarounds
Ruled out so far:
- catalog bloat — none (checked);
jit — already off;ANALYZE on the pg_catalog tables — no change;- lock contention —
wait_event stays NULL, so it is pure execution cost.
Regardless of the planner details, a structural fix on the discovery side would remove the problem entirely — e.g. querying pg_catalog directly, or fetching constraints for the whole schema in a single pass instead of per table, and schema-qualifying the constraint_column_usage join.
Versions
sea-orm 2.0.0-rc.41
sea-schema 0.18.0
sea-query 1.0.1
sqlx 0.9
Description
Entity-driven schema sync (
db.get_schema_registry(...)+registry.sync()) spends 30–40 minutes in schema discovery on PostgreSQL 18.Setup: a single PostgreSQL database shared by 3 applications, each in its own schema with its own database user. Each application runs sea-orm schema sync against its own schema on deploy. All 3 are affected.
The time goes into sea-schema's constraint discovery query (
SchemaQueryBuilder::query_table_constraints— theinformation_schema.table_constraintsjoin withreferential_constraints_subquery):each execution takes 60–96 seconds while returning only 9–14 rows (e.g.
elapsed=96.377s rows_returned=14in our slow-statement log),SchemaDiscovery::discover()re-runs it once per table, sequentially, so total sync time ≈ 90 s × table count.The catalog is small and healthy, so this is not a data-size problem:
pg_constrainthas 1017 rows total,pg_inheritshas 15, and there is no catalog bloat.pg_stat_activityshowswait_event = NULLfor the whole duration — the query is burning CPU inside theinformation_schemaviews, not waiting on locks.Two things in the generated query make it structurally expensive in a multi-schema database: the
constraint_column_usagejoin has no schema qualifier (onlyconstraint_name), and the OR'd join condition onposition_in_unique_constraintprevents hash/merge joins, forcing a nested loop over the view results.Steps to Reproduce
PostgreSQL 18; a single database containing multiple schemas owned by different users (~1000 rows in
pg_constraintoverall).Run entity schema sync:
db.get_schema_registry("crate::*")thenregistry.sync(&db), with entities that have FK relations.Observe statement timings (e.g. sqlx slow-statement logging with a 1s threshold).
Expected Behavior
Discovery queries against a ~1000-constraint catalog complete in milliseconds; syncing an already-converged schema takes seconds.
Actual Behavior
Every per-table constraint discovery query takes 60–96 seconds. With a few dozen tables per schema, each deploy spends 30–40 minutes in
registry.sync().Reproduces How Often
Always — every sync, every table, on all 3 applications sharing the database.
Workarounds
Ruled out so far:
jit— alreadyoff;ANALYZEon thepg_catalogtables — no change;wait_eventstays NULL, so it is pure execution cost.Regardless of the planner details, a structural fix on the discovery side would remove the problem entirely — e.g. querying
pg_catalogdirectly, or fetching constraints for the whole schema in a single pass instead of per table, and schema-qualifying theconstraint_column_usagejoin.Versions