PostgreSQL configuration parameters

Configuration parameters identify objects (such as tables, indexes, and columns) to be stored in the database. Their corresponding values identify how the object will be stored in the database. The parameters and their configuration strings are grouped by configuration keywords.

Legacy:

Configuration keywords and parameters are largely legacy functionality. They were implemented at a time when databases required far more intervention and tinkering on the part of the database administrator to get them to perform well. For the most part, you will not need to alter the configuration parameters in your geodatabase.

When you create an enterprise geodatabase, it is populated with default configuration keywords and parameters. In the majority of cases, the default parameter values are sufficient. One possible exception is the data type used to store spatial data.

If you want the majority of feature classes to use a spatial type different from the default value, change the GEOMETRY_STORAGE parameter for the DEFAULTS keyword. If you want only some feature classes to use a different spatial type, create a custom keyword and include the GEOMETRY_STORAGE parameter set to the spatial type you want, and include the UI_TEXT parameter to make your custom keyword available to users.

As other parameters remain in the sde_dbtune table and could possibly be used in some special cases, the majority of this topic's content remains to allow you to learn what each parameter was intended to control when it was implemented.

In geodatabases stored in a PostgreSQL database, parameter name–configuration string pairs are used by ArcGIS to do the following:

  • Define the data type for spatial columns.
  • Establish the storage characteristics of tables and indexes. You need to understand how PostgreSQL creates and stores tables and indexes to properly alter these settings. Therefore, consult the PostgreSQL documentation for the version of PostgreSQL you are using before you alter these settings.
  • Make keywords available for users in the ArcGIS interface.
  • Provide comments that describe the configuration keyword.
  • Define how XML documents are stored. Because ArcGIS does not use XML documents directly, it is unlikely you'll need to set these parameters.

The following table is an alphabetic list of all the possible configuration parameters that can be used in a geodatabase in PostgreSQL. Default values are listed first where applicable. Following the table is a more in-depth explanation of the parameters grouped by their functionality.

Parameter nameDescriptionValue

A_INDEX_ROWID

Storage clause for the index on the Adds table's ObjectID column

See the PostgreSQL documentation for CREATE INDEX parameters.

A_INDEX_STATEID

Storage clause for the index on the Adds table's sde_state_id column

See the PostgreSQL documentation for CREATE INDEX parameters.

A_INDEX_USER

Storage clause for user indexes on the Adds table

See the PostgreSQL documentation for CREATE INDEX parameters.

A_INDEX_XML

Storage clause for the index on the Adds table's XML column

See the PostgreSQL documentation for CREATE INDEX parameters.

A_STORAGE

Defines storage for the Adds table

See the PostgreSQL documentation for CREATE TABLE parameters.

AUX_INDEX_COMPOSITE

Storage clause for the composite column index on the raster AUX table

See the PostgreSQL documentation for CREATE INDEX parameters.

AUX_STORAGE

Defines storage for the raster AUX table

See the PostgreSQL documentation for CREATE TABLE parameters.

B_INDEX_RASTER

Storage clause for the index on raster columns in the business table

See the PostgreSQL documentation for CREATE INDEX parameters.

B_INDEX_ROWID

Storage clause for the indexes on the business table's ObjectID column and raster rowid R<N>_SDE_ROWID_UK

See the PostgreSQL documentation for CREATE INDEX parameters.

B_INDEX_TO_DATE

Storage parameter info for creating the index r<registration_id>_sde_todate, which is used when updating the history table during an archiving operation

See the PostgreSQL documentation for CREATE INDEX parameters.

B_INDEX_USER

Storage clause for user indexes on business tables

See the PostgreSQL documentation for CREATE INDEX parameters.

B_INDEX_XML

Storage clause for the index for the business table's XML column

See the PostgreSQL documentation for CREATE INDEX parameters.

B_STORAGE

Defines storage for business tables and raster attribute tables

See the PostgreSQL documentation for CREATE TABLE parameters.

BLK_INDEX_COMPOSITE

Storage clause for the composite column index on the Raster BLK table

See the PostgreSQL documentation for CREATE INDEX parameters.

