Database Limits

This section lists the limits that are applied to database objects in Yellowbrick databases.

Database Limit Value Notes
Databases per appliance 128
Tables per appliance 70,000 There is no limit on the number of tables per database, only a limit on the total number per appliance. This limit applies to persistent tables only.
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 1600
Rows per table 264
Width of a row in a column 64256 bytes A single row can be 256 bytes longer than any single VARCHAR column.
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
User connection/session limits (max_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.) See also Managing Idle Sessions.

Practical Limits for 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 appliance, 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.