FAQ

By default, a non-partitioned table can store up to 32 TB of data. A partitioned table can have thousands of partitions of that size.

Yes. pgvector uses PostgreSQL's write-ahead logging (WAL) mechanism, which enables replication and point-in-time recovery.

Use half-precision indexing to index up to 4,000 dimensions, or binary quantization to index up to 64,000 dimensions. You can also consider dimensionality reduction to go beyond 64,000 dimensions.

Yes. Use the vector type instead of vector(3):

CREATE TABLE embeddings (model_id bigint, item_id bigint, embedding vector, PRIMARY KEY (model_id, item_id));

To create an index, use expression indexing or partial indexing to target rows with the same number of dimensions:

CREATE INDEX ON embeddings USING hnsw ((embedding::vector(3)) vector_l2_ops) WHERE (model_id = 123);

After the index is created, run nearest neighbor searches against that subset:

SELECT * FROM embeddings WHERE model_id = 123 ORDER BY embedding::vector(3) <-> '[3,1,2]' LIMIT 5;

Yes. Use the double precision[] or numeric[] data type:

CREATE TABLE items_highpre (id bigserial PRIMARY KEY, embedding double precision[]);

-- Use curly brackets ({}) instead of square brackets ([]) to insert PostgreSQL arrays.
INSERT INTO items_highpre (embedding) VALUES ('{1,2,3}'), ('{4,5,6}');

Optionally, add a check constraint to enforce a specific dimension when the column is cast to vector:

ALTER TABLE items_highpre ADD CHECK (vector_dims(embedding::vector) = 3);

Create a Hierarchical Navigable Small World (HNSW) index with expression indexing:

CREATE INDEX ON items_highpre USING hnsw ((embedding::vector(3)) vector_l2_ops);

After the index is created, run nearest neighbor searches:

SELECT * FROM items_highpre ORDER BY embedding::vector(3) <-> '[3,1,2]' LIMIT 5;

No. However, doing so can result in better performance. To check the index size:

SELECT pg_size_pretty(pg_relation_size('index_name'));

The query must include both ORDER BY and LIMIT clauses. The ORDER BY clause must use a distance operator — not a general expression — and sort results in ascending order.

-- Uses the index.
ORDER BY embedding <=> '[3,1,2]' LIMIT 5;

-- Does not use the index.
ORDER BY 1 - (embedding <=> '[3,1,2]') DESC LIMIT 5;

To force the query planner to use the index:

BEGIN;
SET LOCAL enable_seqscan = off;
SELECT ...
COMMIT;

On small tables, a sequential scan may be faster than an index scan.

The query planner doesn't consider out-of-line storage in cost estimates, which can make a serial scan look cheaper. To reduce the cost of parallel scans for a query:

BEGIN;
SET LOCAL min_parallel_table_scan_size = 1;
SET LOCAL parallel_setup_cost = 1;
SELECT ...
COMMIT;

Alternatively, store vectors inline to avoid out-of-line storage entirely:

ALTER TABLE items ALTER COLUMN embedding SET STORAGE PLAIN;

The result count is limited by the dynamic candidate list size controlled by hnsw.ef_search, which defaults to 40. Dead tuples or filtering conditions can reduce it further. Enable iterative index scans to address this.

Note that NULL vectors are not indexed. Zero vectors are also not indexed when cosine distance is used.

The index was likely created when the table had too little data relative to the number of lists. You can recreate the index after the data size increases, or drop the current index:

DROP INDEX index_name;

The number of probes controlled by ivfflat.probes can also limit results. Enable iterative index scans to address this.

Note that NULL vectors are not indexed. Zero vectors are also not indexed when cosine distance is used.