Overview

What would database monitoring look like if it remembered the past, explained unusual behavior and brought relevant operational knowledge to each investigation?

Static thresholds remain valuable, but they rarely tell the whole story. A familiar daily peak can look alarming, while a gradual change can pass unnoticed. Historical context offers another perspective: not just whether a number is high, but whether it is unusual for this workload.

This article explores a local monitoring design that combines deterministic checks, persistent history, a small numeric predictor and runbook retrieval. Bash and AWK provide one lightweight implementation path. Database clients, operating system utilities and any optional storage extensions still need to be installed and managed.

Article scope: Document records, incident identifiers and numerical examples are synthetic. Schedules, retention periods and thresholds require workload-specific validation.

The Constraint We Designed For

In restricted production environments, access to external language models may be unavailable, and sensitive database information must remain within the approved infrastructure.

This constraint does not rule out useful learning. A small numeric model can run locally, while a retrieval layer can find relevant runbooks without generating an explanation. These are separate capabilities: one estimates risk from measurements, and the other supplies documented context for a person to review.

The broader lesson is that model size is only one part of reliability. Persistent evidence, meaningful labels and careful evaluation can matter just as much. Whether a smaller local approach is sufficient depends on the failure scenarios it must handle.

Architecture

A primary and standby with a connection pool provide one useful example. Other topologies can use the same principles: explicit monitoring ownership, persistent observations and clear boundaries between advice and action.

The useful pattern is a division of responsibilities: periodic health checks build a broad picture, more frequent observations catch urgent changes, and incident review explains how events unfolded. Collection intervals should follow workload sensitivity and monitoring cost rather than a fixed schedule copied from another deployment.

Data Flow in Three Dimensions

Each run moves data upward through five layers. Drag the model to rotate it.

Raw measurements enter at the bottom. They are smoothed into baselines and written into history, which is where memory lives. The learning layer turns that history into labels, thresholds and weights. The reasoning layer combines all of it into a judgement about the next hour, and only the top layer ever reaches a human.

The Monitoring Cycle

A useful sequence is to collect evidence, compare it with history, update memory and report findings. The diagram uses a twenty minute interval as a worked example, not a required schedule.

Two details in this cycle matter more than they look. First, the baseline is copied before any check runs, so anomaly detection compares this run against history rather than against a baseline that already contains this run. Without that snapshot every spike would partly hide itself. Second, every stage after the checks is wrapped so that a failure in learning or prediction can never stop the monitoring itself. If the intelligence layer breaks, the agent falls back to plain threshold alerting and keeps paging.

What We Check

A monitoring plan should cover the host, database, middleware and topology. These areas provide a starting point rather than a completeness guarantee. Select checks according to the workload and its failure modes.

How the Learning Works

Six stages connect observation, memory and learning. The animation shows the conceptual flow, not the timing of an actual monitoring run.

Stage one and two: observing and smoothing

An exponential moving average lets recent readings influence the baseline without replacing all of its history. With a smoothing factor of 0.2, the current reading contributes one fifth of the next baseline. A spike still moves that baseline, which is why detection should compare against a snapshot taken before the update. The smoothing factor and collection interval together determine how quickly the baseline adapts.

A metric is flagged as an anomaly when its current value reaches 150 percent of the baseline captured before the run. When the processor, input and output latency, connection usage and replication lag are anomalous together, the agent raises a composite alert, because that combination is the classic signature of a cascade that ends in failover.

Stage three: labelling itself

Learning needs labels, and nobody has time to label twenty minute rows by hand. So the agent labels its own history. Every run appends a feature row with the health score, the warning and critical counts, and the smoothed processor, connection, replication lag and disk values. The outcome column is left empty. Three runs later, about an hour on, the agent fills that empty outcome with whatever the cluster status turned out to be. Each row therefore answers the question that matters: given what things looked like, what happened in the next hour.

Stage four: adaptive thresholds

Once a day of history exists, which is seventy two rows, the agent computes a threshold for each metric from the last seventy two runs.

The safety rule is deliberate. Adaptive thresholds can only tighten an alert line, never loosen it past what an engineer configured. If the cluster is normally quiet, the agent notices trouble earlier. If the cluster is normally busy, the configured limit still stands.

Stage five: prediction

A small predictor can combine recent and historical pressure into a risk score. The example below uses a warning boundary of seventy out of one hundred. Such a score is not automatically a calibrated probability, and a forecast horizon is not a guaranteed warning time. Both need evaluation against independently observed incidents.

