Within any database that is being created, there must be plans and decisions made about database selection. One of those decision points will be on storage and not all databases are made to be the same based on storage. This could be a programmatic limitation, but it also can be a specific limitation implemented by the vendor. It is important to note that most commercial databases will have different storage limitations. Within a specific database choice, their versions may also have different storage limitations. Note too that with PostgreSQL, MariaDB, MySQL, and Teradata, the database itself can be unlimited in size. Other databases like Oracle, SQL Server, and SQLite have a maximum cap in terms of database size.
Two of the biggest database vendors include Microsoft SQL Server and Oracle. SQL Server has an Express edition that limits the database size to 10 GB in the database size. The database itself is free and it is scalable and completely compatible with the paid editions allowing seamless swapping. However, this limitation does force the organization to make the switch to the paid version at a certain point where the database is heavily used and most likely will benefit from the other paid editions. Oracle also has a current limit of 12 GB for the database which also is scalable and can be swapped to a paid version. In comparison, the enterprise edition of SQL Server has a maximum size of 524 PB while Oracle has over 2047 PB. As a comparison 1 PB is equivalent to 1,000,000 GB.
Even for data types within a database, there may be different size limitations for the data which can create some challenges with the migration of data especially if you have data that goes beyond a limit. For example, VARCHAR, different databases will also have different max sizes. In ANSI SQL, there is no limit defined but in MySQL, there is a 65,535 character limit, in SQL Server, there is a 2 GB limit, in Oracle, there is a 32,767 character limit and in PostgreSQL, there is a 1 GB limit.
With character large objects (CLOB), this could be XML data, file data, temp tables or other uses for the character large objects. However, in MySQL, there is a 216 byte limit, in SQL Server there is a 231 – 1 byte limit, in PostgreSQL, there is a 1 GB limit and in Oracle, there is a 4 GB – 1 byte limit. As such, if we need to transfer data between databases, we must carefully plan how that would work with this data if it becomes an issue.
It is important to note with storage that the physical space available on a system can also be a concern as there is a physical requirement in this case.
Tables themselves also have size limitations. Different databases have different criteria where they may have different storage limitations. Some of them are based on the number of rows while others are based on specific storage size. Some databases like Teradata can be unlimited while others like MySQL can have a max size of 256 TB, in SQL Server, it can be as large as the database itself with 524,272 TB. Others like SQLite are based on the file size.
Source: Authored by Vincent Tran