patternsqlMinor
What are the limits of Postgresql's large object facility?
Viewed 0 times
postgresqlthewhatfacilityarelimitslargeobject
Problem
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:
Is this all correct?
- 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)).
- No more than 2^32 large objects can be stored (reason: the primary key of
pg_largeobjectis anoidwhich is a 32-bit quantity).
pg_largeobjectcannot be partitioned (reason: because it's a system catalog?)
Is this all correct?
Solution
These 3 points are correct. We might add
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
Although that does not imply a hard limit, users should be aware of that for performance reasons.
- 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.
Context
StackExchange Database Administrators Q#127270, answer score: 6
Revisions (0)
No revisions yet.