Stage six: weekly recalibration

Recalibration can run periodically once enough labelled outcomes exist. In this example, healthy, warning and critical outcomes map to targets of 0, 0.5 and 1. The learner measures prediction error and takes a small gradient step on each weight. The appropriate cadence depends on how quickly the workload changes and how much trustworthy evidence is available.

Weights are then clipped between 0.05 and 0.70 so no single metric can take over or disappear, and normalised so they add up to one. The learning rate is small on purpose. We would rather the model drift slowly toward the truth than swing on one bad week. The mean squared error before the update is recorded with the new weights, so every recalibration leaves an audit trail.

The Attention Predictor

A shell native encoder and decoder that looks at four windows of time at once and decides how much each one should count.

Encoder

Each head averages the processor, connection, replication lag and disk values across its window. The four averages are blended using the attention weights, so recent behaviour counts most but the daily and weekly patterns still pull on the result.

The encoded metric is one blended measurement, such as processor usage. The symbol Σ means add the contributions from all time windows. A head is simply one window of history. Its weight controls how much that window contributes, while its average is the mean of the measurements collected within that window. These temporal weights are distinct from the metric weights used in the next formula.

For example, processor averages of 80, 60, 40 and 20 percent across the four windows, with weights of 0.4, 0.3, 0.2 and 0.1, give 0.4 × 80 + 0.3 × 60 + 0.2 × 40 + 0.1 × 20 = 60 percent. The result remains in the original metric units.

Decoder

Each encoded metric is normalised against its ceiling, with 500 megabytes as the ceiling for replication lag and 100 percent for the others, and then projected into a single risk score using the learned weights. The starting weights are 0.35 for processor, 0.30 for connections, 0.20 for replication lag and 0.15 for disk, and the weekly recalibration moves them from there.

Here, w is the learned importance of a metric, x is its encoded measurement divided by a reference scale, and the dot means multiplication. The small labels identify processor utilisation, connection utilisation, replication lag and disk utilisation. Adding the four contributions produces a dimensionless risk score.

For example, normalised measurements of 0.60, 0.50, 0.20 and 0.70 with weights of 0.35, 0.30, 0.20 and 0.15 produce a risk score of 0.505. Multiplying by 100 gives 50.5 on a display scale. It does not establish a 50.5 percent chance of failure. A measurement above its reference scale can exceed one, so clipping and interpretation must be explicit.

The risk score is split into three probabilities. The critical probability rises directly with risk. Between a risk of 0.3 and 0.7 there is a transition zone where the degraded probability peaks and then fades, so the three classes each own a clear region with a shared middle ground for ambiguous readings. The result is written to state after every run and reused by the morning briefing, which labels the day ahead as low, moderate, elevated or high risk.

How the Reasoning Works

Every candidate alert passes through a chain of judgements before it reaches a person.

The noise gate

A warning is held back only when every one of these is true at once: the gate is switched on, there is at least a day of history, the predicted critical probability is below forty percent, and the current value sits inside its own historical ninetieth percentile. If any condition fails, or if anything inside the gate errors, the warning is sent. The gate fails open by design, and it first ran in observe only mode so we could see what it would have suppressed before trusting it.

Keys that are never suppressed

Split brain, virtual address mismatch, stopped replication, inactive replication slots, missing monitor heartbeats, broken idle connections, transaction identifier wraparound, sequence exhaustion, out of memory kills and database log errors can never be held back, whatever the model thinks. Critical alerts never enter the gate at all.

Reasoning after a failover

When the primary changes, the root cause engine reads the full day of logs, keeps only the lines from that date, and rebuilds a chronological timeline of what the agent saw against what actually happened. It matches the log patterns against sixty three known signatures, attaches the related informations, and writes recommendations. That timeline then becomes part of memory, so incidents turn into learning rather than just alerts.

Cluster Wide Memory

Memory should survive a process restart or a change of primary. Shared storage can help, but its availability and completeness still need checking.

One approach is to write observations locally and copy them into a PostgreSQL history table. A newly active monitor can rebuild local history from the replicated data when it is available. Recovery depends on replication progress, retained rows and database connectivity. Local storage also needs capacity checks and a clear policy for retrying failed writes.

TimescaleDB is an optional choice for time series management, while pgvector can support knowledge retrieval. Neither is required for basic persistent history. Seeing effects across services requires aligned observations and relevant service events; putting records in the same database does not by itself establish causality.

