pgvector Knowledge Graph Hybrid: Vector Search and Graph Traversal in PostgreSQL for 2026

TL;DR — pgvector + knowledge graph hybrid in PostgreSQL: vector search and graph traversal in one database. Microsoft Tech Community: "Combining AGE graph traversal with pgvector semantic search. Apache AGE brings Cypher based graph querying into PostgreSQL for GraphRAG pipelines." Instaclustr: "pgvector: open source PostgreSQL extension for vector data types and indexing. Hybrid search: vector similarity + keyword filtering with RRF fusion. Materialize scores, segment indexes, optimize parameters." pgvector GitHub: "Exact nearest neighbor by default, HNSW/IVFFlat for approximate search at scale." Learn more with knowledge graph basics, GraphRAG tutorial, FalkorDB vs Neo4j, and entity extraction.

Microsoft Tech Community frames the approach: "Combining AGE's graph traversal with pgvector's semantic search. Inspired by GraphRAG and PostgreSQL Integration in Docker with Cypher Query and AI Agents, which demonstrated how Apache AGE brings Cypher based graph querying into PostgreSQL for GraphRAG pipelines."

Instaclustr adds: "pgvector is an open source PostgreSQL extension that adds support for vector data types and vector-based indexing. It allows users to store, query, and search high-dimensional vector embeddings directly in a PostgreSQL database."

pgvector + Knowledge Graph Architecture

Extension Comparison

Implementation

from dataclasses import dataclass
from typing import Optional
from enum import Enum

class Extension(Enum):
    PGVECTOR = "pgvector"
    AGE = "age"
    FULLTEXT = "fulltext"
    PG_TRGM = "pg_trgm"

