dosubot[bot] commented on issue #8676: URL: https://github.com/apache/incubator-devlake/issues/8676#issuecomment-3717745386
<!-- Answer --> The main reason for the excessive migration time on PostgreSQL is redundant SELECT queries issued after every schema change. Both the migration helper (`AutoMigrateTables`) and the DAL layer (`Dalgorm`) run extra SELECTs to work around PostgreSQL's "cached plan must not change result type" error, but this results in compounded overhead when running many migrations, especially on a fresh database. These queries are harmless but unnecessary in most cases and are not present in MySQL migrations, which explains the performance gap [see code](https://github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/helpers/migrationhelper/migrationhelper.go#L20-L163) [see code](https://github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/impls/dalgorm/dalgorm.go#L176-L548). A good solution is to refactor the migration logic so that only one layer (preferably the DAL) handles the workaround, and only for PostgreSQL. You can skip the extra `db.First` in `AutoMigrateTables` if the DAL already performs it, or make the workaround more surgical—run the SELECT only if a cached-plan error actually occurs. This should significantly reduce migration time on PostgreSQL without affecting correctness. If you pursue a PR, benchmark migration times before and after, and validate schema correctness on both PostgreSQL and MySQL. This approach should bring migration performance closer to parity across both databases. <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/237bd718-8cb8-4fa1-aff0-ced7a54a341c?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/237bd718-8cb8-4fa1-aff0-ced7a54a341c?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/237bd718-8cb8-4fa1-aff0-ced7a54a341c?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/237bd718-8cb8-4fa1-aff0-ced7a54a341c?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/237bd718-8cb8-4fa1-aff0-ced7a54a341c?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/237bd718-8cb8-4fa1-aff0-ced7a54a341c?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/237bd718-8cb8-4fa1-aff0-ced7a54a341c?feedback_type=other)</sup> [](https://app.dosu.dev/b4e8e847-d479-4541-83a8-d88d83fea5c9/ask?utm_source=github) [](https://cursor.com/link/prompt?text=The%20main%20reason%20for%20the%20excessive%20migration%20time%20on%20PostgreSQL%20is%20redundant%20SELECT%20queries%20issued%20after%20every%20schema%20change.%20Both%20the%20migration%20helper%20%28%60AutoMigrateTables%60%29%20and%20the%20DAL%20layer%20%28%60Dalgorm%60%29%20run%20extra%20SELECTs%20to%20work%20around%20PostgreSQL%27s%20%22cached%20plan%20must%20not%20change%20result%20type%22%20error%2C%20but%20this%20results%20in%20compounded%20overhead%20when%20running%20many%20migrations%2C%20especially%20on%20a%20fresh%20database.%20These%20queries%20are%20harmless%20but%20unnecessary%20in%20most%20cases%20and%20are%20not%20present%20in%20MySQL%20migrations%2C%20which%20explains%20the%20performance%20gap%C2%A0%5Bsee%20code%5D%28https%3A//githu b.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/helpers/migrationhelper/migrationhelper.go%23L20-L163%29%C2%A0%5Bsee%20code%5D%28https%3A//github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/impls/dalgorm/dalgorm.go%23L176-L548%29.%0A%0AA%20good%20solution%20is%20to%20refactor%20the%20migration%20logic%20so%20that%20only%20one%20layer%20%28preferably%20the%20DAL%29%20handles%20the%20workaround%2C%20and%20only%20for%20PostgreSQL.%20You%20can%20skip%20the%20extra%20%60db.First%60%20in%20%60AutoMigrateTables%60%20if%20the%20DAL%20already%20performs%20it%2C%20or%20make%20the%20workaround%20more%20surgical%E2%80%94run%20the%20SELECT%20only%20if%20a%20cached-plan%20error%20actually%20occurs.%20This%20should%20significantly%20reduce%20migration%20time%20on%20PostgreSQL%20without%20affecting%20correctness.%0A%0AIf%20you%20pursue%20a%20PR%2C%20benchmark%20migration%20times%20before%20and%20after%2C%20and%20validate%20 schema%20correctness%20on%20both%20PostgreSQL%20and%20MySQL.%20This%20approach%20should%20bring%20migration%20performance%20closer%20to%20parity%20across%20both%20databases.) [](https://go.dosu.dev/discord-bot) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/incubator-devlake/issues/8676) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
