Creating Databases for Data Compression

Compressed databases can include compressed and uncompressed tables or partitions.

Note:

The default setting for compression in the model database is none, so unless you specify otherwise, compression is off when you create a database.

To create databases with data compression, use:

create database database_name
[…]
with dml_logging = { minimal | full }
, durability =
{ no_recovery | at_shutdown | full }
, compression = {none | row | page}

The compression = parameter indicates that all tables in the database inherit the specified level of compression, unless you explicitly state otherwise. See the Reference Manual: Commands.

This example creates the emaildb database with row-level compression on the emaildb_dev device:

create database emaildb
on emaildb_dev = '50M'
with compression = row

Altering the Compression Level of a Database
Changing a database's compression level does not change the compression level of existing tables in the database; only tables you create after alter the database inherit the new compression level.