Document strategies for dealing with a large number of migrations

[jirikanda]

This is just a feedback of daily EF Core use. No action required.

Situation

We have a project with 450+ tables. Database structure is maintained by Entity Framework Core 2.x and Migrations. Currently, there is 350 migrations. Part of *.designer.cs files is each 2MB large. The whole Migrations folder contains files with size 640 MB.

Observation

During build, csc.exe or vbcscompiler.exe consumes up to 12GB RAM. We consider this as high usage.
We created csc.exe memory dump. The analysis revealed only Roslyn data structures (like MemberAccessExpressionSyntax, InvocationExpresionSyntax, ExpressionStatementSyntax), nothing shows a problem, just a Roslyn feature of compiling so many so large files.

Workaround

We process migration "squash" once a while - we delete all migrations and create a new one initial migration. This is done manually, and it is very annoying.
We also need handle the situation on productions sites with already applied migrations, no problem here.

Suggestion

We would appreciate cleaning or deleting *.Designer.cs files of previous migrations when creating a new migration. But we found some minor issue in generating SQL statements when cleaned designer files manually.

Note

We do not wish to use bounded contexts.

[ajcvickers]

@jirikanda Thanks for the feedback. The main way we're planning to address this is by adding tooling support for squashing migrations--issue dotnet/efcore#2174. In addition, we are thinking about ways to reduce the amount of information that is stored in the snapshots.

I'm going to move this issue to the docs repo because we do think it would be useful to have some guidance around handling situations where the migrations build-up. For example, some people squash all the migrations for each release and just keep the find-grained migrations for the main development branch.

[leyu-wfh]

For the purpose of reducing number of Designer files, is it possible to use the InitialCreate.Designer.cs file + intermediate migration files to re-create all the intermediate Designer file?
Or make the Designer files to be incremental, instead of storing the entire schema?

[archfz]

What is the scope of Designer files? Other frameworks don't have this. Why would we need to make a snapshot every time, weren't migrations in first place made for preventing such things?

[Mano235]

@jirikanda ,Im facing the same issue and the designer file size is causing the issue,Could you pls suggest the work around if you resolved ?

[jirigregor]

@Mano235 we solve this manually by "shrinking" migrations. We do this every 2-3 months when the size of the solution slows down the performance of VS / build / deployment. What are we doing? When we first did this, we created an empty migration called MigrationShrink. We then ran the "script migration" and pasted the scripted migrations into a separate .sql file, while the "MigrationShrink" migration contains a link to this script. Then we excluded all "regular" migrations from the .sln file.
So, for example, we have
Initial migration
Migration 1
Migration 2
...
MigrationShrink.
We have deployed the application in many instances, and we have manually inserted the "MigrationShrink" migration into the migration history table on each of these instances, so it does not run on the existing instance. But it will run on new instances.
Then we are in a situation at some point
Initial migration
Migration 1
Migration 2
...
MigrationShrink
Migration 110
Migration111
...
Migration 150
and we want to make another shrink. We only run the "script migration" command and update the MigrationShrink sql file. So you don't have to do anything for each existing instance, because it contains all the "normal" migrations as well as the records from this first MigrationShrink. Each new instance performs all migrations, including MigrationShrink.
And that's it, we currently have + -700 migration files, each 2 MB. But only a fraction of them are part of the solution at once.

[Mano235]

Thanks for the suggestion!! @jirigregor