prisma: Migrations with CockroachDB: figure out source and impact of slowness

So I’ve been running prisma migrate reset --skip-seed --force to test my local environment.

With Begin/End on my single migration (we are moving to CockroachDB) I can cut the time to 30s, without this it’s 60-80s.

Nothing obvious jumped out in the debug lines. And even 30s is too long, but this is one step we can take to speed things up.

I can get this to 2s if I do my own DB resets in advance (e.g. Drop tables manually in tableplus).

However, any other tools like db push or migrate dev seem to run very slow.

This is using CRDB in docker v23.1 with both prisma 4.7.1 and 5 (we use 4.7.1 in production).

For our tests, we use db push to setup our DBs and right now it’s adding about 11-15s to do that.

The tool was initially just not created with that use case in mind, so we prioritized running each migration one by one. This feedback will help us to potentially reconsider that 👍

We have a few tracking issues for slowness in schema changes on our side. We have made progress on a few of them for the v22.1 release. The worst case is when adding and dropping things repeatedly, which is common in tests.

I’m definitely curious to see the extra details about when the slowness happens. With that, hopefully we can drill further, and potentially link to relevant issues on our side.

We lose probably a couple hours of dev time on the team each week to this issue. I’ve tried a variety of hacks (running single-node, in-memory, with fsync disabled) and in local development it still takes ~60 seconds to run each migration, even if it’s a noop. When we’re iterating on a schema migration, this drags down velocity and morale.

It’s particularly confusing since it’s super fast with postgres. And CRDB can apply the migration steps super fast, there’s just something about the combination of CRDB + prisma that’s surprisingly slow.

@ajwerner I’m working with @jhaynie

group all of the creates and alter statements for new tables into a transaction.

I did this and it made a significant difference. prisma migrate dev --create-only then putting BEGIN; at the beginning and COMMIT; at the end and running prisma migrate deploy brought it down to 30s locally. 🎉

I’m on an M1 Pro, running cockroachdb v22.1.0 in a docker container. I think the remaining slowness is due to the architecture translation or whatever, because the same test using this unofficial arm64v8 container brings it all the way down to 5 seconds. So maybe please release that arm container soon 🙏

Is there any drawback to doing this all in one transaction? Wondering why not just make prisma do that by default 🤔

70 models but each model has between 2-5 relations. i was referring to prisma migrate dev --create-only. But prisma db push takes less time but still a long time (not as long as migrate).

We are running into this issue. Takes about 45m - 1hr on a 16" MacBook Pro M1 to insert into initial schema. Takes about ~2-3m with create only. Same when we run it against the cloud instance as well (thought maybe it was an ARM translation issue). If we run any of the individual statements they are relatively fast (~5-10ms). Running with DEBUG=* doesn’t seem to produce anything useful that i can tell although happy to try and attach if that would help… Any other ideas how to provide better feedback on the issue?

One note for future folks who stumble upon this issue, if you’re experiencing extreme slowness, and are running the database in a multi-region topology, have a look at https://dev.to/chriscasano/make-table-and-index-creation-faster-in-a-multi-region-cockroachdb-environment-1gm1. It can make a world of difference. If you’re still finding it to be slow, please share more details, we, the folks who work on schema changes in cockroachdb, would be eager to understand what is contributing to any sort of bad experience. Also, as @rafiss noted, we’ve done some things in v22.1 to make schema migrations more efficient.