@dataclass
class PgvectorGraphHybridGuide:
    """pgvector + knowledge graph hybrid implementation."""

    def get_schema_setup(self) -> str:
        """Schema and extension setup."""
        return (
            "-- === SCHEMA SETUP ===\n"
            "\n"
            "-- 1. Install extensions\n"
            "CREATE EXTENSION IF NOT EXISTS vector;\n"
            "CREATE EXTENSION IF NOT EXISTS age;\n"
            "CREATE EXTENSION IF NOT EXISTS\n"
            "  pg_trgm;\n"
            "\n"
            "-- 2. Chunks table (documents)\n"
            "CREATE TABLE chunks (\n"
            "  id BIGSERIAL PRIMARY KEY,\n"
            "  doc_id UUID NOT NULL,\n"
            "  text TEXT NOT NULL,\n"
            "  embedding vector(384) NOT NULL,\n"
            "  tsv tsvector\n"
            "    GENERATED ALWAYS AS\n"
            "    (to_tsvector('english', text))\n"
            "    STORED,\n"
            "  metadata JSONB DEFAULT '{}'\n"
            ");\n"
            "\n"
            "-- 3. Entities table (KG nodes)\n"
            "CREATE TABLE entities (\n"
            "  id BIGSERIAL PRIMARY KEY,\n"
            "  name TEXT NOT NULL,\n"
            "  type TEXT NOT NULL,\n"
            "  embedding vector(384),\n"
            "  properties JSONB DEFAULT '{}'\n"
            ");\n"
            "\n"
            "-- 4. Relations table (KG edges)\n"
            "CREATE TABLE relations (\n"
            "  id BIGSERIAL PRIMARY KEY,\n"
            "  source_id BIGINT REFERENCES\n"
            "    entities(id),\n"
            "  target_id BIGINT REFERENCES\n"
            "    entities(id),\n"
            "  relation_type TEXT NOT NULL,\n"
            "  properties JSONB DEFAULT '{}'\n"
            ");\n"
            "\n"
            "-- 5. Mentions (chunk→entity)\n"
            "CREATE TABLE mentions (\n"
            "  chunk_id BIGINT REFERENCES\n"
            "    chunks(id),\n"
            "  entity_id BIGINT REFERENCES\n"
            "    entities(id),\n"
            "  PRIMARY KEY (chunk_id,\n"
            "    entity_id)\n"
            ");\n"
            "\n"
            "-- 6. Indexes\n"
            "-- Vector (HNSW recommended)\n"
            "CREATE INDEX ON chunks\n"
            "  USING hnsw (embedding\n"
            "    vector_cosine_ops);\n"
            "CREATE INDEX ON entities\n"
            "  USING hnsw (embedding\n"
            "    vector_cosine_ops);\n"
            "\n"
            "-- Full-text\n"
            "CREATE INDEX ON chunks\n"
            "  USING gin (tsv);\n"
            "\n"
            "-- Trigram (fuzzy)\n"
            "CREATE INDEX ON chunks\n"
            "  USING gin (text\n"
            "    gin_trgm_ops);\n"
            "\n"
            "-- Entity name\n"
            "CREATE INDEX ON entities\n"
            "  USING gin (name\n"
            "    gin_trgm_ops);\n"
            "\n"
            "-- Relations\n"
            "CREATE INDEX ON relations\n"
            "  (source_id, relation_type);\n"
            "CREATE INDEX ON relations\n"
            "  (target_id, relation_type);"
        )

    def get_vector_search(self) -> str:
        """pgvector similarity search."""
        return (
            "-- === VECTOR SEARCH ===\n"
            "\n"
            "-- Exact nearest neighbor\n"
            "-- (perfect recall, slower)\n"
            "SELECT id, text, embedding <=>\n"
            "  $query_vector AS distance\n"
            "FROM chunks\n"
            "ORDER BY embedding <=>\n"
            "  $query_vector\n"
            "LIMIT 10;\n"
            "\n"
            "-- ANN with HNSW\n"
            "-- (faster, slight recall loss)\n"
            "SET hnsw.ef_search = 100;\n"
            "SELECT id, text,\n"
            "  1 - (embedding <=>\n"
            "    $query_vector) AS similarity\n"
            "FROM chunks\n"
            "ORDER BY embedding <=>\n"
            "  $query_vector\n"
            "LIMIT 10;\n"
            "\n"
            "-- With metadata filter\n"
            "SELECT id, text,\n"
            "  1 - (embedding <=>\n"
            "    $query_vector) AS sim\n"
            "FROM chunks\n"
            "WHERE metadata @>\n"
            "  '{\"category\":\"tech\"}'\n"
            "ORDER BY embedding <=>\n"
            "  $query_vector\n"
            "LIMIT 10;\n"
            "\n"
            "-- Entity vector search\n"
            "SELECT id, name, type,\n"
            "  1 - (embedding <=>\n"
            "    $query_vector) AS sim\n"
            "FROM entities\n"
            "ORDER BY embedding <=>\n"
            "  $query_vector\n"
            "LIMIT 5;"
        )

    def get_graph_traversal(self) -> str:
        """Graph traversal with recursive CTE and AGE."""
        return (
            "-- === GRAPH TRAVERSAL ===\n"
            "\n"
            "-- 1. RECURSIVE CTE (no AGE)\n"
            "-- Multi-hop from entity\n"
            "WITH RECURSIVE graph_traverse AS (\n"
            "  -- Base: start entity\n"
            "  SELECT e.id, e.name,\n"
            "    e.type, 0 AS depth,\n"
            "    ARRAY[e.id] AS path\n"
            "  FROM entities e\n"
            "  WHERE e.name = $start_entity\n"
            "  UNION ALL\n"
            "  -- Recursive: follow edges\n"
            "  SELECT e2.id, e2.name,\n"
            "    e2.type, gt.depth + 1,\n"
            "    gt.path || e2.id\n"
            "  FROM graph_traverse gt\n"
            "  JOIN relations r\n"
            "    ON r.source_id = gt.id\n"
            "  JOIN entities e2\n"
            "    ON e2.id = r.target_id\n"
            "  WHERE gt.depth < 3\n"
            "    AND NOT e2.id = ANY(gt.path)\n"
            ")\n"
            "SELECT DISTINCT name, type,\n"
            "  depth\n"
            "FROM graph_traverse\n"
            "ORDER BY depth, name;\n"
            "\n"
            "-- 2. APACHE AGE (Cypher)\n"
            "-- Create graph\n"
            "SELECT create_graph('kg');\n"
            "\n"
            "-- Query with Cypher\n"
            "SELECT * FROM cypher('kg', $$\n"
            "  MATCH (p:Person)\n"
            "    -[:WORKS_AT]->(o:Organization)\n"
            "  RETURN p.name, o.name\n"
            "$$) AS (person text,\n"
            "        org text);\n"
            "\n"
            "-- Multi-hop with AGE\n"
            "SELECT * FROM cypher('kg', $$\n"
            "  MATCH (p:Person)\n"
            "    -[:WORKS_AT]->(o)\n"
            "    -[:PARTNER_OF]->(o2)\n"
            "  RETURN p.name, o.name,\n"
            "    o2.name\n"
            "$$) AS (p text, o text,\n"
            "        o2 text);\n"
            "\n"
            "-- 3. CHUNK→ENTITY EXPANSION\n"
            "-- Find entities in chunks\n"
            "SELECT c.id, c.text,\n"
            "  e.name, e.type\n"
            "FROM chunks c\n"
            "JOIN mentions m\n"
            "  ON m.chunk_id = c.id\n"
            "JOIN entities e\n"
            "  ON e.id = m.entity_id\n"
            "WHERE c.id IN (\n"
            "  SELECT id FROM chunks\n"
            "  ORDER BY embedding <=>\n"
            "    $query_vector\n"
            "  LIMIT 5\n"
            ");"
        )

    def get_hybrid_search(self) -> str:
        """Hybrid search with RRF fusion."""
        return (
            "-- === HYBRID SEARCH (RRF) ===\n"
            "\n"
            "-- Reciprocal Rank Fusion\n"
            "-- combining vector + fulltext\n"
            "WITH vector_results AS (\n"
            "  SELECT id, text,\n"
            "    ROW_NUMBER() OVER (\n"
            "      ORDER BY embedding <=>\n"
            "        $query_vector\n"
            "    ) AS rank\n"
            "  FROM chunks\n"
            "  ORDER BY embedding <=>\n"
            "    $query_vector\n"
            "  LIMIT 50\n"
            "),\n"
            "fulltext_results AS (\n"
            "  SELECT id, text,\n"
            "    ROW_NUMBER() OVER (\n"
            "      ORDER BY ts_rank_cd(\n"
            "        tsv,\n"
            "        to_tsquery('english',\n"
            "          $query_text)\n"
            "      ) DESC\n"
            "    ) AS rank\n"
            "  FROM chunks\n"
            "  WHERE tsv @@\n"
            "    to_tsquery('english',\n"
            "      $query_text)\n"
            "  LIMIT 50\n"
            ")\n"
            "SELECT COALESCE(v.id, f.id)\n"
            "  AS id,\n"
            "  COALESCE(v.text, f.text)\n"
            "  AS text,\n"
            "  -- RRF: 1/(k+rank)\n"
            "  COALESCE(\n"
            "    1.0/(60 + v.rank), 0)\n"
            "    + COALESCE(\n"
            "    1.0/(60 + f.rank), 0)\n"
            "  AS rrf_score\n"
            "FROM vector_results v\n"
            "FULL OUTER JOIN\n"
            "  fulltext_results f\n"
            "  ON v.id = f.id\n"
            "ORDER BY rrf_score DESC\n"
            "LIMIT 10;\n"
            "\n"
            "-- === VECTOR + GRAPH HYBRID ===\n"
            "-- Semantic seeds + graph expansion\n"
            "WITH vector_seeds AS (\n"
            "  SELECT c.id, c.text,\n"
            "    1 - (c.embedding <=>\n"
            "      $query_vector) AS sim\n"
            "  FROM chunks c\n"
            "  ORDER BY c.embedding <=>\n"
            "    $query_vector\n"
            "  LIMIT 5\n"
            "),\n"
            "expanded AS (\n"
            "  SELECT DISTINCT\n"
            "    c2.id, c2.text,\n"
            "    MAX(vs.sim) AS best_sim,\n"
            "    count(DISTINCT e.id)\n"
            "      AS entity_count\n"
            "  FROM vector_seeds vs\n"
            "  JOIN mentions m1\n"
            "    ON m1.chunk_id = vs.id\n"
            "  JOIN entities e\n"
            "    ON e.id = m1.entity_id\n"
            "  JOIN mentions m2\n"
            "    ON m2.entity_id = e.id\n"
            "  JOIN chunks c2\n"
            "    ON c2.id = m2.chunk_id\n"
            "  WHERE c2.id <> vs.id\n"
            "  GROUP BY c2.id, c2.text\n"
            ")\n"
            "SELECT id, text,\n"
            "  best_sim, entity_count,\n"
            "  -- Combined score\n"
            "  best_sim * 0.7 +\n"
            "    (entity_count::float / 10)\n"
            "    * 0.3 AS combined\n"
            "FROM expanded\n"
            "ORDER BY combined DESC\n"
            "LIMIT 10;"
        )

    def get_python_integration(self) -> str:
        """Python psycopg2 integration."""
        return (
            "# === PYTHON INTEGRATION ===\n"
            "import psycopg2\n"
            "import numpy as np\n"
            "\n"
            "conn = psycopg2.connect(\n"
            "    'postgresql://user:pass'\n"
            "    '@localhost:5432/mydb')\n"
            "\n"
            "# 1. INSERT WITH VECTOR\n"
            "embedding = np.random(\n"
            "    384).tolist()\n"
            "with conn.cursor() as cur:\n"
            "    cur.execute('''\n"
            "        INSERT INTO chunks\n"
            "          (doc_id, text,\n"
            "           embedding)\n"
            "          VALUES (%s, %s, %s)\n"
            "    ''',\n"
            "        ('doc-1', 'Sample text',\n"
            "         embedding))\n"
            "    conn.commit()\n"
            "\n"
            "# 2. VECTOR SEARCH\n"
            "query_vec = np.random(\n"
            "    384).tolist()\n"
            "with conn.cursor() as cur:\n"
            "    cur.execute('''\n"
            "        SELECT id, text,\n"
            "          1 - (embedding <=>\n"
            "            %s) AS sim\n"
            "        FROM chunks\n"
            "        ORDER BY embedding <=>\n"
            "          %s\n"
            "        LIMIT 10\n"
            "    ''',\n"
            "        (query_vec,\n"
            "         query_vec))\n"
            "    for row in cur.fetchall():\n"
            "        print(\n"
            "            f'{row[0]} '\n"
            "            f'{row[2]:.3f} '\n"
            "            f'{row[1][:50]}')\n"
            "\n"
            "# 3. HYBRID SEARCH (RRF)\n"
            "with conn.cursor() as cur:\n"
            "    cur.execute('''\n"
            "        WITH vec AS (\n"
            "          SELECT id, text,\n"
            "            ROW_NUMBER() OVER (\n"
            "              ORDER BY\n"
            "                embedding <=>\n"
            "                  %s) AS rk\n"
            "          FROM chunks\n"
            "          LIMIT 50\n"
            "        ), fts AS (\n"
            "          SELECT id, text,\n"
            "            ROW_NUMBER() OVER (\n"
            "              ORDER BY\n"
            "                ts_rank_cd(\n"
            "                  tsv,\n"
            "                  to_tsquery(\n"
            "                    %s)) DESC\n"
            "            ) AS rk\n"
            "          FROM chunks\n"
            "          WHERE tsv @@\n"
            "            to_tsquery(%s)\n"
            "          LIMIT 50\n"
            "        )\n"
            "        SELECT\n"
            "          COALESCE(v.id,f.id),\n"
            "          COALESCE(v.text,\n"
            "            f.text),\n"
            "          COALESCE(\n"
            "            1.0/(60+v.rk),0)\n"
            "            + COALESCE(\n"
            "            1.0/(60+f.rk),0)\n"
            "            AS rrf\n"
            "        FROM vec v\n"
            "        FULL OUTER JOIN\n"
            "          fts f ON v.id=f.id\n"
            "        ORDER BY rrf DESC\n"
            "        LIMIT 10\n"
            "    ''',\n"
            "        (query_vec,\n"
            "         query_text,\n"
            "         query_text))\n"
            "    results = cur.fetchall()\n"
            "\n"
            "# 4. GRAPH EXPANSION\n"
            "with conn.cursor() as cur:\n"
            "    cur.execute('''\n"
            "        WITH seeds AS (\n"
            "          SELECT id FROM chunks\n"
            "          ORDER BY embedding <=>\n"
            "            %s LIMIT 5\n"
            "        )\n"
            "        SELECT DISTINCT\n"
            "          c2.text, e.name,\n"
            "          e.type\n"
            "        FROM seeds s\n"
            "        JOIN mentions m1\n"
            "          ON m1.chunk_id = s.id\n"
            "        JOIN entities e\n"
            "          ON e.id = m1.entity_id\n"
            "        JOIN mentions m2\n"
            "          ON m2.entity_id = e.id\n"
            "        JOIN chunks c2\n"
            "          ON c2.id = m2.chunk_id\n"
            "    ''',\n"
            "        (query_vec,))\n"
            "    for row in cur.fetchall():\n"
            "        print(row)\n"
            "\n"
            "conn.close()"
        )

    def get_optimization_tips(self) -> dict:
        """Expert optimization tips."""
        return {
            "materialize_scores": "Precompute and cache hybrid scores in materialized views for high-traffic endpoints. Reduces CPU from real-time fusion.",
            "segment_indexes": "Partition dataset and maintain separate HNSW/IVFFlat indexes for different categories, roles, or time ranges. Reduces candidate sets.",
            "index_only_scans": "Store precomputed similarity metrics as generated columns and index those. Enables index-only scans in hybrid patterns.",
            "dynamic_params": "Adjust HNSW ef_search, ef_construction, IVFFlat nlist based on query context. Session-level settings or role-based configs.",
            "embedding_quantization": "Apply PCA or product quantization to compress embeddings before insertion. Store millions of vectors efficiently.",
            "rrf_k_parameter": "RRF k parameter (typically 60) controls fusion balance. Higher k smooths rank differences, lower k amplifies top ranks.",
            "combined_scoring": "Weighted combination: vector_sim * w1 + graph_expansion * w2 + fulltext_rank * w3. Tune weights on eval set.",
        }

