En

Preparing indexes before upgrading to 2.5.1#

This procedure applies when upgrading from 2.5.0 to 2.5.1. For another build with an equivalent partitioned database schema, use it only after the administrator and support confirm that it applies. If the current version is earlier than 2.5.0, such as 2.4.10, do not run the SQL commands below without an agreed upgrade path.

For this upgrade, prepare the indexes before running update.sh or the manual migrations for version 2.5.1. The update script starts migrations but does not perform this preparation. The migration checks that the indexes are ready and will not finish without them.

A database administrator should perform this work. Building indexes on a large database may take time; make a backup and schedule an upgrade window. You need psql connected to the same database used by Sherpa AI Server and a role allowed to create indexes on the tables. Create the SQL files on the machine where you will run psql: the client installation archive does not contain them.

1. Check the database connection#

Replace DB_HOST, DB_OWNER, and SHERPA_DB with your actual connection settings. If you run psql inside the database container, use connection settings available there. Confirm the database and user before continuing:

export PGHOST='DB_HOST'
export PGPORT='5432'
export PGUSER='DB_OWNER'
export PGDATABASE='SHERPA_DB'
psql -X -v ON_ERROR_STOP=1 -c 'SELECT current_database(), current_user;'

For a standard installation from the client archive, you can use psql inside the PostgreSQL container. From the directory containing docker-compose.yml and .env, check the connection without installing psql on the host:

docker compose exec -T aiserver-pg sh -c 'psql -X -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c "SELECT current_database(), current_user;"'

Check that public.embeddings is a partitioned table with an account_id column. Use the command for your connection method:

psql -X -v ON_ERROR_STOP=1 -Atc "SELECT EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace JOIN pg_attribute a ON a.attrelid = c.oid WHERE n.nspname = 'public' AND c.relname = 'embeddings' AND c.relkind = 'p' AND a.attname = 'account_id' AND a.attnum > 0 AND NOT a.attisdropped) AS schema_ready;"

For a standard client installation, use the database container:

printf '%s\n' "SELECT EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace JOIN pg_attribute a ON a.attrelid = c.oid WHERE n.nspname = 'public' AND c.relname = 'embeddings' AND c.relkind = 'p' AND a.attname = 'account_id' AND a.attnum > 0 AND NOT a.attisdropped) AS schema_ready;" | docker compose exec -T aiserver-pg sh -c 'psql -X -v ON_ERROR_STOP=1 -At -U "$POSTGRES_USER" -d "$POSTGRES_DB" -f -'

The expected answer is t. If you get f, an error, or no answer, do not run the SQL files below or update.sh. Agree the next steps with support first.

2. Save the vector search SQL#

Copy all the contents of the following block into prepare_dimension_hnsw.sql without changes:

-- Run with psql -X -v ON_ERROR_STOP=1 -f prepare_dimension_hnsw.sql before
-- Phinx migration 20260930160000 on a database that already has vectors.
-- Each command runs outside a transaction so PostgreSQL can build online.
-- The migration verifies these indexes before its short metadata cutover.

SELECT format(
    'CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON public.%I USING hnsw '
    || '(embedding_vector public.ann_cos_ops) '
    || 'WITH (dims=384, m=8, efconstruction=64, efsearch=800) '
    || 'WHERE cardinality(embedding_vector) = 384',
    child.relname || '_dim_384_hnsw_idx', child.relname
)
FROM pg_inherits inheritance
JOIN pg_class child ON child.oid = inheritance.inhrelid
WHERE inheritance.inhparent = 'public.embeddings'::regclass
ORDER BY child.relname
\gexec

SELECT format(
    'CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON orchestrator.samples USING hnsw '
    || '(embedding_vector public.ann_cos_ops) '
    || 'WITH (dims=384, m=8, efconstruction=64, efsearch=64) '
    || 'WHERE cardinality(embedding_vector) = 384',
    'samples_dim_384_hnsw_idx'
)
\gexec

-- Prepare the standard 384↔1024 model switch before the migration. The
-- 1024 predicates are empty on 384-only accounts but building them online
-- here prevents the first 1024 request from building HNSW on live data.
SELECT format(
    'CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON public.%I USING hnsw '
    || '(embedding_vector public.ann_cos_ops) '
    || 'WITH (dims=1024, m=8, efconstruction=64, efsearch=800) '
    || 'WHERE cardinality(embedding_vector) = 1024',
    child.relname || '_dim_1024_hnsw_idx', child.relname
)
FROM pg_inherits inheritance
JOIN pg_class child ON child.oid = inheritance.inhrelid
WHERE inheritance.inhparent = 'public.embeddings'::regclass
ORDER BY child.relname
\gexec

