PostgreSQL DBTUNE configuration parameters

Configuration parameters, which are stored in the parameter_name column of the sde_dbtune table, identify the database object to be configured or denote a specific setting. Their corresponding values, which are stored in the config_string column of sde_dbtune, identify how the object or setting will be configured. The parameters and their configuration strings are grouped together in the sde_dbtune table by configuration keywords. Keyword/Parameter name combinations are unique, but most parameter names are not and are reused under a number of different keywords throughout the sde_dbtune table.

Valid values for the parameter_name column are fixed; you cannot invent new parameter names. Likewise, the config_string column accepts only certain numeric values or SQL strings. In most cases, these strings are appended to SQL CREATE TABLE and CREATE INDEX statements so they must match the SQL syntax necessary for the given DBMS.

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

• Establish the storage characteristics of tables and indexes.
• Define the fill factor for indexes
• Define the storage type for spatial and raster columns
• Define how XML documents are stored.
• Make keywords available for users in the ArcGIS interface.
• Provide comments that describe the configuration keyword.

By default, PostgreSQL stores tables and indexes in the default tablespace of your database. To store tables and indexes in other tablespaces, the super user 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 sde_dbtune storage parameters.

The syntax for specifying tablespace storage varies depending on the parameter. The dbtune.sde file installed with ArcSDE for PostgreSQL includes commented-out example entries for each parameter to assist you in using the correct syntax.

Note:

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

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

Parameter name	Description	Value
A_INDEX_ROWID	Adds table object ID column index storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
A_INDEX_STATEID	Adds table sde_state_id column index storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
A_INDEX_USER	Adds table index storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
A_INDEX_XML	Adds table XML column index table storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
A_STORAGE	Adds table storage definition	See the PostgreSQL documentation for CREATE TABLE parameters.
AUX_INDEX_COMPOSITE	Raster AUX table composite column index storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
AUX_STORAGE	Raster AUX table storage definition	See the PostgreSQL documentation for CREATE TABLE parameters.
B_INDEX_RASTER	Business table raster column index storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
B_INDEX_ROWID	Business table object ID column index raster rowid index R<N>_SDE_ROWID_UK storage definition	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	Business table user index storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
B_INDEX_XML	Business table XML column index table storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
B_STORAGE	Business table and raster attribute table storage definition	See the PostgreSQL documentation for CREATE TABLE parameters.
BLK_INDEX_COMPOSITE	Raster BLK table composite column index storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
BLK_STORAGE	Raster BLK table storage definition	See the PostgreSQL documentation for CREATE TABLE parameters.
BND_INDEX_COMPOSITE	Raster BND table composite column index storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
BND_INDEX_ID	Raster BND table RID column index storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
BND_STORAGE	Raster BND table storage definition	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	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	Deletes table sde_deleted_at column index storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
D_STORAGE	Deletes table storage definition	See the PostgreSQL documentation for CREATE TABLE parameters.
GEOMETRY_STORAGE	Indicates storage data type for spatial column	ST_GEOMETRY or PG_GEOMETRY
LD_INDEX_ALL	Sde_logfile_data and sde_logpool tables' primary key storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
LD_STORAGE	Sde_logfile_data and sde_logpool tables' storage definition	See the PostgreSQL documentation for CREATE TABLE parameters.
LF_INDEX_ID	Sde_logfile primary key storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
LF_INDEX_NAME	Storage definition for sde_logfiles unique index	See the PostgreSQL documentation for CREATE INDEX parameters.
LF_STORAGE	Sde_logfiles table storage definition	See the PostgreSQL documentation for CREATE TABLE parameters.
MVTABLES_MODIFIED_INDEX	Mvtables_modified index storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
MVTABLES_MODIFIED_TABLE	Mvtables_modified table storage definition	See the PostgreSQL documentation for CREATE TABLE parameters.
RAS_INDEX_ID	Raster RAS table RID index storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
RAS_STORAGE	Raster RAS table storage definition	See the PostgreSQL documentation for CREATE TABLE parameters.
RASTER_STORAGE	Defines the raster data storage type	binary or ST_Raster
SESSION_INDEX	ArcSDE session-based and stand-alone log file indexes storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
SESSION_STORAGE	ArcSDE session-based and stand-alone log file tables storage definition	See the PostgreSQL documentation for CREATE TABLE parameters.
SESSION_TEMP_TABLE	Controls whether log files are created in tempdb	1 or 0 This parameter is present but not currently used in PostgreSQL.
STATES_INDEX	States table storage definition	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	Sde_state_lineages table storage definition	See the PostgreSQL documentation for CREATE TABLE parameters.
STATES_TABLE	Sde_states table storage definition	See the PostgreSQL documentation for CREATE TABLE parameters.
UI_NETWORK_TEXT	User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of network configuration	Description up to 2,048 characters
UI_TERRAIN_TEXT	User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of terrain configuration	Description up to 2,048 characters
UI_TEXT	User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of associated noncomposite configuration keyword	Description up to 2,048 characters
UI_TOPOLOGY_TEXT	User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of topology configuration	Description up to 2,048 characters
VERSIONS_INDEX	Sde_versions index storage definition	See the PostgreSQL documentation for CREATE INDEX parameters.
VERSIONS_TABLE	Sde_versions table storage definition	See the PostgreSQL documentation for CREATE TABLE parameters.
XML_COLUMN_STORAGE	Specifies the type of XML columns to create: either ArcSDE XML or native DBMS XML	SDE_XML or DB_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 sde_xml_doc<n> table	See the PostgreSQL documentation for CREATE TABLE parameters.
XML_DOC_UNCOMPRESSED_TYPE	Determines the storage format for XML documents	BINARY orTEXT
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	AUTOMATIC or MANUAL
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 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 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 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

