首页 » MySQL, ORACLE 9i-23ai, PostgreSQL/GaussDB » Oracle MySQL PostgreSQL BLOB & CLOB maximum size Limit

Oracle MySQL PostgreSQL BLOB & CLOB maximum size Limit

The maximum size of large objects (LOBs) in Oracle, PostgreSQL, and MySQL varies depending on the type of LOB and the database system. Here are the details for each database:

Oracle database

Datatypes Limit Comments
BFILE Maximum size: 4 GB

Maximum size of a file name: 255 characters

Maximum size of a directory name: 128 bytes

Maximum number of open BFILEs: see Comments

The maximum number of BFILEs is limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.
BLOB Maximum size: (4 GB – 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Foot 1).
CHAR Maximum size: 2000 bytes None
CLOB Maximum size: (4 GB – 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Foot 1).

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

  • BLOB can be 65535 bytes (64 KB) maximum.
  • MEDIUMBLOB for 16777215 bytes (16 MB)
  • 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

Name                Type                   References                                                    Description                                                                                        
loid oid pg_largeobject_metadata.oid Identifier of the large object that includes this page
pageno int4 Page number of this page within its large object
data bytea Actual data stored in the large object.

 

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?  Screen Shot 2018-11-13 at 4.04.35 PM.png
Large Object API NOT required  Screen Shot 2018-11-13 at 4.04.35 PM.png
Transaction NOT required  Screen Shot 2018-11-13 at 4.04.35 PM.png
Can stream, and seek over entries  Screen Shot 2018-11-13 at 4.04.35 PM.png
Need to track OID.  Screen Shot 2018-11-13 at 4.04.35 PM.png
Object Storage Limit *4 GB 1 GB

 

 

References
https://dev.mysql.com/doc/refman/8.4/en/storage-requirements.html#id899830
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/datatype-limits.html
https://www.enterprisedb.com/postgres-tutorials/postgresql-toast-and-working-blobsclobs-explained#:~:text=No%20more%20than%2032TB%20of,a%2032%2Dbit%20quantity).

打赏

目前这篇文章还没有评论(Rss)

我要评论