This article will take a look at some common frequently asked question on Pivotal Greenplum limit.
1. What are some of the limit in the Pivotal Greenplum?
|Maximum size for a database?||unlimited|
|Maximum size for a table?||unlimited, 128 TB per partition per segment|
|Maximum size for a row?||>1 GB (approximate)|
|Maximum size for a field?||1 GB|
|Maximum BLOB size||1 GB (Use BYTEA datatype, we don't have BLOB)|
|Maximum number of rows in a table?||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||unlimited|
|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 MAX number of columns that can be present in the SELECT statement ?
3. What is the MAX number of URL's that can be placed in the LOCATION part of the external table creation ?
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 URL's that can be added to the LOCATION clause , what restrict is the amount of size the location clause which is 32712 (32KB) , if you exceed you might hit with the error
ERROR: row is too big: size 42872, maximum size 32712
So if you planning to add many URL's in the Location clause , would recommend to move them on to a separate folder and start the gpfdist file server program in the background using
-d <file_location> -l /home/gpadmin/log &
and write the create external command ( to the one below ) to read all the files in the location
CREATE EXTERNAL TABLE ext3 ( a int ) LOCATION ('gpfdist://mdw:8081/*') FORMAT 'TEXT' (DELIMITER '|');
Also another limit is the FORMAT clause can take in only 8191 bytes , if you exceed you may end up with error
"ERROR: format options must be less than 8191 bytes in size".
4. What is the max text length of the SQL statement ?
No limit , but we (Pivotal Greenplum) do control the length of the text using the max_stack_depth parameter and OS control it using the stack limit defined by unix stack size , so if the customer has a bigger text , then increase the stack size from the OS first and then alter the max_stack_depth in greenplum and restart the database after each changes , since to take advantage of the new stack size , the green plum process needs to be recycled.