vchord

pig build pkg vchord;# build rpm/deb

pig repo add pgsql -u # add both repo and update cache

pig install vchord;# install via package name, for the active PG version  pig install vchord -v 18; # install for PG 18 pig install vchord -v 17; # install for PG 17 pig install vchord -v 16; # install for PG 16 pig install vchord -v 15; # install for PG 15 pig install vchord -v 14; # install for PG 14

shared_preload_libraries = 'vchord';

CREATE EXTENSION vchord CASCADE; -- requires vector

CREATE EXTENSION vchord CASCADE;

CREATE INDEX ON gist_train USING vchordrq (embedding vector_l2_ops) WITH (options = $$ residual_quantization = true [build.internal] lists = [4096] spherical_centroids = false build_threads = 8 $$);

SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

-- Query vectors within a certain distance range -- sphere(center, radius) means the vectors within the sphere with the center and radius, aka range query -- <<->> is L2 distance, <<#>> is inner product, <<=>> is cosine distance SELECT vec FROM t WHERE vec <<->> sphere('[0.24, 0.24, 0.24]'::vector, 0.012) 

-- Set probes to control the number of lists scanned. -- Recommended range: 3%–10% of the total `lists` value. SET vchordrq.probes = 100;  -- Set epsilon to control the reranking precision. -- Smaller value means less rerank for faster speed, larger value for higher recall. -- Recommended range: 0.0–4.0. Default value is 1.9. SET vchordrq.epsilon = 1.9;

-- If using SSDs, set `effective_io_concurrency` to 200 for faster disk I/O. SET effective_io_concurrency = 200;  -- Disable JIT (Just-In-Time Compilation) as it offers minimal benefit (1–2%) -- and adds overhead for single-query workloads. SET jit = off;  -- Allocate at least 25% of total memory to `shared_buffers`. -- For disk-heavy workloads, you can increase this to up to 90% of total memory. You may also want to disable swap with network storage to avoid io hang. -- Note: A restart is required for this setting to take effect. ALTER SYSTEM SET shared_buffers = '8GB';

-- vchordrq_prewarm(index_name::regclass) to prewarm the index into the shared buffer SELECT vchordrq_prewarm('gist_train_embedding_idx'::regclass);

-- Set this to the number of CPU cores available for parallel operations. SET max_parallel_maintenance_workers = 8; SET max_parallel_workers = 8;  -- Adjust the total number of worker processes. -- Note: A restart is required for this setting to take effect. ALTER SYSTEM SET max_worker_processes = 8;

SELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS "%" FROM pg_stat_progress_create_index;

-- Create table of centroids CREATE TABLE public.centroids (id integer NOT NULL UNIQUE, parent integer, vector vector(768)); -- Insert centroids into it INSERT INTO public.centroids (id, parent, vector) VALUES (1, NULL, '{0.1, 0.2, 0.3, ..., 0.768}'); INSERT INTO public.centroids (id, parent, vector) VALUES (2, NULL, '{0.4, 0.5, 0.6, ..., 0.768}'); INSERT INTO public.centroids (id, parent, vector) VALUES (3, NULL, '{0.7, 0.8, 0.9, ..., 0.768}'); -- ...  -- Create index using the external centroid table CREATE INDEX ON gist_train USING vchordrq (embedding vector_l2_ops) WITH (options = $$ [build.external] table = 'public.centroids' $$);

curl --proto '=https' --tlsv1.2 -sSf https://apt.llvm.org/llvm.sh | bash -s -- 18 sudo update-alternatives --install /usr/bin/clang clang $(which clang-18) 255