When you connect to an enterprise geodatabase from an ArcGIS client or through an ArcGIS Server web service, you interact with the datasets that you or other databases users have added to the geodatabase. To track that data and to implement geodatabase behavior, enterprise geodatabases use system tables.
The system tables and their contents should not be altered using anything other than ArcGIS software or SDK. However, you can view the contents of the system tables using SQL.
Core system tables
The system tables and their contents should not be altered using anything other than ArcGIS software or SDK. However, you can view the contents of the system tables using SQL.
When you query a PostgreSQL database that contains an enterprise geodatabase, you'll see the following core system tables in the sde schema:
- gdb_conflicts
- gdb_itemrelationships
- gdb_itemrelationshiptypes
- gdb_items
- gdb_itemtypes
- gdb_locks
- gdb_replicalog
- gdb_tables_last_modified
- sde_archives
- sde_branch_tables_modified
- sde_branches
- sde_column_registry
- sde_compress_log—Created the first time you compress the geodatabase.
- sde_coordinate_systems
- sde_dbtune
- sde_geometry_columns
- sde_layer_locks
- sde_layers
- sde_lineages_modified
- sde_multibranch_tables
- sde_mvtables_modified
- sde_object_ids
- sde_object_locks
- sde_process_information
- sde_raster_columns
- sde_server_config
- sde_spatial_references—Stored in the public schema, not sde schema.
- sde_state_lineages
- sde_state_locks
- sde_states
- sde_table_locks
- sde_table_registry
- sde_tables_last_edit_time
- sde_tables_modified
- sde_version
- sde_versions
The following tables are present in the geodatabase but are no longer used. They may be removed in a future release.
- sde_locators
- sde_metadata
- sde_layer_stats
- sde_logfile_pool
- sde_xml_columns
- sde_xml_index_tags
- sde_xml_indexes
Tables that implement enterprise geodatabase functionality
Information for some geodatabase functionality is stored in core system tables only. For example, information for the following functionality is stored in core system tables, and no additional tables are created in the database when you define or enable this functionality on user data:
- Attribute rules—Stored in the gdb_items system table.
- Branch versions—Six fields are added to the table or feature class business table when it is registered to participate in branch versioning to track edits.
- Domains—Stored in the gdb_items system table. A field in the gdb_itemtypes system table identifies the object as a domain.
- Relationship classes—Stored in the gdb_items and gdb_itemrelationships system tables.
The geodatabase functionality described in the following sections, however, creates additional internal tables when you enable or make use of the functionality.
Geodatabase archives
You can track transaction time history for your data using geodatabase archiving. Transaction time represents the moment in time when the feature was added to, deleted from, or updated in the database.
When you enable geodatabase archiving on a table or feature class, an archive class is created. An archive class is a copy of the business table and contains all the same fields plus three new fields: gdb_from_date, gdb_to_date, and gdb_archive_oid. When you enable archiving on a table or feature class that participates in a traditional version, a record is also added to the sde_archives system table. This record stores the registration IDs of the table that was enabled for archiving and its associated archive class table.
The name of the archive class table is the same as the original business table name with an underscore and H appended to it. For example, if a feature class named buildings is enabled for archiving, an archive class, buildings_H, is created. This archive class table is stored in the same schema as the business table.
When you trim unneeded archive records from archive classes that are not registered as versioned, that transaction is recorded in the sde_metadata system table.
Traditional versions
When you register a feature class or table to participate in traditional versions, two tables are created to track edits to the data: the adds table and the deletes table. Collectively, these are referred to as delta tables.
The adds table (a_<registration_id>) maintains information about each inserted or updated record (feature) in a versioned business table and is queried to identify which records have been added or modified at a particular geodatabase state.
The deletes table (d_<registration_id>) maintains information about the rows that were deleted or updated in a versioned table and is queried to identify which rows have been deleted or modified at a particular state. When a row is deleted, the record is not physically removed; it's flagged as deleted and never returned in subsequent database queries.
The registration_id in the adds table and deletes table names is the value for the versioned table in the sde_table_registry system table.
These tables are created in the same user schema as the table or feature class that is registered as versioned.
In addition to the delta tables, the core system tables that track versioned tables and edits are the sde_states, sde_state_lineages, sde_mvtables_modified, and sde_versions tables.
Keyset tables
Keyset tables are used by ArcGIS clients to improve query performance. Keyset tables store a list of selected rows when an ArcGIS client executes a geodatabase relationship query that joins tables using attributes that are type integer, number, date, or string. They accommodate joins using attributes other than the Object ID field.
No keyset tables are present in the geodatabase until you perform one of the following operations:
- Select more than 99 records from a feature class in a map in ArcGIS Pro or ArcMap, and the feature class is involved in a relationship class.
- In ArcGIS Pro or ArcMap, open the attribute table of a feature class that is involved in a relationship class and retrieve the related table.
One keyset table is created as a global temporary table per connection per session. Because it is a temporary table, the keyset table is deleted when the user disconnects from the geodatabase. However, you may notice the temporary schemas created by PostgreSQL to store this table remain. These have names such as pg_temp_3.
Keyset table names are formatted as follows:
keyset_<process_id>, where <process_id> is the process identification number of the session that caused the creation of the keyset table.
Log file tables
Log file tables are used by ArcGIS clients to improve query performance by storing lists of selected rows. Log file tables use joins based on Object ID attributes.
Geodatabases in PostgreSQL use temporary tables in memory, so they are not stored in the database. These tables are created per user and are named pg_temp<#>.sde_logfiles.