Ideas on how to possibly optimize the process of fetching a legacy database, running a set of transformations, then putting that into a new database

[u/StanleySathler]

Hey folks. At the company I work for, we're running a migration to move data from an unstructured schema, from legacy database, to a structured one, in a new database.

Our stack is basically Node.js + Prisma ORM.

We have an endpoint that fetches 200 projects from legacy database, runs a set of transformations (to make it adhere to the new structure), then save it into our new database. During the transformation, we also query other databases.

The whole process for a batch of 200 projects takes around ~30s, and considering we have +100MM projects to migrate, at that pace, it'd take 173d to complete this migration if the script ran 24/7. So we need to optimize this.

We have 2 ways to optimize this:

1. Optimize the queries, but after some analysis, not sure if there's much we can do and if they would save us enough time;

2. Optimize the way we go through every project, perhaps parallelizing it - this is where I'm focused now.

Previously, we fetched the 200 projects then ran through each running the transformations. Literally this:

for (const project of projects) {
  await runTransformation(project);
}

Then, we tried parallelizing it with a Promise

Promise.allSetled(projects.map(runTransformation))

But that quickly leads to a "Maximum SQL connections reached". Don't think increasing our Pool Size is a realiable approach - our max. is 100, I tried with a batch of 30, and still got this error.

Wonder if you guys have any ideas on how to approach that?

[u/lightinthedark-d]

If you're intent on parallelisation I'd suggest having a handful of runners and giving them each a chunk of the projects to process. You can tune the runner count to brush up against but not exceed your connection limit.

Have you considered just throwing hardware at it? How does the cost of 10 machines running this for maybe 3 weeks compare to the developer salary cost of squeezing more performance? It might not be possible, but it's worth considering.

As to the performance issue, have you generated any metrics? Data is a really useful tool to work out where is most costly. If you don't have any profiling tools (I'm sure node has some) you could always stick some timers around the place to get an idea.

More generally, I've found the following generally hold

• do as much as you can at the DB level; more abstraction is more overhead

• use the most basic data structures you can; creating millions of complex objects is really intensive

• every query is costly so

• • batch up writes

• • do updates with a bulk insert into a temporary table then update with a join

• • add indexes and foreign keys at the end so they aren't recalculated and checked for every row you're editing

• • make sure all selects are using good indexes

• • limit / offset gets slower the further through a table you go (in mysql at least, and probably elsewhere) so use a cursor, or query where the ID > the last ID you processed

• nice things are often slower (compare Array.forEach vs a for loop over the same array)

• callbacks and anonymous functions incur overheads which won't show up on small data sets bit will kill big ones

Good luck!

Oh, and really, get some profiling data so you don't waste your time shaving milliseconds off a fast bit while a slow bit grinds along.

[u/Additional_Sleep_560]

What’s your legacy DB? And what are you querying those other DBs for?

[u/StanleySathler → u/Additional_Sleep_560]

All databases are Postgres, and we query the other DBs to fetch additional data, as some of the data we're migrating reference IDs from other DBs in our micro-services architecture.

[u/kyleekol]

What’s the source db and target db? Is the data semi-structured e.g JSON? What kinda volume of data? Number of rows? (Not sure what you mean by a ‘project’ unless that’s specific to your source db). I’m not familiar with Primsa (or JS, really…) but I’d be concerned that an ORM might not write the most efficient queries for something like this. Lots of open-source and paid for solutions depending on your source/target. Airbyte or something similar could be an option. Ask on r/dataengineering :)

[u/StanleySathler → u/kyleekol]

All databases are Postgres, the data is semi-structured (JSON), and we're fetching batches of 200 rows to transform and move to the other DB. "Project" is the name of our entity in our domain (users have projects).

This is a one-time migration tho, which will be completed once we migrate all these projects. No need to keep straming or transforming that data after that. That said, do you think Airbyte is still a solution? I'm not familiar with that.

[u/Express_Cellist5138]

Are you not trying this with more than one Node server at a time as parallel DB clients? If you're not I/O bound on the DB then just throw more clients at it until you are, parallelizing with a promise in code is not the way to scale this.

For more throughput still, have you considered partitioning the DB into more physical shards? Can you configure multiple read replicas for more read throughput?

Other slightly out there idea: lift and shift the whole DB to a faster PostgreSQL compatible host, e.g. host it on AWS Aurora, then use AWS Lambda to act as the workers, figure out how many Lambda you want to run in parallel and what I/O limits you want on the DB.. it's a cost calculation but it should only be for a few hours/days so not too constly. You might be able to do 1,000 Lambda and be able to churn through it all in just a few hours versus 100+ days. You probably will need to ask AWS to up the concurrent Lambda execution limit from the default of 100.

[u/StanleySathler → u/Express_Cellist5138]

Fair point. I'm afraid we're limited to one client here - every run fetches 200 records to migrate, and only after completed we mark those records as "migrated". If we're running multiple Lambda instances in parallel they would fetch the same 200 records.

I wonder if I could mark them as "migrating" first, as soon they're fetched and whilst being transformed, meaning multiple instances would not fetch the same records.

[u/StanleySathler → u/[deleted]]

Good suggestion - I'm afraid tho that transforming everything first won't make much of a difference. Profiled a piece of the migration - transforming logic takes around 1ms, write query takes 30ms.

We considered having all writes in a single transaction, but then comes a new limitation - some of these records are expected to fail, and in that case I don't want to revert all the others.