Inside SearchFit Engineering: Our pgvector and TimescaleDB Stack

If you’re tracking brand visibility across ChatGPT, Perplexity, Gemini, Claude, and Grok, you already know the old rules don’t apply. Traditional rank-tracking tools weren’t built to handle citations that appear inside generated paragraphs, disappear in the next conversation turn, and vary wildly by prompt intent. At SearchFit we needed something fundamentally different, a data layer that could store and query billions of citation events, understand semantic relevance, and serve real-time dashboards without melting down. This post is the engineering walkthrough we wish we’d had two years ago. It covers our core pgvector timescaledb stack: pgvector for vector similarity search on citation embeddings, and TimescaleDB for time-series analytics on everything that moves. No fluff, just operator-level decisions and code.

Prerequisites

Before diving in, make sure you have a solid grasp of:

• PostgreSQL fundamentals, extensions, partitions, triggers, and the query planner.
• Vector similarity search concepts: embeddings, cosine distance, approximate nearest neighbor indexes.
• Time-series databases and the difference between hypertables and standard PostgreSQL tables.
• Basic familiarity with SQLAlchemy or your preferred Python ORM (our examples use raw SQL because that’s where the leverage lives).

You’ll also need a running PostgreSQL 15+ instance with the pgvector and timescaledb extensions installed. If you’re starting from scratch, the official pgvector documentation and Timescale documentation are your first stops.

Step 1: Schema Design for Citation Vectors Using pgvector

The unit of work in SearchFit is a citation: a specific snippet of text where an AI answer engine mentions a brand, product, or topic we’re tracking. Each citation carries metadata, source engine (ChatGPT/Perplexity/Gemini/Claude/Grok), prompt used, timestamp, brand ID, URL if available, and a text column with the exact cited passage. The text gets embedded into a 1536-dimensional vector (we use OpenAI’s text-embedding-3-small; swap for your own if you prefer). Those vectors are what pgvector indexes so we can run semantic similarity queries.

1.1. Install and Enable the Extension

CREATE EXTENSION IF NOT EXISTS vector;

You can confirm with SELECT * FROM pg_extension WHERE extname = 'vector';. If you’re on a managed service, test that the extension is available. On self-hosted, you’ll install it from the pgvector GitHub repository.

1.2. Create the Citation Table

CREATE TABLE citations (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    brand_id INTEGER NOT NULL,
    engine VARCHAR(50) NOT NULL,
    prompt_hash VARCHAR(64) NOT NULL,
    citation_text TEXT NOT NULL,
    embedding VECTOR(1536),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_citations_engine ON citations (engine);
CREATE INDEX idx_citations_brand_id_created_at ON citations (brand_id, created_at DESC);

We store the raw text and the embedding so we can inspect what the model actually said, critical for our Validator agent, which scores hallucinations and factual drift. (Read how that agent works in our autonomous content validation breakdown.)

1.3. Build an Index for Fast Vector Search

pgvector supports two ANN index types: IVFFlat and HNSW. We started with IVFFlat, then migrated to HNSW for better query-per-second performance after benchmarks showed a 40% latency improvement at the 10,000-embedding scale and beyond. The Crunchy Data performance guide and Instaclustr’s benchmark results were essential reading.

Create the HNSW index after populating a representative sample of data:

CREATE INDEX ON citations USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 200);

For production, test m and ef_construction values under your workload. Higher ef_construction makes index building slower but improves recall.

Step 2: Time-Series Tracking with TimescaleDB

Vectors alone don’t tell you when a citation happened, how often a brand gets cited per day, or whether a competitor’s share of voice is growing. That’s where TimescaleDB comes in. It turns PostgreSQL into a full time-series database with automatic partitioning, columnar compression, and continuous aggregates. We use it to store and query every citation event as a time-series fact.

2.1. Enable TimescaleDB

CREATE EXTENSION IF NOT EXISTS timescaledb;

Follow the Timescale documentation to verify your server configuration (especially shared_preload_libraries).

2.2. Create a Hypertable for Citation Events

We keep a separate event table from the vector store, this lets us scale time-series queries independently and apply compression aggressively without touching the embedding data.

CREATE TABLE citation_events (
    time TIMESTAMPTZ NOT NULL,
    brand_id INTEGER NOT NULL,
    engine VARCHAR(50),
    prompt_hash VARCHAR(64),
    citation_id BIGINT,
    is_first_occurrence BOOLEAN,
    snippet_length INTEGER
);

SELECT create_hypertable('citation_events', 'time', chunk_time_interval => INTERVAL '1 day');

