Database limits in Tanzu Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article covers some frequently asked questions pertaining to the database limits in Tanzu Greenplum.

Resolution

1. What are some of the database limits in Tanzu Greenplum?
 

Dimension	Limit
Maximum size for a database?	unlimited
Maximum size of a table?	unlimited, 128 TB per partition per segment
Maximum size for a row?	1.6 TB (1600 columns * 1 GB)
Maximum size of a field?	1 GB
Maximum BLOB size	1 GB (Use BYTEA datatype, we don't have BLOB)
Maximum number of rows in a table?	281474976710656 (2^48)
Maximum number of columns in a table?	1600
Maximum number of indexes on a table?	unlimited
Maximum number of databases/users	unlimited
Maximum number of tables per database	4200 million
Maximum number of columns per View	1664
Maximum length of column/table/database name	63
Maximum number of columns per index	unlimited
Maximum number of table level constraints per table	unlimited
Maximum active concurrent transactions	unlimited
Maximum data format descriptor size	63 characters
Maximum database, user, base table, view, index, trigger, stored procedure, UDF, UDT, constraint or column name size.	63 characters
Maximum sessions per parsing engine	No concept of parsing engine other than masterDB node. No fixed limit, up to a few hundred.
Maximum columns per primary and secondary index	32

2. What is the maximum number of columns that can be present in the "Select" statement?

The maximum number of columns that can be present in the Select statement is 1664.

3. What is the maximum number of URLs that can be placed in the LOCATION part of external table creation?

Example:

CREATE EXTERNAL TABLE ext3 ( a int )
LOCATION ('gpfdist://mdw:8081/a1','gpfdist://mdw:8081/a2',.....,'gpfdist://mdw:8081/a999',) FORMAT 'TEXT' (DELIMITER '|');

There is no upper limit for the max number of URLs that can be added to the LOCATION clause. The amount of storage attributed to the location clause is restricted and this limit is 32712 (32 KB). If this limit is exceed the following error message is produced:

ERROR:  row is too big: size 42872, maximum size 32712

When adding 32 KB or more of URLs in the LOCATION clause, move them to a separate folder. Then start the gpfdist file server program in the background using the following command:

gpfdist -p 8081 -d <file_location> -l /home/gpadmin/log &

Write the create external command to read all the files in the location:

CREATE EXTERNAL TABLE ext3 ( a int )
 LOCATION ('gpfdist://mdw:8081/*') FORMAT 'TEXT' (DELIMITER '|');

Another limit is the FORMAT clause, which can take in only 8191 Bytes. If this amount is exceed, the following error message is produced:

"ERROR: format options must be less than 8191 bytes in size".  

4. What is the max text length of the SQL statement?

There is no limit. However, Tanzu Greenplum does control the length of the text using the max_stack_depth parameter and the OS. Use the stack limit defined by Unix stack size. If the customer has a bigger text, increase the stack size from the OS first and then alter the max_stack_depth in Tanzu Greenplum.

Note: Restart the database after each change is made. To take advantage of the new stack size, the Tanzu Greenplum process needs to be recycled.

Additional Information