### PGRX Development Setup

Source: https://github.com/timescale/pgvectorscale/blob/main/TESTING.md

Start a PGRX PostgreSQL instance and install the pgvectorscale extension for development purposes. This involves starting a specific PostgreSQL version and then installing the extension with features.

```bash
cd pgvectorscale && cargo pgrx start pg17
```

```bash
cargo pgrx install --features pg17
```

--------------------------------

### End-to-End AI Document Search Example

Source: https://context7.com/timescale/pgvectorscale/llms.txt

A comprehensive example covering schema setup, data ingestion, index creation with specific parameters, and a parameterized similarity search with filtering. Requires the vectorscale extension.

```sql
-- 0. Setup
CREATE EXTENSION IF NOT EXISTS vectorscale CASCADE;

-- 1. Schema
CREATE TABLE articles (
    id         BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    title      TEXT         NOT NULL,
    body       TEXT,
    embedding  VECTOR(1536) NOT NULL,
    topic_ids  SMALLINT[]   DEFAULT '{}',
    published  BOOLEAN      DEFAULT false,
    created_at TIMESTAMPTZ  DEFAULT NOW()
);

CREATE TABLE topics (
    id   SMALLINT PRIMARY KEY,
    name TEXT NOT NULL
);

INSERT INTO topics VALUES
    (1, 'AI'),
    (2, 'PostgreSQL'),
    (3, 'Rust'),
    (4, 'Performance');

-- 2. Ingest sample data
INSERT INTO articles (title, body, embedding, topic_ids, published)
SELECT
    'Article ' || i,
    'Body of article ' || i,
    (SELECT array_agg((random() - 0.5)::float4) 
     FROM generate_series(1, 1536))::vector,
    ARRAY[(i % 4 + 1)::smallint, ((i + 1) % 4 + 1)::smallint],
    (i % 3 != 0)  -- ~67% published
FROM generate_series(1, 200000) i;

-- 3. Build index (high-quality settings, parallel-eligible)
SET maintenance_work_mem = '2GB';
SET diskann.force_parallel_workers = 4;

CREATE INDEX articles_embedding_idx
    ON articles
    USING diskann (embedding vector_cosine_ops, topic_ids)
    WITH (
        num_neighbors          = 75,
        search_list_size       = 120,
        max_alpha              = 1.4,
        storage_layout         = 'memory_optimized',
        num_bits_per_dimension = 2
    );

-- 4. Query: Find top-10 AI + PostgreSQL articles by semantic similarity
BEGIN;
SET LOCAL diskann.query_search_list_size = 150;
SET LOCAL diskann.query_rescore           = 100;

SELECT
    a.id,
    a.title,
    array_agg(t.name ORDER BY t.name) AS topics,
    a.embedding <=> $1::vector          AS distance
FROM articles a
JOIN topics t ON t.id = ANY(a.topic_ids)
WHERE a.topic_ids && ARRAY[1, 2]::smallint[]  -- AI or PostgreSQL
  AND a.published = true
GROUP BY a.id, a.title, a.embedding
ORDER BY a.embedding <=> $1::vector
LIMIT 10;
COMMIT;

-- Expected output (example):
--  id   | title      | topics                  | distance
-- ------+------------+-------------------------+----------
--  1234 | Article 1  | {AI,Performance}        | 0.0812
--  5678 | Article 5  | {AI,PostgreSQL}         | 0.0934
--  ...  | ...        | ...                     | ...

```

--------------------------------

### Install pgvectorscale from Source (Linux/macOS ARM)

Source: https://context7.com/timescale/pgvectorscale/llms.txt

Steps to install pgvectorscale from source on Linux/macOS ARM, including Rust installation, prerequisite setup, and cargo-pgrx usage.

```bash
# Install Rust
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh

# Prerequisites (Ubuntu/Debian)
sudo apt-get install make gcc pkg-config clang postgresql-server-dev-16 libssl-dev

# Clone repo and install cargo-pgrx
git clone https://github.com/timescale/pgvectorscale
cd pgvectorscale/pgvectorscale

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

# Initialize and install
cargo pgrx init --pg16 pg_config
cargo pgrx install --release
# If permission error:
# cargo pgrx install --sudo --release
```