CREATE INDEX idx_citation_events_brand_time ON citation_events (brand_id, time DESC);

Each time we insert a citation vector into citations, we also write a row here. The citation_id foreign key links back, but for analytics we rarely join, we aggregate directly on this table.

2.3. Automatic Compression and Retention

After 7 days, we compress chunks to save up to 90% disk space:

SELECT add_compression_policy('citation_events', INTERVAL '7 days');

And we drop raw data older than 24 months (regulatory and compliance needs vary; check your own data retention policy). The NIST AI Risk Management Framework offers useful context for production AI systems handling third-party data. While the NIST AI RMF isn’t a database tutorial, its governance principles apply directly to tracking and retaining AI-generated citations.

Step 3: Querying Citations by Semantic Similarity and Time Range

The real power comes when you combine vector and time-series queries. A typical SearchFit user wants to know: “Show me all recent citations about ‘enterprise AEO platform’ across Claude and Gemini, grouped by week.” That’s a two-part query: semantic similarity on the text embedding, plus time-bucketed aggregation.

3.1. The Hybrid Query Pattern

We use a CTE to fetch the top-K similar citations from the vector store, then join to the time-series table for aggregation.

WITH similar AS (
    SELECT id, citation_text, brand_id,
           1 - (embedding <=> query_embedding) AS similarity
    FROM citations
    WHERE engine IN ('claude', 'gemini')
    ORDER BY embedding <=> query_embedding
    LIMIT 100
)
SELECT date_trunc('week', ce.time) AS week,
       s.brand_id,
       COUNT(*) AS citation_count
FROM similar s
JOIN citation_events ce ON s.id = ce.citation_id
WHERE ce.time >= NOW() - INTERVAL '30 days'
GROUP BY week, s.brand_id
ORDER BY week DESC;

The <=> operator computes cosine distance; we invert it to a similarity score. We default to LIMIT 100 and then let the time filter narrow the set. This keeps the vector index probe light.

3.2. Materialized Combined Views for Dashboards

For our multi-source analytics dashboards, we pre-aggregate a weekly materialized view that joins the two stores:

CREATE MATERIALIZED VIEW weekly_engine_citations AS
SELECT date_trunc('week', ce.time) AS week,
       ce.engine,
       ce.brand_id,
       COUNT(*) FILTER (WHERE ce.is_first_occurrence) AS new_citations,
       COUNT(*) AS total_citations
FROM citation_events ce
JOIN citations c ON c.id = ce.citation_id
GROUP BY week, ce.engine, ce.brand_id;