Reasoning from Operational Evidence

The reasoning layer brings together previous incident reviews, log evidence, monitoring history and runbooks. It uses current symptoms to find relevant operational context and presents that evidence with its source.

Two paths work together. Deterministic mappings connect known alert keys and log signatures to investigation guidance. Vector retrieval searches approved incident and runbook passages for similar symptoms, including context not covered by a direct mapping. Neither a rule match nor a vector score proves the root cause.

The deterministic path remains available when the vector database is unavailable or no suitable passage is found. Retrieval adds context; it does not delay the original alert or authorize a remediation command.

Documents to pgvector

The indexing application reads approved incident reviews, sanitized log excerpts and runbooks. It separates documents into passages, records their source and revision, and converts the text into numeric vectors. PostgreSQL stores the readable text and metadata alongside each vector. The pgvector extension supplies the vector type and distance operators; the application owns tokenization, weighting and document lifecycle.

Tokens and the Symptom Query

Tokenization happens in the indexing application, not inside pgvector. pgvector stores numeric vectors and supplies distance operators; it does not read or tokenize documents.

First remove secrets and identify the document, revision, product version and section. Split at headings while retaining enough surrounding context to understand each passage. Preserve the original passage text separately from the token list: the engineer must receive readable evidence, not a vector.

How text becomes tokens

This example uses smoothed TF-IDF. Freeze a vocabulary that assigns one coordinate to each retained token, calculate IDF from the indexed corpus, and save both with a representation version. Divide each nonzero vector by its Euclidean length. Documents and queries must use the same coordinate order, IDF values and tokenizer version. Skip zero vectors; they have no usable cosine direction.

A small, reproducible example

Two short passages keep every coordinate visible: passage A comes from an incident review, and passage B from a runbook. Their identifiers and content are demonstration data, not production records.

Incident passage A: "Replication lag on standby"
Runbook passage B:  "Connection limit reached"
After lowercase and removal of "on":
  A tokens: replication, lag, standby
  B tokens: connection, limit, reached

Frozen coordinate order:
  [replication, lag, standby, connection, limit, reached]
N = 2 passages; each term occurs in 1 passage
IDF for each term = 1 + ln(3 / 2)

Normalized A = [0.577350, 0.577350, 0.577350, 0, 0, 0]
Normalized B = [0, 0, 0, 0.577350, 0.577350, 0.577350]
Query "replication lag":
  Q = [0.707107, 0.707107, 0, 0, 0, 0]
Cosine similarity(Q, A) = approximately 0.816497
Cosine similarity(Q, B) = 0

The six coordinates are only for this worked example. A real vocabulary is much larger; select a supported vector type and dimension after measuring it. Out-of-vocabulary query terms do not receive invented coordinates. A changed vocabulary or IDF snapshot requires a new representation and consistent reindexing.

How an alert becomes a query

The alert-enrichment path combines the alert key, metric description, relevant sanitized log excerpt and severity into symptom text. It applies the saved tokenizer, vocabulary and IDF snapshot. Incident analysis uses the same path with the relevant incident evidence.

alert key         repl_lag_mb
metric            replication lag in megabytes on the standby
log signature     could not receive data from WAL stream
severity          warning

query tokens      repl_lag_mb  replication  lag  megabytes  standby  receive  data  wal  stream  warning

These are candidate tokens before vocabulary lookup. Technical names can improve exact matching, but synonyms, ambiguous terms and application-specific wording can still cause misses. Unknown terms or a zero query vector should produce no vector result; existing deterministic runbook selection should continue.

What PostgreSQL stores

The database stores one row per passage, not one opaque vector for an entire incident. The application supplies already-normalized coordinates. Each row keeps the readable passage, its source, revision and representation version alongside the numbers. This compact example uses six dimensions so the complete stored values fit on the page.

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE knowledge_passage_example (
    passage_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    source_id TEXT NOT NULL,
    revision TEXT NOT NULL,
    section TEXT NOT NULL,
    content TEXT NOT NULL,
    representation_version TEXT NOT NULL,
    verified_at TIMESTAMPTZ,
    active BOOLEAN NOT NULL DEFAULT true,
    embedding vector(6) NOT NULL
);

INSERT INTO knowledge_passage_example
    (source_id, revision, section, content, representation_version, embedding)