--------------------------------

### Build and Install pgvectorscale

Source: https://github.com/timescale/pgvectorscale/blob/main/DEVELOPMENT.md

Builds the pgvectorscale extension in release mode and installs it. Use the --sudo flag if elevated permissions are required.

```bash
cargo pgrx install --release
```

```bash
cargo pgrx install --sudo --release
```

--------------------------------

### Install Development Packages

Source: https://github.com/timescale/pgvectorscale/blob/main/DEVELOPMENT.md

Installs essential development tools and libraries for building PostgreSQL extensions on Debian/Ubuntu systems.

```bash
sudo apt-get install make gcc pkg-config clang postgresql-server-dev-16 libssl-dev
```

--------------------------------

### Install pgvectorscale from Source

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Compile and install pgvectorscale using cargo-pgrx. Ensure Rust and the correct version of cargo-pgrx are installed. This process involves cloning the repository, initializing pgrx, and building the extension.

```bash
# 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')
cargo pgrx init --pg18 pg_config
# build and install pgvectorscale
cargo pgrx install --release
```

--------------------------------

### Setup and Run Python Tests

Source: https://github.com/timescale/pgvectorscale/blob/main/TESTING.md

Set up the Python testing environment by creating a virtual environment and then run all Python tests. Specific test categories can be targeted using pytest markers.

```bash
make test-python-setup
```

```bash
make test-python
```

```bash
pytest tests/ -m concurrency -v    # Multi-process concurrency tests
```

```bash
pytest tests/ -m integration -v    # Basic integration tests
```

--------------------------------

### Install Rust

Source: https://github.com/timescale/pgvectorscale/blob/main/DEVELOPMENT.md

Installs the Rust programming language and its package manager, Cargo, using the official installation script.

```shell
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh
```

--------------------------------

### Install Command Line Tools (macOS)

Source: https://github.com/timescale/pgvectorscale/blob/main/DEVELOPMENT.md

Installs the Xcode command line tools on macOS, which are often required for development tasks.

```bash
xcode-select --install
```

--------------------------------

### SQL Distance and Label Operators with Example

Source: https://context7.com/timescale/pgvectorscale/llms.txt

