Autovacuum for Knowledge

One entity in my knowledge graph has 282,950 observations. That number is not a mistake. The knowledge graph has been running continuously for months, every agent session adding facts about infrastructure, decisions, and code as work happens. Most entities stay manageable — a few dozen observations. But the busiest ones accumulate without bound, and at 282,950 entries the JSON column is no longer a knowledge store. It is a graveyard.

The fix I built is what I am calling autovacuum for knowledge: a PostgreSQL-native queue where any LLM can claim a compaction job, send it to every available model in parallel, store all the results, and let empirical data decide which model compressed best. It is running in production now. Here is how it works, and why the pattern matters beyond knowledge graphs.

---

The problem with accumulation

The knowledge graph stores entities as rows in a PostgreSQL table, with observations as a JSONB array on each row. Every time an agent learns something new — a service changed its port, a deployment strategy shifted, a decision was revisited — it appends to that array. This is intentional. The graph is an append-only log of facts, not a snapshot of the current state.

An earlier migration introduced dictionary-based token substitution: short codes assigned to entity names, relation types, and property keys. An entity that would normally cost 125 tokens to load as raw JSON costs 5 tokens at L0 — a 25x reduction. But this compression reformats. It does not deduplicate. An entity with 282,950 observations that all say approximately the same thing gets reformatted into 282,950 compressed entries. The underlying problem — redundant, superseded, repeated knowledge — is not addressed. Reformatting a graveyard does not make it a library.

What was needed was a process analogous to PostgreSQL's own autovacuum: a background job that detects bloat, reclaims space, and keeps the table healthy without requiring manual intervention. Autovacuum for tables removes dead row versions created by updates and deletes. Autovacuum for knowledge removes dead facts: superseded decisions, repeated observations, stale details that have been absorbed into newer summaries.

---

The queue architecture

The design is PG-native from the trigger to the cleanup. No separate scheduler, no Redis, no Celery. PostgreSQL is the coordinator, the audit log, and the benchmark dataset all at once.

The central table is the compaction queue:

