PostgreSQL Query Change Alerts for Developers: A Practical Guide

Shipping features is only half the job. The other half is knowing when something important changes in your data. Whether a spike in failed logins, a sudden drop in signups, or a schema change before a release, timely visibility lets you respond fast. This guide explains postgresql query change alerts for developers in practical detail: which techniques exist, how to implement them safely, and how to avoid noisy, expensive alerts.

Why change alerts matter

Data moves faster than dashboards. Query-driven alerts turn critical conditions into action. Done well, they:

• Catch regressions early, before customers notice
• Protect data quality with guardrail checks
• Provide context-rich notifications tied to the exact query you care about
• Reduce manual log-scrubbing and ad hoc SQL during incidents

Four ways to detect changes in PostgreSQL

There is no single best approach; choose based on latency needs, privileges, and operational constraints.

1) Triggers + NOTIFY for near real-time alerts

Use row-level triggers to capture inserts, updates, or deletes and push a lightweight event via pg_notify. A listener app subscribes with LISTEN to receive notifications almost instantly.

-- Example: audit and notify on user signup changes
CREATE TABLE user_audit (
  id bigserial PRIMARY KEY,
  action text NOT NULL,
  user_id bigint NOT NULL,
  changed_at timestamptz NOT NULL DEFAULT now(),
  payload jsonb
);

CREATE OR REPLACE FUNCTION notify_user_change() RETURNS trigger AS $$
BEGIN
  INSERT INTO user_audit(action, user_id, payload)
  VALUES (TG_OP, NEW.id, to_jsonb(NEW));
  PERFORM pg_notify('user_changes', json_build_object(
    'action', TG_OP,
    'user_id', NEW.id,
    'at', now()
  )::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_change_trg
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION notify_user_change();

Then listen from an app or script:

# Python listener using psycopg2
import psycopg2, select
conn = psycopg2.connect(dsn='dbname=app user=readonly sslmode=require')
conn.set_session(autocommit=True)
cur = conn.cursor()
cur.execute('LISTEN user_changes;')
print('Listening...')

while True:
    if select.select([conn], [], [], 30) == ([], [], []):
        continue
    conn.poll()
    while conn.notifies:
        n = conn.notifies.pop(0)
        print('Alert:', n.payload)

Pros: low latency, precise, low overhead per event. Cons: payload limit (~8 KB), requires app listener uptime, triggers add write-path logic.

2) Polling queries on a schedule

Schedule SQL checks to run at intervals and alert on condition changes (e.g., thresholds, anomalies). Suitable when you cannot add triggers or need aggregate logic across many rows.

-- Index to support change detection by timestamp
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_updated_at ON users(updated_at);

-- Query: alert if today's signups drop >= 50% vs 7-day average
WITH daily AS (
  SELECT date_trunc('day', created_at) AS day, count(*) AS c
  FROM users
  WHERE created_at >= now() - interval '8 days'
  GROUP BY 1
),
agg AS (
  SELECT avg(c) FILTER (WHERE day < date_trunc('day', now())) AS avg7,
         max(c) FILTER (WHERE day = date_trunc('day', now())) AS today
  FROM daily
)
SELECT today, avg7, (today <= 0.5 * avg7) AS should_alert FROM agg;

Or detect rapid growth of error rows:

SELECT count(*) AS errors_last_10m
FROM api_logs
WHERE level = 'error' AND ts >= now() - interval '10 minutes';

Pros: simple, works with read-only access, great for aggregates. Cons: minute-level latency, potential load if queries scan large tables. Use indexes, predicates, and statement_timeout.

3) Logical decoding and CDC

Change Data Capture streams row changes from WAL using a replication slot and a decoding plugin (e.g., pgoutput, wal2json). Ideal for robust pipelines and high-volume change feeds.

Pros: comprehensive, ordered stream of changes, decoupled from write path. Cons: needs replication privileges, infra to manage slots and consumers, more setup than triggers.

4) Event triggers for schema change alerts

To catch DDL (schema) changes, use event triggers:

CREATE TABLE ddl_audit (
  id bigserial PRIMARY KEY,
  tag text,
  objid oid,
  ddl text,
  executed_at timestamptz DEFAULT now()
);

CREATE OR REPLACE FUNCTION log_ddl() RETURNS event_trigger AS $$
BEGIN
  INSERT INTO ddl_audit(tag, objid, ddl)
  SELECT tg_tag, objid, ddl_command_end FROM pg_event_trigger_ddl_commands();
  PERFORM pg_notify('ddl_changes', 'DDL change recorded');
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER on_ddl ON ddl_command_end EXECUTE FUNCTION log_ddl();

What to monitor: practical query ideas

Use case	Query pattern	Alert when
Signup drop	Daily count vs 7-day average	Today <= 50% of baseline
Error spike	Count errors in last 10 min	> X or slope increases sharply
Data freshness	Max(updated_at) per table	Stale beyond SLA (e.g., 15 min)
Payment failures	Failed status ratio	Failure rate > 2% over 30 min
Slow queries	pg_stat_statements	mean_time or calls jumps
Replication lag	pg_last_wal_receive_lsn	Lag > 60 s
Schema drift	Event triggers	Any DDL outside change window
Disk pressure	pg_database_size	Size growth exceeds budget

