# Sync And Maintenance

The base graph is immutable CSR once built. Sync captures source table changes
into durable log tables. In `mutable_overlay` mode, committed edge changes are
published as durable projection segments so other backends can observe them
after `graph.apply_sync()` without a full rebuild. When sync rows identify the
affected source nodes, pgGraph stores that narrower dirty range in the segment
metadata instead of treating the segment as whole-graph coverage. Vacuum and
maintenance still rebuild the base graph to fold accumulated changes back into
the core stores.

Run `graph.build()` or `graph.vacuum()` after committing changes made by the
calling transaction to graph registrations or registered source tables. Builds
lock the mapping catalog and every registered source, including current
partitions, in a stable order and hold the sync-writer fence through
publication. If the caller already owns a write-capable lock, or another writer
prevents the source boundary from being acquired immediately, the operation
fails with SQLSTATE `55P03` and diagnostic `PG006`; the last serving generation
is left unchanged.

## Modes

| Mode | Current behavior |
|---|---|
| `manual` | No automatic trigger install. Use `graph.build()` to refresh from source tables. |
| `trigger` | Default. `graph.build()` and `graph.enable_sync()` install triggers that write to `graph._sync_log`; topology reads auto-apply pending rows by default, and `graph.apply_sync()` or `graph.maintenance()` remain available for explicit operator workflows. |
| `wal` | Reserved and rejected by current sync code. |

```sql
ALTER SYSTEM SET graph.sync_mode = 'manual';
SELECT pg_reload_conf();
```

## Trigger Sync

```sql
SELECT graph.enable_sync();
```

With the default `graph.sync_mode = 'trigger'`, `graph.build()` creates trigger
functions and attaches INSERT, UPDATE, DELETE, and TRUNCATE triggers to
registered tables. `graph.enable_sync()` is still available when operators want
to install or reinstall those triggers explicitly. Trigger rows are durable in
`graph._sync_log`.

After upgrading from a pgGraph release whose sync triggers predate the durable
writer barrier, run `graph.enable_sync()` before resuming durable ingestion, or
run `graph.build()` with `graph.sync_mode = 'trigger'`. Durable
`graph.apply_sync()` and `graph.ingest_projection()` verify the installed
trigger definitions and fail with upgrade guidance until every registered
source table is protected. A trigger-mode build refreshes the definitions
before reading source rows, so transactions using an older definition finish
before the new base snapshot is taken.

For named graphs, the trigger log remains source-table oriented. A source table
change is written once, and `graph.apply_sync()`, query freshness, and
`graph.sync_health()` filter that log to the selected graph's registered source
tables. Changes for unrelated graphs do not advance or replay the selected
graph's backend-local sync state.

Mapped relationship INSERT, UPDATE, and DELETE replay uses the relationship
source table's registered primary key as canonical identity. Deleting one of
several relationship rows with equal endpoints and type therefore tombstones
only that row through immediate overlays, durable ingestion, reload, and
compaction.

```text
source table DML
      |
      v
AFTER trigger
      |
      v
graph._sync_log
      |
      +--> graph.apply_sync()
      |
      +--> graph.maintenance()
```

## Apply Sync

```sql
SELECT * FROM graph.apply_sync();
```

Return columns:

| Column | Meaning |
|---|---|
| `inserts_applied` | Insert sync rows applied |
| `updates_applied` | Update sync rows applied |
| `deletes_applied` | Delete sync rows applied |

Current apply behavior includes:

| Change | Behavior |
|---|---|
| Inserted node row | Materializes mmap node store if needed, appends node, updates resolution delta, filter values, and tenant membership |
| Updated node row | Handles primary-key changes, property/filter refresh, tenant refresh, and edge mutations where possible |
| Deleted node row | Tombstones node in owned store |
| Edge mutations in `mutable_overlay` | Published into durable projection segments and loaded through the layered read path |
| Registered filter changes in persisted `mutable_overlay` graphs | Published with their exact signed numeric, boolean, text, date, timestamp-with-time-zone, UUID, or SQL `NULL` value and restored on reload |
| Edge mutations outside durable projection coverage | Stored in the legacy edge overlay buffer until rebuild/vacuum |
| Truncate | Marks rebuild/vacuum need |

`graph.apply_sync()` drains the selected graph's durable sync rows that were
pending when the call started. Internally, replay is bounded by
`graph.sync_batch_size`, so large backlogs are processed in smaller batches
without changing the public admin contract.

