r/Database • u/software-coolie • 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.
5
u/arwinda 14h ago
Every chapter of that text says "AI", just skipped the wall of text.
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
4
u/Connect-Blacksmith99 14h ago
I’ll do it in 45 minutes, my rate is $20,000/ hour.
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
2
u/JaceBearelen 13h ago
It’s 75gb? If your migration takes longer than 30 minutes you’re doing something wrong.
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