PostgreSQL can act as a lightweight change-notification bus for systems that need to mirror table rows into a search index, document store, cache, or downstream API. Instead of polling a table every few seconds, an AFTER INSERT OR UPDATE OR DELETE trigger emits a small notification payload with pg_notify. A long-running sync process listens on that channel, batches incoming changes for a short window, fetches or transforms the affected rows, and applies them to the target system.
This pattern is useful when the source of truth is already PostgreSQL and the sync target does not require a full Kafka, Debezium, or logical-replication deployment. It gives application teams a direct path from row mutation to downstream update with low latency, transactional visibility, and very little infrastructure. The tradeoff is that LISTEN/NOTIFY is a signaling mechanism rather than a durable queue, so the design must include periodic reconciliation, reconnect handling, idempotent writes, and metrics for missed or failed syncs.
A well-designed trigger-based sync loop has two modes. The real-time path reacts to notifications for freshness, often seeing inserts or updates within milliseconds after transaction commit. The reconciliation path periodically scans the source table with content hashes or timestamps so that dropped connections, listener restarts, or malformed payloads cannot create permanent divergence. Together, the two paths provide a practical middle ground between expensive polling and heavyweight change-data-capture infrastructure.
NOTIFY events are delivered only after the transaction that emitted them commits. If the transaction rolls back, the notification is not delivered. PostgreSQL also preserves ordering for notifications from the same transaction and for notifications across transactions in commit order. This makes notifications a good wake-up signal for downstream systems: a listener never sees a notification for data that did not actually commit.
A row-level trigger can observe the operation type and the NEW or OLD row image for inserts, updates, and deletes. For sync systems, the trigger usually sends a compact JSON payload containing the operation, primary key, and timestamp. Large row bodies should normally remain in the table and be loaded by the sync worker, because PostgreSQL notification payloads are intentionally small and are not designed to carry large documents.
Use LISTEN/NOTIFY to signal that a key changed, not as the authoritative data transport. The sync worker should treat the notification as a hint, then read the current committed row from PostgreSQL or apply a tombstone for deletes. This avoids payload-size limits, reduces trigger overhead, and lets repeated updates to the same row collapse into one downstream write.
Bulk imports, migrations, and update statements can generate many notifications in a short period. A sync worker should accumulate changes for a small interval such as one second, deduplicate by primary key, and apply the final state of each row once. This reduces downstream write amplification while preserving eventual correctness.
The database side creates a trigger function that normalizes inserts, updates, and deletes into a common notification envelope. Inserts and updates can reference NEW; deletes must reference OLD. The payload includes only fields needed to route the change and find the affected row.
CREATE OR REPLACE FUNCTION notify_document_change()
RETURNS trigger AS $$
DECLARE
row_id text;
op text := TG_OP;
BEGIN
IF TG_OP = 'DELETE' THEN
row_id := OLD.id;
ELSE
row_id := NEW.id;
END IF;
PERFORM pg_notify(
'documents_changes',
json_build_object(
'operation', op,
'id', row_id,
'changed_at', clock_timestamp()
)::text
);
IF TG_OP = 'DELETE' THEN
RETURN OLD;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER documents_change_trigger
AFTER INSERT OR UPDATE OR DELETE ON documents
FOR EACH ROW
EXECUTE FUNCTION notify_document_change();The worker holds a dedicated PostgreSQL connection, issues LISTEN documents_changes, and receives asynchronous notifications. It stores pending changes in a map keyed by row ID. A periodic flush processes the accumulated map, so rapid sequences like insert-update-update become one downstream upsert.
// Pseudocode for the worker loop.
type Change struct {
Operation string `json:"operation"`
ID string `json:"id"`
}
pending := map[string]Change{}
ticker := time.NewTicker(time.Second)
for {
select {
case notification := <-notifications:
var change Change
if err := json.Unmarshal([]byte(notification.Payload), &change); err != nil {
metrics.BadPayloads.Add(1)
continue
}
pending[change.ID] = change
case <-ticker.C:
batch := pending
pending = map[string]Change{}
if err := syncBatch(ctx, db, target, batch); err != nil {
metrics.SyncErrors.Add(1)
for id, change := range batch {
pending[id] = change
}
}
}
}For upserts, the worker reads the current row by primary key and writes it to the target. For deletes, it sends a delete or tombstone to the target system. If an update notification arrives but the row no longer exists because a later delete committed, the worker should treat the missing row as a delete; the batch always converges to the latest committed state.
Applications often store canonical records in PostgreSQL but query them through a search engine or vector index. Trigger-based sync keeps the index fresh without forcing every application write path to remember to call the indexing service. The application writes one transaction to PostgreSQL; the trigger emits a signal; the sync worker performs indexing out of band.
When a table backs expensive API responses, a listener can invalidate cache keys or refresh a materialized projection as soon as rows change. This works best when the notification payload contains a stable primary key, tenant ID, or partition key so the worker can update only the affected slice.
Small teams can use this pattern to publish internal integration events without introducing a broker. For example, a billing service can listen for account-plan changes, or a document-processing worker can listen for newly inserted files. The pattern should remain local and bounded; once multiple services need durable fan-out, replay, and independent retention, a real event log becomes the better fit.
A notification listener can disconnect, restart, or fall behind. Add a periodic full or incremental reconciliation pass that scans rows changed since the last checkpoint and compares content hashes against the target. The real-time path optimizes latency; the reconciliation path provides correctness.
-- Source table fields that make reconciliation cheap.
ALTER TABLE documents
ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now(),
ADD COLUMN content_hash text GENERATED ALWAYS AS
(md5(data::text)) STORED;
CREATE INDEX documents_updated_at_idx ON documents (updated_at);
-- Reconciliation scan.
SELECT id, data, content_hash
FROM documents
WHERE updated_at > $1
ORDER BY updated_at
LIMIT 1000;Trigger functions run inside the mutating transaction. Expensive JSON construction, external calls, or complex queries increase write latency and can block user-facing transactions. Keep the trigger limited to computing the key and emitting a compact notification. Heavy transformation belongs in the worker.
Because a batch can be retried after a partial failure, the target write should be an upsert keyed by the source primary key. Deletes should also be idempotent: deleting an already-missing document should be treated as success. With idempotent target writes, the worker can safely retry after timeouts or reconnects.
LISTEN/NOTIFY does not provide replay after listener downtime. Always include reconciliation or a separate durable outbox if missed events are unacceptable.NEW row data. Include enough identity in the delete notification to remove or tombstone the target record.LISTEN, and run reconciliation from the last safe checkpoint before trusting real-time mode again.