<Callout type="warning">
The base CSR store is immutable. Durable projection segments make committed
mutable-overlay edge changes visible across backends after sync is applied, but
`graph.vacuum()` or `graph.maintenance()` is still the normal path for merging
accumulated projection state into a clean base graph. Dirty-range segment
metadata lets compaction and diagnostics reason about the affected source-node
span without rewriting existing mmap'd CSR bytes.
</Callout>

Projection segment format v6 carries stable relationship identity on topology,
deletion, and weight rows so parallel source relationships remain distinct
through compaction. Older segment versions are rejected across this format
boundary and require a fresh `graph.build()` before the persisted mutable graph
is served. The rebuild reads authoritative PostgreSQL source tables and does
not rewrite application rows or graph registrations.

Mutable projection manifests also reference a checksummed relationship-identity
dictionary for rows ingested after the base build. This keeps mapped
relationship primary keys stable across backend reloads and allows parallel
rows with the same endpoints and type to remain separately hydratable. A
manifest written before this dictionary reference was introduced is not served
as a current mutable generation; rebuild the graph from its PostgreSQL source
tables after upgrading.

Trigger mode installs sync triggers on registered node tables and on mapped
relationship source tables. Relationship tables do not need a separate
`graph.add_table()` registration: their declared primary key and mapped
endpoint columns are captured by the relationship trigger.

## Query Freshness

`graph.query_freshness` controls whether topology reads apply pending
trigger-sync rows before executing. The default is `apply_pending_sync`, so
topology reads catch up to a captured `_sync_log` high-water mark before reading
when `graph.sync_mode = 'trigger'`.

| Value | Behavior |
|---|---|
| `apply_pending_sync` | Topology reads apply rows up to a captured `_sync_log` high-water mark before reading. |
| `off` | Compatibility mode; reads do not apply pending sync rows automatically. |
| `error_on_pending` | Topology reads fail when pending sync rows exist. |

The topology reads covered by query freshness are `graph.traverse()` variants,
shortest-path queries, weighted shortest-path queries, component APIs, and
`graph.traverse_search()` before it traverses from search matches. Primitive
`graph.search()` remains source-table SQL search and does not use the topology
freshness helper. `graph.apply_sync()` remains the explicit operator/admin path
for all pending sync rows.

## Sync Health

```sql
SELECT *
FROM graph.sync_health();
```

`graph.sync_health()` returns one row of scheduler-friendly signals:

| Column | Meaning |
|---|---|
| `query_freshness` | Current topology-read freshness mode |
| `sync_batch_size` | Maximum durable sync rows replayed per internal batch |
| `applied_sync_id` | Last durable sync log row applied by this backend-local engine |
| `max_sync_log_id` | Current high-water row in `graph._sync_log` for the selected graph's registered tables |
| `pending_sync_rows` | Durable sync rows for the selected graph newer than `applied_sync_id` |
| `disabled_trigger_count` | Registered graph triggers currently disabled |
| `edge_buffer_used` | Pending edge overlay mutations |
| `edge_buffer_size` | Configured edge overlay capacity |
| `projection_mode` | Built projection mode: `csr_readonly` or `mutable_overlay` |
| `overlay_tombstone_count` | Pending overlay delete/tombstone count |
| `overlay_memory_bytes` | Estimated backend-local overlay heap memory |
| `compaction_recommended` | `true` when durable overlay count, tombstones, memory, or vacuum state crosses the configured compaction policy |
| `tx_delta_dirty` | Whether this backend has transaction-local graph deltas |
| `tx_delta_added_nodes` / `tx_delta_deleted_nodes` | Transaction-local node delta counts |
| `tx_delta_added_edges` / `tx_delta_deleted_edges` | Transaction-local edge delta counts |
| `tx_delta_memory_bytes` | Estimated transaction-delta heap memory |
| `read_only_reason` | Typed serving-state reason such as `edge_buffer_full` when read-only |
| `apply_sync_recommended` | Pending rows can be replayed by `graph.apply_sync()` while the engine is writable |
| `maintenance_recommended` | Overlay, read-only, vacuum, or rebuild state should be compacted |
| `durable_ingest_recommended` | Durable projection rows are waiting to be ingested |
| `durable_compaction_recommended` | Durable segment fanout exceeds the configured compaction threshold |
| `durable_gc_recommended` | Manifest-declared obsolete projection files can be evaluated by GC |
| `durable_repair_recommended` | Active projection artifacts need targeted repair or full rebuild |
| `sync_log_retention_floor` | Computed safe floor to prune `graph._sync_log` below, or `NULL` when no active backend heartbeat or durable projection watermark makes a floor safe yet |
| `sync_log_prune_recommended` | `true` when a floor exists and `graph._sync_log` has grown far enough past it that pruning would meaningfully shrink it |
| `active_sync_watermark_backends` | Count of currently active (unexpired) sync-watermark heartbeats for this graph; a lagging or crashed-without-cleanup backend holding an unexpired heartbeat is the usual reason pruning is blocked |