BLK_STORAGE

Defines storage for the raster BLK table

See the PostgreSQL documentation for CREATE TABLE parameters.

BND_INDEX_COMPOSITE

Storage clause for the composite column index on the raster BND table

See the PostgreSQL documentation for CREATE INDEX parameters.

BND_INDEX_ID

Storage clause for the index on the raster BND table's RID column

See the PostgreSQL documentation for CREATE INDEX parameters.

BND_STORAGE

Defines storage for the raster BND table

See the PostgreSQL documentation for CREATE TABLE parameters.

COMMENT

Line used for comments

Can place any comment up to 2,048 characters

D_INDEX_ALL

Defines fill factor for index on sde_states_id, sde_deletes_row_id, and deleted_at columns

See the PostgreSQL documentation for CREATE INDEX parameters.

D_INDEX_DELETED_AT

Storage clause for the index on the Deletes table's sde_deleted_at column

See the PostgreSQL documentation for CREATE INDEX parameters.

D_STORAGE

Defines storage for the Deletes table

See the PostgreSQL documentation for CREATE TABLE parameters.

GEOMETRY_STORAGE

Specifies the data type to be used for the spatial column in a feature class

ST_GEOMETRY, PG_GEOMETRY, or PG_GEOGRAPHY

LD_INDEX_ALL

Defines the primary key for sde_logfile_data temporary table

See the PostgreSQL documentation for CREATE INDEX parameters.

LD_STORAGE

Defines storage for the sde_logfile_data temporary table

See the PostgreSQL documentation for CREATE TABLE parameters.

LF_INDEX_ID

Defines primary key index storage for the sde_logfiles temporary table

See the PostgreSQL documentation for CREATE INDEX parameters.

LF_INDEX_NAME

Storage clause for the unique index created on the sde_logfiles temporary table

See the PostgreSQL documentation for CREATE INDEX parameters.

LF_STORAGE

Defines storage for the sde_logfiles temporary table

See the PostgreSQL documentation for CREATE TABLE parameters.

MVTABLES_MODIFIED_INDEX

Storage clause for the index for mvtables_modified

See the PostgreSQL documentation for CREATE INDEX parameters.

MVTABLES_MODIFIED_TABLE

Defines storage for the mvtables_modified table

See the PostgreSQL documentation for CREATE TABLE parameters.

RAS_INDEX_ID

Storage clause for the RID index on the raster RAS table

See the PostgreSQL documentation for CREATE INDEX parameters.

RAS_STORAGE

Defines storage for the raster RAS table

See the PostgreSQL documentation for CREATE TABLE parameters.

RASTER_STORAGE

Specifies the raster data storage type to use in a table

binary

SESSION_INDEX

Storage clause for the index on session-based log file tables

This parameter is present but not used in PostgreSQL.

SESSION_STORAGE

Defines storage for session-based log file tables

This parameter is present but not used in PostgreSQL.

SESSION_TEMP_TABLE

Controls whether log files are created in tempdb

1 or 0

This parameter is present but not used in PostgreSQL.

STATES_INDEX

Storage clause for the index on the States table

See the PostgreSQL documentation for CREATE INDEX parameters.

STATES_LINEAGES_INDEX

Controls the storage of the index on the sde_state_lineages table's primary key

See the PostgreSQL documentation for CREATE INDEX parameters.

STATES_LINEAGES_TABLE

Defines storage for the sde_state_lineages table

See the PostgreSQL documentation for CREATE TABLE parameters.

STATES_TABLE

Defines storage for the sde_states table

See the PostgreSQL documentation for CREATE TABLE parameters.

UI_NETWORK_TEXT

Required for the associated configuration keyword to appear in the ArcGIS user interface; contains description of network configuration

Description up to 2,048 characters

UI_TERRAIN_TEXT

Required for the associated configuration keyword to appear in the ArcGIS user interface; contains description of terrain configuration

Description up to 2,048 characters

UI_TEXT

Required for the associated configuration keyword to appear in the ArcGIS user interface; contains description of associated noncomposite configuration keyword

Description up to 2,048 characters