Demonstrates the usage of distance operators (<=>, <->, <#>) for ordering and retrieval, and the label overlap operator (&&). Ensure the vector extension is enabled.

```sql
-- Distance operators (ORDER BY usage)
-- <=>  cosine distance        (0.0 = identical, 2.0 = opposite)
-- <->  L2 / Euclidean distance
-- <#>  negative inner product (lower = more similar for normalized vectors)

-- Retrieve distance alongside results
SELECT
    id,
    contents,
    embedding <=> '[0.1, 0.2, 0.3]'::vector AS cosine_dist,
    embedding <-> '[0.1, 0.2, 0.3]'::vector AS l2_dist,
    embedding <#> '[0.1, 0.2, 0.3]'::vector AS neg_ip
FROM document_embedding
ORDER BY embedding <=> '[0.1, 0.2, 0.3]'::vector
LIMIT 5;

-- Label overlap operator
-- &&  smallint[] overlap (returns TRUE if arrays share any element)
SELECT id, labels
FROM documents
WHERE labels && ARRAY[1, 2]::smallint[];

-- Operator classes for CREATE INDEX
-- vector_cosine_ops    — cosine distance  (default; compatible with all storage layouts)
-- vector_l2_ops        — L2 distance      (compatible with all storage layouts)
-- vector_ip_ops        — inner product    (requires memory_optimized storage)
-- vector_smallint_label_ops — label overlap (used as second column in label index)

-- Example: explicit operator class specification
CREATE INDEX ON documents
    USING diskann (embedding vector_cosine_ops, labels vector_smallint_label_ops);
```

--------------------------------

### Create DiskANN Index with Custom Parameters

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Example of creating a diskann index with a specified number of neighbors. The `num_neighbors` parameter affects accuracy and traversal speed.

```sql
CREATE INDEX document_embedding_idx ON document_embedding
USING diskann (embedding) WITH(num_neighbors=50);
```

--------------------------------

### Install Cargo-pgrx

Source: https://github.com/timescale/pgvectorscale/blob/main/DEVELOPMENT.md

Installs the Cargo-pgrx tool, which is used for developing PostgreSQL extensions with Rust. Ensure it's built with the same compiler as your extension.

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

--------------------------------

### Label-Based Filtered Vector Search Setup

Source: https://context7.com/timescale/pgvectorscale/llms.txt

Defines tables for documents and categories, inserts sample data, and creates a DiskANN index supporting label filtering. Labels are stored as SMALLINT arrays.

```sql
-- Table with label support
CREATE TABLE documents (
    id         SERIAL PRIMARY KEY,
    embedding  VECTOR(768),
    labels     SMALLINT[],   -- Array of category IDs
    title      TEXT,
    status     TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
```

```sql
-- Human-readable label definitions (optional companion table)
CREATE TABLE categories (
    id          INTEGER PRIMARY KEY,
    name        TEXT,
    description TEXT
);
INSERT INTO categories VALUES
    (1, 'science',    'Scientific articles'),
    (2, 'technology', 'Technology news'),
    (3, 'business',   'Business content'),
    (4, 'health',     'Health and wellness');
```

```sql
-- Insert documents with labels
INSERT INTO documents (embedding, labels, title, status)
SELECT
    (SELECT array_agg((random() - 0.5)::float4) FROM generate_series(1, 768))::vector,
    ARRAY[(random() * 3 + 1)::int,
          (random() * 3 + 1)::int]::smallint[],
    'Document ' || i,
    CASE WHEN random() > 0.5 THEN 'published' ELSE 'draft' END
FROM generate_series(1, 50000) i;
```

```sql
-- Create DiskANN index with label column (second column in index)
CREATE INDEX documents_label_idx
    ON documents
    USING diskann (embedding vector_cosine_ops, labels);
```

--------------------------------

### Create DiskANN Index for Embeddings

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Create a StreamingDiskANN index on the embedding column for efficient similarity searches. This example uses cosine distance operations.

```postgresql
CREATE INDEX document_embedding_idx ON document_embedding
USING diskann (embedding vector_cosine_ops);
```

--------------------------------

### Check for pgvector Extension Availability

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Verify if the pgvector extension is available in your PostgreSQL instance. If not, follow the pgvector installation instructions.

```sql
SELECT * FROM pg_available_extensions WHERE name = 'vector';
```

--------------------------------

### Create Table with Embedding Column

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Define a table schema that includes a column for storing vector embeddings. This example uses a VECTOR type with a dimension of 1536.

```postgresql
CREATE TABLE IF NOT EXISTS document_embedding  (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    metadata JSONB,
    contents TEXT,
    embedding VECTOR(1536)
)
```

--------------------------------

### Create DiskANN Index with SBQ

Source: https://context7.com/timescale/pgvectorscale/llms.txt

Demonstrates creating DiskANN indexes using Statistical Binary Quantization (SBQ) with different bit configurations for compression. SBQ is enabled by default for memory_optimized storage.

```sql
-- SBQ is enabled by default (memory_optimized storage)
-- For 1536-dim vectors: ~6144 bytes → ~192 bytes (1-bit) or ~384 bytes (2-bit)
CREATE INDEX doc_sbq_default_idx
    ON document_embedding
    USING diskann (embedding vector_cosine_ops);
-- Auto-selects: 2 bits/dim if dims < 900, else 1 bit/dim

-- Explicit 2-bit SBQ (better accuracy, ~16× compression)
CREATE INDEX doc_sbq_2bit_idx
    ON document_embedding
    USING diskann (embedding vector_cosine_ops)
    WITH (
        storage_layout         = 'memory_optimized',
        num_bits_per_dimension = 2   -- Only valid for ≤900 dimensions
    );

-- Explicit 1-bit SBQ (maximum compression, ~32× compression)
CREATE INDEX doc_sbq_1bit_idx
    ON document_embedding
    USING diskann (embedding vector_cosine_ops)
    WITH (
        storage_layout         = 'memory_optimized',
        num_bits_per_dimension = 1
    );

-- Plain (uncompressed) storage — highest accuracy, most memory
CREATE INDEX doc_plain_idx
    ON document_embedding
    USING diskann (embedding vector_cosine_ops)
    WITH (storage_layout = 'plain');
-- Note: plain storage is NOT compatible with vector_ip_ops (inner product)

-- Tune rescoring to compensate for SBQ approximation at query time
SET diskann.query_rescore = 100;  -- Rescore top 100 with exact distances
SELECT id, contents, embedding <=> $1::vector AS distance
FROM document_embedding
ORDER BY embedding <=> $1::vector
LIMIT 10;
```

--------------------------------

### Configure and Enable Parallel Index Build

Source: https://context7.com/timescale/pgvectorscale/llms.txt

Shows how to set GUC parameters to enable and tune parallel index builds for DiskANN. Parallel builds require SBQ storage and are not available for indices with label columns.

```sql
-- Prerequisites: increase build memory
SET maintenance_work_mem = '4GB';

-- GUC parameters for parallel builds
SET diskann.min_vectors_for_parallel_build    = 65536; -- default; lower to enable for smaller tables
SET diskann.force_parallel_workers            = 8;     -- default -1 (auto); set to force worker count
SET diskann.parallel_flush_interval           = 0.05;  -- default; fraction of vectors before cache flush
SET diskann.parallel_initial_start_nodes_count = 1024; -- default; initial nodes before parallel phase

-- Create a large table
CREATE TABLE large_vectors (
    id        BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    embedding VECTOR(768)
);

INSERT INTO large_vectors (embedding)
SELECT (SELECT array_agg((random() - 0.5)::float4)
        FROM generate_series(1, 768))::vector
FROM generate_series(1, 1000000);

-- Build index in parallel (eligible: SBQ storage, no labels, ≥65536 vectors)
CREATE INDEX large_vectors_idx
    ON large_vectors
    USING diskann (embedding vector_cosine_ops)
    WITH (storage_layout = 'memory_optimized');

-- Force parallel with fewer initial serial nodes (for very large datasets)
SET diskann.min_vectors_for_parallel_build     = 10000;
SET diskann.force_parallel_workers             = 16;
SET diskann.parallel_initial_start_nodes_count = 512;

CREATE INDEX large_vectors_fast_idx
    ON large_vectors
    USING diskann (embedding vector_cosine_ops);
```

--------------------------------

### Connect to PostgreSQL Database

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

This bash command demonstrates how to connect to your PostgreSQL database using psql. Replace placeholders with your actual database credentials and connection details.

```bash
psql -d "postgres://<username>:<password>@<host>:<port>/<database-name>"
```

--------------------------------

### Initialize pgrx Development Environment

Source: https://github.com/timescale/pgvectorscale/blob/main/DEVELOPMENT.md

Initializes the pgrx development environment, configuring it for PostgreSQL version 16.

```bash
cargo pgrx init --pg16 pg_config
```

--------------------------------

### Create Fully Tuned High-Recall StreamingDiskANN Index

Source: https://context7.com/timescale/pgvectorscale/llms.txt

Creates a StreamingDiskANN index with specific tuning parameters for high recall, including num_neighbors, search_list_size, max_alpha, storage_layout, num_dimensions, and num_bits_per_dimension.

```sql
-- Fully tuned high-recall index
CREATE INDEX doc_embedding_precise_idx
    ON document_embedding
    USING diskann (embedding vector_cosine_ops)
    WITH (
        num_neighbors         = 100,   -- Max neighbors per node (default 50, range 10–1000)
        search_list_size      = 150,   -- Build-time search list (default 100, range 10–1000)
        max_alpha             = 1.5,   -- Graph pruning alpha (default 1.2, range 1.0–5.0)
        storage_layout        = 'memory_optimized', -- 'memory_optimized' or 'plain'
        num_dimensions        = 0,     -- 0 = index all dims; set for Matryoshka embeddings
        num_bits_per_dimension = 2     -- SBQ bits (auto if 0; >1 requires ≤900 dims)
    );
```

--------------------------------

### Connect to PostgreSQL Database

Source: https://github.com/timescale/pgvectorscale/blob/main/DEVELOPMENT.md

Connects to a PostgreSQL database using a connection string. Replace placeholders with your actual database credentials and details.

```bash
psql -d "postgres://<username>@<password>:<port>/<database-name>"
```

--------------------------------

### Configure Parallel Index Build Parameters

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Set parameters to control parallel index building. These settings can enable parallel builds for smaller datasets, force a specific number of workers, or adjust cache flushing frequency.

```sql
SET diskann.min_vectors_for_parallel_build = 10000;

-- Use 4 parallel workers regardless of PostgreSQL's automatic determination
SET diskann.force_parallel_workers = 4;

-- Adjust cache flushing frequency for memory usage (flush after processing 5% of vectors)
SET diskann.parallel_flush_interval = 0.05;

CREATE INDEX ON my_table USING diskann (embedding vector_cosine_ops);
```

--------------------------------

### Create StreamingDiskANN Index (Inner Product)

Source: https://context7.com/timescale/pgvectorscale/llms.txt

Creates a StreamingDiskANN index on a vector column using the inner product distance metric. This requires memory_optimized storage.

```sql
-- Inner product index (requires memory_optimized storage)
CREATE INDEX doc_embedding_ip_idx
    ON document_embedding
    USING diskann (embedding vector_ip_ops);
```

--------------------------------

### Create Memory-Efficient StreamingDiskANN Index

Source: https://context7.com/timescale/pgvectorscale/llms.txt

Creates a memory-efficient StreamingDiskANN index with maximum compression using specific tuning parameters for num_neighbors, storage_layout, and num_bits_per_dimension.

```sql
-- Memory-efficient index (maximum compression)
CREATE INDEX doc_embedding_compact_idx
    ON document_embedding
    USING diskann (embedding vector_cosine_ops)
    WITH (
        num_neighbors          = 30,
        storage_layout         = 'memory_optimized',
        num_bits_per_dimension = 1
    );
```

--------------------------------

### Run Python Tests with Custom Port

Source: https://github.com/timescale/pgvectorscale/blob/main/TESTING.md

Execute Python tests using a custom database port by setting the DB_PORT environment variable before running the script.

```bash
DB_PORT=28817 ./scripts/run-python-tests.sh
```

--------------------------------

### Create DiskANN Index with Label-Based Filtering

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Demonstrates creating a diskann index that supports label-based filtering. This involves specifying the vector operator and including 'labels' in the index definition.

```sql
CREATE INDEX document_embedding_idx ON document_embedding
USING diskann (embedding vector_cosine_ops, labels);
```

--------------------------------

### Create DiskANN Index with Partial-Dimension Indexing

Source: https://context7.com/timescale/pgvectorscale/llms.txt

Demonstrates creating DiskANN indexes that only index a leading subset of dimensions from full-length embedding vectors using `num_dimensions`. This is useful for Matryoshka embedding models.

```sql
-- Table stores full 1536-dim embeddings
CREATE TABLE matryoshka_docs (
    id        SERIAL PRIMARY KEY,
    embedding VECTOR(1536),
    contents  TEXT
);

INSERT INTO matryoshka_docs (embedding, contents)
SELECT
    (SELECT array_agg((random() - 0.5)::float4)
     FROM generate_series(1, 1536))::vector,
    'Doc ' || i
FROM generate_series(1, 50000) i;

-- Index only first 768 dimensions (half the storage, similar recall)
CREATE INDEX matryoshka_768_idx
    ON matryoshka_docs
    USING diskann (embedding vector_cosine_ops)
    WITH (num_dimensions = 768);

-- Index only first 256 dimensions (maximum speed/compression trade-off)
CREATE INDEX matryoshka_256_idx
    ON matryoshka_docs
    USING diskann (embedding vector_cosine_ops)
    WITH (num_dimensions = 256);

-- Queries work normally; the index uses only the first num_dimensions
SELECT id, contents, embedding <=> $1::vector AS distance
FROM matryoshka_docs
ORDER BY embedding <=> $1::vector
LIMIT 10;
```

--------------------------------

### Clone pgvectorscale Repository

Source: https://github.com/timescale/pgvectorscale/blob/main/DEVELOPMENT.md

Clones the pgvectorscale GitHub repository and navigates into the extension's subdirectory.

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

--------------------------------

### Create StreamingDiskANN Index (L2 Distance)

Source: https://context7.com/timescale/pgvectorscale/llms.txt

Creates a StreamingDiskANN index on a vector column using the L2 (Euclidean) distance metric.

```sql
-- L2 (Euclidean) distance index
CREATE INDEX doc_embedding_l2_idx
    ON document_embedding
    USING diskann (embedding vector_l2_ops);
```

--------------------------------

### Create StreamingDiskANN Index (Cosine Distance)

Source: https://context7.com/timescale/pgvectorscale/llms.txt

Creates a basic StreamingDiskANN index on a vector column using cosine distance. This is the default distance metric.

```sql
-- Basic table setup
CREATE EXTENSION IF NOT EXISTS vectorscale CASCADE;

CREATE TABLE document_embedding (
    id       BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    metadata JSONB,
    contents TEXT,
    embedding VECTOR(1536)
);

-- Insert sample rows
INSERT INTO document_embedding (contents, embedding)
SELECT
    'Document ' || i,
    (SELECT array_agg((random() - 0.5)::float4)
     FROM generate_series(1, 1536))::vector
FROM generate_series(1, 100000) i;

-- Cosine distance index (default) with default parameters
CREATE INDEX doc_embedding_cosine_idx
    ON document_embedding
    USING diskann (embedding vector_cosine_ops);
```

--------------------------------

### Query with Label Names using JOIN

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Perform a vector search and join with the label_definitions table to retrieve meaningful label names for the results. This query aggregates label names for each document.

```postgresql
-- Find similar science documents and include label information
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]  -- Science label
GROUP BY d.id, d.embedding, d.labels, d.status, d.created_at
ORDER BY d.embedding <=> '[...]'
LIMIT 10;
```

--------------------------------

### Verify SDKROOT Variable (macOS)

Source: https://github.com/timescale/pgvectorscale/blob/main/DEVELOPMENT.md

Verifies that the SDKROOT environment variable has been set correctly by displaying its value.

```bash
$ env | grep SDKROOT
SDKROOT=/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk
```

--------------------------------

### Run Rust Tests with PGRX

Source: https://github.com/timescale/pgvectorscale/blob/main/TESTING.md

Execute all Rust tests or a specific test using the cargo pgrx test command. Ensure you are in the pgvectorscale directory.

```bash
cd pgvectorscale && cargo pgrx test pg17

```

```bash
cd pgvectorscale && cargo pgrx test pg17 test_name
```

--------------------------------

### Create DiskANN Index with Labels

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Create a StreamingDiskANN index on the embedding column, including the labels column for efficient label-based filtering. The `&&` overlap operator is used for this.

```postgresql
CREATE INDEX ON documents USING diskann (embedding vector_cosine_ops, labels);
```

--------------------------------

### Create Labels Definition Table

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Define a table to store semantic meanings for integer labels, including names and descriptions. This table can also store additional metadata in a JSONB column.

```postgresql
CREATE TABLE label_definitions (
    id INTEGER PRIMARY KEY,
    name TEXT,
    description TEXT,
    attributes JSONB  -- Can store additional metadata about the label
);

-- Insert some label definitions
INSERT INTO label_definitions (id, name, description, attributes) VALUES
(1, 'science', 'Scientific content', '{"domain": "academic", "confidence": 0.95}'),
(2, 'technology', 'Technology-related content', '{"domain": "technical", "confidence": 0.92}'),
(3, 'business', 'Business and finance content', '{"domain": "commercial", "confidence": 0.88}');
```

--------------------------------

### Basic Similarity Search Queries

Source: https://context7.com/timescale/pgvectorscale/llms.txt

Perform approximate nearest neighbor search using cosine, L2, or inner product operators. Use a materialized CTE for strictly ordered results if needed.

```sql
-- Basic cosine similarity search (top-10 nearest neighbors)
SELECT id, contents, embedding <=> '[0.1, 0.2, ...]'::vector AS distance
FROM document_embedding
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;
```

```sql
-- L2 (Euclidean) distance search
SELECT id, contents, embedding <-> $1::vector AS distance
FROM document_embedding
ORDER BY embedding <-> $1::vector
LIMIT 10;
```

```sql
-- Inner product search (for normalized vectors)
SELECT id, contents, embedding <#> $1::vector AS distance
FROM document_embedding
ORDER BY embedding <#> $1::vector
LIMIT 10;
```

```sql
-- With non-vector WHERE filter (post-filtering)
SELECT id, contents
FROM document_embedding
WHERE (metadata->>'status') = 'published'
  AND (metadata->>'created_at')::timestamptz > NOW() - INTERVAL '30 days'
ORDER BY embedding <=> $1::vector
LIMIT 10;
```

```sql
-- Strict ordering via materialized CTE
WITH results AS MATERIALIZED (
    SELECT id, contents, embedding <=> $1::vector AS distance
    FROM document_embedding
    ORDER BY embedding <=> $1::vector
    LIMIT 20
)
SELECT * FROM results ORDER BY distance;
```

--------------------------------

### Generate SQL Schema Update

Source: https://github.com/timescale/pgvectorscale/wiki/Release-process

Use this command to generate the SQL schema update file for a specific version. Ensure you replace placeholders with actual version numbers.

```bash
cargo pgrx schema > sql/timescale_vector--<PREVIOUS VERSION>--<NEW VERSION>.sql
```

--------------------------------

### Set StreamingDiskANN Query Search List Size Locally

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Use the `SET LOCAL` command within a transaction to temporarily adjust `diskann.query_search_list_size`. This setting is reset after the transaction ends.

```sql
BEGIN;
SET LOCAL diskann.query_search_list_size= 10;
SELECT * FROM document_embedding ORDER BY embedding <=> $1 LIMIT 10
COMMIT;
```

--------------------------------

### Create pgvectorscale Extension

Source: https://github.com/timescale/pgvectorscale/blob/main/DEVELOPMENT.md

Enables the pgvectorscale extension within the connected PostgreSQL database. The CASCADE option ensures that any dependent objects are also created.

```postgresql
CREATE EXTENSION IF NOT EXISTS vectorscale CASCADE;
```

--------------------------------

### Insert Document with Label IDs

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Insert a document into the documents table, assigning it integer label IDs that correspond to entries in the label_definitions table.

```postgresql
-- Insert a document with science and technology labels
INSERT INTO documents (embedding, labels)
VALUES ('[...]', ARRAY[1, 2]);
```

--------------------------------

### Label-Accelerated Vector Search Queries

Source: https://context7.com/timescale/pgvectorscale/llms.txt

Perform vector similarity searches combined with label filtering. The '&&' operator on indexed SMALLINT arrays accelerates filtering. Supports single or multiple label matches and dynamic label ID resolution.

```sql
-- Query: Science OR Technology documents (label-accelerated)
SELECT d.id, d.title, array_agg(c.name) AS categories
FROM documents d
JOIN categories c ON c.id = ANY(d.labels)
WHERE d.labels && ARRAY[1, 2]::smallint[]
ORDER BY d.embedding <=> '[...]'::vector
LIMIT 10;
```

```sql
-- Query: Single category, published only (label filter + post-filter)
SELECT id, title, status
FROM documents
WHERE labels && ARRAY[3]::smallint[]  -- Business
  AND status = 'published'
ORDER BY embedding <=> $1::vector
LIMIT 20;
```

```sql
-- Query: Resolve label names to IDs dynamically
SELECT d.*
FROM documents d
WHERE d.labels && (
    SELECT array_agg(id)::smallint[]
    FROM categories
    WHERE name IN ('science', 'technology')
)
ORDER BY d.embedding <=> $1::vector
LIMIT 10;
```

--------------------------------

### Set StreamingDiskANN Query Rescore Parameter

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Use the SET command to adjust the `diskann.query_rescore` parameter for accuracy vs. query speed trade-off. This setting applies to the entire session.

```sql
SET diskann.query_rescore = 400;
```

--------------------------------

### Query-Time Accuracy/Speed Tuning

Source: https://context7.com/timescale/pgvectorscale/llms.txt

Tune accuracy vs. latency using GUC parameters. `diskann.query_search_list_size` controls candidate exploration, and `diskann.query_rescore` rescores top candidates with exact distances. Settings can be applied session-level or transaction-local.

```sql
-- Session-level: high recall settings
SET diskann.query_search_list_size = 200;  -- default 100, range 1–10000
SET diskann.query_rescore           = 150; -- default 50,  range 0–1000

SELECT id, contents, embedding <=> $1::vector AS distance
FROM document_embedding
ORDER BY embedding <=> $1::vector
LIMIT 10;
```

```sql
-- Transaction-local: low-latency settings (resets after COMMIT)
BEGIN;
SET LOCAL diskann.query_search_list_size = 50;
SET LOCAL diskann.query_rescore           = 0;  -- 0 disables rescoring entirely

SELECT id, contents
FROM document_embedding
ORDER BY embedding <=> $1::vector
LIMIT 100;
COMMIT;
```

```sql
-- Balanced defaults (equivalent to no SET)
BEGIN;
SET LOCAL diskann.query_search_list_size = 100;
SET LOCAL diskann.query_rescore           = 50;

SELECT id, contents, embedding <=> $1::vector AS distance
FROM document_embedding
ORDER BY embedding <=> $1::vector
LIMIT 10;
COMMIT;
```

--------------------------------

### Create Table for Label-Based Filtering

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Define a table with an embedding column and a labels array for optimized filtering. Ensure label values are within the PostgreSQL smallint range.

```postgresql
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    embedding VECTOR(1536),
    labels SMALLINT[],  -- Array of category labels
    status TEXT,
    created_at TIMESTAMPTZ
);
```

--------------------------------

### Set SDKROOT Environment Variable (macOS)

Source: https://github.com/timescale/pgvectorscale/blob/main/DEVELOPMENT.md

Explicitly sets the SDKROOT environment variable to the macOS SDK path, which can resolve 'stdio.h' not found errors.

```bash
export SDKROOT=$(xcrun --sdk macosx --show-sdk-path)
```

--------------------------------

### Filter by Label Names using Subquery

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Query documents by filtering on label names by using a subquery to convert the names into their corresponding integer IDs. This allows filtering based on semantic meaning.

```postgresql
-- Find documents with specific label names
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;
```

--------------------------------

### Perform Similarity Search

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Retrieve the 10 closest embeddings to a given query vector using the created index. Supports cosine, L2, and inner product distances.

```postgresql
SELECT *
FROM document_embedding
ORDER BY embedding <=> $1
LIMIT 10;
```

--------------------------------

### Perform Arbitrary WHERE Clause Filtering

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Execute a vector search query with arbitrary WHERE clause conditions, such as status and date range. These conditions are applied as post-filtering after the vector search.

```postgresql
-- Find similar documents with specific status and date range
SELECT * FROM documents
WHERE status = 'active' AND created_at > '2024-01-01'
ORDER BY embedding <=> '[...]'
LIMIT 10;
```

--------------------------------

### Perform Label-Filtered Vector Search

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Execute a vector search query that filters results based on specific labels using the `&&` array overlap operator. This method offers significantly lower latency.

```postgresql
-- Find similar documents with specific labels
SELECT * FROM documents
WHERE labels && ARRAY[1, 3]  -- Documents with label 1 OR 3
ORDER BY embedding <=> '[...]'
LIMIT 10;
```

--------------------------------

### Set maintenance_work_mem for Index Builds

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

Increase maintenance_work_mem to improve memory usage during index builds, especially for large datasets. The default is 64MB.

```sql
SET maintenance_work_mem = '2GB';
```

--------------------------------

### Achieve Strict Ordering with Materialized CTE

Source: https://github.com/timescale/pgvectorscale/blob/main/README.md

When strict ordering by distance is required, use a materialized CTE to first select and order results, then re-order the CTE results. This bypasses the relaxed ordering of the diskann index.

```sql
WITH relaxed_results AS MATERIALIZED (
    SELECT id, embedding <=> '[1,2,3]' AS distance
    FROM items
    WHERE category_id = 123
    ORDER BY distance
    LIMIT 5
) SELECT * FROM relaxed_results ORDER BY distance;
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.