## Sync-Log Retention

`graph._sync_log` durable rows accumulate as trigger-captured changes are
written. `graph.maintenance()` and `graph.run_scheduled_maintenance()` prune
applied rows below a safe floor as part of the existing rebuild; nothing
prunes the log outside of maintenance, and `graph.apply_sync()` never deletes
rows.

Backend-local `applied_sync_id` (the replay progress `csr_readonly` mode
tracks) is otherwise invisible to other backends, so pruning cannot safely
use just the calling backend's own progress: a slower or idle backend that
has not replayed a row yet would silently diverge from PostgreSQL if that row
were deleted. Each backend registers a durable heartbeat of its own
`applied_sync_id` in `graph._sync_watermarks` (a five-minute TTL, refreshed
whenever `graph.status()` or `graph.sync_health()` runs) whenever the graph
uses trigger sync. The safe floor for a maintenance pass is the minimum of:

- the durable projection's own sync watermark, for a persisted
  `mutable_overlay` graph;
- the minimum `applied_sync_id` across all currently active (unexpired)
  backend heartbeats for the graph.

If neither is available — for example, a freshly built graph nobody has
queried since, or every backend that ever touched it has gone idle past its
heartbeat TTL — no floor is considered safe and pruning is skipped for that
pass. The log grows a little longer on an idle graph rather than the
alternative of guessing a floor is safe.

<Callout type="info">
A backend that goes long-lived and idle without calling `graph.status()`,
`graph.sync_health()`, or applying sync will eventually have its heartbeat
expire, after which pruning can proceed past its last-known watermark.
`graph._graphs` tracks the highest floor ever pruned to
(`sync_log_pruned_before_id`); if that backend later resumes and its own
`applied_sync_id` predates that watermark, `graph.apply_sync()` and
query-freshness catch-up both fail with SQLSTATE `55000` and diagnostic
`PG022` instead of silently skipping the rows that are now gone. Run
`graph.build()` or `graph.vacuum()` to get a fresh consistent base, then
resume normal sync.
</Callout>

## Scheduled Maintenance

Use `graph.run_scheduled_maintenance()` from `pg_cron` or an external scheduler
when you want one call that applies sync if recommended and then starts
background maintenance when compaction or rebuild work remains:

```sql
SELECT * FROM graph.run_scheduled_maintenance();
```

For PostgreSQL installations that use `pg_cron`:

```sql
CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule(
  'pggraph-maintenance',
  '*/1 * * * *',
  $$SELECT * FROM graph.run_scheduled_maintenance();$$
);
```

Use a slower cadence when write volume is low:

```sql
SELECT cron.schedule(
  'pggraph-maintenance',
  '*/5 * * * *',
  $$SELECT * FROM graph.run_scheduled_maintenance();$$
);
```

The extension does not run an always-on internal scheduler or background loop.
Operators own the maintenance cadence and lifecycle through `pg_cron`,
Kubernetes CronJobs, systemd timers, Docker initialization SQL, or an
application-side scheduler. Keep the scheduled statement to the single
admin-only call above so pgGraph can continue to apply sync and start
maintenance using its built-in health checks.

## Sync Policies And Durable Jobs

Sync policies store graph-scoped replay intent in PostgreSQL catalogs so hosted
schedulers can run sync consistently across backends. They do not start an
always-on scheduler inside PostgreSQL. Use `pg_cron`, an application scheduler,
or another host-managed worker to call `graph.run_due_jobs()` at the cadence
recorded on the policy.

Create a policy for a named graph:

```sql
SELECT *
FROM graph.add_sync_policy(
  'customer_360',
  schedule_interval_secs := 60,
  max_sync_lag_rows := 10000,
  graph_namespace := 'analytics'
);
```

Run the policy immediately:

```sql
SELECT * FROM graph.run_sync_policy('<policy-id>');
```

Or run it through the generic job surface:

```sql
SELECT * FROM graph.run_job('<job-id>');
```

Hosted schedulers can run all due jobs in one bounded call:

```sql
SELECT * FROM graph.run_due_jobs(max_jobs := 64);
```