Business table and index storage parameters

A business table is any PostgreSQL table created by an ArcSDE client, the sdetable administration command, or the ArcSDE C application programming interface (API) SE_table_create function. Use the sde_dbtune table's 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 created with the C API function SE_table_create_index and the create_index operation of the sdetable command.
• The B_INDEX_ROWID parameter holds the storage configuration of the index that ArcSDE creates on a register table's object ID 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 ArcSDE creates when a raster column is added to a business table. This index is created by the ArcSDE C API function SE_rastercolumn_create. This function is called by ArcGIS when it creates a feature class and by the add, copy, and import operations of the sderaster command.
• 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 subsequent "XML type parameters" section).

Adds and deletes tables storage parameters

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

At appropriate intervals, the 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 TABLE_REGISTRY system table. For instance, if the business table ROADS is listed with a registration ID of 10, ArcSDE 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 ArcSDE creates on the multiversioned object ID 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 ArcSDE 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 ArcSDE 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 subsequent "XML type parameters" section).

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 TABLE_REGISTRY system table. For instance, if the business table ROADS is listed with a registration ID of 10, ArcSDE creates the deletes table as D10.

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

• The D_INDEX_ALL parameter specifies the fill factor of the D<n>_IDX1 index that ArcSDE 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 ArcSDE creates on the deletes table's SDE_DELETED_AT column.

For more information on the structure of adds and deletes tables and how they are used, see Versioned tables in a geodatabase in PostgreSQL .

Raster table parameters

A raster column added to a business table is actually a foreign key reference to raster data stored in a schema consisting of four tables and five supporting indexes. The raster table parameters define configuration for the 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 in the sde_dbtune table. That is because the raster data typically does not change as much.

There is an additional type of raster table—the raster attribute table. This table (or tables; there can be multiple such tables) stores attribute values based on cell values in the raster. The B_STORAGE parameter defines the storage of these tables. If you need to define a different storage location for these tables than you do for other feature class business tables, be sure to create a raster keyword you can use when creating raster datasets and raster catalogs that specifies different storage information for the raster attribute tables.

To learn more about raster attribute tables, see Raster dataset attribute tables. To learn about custom configuration keywords, see Custom configuration keywords.

Geometry storage parameters

ArcSDE for PostgreSQL provides two spatial data storage formats. The GEOMETRY_STORAGE parameter indicates which geometry storage method is to be used. It has the following values: ST_GEOMETRY or PG_GEOMETRY.

Note:

To use the PG_GEOMETRY storage type, you must have PostGIS installed and the database must be enabled to use the PostGIS geometry storage type. See ArcGIS and the PostGIS geometry type for more information.

See What is the ST_Geometry storage type? for a description of this data storage type.

Log file parameters

Log file tables are used by ArcSDE to maintain sets of selected records. Log file parameters affect log file and log file data tables and indexes. They begin with the letter L or the word SESSION.

The parameters are as follows:

• LD_INDEX_ALL defines sde_logfile_data and sde_logpool tables primary key fill factor.
• LD_STORAGE defines configuration for the sde_log file_data and sde_logpool_<sde_id> tables.
• LF_INDEX_ID defines sde_log file primary key fill factor storage.
• LF_INDEX_NAME defines fill factor for the sde_log file unique index.
• LF_STORAGE defines the storage configuration for the sde_logfiles table.
• SESSION_INDEX configures the fill factor of the logdata_<sde_id>_<sde_id>_<current_standalone_id>_idx1 index for the stand-alone log table and the logsession_<sde_id>_idx1 index on the session table.
• SESSION_STORAGE defines storage configuration for the logdata_<sde_id>_<current_standalone_id> stand-alone log table and session_<sde_id> session table.
• SESSION _TEMP_TABLE is not used in geodatabases in PostgreSQL.

For more information on how log file tables are used in the geodatabase, see ArcSDE log file tables 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_TERRAIN_TEXT is used for terrain keywords. UI_NETWORK_TEXT is used for network keywords. See Making configuration keywords available in ArcGIS for more information on how to use UI parameters.

XML type parameters

Note:

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 ArcSDE XML (SDE_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 ArcSDE geodatabase system tables. If you want 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) or not.

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

See XML documents in a geodatabase in PostgreSQL for information on using XML columns.

Comments

You can add a COMMENT parameter in the dbtune.sde file if you want by adding a line beginning with a single pound sign (#). You might do this if you create your own custom keywords and want to add comments on how or when the keyword should be used. For example, you could add a comment to a user's log file keyword:

#COMMENT		"This keyword is used by ArcSDE to create log file tables for all users logged in as editor"