UI_TOPOLOGY_TEXT

Required for the associated configuration keyword to appear in the ArcGIS user interface; contains description of topology configuration

Description up to 2,048 characters

VERSIONS_INDEX

Storage clause for index on the sde_versions table

See the PostgreSQL documentation for CREATE INDEX parameters.

VERSIONS_TABLE

Defines storage for the sde_versions table

See the PostgreSQL documentation for CREATE TABLE parameters.

XML_COLUMN_STORAGE

Specifies the type of XML columns to create: either native DBMS XML or ArcSDE XML

DB_XML or SDE_XML

XML_DOC_INDEX

Storage clause for xmldoc<n>_pk and xml_doc<n>_ix indexes on the sde_xml_doc<n> table

See the PostgreSQL documentation for CREATE INDEX parameters.

XML_DOC_STORAGE

Storage clause for the sde_xml_doc<n> table

See the PostgreSQL documentation for CREATE TABLE parameters.

XML_DOC_UNCOMPRESSED_TYPE

Specifies the storage format for XML documents

BINARY or TEXT

XML_IDX_FULLTEXT_UPDATE_METHOD

Dictates how changes made to the xml_doc_val column in the sde_xml_doc<n> table (the XML document table) and the text_tag column of the sde_xml_idx<n> table (the index table of an XML column) are propagated to the full-text index

MANUAL or AUTOMATIC

XML_IDX_INDEX_DOUBLE

Storage clause for the xmlix<n>_db index on the double_tag column of the sde_xml_idx<n> table

See the PostgreSQL documentation for CREATE INDEX parameters.

XML_IDX_INDEX_ID

Storage clause for the xmlix<n>_id index on the ID column of the xml_idx<n> table

See the PostgreSQL documentation for CREATE INDEX parameters.

XML_IDX_INDEX_PK

Storage clause for the xmlix<n>_pk index on the xml_key_column identity column of the sde_xml_idx<n> table

See the PostgreSQL documentation for CREATE INDEX parameters.

XML_IDX_INDEX_STRING

Storage clause for the xmlix<n>_st index on the string_tag column of the sde_xml_idx<n> table

See the PostgreSQL documentation for CREATE INDEX parameters.

XML_IDX_INDEX_TAG

Storage clause for the xmlix<n>_tg index on the tag_id column of the sde_xml_idx<n> table

See the PostgreSQL documentation for CREATE INDEX parameters.

XML_IDX_STORAGE

Storage clause for the sde_xml_idx<n> table (the index table of an XML column)

See the PostgreSQL documentation for CREATE TABLE parameters.

Configuration parameter summary

For the XML parameters, <n> refers to the xml_column_id associated with a specific XML column.

Functional descriptions of parameters

By default, PostgreSQL stores tables and indexes in the default tablespace of your database. To store tables and indexes in other tablespaces, the postgres superuser needs to create additional tablespaces and grant CREATE privileges on the tablespaces to the users who will be creating objects in them. You can use the psql meta-command \db+ to list existing tablespaces and their permissions.

Once this configuration is complete, you can specify different tablespaces to store various tables using storage parameters. The syntax for specifying tablespace storage varies depending on the parameter.

Note:

Tablespaces in PostgreSQL use symbolic links; therefore, user-defined tablespaces can only be used on systems that support symbolic links.

Business table and index storage parameters

The business table is the attribute table of a feature class or nonspatial table. Use the B_STORAGE parameter to define the storage configuration of a business table.

Five index storage parameters exist to support the creation of business table indexes:

  • The B_INDEX_USER parameter holds the storage configuration for user-defined indexes.
  • The B_INDEX_ROWID parameter holds the storage configuration of the index that ArcGIS creates on a register table's ObjectID column, also referred to as the ROWID or OBJECTID.
  • The B_INDEX_RASTER parameter holds the fill factor information of the raster column index that ArcGIS creates when a raster column is added to a business table. ArcGIS creates this index on feature classes with a raster column.
  • The B_INDEX_TO_DATE parameter specifies the fill factor for the index R<registration_id>_sde_todate. This index is created when archiving is enabled on a business table and is used when updating the history table during an archive operation.
  • The B_INDEX_XML parameter specifies the fill factor for the index on the XML column of a business table (also covered in the XML type parameters section below).

