Faster, Safer Version Upgrades for Databases with Large Objects

By Varun Dhawan, Ilan Benschikovski, and Alexander Kukushkin – Azure PostgreSQL, Microsoft

Some PostgreSQL applications store documents, images, PDFs, and other files as large objects (LOBs). While this works well under normal conditions, a high number of LOBs can slow down, complicate, or even lead to failures during a major version upgrade.

This update is designed to make upgrades safer and more predictable for users of Azure Database for PostgreSQL flexible server.

When performing a major version upgrade, PostgreSQL employs pg_upgrade, which internally uses pg_dump to transfer schema and metadata to the new version.

With databases containing millions of LOBs, the previous upgrade method processed large-object metadata one piece at a time, causing excessive memory and temporary space usage during the schema dump.

This update alters that upgrade procedure. Rather than processing large-object metadata individually, PostgreSQL can now transfer it in bulk. This enhancement solely affects how metadata is handled, not the actual large-object data.

This enhancement builds on advancements made in upstream PostgreSQL, optimising how large-object metadata is managed during upgrades. We’ve incorporated these benefits into Azure Database for PostgreSQL flexible server for any upgrades to PostgreSQL 15 and higher, so users with large-object-heavy workloads can take advantage without delay.

We conducted tests upgrading from PostgreSQL 13, with large-object counts between 10 million to 500 million. The outdated method is illustrated as PostgreSQL 13 → 14, while the improved method is represented by PostgreSQL 13 → 15.

Note: These results were based on testing across multiple databases with large objects distributed over 100 databases. Since pg_dump operates per database, single-database scenarios with the same total LOB count may yield different execution times.

Cap

Illustration: A bar chart showcasing the time taken for major version upgrades with the older and improved large-object methods. Time decreases significantly at higher LOB counts.





Illustration: A bar chart that highlights how the improved pathway is significantly faster at various large-object counts.

Main takeaway: This improvement is about more than just speed. For larger LOB counts, it shifts the outcome from upgrade failures to successful completions.

Illustration: A line chart illustrating runtime for the enhanced upgrade process, showing successful upgrades from 10 million to 500 million large objects.

If your database houses large binary content as PostgreSQL large objects, this concerns you.

Carry out these checks in every database you plan to upgrade.

-- Count PostgreSQL large objects in the current database
SELECT
  current_database() AS database_name,
  count(*) AS large_object_count
FROM pg_largeobject_metadata;

-- Estimate size of large-object data and metadata
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;

-- Review the shape of large-object metadata
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;

-- Find 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;