Blog Post

Microsoft Blog for PostgreSQL
4 MIN READ

Faster, Safer Version Upgrades for Databases with Large Objects

varun-dhawan's avatar
varun-dhawan
Icon for Microsoft rankMicrosoft
Aug 04, 2026

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

AreaBeforeAfter
Metadata handlingOne operation per large objectBulk metadata transfer
Memory/temp pressureGrew heavily with LOB countMuch flatter and more predictable
High LOB countsRisk of OOM or temp-space failureCompletes more reliably for PostgreSQL 15+ targets
Customer workaroundvacuumlo + scale-up often neededLess 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 objectsOlder pathImproved pathWhat changed
10M48 min16 min3x faster
20M1h 02m18 min3.4x faster
30M2h 41m21 min7.6x faster
50MFailed at higher scale26-30 minNow completes
100MFailed54 minNow completes
500MFailed4h 01mNow 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 patternWhy it matters
Document managementPDFs, contracts, scans, and archived files
Attachment-heavy appsFiles stored inside PostgreSQL instead of external storage
Legacy apps using lo APIsLOBs may have accumulated for years
Image/archive systemsMillions of binary objects can build up quietly
Previous upgrade failuresFailures 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 laterTarget PostgreSQL 15 or later to benefit from improved large-object metadata handling.
Target is PostgreSQL 14 or earlierPrefer PostgreSQL 15+ where possible; very high LOB counts may still hit older-path limitations.
Very large or unusual databaseRestore a copy and rehearse the upgrade before production.
Suspected orphan LOBsConsider vacuumlo only after testing. It can delete valid LOBs if your app uses custom references.
Any major version upgradeKeep 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.

Learn more

Updated Aug 04, 2026
Version 4.0
No CommentsBe the first to comment