Skip to main content
Version: 2.8.0

Indexing

For performance reasons, you may want to add indexes to your database tables. These are added in the YAML-files defining the serializable objects.

Add an index​

To add an index, add an indexes section to the YAML-file. The indexes section is a map where the key is the name of the index and the value is a map with the index details.

class: Company
table: company
fields:
name: String
indexes:
company_name_idx:
fields: name

The fields keyword holds a comma-separated list of column names. These are the fields upon which the index is created. Note that the index can contain several fields.

class: Company
table: company
fields:
name: String
foundedAt: DateTime
indexes:
company_idx:
fields: name, foundedAt

Making fields unique​

Adding a unique index ensures that the value or combination of values stored in the fields are unique for the table. This can be useful for example if you want to make sure that no two companies have the same name.

class: Company
table: company
fields:
name: String
indexes:
company_name_idx:
fields: name
unique: true

The unique keyword is a bool that can toggle the index to be unique, the default is set to false. If the unique keyword is applied to a multi-column index, the index will be unique for the combination of the fields.

Specifying index type​

It is possible to add a type key to specify the index type.

class: Company
table: company
fields:
name: String
indexes:
company_name_idx:
fields: name
type: brin

If no type is specified the default is btree. All PostgreSQL index types are supported, btree, hash, gist, spgist, gin, brin.

Vector indexes​

To enhance the performance of vector similarity search, it is possible to create specialized vector indexes on Vector fields. Serverpod supports both HNSW and IVFFLAT index types with full parameter specification.

info

Each vector index can only be created on a single Vector field. It is not possible to create a vector index on multiple fields of any kind.

HNSW indexes​

Hierarchical Navigable Small World (HNSW) indexes provide fast approximate nearest neighbor search:

class: Document
table: document
fields:
content: String
embedding: Vector(1536)
indexes:
document_embedding_hnsw_idx:
fields: embedding
type: hnsw
distanceFunction: cosine
parameters:
m: 16
ef_construction: 64

Available HNSW parameters:

  • m: Maximum number of bi-directional links for each node (default: 16)
  • efConstruction: Size of the dynamic candidate list (default: 64)

IVFFLAT indexes​

Inverted File with Flat compression (IVFFLAT) indexes are suitable for large datasets:

class: Document
table: document
fields:
content: String
embedding: Vector(1536)
indexes:
document_embedding_ivfflat_idx:
fields: embedding
type: ivfflat
distanceFunction: innerProduct
parameters:
lists: 100

Available IVFFLAT parameters:

  • lists: Number of inverted lists (default: 100)

Distance functions​

Supported distance functions for vector indexes (distanceFunction parameter):

Distance FunctionDescriptionUse Case
l2Euclidean distanceDefault for most embeddings
innerProductInner productWhen vectors are normalized
cosineCosine distanceText embeddings
l1Manhattan or taxicab distanceSparse/high-dimensional data
tip

If more than one distance function is going to be frequently used on the same vector field, consider creating one index for each distance function to ensure optimal performance.

For more details on vector indexes and its configuration, refer to the pgvector extension documentation.