pgvectorscale

To maintain high performance while using human-readable labels, store integer IDs in your main table and map them to a separate label_definitions table. You can then use joins or subqueries to filter by name or include names in your results.

-- 1. Create lookup table
CREATE TABLE label_definitions (
    id INTEGER PRIMARY KEY,
    name TEXT,
    description TEXT,
    attributes JSONB
);

-- 2. Query by joining for semantic names
SELECT d.*, array_agg(l.name) as label_names
FROM documents d
JOIN label_definitions l ON l.id = ANY(d.labels)
WHERE d.labels && ARRAY[1]  -- Filter by ID 1
GROUP BY d.id, d.embedding, d.labels, d.status, d.created_at
ORDER BY d.embedding <=> '[...]'
LIMIT 10;

-- 3. Query by converting names to IDs via subquery
SELECT d.*
FROM documents d
WHERE d.labels && (
    SELECT array_agg(id)
    FROM label_definitions
    WHERE name IN ('science', 'business')
)
ORDER BY d.embedding <=> '[...]'
LIMIT 10;

For high-performance vector search with metadata filtering, use label-based filtering. This requires storing labels as a smallint[] array and including that column in the diskann index definition. This method uses the && (array overlap) operator for efficient filtering.

Requirements:

• Labels must be within the PostgreSQL smallint range (-32768 to 32767).
• The index must be created using the diskann access method and explicitly include the label column.

Workflow:

1. Create a table with an embedding column and a smallint[] labels column.
2. Create a diskann index specifying both the embedding and the labels column.
3. Query using the && operator to find documents containing any of the specified labels.

-- 1. Create table
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    embedding VECTOR(1536),
    labels SMALLINT[],
    status TEXT,
    created_at TIMESTAMPTZ
);

-- 2. Create index with labels
CREATE INDEX ON documents USING diskann (embedding vector_cosine_ops, labels);

-- 3. Query using overlap operator (&&)
SELECT * FROM documents
WHERE labels && ARRAY[1, 3]  -- Documents with label 1 OR 3
ORDER BY embedding <=> '[...]'
LIMIT 10;

Follow these steps to clone, build, and install the pgvectorscale extension into your PostgreSQL environment:

1. Clone the repository and enter the extension directory:

   git clone https://github.com/timescale/pgvectorscale && \
   cd pgvectorscale/pgvectorscale

2. Install cargo-pgrx matching your current pgrx version:

   cargo install --locked cargo-pgrx --version $(cargo metadata --format-version 1 | jq -r '.packages[] | select(.name == "pgrx") | .version')

   Note: Reinstall cargo-pgrx whenever you update Rust to ensure it uses the same compiler as pgvectorscale.

3. Initialize the pgrx environment for PostgreSQL 16:

   cargo pgrx init --pg16 pg_config

4. Build and install the extension:

   cargo pgrx install --release

   If the installation destination requires elevated permissions, use the --sudo flag:

   cargo pgrx install --sudo --release

5. Enable the extension in your database: Connect via psql and run:

   CREATE EXTENSION IF NOT EXISTS vectorscale CASCADE;

git clone https://github.com/timescale/pgvectorscale && \
cd pgvectorscale/pgvectorscale

# Install cargo-pgrx
cargo install --locked cargo-pgrx --version $(cargo metadata --format-version 1 | jq -r '.packages[] | select(.name == "pgrx") | .version')

# Initialize
cargo pgrx init --pg16 pg_config

# Build and install
cargo pgrx install --release

# Enable in SQL
CREATE EXTENSION IF NOT EXISTS vectorscale CASCADE;

You can compile and install pgvectorscale into an existing PostgreSQL server.

Note: Building on macOS X86 (Intel) is currently unsupported. Use an ARM-based Mac, Linux, or Docker instead.

Steps:

1. Install Rust.
2. Clone the repository.
3. Install cargo-pgrx matching your pgrx version.
4. Initialize pgrx with your pg_config.
5. Build and install the extension.
6. Create the extension in your database using CREATE EXTENSION IF NOT EXISTS vectorscale CASCADE;.

# install rust
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh

# download pgvectorscale
cd /tmp
git clone --branch <version> https://github.com/timescale/pgvectorscale
cd pgvectorscale/pgvectorscale

# install cargo-pgrx with the same version as pgrx
cargo install --locked cargo-pgrx --version $(cargo metadata --format-version 1 | jq -r '.packages[] | select(.name == "pgrx") | .version')

# initialize pgrx (example for pg18)
cargo pgrx init --pg18 pg_config

# build and install pgvectorscale
cargo pgrx install --release