pgvector Knowledge Graph Hybrid Checklist

• [ ] pgvector: open-source PostgreSQL extension for vector data types and similarity search
• [ ] pgvector: store high-dimensional vector embeddings directly in PostgreSQL
• [ ] pgvector: vector type — vector(384), vector(1536), configurable dimensions
• [ ] pgvector: distance operators — <=> (cosine), <-> (L2), <#> (inner product)
• [ ] pgvector: exact nearest neighbor by default (perfect recall, slower)
• [ ] pgvector: HNSW index (recommended) for approximate nearest neighbor (faster, slight recall loss)
• [ ] pgvector: IVFFlat index as alternative ANN method
• [ ] pgvector: HNSW parameters — ef_search, ef_construction tunable
• [ ] pgvector: IVFFlat parameters — nlist, n_probe tunable
• [ ] Apache AGE: PostgreSQL extension for Cypher graph queries
• [ ] Apache AGE: OpenCypher compatible — same syntax as Neo4j/FalkorDB
• [ ] Apache AGE: CREATE EXTENSION age, SELECT * FROM cypher('graph', $$ ... $$)
• [ ] Apache AGE: multi-hop: MATCH (n)-[:TYPE*1..3]->(m)
• [ ] Apache AGE: create nodes: CREATE (n:Person {name:'Alice'})
• [ ] Apache AGE: merge: MERGE (n:Person {name:$name})
• [ ] Recursive CTE: native SQL graph traversal without AGE extension
• [ ] Recursive CTE: WITH RECURSIVE — base case + recursive case with depth limit and path tracking
• [ ] Full-text search: tsvector, tsquery, ts_rank_cd with GIN index
• [ ] Full-text search: GENERATED ALWAYS AS (to_tsvector('english', text)) STORED for auto-indexing
• [ ] pg_trgm: trigram similarity for fuzzy matching with GIN index
• [ ] Schema: chunks (id, doc_id, text, embedding, tsv, metadata), entities (id, name, type, embedding, properties)
• [ ] Schema: relations (source_id, target_id, relation_type, properties), mentions (chunk_id, entity_id)
• [ ] Indexes: HNSW on chunks.embedding and entities.embedding, GIN on chunks.tsv, GIN on text gin_trgm_ops
• [ ] Indexes: B-tree on relations (source_id, relation_type) and (target_id, relation_type)
• [ ] Hybrid search: vector subquery + full-text subquery + RRF fusion
• [ ] RRF: 1/(k+rank) for each result source, sum scores, k typically 60
• [ ] Vector + graph hybrid: vector seeds → mentions → entities → mentions → chunks for graph expansion
• [ ] Combined scoring: vector_sim * w1 + graph_expansion * w2 + fulltext_rank * w3
• [ ] Materialize hybrid scores in materialized views for high-traffic endpoints
• [ ] Segment ANN indexes by metadata constraints (category, role, time range)
• [ ] Optimize vector index parameters dynamically (session-level or role-based)
• [ ] Use embedding quantization (PCA, product quantization) for large-scale storage
• [ ] RRF k parameter controls fusion balance — higher k smooths, lower k amplifies top ranks
• [ ] Python: psycopg2 with vector parameters as lists, SQL queries for hybrid search
• [ ] Python: conn.cursor() for queries, conn.commit() for writes
• [ ] Single PostgreSQL: vectors + graphs + relational data in one database
• [ ] ACID transactions: vector inserts, graph updates, relational changes in one transaction
• [ ] Simpler infrastructure: no external vector DB or graph DB needed
• [ ] Existing PostgreSQL skills: DBAs and developers already know PostgreSQL
• [ ] Cost: no separate vector DB or graph DB licenses
• [ ] Limitations: not as optimized as dedicated graph DBs for complex graph algorithms
• [ ] Limitations: not as optimized as dedicated vector DBs for billion-scale vectors
• [ ] Read knowledge graph basics for concepts
• [ ] Read GraphRAG tutorial for GraphRAG frameworks
• [ ] Read FalkorDB vs Neo4j for dedicated graph DBs
• [ ] Read entity extraction for extraction pipelines
• [ ] Test: vector search returns relevant results with cosine similarity
• [ ] Test: HNSW index improves query latency vs exact search
• [ ] Test: full-text search finds exact keyword matches
• [ ] Test: RRF fusion improves recall over single-method search
• [ ] Test: graph expansion finds related chunks via entity mentions
• [ ] Test: recursive CTE traverses multi-hop paths correctly
• [ ] Test: Apache AGE Cypher queries return expected results
• [ ] Test: combined scoring weights are tuned on eval set
• [ ] Document schema, indexes, hybrid query strategy, optimization parameters, Python integration