SELECT add_continuous_aggregate_policy('weekly_engine_citations',
    start_offset => INTERVAL '2 weeks',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

TimescaleDB’s continuous aggregate policy automatically refreshes this view on a schedule. We tie it directly into our GA4 and GSC integrations so customers can compare AI citation trends with organic traffic, closing the AEO loop.

Step 4: Real-Time Citation Monitoring with Triggers and Streaming

Answer engine visibility is erratic; a brand can be cited in a thousand conversations in a single hour, then zero the next. Our users needed real-time alerts, not just daily reports. We built a streaming pipeline that publishes every citation event to a Redis pub/sub channel, which then fans out to our Zapier integration and the SearchFit API.

4.1. Database Triggers as Event Publishers

We use a PostgreSQL AFTER INSERT trigger on citations that pushes a JSON payload to a notifications table:

CREATE TABLE citation_notifications (
    id BIGSERIAL PRIMARY KEY,
    payload JSONB NOT NULL,
    processed BOOLEAN DEFAULT FALSE
);

CREATE OR REPLACE FUNCTION notify_citation_event()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO citation_notifications (payload)
    VALUES (jsonb_build_object(
        'citation_id', NEW.id,
        'brand_id', NEW.brand_id,
        'engine', NEW.engine,
        'snippet', LEFT(NEW.citation_text, 200),
        'created_at', NEW.created_at
    ));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_citation_insert
AFTER INSERT ON citations
FOR EACH ROW
EXECUTE FUNCTION notify_citation_event();

A lightweight worker then polls citation_notifications and publishes events, marking rows as processed. This design keeps the trigger fast, no external calls from inside the database.

4.2. Streaming to Clients with the MCP Server

For Claude-integrated workflows, we expose citation events through the SearchFit MCP server. The server queries time-series aggregates and also subscribes to the Redis stream for live updates. Users can ask, “Have any competitor brands appeared in Perplexity citations in the last hour?” and get a real-time answer. Under the hood, the query hits the citation_events hypertable with a time filter, then optionally enriches with vector similarity if the question contains a semantic component.

Step 5: Performance Tuning and Production Hardening

Running pgvector and TimescaleDB together at production volume required tuning several knobs. We learned most of these the hard way during our Claude 4.7 migration, where we re-ran 10,000 AEO audits and cut costs by 34% while improving accuracy. The load on the vector database spiked because every audit re-ran generated fresh embeddings. Here’s what we adjusted.

5.1. pgvector Index Tuning

• ef_search: Set at query time. For dashboards, we use ef_search = 100 (high recall); for less critical batch jobs, we drop to 40. Override it per session: SET hnsw.ef_search = 100;
• Parallelism: pgvector supports parallel index scans, but we found that limiting max_parallel_workers_per_gather to 2 per query kept CPU from being flooded during large batch embedding inserts.
• List building: The IVFFlat kmeans implementation for building centroids works best when you have at least 10× the number of vectors per cluster. For HNSW, the Severalnines deep dive explains neighbor selection mechanics. In short: m=16 gave us a good trade-off; higher m boosts recall but increases build time and memory.

5.2. TimescaleDB Compression and Chunk Sizing

We moved from 1-hour chunks to 1-day chunks after seeing query planner overhead on high-cardinality time ranges. With chunk_time_interval => INTERVAL '1 day', the planner has fewer chunks to scan for a 30-day query. Compression settings also matter:

ALTER TABLE citation_events
SET (timescaledb.compress,
     timescaledb.compress_segmentby = 'brand_id',
     timescaledb.compress_orderby = 'time DESC');

Segmenting by brand_id means compression groups rows for the same brand together, making decompression on brand-filtered queries efficient.

5.3. Connection Pooling and Monitoring

We run PgBouncer in transaction mode between our application servers and the database. Without it, the citation_notifications polling and simultaneous embedding inserts would exhaust connection limits. We also monitor pg_stat_user_tables and timescaledb_information.job_stats to catch compression lag or stuck continuous aggregate refresh jobs.

Pro Tips

• Don’t join hypertables on unfiltered columns. A join between citations and citation_events without a time filter on the hypertable forces a full chunk scan. Always push a WHERE time > … into the query to let TimescaleDB leverage partition pruning.
• Pre-warm vector indexes. After a large bulk insert, the HNSW index may not reflect the new data immediately. Run a manual VACUUM ANALYZE citations; and then re-run a representative workload to warm the graph. pgvector’s index is immutable except for rebuilds; incremental build algorithms are still experimental.
• Embedding reuse. We cache embeddings for frequently seen prompts (prompt_hash column) so we don’t repeatedly call the embedding API. This alone cut our embedding costs by about 25% during the Claude 4.7 audit rerun. The validator agent checks that the text hasn’t changed before reusing the cached vector.
• Be stingy with dimensionality. OpenAI’s current 1536-dimension embeddings work well, but always evaluate if a smaller model (e.g., 768-dim) meets your recall requirements. Smaller dimensions reduce index size and speed up queries. The Tiger Data Learn guide on PostgreSQL as a vector database provides a practical walkthrough of dimensionality trade-offs.

Wrapping It Up: Key Takeaways

The combination of pgvector for semantic retrieval and TimescaleDB for time-series analytics gives us a single, operational PostgreSQL cluster that handles:

1. Vector similarity search on millions of AI-generated citation snippets.
2. Time-based aggregation of citation frequency, trend lines, and share-of-voice metrics.
3. Real-time event streaming via triggers, with no additional infrastructure beyond Redis for pub/sub.
4. Cost-efficient data management through automatic compression and retention policies.

This architecture underpins every piece of SearchFit’s answer engine optimization workflow, from the multi-agent orchestration that coordinates instant audits to the persistent agent memory that accumulates brand knowledge over weeks. The validator and scorer agents use vector similarity to detect semantic drift; the dashboard team embedded these queries behind GA4-mirroring charts.

If you’re building an AI visibility tracker, start with this stack instead of cobbling together a vector store and a separate time-series database. The operational simplicity of a single PostgreSQL cluster, where you can join a vector result set to a hypertable in one SELECT, eliminates an entire class of distributed system bugs.

Ready to go deeper? Our AEO agent team architecture post walks through the full system design. To see how we evolved from single prompts to this agentic engine, read From Prompts to Agents. And if you want to try the monitoring side yourself, the automated AEO report templates give you copy-paste API calls.

Now go build something that works.