Adds and deletes tables storage parameters

Registering a business table or feature class as versioned allows multiple users to maintain and edit an object. ArcGIS creates two tables—the adds table and the deletes table—for each table that is registered as versioned.

At appropriate intervals, users merge the changes they have made with the changes made by other users and reconcile any conflicts that arise when the same features are modified.

Adds table parameters

The A_STORAGE parameter maintains the storage configuration of the adds table. The adds table is named A<n>, where <n> is the registration ID listed in the sde_table_registry geodatabase system table. For instance, if the business table ROADS is listed with a registration ID of 10, ArcGIS creates the adds table as A10.

Five other storage parameters hold the storage configuration of the indexes of the adds table. The A_INDEX_ROWID parameter specifies the fill factor of the index that ArcGIS creates on the versioned ObjectID column, also referred to as the ROWID. The adds table ROWID index is named A<n>_ROWID_IX1, where <n> is the business table's registration ID with which the adds table is associated.

The A_INDEX_STATEID parameter holds the fill factor configuration of the index that ArcGIS creates on the adds table's SDE_STATE_ID column. The SDE_STATE_ID column index is called A<n>_STATE_IX2, where <n> is the business table's registration ID with which the adds table is associated.

The A_INDEX_USER parameter holds the fill factor configuration of user-defined indexes that ArcGIS creates on the adds table. The user-defined indexes on the business tables are duplicated on the adds table. The A_INDEX_RASTER parameter specifies the fill factor for the index of the raster column index of an adds table's raster column.

The A_INDEX_XML parameter specifies the fill factor for the index on the XML column of an adds table (also covered in the XML type parameters section below).

Deletes table parameters

The D_STORAGE parameter holds the storage configuration of the deletes table. The deletes table is named D<n>, where <n> is the registration ID listed in the sde_table_registry system table. For instance, if the business table ROADS is listed with a registration ID of 10, ArcGIS creates the deletes table as D10.

Two other storage parameters hold the storage configuration of the indexes that ArcGIS creates on the deletes table:

  • The D_INDEX_ALL parameter specifies the fill factor of the D<n>_IDX1 index that ArcGIS creates on the deletes table's SDE_STATE_ID and SDE_DELETES_ROW_ID columns.
  • The D_INDEX_DELETED_AT parameter holds the fill factor of the D<n>_IDX2 index that ArcGIS creates on the deletes table's SDE_DELETED_AT column.

Raster table parameters

When you specify a keyword with RASTER_STORAGE set to binary when creating a raster dataset or mosaic dataset, the raster column added to a business table is a foreign key reference to raster data stored in a schema consisting of supporting tables and indexes. When RASTER_STORAGE is set to rasterblob (the default), a bytea column is added to the business table and supporting raster information is stored in additional tables.

The following is an explanation of the raster table parameters that define configuration for the supporting raster tables and indexes:

The RAS_STORAGE parameter holds the PostgreSQL CREATE TABLE storage configuration of the RAS table.

The RAS_INDEX_ID parameter specifies the fill factor for the RAS table index. The BND_STORAGE parameter holds the PostgreSQL CREATE TABLE storage configuration of the BND table.

The BND_INDEX_COMPOSITE parameter specifies the fill factor of the BND table's composite column index.

The BND_INDEX_ID storage specifies the fill factor of the BND table's row ID (RID) column index.

The AUX_STORAGE parameter holds the PostgreSQL CREATE TABLE storage configuration of the AUX table.

The AUX_INDEX_COMPOSITE parameter specifies the fill factor for the AUX table's index.

The BLK_STORAGE parameter holds the PostgreSQL CREATE TABLE storage configuration of the BLK table.

The BLK_INDEX_COMPOSITE parameter specifies the fill factor of the BLK table's index.

You may notice the default fill factor for raster indexes is higher than for other indexes. That is because the raster data typically does not change as much.

Geometry storage parameters