FAQ

How do you combine pgvector with knowledge graph traversal in PostgreSQL?

Use pgvector for semantic vector search and Apache AGE or recursive CTEs for graph traversal in a single PostgreSQL database. Microsoft Tech Community: "Combining AGE graph traversal with pgvector semantic search. Inspired by GraphRAG and PostgreSQL Integration in Docker with Cypher Query and AI Agents, demonstrating how Apache AGE brings Cypher based graph querying into PostgreSQL for GraphRAG pipelines." Combination: (1) pgvector: store and query vector embeddings with HNSW/IVFFlat indexes. (2) Apache AGE: Cypher graph queries in PostgreSQL for entity-relationship traversal. (3) Recursive CTE: native SQL graph traversal without AGE extension. (4) Hybrid: vector search seeds → graph expansion → re-rank. (5) Single database: no external graph DB needed. (6) RRF fusion: combine vector + fulltext + graph ranks.

What is pgvector and how does it enable vector search in PostgreSQL?

pgvector is an open-source PostgreSQL extension for vector data types and similarity search. Instaclustr: "pgvector is open source PostgreSQL extension that adds support for vector data types and vector-based indexing. Store, query, and search high-dimensional vector embeddings directly in PostgreSQL. Specialized indexing: approximate nearest neighbor (ANN) using IVFFlat and HNSW. Bridges gap between traditional relational data and modern unstructured data representations." pgvector GitHub: "By default, pgvector performs exact nearest neighbor search with perfect recall. Add an index for approximate nearest neighbor search, trading some recall for speed." Features: (1) vector type: store embeddings as vector(384) or vector(1536). (2) Distance: <-> (L2), <#> (inner product), <=> (cosine). (3) Indexes: HNSW (recommended), IVFFlat. (4) Exact: default, perfect recall. (5) ANN: HNSW/IVFFlat for speed at scale. (6) Hybrid: combine with full-text search via RRF.

