Linux, macOS, FreeBSD

Engineering teams default to Postgres and pgvector because nobody wants more infrastructure to manage. The out-of-the-box setup works great for prototyping.

But as retrieval-augmented generation (RAG) pipelines and AI agents move into production, teams hit a wall. Scaling vector search inside a relational database means moving beyond default configurations.

Getting there requires bypassing memory limits, fixing recall loss under metadata filters, and knowing when to hand the hot path to a dedicated vector database.

## TL;DR

* **Prototypes:** Default Postgres \+ pgvector works great for initial RAG development and small corpora. Tuning, quantization, and extensions become necessary past a few million high-dimensional vectors.  
* **What breaks at scale:** HNSW index size grows quickly as vector count and dimensionality increase. The index is stored on disk, but performance depends heavily on keeping the active working set cache-resident. When active graph pages are evicted from memory, latency can degrade sharply, and standard post-filtering can silently degrade recall.  
* **How to scale in Postgres:** Use pgvector 0.8+ iterative scans to improve filtering recall. For memory limits, use quantization, partitioning, and, where supported, disk-oriented vector indexes such as pgvectorscale/StreamingDiskANN.  
* **When to go hybrid:** Move to a dedicated vector database if sub-20ms p99 latency is a requirement, you're scaling into the billions of vectors, or high write churn from frequent re-embedding would cause severe MVCC bloat.

## How to scale vector search in Postgres at a glance

Scaling pgvector beyond prototypes comes down to three steps. First, fix recall under metadata filters by enabling pgvector 0.8+ iterative index scans. Second, bypass HNSW's RAM ceiling using quantization, tenant-level partitioning, and, where available, SSD-oriented vector indexes. Third, know the workload signals, such as sub-20 ms p99 latency, billions of vectors, GPU ANN, or heavy re-embedding churn, that justify a hybrid Postgres \+ dedicated vector database architecture.

## Why pgvector HNSW slows down when the working set outgrows memory

Postgres vector search can slow down at scale because the Hierarchical Navigable Small World (HNSW) index is most effective when its active graph pages remain in cache. The graph can technically spill beyond memory, but HNSW's random-access traversal pattern is sensitive to cache misses, so latency can degrade sharply once the working set exceeds available RAM.

### How much RAM does HNSW need per vector?

