`pg push --watch` can read the full PostgreSQL vector state every 30 seconds and consume 100+ GB of network traffic

[mudssky]

agentsview version

agentsview v0.38.1

The same relevant behavior is still present on the current main branch as of 2026-07-20.

Install method

Other / not sure

OS / platform

Windows 11 host with WSL2 Linux; PostgreSQL is on another machine reached over Tailscale.

Which agent and version

n/a — this affects PostgreSQL watch synchronization rather than one parser.

Which model(s)

n/a

What happened, and what did you expect

agentsview pg push --watch --interval 5m generated more than 100 GB of inbound Tailscale traffic in about
63 hours, even though each push normally changed only a small number of sessions.

Observed evidence:

• Windows Tailscale adapter cumulative receive: 116.58 GB
• WSL eth0 cumulative receive: approximately 106.37 GB
• pg-watch.log: 6,034 pushes in 62.66 hours
• Effective push cadence: one push every 37.4 seconds on average, despite --interval 5m
• One measured push received approximately 17.1 MB from the remote PostgreSQL host
• 6,034 x 17.1 MB is approximately 103 GB, which accounts for nearly all WSL/Tailscale receive traffic

There was also a separate high-traffic path in the same deployment: a central machine periodically used SSH remote
sync to pull sessions from another Linux host. The retained sync log contains 317 completed full remote downloads.
At an observed archive size of approximately 2.5 GB per run, that path accounts for an estimated 792 GB.

The retained evidence therefore accounts for approximately 895 GB across the two AgentsView paths:

• approximately 103 GB from high-frequency PostgreSQL watch pushes
• approximately 792 GB from repeated full SSH remote-sync downloads

The operator's wider network statistics, which cover additional activity outside the retained sync log, exceeded
1 TB in total AgentsView-related traffic. The SSH figure is included as deployment-wide impact, not as evidence
that the PostgreSQL vector query itself transferred 1 TB: SSH remote sync and PostgreSQL watch push are separate
transport paths with separate read-amplification causes.

The writes are incremental, but the read side is not bounded by the number of changed sessions. An active Agent
session keeps changing its source file, so the default 30-second debounce repeatedly starts a new push. Each push
then performs PostgreSQL comparison/reconciliation reads even when only one session changed.

I expected a watch push caused by one changed session to transfer data proportional to that change. A periodic full
reconciliation is reasonable, but it should not run after every file-change debounce.

Source inspection

There appear to be two multiplicative causes:

1. cmd/agentsview/pg_watch_loop.go implements debounce as a fixed window. The first dirty notification arms a
   timer, additional changes are coalesced only until that timer fires, and a later change starts another window.
   With a continuously active session, this permits one push approximately every 30 seconds. --interval is a
   periodic floor ticker, not a minimum interval between pushes.

2. The vector phase in internal/postgres/vector_push.go runs on every push. It loads all local session document
   hashes with SessionDocHashes, then readVectorPushState selects every vector_push_state row for the active
   generation and joins the corresponding session owner metadata:

   SELECT ps.session_id, ps.doc_agg_hash, s.owner_marker, s.machine,
          (s.id IS NOT NULL)
     FROM vector_push_state ps
     LEFT JOIN sessions s ON s.id = ps.session_id
    WHERE ps.generation_id = $1

   This makes the remote read cost grow with the full generation size rather than with the changed session set.

The exact share of the measured 17.1 MB attributable to the vector phase has not yet been isolated with a
--no-vectors comparison, so the traffic measurement and source-level vector-state finding should be treated as
separate evidence rather than assuming the entire transfer comes from one query.

The SSH remote-sync amplification is also separate: v0.38.1 downloads and extracts the configured remote roots
before importer fingerprinting skips unchanged sessions. Its unchanged count therefore reflects database import
deduplication, not network-level incremental transfer.

Steps to reproduce

1. Configure a remote PostgreSQL target with vector pushing enabled and several thousand existing sessions.

2. Run:

   agentsview pg push --watch --interval 5m

3. Keep one Agent session active so its source file changes continuously.

4. Count pg watch: pushed ... (change) entries and monitor received bytes from the PostgreSQL peer.

5. Observe change-triggered pushes approximately every 30-40 seconds and a substantial fixed receive cost per push,
   even when only one session changes and no vector chunks need updating.

Related work

• pg push: slow for large databases due to per-session PG round-trips #331 fixed per-candidate PostgreSQL comparison round trips by batching reads. It does not cover the later
  generation-wide readVectorPushState query or watch trigger frequency.
• feat(postgres): pgvector-backed semantic and hybrid search #1060 introduced PostgreSQL vector replication and vector_push_state.

Suggested direction

• Add a true minimum push cooldown, or make change notifications use a sliding debounce, so continuous file writes
  cannot trigger a remote push every 30 seconds.
• Keep a fast incremental vector path that reads state only for changed/candidate session IDs.
• Run generation-wide deletion/eviction reconciliation on startup, the periodic floor, --full, or a separate
  lower-frequency maintenance pass instead of every change-triggered push.
• Log the push reason, candidate count, PostgreSQL state rows read, and vector rows/chunks written so operators can
  distinguish local scanning from network transfer.

Current workaround

Reduce trigger frequency:

agentsview pg push --watch \
  --debounce 15m \
  --interval 30m

If that machine does not need to publish semantic-search vectors, --no-vectors or push_vectors = false avoids the
vector phase, but that changes search coverage and is not a general fix.

Checklist

• I searched existing issues.
• I removed secrets and private data from the report.