Installations that allow pgGraph dynamic background workers can launch one
internal pass instead:

```sql
SELECT graph.run_due_jobs_async(max_jobs := 64);
```

Policy execution restores the selected graph context, uses the same replay path
as `graph.apply_sync()`, and records the result in `graph._job_runs`. Disabled
jobs produce a `disabled` run row and do not apply sync. Concurrent runners use
row locking plus a graph/job advisory lock; a contended job records
`lock_skipped` instead of replaying the same work twice.

`graph.run_due_jobs_async()` is a one-shot worker registration. It uses the same
durable job catalogs and records `execution_mode = 'internal'`, but it does not
install an always-on scheduler loop. Use `pg_cron`, a provider scheduler, or an
application scheduler to choose the cadence.

Inspect policy and job state:

```sql
SELECT * FROM graph.sync_policy_status('customer_360', graph_namespace := 'analytics');
SELECT * FROM graph.jobs('customer_360', graph_namespace := 'analytics');
SELECT * FROM graph.job_runs(graph_name := 'customer_360', graph_namespace := 'analytics');
SELECT * FROM graph.job_stats('customer_360', graph_namespace := 'analytics');
```

Update or remove scheduler records:

```sql
SELECT * FROM graph.alter_job('<job-id>', enabled := false);
SELECT * FROM graph.alter_sync_policy('<policy-id>', schedule_interval_secs := 300);
SELECT * FROM graph.remove_job('<job-id>');
-- or
SELECT * FROM graph.drop_sync_policy('<policy-id>');
```

Policy and job listing functions only return graphs visible to the current
role. Running, altering, or removing a policy/job requires graph administration
for the target graph. Hard `max_graph_jobs` quotas count generic jobs, build
jobs, and maintenance jobs before a new sync policy job is created.

Durable job status values are `queued`, `running`, `completed`, `failed`,
`disabled`, `retryable_failed`, `permanent_failed`, `quota_blocked`, and
`lock_skipped`. Existing build and maintenance job APIs continue to use their
legacy queued/running/completed/failed subset.

## Edge Buffer Limits

`graph.edge_buffer_size` limits legacy/backend-local pending edge mutations.
For persisted `mutable_overlay` graphs, committed edge changes normally use
durable projection segments instead of this buffer. When the legacy buffer is
full, the engine enters read-only mode, reports SQLSTATE `54000` with diagnostic
`PG008` at the capacity breach,
and exposes `read_only_reason = 'edge_buffer_full'`.

```sql
ALTER SYSTEM SET graph.edge_buffer_size = 500000;
SELECT pg_reload_conf();
```

Recovery:

```sql
SELECT * FROM graph.vacuum();
-- or
SELECT * FROM graph.maintenance();
```

## Vacuum

```sql
SELECT * FROM graph.vacuum();
```

Return columns:

| Column | Meaning |
|---|---|
| `nodes_before` | Node slots before rebuild |
| `nodes_after` | Node slots after rebuild |
| `tombstones_removed` | Removed inactive node slots |
| `edges_rebuilt` | Directed edges in rebuilt CSR |
| `vacuum_time_ms` | Wall-clock rebuild time |

The current implementation performs a full rebuild from source tables. This is
correct for immutable CSR and reclaims tombstones and overlay state.

<Callout type="warning">
Vacuum has a double-memory period because the old and new engines coexist until
the replacement build completes. Persisted rebuilds release the owned
replacement before mmap validation/reload, but `graph.memory_limit_mb` still
needs enough headroom for the old engine plus the peak replacement build.
Set `graph.low_memory_build = on` for maintenance windows where the building
backend may unload its current graph before constructing the replacement.
</Callout>

## Maintenance

Foreground maintenance:

```sql
SELECT * FROM graph.maintenance(concurrently := false);
```

Background maintenance:

```sql
SELECT * FROM graph.maintenance(concurrently := true);
SELECT * FROM graph.maintenance_status('<job_id>');
```

If background maintenance fails after the worker starts, pgGraph records the
failed status and error detail from a fresh worker transaction so
`graph.maintenance_status('<job_id>')` remains the operator source of truth.

Return columns:

| Column | Meaning |
|---|---|
| `job_id` | Durable maintenance job ID, or zero UUID for foreground path |
| `status` | `queued`, `running`, `completed`, or `failed` |
| `sync_rows_applied` | Sync rows folded into the rebuild |
| `nodes_after` | Node count after maintenance |
| `edges_after` | Edge count after maintenance |
| `vacuum_time_ms` | Rebuild duration |
| `progress_phase` | Coarse operator phase such as `queued`, `rebuilding`, `persisting`, `validating_persistence`, `completed`, or `failed` |
| `progress_message` | Short operator-readable progress or failure detail |
| `error` | Failure text if any |

