By Varun Dhawan, Ilan Benschikovski, and Alexander Kukushkin - Azure PostgreSQL, Microsoft
Faster, Safer Upgrades for Databases with Large Objects
TL;DR: We improved major version upgrades for PostgreSQL databases with very high large-object counts. For upgrades targeting PostgreSQL 15 and later, large-object metadata is now handled more efficiently, reducing memory/temp-space pressure and helping previously risky upgrades complete more reliably.
Why this matters
Some PostgreSQL workloads store documents, images, PDFs, scanned files, or attachments as large objects (LOBs). In normal operations this is fine. But during a major version upgrade, very high LOB counts could make the schema dump step slow, memory-heavy, or fail.
This improvement is about making that upgrade path safer and more predictable for Azure Database for PostgreSQL flexible server customers.
What changed?
During a major version upgrade, PostgreSQL uses pg_upgrade, which internally runs pg_dump to move schema and metadata into the new version.
For databases with millions of large objects, the older upgrade path handled large-object metadata one object at a time. That created high memory and temporary-space pressure during the schema dump phase.
This fix changes the upgrade path. Instead of processing large-object metadata one object at a time, PostgreSQL now transfers that metadata in bulk. The actual large-object data is not changed; only the upgrade metadata handling is improved.
Why this is different
This improvement builds on upstream PostgreSQL work that makes large-object metadata handling more efficient during upgrades. We brought that benefit into Azure Database for PostgreSQL flexible server for supported PostgreSQL 15+ upgrade targets, so customers with large-object-heavy workloads can benefit without waiting for a future PostgreSQL major version.
Before vs after
| Area | Before | After |
|---|---|---|
| Metadata handling | One operation per large object | Bulk metadata transfer |
| Memory/temp pressure | Grew heavily with LOB count | Much flatter and more predictable |
| High LOB counts | Risk of OOM or temp-space failure | Completes more reliably for PostgreSQL 15+ targets |
| Customer workaround | vacuumlo + scale-up often needed | Less reliance on LOB-specific workarounds for PostgreSQL 15+ targets |
In plain English: the upgrade no longer has to carry paperwork for every large object one by one. It moves the metadata in bulk, which makes the upgrade faster, safer, and less likely to fail at very high LOB counts.
The numbers
We tested upgrades from PostgreSQL 13 with large-object counts ranging from 10M to 500M. The older path is represented by PostgreSQL 13 → 14. The improved path is represented by PostgreSQL 13 → 15.
Note: These figures come from a multi-database test where large objects were spread across 100 databases. Because pg_dump runs per database, single-database workloads with the same total large-object count may see different runtimes.
Cap
Illustration: Bar chart comparing major version upgrade runtime for the older and improved large-object paths. At 10 million large objects, runtime drops from 48 to 16 minutes; at 20 million, from 62 to 18 minutes; and at 30 million, from 161 to 21 minutes.Illustration: Bar chart showing upgrade speedup at increasing large-object counts. The improved path is 3× faster at 10 million large objects, 3.4× faster at 20 million, and 7.6× faster at 30 million.
Outcome summary
| Large objects | Older path | Improved path | What changed |
|---|---|---|---|
| 10M | 48 min | 16 min | 3x faster |
| 20M | 1h 02m | 18 min | 3.4x faster |
| 30M | 2h 41m | 21 min | 7.6x faster |
| 50M | Failed at higher scale | 26-30 min | Now completes |
| 100M | Failed | 54 min | Now completes |
| 500M | Failed | 4h 01m | Now completes |
Key takeaway: this is not just faster. At higher LOB counts, the improvement changes the outcome from upgrade fails to upgrade completes.
Illustration: Line chart showing runtime for the improved upgrade path from 10 million to 500 million large objects. Runtime increases from 16 minutes at 10 million to 54 minutes at 100 million and 4 hours 1 minute at 500 million, with all tested upgrades completing successfully.
Who benefits from this?
You should care if your database stores large binary content using PostgreSQL large objects.
| Workload pattern | Why it matters |
|---|---|
| Document management | PDFs, contracts, scans, and archived files |
| Attachment-heavy apps | Files stored inside PostgreSQL instead of external storage |
Legacy apps using lo APIs | LOBs may have accumulated for years |
| Image/archive systems | Millions of binary objects can build up quietly |
| Previous upgrade failures | Failures during schema dump may map to this scenario |
Copy/paste: check your large-object count
Run these checks in each database you plan to upgrade.
1. Count large objects in the current database
-- Count PostgreSQL large objects in the current database
SELECT
current_database() AS database_name,
count(*) AS large_object_count
FROM pg_largeobject_metadata;
2. Check large-object storage footprint
-- Estimate large-object data and metadata size
SELECT
pg_size_pretty(pg_total_relation_size('pg_largeobject'::regclass)) AS large_object_data_size,
pg_size_pretty(pg_total_relation_size('pg_largeobject_metadata'::regclass)) AS large_object_metadata_size;
3. Understand ownership and ACL shape
-- Inspect large-object metadata shape
SELECT
count(*) AS total_large_objects,
count(lomacl) AS large_objects_with_custom_acl,
count(DISTINCT lomowner) AS distinct_large_object_owners
FROM pg_largeobject_metadata;
4. Find top large-object owners
-- Top large-object owners
SELECT
lomowner::regrole AS owner,
count(*) AS large_object_count
FROM pg_largeobject_metadata
GROUP BY lomowner
ORDER BY large_object_count DESC
LIMIT 10;
What should I do before my next major version upgrade?
| If your situation is... | Recommended action |
|---|---|
| Target is PostgreSQL 15 or later | Target PostgreSQL 15 or later to benefit from improved large-object metadata handling. |
| Target is PostgreSQL 14 or earlier | Prefer PostgreSQL 15+ where possible; very high LOB counts may still hit older-path limitations. |
| Very large or unusual database | Restore a copy and rehearse the upgrade before production. |
| Suspected orphan LOBs | Consider vacuumlo only after testing. It can delete valid LOBs if your app uses custom references. |
| Any major version upgrade | Keep healthy free space and leverage pre-upgrade validation checks to validate extension/schema compatibility first. |
Bottom line
If large objects were making your PostgreSQL upgrade risky, this improvement makes the upgrade path safer and more predictable. For large-object-heavy databases, upgrades targeting PostgreSQL 15 and later now show faster runtime, lower memory/temp-space pressure, and successful validation up to 500M large objects.