Available with Data Reviewer license.
Configuration keywords allow you to control how objects are created within a geodatabase in PostgreSQL. You can determine space allocation for a table or an index in the tablespace where a table or an index is created, along with other PostgreSQL specific storage attributes. They also allow you to specify one of the available storage formats for the geometry of a spatial column.
The configuration keywords are stored in the DBTUNE table. The DBTUNE table, as well as other metadata tables, is created in the database when the Create Enterprise Geodatabase or Enable Enterprise Geodatabase tool is executed.
If a large number of database connections are accessing the same files in the same location on the disk, database performance will be slow, because the connections are competing with one another for the same resources. To reduce this competition, you can store database files in different locations on the disk.
For example, DBTUNE can be modified to store the workspace tables in separate data files in different locations on the disk. This will lead to reduced disk contention and improved database input and output.
Standard GIS storage recommendations favor keeping index and log files separate from vector and tabular business tables. For performance reasons, it is better to position the business, feature, and spatial index tables separately, and position tablespace data files based on their usage pattern.
For a multiversioned, highly active editing geodatabase, database files of the VERSIONS tablespace can be separated and dispersed across available disks to avoid input or output contention.
Disk configuration
Large production enterprise geodatabase systems should employ a hardware striping solution. Your best disk and data organization strategies involve spreading your data across multiple disks.
With data spread across multiple disks, more spindles actively search for it. This can increase disk read time and decrease disk contention. However, too many disks can slow down a query. The following are two main ways of achieving striping:
- Tablespaces
- Redundant array of independent disks (RAID)
You can employ data segregation strategies, such as keeping tables from indexes or certain types of tables from other tables, to improve performance and alleviate administrative burden.
The suggested PostgreSQL optimal configuration is as follows:
- DISK 0—PostgreSQL/Application software
- DISK 1—Feature data tables
- DISK 2—Spatial index data tables
- DISK 3—Attribute data/Business rules
- DISK 4—Indexes
Reducing disk input and output contention
As a rule, you should create database files as large as possible based on the maximum amount of data you estimate the database will contain to accommodate future growth. By creating large files, you can avoid file fragmentation and gain better database performance. In many cases, you can let data files grow automatically; just be sure to limit automatic growth by specifying a maximum growth size that leaves some available hard disk space. By putting different tablespaces on different disks, you can also help eliminate physical fragmentation of your files as they grow.
To configure data and log files for best performance, follow these best practices:
- To avoid disk contention, do not put data files on the same drive that contains the operating system files.
- The RAID configuration that is best for your database files depends on several factors, including performance and recoverability needs. RAID 10 is the recommended RAID system for transaction log, data, and index files. Since the transaction log files are generally smaller than the data and index files, you can consider keeping the transaction log files in a RAID 10 system with a smaller array capacity, and store the data and index files in a RAID 5 system with a larger array capacity for those who have budget restrictions.