Design alert rules that do not spam

• Thresholds with hysteresis: use different trip and clear thresholds to avoid flapping.
• Aggregation windows: count over 5–15 minutes, not per second, to smooth noise.
• Deduplication: group repeated hits into one alert with a counter.
• Cooldowns: suppress repeats for N minutes after firing.
• Severity: map critical alerts to paging, info alerts to email or chat.
• Context: include key IDs, sample rows, and links to runbooks.

Performance, security, and reliability

• Least privilege: use a dedicated read-only role. Grant only needed schemas/tables.
• Safe timeouts: set statement_timeout and lock_timeout on sessions that run checks.
• Indexes: support predicates like updated_at >= now() - interval 'X'.
• Run on a replica: offload read-heavy checks to a read replica when possible.
• Idempotence: alert conditions should be deterministic over the window queried.
• Bounded payloads: if using pg_notify, keep messages small and enrich later.
• Backpressure: for CDC, monitor replication slot lag and consumer health.
• Observability: log when checks run, duration, and whether they fired.

A quick blueprint: from zero to first alert

1. Choose a signal: for example, error rate in api_logs.
2. Add indexes: index on (level, ts).
3. Write the check:

SELECT count(*) AS c
FROM api_logs
WHERE level = 'error' AND ts >= now() - interval '10 minutes';

4. Define the threshold: fire when c >= 100.
5. Schedule: run every 1 minute with your job runner (cron, CI job, or serverless).
6. Notify: send to chat, email, or a webhook.

Here is a minimal Python polling script you can adapt:

import os, time, psycopg2
THRESH = int(os.getenv('ERROR_THRESHOLD', '100'))
DSN = os.getenv('PG_DSN')

sql = (
  "SELECT count(*) FROM api_logs "
  "WHERE level = 'error' AND ts >= now() - interval '10 minutes'"
)

while True:
    with psycopg2.connect(DSN) as conn:
        with conn.cursor() as cur:
            cur.execute("SET statement_timeout = '5s'")
            cur.execute(sql)
            c = cur.fetchone()[0]
            if c >= THRESH:
                print(f'ALERT errors_last_10m={c}')
                # send_email('Error spike', f'errors_last_10m={c}')
    time.sleep(60)

Example: alert when daily signups drop by half

Detect a significant drop relative to recent history. We will compute a baseline and compare today.

WITH daily AS (
  SELECT date_trunc('day', created_at) AS day, count(*) AS c
  FROM users
  WHERE created_at >= now() - interval '15 days'
  GROUP BY 1
),
series AS (
  SELECT c AS today
  FROM daily WHERE day = date_trunc('day', now())
),
baseline AS (
  SELECT avg(c) AS avg7
  FROM daily
  WHERE day < date_trunc('day', now())
  ORDER BY day DESC
  LIMIT 7
)
SELECT today, avg7, (today <= 0.5 * avg7) AS should_alert
FROM series, baseline;

Attach this to your scheduler and alert when should_alert is true. For richer context, include sample signup sources:

SELECT source, count(*) AS c
FROM users
WHERE created_at >= date_trunc('day', now())
GROUP BY source
ORDER BY c DESC
LIMIT 5;

Advanced patterns

• Materialized views: pre-aggregate heavy queries and refresh on a cadence to keep checks fast.
• Stateful checks: store last processed ID or timestamp to only scan deltas.
• Multi-signal alerts: require two conditions to reduce false positives (e.g., signups drop and error logs spike).
• Anomaly detection: use median and MAD or a rolling z-score for robust baselines in the presence of outliers.

-- Rolling z-score example over 30 days for daily signups
WITH series AS (
  SELECT date_trunc('day', created_at) AS d, count(*) AS c
  FROM users
  WHERE created_at >= now() - interval '60 days'
  GROUP BY 1
),
stats AS (
  SELECT d, c,
         avg(c) OVER (ORDER BY d ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING) AS mean,
         stddev_pop(c) OVER (ORDER BY d ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING) AS sd
  FROM series
)
SELECT d, c, (c - mean) / nullif(sd, 0) AS z
FROM stats
WHERE d = date_trunc('day', now());

Testing and maintenance

• Dry runs: log would-be alerts for a week before enabling notifications.
• Backfill tests: run checks on historical windows to validate thresholds.
• Chaos drills: simulate conditions (e.g., insert dummy error rows) to ensure the pipeline works end-to-end.
• Runbooks: include next steps in the alert, such as links to dashboards and rollback playbooks.
• Review quarterly: retire noisy alerts and add new ones as your product evolves.

Tip: start with a handful of high-signal alerts tied to business outcomes and SLOs. Expand only after you prove they are actionable.

Choosing the right approach

Use triggers and LISTEN/NOTIFY when you need near real-time row-level alerts and can modify the schema. Use scheduled polling for aggregated business metrics and when you only have read-only access. Pick CDC for comprehensive streams or cross-system integrations. Combine approaches in one stack if needed.

If you prefer an off-the-shelf, mobile-first way to run secure read-only checks and receive push or email notifications, the iOS app Query Alert connects to PostgreSQL and other databases to turn your queries into alerts with customizable schedules.