CREATE TABLE kg_compaction_queue (
    id          SERIAL PRIMARY KEY,
    entity_id   INTEGER NOT NULL REFERENCES knowledge_entities(id) ON DELETE CASCADE,
    priority    INTEGER NOT NULL DEFAULT 0,
    status      VARCHAR(20) NOT NULL DEFAULT 'pending',
    claimed_by  VARCHAR(128),
    claimed_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- One pending or claimed job per entity at a time
CREATE UNIQUE INDEX idx_compaction_queue_entity_active
    ON kg_compaction_queue(entity_id)
    WHERE status IN ('pending', 'claimed');

The unique partial index is doing real work here. An entity can only have one active job. If the trigger fires again while a compaction is already claimed, ON CONFLICT DO NOTHING absorbs it silently. No duplicate jobs, no coordinator needed.

The trigger fires after every observation change:

CREATE OR REPLACE FUNCTION trg_check_compaction()
RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
    IF OLD.observations IS DISTINCT FROM NEW.observations THEN
        PERFORM kg_check_compaction_needed(NEW.id);
    END IF;
    RETURN NEW;
END;
$$;

kg_check_compaction_needed reads the threshold from a singleton config table — default 12 observations — and inserts into the queue if the count exceeds it. Thresholds are tunable at runtime without a migration.

Claiming work without contention

Multiple workers can run simultaneously. The claim function uses FOR UPDATE SKIP LOCKED, the correct primitive for concurrent queue consumers in PostgreSQL:

SELECT q.id, q.entity_id
INTO v_queue_id, v_entity_id
FROM kg_compaction_queue q
WHERE q.status = 'pending'
ORDER BY q.priority DESC, q.created_at ASC
LIMIT 1
FOR UPDATE SKIP LOCKED;

SKIP LOCKED means a worker that races to claim the same job does not block — it simply skips the locked row and moves to the next one. No deadlocks. No contention. The workers are naturally distributed across the queue without any application-level coordination.

Stale claims expire automatically: if a worker crashes mid-compaction, the claim timeout (default 5 minutes) resets the job to pending when the next worker calls the claim function.

---

The fan-out principle: do not assume which model compacts best

This is the part I am most confident about, even though it means doing more work upfront.

When a compaction job is claimed, the worker does not pick a model and send the job there. It sends the job to every available model simultaneously. Each model gets the same prompt, the same observations, and the same task: summarize these facts into a compact set that preserves all semantically distinct information.

Every result goes into a candidates table with columns for model used, observation count, token count, latency, and whether the result was selected. A benchmark view aggregates this over time:

SELECT
    model_used,
    COUNT(*) AS total_runs,
    COUNT(*) FILTER (WHERE was_selected) AS times_selected,
    ROUND(AVG(obs_count)::NUMERIC, 1) AS avg_obs_count,
    ROUND(AVG(token_count)::NUMERIC, 1) AS avg_token_count,
    ROUND(AVG(latency_ms)::NUMERIC, 0) AS avg_latency_ms,
    ROUND(
        (COUNT(*) FILTER (WHERE was_selected))::NUMERIC
        / NULLIF(COUNT(*), 0) * 100, 1
    ) AS selection_rate_pct
FROM kg_compaction_candidates
GROUP BY model_used
ORDER BY times_selected DESC, avg_latency_ms;

After enough compaction runs, this table tells me empirically which model compacts knowledge most effectively across different entity types. Maybe a small local model is fast and good enough for simple service entities. Maybe a larger model performs better on entities with complex causal relationships and architectural reasoning. I do not know yet. That is the point. The system will learn it, and the selection logic can become adaptive as the dataset grows.

The worker side:

async def compact_entity(job: CompactionJob) -> str:
    """Fan out to all available models, store all candidates, pick best."""
    started_at = datetime.now(timezone.utc)

    # Fan out to every available model concurrently
    tasks = [
        compact_with_model(job, model)
        for model in await get_available_models()
    ]
    candidates = await asyncio.gather(*tasks, return_exceptions=True)

    # Filter successful results, store all in candidates table
    valid = [c for c in candidates if isinstance(c, CompactionResult)]
    if not valid:
        raise RuntimeError(f"All models failed for entity {job.entity_id}")

    # Pick best: fewest observations that still pass semantic dedup check
    best = min(valid, key=lambda c: c.obs_count)
    best.was_selected = True

    await store_candidates(valid)

    # Complete: archive old observations, replace, rebuild compression layers
    log_id = await complete_compaction(
        entity_id=job.entity_id,
        new_observations=best.observations,
        model_used=best.model_used,
        worker_id=WORKER_ID,
        started_at=started_at,
    )

The completion function does the full commit atomically: snapshot old observations into the archive, replace with compacted, rebuild compression layers (handled by the existing trigger), write the audit log, and remove from the queue. Either the compaction commits completely or it does not commit at all.

Semantic dedup on insert

The other half of the bloat problem is observation duplication at write time. Two agents adding the same fact in different sessions will create two identical or near-identical observations. The semantic dedup layer addresses this using pgvector:

CREATE OR REPLACE FUNCTION kg_dedup_observation(
    p_entity_id INTEGER,
    p_embedding vector(384)
)
RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
    v_threshold REAL;
    v_max_sim   REAL;
BEGIN
    SELECT dedup_similarity INTO v_threshold
    FROM kg_compaction_config WHERE id = 1;

    SELECT MAX(1 - (embedding <=> p_embedding))
    INTO v_max_sim
    FROM kg_observation_embeddings
    WHERE entity_id = p_entity_id
      AND embedding IS NOT NULL;

    IF v_max_sim IS NULL THEN RETURN FALSE; END IF;
    RETURN v_max_sim >= v_threshold;  -- default threshold: 0.92
END;
$$;

Before an observation is written, the calling service computes a 384-dimension embedding and asks this function whether anything semantically equivalent already exists for this entity. Above 0.92 cosine similarity, the write is skipped. The embedding index uses HNSW — it can be built on an empty table, unlike IVFFlat, which requires a training set.

---

The bigger pattern: PostgreSQL as universal work coordinator

Building this, I kept noticing that the compaction queue is not a knowledge-graph-specific thing. The three-step pattern — detect threshold breach, enqueue work, claim with SKIP LOCKED, process, complete — is a general primitive for distributing compute across heterogeneous workers. It works for any task where you have more work than a single process can handle, and where work is naturally decomposable into independent units.

This is not a new insight. PostgreSQL has been used as a job queue for as long as people have wanted to avoid running RabbitMQ. What is interesting is the specific application: heterogeneous AI compute. When workers include a local inference server, a paid API, and a model hosted by a research institution, the queue does not care. Each worker advertises its capabilities, claims work, and reports back. The benchmark dataset accumulates. Over time the system learns where to route work.

---

The scientific computing connection

BOINC launched in 2002. Folding@home has been running since 2000. The citizen science computing model — donate your idle CPU cycles to a problem too large for any single institution — works. Millions of people have participated. Real science has happened. The model is empirically validated.

The problem is friction. Participating requires installing client software, trusting that the work units are legitimate, and accepting that your machine will be used for computation you cannot inspect. For organizations — a university with 500 research workstations, a startup with spare GPU capacity, a national lab with weekend availability — the friction is high enough that the capacity goes unused.

The compaction queue pattern removes most of that friction. A scientific work queue looks identical in structure:

CREATE TABLE simulation_work_queue (
    id          SERIAL PRIMARY KEY,
    problem_id  VARCHAR(64) NOT NULL,
    parameters  JSONB NOT NULL,
    priority    INTEGER NOT NULL DEFAULT 0,
    status      VARCHAR(20) NOT NULL DEFAULT 'pending',
    claimed_by  VARCHAR(128),
    claimed_at  TIMESTAMPTZ,
    result      JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_simulation_active
    ON simulation_work_queue(problem_id)
    WHERE status IN ('pending', 'claimed');

A worker on a university compute cluster claims a job with FOR UPDATE SKIP LOCKED, runs the computation, writes the result back, and marks the job complete. The same worker code runs on a researcher's workstation, on spare capacity, or on a cloud burst instance. The queue is the only shared state. Workers are stateless. Participation requires exactly one call to claim a job and one call to submit the result.

The fan-out pattern from compaction applies directly to numerical verification: run the same simulation on different hardware, compare results, detect numerical drift caused by differences in floating-point arithmetic between GPU architectures. The candidates table — storing every result from every worker — becomes a reproducibility audit trail.

The missing piece in existing citizen science platforms is not compute. It is trust and simplicity. A PostgreSQL queue with a well-defined API surface is auditable: anyone can inspect the work unit schema and verify that the computation is what was claimed. The benchmark view is not just useful for routing decisions — it is a public record of which hardware produced which results.

---

Current state and what comes next

The migration is applied. The trigger fires. Entities exceeding 12 observations are queued automatically. A backfill function populated the initial queue with everything already over threshold, ordered by observation count descending. The entity with 282,950 observations is at the top of the queue.

The workers are not yet running continuously. The next step is deploying a background service that polls the queue, fans out to available models, and completes jobs. The model router already handles local and API inference; wiring it to the queue is the remaining work.

What I expect to learn from the benchmark data:

• Which entity types benefit most from compaction — service entities with many configuration facts, or decision entities with complex reasoning chains
• Whether smaller local models perform adequately for simple factual compaction, which would reduce API costs substantially
• Whether the 0.92 cosine similarity threshold for semantic dedup is correctly calibrated, or whether it is blocking legitimate observations that happen to use similar language

The system will generate this data automatically as it processes the backlog. The benchmark view will tell me what I need to know without requiring me to design an experiment. That is the other advantage of the fan-out approach: the benchmark emerges from production usage rather than synthetic benchmarks designed in advance.

The entity with 282,950 observations is going to be interesting to compact.

---