How do you perform hybrid search with pgvector?

Combine vector similarity search with full-text search using Reciprocal Rank Fusion (RRF). Instaclustr Tutorial: "Hybrid search combines vector-based similarity search with traditional keyword or metadata filtering. Vector search leverages embeddings for semantic similarity, full-text finds exact keyword matches. Two-part query: one subquery for vector search, another for full-text. Aggregate and re-rank using Reciprocal Rank Fusion (RRF). Hybrid approach boosts results that rank well in both dimensions, improving recall and relevance without external tools." Expert Tips: "Materialize hybrid scores for frequent queries. Segment ANN indexes by metadata constraints. Optimize vector index parameters dynamically. Use embedding quantization for large-scale storage." Hybrid: (1) Vector subquery: ORDER BY embedding <=> $query_vector. (2) Full-text subquery: ts_rank_cd with to_tsquery. (3) RRF fusion: 1/(k+rank) for each, sum scores. (4) Materialized views for frequent queries. (5) Partition indexes by category/role/time.

How do you use Apache AGE for graph queries in PostgreSQL?

Apache AGE is a PostgreSQL extension that brings Cypher graph querying into PostgreSQL. Microsoft Tech Community: "Apache AGE brings Cypher based graph querying into PostgreSQL for GraphRAG pipelines. Combining AGE graph traversal with pgvector semantic search." Apache AGE: (1) CREATE EXTENSION age. (2) SELECT * FROM cypher('graph_name', $$ MATCH (n:Person)-[:WORKS_AT]->(o) RETURN n.name, o.name $$) AS (person text, org text). (3) OpenCypher compatible — same syntax as Neo4j/FalkorDB. (4) Multi-hop: MATCH (n)-[:KNOWS*1..3]->(m). (5) Create: CREATE (n:Person {name:'Alice'}). (6) Merge: MERGE (n:Person {name:$name}). (7) Graph in PostgreSQL — no external graph DB. (8) Combine with pgvector for hybrid vector + graph retrieval.

What are the advantages of using PostgreSQL for both vectors and graphs?

Single database for vectors, graphs, and relational data — simpler infrastructure, ACID transactions, SQL queries. Advantages: (1) Single database: pgvector for vectors, Apache AGE for graphs, PostgreSQL for relational — no external graph DB or vector DB. (2) ACID transactions: vector inserts, graph updates, and relational changes in one transaction. (3) SQL queries: combine vector similarity, graph traversal, and SQL filtering in one query. (4) Hybrid retrieval: vector search → graph expansion → SQL filtering → RRF fusion. (5) Simpler infrastructure: one database to manage, backup, monitor. (6) Existing PostgreSQL skills: DBAs and developers already know PostgreSQL. (7) Extensions ecosystem: pgvector, Apache AGE, pg_trgm, full-text search. (8) Cost: no separate vector DB or graph DB licenses. (9) Performance: HNSW indexes for vectors, AGE indexes for graphs. (10) Limitations: not as optimized as dedicated graph DBs (Neo4j, FalkorDB) for complex graph algorithms, or dedicated vector DBs (Qdrant, Milvus) for billion-scale vectors.

Want a self-hosted AI company brain that does all of this out of the box?
Book a demo →