New maximum size limits for database objects (Preview)

Attention

This behavior change is in the 2025_02 bundle.

For the current status of the bundle, refer to Bundle History.

When this behavior change bundle is enabled, the storage of database objects changes as follows:

Before the change:

For columns of the following types, the maximum allowed length is 16 MB:

  • VARCHAR

  • VARIANT

  • ARRAY

  • OBJECT

For columns of the following types, the maximum allowed length is 8 MB:

  • BINARY

  • GEOGRAPHY

  • GEOMETRY

Statements that attempt to store values larger than these maximum allowed lengths fail.

After the change:

For columns of the following types, the maximum allowed length is 128 MB:

  • VARCHAR

  • VARIANT

  • ARRAY

  • OBJECT

For columns of the following types, the maximum allowed length is 64 MB:

  • BINARY

  • GEOGRAPHY

  • GEOMETRY

Statements that attempt to store values up to these new maximum allowed lengths succeed. Statements that attempt to store values larger than these maximum allowed lengths fail.

For tables created after the change, the default length for columns of type VARIANT, ARRAY, and OBJECT is 128 MB, and the default length for columns of type GEOGRAPHY and GEOMETRY is 64 MB.

However, the default length for columns of type VARCHAR and BINARY is 16 MB and 8 MB, respectively. You can increase the length by explicitly specifying the length when creating a table. If a table has a new limit and stores objects exceeding 16 MB, any downstream tables created using CREATE TABLE AS SELECT (CTAS) from that table will fail. To prevent this failure, adjust the CTAS statement, and explicitly set the size of the corresponding VARCHAR column to 134217728 (67108864 for BINARY).

Sizes greater than 16 MB are visible in query results

Sizes greater than 16 MB for VARCHAR and 8 MB for BINARY are visible in query results. For example, the sizes are visible in queries that call the SYSTEM$TYPEOF function or queries of views that provide information about functions and procedures (for example, the INFORMATION_SCHEMA FUNCTIONS view).

The following example concatenates two columns that are 16 MB in size:

CREATE OR REPLACE TABLE test_larger_sizes(col1 VARCHAR, col2 VARCHAR) AS
  SELECT 'foo', 'bar';

SELECT SYSTEM$TYPEOF(CONCAT(col1, col2)) FROM test_larger_sizes;
Copy
+-----------------------------------+
| SYSTEM$TYPEOF(CONCAT(COL1, COL2)) |
|-----------------------------------|
| VARCHAR(33554432)[LOB]            |
+-----------------------------------+

For functions and procedures, the new sizes are shown in the INFORMATION_SCHEMA FUNCTIONS view:

CREATE OR REPLACE FUNCTION test_larger_sized_func(in_arg VARCHAR)
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
  CALLED ON NULL INPUT AS
$$
  RETURN NULL;
$$
;

SELECT data_type FROM INFORMATION_SCHEMA.FUNCTIONS
  WHERE function_name = 'TEST_LARGER_SIZED_FUNC';
Copy
+--------------------+
| DATA_TYPE          |
|--------------------|
| VARCHAR(134217728) |
+--------------------+

Sizes greater than 16 MB are visible in error messages

The error message might change for some queries.

The following is an example of an insert for a VARCHAR column that returns an error:

CREATE OR REPLACE TABLE test_larger_size_error(col VARCHAR);
INSERT INTO test_larger_size_error SELECT RANDSTR(20000000, 1);
Copy

The following error message is returned before the change:

100082 (22000): Max LOB size (16777216) exceeded, actual size of parsed column is 20000000

The following error message is returned after the change:

100078 (22000): String
'CaFHJdoX3upWliCCdAPXXgytQuXzQpFO4laQEFdmiE1NDOywjwHoBqSNTCzTW66ynuR7EsI4ZxStCh3VMIBMYeHWgv1gUZRmHEK4kGmZcC02jGQhnnFJ0jtcIEWBIN6vKGkvSwG482IvfgVVwF3FTj7sb86t1SK9qigI6ujlSNByytIYBk0lkI1MM0zpRFeH2BNvGxtI.'
is too long and would be truncated

Supported drivers

You might need to update your driver version to the one that supports larger database objects. Otherwise, an error similar to the following might be returned:

100067 (54000): The data length in result column <column_name> is not supported by this version of the client.
Actual length <actual_size> exceeds supported length of 16777216.

Iceberg support

For unmanaged Iceberg tables, the default length for VARCHAR and BINARY columns is 128 MB. This limit applies to newly created or refreshed tables. Tables created before the new size limit was enabled and not refreshed still have the old size limit.

For managed Iceberg tables, the default length for VARCHAR and BINARY columns is 128 MB. Tables created before the new size limit was enabled still have the old size limit. To apply the new size to these tables, recreate the tables or alter the columns. The following example alters a column to use the new size limit:

ALTER ICEBERG TABLE my_iceberg_table
  ALTER COLUMN col1 SET DATA TYPE VARCHAR(134217728);
Copy

Ref: 1942

Language: English