You must have the Esri ST_Geometry type present in geodatabases you use in PostgreSQL. But, once created, you have three spatial data storage formats you can use in geodatabases in PostgreSQL: ST_Geometry, PostGIS Geometry, or PostGIS Geography. The GEOMETRY_STORAGE parameter indicates which geometry storage method is to be used. It has the following values: ST_GEOMETRY, PG_GEOMETRY, or PG_GEOGRAPHY.

Note:

To use the PG_GEOMETRY or PG_GEOGRAPHY values, you must have PostGIS installed and the database must be enabled to use PostGIS.

Log file parameters

Log file tables are used by ArcGIS to maintain sets of selected records. Log file parameters are no longer used in geodatabases in PostgreSQL.

User interface parameters

User interface parameters begin with UI and indicate whether their associated configuration keyword will be available through the ArcGIS user interface and ArcObjects. UI_TEXT is used for noncomposite configuration keywords. UI_TOPOLOGY_TEXT is used for topology keywords. UI_NETWORK_TEXT is used for network keywords. The default configuration keywords that need UI parameters already have them. You would only add one of these parameters if you created your own custom keywords.

XML type parameters

Note:

ArcGIS clients do not work directly with XML columns. If you do not use XML columns and XML documents in your geodatabase, you do not need to configure these parameters.

XML configuration parameters are used to specify storage information for the tables and indexes used to track and store XML documents.

The XML_COLUMN_STORAGE parameter determines whether XML columns are created as ArcSDE XML or native PostgreSQL XML. The default setting is to use native PostgreSQL XML (DB_XML).

The XML_INDEX_TAGS_INDEX parameter of the DATA_DICTIONARY configuration keyword specifies the fill factor for the indexes on the sde_xml_indexes table.

The XML_INDEX_TAGS_TABLE parameters of the DATA_DICTIONARY configuration keyword defines the storage for the sde_xml_index_tags geodatabase system tables. To specify custom storage with these two parameters, you must alter their values before you create your geodatabase since they control storage for geodatabase system tables.

The A_INDEX_XML parameter defines the fill factor of the index on the XML column in the adds table of a versioned feature class.

The B_INDEX_XML parameter defines the fill factor of the index on the XML column of business tables.

XML_IDX_FULLTEXT_UPDATE_METHOD defines how changes made to the xml_doc_val column in the XML document table (sde_xml_doc<n>) and the text_tag column of the index table of an XML column (sde_xml_idx<n>) are propagated to the full-text index. The options for this parameter are AUTOMATIC or MANUAL. When set to AUTOMATIC, a trigger created on the XML side tables updates indexes as rows are inserted. If the value for XML_IDX_FULLTEXT_UPDATE_METHOD is not set to AUTOMATIC, it is assumed to be MANUAL.

The XML_DOC_STORAGE parameter sets the storage for the sde_xml_doc<n> table.

The XML_DOC_INDEX configuration parameter sets the fill factor for the xmldoc<n>_pk and xml_doc<n>_ix indexes on the sde_xml_doc<n> table.

The XML_DOC_UNCOMPRESSED_TYPE configuration parameter determines how the contents of XML documents will be stored. The options are BINARY or TEXT. If you use BINARY, data is stored as bytea data type. If you use TEXT, the data will be either Unicode or ASCII, depending on whether your database is set to store Unicode data (UTF-8).

The XML_IDX_STORAGE configuration parameter sets the storage for the sde_xml_idx<n> table, which is the index table of an XML column. The following parameters define storage for indexes on columns in the sde_xml_idx<n> table itself:

  • XML_IDX_INDEX_DOUBLE—Defines storage for the xmlix<n>_db index on the double_tag column
  • XML_IDX_INDEX_ID—Defines storage for the xmlix<n>_id index on the ID column
  • XML_IDX_INDEX_PK—Defines storage for the xmlix<n>_pk index on the xml_key_column identity column
  • XML_IDX_INDEX_STRING—Defines storage for the xmlix<n>_st index on the string_tag column
  • XML_IDX_INDEX_TAG—Defines storage for the xmlix<n>_tg index on the tag_id column