SELECT format(
    'CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON orchestrator.samples USING hnsw '
    || '(embedding_vector public.ann_cos_ops) '
    || 'WITH (dims=1024, m=8, efconstruction=64, efsearch=64) '
    || 'WHERE cardinality(embedding_vector) = 1024',
    'samples_dim_1024_hnsw_idx'
)
\gexec

-- Older installations might already contain other dimensions where the old
-- index was absent. Preserve them without building a blocking index in Phinx.
SELECT format(
    'CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON public.%I USING hnsw '
    || '(embedding_vector public.ann_cos_ops) '
    || 'WITH (dims=%s, m=8, efconstruction=64, efsearch=800) '
    || 'WHERE cardinality(embedding_vector) = %s',
    format('embeddings_account_%s_dim_%s_hnsw_idx', account_id, dimension),
    format('embeddings_account_%s', account_id),
    dimension, dimension
)
FROM (
    SELECT account_id, cardinality(embedding_vector) AS dimension
    FROM public.embeddings
    WHERE cardinality(embedding_vector) NOT IN (384, 1024)
    GROUP BY account_id, cardinality(embedding_vector)
) dimensions
ORDER BY account_id, dimension
\gexec

SELECT format(
    'CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON orchestrator.samples USING hnsw '
    || '(embedding_vector public.ann_cos_ops) '
    || 'WITH (dims=%s, m=8, efconstruction=64, efsearch=64) '
    || 'WHERE cardinality(embedding_vector) = %s',
    format('samples_dim_%s_hnsw_idx', dimension), dimension, dimension
)
FROM (
    SELECT cardinality(embedding_vector) AS dimension
    FROM orchestrator.samples
    WHERE embedding_vector IS NOT NULL
      AND cardinality(embedding_vector) NOT IN (384, 1024)
    GROUP BY cardinality(embedding_vector)
) dimensions
ORDER BY dimension
\gexec

3. Save the duplicate-fragment check SQL#

Copy all the contents of the following block into prepare_documents_dedup_index.sql without changes:

-- Run with psql -X -v ON_ERROR_STOP=1 -f before Phinx migration
-- 20260930161000 on an existing database with documents. psql must not wrap
-- this command in a transaction: the index is built without blocking writes.
CREATE INDEX CONCURRENTLY IF NOT EXISTS documents_file_text_md5_active_idx
ON public.documents (file_id, md5(text_chunk))
WHERE NOT is_deleted;

Check the saved files against these SHA-256 checksums for the blocks above (including the final newline):

sha256sum prepare_dimension_hnsw.sql prepare_documents_dedup_index.sql

fe7e626f0bfb5d8d6b8a8147ee128f4ae6b830ca7753c44648ab812229c17521  prepare_dimension_hnsw.sql
dfde574e8a213c32b2e2cb16f713701d29d1991de905f1e16d414e9b0dbe9401  prepare_documents_dedup_index.sql

If the checksums differ, correct the files before running them.

4. Run both files before upgrading#

Run these commands one at a time on the machine holding the files. Each command must exit with status 0. Do not use psql -1 or BEGIN: CREATE INDEX CONCURRENTLY must run outside a shared transaction.

psql -X -v ON_ERROR_STOP=1 -f prepare_dimension_hnsw.sql
psql -X -v ON_ERROR_STOP=1 -f prepare_documents_dedup_index.sql

If psql is unavailable on the host, use these commands instead from the directory containing docker-compose.yml. The SQL files stay on the host and are passed to the container through standard input:

docker compose exec -T aiserver-pg sh -c 'psql -X -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB" -f -' < prepare_dimension_hnsw.sql
docker compose exec -T aiserver-pg sh -c 'psql -X -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB" -f -' < prepare_documents_dedup_index.sql

5. Verify the result#

After both files complete successfully, this query should return no rows with invalid indexes:

psql -X -v ON_ERROR_STOP=1 -c "SELECT n.nspname AS schema_name, c.relname AS invalid_index FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid JOIN pg_namespace n ON n.oid = c.relnamespace WHERE NOT i.indisvalid AND (c.relname ~ '_dim_[0-9]+_hnsw_idx$' OR c.relname = 'documents_file_text_md5_active_idx');"

If using the PostgreSQL container, run the same check this way:

printf "%s\n" "SELECT n.nspname AS schema_name, c.relname AS invalid_index FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid JOIN pg_namespace n ON n.oid = c.relnamespace WHERE NOT i.indisvalid AND (c.relname ~ '_dim_[0-9]+_hnsw_idx$' OR c.relname = 'documents_file_text_md5_active_idx');" | docker compose exec -T aiserver-pg sh -c 'psql -X -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB" -f -'

