Filters

Applies to VAST Cluster 5.5.

Most real searches combine "closest to this vector" with conditions on other columns: a language, a date range, a tenant. In VAST Cluster 5.5 you write those conditions as an ordinary WHERE clause on the vector search. This guide shows how filters behave with the index, and how to filter results by the user's permissions.

Run the code blocks in order in one Python session. Below each block is the output you should see.

Set up an example table

import os
import re
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/vs_guide_filters"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def sql(query):
    with conn.cursor() as cur:
        cur.execute(query)
        return cur.fetchall()

def try_sql(query):
    try:
        sql(query)
        return "OK"
    except Exception as e:
        msg = str(e).replace('\\"', '"')
        m = re.search(r'failed with: (.*?)", request_id', msg, re.S) or re.search(r"internal error: ([^)]*)\)", msg)
        return m.group(1) if m else msg

sql(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
sql(f"""CREATE TABLE IF NOT EXISTS {SCHEMA}.chunks (
          id BIGINT, chunk_id INTEGER, language VARCHAR, published DATE, title VARCHAR,
          embedding VECTOR(FLOAT, 4))
        VECTOR CLUSTER BY embedding (metric = 'cosine')""")
sql(f"""INSERT INTO {SCHEMA}.chunks VALUES
    (1, 0, 'en', DATE '2026-01-10', 'apples',   [0.9, 0.1, 0.0, 0.0]::FLOAT[4]),
    (1, 1, 'en', DATE '2026-01-10', 'apples',   [0.8, 0.2, 0.1, 0.0]::FLOAT[4]),
    (2, 0, 'fr', DATE '2026-02-03', 'pommes',   [0.9, 0.2, 0.0, 0.0]::FLOAT[4]),
    (3, 0, 'en', DATE '2026-03-15', 'bananas',  [0.1, 0.9, 0.1, 0.0]::FLOAT[4]),
    (4, 0, 'de', DATE '2026-04-01', 'Äpfel',    [0.85, 0.15, 0.0, 0.0]::FLOAT[4]),
    (5, 0, 'en', DATE '2026-05-20', 'cherries', [0.6, 0.0, 0.7, 0.0]::FLOAT[4])""")
print(sql(f"SELECT count(*) FROM {SCHEMA}.chunks"))

[(6,)]

Filter with WHERE

Add a WHERE clause to the search. Keep the rest of the query shape: one ascending distance in ORDER BY, a LIMIT, no OFFSET. Any column can be filtered, with the usual operators:

q = "[1.0, 0.1, 0.0, 0.0]::FLOAT[4]"
search = f"""SELECT id, chunk_id, language, title,
                    round(array_cosine_distance(embedding::FLOAT[4], {q})::DOUBLE, 4) AS distance
             FROM {SCHEMA}.chunks
             WHERE {{where}}
             ORDER BY distance LIMIT 3"""

for where in ("language = 'en'",
              "language IN ('fr', 'de')",
              "published >= DATE '2026-03-01' AND chunk_id = 0"):
    print(where)
    for row in sql(search.format(where=where)):
        print("   ", row)

language = 'en'
    (1, 0, 'en', 'apples', 0.0001)
    (1, 1, 'en', 'apples', 0.0177)
    (5, 0, 'en', 'cherries', 0.3524)
language IN ('fr', 'de')
    (4, 0, 'de', 'Äpfel', 0.0028)
    (2, 0, 'fr', 'pommes', 0.0071)
published >= DATE '2026-03-01' AND chunk_id = 0
    (4, 0, 'de', 'Äpfel', 0.0028)
    (5, 0, 'en', 'cherries', 0.3524)
    (3, 0, 'en', 'bananas', 0.7925)

How a filter affects the search: the search visits the index's clusters closest first, and checks the filter on the rows it reads. At the lowest level it keeps reading clusters until it has found enough rows that pass the filter. So:

• A filter that most rows pass costs little.
• A very selective filter (one tenant among thousands, one rare tag) makes the search read many more clusters. In a test with a filter that one row in a hundred passed, the search still returned the full LIMIT and the exact filtered neighbours, but took about two and a half times as long. Measure recall and latency with your real filters; see measure recall and latency.

Schematic: a selective filter makes the search read more clusters before it fills the LIMIT.

A design option for one selective condition. If nearly every query filters on the same selective condition, for example a tenant ID, consider a separate table for each value of it. Each search then reads only that tenant's index, instead of reading through other tenants' rows to fill LIMIT. Whether it's worth it depends on how many values there are and how selective each one is; measure both designs.

Plan the filter columns before you create the table

You can't add or drop columns in a vector-indexed table (5.5.0 release notes). So every column you'll filter on, and the vastdb_s3_path_auth column if you'll filter by permissions, has to be in the table from the start. Design a vector table lists what you can and can't change, with the cluster's messages.

Permissions as filters

Row and column security rules in your platform team's identity policies also filter vector searches, so that each user sees only the rows they're allowed to see (Vector search). For retrieval-augmented generation the common rule is the row filter by S3 path: a row is returned only if the user may read (s3:GetObject) every S3 object listed in the row's vastdb_s3_path_auth column (Row and column security).

The policy statement has the form:

{
  "Effect": "RowColumnSecurity",
  "RowFilterByS3PathAuth": "True"
}

What it means for your table and your application:

• The table needs a vastdb_s3_path_auth column holding the path of each chunk's source object, such as "my-bucket/docs/report.pdf". Add it when you create the table.
• The rule filters for the user running the query. If your application queries with one service key on behalf of many users, it must impersonate each user (vast.db.end_user); the search then returns only the rows that end user may read. The service key's user then needs s3:EndUserImpersonation and s3:TabularGetRowColumnSecurity.
• A filtered user still gets LIMIT k rows when they may read at least k: the nearest rows they're allowed to see (security).
• With the Python SDK 2.1.0, insert rows into a table with vastdb_s3_path_auth in batches of a few thousand rows. A batch larger than the SDK's request size fails with trying to insert to a table without values for column=vastdb_s3_path_auth, because the SDK splits it and sends that column separately.
• Permission rules aren't a substitute for WHERE. Use them for access control, and WHERE for what the query is about.

Admin task: send to your platform team. Ask for the identity policy with the row and column security statement, and for the impersonation permissions if your application needs them. See Row and column security and security, which also says which other row and column rules work with the Query Engine.

A vector-indexed table can have the vastdb_s3_path_auth column:

print(try_sql(f"""CREATE TABLE IF NOT EXISTS {SCHEMA}.secured (
          id BIGINT, vastdb_s3_path_auth VARCHAR, embedding VECTOR(FLOAT, 4))
        VECTOR CLUSTER BY embedding (metric = 'cosine')"""))

OK

The row filter hides a chunk from users who may not read its source document, for Query Engine searches and for SDK select() alike. Paths must be quoted ("bucket/object"): an unquoted path is rejected on insert with InvalidArgument. A GetObject deny without the row-filter statement doesn't hide rows. The worked example shows all three.

Clean up

for name in ("chunks", "secured"):
    sql(f"DROP TABLE IF EXISTS {SCHEMA}.{name}")
sql(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()
print("cleaned up")

cleaned up

Next steps

• Query design
• Design a vector table: plan the columns you'll filter on.
• Security
• RAG retrieval