skip to content

In pgvector, how do you index 3,072-dimension embeddings when an HNSW index on a vector column refuses more than 2,000 dimensions?

level: seniorimportance: nice to knowfreq 27%

answer

  1. store 16,000, index 2,000
  2. half precision doubles the limit
  3. expression index with a cast
  4. binary_quantize, then re-rank
  5. sparsevec counts non-zeros

basics

~20 s

pgvector's vector type stores up to 16,000 dimensions but HNSW and IVFFlat index at most 2,000. Index a halfvec instead, which allows 4,000, either as the column type or as an expression index on embedding::halfvec(3072), and query with the same cast.

solid answer

~40 s

pgvector has four types: `vector` (4-byte floats, `4 * d + 8` bytes, up to 16,000 dimensions), `halfvec` (2-byte floats, `2 * d + 8` bytes), `bit` (PostgreSQL's own bit string, which pgvector can index) and `sparsevec` (only non-zero elements stored). Storage limits are generous; index limits are not: HNSW and IVFFlat accept `vector` up to 2,000 dimensions, `halfvec` up to 4,000 and `bit` up to 64,000, and only HNSW accepts `sparsevec`, up to 1,000 non-zero elements. For a 3,072-dimension model, either store `halfvec(3072)` or keep `vector(3072)` and build `USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops)`, querying with the same cast. Beyond 4,000 dimensions, a `binary_quantize(embedding)::bit(n)` expression index with re-ranking on the full vectors works, at a recall cost you must measure.

code

sql · 15 lines
sql
CREATE TABLE ticket_embeddings_large (
    ticket_id bigint PRIMARY KEY,
    account_id bigint NOT NULL,
    embedding vector(3072) NOT NULL
);

CREATE INDEX CONCURRENTLY ticket_embeddings_large_half_idx
    ON ticket_embeddings_large
    USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);

SELECT ticket_id
FROM ticket_embeddings_large
ORDER BY embedding::halfvec(3072)
      <=> (SELECT embedding FROM ticket_embeddings_large WHERE ticket_id = 42)::halfvec(3072)
LIMIT 10;

go deeper

for a junior

Recall the four types and that vector stores up to 16,000 dimensions but indexes only up to 2,000.

for a middle

Explain halfvec and expression indexes, and why the query must repeat the same cast for the planner to match the index.

for a senior

Show you would weigh halfvec, binary quantization with re-ranking and subvectors against measured recall, and build the index concurrently on a live table.

for a principal

Treat embedding dimensionality as a cost decision across storage, memory and recall, made with the model choice rather than after it.

