pgvector offers two main index types for vector similarity search

When working with vector data in PostgreSQL, the choice between HNSW and IVFFLAT indexes involves a trade-off between query speed and index build time. HNSW builds a multilayer graph structure that excels at SELECT performance but demands significantly more time during index creation. IVFFLAT, by contrast, builds much faster, although query performance may suffer as a result.

The dataset and environment

The test data comes from a Wikipedia import performed with pgai. The raw dataset contains 6.4 million Wikipedia articles, which are split into 41 million chunks and converted into vector representations:

1

2

3

4

5

cybertec=# SELECT count(*) FROM demo_wiki_emb;

  count  

----------

40961670

(1 row)

Before creating indexes, we need to configure memory and parallelism appropriately. The maintenance_work_mem setting controls how much memory is available for index builds. A common question is whether this parameter applies per CREATE INDEX statement or per worker process. The answer depends on the index type: for standard index types like b-trees, it represents the total memory allowance. However, each index type can decide on its own semantics, so this may vary for less common types.

1

2

3

4

5

cybertec=# \timing

Timing is on.

cybertec=# SET maintenance_work_mem TO '32 GB';

SET

Time: 6.509 ms

Parallel index creation also helps reduce build times. Enabling multiple worker processes lets PostgreSQL distribute the work across available CPU cores:

1

2

3

cybertec=# SET max_parallel_maintenance_workers TO 8;

SET

Time: 0.214 ms

Building an HNSW index

Creating an HNSW index with cosine distance on our vector table is a heavy operation. The command is straightforward:

1

2

3

4

5

6

7

cybertec=# CREATE INDEX ON demo_wiki_emb_store

USING hnsw (embedding vector_cosine_ops);

NOTICE:  hnsw graph no longer fits into maintenance_work_mem after 15488425 tuples

DETAIL:  Building will take significantly more time.

HINT:  Increase maintenance_work_mem to speed up builds.

CREATE INDEX

Time: 44216893.145 ms (12:16:56.893)

The process is resource-intensive. PostgreSQL spawns multiple parallel processes that fully saturate the CPU. Profiling reveals that most of the time is spent inside a single function that computes vector products repeatedly. This function, central to machine learning workloads, runs on the CPU in this implementation. While GPU acceleration is theoretically possible, the standard interfaces used here make that difficult.

The result: building this index takes half a day.

Semantic Search blog graphic 2

Storage footprint of HNSW

Vector data adds noticeable overhead to the raw input. The original Wikipedia data occupies a certain amount of space, and adding vectors increases this further:

1

2

3

4

5

6

7

                      List of relations

Schema |        Name         | Type  | ... |  Size   |  

--------+---------------------+-------+-----+---------+

public | demo_wiki_emb       | view  |     | 0 bytes |

public | demo_wiki_emb_store | table |     | 92 GB   |

public | wiki                | table |     | 13 GB   |

(3 rows)

The HNSW index itself is surprisingly large: at 77 GB, it exceeds the size of the underlying table data. This is an important consideration when planning disk usage.

1

2

3

4

5

6

cybertec=# SELECT pg_size_pretty(

  pg_total_relation_size('demo_wiki_emb_store_embedding_idx'));

pg_size_pretty

----------------

77 GB

(1 row)

Comparing with IVFFLAT

Given the long build time of HNSW, it makes sense to evaluate the alternative. IVFFLAT indexes can be created with a similar command:

1

2

cybertec=# CREATE INDEX ON demo_wiki_emb_store

USING ivfflat (embedding vector_cosine_ops);

The difference during index creation is dramatic. IVFFLAT consumes substantially less CPU, and the build completes in just 38 minutes. A meaningful portion of that time is I/O rather than compute. The resulting index is 63 GB, so the size savings compared to HNSW are minimal.

1

Time: 2314162.345 ms (38:34.162)

Key considerations

The fundamental trade-off is clear: HNSW offers better query performance but requires hours of build time, while IVFFLAT finishes much faster but is likely to perform worse when running queries. The choice depends on whether your workload prioritizes write/index-build speed or read performance. Both index types should be tuned and tested against your actual data to confirm which fits your operational constraints.