Schema API supports specialized index types, like PostgreSQL GIN and GiST indexes

Drupal's Schema API could only describe regular (B-tree) indexes. Table schema definitions can now describe specialized index technologies as well, starting with PostgreSQL's GIN (Generalized Inverted Index) and GiST (Generalized Search Tree) indexes. These index types can dramatically speed up queries on JSON columns, arrays, and full-text data on PostgreSQL.

New index definition classes

The \Drupal\Core\Database\SchemaDefinition\Index value object now extends the new abstract base class \Drupal\Core\Database\SchemaDefinition\IndexBase. Concrete subclasses of IndexBase describe the index technology to be used. The PostgreSQL driver provides two specialized index definitions:

• \Drupal\pgsql\Driver\Database\pgsql\GinIndex
• \Drupal\pgsql\Driver\Database\pgsql\GistIndex

Not every database engine supports every index technology. Each database driver declares which index definition classes it supports, and skips the creation of indexes it does not support. The new \Drupal\Core\Database\SchemaDefinition\AlternativeIndexes value object lists alternative definitions for the same logical index, in order of preference; the driver creates the first index it supports and ignores the others.

Using specialized indexes in a table schema definition

The indexes property of a Table definition now accepts IndexBase and AlternativeIndexes objects:

use Drupal\Core\Database\SchemaDefinition\AlternativeIndexes;
use Drupal\Core\Database\SchemaDefinition\Index;
use Drupal\Core\Database\SchemaDefinition\Table;
use Drupal\pgsql\Driver\Database\pgsql\GinIndex;

new Table(
  name: 'my_table',
  columns: [
    // ...
  ],
  indexes: [
    // A GIN index on PostgreSQL. Drivers that do not support GIN
    // indexes skip this index entirely.
    new GinIndex(name: 'ages', columns: ['age']),
    // A GIN index on PostgreSQL, falling back to a regular index on
    // other databases.
    new AlternativeIndexes(
      name: 'ages',
      indexes: [
        new GinIndex(name: 'ages', columns: ['age']),
        new Index(name: 'ages', columns: ['age']),
      ],
    ),
  ],
);

Using specialized indexes with Schema::addField() and Schema::changeField()

The indexes element of the keys specification passed to Schema::addField() and Schema::changeField() now also accepts index definition objects, next to the legacy array-based index specifications:

$schema->addField('my_table', 'age', $field_spec, [
  'indexes' => [
    'ages' => new AlternativeIndexes(
      name: 'ages',
      indexes: [
        new GinIndex(name: 'ages', columns: ['age']),
        new Index(name: 'ages', columns: ['age']),
      ],
    ),
  ],
]);

As with table schema definitions, index definitions of a technology not supported by the driver are skipped, and alternative indexes fall back to their most preferred supported definition.

New PostgreSQL schema methods

The PostgreSQL driver's Schema class has two new public methods to add a specialized index to an existing table directly:

$schema = \Drupal::database()->schema();
if ($schema instanceof \Drupal\pgsql\Driver\Database\pgsql\Schema) {
  $schema->addGinIndex('my_table', 'ages', ['age']);
  $schema->addGistIndex('my_table', 'locations', ['location']);
}

Both methods throw a SchemaObjectDoesNotExistException when the table does not exist and a SchemaObjectExistsException when the table already has an index by that name. Limited length columns are not allowed for GIN and GiST indexes.

Character type columns

GIN and GiST indexes have no operator class for character type columns (text, character varying, character). For these columns the driver creates an expression index on the text search vector of the column, using to_tsvector(). This covers all character types from the Schema API type map: varchar, varchar_ascii, char, and text in all sizes.

The text search configuration matching the site default language is used when it exists in the PostgreSQL pg_ts_config catalog. Otherwise the english configuration is used. The configuration is resolved when the index is created; changing the site default language later does not change existing indexes.

Queries must repeat the same expression to use the index:

  SELECT ... WHERE to_tsvector('english', body) @@ to_tsquery('english', 'search terms');

This applies to every way a GIN or GiST index is created: table schema definitions, the keys specification of addField() and changeField(), and the addGinIndex() and addGistIndex() methods.

For database driver developers

Drivers for database engines that support specialized index technologies can opt in by overriding two new protected methods of \Drupal\Core\Database\Schema:

• Schema::getSupportedIndexDefinitionClasses(): returns the list of index definition classes the driver supports. The default implementation only declares the regular Index class.
• Schema::createSpecializedIndex(): creates an index from a specialized index definition. The default implementation throws a \BadMethodCallException.

Contributed drivers extending a core driver's Schema class inherit this behavior. Drivers with their own addField() or changeField() implementations can use the new protected helper Schema::prepareKeysSpecification() to convert index definition objects in a keys specification to legacy arrays and to collect the specialized index definitions to create.