## pgvector's four types | Type | Element | Storage | Stored limit | HNSW index limit | IVFFlat index limit | |---|---|---|---|---|---| | `vector(n)` | 4-byte float | `4 * n + 8` bytes | 16,000 dimensions | 2,000 dimensions | 2,000 dimensions | | `halfvec(n)` | 2-byte float | `2 * n + 8` bytes | 16,000 dimensions | 4,000 dimensions | 4,000 dimensions | | `bit(n)` | 1 bit | `n / 8 + 8` bytes | PostgreSQL's bit-string type | 64,000 dimensions | 64,000 dimensions | | `sparsevec(n)` | index plus 4-byte float per non-zero | `8 * nnz + 16` bytes | 16,000 non-zero elements | 1,000 non-zero elements | not supported | - `halfvec` and `sparsevec` arrived in pgvector 0.7.0, together with index support for `bit`. - `bit` is PostgreSQL's own bit-string type; pgvector adds the Hamming (`<~>`) and Jaccard (`<%>`) operators and index support for it. - Float elements must be finite: `NaN` and infinities are rejected. ## Why storage and index limits differ The index limits follow from each index element having to fit on one index page (8 kB by default): with 4-byte floats, pgvector caps indexed vectors at 2,000 dimensions. Halving the element size doubles the limit; one bit per dimension multiplies it by 32. A 3,072-dimension `vector` column is stored without complaint, but `CREATE INDEX ... USING hnsw` on it fails with `column cannot have more than 2000 dimensions for hnsw index`. ## Option 1: index at half precision Either store `halfvec(3072)` directly, which also halves the table's working set, or keep full precision in the table and index an expression: ```sql CREATE INDEX CONCURRENTLY ticket_embeddings_large_half_idx ON ticket_embeddings_large USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops); ``` The query must repeat the expression exactly, or the planner will not match the index: ```sql ORDER BY embedding::halfvec(3072) <=> $1::halfvec(3072) LIMIT 10 ``` Half precision loses some precision per element; measure recall against an exact search on your own data before relying on it. ## Option 2: binary quantization with re-ranking `binary_quantize(embedding)` turns each dimension into one bit - 1 where the value is positive, 0 otherwise. An expression index on `binary_quantize(embedding)::bit(3072)` with `bit_hamming_ops` handles up to 64,000 dimensions and is far smaller, but the Hamming ordering is coarse, so: 1. Fetch a shortlist, say 40 rows, ordered by `binary_quantize(embedding)::bit(3072) <~> binary_quantize(q)` - this uses the index. 2. In an outer query, order that shortlist by `embedding <=> q` on the full vectors and keep the top 10. Why quantized indexes rescore a shortlist is general vector-index theory; in pgvector the rescoring is just an outer `ORDER BY` on the original column. ## Option 3: fewer dimensions - `subvector(embedding, 1, 1024)::vector(1024)` indexes a prefix of each vector. That only makes sense for models trained so that a prefix is itself a usable embedding; re-rank with the full vector afterwards. - Reducing dimensionality before storing the vectors is the remaining route. ## sparsevec is a different tool `sparsevec` stores only non-zero elements, written as `'{1:0.5,7:1.2}/30000'` with indices starting at 1, like PostgreSQL arrays. It suits sparse embeddings whose dimension is the size of a vocabulary but where few entries are non-zero. It can hold up to 16,000 non-zero elements, yet HNSW indexes it only up to 1,000 non-zeros and IVFFlat not at all. Blending sparse and dense results is hybrid ranking, a search-design question rather than a storage one. ## Picking among the options 1. Start with `halfvec` - as the column type or an expression index - when the model's dimension is at most 4,000; it is the smallest change and needs no separate re-ranking query. 2. Move to binary quantization with re-ranking when the index must be much smaller or the dimension exceeds 4,000. 3. Use `subvector` only when the model documents that its prefixes are meaningful. 4. Whatever you choose, compare its top-k with an exact search on the full vectors before shipping it. ## Two errors worth recognising - `column cannot have more than 2000 dimensions for hnsw index` - too many dimensions for the type; use one of the options above. - `column does not have dimensions` - the column was declared as plain `vector` with no `(n)`, for example to hold embeddings from several models. Index an expression cast to a fixed size, usually combined with a partial index per model, and query with the same cast.

  • Why does ORDER BY embedding <=> $1 ignore a pgvector index built on embedding::halfvec(3072)?
    An expression index matches only queries that use the same expression. The query must order by `embedding::halfvec(3072) <=> $1::halfvec(3072)`, using the `halfvec` operator from `halfvec_cosine_ops`; ordering by the plain `vector` column is a different expression with no index.
  • What does pgvector's binary_quantize keep, and how do you recover recall?
    It keeps one bit per dimension, set where the value is positive, so the index is tiny but its Hamming ordering is coarse. Fetch a larger shortlist from the bit index, then re-rank it by the full-precision distance in an outer query.

  • A vector column is limited to 2,000 dimensions, so 3,072-dimension embeddings cannot be stored
  • sparsevec can be indexed with IVFFlat as well as HNSW
  • bit is a type pgvector invented for binary embeddings
  • An index on embedding::halfvec(3072) serves queries ordering by embedding <=> q
  • binary_quantize keeps full recall, so the re-rank step is optional