Reducing a PostgreSQL Large Object Migration from 36 Hours to 30 Minutes
Introduction
Database migrations are usually associated with tables, indexes, sequences, and schemas. However, one component often remains unnoticed until migration day arrives: PostgreSQL Large Objects (LOBs).
In environments that have evolved over many years, applications frequently store documents, images, PDFs, and other binary content as Large Objects. While this approach works well operationally, it can become a major challenge during migrations.
Recently, I faced a scenario involving tens of gigabytes of Large Objects that needed to be migrated between PostgreSQL servers within a limited maintenance window. Traditional approaches were simply too slow, forcing me to explore alternative methods.
The result was a lightweight solution based entirely on native PostgreSQL functionality, parallelism, and Unix pipes that dramatically reduced migration times.
Why Large Objects Can Become a Bottleneck
Unlike regular table data, PostgreSQL Large Objects are stored internally in the pg_largeobject system catalog.
This introduces several challenges during migrations:
- Large Objects can represent significant data volumes.
- Migration processes are often more sequential in nature.
- Many migration tools focus primarily on table data.
- Exporting and importing files introduces additional I/O overhead.
- Maintenance windows may not be large enough to accommodate lengthy transfers.
As databases grow, Large Objects can quickly become the slowest component of the entire migration process.
The Challenge
The environment presented several constraints:
- Tens of thousands of Large Objects.
- Multi-gigabyte binary content.
- Limited maintenance windows.
- Need to minimize disk I/O.
- Desire to avoid complex third-party tooling.
The key question became:
Can Large Objects be streamed directly from one PostgreSQL server to another, while taking advantage of parallel execution?
The Idea
The approach relied on four simple concepts.
1. Read Directly from pg_largeobject
Instead of exporting objects to temporary files, read content directly from PostgreSQL's internal storage structures.
This removes unnecessary intermediate steps and reduces disk activity.
2. Distribute the Workload
Each Large Object has a unique Object Identifier (loid).
Using a simple modulo operation:
mod(loid::bigint, N)
it becomes possible to divide the workload evenly across multiple worker processes.
3. Execute in Parallel
Rather than processing all objects sequentially, multiple independent sessions can migrate different subsets simultaneously.
This allows the migration to leverage available CPU, network bandwidth, and server resources far more efficiently.
4. Stream Through Pipes
Instead of writing data to local storage, the output generated on the source server is streamed directly to the destination server using operating system pipes.
The resulting workflow is remarkably simple:
Source PostgreSQL
↓
Parallel Processing
↓
Unix Pipes
↓
Target PostgreSQL
No intermediate files.
No additional storage requirements.
No unnecessary I/O operations.
Results
Testing was performed using:
- PostgreSQL Flexible Server as source.
- PostgreSQL Flexible Server as target.
- Linux VM located in the same Azure region.
- Accelerated networking enabled.
- Standard 4-vCPU environments.
Some metrics.
| Metric | Result |
|---|---|
| Data Volume Tested | 500 MB |
| Parallel Workers | 16 |
| Migration Time | ~11 seconds |
| CPU Consumption | ~15% |
| Estimated Time for 80 GB | ~30 minutes |
While actual results will vary depending on infrastructure, storage performance, network bandwidth, and workload characteristics, the improvement compared to traditional approaches was substantial.
Lessons Learned
Several important lessons emerged during testing.
Validate Distribution First
Before launching multiple workers, verify that Large Object identifiers are sufficiently distributed.
Poor distribution can reduce the effectiveness of parallel execution.
More Threads Are Not Always Better
Parallelism introduces overhead.
The optimal number of workers depends on CPU availability, network latency, and destination server capacity.
Benchmarking different parallelization levels is highly recommended.
Watch the Destination Server
In many migrations, the bottleneck eventually moves from the source system to the target environment.
Continuous monitoring helps identify the optimal balance.
Consider Resume Logic
For very large migrations, implementing checkpoint or resume capabilities can significantly improve operational resilience.
When This Approach Makes Sense
This technique can be particularly useful for:
- PostgreSQL-to-PostgreSQL migrations.
- Cloud migration projects.
- Environment consolidations.
- Database modernization initiatives.
- Large binary repositories.
- Scenarios where storage is limited.
- Strict maintenance windows.
Final Thoughts
Large Objects remain one of the least discussed aspects of PostgreSQL migrations, yet they are often responsible for a disproportionate amount of migration time.
By combining direct access to pg_largeobject, workload partitioning, parallel execution, and streaming through pipes, it is possible to achieve significant performance gains while keeping the solution simple and lightweight.
Sometimes the difference between a migration that requires days and one that completes within a maintenance window is not additional hardware, but a different approach.
If you would like to dive into the technical implementation, scripts, and detailed performance testing, check out my original Microsoft TechCommunity article:
PostgreSQL: Migrating Large Objects (LOBs) with Parallelism and PIPES
Read the full technical article: https://techcommunity.microsoft.com/blog/adforpostgresql/postgresql-migrating-large-objects-lobs-with-parallelism-and-pipes/4467991
