How to set the maximum disk size of a database?

Question

How to set the maximum disk size of a Mysql, Oracle, Postgresql or DB2 database?

This is how I do in SQL Server:

Create Database MyDatabase
on (Name='MyDatabase_Data',
    Filename='c:\db\BdUnisanta_Data.mdf',
    Size= 20MB,
    FileGrowth = 10%,
    Maxsize=100MB)
log on 
    (Name = 'MyDatabase_log',
     Filename = 'c:\db\MyDatabase_Log.ldf',
     Size = 5MB,
     FileGrowth = 5%,
     MAXSIZE = UNLIMITED
     ) 

How could I do that in these databases?

Answer 1

Each database has its own way of limiting size, or not.

MYSQL

Mysql does not provide a system to limit the size of the database with Create Database as your example. One of the ways to solve this is to use Innodb as the database engine and set the configuration innodb_file_per_table values close to the desired value of files per table that would probably work to limit table growth beyond a computable limit. In addition, Innodb keeps the data in a file(s) defined(s) by the property innodb_data_file_path and can also be limited its maximum size through configuration, however this property cannot be changed in banks already installed.

Unfortunately I don’t see a practical way to get what you want. If you are worried about using disk space exceeding the predefined limits and do not want to follow the path of external quota regulations, I suggest keeping the table space settings combined (and not innodb_file_per_table) and remove autoextend from the innodb_data_file_path setting or set a maxsize (eg. ibdata1:16M:autoextend:max:1GB)

Even so you still won’t be able to get specific limits of each user or schemas but at least it will prevent the space occupied by the tables from growing beyond their initial size in this configuration. Yet, and unfortunately, there is no way to set a size for each of the schemes in an individualized way.

Oracle

Oracle is a database that has been heavily tested in the business environment and scalability and disk size control has been very well explored. There are various settings that aim to set the physical size of the database on disk and to control the share of each user.

The very example of Create Database documentation shows how it is possible to define file sizes of the database:

CREATE DATABASE mynewdb
   USER SYS IDENTIFIED BY sys_password
   USER SYSTEM IDENTIFIED BY system_password
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/app/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/app/oracle/oradata/mynewdb/redo03.log') SIZE 100M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET US7ASCII
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
   SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs
  DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
  SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

You can set multiple limits during database creation. Some of these limits are restricted and affected by the limits of operating system. For example, if you set MAXDATAFILES, the Oracle Database allocates enough space in the control file to store MAXDATAFILES file names even if the database has only one data file initially. However, as the maximum size of the control file is limited and dependent on the operating system, it may not be possible to define all CREATE DATABASE parameters at their theoretical maxima. For more information on how to set limits during the creation of the data, see Oracle Database SQL Language Reference and operating system specific Oracle documentation.

Postgresql

In the official documentation nothing indicates that there is support right into Postgresql to perform this restriction. There are workarounds out there that use even triggers to limit the size of rows in a table. Or even using tabalespaces within "Volumegroup" of linux, in mailing lists limit usage through volume groups was considered as "A disaster waiting to happen", despite being a solution that can work.

DB2

TABLESPACE on DB2 has a MAXSIZE attribute that can be used to specify physical size attributes in the instruction CREATE DATABASE (or when creating additional TABLESPACE) or use ALTER TABLESPACE to define them later.

MAXIZIZE Specifies the maximum size at which a TABLESPACE is enabled for automatic resizing can be automatically increased. Whether TABLESPACE is automatically scalable, but the clause MAXSIZE is not specified, default is NONE.

Using the value "NONE" (default), defines that TABLESPACE is allowed to grow to the capacity of the file system or to the maximum size of TABLESPACE (defined by the size and number of files).

Then, the MAXSIZE property in the CREATE DATABASE can be used to specify a limit on the size per database, to which an automatic storage TABLESPACE can be automatically increased. The whole value should be followed by K (for kilobytes), M (for megabytes) or G (for gigabytes).

CREATE DATABASE MyDatabase
   automatic storage yes
   on C:\mydb
   user tablespace managed by automatic storage
      initialsize 20 M increasesize 10 percent MAXSIZE 100 M;