Infino vs Postgres / ClickHouse
Postgres can assemble hybrid retrieval from native text search, pgvector, and a separate BM25 extension, but the indexes scale with the application database. ClickHouse has token filtering and vector search, but no BM25 relevance. Infino includes BM25, vector search, RRF, and SQL over one Parquet copy.
diff --stat postgres clickhouse infino
The short answer
Move retrieval workloads to Infino when you want BM25, vector search, rank fusion, and SQL over one copy. Keep Postgres or ClickHouse for the application and warehouse work around those queries.
- Postgres hybrid retrieval combines
tsvector/ts_rank, pgvector, and a separate BM25 extension. The database carries the text index, vector index, and application workload together, so retrieval cannot scale independently. - ClickHouse provides a native text index for token filtering plus HNSW, QBit, and brute-force vector search, but it does not provide BM25 relevance scoring.
- Infino runs BM25 and vector search in one query, fuses the ranks with RRF, then applies
JOINandGROUP BYto the result.
cat ARCHITECTURE.diff
Where the data lives
Postgres and ClickHouse keep their own tables. Infino searches the Parquet already in your bucket.
- Postgres stores the application rows, text index, and pgvector HNSW index on the database you scale and replicate. BM25 comes from another extension.
- ClickHouse’s native tables are MergeTree. Its text index accelerates token filtering without BM25 scoring; vector search runs through HNSW, QBit, or a scan.
- Infino’s indexes live inside the Parquet. Storage and compute scale apart.
infino diff --features
Capability by capability
| capability | postgres | clickhouse | infino |
|---|---|---|---|
| sql | full Postgres SQL | full SQL, ClickHouse dialect | DataFusion; search as table functions |
| keyword search | tsvector / ts_rank; BM25 needs another extension | native text index; no BM25 scoring | BM25 in the engine |
| vector search | exact, HNSW, IVFFlat | HNSW, QBit, brute force | HNSW when pinned in RAM; OPANN + Sq16 on object storage |
| hybrid ranking | combine text and vector ranks yourself | combine text filters and vector ranks yourself | RRF in one query, one pass |
| data format | Postgres tables | MergeTree; also reads and writes Parquet | standard Parquet |
| where data lives | block storage on the Postgres you run | block or object, through ClickHouse | your bucket, searched in place |
| runs as | a Postgres database | a cluster, or ClickHouse Cloud | embedded library, or Infino Cloud |
| warehouse ecosystem | the Postgres catalog, BI, extensions | ClickHouse Cloud, BI, the analytical dialect | retrieval SQL, not warehouse BI or governance |
| license | PostgreSQL license | Apache-2.0 | Apache-2.0 engine |
elasticsearch / opensearch · qdrant / pinecone · postgres / clickhouse · the field
infino bench --sql
Measured SQL latency
Infino warm p50 over 10M rows, reported as four named query shapes rather than one synthetic “typical” SQL query.
External benchmark reference for SQL engines: ClickBench (different query suite) →
infino cost --compare sql
Why the bill is different
SQL mode compares columns-only Postgres and ClickHouse. Hybrid mode prices Postgres with its fp32 vectors and resident pgvector HNSW index against the split Elastic, ClickHouse, and Pinecone stack.
Amazon RDS PostgreSQL
Multi-AZ · SQL columns only · 16 GB instance · 10 GB stored · us-east-1 list rates
ClickHouse Cloud · Scale
Published HA floor · 2 replicas × 8 GB · us-east-1 · always-on. Queries spread through the month keep the service active.
cat TRADEOFFS.md
Postgres and warehouse requirements
Infino is not Postgres, and it is not a warehouse.
- Postgres keeps retrieval inside the application database, but text, vector, and application workloads scale together.
- ClickHouse provides warehouse SQL, BI integrations, and its analytics ecosystem.
infino migrate --from postgres
How migration works
Move search and the SQL on top of it. Keep the warehouse for BI.
- Export the columns, the text you rank, and the embeddings.
- Append into Infino over REST, Arrow or JSON. Indexing happens on write.
- Rewrite the fused query as
hybrid_search(orbm25_search/vector_search) inside SQL. Dual-run, then cut those reads over.
cat FAQ.md
Does Infino replace Postgres or ClickHouse?
Infino can replace the retrieval workloads built on Postgres or ClickHouse: keyword, vector, hybrid ranking, and SQL run over the same Parquet files. Keep Postgres for the application database and ClickHouse for warehouse BI.
What is the main difference?
Postgres pairs tsvector and ts_rank with pgvector; BM25 requires a separate extension. ClickHouse has a native text index for token filtering and several vector-search paths, but no BM25 relevance model. Infino includes BM25 and vector search, fuses their ranks with RRF, and exposes the result to SQL.
How much does Infino reduce hybrid retrieval and SQL cost?
SQL mode compares Infino against columns-only Postgres and ClickHouse. Hybrid mode compares Infino against Postgres carrying the SQL rows, fp32 vectors, and a resident pgvector HNSW index, plus the split Elastic, ClickHouse, and Pinecone stack. Postgres must scale the database with the vector index and cannot scale retrieval independently from the application workload. Infino is 10× cheaper than the full stack because BM25, vector search, RRF, and SQL run over one Parquet copy.
Does Infino replace Snowflake, Databricks, or DuckDB?
No. Infino does not replace a warehouse. Teams move query workloads onto Infino to reduce cost and improve performance, but Infino does not have the ecosystem of a typical data warehouse.
Why would I use Postgres or ClickHouse instead?
Use Postgres when retrieval must stay inside the application database. Use ClickHouse when the requirement is a warehouse, including its SQL dialect and BI integrations.
Can I use Postgres or ClickHouse for agent retrieval?
Postgres can combine native text search, pgvector, and a separate BM25 extension, but you operate multiple indexes, define rank fusion yourself, and scale the database with the retrieval workload. ClickHouse combines native token filtering with vector search but does not provide BM25 relevance. Infino supplies BM25, vector search, and RRF as one search relation inside SQL.
How do I migrate from Postgres or ClickHouse?
Export the columns, embeddings, and text you search, and append them into Infino tables over REST in Arrow or JSON. Dual-run, then move those reads. Keep the warehouse for BI.