VALUES
    ('incident-demo-001', 'r1', 'Symptoms', 'Replication lag on standby',
     'tfidf-demo-v1', '[0.577350,0.577350,0.577350,0,0,0]'),
    ('runbook-demo-connections', 'r1', 'Symptoms', 'Connection limit reached',
     'tfidf-demo-v1', '[0,0,0,0.577350,0.577350,0.577350]');

Inspecting the stored rows shows that vectorization does not replace the original evidence:

SELECT passage_id, source_id, content, embedding
FROM knowledge_passage_example
ORDER BY passage_id;

passage_id: 1
source_id:  incident-demo-001
content:    Replication lag on standby
embedding:  [0.57735,0.57735,0.57735,0,0,0]

passage_id: 2
source_id:  runbook-demo-connections
content:    Connection limit reached
embedding:  [0,0,0,0.57735,0.57735,0.57735]

These identifiers assume a newly created example table. The vector(6) column stores six single-precision numbers in PostgreSQL's vector representation. The bracketed text is its SQL input/output form, not a separate JSON document. Source metadata and passage text remain ordinary relational columns. The vector value uses 32 bytes for six dimensions under pgvector's 4 × dimensions + 8 storage formula, excluding row, text, metadata and index overhead.

Retrieval during an alert or incident

The application binds the query vector as $1 and its representation version as $2. The <=> operator is cosine distance: smaller is closer. This exact-search example needs no approximate index. A production integration must also restrict results to authorized sources and compatible component versions.

SELECT passage_id, source_id, revision, section, content, verified_at,
       1 - (embedding <=> $1::vector(6)) AS similarity
FROM knowledge_passage_example
WHERE active AND representation_version = $2
ORDER BY embedding <=> $1::vector(6)
LIMIT 3;

Example one: replication-lag alert

The symptom replication lag becomes [0.707107,0.707107,0,0,0,0]. With the matching representation version, the query returns these approximate scores:

The enrichment layer rejects the unrelated second result. Using 0.60 as a demonstration minimum similarity, it attaches the first passage as evidence:

{
  "event_id": "demo-alert-001",
  "alert_key": "repl_lag_mb",
  "query_text": "replication lag",
  "representation_version": "tfidf-demo-v1",
  "matches": [{
    "passage_id": 1,
    "source_id": "incident-demo-001",
    "revision": "r1",
    "section": "Symptoms",
    "content": "Replication lag on standby",
    "similarity": 0.8165,
    "verified_at": null
  }]
}

The unknown verification date stays visible. This tiny symptom passage is context, not a complete remediation runbook or a confirmed diagnosis. Real passages retain enough investigation detail to support an engineer's next decision.

Example two: connection-limit incident

The symptom connection limit becomes [0,0,0,0.707107,0.707107,0]. The same query ranks the connection runbook at similarity 0.8165 and the replication incident at 0.0000. The stored rows do not change; the event's vector changes which passage is retrieved.

LIMIT 3 is not a relevance guarantee. The enrichment layer applies a validated minimum score and permits an empty result. For example, a query containing only checkpoint has no coordinates in this toy vocabulary, so the application skips vector search instead of submitting a zero vector. Database errors or unavailable vectors do not block the original alert. Retrieved text never authorizes an automatic action.

Ranking and Ageing

Similarity measures a text match, not correctness. Verification age is additional context, not a guarantee of trust.

The database query orders compatible passage vectors by cosine distance. The enrichment application then applies its verification-age policy to eligible results. This reranking is application logic, not automatic behavior supplied by pgvector. The two worked examples above show the raw similarity before any age adjustment.

With this illustrative factor, verification 180 days ago halves the score. An older passage can still outrank a newer, less similar passage; the formula does not prohibit that. Filter incompatible or superseded guidance separately, and leave an unknown verification date visible rather than treating it as fresh. Both the age scale and relevance threshold need evaluation.

Where the ranking has limits

TF-IDF can miss synonyms and application-specific wording. A local encoder is another design option, but its quality, runtime dependencies and security approval need evaluation. A different dimension can require schema and index changes. Re-encode documents and queries consistently before comparing results on held-out incident queries.

Storage types, distance operators and index limits are documented in the official pgvector reference. The worked SQL uses exact search; approximate indexes trade some recall for speed and need separate validation.

Revisions and Re Embedding

Changed content passes review before the indexer publishes a consistent searchable version.

The indexer fingerprints document content with a checksum. For an approved changed revision, it regenerates passages and vectors, then atomically activates the new rows and retires the previous rows from active search. Prior revisions and retrieval records remain available for incident audit. A checksum identifies changed bytes, not an obsolete recommendation; operational review remains separate.