If a command fails or the query reports an invalid index, stop and resolve the cause with the database administrator before upgrading. Once the checks pass, run update.sh or your manual migration procedure. You do not need to reindex documents just for this preparation: the indexes are built on the existing tables and cover any records already present.

6. Additional dimensions after the upgrade #

Use this section after the version 2.5.1 migrations if the new model produces vectors with a dimension other than 384 or 1024. Prepare an index for every affected account before saving the new model in the indexing settings. Find the numeric account_id in the Sherpa AI Server database and the actual vector dimension (dimension, from 32 to 2000). Run the preparation separately for each account. Standard dimensions 384 and 1024 were prepared by the preceding steps before the upgrade.

Copy all the contents of the following block into prepare_additional_dimension_hnsw.sql without changes:

-- Prepare one account and the shared samples table for an additional embedding
-- dimension after migration 20260930160000. Run with psql outside a transaction:
-- psql -X -v ON_ERROR_STOP=1 -v account_id=123 -v dimension=768 \
--   -f backend/migrations/prepare_additional_dimension_hnsw.sql
-- CREATE INDEX CONCURRENTLY keeps ordinary inserts and updates available.

\set AUTOCOMMIT on

SELECT CASE WHEN EXISTS (
    SELECT 1 FROM orchestrator.accounts WHERE id = :'account_id'::integer
) AND :'dimension'::integer BETWEEN 32 AND 2000
THEN 'true' ELSE 'false' END AS valid_parameters
\gset
\if :valid_parameters
\else
    \echo 'Expected an existing account_id and a dimension between 32 and 2000'
    SELECT 1 / 0;
\endif

-- Before the tuning migration, keep the index compatible with its migration
-- precheck. Afterwards create future dimensions with the tuned search depth.
SELECT CASE WHEN EXISTS (
    SELECT 1 FROM orchestrator.phinxlog WHERE version = 20260930163000
) THEN 32000 ELSE 800 END AS hnsw_efsearch
\gset

-- The session lock serializes simultaneous preparation of the shared samples
-- index. It is released automatically if psql exits on an error.
SELECT pg_advisory_lock(2291, -1);
SELECT public.ensure_embeddings_account_partition(:'account_id'::integer);

SELECT format(
    'CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON public.%I USING hnsw '
    || '(embedding_vector public.ann_cos_ops) '
    || 'WITH (dims=%s, m=8, efconstruction=64, efsearch=%s) '
    || 'WHERE cardinality(embedding_vector) = %s',
    format('embeddings_account_%s_dim_%s_hnsw_idx', :'account_id'::integer, :'dimension'::integer),
    format('embeddings_account_%s', :'account_id'::integer),
    :'dimension'::integer,
    :'hnsw_efsearch'::integer,
    :'dimension'::integer
)
\gexec

SELECT format(
    'CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON orchestrator.samples USING hnsw '
    || '(embedding_vector public.ann_cos_ops) '
    || 'WITH (dims=%s, m=8, efconstruction=64, efsearch=64) '
    || 'WHERE cardinality(embedding_vector) = %s',
    format('samples_dim_%s_hnsw_idx', :'dimension'::integer),
    :'dimension'::integer,
    :'dimension'::integer
)
\gexec

SELECT public.ensure_embeddings_dimension_index(:'account_id'::integer, :'dimension'::integer);
SELECT orchestrator.ensure_samples_dimension_index(:'dimension'::integer);
SELECT pg_advisory_unlock(2291, -1);

SHA-256 checksum of the file including its final newline:

sha256sum prepare_additional_dimension_hnsw.sql

94e18e038e02371a0859d60ca8f8e6345d0d5091e04f1659a8beecba034c4702  prepare_additional_dimension_hnsw.sql

Replace ACCOUNT_ID and DIMENSION with the actual numbers. Run this command outside a shared transaction with the same psql connection settings:

psql -X -v ON_ERROR_STOP=1 -v account_id=ACCOUNT_ID -v dimension=DIMENSION -f prepare_additional_dimension_hnsw.sql

For the standard client installation, run this from the directory containing docker-compose.yml to use the database container:

docker compose exec -T aiserver-pg sh -c 'psql -X -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$POSTGRES_DB" -v account_id=ACCOUNT_ID -v dimension=DIMENSION -f -' < prepare_additional_dimension_hnsw.sql

The command must exit with status 0: the script checks that the account exists, the dimension is allowed, and both indexes are ready. If it fails, do not save the new indexing model until the cause is resolved. After a successful preparation, return to the indexing model change procedure.