Take a baseline corpus using [OpenAI's text-embedding-ada-002 model](https://developers.openai.com/api/docs/guides/embeddings), which generates 1,536-dimensional vectors.

Storing a single vector at full precision (float32) requires 4 bytes per dimension, roughly 6 kilobytes per vector. The HNSW index adds overhead to maintain its multi-layered proximity graph. Pointers, neighbor lists, and structural metadata push the memory requirement well past raw vector storage. Real-world deployments can land around 20 to 25 kilobytes per vector with common settings once graph metadata is accounted for, though the exact footprint depends on index parameters, dimensions, and workload.

At one million vectors, that can mean tens of gigabytes of memory. At ten million vectors, memory pressure becomes a central design concern. At 100 million vectors, raw FP32 vector storage alone is over 600 gigabytes before index overhead is included.

### What happens when the HNSW working set is no longer cache-resident?

pgvector indexes are stored on disk like other Postgres indexes, but query performance depends heavily on the active graph pages being served from shared buffers or the operating system page cache. When the active HNSW working set no longer fits in memory, queries may need to fetch graph pages from storage during traversal.

HNSW traversal involves random access rather than sequential scanning. Disk spillover can cause severe page churn. Search latency can degrade from milliseconds to seconds, depending on storage architecture, cache hit rate, and concurrency. That can stall the active connection pool and eventually compete with standard OLTP workloads.

As a rough rule of thumb, pgvector scaling tends to fall into bands, though the exact thresholds depend heavily on dimensionality, hardware, recall targets, and filter patterns. Around one million vectors, naive implementations often succeed. Approaching ten million, quantization, partitioning, and strict memory tuning tend to matter more. Near 100 million, it's worth evaluating whether standard in-memory HNSW is still the right index structure. Beyond a billion vectors, a pure Postgres architecture is often no longer the simplest or most cost-effective design.

## Common pgvector pitfalls: when you don't need ANN, dimension limits, and MVCC bloat

The most common myth in vector database architecture is the assumption that every semantic search requires an Approximate Nearest Neighbor (ANN) index.

For multi-tenant SaaS applications, developers often partition data by workspace or customer ID. If a single tenant only owns a few thousand vectors, building and querying an HNSW index can be overkill. A standard B-tree index on the partition key followed by a flat scan of the relevant vectors yields 100 percent recall with zero approximation loss, and can execute faster than traversing a graph structure.

Another critical pitfall is ignoring Postgres internal storage mechanics, specifically the hard 8K block-size limit. Postgres uses a fixed 8-kilobyte page size and relies on the [TOAST mechanism to compress or move large tuples out-of-line](https://www.postgresql.org/docs/current/storage-toast.html).

The pgvector extension enforces a hard limit of 2,000 dimensions for HNSW and IVFFlat indexing on the standard vector type. Storing 3,072-dimension vectors from newer, wider embedding models works, but `CREATE INDEX USING hnsw` on the standard `vector` type can fail because index entries cannot fit within Postgres's page constraints. The workarounds are `halfvec`, which raises the indexable dimension cap to 4,000, or dimension reduction.

Teams also frequently treat vector indexes like deterministic B-trees, assuming they provide exact ordering and deterministic results, but they do not.

Vector graphs degrade under high-concurrency update workloads. Postgres uses Multi-Version Concurrency Control (MVCC), meaning updates generate new row versions and [leave behind dead tuples](https://skylinecodes.substack.com/p/postgresql-dead-tuples-mvcc-autovacuum). In a highly volatile RAG corpus, these dead tuples accumulate before autovacuum can clean them. Queries recheck and discard stale entries, shrinking the effective candidate pool and degrading recall.

Finally, teams operating Postgres vector workloads often skip RAG observability entirely. They deploy vector search without establishing telemetry for embedding drift, recall degradation, or query performance. At minimum, track query latency percentiles and recall against a held-out ground truth set. Without these signals, recall degradation from MVCC bloat or embedding drift is invisible until users complain.

## How to fix pgvector filtering and recall loss

The most common failure point in production RAG systems is the post-filtering trap.

Historically, developers applied metadata filters using a standard `WHERE` clause after the vector index had already retrieved its nearest neighbors. If your query requests ten nearest neighbors, the index retrieves ten. If the post-filter then rejects nine of those results because of strict metadata constraints, your query silently returns a single document. That destroys system recall and starves the language model of context.

### How iterative index scans work in pgvector 0.8+

The fix for this arrived in [pgvector 0.8 with the introduction of iterative index scans](https://www.postgresql.org/about/news/pgvector-080-released-2952/). Rather than retrieving a fixed batch of nearest neighbors, iterative scans keep pulling more candidates from the index until enough rows pass the `WHERE` clause.

Iterative scans are not automatic. Enable them per session or transaction:


Frequently asked questions

How to scale vector search in Postgres (pgvector) for RAG and AI agents: memory limits, filtering, and when to go hybrid

To query a REST API in Python, use [chDB](/resources/engineering/what-is-chdb) to read the JSON response directly into a DataFrame:


`df` supports the pandas API you already use (indexing, filtering, `groupby`, `merge`), but the work runs on ClickHouse's engine instead of pandas, so it stays fast as files grow. There is no server to start and no separate load step.


Prefer the command line? See [how to query a REST API with SQL](/resources/engineering/query-rest-api-with-sql) to do this with clickhouse local.

## Read a JSON API response into a DataFrame

The companion example serves sample data over Python's built-in `http.server`. That stand-in for a real API shows the exact call you would make against any live JSON endpoint.


chDB infers the schema from the response: `id` comes back as an integer, `amount` as a float. The nested `labels` field comes back as an object column (a numpy array per row when you materialize). You declared nothing.

One thing is different from a regular DataFrame: `df` is not the response loaded into memory. It is a lazy, ClickHouse-backed object that records what you asked for and runs on ClickHouse's engine when you materialize a result, whether you print it, take `len()`, or call `.to_pandas()`. That is why it stays fast on large responses.

## Filter and aggregate the way you already do

The pandas you write does not change. Filter with a boolean mask, group, and aggregate:


Same syntax as pandas, same result. chDB compiles the whole chain into one optimized query and only materializes what you asked for, rather than pulling the full response into memory first. Joins with `merge`, computed columns with `assign`, and the `.str` and `.dt` accessors all work the same way.

## Hand off to real pandas when you need it

When a library downstream needs an actual pandas DataFrame (scikit-learn, a plotting call, anything that mutates in place), call `.to_pandas()` to materialize one:


The usual pattern is to do the heavy filtering and aggregation on the chDB object first, then `.to_pandas()` the small result and continue in ordinary pandas.

## Multiple pages: read each URL, then concat

When an API paginates across predictable URLs, read each page into a DataFrame and concat them:


## Is it faster than requests + json?

On a large response, yes. The same filter-and-aggregate over a 2M-row (~120 MB) JSON endpoint, served on localhost so network latency is out of the picture, best-of-3 with a warm cache on an Apple M4 Pro (14 cores, 24 GB RAM, macOS):


About 12-13x faster here. The gap is fetch-plus-parse-plus-aggregate cost: chDB streams the response and aggregates as it goes, while the Python loop decodes every line into objects before it can do anything with them. Against a real API the network round trip adds equally to both sides, so it does not change the ratio; it just raises the floor.

The back-to-back ratio is the robust claim; absolute times shift with concurrent load. On a tiny response the comparison can flip, because chDB pays a small fixed per-query cost that dominates when there is barely any data. Reach for `DataStore.from_url` when the payload is large enough that parsing and aggregating it is the real work.

Hardware: Apple M4 Pro (14 cores, 24 GB RAM, macOS); chDB 4.1.8, Python 3.14; best-of-3, warm.

## Works in Jupyter

Printing a chDB object renders it as a table in a notebook, and `.to_pandas()` feeds straight into `.plot()`, joins, or any pandas code. The companion folder ships a `run.ipynb` you can open and run cell by cell.

## Run it yourself

The complete, runnable example is here, with `generate.sh` to create the sample JSON files, plus `run.ipynb` and a `run.py` mirror containing the exact code above:

[github.com/ClickHouse/examples/tree/main/local-analytics/chdb-rest-api](https://github.com/ClickHouse/examples/tree/main/local-analytics/chdb-rest-api)

Working with JSON from other sources? [Read a JSON file in Python](/resources/engineering/read-json-file-python) and [read a JSON Lines file in Python](/resources/engineering/read-jsonl-file-python) use the same pandas drop-in pattern; only the reader changes. The same code scales from a local script to [ClickHouse Cloud](https://clickhouse.com/cloud) with no rewrite.


How to query a REST API in Python

To convert Parquet to ORC, use [`clickhouse local`](/resources/engineering/what-is-clickhouse-local). It runs SQL directly on files from the command line, with no server to install. It's part of ClickHouse, so the same query scales to billions of rows when you outgrow your laptop.

Install it with [`clickhousectl`](https://clickhouse.com/docs/install):


The write command exits silently; the response above is what `DESCRIBE file('events.orc')` returns once the file is on disk. ClickHouse reads the schema straight from the Parquet footer and writes ORC in one pass, so the file is converted in place with no upload or import step first.

## Convert it in one line

`SELECT` from the Parquet file, `INTO OUTFILE` as ORC. `TRUNCATE` overwrites the output if it already exists, so the command is safe to re-run.


The `.orc` extension is enough for ClickHouse to pick the writer, but the explicit `FORMAT ORC` keeps the intent obvious and works even when the extension is unusual.

## The schema carries over

There is no DDL to write. ClickHouse reads the column names and types from the Parquet footer:


After the conversion, the ORC file reports the same columns:


Two differences are worth knowing about, and neither loses data:

- **Columns come back `Nullable`.** ORC carries a null bitmap per column, so on read-back every column is reported as `Nullable`. The values are identical; the type just gained an explicit "can be null" marker.
- **`UInt64` is read back as `Int64`.** ORC's integer types are signed, so a ClickHouse `UInt64` maps to ORC's 64-bit signed integer. For values inside the signed range this is lossless. If you store IDs near the top of the unsigned range, keep them as `String` before converting, or be aware of the mapping.

The nested `Map` column survives the trip intact. ORC supports nested map and list types natively, which is the payoff of going columnar-to-columnar: you are not flattening structure into a single text column the way a CSV target would force you to.

## Check the values match

The fastest way to trust a conversion is to run the same query against both files and compare. Same aggregate, same numbers:


## Options: choose the ORC compression codec

This is where a local command beats an upload-and-download converter site: you control the output, and you can script it. Pick the ORC compression method explicitly with a setting:


`zstd` gives a strong size-to-speed balance; `lz4` is faster to write and read with slightly larger files; `none` skips compression when something downstream re-compresses anyway. Because both Parquet and ORC are columnar, the per-column encodings do most of the work, and the codec mainly trades file size against CPU.

There is no row limit, no file-size cap, and the data never leaves your machine. The same command runs in a shell script, a cron job, or a CI step.

## How fast is it?

On a 3,000,000-row Parquet file (~22 MB), the full Parquet-to-ORC conversion runs in:


**~0.73 seconds**, best of three with a warm OS page cache, on an Apple M4 Pro laptop (14 cores, 24 GB RAM). The number can drift slightly under concurrent load, but the shape holds: the conversion is I/O-bound, not a heavyweight transform. The resulting ORC file is ~20 MB, in the same ballpark as the Parquet input, as expected for two compressed columnar formats over the same data.

## Reverse direction?

Going the other way is the same one-liner with the formats swapped. See [how to convert ORC to Parquet](/resources/engineering/convert-orc-to-parquet).

## Prefer Python?

The same conversion runs in-process with [chDB](/resources/engineering/what-is-chdb), the embeddable ClickHouse engine for Python. Same SQL, written straight to a file:


No server, no temp files you have to manage, and the result lands in a real ORC file you can hand to any ORC reader.

## Run it yourself

The complete, runnable example lives in the ClickHouse examples repo: `generate.sh` to create the sample Parquet files (including the 3M-row file used for the timing above), `run.sh` with every command on this page, `run.py` plus `run.ipynb` for the chDB version, and `expected_output.txt`.

[github.com/ClickHouse/examples/tree/main/local-analytics/convert-parquet-to-orc](https://github.com/ClickHouse/examples/tree/main/local-analytics/convert-parquet-to-orc)

## Related

- [What is the ORC file format?](/resources/engineering/orc-file-format)
- [What is a Parquet file?](/resources/engineering/what-is-parquet-file)
- [How to read an ORC file with SQL](/resources/engineering/read-orc-file)
- [How to query a Parquet file](/resources/engineering/how-to-query-parquet-file)

The same SQL you ran here scales straight up: from a file on your laptop, to a ClickHouse server, to [ClickHouse Cloud](https://clickhouse.com/cloud), with no rewrite.


Resource hub

Engineering

How to scale vector search in Postgres (pgvector) for RAG and AI agents: memory limits, filtering, and when to go hybrid

How to query a REST API in Python

How to convert Parquet to ORC