gp_default_storage_options

Set the default values for the following table storage options when a table is created with the CREATE TABLE command.
  • APPENDONLY
  • BLOCKSIZE
  • CHECKSUM
  • COMPRESSTYPE
  • COMPRESSLEVEL
  • ORIENTATION

Specify multiple storage option values as a comma separated list.

You can set the storage options with this parameter instead of specifying the table storage options in the WITH of the CREATE TABLE command. The table storage options that are specified with the CREATE TABLE command override the values specified by this parameter.

Not all combinations of storage option values are valid. If the specified storage options are not valid, an error is returned. See the CREATE TABLE command for information about table storage options.

The defaults can be set for a database and user. If the server configuration parameter is set at different levels, this the order of precedence, from highest to lowest, of the table storage values when a user logs into a database and creates a table:

  1. The values specified in a CREATE TABLE command with the WITH clause or ENCODING clause
  2. The value of gp_default_storage_options that set for the user with the ALTER ROLE...SET command
  3. The value of gp_default_storage_options that is set for the database with the ALTER DATABASE...SET command
  4. The value of gp_default_storage_options that is set for the Greenplum Database system with the gpconfig utility

The parameter value is not cumulative. For example, if the parameter specifies the APPENDONLY and COMPRESSTYPE options for a database and a user logs in and sets the parameter to specify the value for the ORIENTATION option, the APPENDONLY, and COMPRESSTYPE values set at the database level are ignored.

This example ALTER DATABASE command sets the default ORIENTATION and COMPRESSTYPE table storage options for the database mystest. 
ALTER DATABASE mytest SET gp_default_storage_options = 'orientation=column, compresstype=rle_type'

To create an append-optimized table in the mytest database with column-oriented table and RLE compression. The user needs to specify only APPENDONLY=TRUE in the WITH clause.

This example gpconfig utility command sets the default storage option for a Greenplum Database system. If you set the defaults for multiple table storage options, the value must be enclosed in single quotes and then in double quotes.
gpconfig -c 'gp_default_storage_options' -v "'appendonly=true, orientation=column'"
This example gpconfig utility command shows the value of the parameter. The parameter value must be consistent across the Greenplum Database master and all segments.
gpconfig -s 'gp_default_storage_options'
Value Range Default Set Classifications 1
APPENDONLY= TRUE | FALSE

BLOCKSIZE= integer between 8192 and 2097152

CHECKSUM= TRUE | FALSE

COMPRESSTYPE= ZLIB | QUICKLZ | RLE_TYPE | NONE

COMPRESSLEVEL= integer between 0 and 9

ORIENTATION= ROW | COLUMN

APPENDONLY=FALSE

BLOCKSIZE=32768

CHECKSUM=TRUE

COMPRESSTYPE=none

COMPRESSLEVEL=0

ORIENTATION=ROW

 master

session

reload
Note: 1The set classification when the parameter is set at the system level with the gpconfig utility.