ANBOB™

Oracle database

MySQL database

In MySQL, the maximum size for a BLOB or TEXT field, which can be considered as large objects, depends on the storage engine used. For the InnoDB storage engine

• A BLOB can be 65535 bytes (64 KB) maximum.
• a MEDIUMBLOB for 16777215 bytes (16 MB)
• a LONGBLOB for 4294967295 bytes (4 GB).

BLOB ≈ 64KB, MEDIUMBLOB ≈ 16MB and LONGBLOB ≈ 4GB

• The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.
• The maximum row size, excluding any variable-length columns that are stored off-page, is slightly less than half of a page for 4KB, 8KB, 16KB, and 32KB page sizes. For example, the maximum row size for the default innodb_page_size of 16KB is about 8000 bytes. However, for an InnoDB page size of 64KB, the maximum row size is approximately 16000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row size, including BLOB and TEXT columns, must be less than 4GB.

PostgreSQL database

In Postgres, the simplest representation of how LOBs are handled is shown below, where BLOBs are equivalent to the BYTEA data type and CLOBs are equivalent to the TEXT data type

Large Objects can also be handled in a more conventional manner using data types CLOB and BLOB.  In Postgres, these data types are stored in a single system table called ‘pg_largeobject’ which has to be accessed via identifiers of data type OID which are stored with the table using BLOB/CLOB data.

The catalog pg_largeobject holds the data making up “large objects”. A large object is identified by an OID assigned when it is created. Each large object is broken into segments or “pages” small enough to be conveniently stored as rows in pg_largeobject.

pg_largeobject

Large Objects limits in Postgres

• 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_largeobject is an oid which is a 32-bit quantity).
• pg_largeobject cannot be partitioned (reason: because it’s a system catalog)
• A large object cannot exceed 4TB for PostgreSQL 9.3 or newer or 2GB for older versions.

IMPORTANT NOTES:

• Since PostgreSQL considers a LO entry as an object, deleting or updating rows in the user table does not delete entries in pg_largeobjects. pg_largeobjects therefore grows infinitely unless a separate purging process is made.
• To prevent this, typically a trigger needs to be added which deletes entries in pg_largeobjects
• Since large objects are created independently from the table columns that reference the objects (unlike TOASTed objects), when you delete a row from the table that points to a large object, the large object is not deleted.  Therefore, the management of those deleted objects should be built into your design (a trigger is one option).
• You can use the pg_total_relation_size function to check the size of a large object. It will return the total disk space used by the table, including all its indexes, toast tables, and free space map.

Summary  of Large Objects using BYTEA/TEXT vs. BLOB/CLOB

Now that we have a basic understanding of how large objects can be handled in Postgres, here is a brief but important summary of the different mechanisms for Large Objects (below):

	BLOB/CLOB	BYTEA/TEXT
“TOAST”-able?
Large Object API NOT required
Transaction NOT required
Can stream, and seek over entries
Need to track OID.
Object Storage Limit	*4 GB	1 GB