Vector & Partially Unique Indexes

Relevant source files

The following files were used as context for generating this wiki page:

• src/mysql/writer/index.rs
• CHANGELOG.md
• Cargo.toml
• src/sqlite/def/column.rs
• src/sqlite/def/table.rs
• tests/partially_unique_index/Cargo.toml

SeaSchema provides specialized support for modern database indexing features, including Vector indexes for AI/ML applications and Partially Unique indexes for optimized data constraints. These features are implemented across the definition, discovery, and writer modules to ensure seamless schema management for MySQL, PostgreSQL, and SQLite.

The project utilizes conditional compilation features to manage backend-specific index types, such as the planetscale feature for MySQL vectors and the postgres-vector feature for PostgreSQL extensions. This architecture allows the library to support advanced indexing strategies while maintaining a clean core definition.

Vector Indexes

Vector indexing is a critical feature for high-performance similarity searches in vector databases. SeaSchema implements support for these in both MySQL and PostgreSQL backends through specific feature flags and specialized writer logic.

MySQL Vector Support

In MySQL, Vector indexes are specifically targeted at platforms like PlanetScale. The IndexType enum includes a Vector variant, which is enabled via the planetscale feature. When writing a schema, the MySQL index writer identifies the Vector type and exports it as a custom index type using SeaQuery.

Sources: [src/mysql/writer/index.rs:47-52](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/src/mysql/writer/index.rs#L47-L52), [CHANGELOG.md:29-30](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/CHANGELOG.md#L29-L30), [Cargo.toml:51](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/Cargo.toml#L51)

PostgreSQL Vector Support

PostgreSQL support for vector indexes is managed through the postgres-vector feature. This includes mapping the postgres-vector type during discovery and implementing support for the PostgreSQL vector extension.

Sources: [CHANGELOG.md:25-26, 31](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/CHANGELOG.md#L25-L26), [Cargo.toml:44](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/Cargo.toml#L44)

Vector Index Processing Flow

The following diagram illustrates how the IndexInfo::write method processes different index types, specifically handling the Vector type for MySQL.

The diagram shows the logic path for generating SQL index statements, highlighting the branching for Vector types under specific feature gates.
Sources: [src/mysql/writer/index.rs:4-55](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/src/mysql/writer/index.rs#L4-L55)

Partially Unique Indexes

Partially Unique indexes (also known as filtered indexes) allow for uniqueness constraints on a subset of table rows, typically defined by a WHERE clause. SeaSchema supports the discovery and definition of these across multiple backends.

SQLite Implementation

In SQLite, IndexInfo includes a partial field (represented as an i64) which indicates whether the index is a partial index. During schema discovery, SeaSchema queries PRAGMA index_list(table_name), which returns a partial column. This value is used to populate the internal PartialIndexInfo and subsequently the IndexInfo structure.

Sources: [src/sqlite/def/column.rs:106, 122, 131](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/src/sqlite/def/column.rs#L106)

PostgreSQL Implementation

PostgreSQL support for partial indexes includes the is_partial property for IndexInfo. This allows the library to correctly identify and replicate indexes that only cover a portion of the table data.

Sources: [CHANGELOG.md:12](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/CHANGELOG.md#L12)

Index Data Structures

The internal representation of indexes varies slightly to accommodate the metadata required for partial and vector types.

Field	Type	Description	Backends
name	String	Unique identifier for the index	All
idx_type	IndexType	Enum: BTree, Vector, Hash, etc.	MySQL, Postgres
unique	bool	Whether the index enforces uniqueness	All
partial	i64 / bool	Flag indicating a filtered index	SQLite, Postgres
columns	Vec<String>	Columns participating in the index	SQLite

Sources: [src/sqlite/def/column.rs:98-108](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/src/sqlite/def/column.rs#L98-L108), [src/mysql/writer/index.rs:40-54](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/src/mysql/writer/index.rs#L40-L54)

Architecture and Discovery

The discovery of indexes involves querying database-specific system catalogs or PRAGMAs to reconstruct the IndexInfo object.

SQLite Discovery Logic

SQLite discovery uses a multi-step process:

1. Querying PRAGMA index_list to get basic info and partial status.
2. Invoking get_single_indexinfo to map specific column names to the index via PRAGMA index_info.
3. Filtering by origin (e.g., 'u' for unique constraints, 'c' for standard indexes).

This sequence illustrates the iterative process SeaSchema uses to gather complete index metadata including partial status in SQLite.
Sources: [src/sqlite/def/table.rs:78-124](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/src/sqlite/def/table.rs#L78-L124)

Feature Flags for Extended Support

The Cargo.toml file defines features that enable advanced index types.

• planetscale: Enables IndexType::Vector for MySQL.
• postgres-vector: Enables PostgreSQL vector extension support.
• discovery: Enables the ability to read existing indexes from a live database.

Sources: [Cargo.toml:44, 51, 55](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/Cargo.toml#L44)

Summary

SeaSchema provides robust handling for specialized indexing through a combination of backend-specific features and shared data definitions. By supporting Vector types for modern search requirements and partial flags for filtered uniqueness constraints, the library allows developers to manage complex schema requirements across MySQL, PostgreSQL, and SQLite with consistent Rust-based definitions.

Sources: [CHANGELOG.md:12, 29](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/CHANGELOG.md#L12), [src/mysql/writer/index.rs:47-53](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/src/mysql/writer/index.rs#L47-L53), [src/sqlite/def/column.rs:106](https://github.com/marlon-costa-dc/sea-schema/blob/60ddf8b12634d04955b908c03f5900412a47f5c0/src/sqlite/def/column.rs#L106)