Product DocumentationAppearance
Database Limits
This section lists the limits that are applied to database objects in Yellowbrick instances.
| Database Limit | Value | Notes |
|---|---|---|
| Databases per instance | 1,000 | The maximum number of databases includes the yellowbrick database, so the effective limit is 999 user-created databases. |
| Tables per instance | 100,000 | There is no limit on the number of tables per database, only a limit on the total number per data warehouse instance. This limit applies to persistent tables only. |
| Maximum number of nodes per compute cluster per instance | 64 | |
| Size of internal temporary tables | 30MB | Temporary tables that the system creates during queries are limited to a size of 30MB. This limitation does not apply to temporary tables created by users. |
| Columns per table | 2000 | Storage limit for the maximum number of columns in a single table |
| Columns per view or select | 8000 | The maximum number of columns that can be placed in a row. For example, the output of a UNION query is limited to 8000 columns. In practice, the limit applies to the number of columns that have to be propagated up the query plan tree during execution. Therefore, you may find that less than 8000 columns can be selected because of the behavior of the plan. Columns that are not in the select list but are needed for its computation (such as ORDER BY columns, GROUP BY columns, and temporary columns for CASE expressions) do count towards the limit.For example, a simple query that selects one column but orders by another column propagates two columns, not one: yellowbrick=> explain(stable,verbose) select c1 from t1 order by c2; QUERY PLAN ---------------------------------------------workers node single SELECT (t1.c1, t1.c2) distribute single all DISTRIBUTE SORT (t1.c1, t1.c2) distribute single all SORT ON (t1.c2 ASC NULLS LAST) (t1.c1, t1.c2) distribute on (t1.c1) all SCAN t1 (t1.c1, t1.c2) distribute on (t1.c1)(14 rows) |
| Rows per table | 264 | |
| Maximum width of a row in a column | 64231 bytes | A single row can be 231 bytes longer than any single VARCHAR column. See Maximum Row Size. |
Length of a VARCHAR column | 64000 bytes | For other data type limits, see SQL Data Types. |
| Length of database object names | 128 bytes | See SQL Identifiers. |
| Number of distribution columns per table | 1 | See Distribution Options. |
| Number of sort columns per table | 1 | See Sorted and Clustered Tables. |
| Number of cluster columns per table | 4 | See Sorted and Clustered Tables. |
| Number of partition columns per table | 4 | See Partitioning Options. |
Maximum number of IN list items | 200000 | See IN. |
Maximum number of WHEN clauses in a CASE expression | 8192 | |
| Maximum number of ACLs per database object | 5000 | The maximum number of privileges you can grant to a database object is 5000. |
User connection/session limits (max_user_connections) | 2000 | The maximum number of database user connections is 2000. (The related max_connections value is 2300, which allows for 300 additional system user connections; max_user_connections must be set to a lower value than max_connections.) See also Managing Idle Sessions. |
Maximum Row Size
When you create a table, the maximum row limit of 64231 bytes is not enforced. However, you will not be able to insert more than 64231 bytes into a table (as an absolute maximum), and depending on the definition of the columns in the table, the actual limit on the stored size of a row may be smaller.
Additional overhead bytes per column are stored as follows:
- If a column (of any type) can be
NULL: + 1 byte
For example, a CHAR(10) column that accepts NULL values occupies 11 bytes.
- If the declared length of a
VARCHARcolumn <= 30: declared length + 2
For example, a VARCHAR(25) column always occupies 27 bytes (or 28 if it is nullable).
VARCHARcolumns >30: byte length + 2 + 8
For example, a VARCHAR(500) column occupies its actual length + 10 bytes. If 500 bytes are inserted, its size is 510 bytes (or 511 if it is nullable). If 100 bytes are inserted, its size is 110 bytes (or 111 if it is nullable).
Practical Limits for Numbers of Tables and Partitions
Yellowbrick does not enforce limits on either the number of tables per database or the number of partitions per table. The maximum number of tables is 70,000 per cluster, regardless of the number of databases you create. Ideally, the number of partitions created per table should be less than 1,000. (See Partitioning Tables for details about how the actual number of partitions is computed, based on the partitioning columns you define. The enforced maximum is approximately 250,000 partitions.)
Respecting these more practical limits will help to optimize the storage system and improve both query performance and the efficiency of background database operations. In addition to monitoring the number of tables and partitions on your system, try to avoid rapid loading of a large number of small tables with a few rows at a time. When many thousands of small write transactions are committed to the database, fragmentation of the storage media is likely to occur.