A content-only change can use the frozen vocabulary and IDF snapshot. Changing either requires reindexing the corpus under a new representation version. An engineer must verify operational accuracy and software compatibility independently of the checksum.

Feedback and Precision

Incident review connects what the agent reported with independently observed outcomes.

The enrichment record captures the incident identifier and exact passage revisions returned. Engineers record whether the page was justified and whether its context helped. The compact table below stores those verdicts; its document references identify the retrieved revisions. Alert correctness and passage usefulness are separate judgements.

CREATE TABLE page_feedback (
    incident_id     TEXT PRIMARY KEY,
    alert_key       TEXT        NOT NULL,
    verdict         BOOLEAN     NOT NULL,
    retrieved_docs  TEXT[],
    doc_helpful     BOOLEAN,
    reviewed_by     TEXT        NOT NULL,
    reviewed_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

Later monitoring status supplies proxy labels for numeric recalibration. Engineer verdicts are retained separately as evaluation evidence rather than silently replacing those labels. An unreviewed page remains unknown, not a negative example. Any training process that uses reviewed outcomes must distinguish them from proxy labels.

What we measure

The evaluation report tracks precision and flags passages with repeated unhelpful verdicts for review. A false page does not by itself prove its attached document was misleading. Document-feedback evaluation is separate from the weekly numeric gradient update.

Catching silent misses

Incident review should compare what paged against independently observed impact. Record an incident with no corresponding alert as a possible miss, then investigate telemetry, routing and threshold behavior before attributing it to the predictor.

Classifying Misses

Incident reviewers classify a miss by where evidence was lost or misinterpreted before choosing a correction.

Threshold misses require investigation; recalibration is not a guaranteed repair. Missing features may require additional telemetry. Wrong context can come from inaccurate documents, a bad mapping, query construction or ranking. Review the evidence before deciding which component to change.

The three strikes rule

Three similar routine misses trigger a tuning review, while a severe availability failure gets immediate investigation. Reviewers approve the correction; the count does not trigger an automatic production change. Recalibration itself does not roll back a harmful update.

Replay, Shadow, Promote

A candidate is evaluated on historical and live evidence before it is allowed to affect notifications.

Offline replay

Replay a candidate only against features and outcomes actually retained. A new column cannot reconstruct missing historical measurements. Use later, held-out periods and independent incident labels where available; count both missed incidents and additional pages.

Shadow scoring

A candidate runs without paging while its decisions are compared with the active version. The evaluation period must contain enough relevant incidents rather than simply reach a fixed number of days. Promotion requires reviewed results against explicit criteria, including missed incidents as well as precision and alert volume.

Locked evaluation thresholds

Version evaluation criteria before testing and retain a recoverable active version. Specify who can promote or roll back a candidate. Recording the pre-update model error alone is not an acceptance test or an automatic rollback mechanism.

Acceptance Gates

Retrieval review considers both freshness and usefulness. Neither freshness nor changed context alone proves that a candidate is better.

Each comparison retains the old and new passage identifiers, revisions, scores and engineer relevance verdicts. Identical passages can still be useful; different passages can be worse. Numeric prediction and retrieval quality are evaluated independently.

Measuring the retrieval gap

A cosine gap between average passage vectors can describe a change in context. Computing it requires the vectors or an additional database calculation; query-to-passage distances alone are insufficient. Compare vectors only within the same representation version, and handle empty results or zero means explicitly. The example threshold below is not validated.

Seasonality and Richer Features

Potential improvements that need collected evidence and evaluation.

Seasonality beyond the week

A weekly average summarizes recent history; it does not by itself learn calendar-aware seasonality. Day of month or fiscal period could become additional features after collection and evaluation. Extra fields need schema, model and resource changes. Their benefit and overhead must be measured rather than assumed.

Richer inputs

Today the predictor reads numeric metrics only. Query level signals, such as which statements were running when replication lag spiked, would sharpen the early warning further. The challenge is doing that in AWK without bloating the forward pass, so any new input goes through the same replay and shadow path before it is trusted.

Adding a feature, step by step

Production Guardrails

This is production, so every intelligent part of the system is deliberately conservative.

Questions and Answers

Questions a database engineer, reviewer or operator might ask, with the practical reasoning behind each answer.

These questions explain the operating model, the stored evidence and the boundaries between retrieval, prediction and human judgement.

Design boundaries