`graph.maintenance_status(NULL)` returns the latest 50 maintenance jobs.

## Status Columns For Sync

```sql
SELECT
  sync_mode,
  sync_status,
  edge_buffer_used,
  needs_vacuum,
  needs_rebuild,
  projection_mode,
  tx_delta_dirty,
  applied_sync_id,
  pending_sync_rows,
  disabled_trigger_count,
  invalid_reason
FROM graph.status();
```

| Column | Meaning |
|---|---|
| `sync_status` | `idle`, `syncing`, or `read_only` |
| `edge_buffer_used` | Pending edge overlay mutations |
| `needs_vacuum` | Overlay/tombstone state should be merged |
| `needs_rebuild` | Catalog/schema drift requires full rebuild |
| `projection_mode` | Built projection mode |
| `tx_delta_dirty` | Whether this backend has transaction-local graph deltas |
| `applied_sync_id` | Last durable sync log row applied by this backend-local engine |
| `pending_sync_rows` | Durable sync rows newer than `applied_sync_id` |
| `disabled_trigger_count` | Registered graph triggers currently disabled |

Use `graph.active_generation_count()` to inspect unexpired backend heartbeats
that retain durable projection generations for generation-aware cleanup.
Use `graph.projection_status()` for the full durable projection diagnostic row:

```sql
SELECT *
FROM graph.projection_status();
```

The row reports the active manifest generation and watermark, pending durable
rows, active base/manifest/artifact bytes, segment and chunk counts/bytes,
segment fanout, durable read amplification, tombstone ratio, compaction backlog,
obsolete file counts/bytes, active generation count, artifact validation state,
persisted last-ingestion/compaction/GC/repair timestamps, and
ingestion/compaction/GC/repair recommendations. `graph.sync_health()` keeps
only the lightweight durable recommendation booleans; use
`graph.projection_status()` for full artifact validation.

Run `graph.projection_compact()` to reduce durable segment fanout through a new
copy-on-write manifest generation:

```sql
SELECT *
FROM graph.projection_compact(
  max_rows := 10000,
  max_segments := 1000,
  dirty_chunk_segment_threshold := 8
);
```

When the dirty-chunk threshold is reached, compaction writes replacement chunk
files for the affected source-node ranges and publishes a manifest that points
at those chunks. Existing mmap'd base CSR bytes are not overwritten, and older
generations remain available until retention and active-generation heartbeat
rules allow cleanup.

Run `graph.projection_gc()` to delete obsolete projection files after the
configured generation retention floor and active-generation heartbeats no
longer protect them. The same pass bounds crash leftovers by removing abandoned
generation manifests, their unshared artifacts, and abandoned manifest or
pointer temp files while holding the exclusive generation lock:

```sql
SELECT *
FROM graph.projection_gc();
```

`protected_candidates` counts artifact candidates that were still referenced
by retained current-generation ancestry or an active backend generation.
`deleted_files` and `deleted_bytes` include obsolete artifacts, abandoned
manifests and unique artifacts, and abandoned temp files removed during that
call.

Run `graph.projection_repair()` when load or maintenance diagnostics indicate
corrupt durable projection metadata:

```sql
SELECT *
FROM graph.projection_repair();
```

The result reports `healthy`, `targeted_chunk_repair`, `full_rebuild`, or
`no_projection`. Full rebuilds use PostgreSQL source tables, persist a fresh
`.pggraph` artifact, and publish a new base-only projection generation before
the backend reloads it.

## Operational Patterns

Small manual graph:

```sql
ALTER SYSTEM SET graph.sync_mode = 'manual';
SELECT pg_reload_conf();
SELECT * FROM graph.build();
-- periodically:
SELECT * FROM graph.build();
```

Default trigger-sync graph:

```sql
SELECT * FROM graph.build();

-- after workload:
SELECT * FROM graph.apply_sync();
SELECT * FROM graph.maintenance();
```

High-write graph:

```text
Use trigger sync for visibility.
Run graph.apply_sync() when graph.sync_health().apply_sync_recommended is true.
Run graph.maintenance(concurrently := true) when maintenance_recommended is true.
Watch edge_buffer_used, pending_sync_rows, disabled_trigger_count, read_only, and read_only_reason.
```