PostgreSQL Large Object Facility Limits Explained

postgresql

I've not found an equivalent of the about PostgreSQL page that covers the limitations of the large object facility in particular, but reading between the lines I think the following is true:

  1. No more than 32TB of large objects can be stored (reason: they are all stored in a single table named pg_largeobject, and the per-table size limit is 32TB (assuming default page size)).
  2. No more than 2^32 large objects can be stored (reason: the primary key of pg_largeobject is an oid which is a 32-bit quantity).
  3. pg_largeobject cannot be partitioned (reason: because it's a system catalog?)

Is this all correct?

Best Answer

These 3 points are correct. We might add

  1. A large object cannot exceed 4TB for PostgreSQL 9.3 or newer, or 2GB for older versions. This is based on the release notes:

E.11.3.5. Data Types

Increase the maximum size of large objects from 2GB to 4TB (Nozomi Anzai, Yugo Nagata)

Also, the binary contents are sliced into tiny chunks of 2000 bytes in pg_largeobject. There is one row per chunk, so when importing large contents (large by today's standards), the number of rows in this table tend to grow quickly. Although that does not imply a hard limit, users should be aware of that for performance reasons.

Related Question