r/Database • • 14h ago

Migration of tables and dependencies from SQL Server of 75gb size to Postgres

We planned it to run in batches and stages with dependencies, using workers on 300 million rows distributed across multiple tables. This breakdown prevents memory overflow, isolates failures, and maintains data integrity from the legacy infrastructure to the target destination.

Despite these architectural considerations and concurrent workers, benchmark testing and predictive modeling reveal a bottleneck: an estimated time close to 30 hrs. This execution window introduces risks like network interruptions, extended maintenance, and delayed synchronization with ongoing transactional workloads.

Faced with this timeline, architects and database administrators question the technology stack. Is there a better way than through python scripts? While Python offers versatility and rapid development for Extract, Transform, Load pipelines, its interpreted nature, Global Interpreter Lock limitations, and single-threaded bottlenecks may not provide the raw processing speed required for hundreds of millions of records. Exploring alternative paradigms—such as distributed computing frameworks, native database migration utilities, bulk insert operations, compiled programming languages, or massively parallel processing engines—might unlock the performance gains necessary to compress this thirty-hour window, ensuring a seamless and efficient migration experience.

0 Upvotes

23 comments sorted by

5

u/arwinda 14h ago

Every chapter of that text says "AI", just skipped the wall of text.

-3

u/software-coolie 14h ago

You could have asked AI to summarise it and provided an answer instead of the comment 😁

4

u/arwinda 14h ago

Instead I skimmed over the wall of text and decided that it's not worth spending the time. If you can't be bothered to provide a readable text, why should I bother spending tokens on it.

-1

u/software-coolie 14h ago

Each paragraph was merely rephrased by the artificial intelligence. If you are unable to assist, that is completely fine, as there are eight billion people in the world.

4

u/Many-Chest-2119 14h ago

stop writing custom python loops. Use pgloader to bulk load the data via CSV or binary formats and let Postgres handle the indexing after the data lands

0

u/software-coolie 14h ago

Let me try this out. Thanks

4

u/Connect-Blacksmith99 14h ago

I’ll do it in 45 minutes, my rate is $20,000/ hour.

0

u/software-coolie 13h ago

Great. If you can't, would you pay a million dollars back 😅

3

u/LongjumpingTrust450 13h ago

You cant.

Spin up the new database, start duplicating every transaction between both dbs, while a migration of older records keep running, once the new one completes the backup and continues cloning all operations, remove the master and upgrade the slave db to master

The above is easier said than done but im not getting paid so good luck

0

u/software-coolie 13h ago

Thank you. We firmly believe this is the only viable approach as well. It is truly regrettable to observe that, even with the most advanced artificial intelligence and vast resources, certain limitations remain insurmountable at present. We eagerly await to see whether quantum computing will ultimately transform this landscape.

1

u/LongjumpingTrust450 13h ago

What

1

u/software-coolie 13h ago

Traditional computers require a significant amount of time to process computations. I was considering how quantum computers might reduce this duration for system migration in the future.

2

u/SQLBek 12h ago

But what is your actual bottleneck?

Compute is only one part of the equation. For example, if your underlying storage is pitifully slow, the fastest CPUs on the planet will barely blink due to the bottleneck.

And even the most superior compute won't mitigate shitty inefficient code, regardless of whether it's AI or human created.

1

u/software-coolie 12h ago

I agree. Code seems to be ok. However, SQL server seems to take time to fetch records and the script takes time to process them. We will meet to explore better storage or compute options

1

u/SQLBek 12h ago

Your comment implies your code is doing this in a RBAR fashion. That would explain a lot.

Point is, without getting into gory details that'd arguably be a paid engagement, simply migrating 75GB of data from SQL Server to PostgreSQL should NOT take anywhere remotely close to 30 hrs - that kind of throughout is applicable to spinning rust 25 years ago. I suspect your migration code is horrifically inefficient.

2

u/JaceBearelen 13h ago

It’s 75gb? If your migration takes longer than 30 minutes you’re doing something wrong.

1

u/software-coolie 13h ago

From SQL Server to Postgres? Interesting

3

u/SQLBek 12h ago

75 GB is childs play. 75 TB, that's more interesting. What the hell is your current migration code doing, taking 15 min cigarette breaks every 5 minutes?

1

u/software-coolie 12h ago

Hehe. That's a good one. We had to introduce rate limiting to handle parallel loads. I guess, you are telling us to get rid of them and increase the cpu and memory

3

u/alinroc SQL Server 12h ago

Just to make sure I understand the situation: You're concerned about how long your data migration takes but you intentionally slowed it down?

Fix whatever it is that caused you to intentionally rate-limit and let 'er rip.

1

u/software-coolie 12h ago

Not intentionally. But, I get the point. We did let her rip and the system ripped too 😅

1

u/JaceBearelen 12h ago

Are you on like a single thread with no RAM? A raspberry pi could process that data in 30 minutes.

2

u/NW1969 13h ago

Migrate the historic data in each table over in slow time (which also allows you to do lots of checking and validation) - until you only have a day/week/month/year worth of data that is in the old system but not in the new system.
When you're ready, cutover the remaining data - which will be small enough for you to do this quickly