pg_tide

Transactional outbox, idempotent inbox, and relay pipelines for PostgreSQL 18+.

pg_tide gives your PostgreSQL database a built-in messaging backbone. Publish events within your existing transactions — no dual-writes, no distributed transactions, no message brokers required at the database layer.

When you're ready to fan out to Kafka, NATS, Redis Streams, or any other system, the pg-tide relay binary bridges the gap — reading from outboxes, delivering to external sinks, and writing back to inboxes with exactly-once semantics.

The Problem pg_tide Solves

Most applications eventually need to publish events to other systems. A customer places an order — and the warehouse needs to know, the analytics pipeline needs to know, the email service needs to send a confirmation. The naive approach is deceptively simple: save the order to your database, then publish an event to your message broker.

But what happens when the broker publish fails after the database commit? Or when your application crashes between the two operations? You get silent data loss — the order exists but nobody downstream knows about it. This is the dual-write problem, and it's one of the most common sources of data inconsistency in distributed systems.

pg_tide eliminates this entire class of bugs by implementing the transactional outbox pattern as a PostgreSQL extension. Your application writes both the business data and the event in a single database transaction. They succeed or fail together — atomically, consistently, and durably. A separate relay process then delivers committed events to downstream systems, retrying indefinitely until delivery succeeds.

How It Works

┌─────────────────────────────────────────────────────────────┐
│                    PostgreSQL 18+                             │
│                                                              │
│  Your Application                                            │
│       │                                                      │
│       ├──▶ INSERT INTO orders (...)  ─┐                      │
│       │                               ├── Same transaction   │
│       └──▶ SELECT tide.outbox_publish ─┘                     │
│                        │                                     │
│            tide.tide_outbox_messages                          │
│                        │                                     │
└────────────────────────┼─────────────────────────────────────┘
                         │  pg_notify wakes relay
                         ▼
┌──────────────────────────────────────────────────────────────┐
│                   pg-tide relay binary                         │
│                                                               │
│  Polls outbox ──▶ Delivers to sink ──▶ Commits offset         │
└──────────────────────────────────────────────────────────────┘
                         │
                         ▼
          NATS · Kafka · Redis · RabbitMQ · SQS · Webhooks

Key Features

At a Glance

-- Create an outbox (one-time setup)
SELECT tide.outbox_create('orders', p_retention_hours := 48);

-- Publish within your business transaction
BEGIN;
  INSERT INTO orders (id, total) VALUES (42, 99.99);
  SELECT tide.outbox_publish('orders',
    '{"order_id": 42, "total": 99.99}'::jsonb,
    '{"event_type": "order.created"}'::jsonb
  );
COMMIT;

-- Configure a relay pipeline
SELECT tide.relay_set_outbox('orders-nats', 'orders', 'nats',
  '{"url": "nats://localhost:4222", "subject": "orders.events"}'::jsonb
);

-- Start the relay — messages flow automatically
-- pg-tide --postgres-url "postgres://user:pass@localhost:5432/mydb"

Who Is This For?

pg_tide is built for:

• Backend engineers building applications on PostgreSQL who need reliable event publishing
• Platform teams providing messaging infrastructure without the operational burden of a full streaming platform
• DBAs who want messaging capabilities that integrate naturally with their existing PostgreSQL operational practices

If PostgreSQL is your source of truth and you need events to flow reliably to other systems, pg_tide is designed for you.

Glossary

Key terms used throughout this documentation:

Documentation Guide

This documentation is organized to match your learning journey:

1. Evaluate — decide if pg_tide is right for your use case
2. Getting Started — install and build your first pipeline
3. Concepts — understand the mechanics in depth
4. SQL Reference — complete API documentation
5. Relay Guide — configure and operate the relay binary
6. Tutorials — guided walkthroughs of common patterns
7. Operations — production deployment and maintenance
8. Integrations — platform-specific guidance

License

pg_tide is released under the Apache-2.0 license.

Choosing pg_tide

Choosing the right messaging infrastructure is one of the most consequential architectural decisions you'll make. This page helps you determine whether pg_tide is the right fit for your project by examining where it excels, where alternatives serve better, and how it compares to other tools you might be considering.

When pg_tide Is a Great Fit

You need reliable event publishing from PostgreSQL

Your application writes to PostgreSQL and needs to notify other systems about those writes — sending emails, updating search indexes, feeding analytics pipelines, triggering downstream workflows. You want guarantees that every committed transaction produces exactly one event: no lost messages, no duplicates, no manual reconciliation.

pg_tide was built specifically for this scenario. The transactional outbox pattern ensures your events are published atomically with your business data. If the transaction commits, the event is guaranteed to be delivered. If it rolls back, the event never existed.

You want to eliminate dual-write bugs

The dual-write problem is pernicious because it's intermittent and silent. Your application might work perfectly 99.9% of the time, but during network hiccups, process restarts, or database failovers, events get lost or duplicated. These bugs are incredibly difficult to detect in testing and even harder to reproduce.

pg_tide eliminates this entire class of bugs by design. There is no dual write — only a single database write that includes both your data and the event. The relay handles delivery separately, retrying indefinitely until downstream systems acknowledge receipt.

You prefer SQL over SDKs

pg_tide is a PostgreSQL extension. Publishing an event is a SELECT tide.outbox_publish(...) call. There's no client library to install, no serialization framework to learn, no connection pooling for a separate broker, no SDK version compatibility to manage. Any language or framework that can talk to PostgreSQL can publish events.

This means your Go service, Python script, dbt model, PL/pgSQL function, and psql session can all publish events using exactly the same API. The outbox is a database table — you can query it, monitor it, and manage it with standard SQL tools.

You're already running PostgreSQL

If PostgreSQL is your primary data store — and for many teams, it is — pg_tide adds messaging capabilities without introducing new infrastructure. No Kafka cluster to operate, no ZooKeeper to babysit, no broker to monitor. The relay binary is a single static executable that reads its configuration from the same database it's delivering messages from.

This dramatically reduces operational overhead. You already know how to back up PostgreSQL, monitor its performance, manage its connections, and failover between replicas. pg_tide inherits all of that operational maturity.

You need exactly-once delivery semantics

Many messaging systems provide at-most-once or at-least-once delivery. True exactly-once requires coordination between the sender and receiver. pg_tide achieves this through the combination of transactional publishing (no message loss), consumer offset tracking (no re-processing), and the idempotent inbox (no duplicates). The end-to-end result is effectively exactly-once — each event is processed precisely one time.

Your throughput fits within PostgreSQL's capacity

For most applications, PostgreSQL can handle 5,000–15,000 outbox publishes per second on a single connection (depending on payload size and hardware). If your event volume fits within this range — which covers the vast majority of OLTP workloads — pg_tide provides simpler operations than dedicated streaming platforms.

When to Consider Alternatives

You need sub-millisecond propagation latency

pg_tide's relay polls the outbox at a configurable interval (it wakes immediately via pg_notify for new messages, but batching introduces slight delays). For use cases that demand microsecond-level propagation — high-frequency trading signals, real-time game state — a dedicated event streaming platform with direct in-memory writes (like NATS Core or Kafka with acks=0) will provide lower latency.

That said, pg_tide's latency is typically under 100ms end-to-end. For most applications (webhook delivery, service coordination, analytics feeds), this is more than adequate.

You have no PostgreSQL in your stack

pg_tide is a PostgreSQL extension — that's the whole point. If your data lives in MySQL, MongoDB, DynamoDB, or another database, pg_tide can't help you. Look at:

• Debezium — CDC for MySQL, PostgreSQL, MongoDB, SQL Server, and more
• Maxwell — MySQL-specific CDC tool
• DynamoDB Streams — built-in change capture for DynamoDB

You're doing pure pub/sub without durability requirements

If you need ephemeral fire-and-forget messaging — real-time typing indicators, presence updates, live dashboard refreshes — where missed messages are perfectly acceptable, a simple Redis Pub/Sub or NATS Core subscription is lighter and faster. No durability means no outbox, no offset tracking, and no relay to operate.

Your sustained throughput exceeds PostgreSQL's write capacity

pg_tide's throughput ceiling is fundamentally PostgreSQL's INSERT performance. For sustained write rates above ~100,000 messages/second on a single outbox table (very high-volume telemetry, clickstream data, IoT sensor feeds), dedicated log-structured systems (Kafka, Redpanda, Pulsar) are purpose-built for this scale.

However, before concluding that you need more throughput, consider whether you can partition your events across multiple outboxes, which allows parallel relay consumption.

You need automatic schema-change capture

If your use case is "capture every row change in every table automatically, without modifying application code," Debezium's CDC approach is better suited. pg_tide requires you to explicitly publish events — you choose what gets published, when, and in what format. This is a strength (explicit > implicit for event contracts), but it requires more application involvement.

The Sweet Spot

pg_tide occupies the space where transactional correctness matters more than raw throughput, and where operational simplicity (no broker cluster, no JVM, no ZooKeeper) outweighs the need for a standalone streaming platform.

Typical use cases that pg_tide handles beautifully:

Detailed Comparison with Alternatives

pg_tide vs. Debezium

Choose pg_tide when you want explicit, semantic events that are decoupled from your table schema, and you prefer minimal infrastructure. Choose Debezium when you need automatic capture of all database changes without modifying application code, and you're willing to operate the Kafka ecosystem.

pg_tide vs. Application-Level Outbox (DIY)

Choose pg_tide to avoid reinventing reliable messaging infrastructure. A DIY outbox is deceptively simple to start but grows in complexity quickly as you add retry logic, offset tracking, multiple consumers, monitoring, and failover. Choose DIY only when you have very specific requirements that don't map to pg_tide's model.

pg_tide vs. pg_notify / LISTEN

Choose pg_tide when you need durable, reliable delivery with guarantees. Choose pg_notify for lightweight real-time signals where message loss is acceptable — like cache invalidation hints or live UI updates where the client can refresh on reconnect.

pg_tide vs. Writing Directly to Kafka/NATS

Choose pg_tide when transactional correctness is paramount and throughput fits within PostgreSQL's capacity. Choose direct broker writes when you accept the dual-write tradeoff for maximum throughput and minimum latency, or when your application already runs inside the broker ecosystem (e.g., a Kafka Streams application).

Cost Analysis: pg_tide vs. Running Kafka

For teams evaluating pg_tide against a full Kafka deployment, here's a practical comparison of operational costs:

pg_tide's total cost of ownership is dramatically lower for teams whose primary workload is a PostgreSQL-backed application with moderate event volumes (< 50K events/second).

Decision Flowchart

Ask yourself these questions in order:

1. Is PostgreSQL your primary data store? If not → look at Debezium, platform-specific CDC
2. Do your events need transactional guarantees? If not → consider direct broker writes or pg_notify
3. Is your throughput under ~50K events/second? If not → consider Kafka/Redpanda
4. Do you want to minimize operational overhead? If yes → pg_tide
5. Do you need automatic schema-change capture? If yes → consider Debezium (or combine both)

If you answered "yes" to questions 1, 2, 3, and 4 — pg_tide is an excellent fit.

Migration Paths

Coming from pg_notify

If you're currently using pg_notify for event delivery and hitting its limitations (payload size, durability, reliability):

1. Install pg_tide and create outboxes for your event channels
2. Replace PERFORM pg_notify(channel, payload) with SELECT tide.outbox_publish(outbox, payload, headers)
3. Set up relay pipelines to your downstream consumers
4. Benefit from durability, retry, offset tracking, and exactly-once semantics

Coming from a DIY outbox table

If you've built a custom outbox pattern:

1. Install pg_tide alongside your existing tables
2. Migrate pipeline logic to pg_tide's relay (eliminates your custom polling code)
3. Use pg_tide's consumer groups instead of custom offset tracking
4. Add inboxes for receiving-side deduplication
5. Decommission your custom relay code

Coming from Debezium

If you're considering pg_tide as a complement or replacement for Debezium:

• Complement: Use Debezium for bulk CDC (replicating entire tables) and pg_tide for semantic business events (explicit, shaped events published by application logic)
• Replace: If you're using Debezium primarily for outbox-style event publishing (Debezium's outbox router), pg_tide provides the same capability with far less infrastructure

Architecture

pg_tide consists of two components that work together: a PostgreSQL extension that manages the outbox/inbox catalog, and a relay binary that bridges messages to external systems.

High-Level Overview

┌─────────────────────────────────────────────────────────┐
│                    PostgreSQL 18+                         │
│                                                          │
│  ┌──────────────┐   ┌──────────────┐   ┌────────────┐  │
│  │ tide_outbox   │   │ tide_inbox    │   │ relay_     │  │
│  │ _config       │   │ _config       │   │ *_config   │  │
│  └──────┬───────┘   └──────┬───────┘   └─────┬──────┘  │
│         │                   │                  │         │
│  ┌──────▼───────┐   ┌──────▼───────┐         │         │
│  │ tide_outbox   │   │ {name}_inbox  │         │         │
│  │ _messages     │   │  (per inbox)  │         │         │
│  └──────┬───────┘   └──────▲───────┘         │         │
│         │                   │                  │         │
└─────────┼───────────────────┼──────────────────┼─────────┘
          │                   │                  │
          │  LISTEN/NOTIFY    │                  │
          ▼                   │                  ▼
┌─────────────────────────────────────────────────────────┐
│                   pg-tide relay binary                    │
│                                                          │
│  ┌────────────┐        ┌────────────┐                   │
│  │   Source    │───────▶│    Sink     │                   │
│  │ (outbox    │        │ (NATS,     │                   │
│  │  poller)   │        │  Kafka,    │                   │
│  └────────────┘        │  Redis…)   │                   │
│                         └────────────┘                   │
│  ┌────────────┐        ┌────────────┐                   │
│  │   Source    │───────▶│    Sink     │                   │
│  │ (NATS,     │        │ (inbox     │                   │
│  │  Kafka…)   │        │  writer)   │                   │
│  └────────────┘        └────────────┘                   │
└─────────────────────────────────────────────────────────┘
          │                                     │
          ▼                                     ▼
┌──────────────┐                    ┌──────────────────┐
│  External     │                    │  External         │
│  Systems      │                    │  Systems          │
│  (consumers)  │                    │  (producers)      │
└──────────────┘                    └──────────────────┘

The Extension Layer

The pg_tide extension installs into the tide schema and provides:

• Catalog tables — configuration for outboxes, inboxes, consumer groups, and relay pipelines
• Message storage — a shared tide_outbox_messages table for all outboxes, individual {name}_inbox tables for each inbox
• SQL API — functions for publishing, consuming, and managing the lifecycle
• NOTIFY triggers — real-time notifications when relay config changes

The extension has no background workers and no shared memory. All state is purely relational, making it compatible with connection poolers (PgBouncer, PgCat) and managed PostgreSQL services.

The Relay Binary

The pg-tide binary is a standalone Rust process that:

1. Connects to PostgreSQL and reads pipeline configurations from the relay catalog
2. Acquires advisory locks for each pipeline (enabling multi-relay HA deployments)
3. Polls outbox tables for new messages (forward mode)
4. Subscribes to external sources for incoming messages (reverse mode)
5. Delivers messages to configured sinks with retry and dedup
6. Commits offsets after successful delivery (exactly-once semantics)
7. Exposes Prometheus metrics and a health endpoint

The relay supports hot-reload via LISTEN tide_relay_config — when you update pipeline config in the database, the relay picks up changes without restart.

Forward Mode (Outbox → External)

Application   ──INSERT──▶  tide_outbox_messages
                                   │
                                   │ (relay polls)
                                   ▼
                             pg-tide relay  ──publish──▶  NATS / Kafka / Redis / …
                                   │
                                   │ (on success)
                                   ▼
                          commit offset + mark consumed

Reverse Mode (External → Inbox)

NATS / Kafka / Redis / …  ──subscribe──▶  pg-tide relay
                                                  │
                                                  │ (dedup + insert)
                                                  ▼
                                           {name}_inbox table
                                                  │
                                                  │ (application reads)
                                                  ▼
                                            Your application

Deployment Topologies

Single relay (simplest)

One relay instance handles all pipelines. Suitable for low-to-medium throughput.

Multiple relays (HA)

Multiple relay instances connect to the same database. PostgreSQL advisory locks ensure each pipeline is owned by exactly one relay — automatic failover when a relay dies.

Sidecar pattern

Deploy the relay as a sidecar container alongside your application pod in Kubernetes. Each pod handles its own subset of pipelines.

Installation

pg_tide has two components to install: the PostgreSQL extension (SQL functions and catalog tables) and the relay binary (the pg-tide process that bridges messages to external systems).

Prerequisites

• PostgreSQL 18 or later
• Superuser or CREATE EXTENSION privileges on your target database

Installing the Extension

From Source (pgrx)

# Install cargo-pgrx if you haven't already
cargo install cargo-pgrx --version "=0.18.0" --locked
cargo pgrx init --pg18 $(which pg_config)

# Build and install
cd pg-tide-ext
cargo pgrx install --release

Enable the Extension

CREATE EXTENSION pg_tide;

This creates the tide schema with all catalog tables, views, and functions.

Installing the Relay Binary

From GitHub Releases

Download the latest release for your platform from the releases page:

# Linux (amd64)
curl -LO https://github.com/trickle-labs/pg-tide/releases/latest/download/pg-tide-x86_64-unknown-linux-gnu.tar.gz
tar xzf pg-tide-x86_64-unknown-linux-gnu.tar.gz
sudo mv pg-tide /usr/local/bin/

# macOS (Apple Silicon)
curl -LO https://github.com/trickle-labs/pg-tide/releases/latest/download/pg-tide-aarch64-apple-darwin.tar.gz
tar xzf pg-tide-aarch64-apple-darwin.tar.gz
sudo mv pg-tide /usr/local/bin/

From Source (Cargo)

cargo install --git https://github.com/trickle-labs/pg-tide pg-tide-relay

Docker

docker pull ghcr.io/trickle-labs/pg-tide:latest

Verify Installation

# Check relay version
pg-tide --version

# Check extension is installed
psql -c "SELECT * FROM pg_extension WHERE extname = 'pg_tide';"

Next Steps

• Quickstart → — publish your first message in 5 minutes
• Tutorial → — set up a complete outbox-to-sink pipeline

Your First Pipeline

This guide walks you through setting up pg_tide from scratch and building a complete message pipeline. By the end, you'll have an outbox publishing order events and a relay delivering them to NATS — with monitoring, consumer tracking, and exactly-once delivery all working together.

We'll go step by step, explaining what's happening at each stage so you understand not just what to do, but why each piece matters.

Prerequisites

Before starting, make sure you have:

• PostgreSQL 18+ running and accessible (local or remote)
• NATS server running locally (we'll use this as our message sink)
• pg-tide relay binary installed (see Installation)

If you just want to kick the tires quickly, here's a Docker Compose file that sets up everything:

# docker-compose.yml — complete pg_tide development environment
services:
  postgres:
    image: postgres:18
    environment:
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: app
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

  nats:
    image: nats:latest
    ports:
      - "4222:4222"   # Client connections
      - "8222:8222"   # Monitoring

  pg-tide-relay:
    image: ghcr.io/trickle-labs/pg-tide:latest
    depends_on:
      - postgres
      - nats
    environment:
      PG_TIDE_POSTGRES_URL: "postgres://postgres:postgres@postgres:5432/app"
      PG_TIDE_LOG_FORMAT: "json"
      PG_TIDE_LOG_LEVEL: "info"
    ports:
      - "9090:9090"   # Metrics + health

volumes:
  pgdata:

Start it with docker compose up -d, then connect to PostgreSQL with:

psql "postgres://postgres:postgres@localhost:5432/app"

Step 1: Install the Extension

The pg_tide extension creates the tide schema with all the catalog tables, views, and functions you'll need:

CREATE EXTENSION pg_tide;

Let's verify it's installed correctly:

SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_tide';

 extname | extversion
---------+------------
 pg_tide | 0.1.0

Behind the scenes, this created:

• The tide schema
• Configuration tables for outboxes, inboxes, consumer groups, and relay pipelines
• The shared tide.tide_outbox_messages table where all outbox messages live
• Views like tide.outbox_pending and tide.consumer_lag for monitoring
• SQL functions like tide.outbox_publish() for the API

Step 2: Create an Outbox

An outbox is a named message stream. You might have one outbox for order events, another for user events, another for inventory changes — each is logically separate but physically stored in the same table (discriminated by name).

Let's create an outbox for order events:

SELECT tide.outbox_create('orders',
  p_retention_hours := 48,
  p_inline_threshold := 10000
);

What do these parameters mean?

• 'orders' — the name of our outbox. This is how you'll refer to it when publishing and when configuring relay pipelines.
• p_retention_hours := 48 — consumed messages are kept for 48 hours before cleanup. This gives you time to investigate issues and replay if needed.
• p_inline_threshold := 10000 — if more than 10,000 messages are pending (unconsumed), publishing will pause to create backpressure. This prevents unbounded outbox growth if the relay is down.

Verify the outbox exists:

SELECT * FROM tide.tide_outbox_config;

 outbox_name | retention_hours | inline_threshold | enabled |         created_at
-------------+-----------------+------------------+---------+----------------------------
 orders      |              48 |            10000 | t       | 2025-01-15 10:00:00.000+00

Step 3: Publish Your First Messages

Now let's simulate what your application would do — publishing events within business transactions. The key insight is that the event publish is inside the same transaction as the business logic:

-- Create a simple orders table for this tutorial
CREATE TABLE IF NOT EXISTS orders (
    id SERIAL PRIMARY KEY,
    customer_id TEXT NOT NULL,
    total NUMERIC(10,2) NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Now publish an event atomically with the business operation
BEGIN;
  INSERT INTO orders (id, customer_id, total, status)
  VALUES (1, 'cust-alice', 149.99, 'confirmed');

  SELECT tide.outbox_publish('orders',
    '{"order_id": 1, "customer_id": "cust-alice", "total": 149.99, "status": "confirmed"}'::jsonb,
    '{"event_type": "order.confirmed", "source": "tutorial"}'::jsonb
  );
COMMIT;

Let's publish a few more events to make things interesting:

BEGIN;
  INSERT INTO orders (id, customer_id, total, status)
  VALUES (2, 'cust-bob', 42.00, 'confirmed');

  SELECT tide.outbox_publish('orders',
    '{"order_id": 2, "customer_id": "cust-bob", "total": 42.00, "status": "confirmed"}'::jsonb,
    '{"event_type": "order.confirmed", "source": "tutorial"}'::jsonb
  );
COMMIT;

BEGIN;
  INSERT INTO orders (id, customer_id, total, status)
  VALUES (3, 'cust-charlie', 299.95, 'confirmed');

  SELECT tide.outbox_publish('orders',
    '{"order_id": 3, "customer_id": "cust-charlie", "total": 299.95, "status": "confirmed"}'::jsonb,
    '{"event_type": "order.confirmed", "source": "tutorial"}'::jsonb
  );
COMMIT;

What just happened: Each transaction atomically wrote the order to the orders table AND the event to the outbox. If any transaction had failed (constraint violation, network error, application crash), both the order and the event would have been rolled back together. No orphaned events, no missing events.

Step 4: Check the Outbox Status

Let's verify our messages are pending (waiting for delivery):

SELECT * FROM tide.outbox_pending;

 outbox_name | pending_count |       oldest_at        | max_id
-------------+---------------+------------------------+--------
 orders      |             3 | 2025-01-15 10:01:00+00 |      3

Three messages waiting for the relay to pick them up. You can also get detailed status:

SELECT tide.outbox_status('orders');

This returns a JSONB object with comprehensive information about the outbox's current state.

Step 5: Create a Consumer Group

Before the relay can start delivering messages, it needs a consumer group to track its progress:

SELECT tide.create_consumer_group('nats-relay', 'orders',
  p_auto_offset_reset := 'earliest'
);

We're using 'earliest' because we want the relay to process all existing messages, including the three we just published. If we used 'latest', it would skip those and only process future messages.

Step 6: Configure the Relay Pipeline

Now we tell pg_tide how to deliver messages from the orders outbox to NATS:

SELECT tide.relay_set_outbox(
  'orders-to-nats',        -- pipeline name (unique identifier)
  'orders',                -- source outbox
  'nats',                  -- sink type
  jsonb_build_object(      -- sink-specific configuration
    'url', 'nats://localhost:4222',
    'subject', 'orders.{event_type}'
  )
);

Notice the subject template: 'orders.{event_type}'. The relay will substitute {event_type} with the value from the message headers. Our messages have "event_type": "order.confirmed", so they'll be published to the NATS subject orders.order.confirmed.

This is powerful — different event types from the same outbox can be routed to different NATS subjects without any relay-side logic.

Step 7: Start the Relay

If you're using the Docker Compose setup, the relay is already running. Otherwise, start it manually:

pg-tide --postgres-url "postgres://postgres:postgres@localhost:5432/app"

You'll see log output like:

INFO  pg_tide_relay: Starting pg-tide relay v0.1.0
INFO  pg_tide_relay: Connected to PostgreSQL
INFO  pg_tide_relay: Discovered pipeline: orders-to-nats (forward, nats)
INFO  pg_tide_relay: Acquired advisory lock for pipeline: orders-to-nats
INFO  pg_tide_relay: Pipeline orders-to-nats: processing 3 pending messages
INFO  pg_tide_relay: Pipeline orders-to-nats: published batch [1..3] to nats

What's happening under the hood:

1. The relay connects to PostgreSQL and reads the pipeline catalog
2. It discovers orders-to-nats and attempts to acquire an advisory lock for it
3. It succeeds (it's the only relay instance), so it owns this pipeline
4. It polls tide.tide_outbox_messages for messages in the orders outbox where id > last_committed_offset
5. It delivers each message to the configured NATS subject
6. On success, it commits the offset and marks messages as consumed

Step 8: Verify Delivery

Subscribe to NATS to see the delivered messages (in another terminal):

# Using the nats CLI tool
nats sub "orders.>"

If messages were already delivered (relay started before you subscribed), you can verify from the PostgreSQL side:

-- Check that messages are now consumed
SELECT * FROM tide.outbox_pending;

 outbox_name | pending_count | oldest_at | max_id
-------------+---------------+-----------+--------
(0 rows)

No pending messages — they've all been delivered! Check consumer lag:

SELECT * FROM tide.consumer_lag;

 group_name | outbox_name | consumer_id | committed_offset | lag | last_heartbeat
------------+-------------+-------------+------------------+-----+--------------------
 nats-relay | orders      | relay-0     |                3 |   0 | 2025-01-15 10:02:00

Zero lag — the relay is fully caught up. The committed_offset of 3 means all messages through ID 3 have been delivered.

Step 9: Publish More Messages and Watch Them Flow

Now that the pipeline is running, new messages are delivered in near-real-time. In another terminal, subscribe to NATS:

nats sub "orders.>"

Then publish a new event in psql:

BEGIN;
  INSERT INTO orders (id, customer_id, total, status)
  VALUES (4, 'cust-diana', 75.50, 'confirmed');

  SELECT tide.outbox_publish('orders',
    '{"order_id": 4, "customer_id": "cust-diana", "total": 75.50, "status": "confirmed"}'::jsonb,
    '{"event_type": "order.confirmed"}'::jsonb
  );
COMMIT;

Within milliseconds, you'll see the message appear on the NATS subscription. The flow is:

1. COMMIT triggers pg_notify('tide_outbox_new', 'orders')
2. The relay receives the notification and immediately polls for new messages
3. Message ID 4 is fetched, delivered to NATS, and the offset is committed

Step 10: Monitor with Prometheus

The relay exposes Prometheus metrics at its metrics endpoint:

curl http://localhost:9090/metrics

Key metrics to watch:

# Total messages delivered
pg_tide_relay_messages_published_total{pipeline="orders-to-nats",direction="forward"} 4

# Error count (should be 0)
pg_tide_relay_publish_errors_total{pipeline="orders-to-nats",direction="forward"} 0

# Pipeline health
pg_tide_relay_pipeline_healthy{pipeline="orders-to-nats"} 1

And the health endpoint:

curl http://localhost:9090/health
# Returns 200 OK when all pipelines are healthy

What You've Built

In this tutorial, you've set up a complete transactional outbox pipeline:

1. The extension provides the schema, tables, and SQL API
2. The outbox stores events atomically with your business transactions
3. The consumer group tracks how far the relay has progressed
4. The pipeline configuration tells the relay where to deliver messages
5. The relay binary bridges the gap between PostgreSQL and NATS
6. Monitoring via Prometheus metrics and the consumer lag view

This is the foundational pattern. From here, you can:

• Add more pipelines to fan out events to multiple systems
• Create an inbox to receive events from external services
• Add more relay instances for high availability
• Configure different backends (Kafka, Redis, webhooks, etc.)

Next Steps

• Concepts: Message Guarantees → — understand exactly-once delivery in depth
• Concepts: Consumption and Relay → — deep dive into consumer groups and pipeline mechanics
• Relay Guide: Backends → — configure NATS, Kafka, Redis, and more
• Tutorial: End-to-End Pipeline → — build a forward + reverse pipeline with Kafka
• Operations: Deployment → — production deployment patterns

Concept: The Transactional Outbox Pattern

The transactional outbox pattern solves one of the most common problems in distributed systems: how to reliably update a database and publish an event at the same time. Without it, you face the "dual-write problem" — a situation where your database write succeeds but the message publish fails (or vice versa), leaving your system in an inconsistent state.

The Problem

Consider an e-commerce application that needs to save an order and notify the shipping service:

BEGIN;
  INSERT INTO orders (id, status) VALUES ('ORD-001', 'confirmed');
COMMIT;

-- Outside the transaction:
publish_to_kafka('order.confirmed', { order_id: 'ORD-001' });

What happens if the application crashes between the COMMIT and the publish? The order exists in the database, but the shipping service never learns about it. The event is lost.

What if you reverse the order — publish first, then commit? If the COMMIT fails (constraint violation, connection loss), the event was already published for an order that doesn't exist.

There is no safe ordering for two independent systems. This is the dual-write problem.

The Solution

The transactional outbox pattern writes the event to an "outbox" table in the same database transaction as the business data:

BEGIN;
  INSERT INTO orders (id, status) VALUES ('ORD-001', 'confirmed');
  SELECT tide.outbox_publish('order_events', 'orders', '{"order_id": "ORD-001", "status": "confirmed"}');
COMMIT;

Both writes are part of the same ACID transaction. Either both succeed or both fail. There is no inconsistency window.

A separate process (the relay) polls the outbox table and publishes events to external systems. If the relay crashes, it simply resumes from where it left off — the events are safely persisted in PostgreSQL.

Guarantees

The transactional outbox provides:

1. Atomicity — The business operation and event publication succeed or fail together
2. Durability — Events survive crashes (they're in PostgreSQL's WAL)
3. Ordering — Events from the same outbox are delivered in the order they were written
4. At-least-once delivery — Every committed event will eventually be delivered to the sink

How pg_tide Implements It

Application                  PostgreSQL                    Relay                    Sink
    │                            │                          │                       │
    │─── BEGIN ─────────────────→│                          │                       │
    │─── INSERT INTO orders ────→│                          │                       │
    │─── outbox_publish() ──────→│ (writes to outbox table) │                       │
    │─── COMMIT ────────────────→│                          │                       │
    │                            │                          │                       │
    │                            │←── poll outbox ──────────│                       │
    │                            │─── return rows ─────────→│                       │
    │                            │                          │─── publish ──────────→│
    │                            │                          │←── ack ───────────────│
    │                            │←── mark delivered ───────│                       │

The relay advances through the outbox table sequentially. After successful delivery, it advances its cursor. If it crashes and restarts, it re-reads from the last acknowledged position — potentially re-delivering a few messages (at-least-once), but never losing any.

When to Use This Pattern

Use the transactional outbox when:

• You need to update a database and publish an event reliably
• Consistency between your database and event stream matters
• You can't afford lost events (order notifications, payment confirmations, audit logs)
• You want to decouple your application from the messaging infrastructure

Comparison with Alternatives

Further Reading

• Architecture — How pg_tide implements the pattern
• Message Guarantees — Delivery semantics in detail
• Tutorial: Getting Started — Hands-on first pipeline

Concept: The Idempotent Inbox Pattern

The idempotent inbox is the receiving counterpart to the transactional outbox. While the outbox ensures events are reliably published, the inbox ensures events are reliably received and processed exactly once — even when the same message arrives multiple times due to network retries, relay restarts, or at-least-once delivery semantics.

The Problem

In distributed systems, at-least-once delivery is the norm. Messages can be delivered more than once due to:

• Network timeouts (sender retries after no ack)
• Consumer crashes (message re-delivered after acknowledgment timeout)
• Relay restarts (last batch re-delivered)
• Partition rebalances (offset not committed)

If your service processes the same payment event twice, you might charge the customer twice. If it processes the same order event twice, you might ship duplicate items.

The Solution

The inbox table stores every received message with a unique identifier (deduplication key). Before processing, it checks whether the message has already been seen:

-- The relay writes incoming messages to the inbox
-- Duplicate dedup_keys are silently ignored (idempotent)
INSERT INTO tide.inbox_events (dedup_key, event_type, payload)
VALUES ('evt-123', 'order.created', '{"order_id": "ORD-001"}')
ON CONFLICT (dedup_key) DO NOTHING;

If the same message arrives again (same dedup_key), the INSERT silently does nothing. The message is not processed a second time.

Processing Workflow

1. Message arrives from source (Kafka, NATS, webhook, etc.)
2. Relay writes to inbox (ON CONFLICT DO NOTHING)
3. Application queries inbox for pending messages
4. Application processes the message within a transaction
5. Application marks the message as processed

-- Step 3: Query pending messages
SELECT id, event_type, payload 
FROM tide.inbox_pending('payment_events') 
LIMIT 10;

-- Step 4-5: Process within transaction
BEGIN;
  -- Your business logic here
  INSERT INTO payments (order_id, amount, status) 
  VALUES ('ORD-001', 149.99, 'captured');
  
  -- Mark as processed (atomically with business logic)
  SELECT tide.inbox_mark_processed('payment_events', 42);
COMMIT;

Because the mark-processed call is inside the same transaction as the business logic, either both succeed or both fail. If the transaction rolls back, the message remains pending and will be retried.

Deduplication Keys

The dedup key uniquely identifies a message. Common strategies:

pg_tide extracts the dedup key from the message based on the wire format configuration. For native format, it uses the message key. For Debezium, it uses the record key.

Failure Handling

If processing a message fails (business logic error, constraint violation), you have two options:

Retry later:

-- Leave as pending, it will be retried on next poll
-- Optionally record the error for monitoring
SELECT tide.inbox_mark_failed('payment_events', 42, 'Insufficient funds');

Skip permanently:

-- Mark as processed to advance past it
SELECT tide.inbox_mark_processed('payment_events', 42);

Exactly-Once Processing

The inbox provides exactly-once processing (not delivery) through this mechanism:

1. At-least-once delivery: The source may deliver the same message multiple times
2. Deduplication on write: The inbox's unique constraint prevents duplicate storage
3. Atomic processing: Business logic + mark-processed in one transaction

The combination ensures each unique message is processed exactly once, regardless of how many times it's delivered.

When to Use the Inbox

Use the inbox when:

• You receive events from external systems that may duplicate
• Processing has side effects (charging money, sending emails, updating state)
• You need a reliable buffer between message receipt and processing
• You want to decouple message consumption from message processing

Further Reading

• Transactional Outbox — The publishing counterpart
• SQL Reference: Inbox API — Complete function reference
• Message Guarantees — Delivery semantics

Concept: Consumer Groups

Consumer groups allow multiple independent consumers to process messages from the same outbox, each maintaining its own position. This enables fan-out patterns where a single stream of events is consumed by different services at different speeds, without them interfering with each other.

The Problem

Without consumer groups, a single outbox has a single "cursor" — one position tracking which messages have been delivered. If you want two services to receive the same events (say, an analytics service and a notification service), you'd need to create two separate outboxes and publish events to both.

The Solution

Consumer groups give each consumer its own independent position within the same outbox:

Outbox: order_events
┌───┬───┬───┬───┬───┬───┬───┬───┬───┬───┐
│ 1 │ 2 │ 3 │ 4 │ 5 │ 6 │ 7 │ 8 │ 9 │10 │
└───┴───┴───┴───┴───┴───┴───┴───┴───┴───┘
                    ↑               ↑
          Analytics group      Notifications group
          (position: 4)        (position: 8)

The analytics service processes messages slowly (heavy aggregation), while the notification service processes them quickly. Each advances independently.

Creating Consumer Groups

-- Create an outbox
SELECT tide.outbox_create('order_events');

-- Create consumer groups for different services
SELECT tide.consumer_group_create('order_events', 'analytics');
SELECT tide.consumer_group_create('order_events', 'notifications');
SELECT tide.consumer_group_create('order_events', 'search-indexer');

Configuring Pipelines per Group

Each consumer group gets its own relay pipeline:

-- Analytics: sends to data warehouse (slow, large batches)
SELECT tide.relay_set_outbox(
    'orders-analytics',
    'order_events',
    '{
        "sink_type": "bigquery",
        "consumer_group": "analytics",
        "batch_size": 1000,
        "dataset": "raw_events",
        "table": "orders"
    }'::jsonb
);

-- Notifications: sends to Slack (fast, small batches)
SELECT tide.relay_set_outbox(
    'orders-notifications',
    'order_events',
    '{
        "sink_type": "slack",
        "consumer_group": "notifications",
        "batch_size": 1,
        "webhook_url": "${env:SLACK_WEBHOOK}"
    }'::jsonb
);

-- Search: sends to Elasticsearch (medium speed)
SELECT tide.relay_set_outbox(
    'orders-search',
    'order_events',
    '{
        "sink_type": "elasticsearch",
        "consumer_group": "search-indexer",
        "batch_size": 100,
        "url": "http://elasticsearch:9200",
        "index": "orders"
    }'::jsonb
);

Independent Processing

Each consumer group:

• Tracks its own position (last delivered outbox ID)
• Advances at its own pace
• Has its own circuit breaker state
• Can have different transforms, routing, and rate limits
• Can target different sinks

If the analytics pipeline falls behind (BigQuery is slow), notifications and search indexing continue unaffected.

Checking Group Status

-- See position and lag for each consumer group
SELECT * FROM tide.consumer_group_status('order_events');

Returns:

Adding a New Consumer Group

When you add a new consumer group, you choose where it starts:

-- Start from the beginning (process all historical events)
SELECT tide.consumer_group_create('order_events', 'new-service', 0);

-- Start from the current position (only future events)
SELECT tide.consumer_group_create('order_events', 'new-service');

Use Cases

• Fan-out: Same events go to Kafka, Elasticsearch, and a data lake
• Selective processing: Each group applies different filters/transforms
• Speed isolation: Fast consumers aren't blocked by slow ones
• A/B testing: Two groups process the same events with different logic
• Migration: New consumer group processes alongside old one during transition

Further Reading

• SQL Reference: Consumer Groups API — Complete function reference
• Fan-Out Pattern — Tutorial with multiple consumers
• Notification Fan-Out — Multi-channel notification example

Message Guarantees

pg_tide provides end-to-end exactly-once delivery semantics by combining three mechanisms that work together as a unified system: the transactional outbox ensures no messages are lost at the source, the relay delivers them reliably to downstream systems, and the idempotent inbox catches any duplicates at the destination. This page explains all three mechanisms in depth, how they interact, and what guarantees you can rely on in production.

The Fundamental Problem: Dual Writes

Imagine you're building an e-commerce platform. When a customer places an order, your application needs to do two things: save the order to your PostgreSQL database, and notify the warehouse service that a new order is ready to ship. The naive approach looks straightforward:

Application ──INSERT──▶ PostgreSQL  ✓  (order saved)
            ──publish──▶ Kafka      ✗  (network timeout!)

The database has the order. Kafka does not. The warehouse never learns about the order. The customer waits indefinitely for a shipment that nobody knows to send.

This is the dual-write problem. Any time your application writes to two separate systems — a database and a message broker — there's a window where one write can succeed and the other can fail. No amount of application-level retry logic can fully close this window, because your application itself might crash between the two writes.

The consequences are severe and insidious:

• Silent data loss — downstream consumers never see the event
• Inconsistent state — the database says one thing, the event stream says another
• Difficult detection — unless you actively reconcile both systems, you won't know events were lost
• Impossible recovery — once the transaction is committed without the event, you can't retroactively publish it without complex compensating logic

Why retry logic isn't enough

You might think: "I'll just retry the Kafka publish until it succeeds." But consider what happens if the publish succeeds, then your application crashes before recording that success. On restart, it retries — and now the event is published twice. You've traded message loss for message duplication.

What about the reverse order — publish first, then commit? If the database commit fails after a successful publish, you've sent an event about something that never happened.

There is no safe ordering of two independent writes that guarantees exactly-once semantics. The only solution is to eliminate the dual write entirely.

The Solution: The Transactional Outbox Pattern

The transactional outbox pattern eliminates dual writes by reducing two writes to one. Instead of writing to your database and a message broker, you write to your database only — and the message goes into a special outbox table within the same transaction as your business data:

BEGIN;
  -- Your business logic: save the order
  INSERT INTO orders (id, customer_id, total, status)
  VALUES (42, 'cust-123', 99.99, 'confirmed');

  -- Event publishing: same transaction, same database
  SELECT tide.outbox_publish('orders',
    '{"order_id": 42, "customer_id": "cust-123", "total": 99.99, "status": "confirmed"}'::jsonb,
    '{"event_type": "order.confirmed", "correlation_id": "req-abc-789"}'::jsonb
  );
COMMIT;

Both the order insert and the message insert succeed or fail together — they're part of the same PostgreSQL transaction. There is no window where one succeeds without the other. If the transaction commits, the message is guaranteed to exist. If it rolls back (for any reason — constraint violation, application crash, network disconnect), the message disappears along with the business data.

A separate relay process then reads committed messages from the outbox table and delivers them to whatever downstream system you've configured (Kafka, NATS, webhooks, etc.). The relay runs independently of your application and can retry indefinitely — the message is safely persisted in PostgreSQL until delivery succeeds.

This separation of concerns gives you the best of both worlds:

• Transactional safety — your application only writes to one system
• Guaranteed delivery — the relay keeps trying until the downstream system acknowledges
• Decoupled systems — your application doesn't need to know about broker availability
• Simple application code — publishing an event is just a SQL function call

How pg_tide implements the outbox

When you call tide.outbox_publish(name, payload, headers), pg_tide:

1. Inserts a row into tide.tide_outbox_messages with your payload and headers
2. Fires pg_notify ('tide_outbox_new', outbox name) to wake the relay immediately

The outbox messages table stores all messages from all named outboxes in a single table, discriminated by outbox_name:

The auto-incrementing id column is crucial: it provides a total ordering of messages within an outbox, which the relay uses to guarantee in-order delivery and to track its position.

The relay loop

The pg-tide relay binary continuously:

1. Polls for pending messages (WHERE consumed_at IS NULL AND id > last_committed_offset)
2. Delivers each batch to the configured sink (NATS, Kafka, Redis, webhooks, etc.)
3. Commits the offset — records how far it's read, so it can resume from this position after a restart
4. Marks messages consumed — sets consumed_at so they're excluded from future polls
5. Respects retention — messages older than retention_hours are eligible for cleanup

If the relay crashes at any point in this loop, it restarts from the last committed offset and re-delivers any messages that weren't confirmed. This is why the relay provides at-least-once delivery — it never skips a message, but it might deliver one twice.

Retention and cleanup

Each outbox has a configurable retention window. After messages have been consumed and their retention period has elapsed, they can be cleaned up to prevent unbounded table growth:

-- Create an outbox with 48-hour retention
SELECT tide.outbox_create('orders', p_retention_hours := 48);

The inline_threshold parameter provides backpressure: if the number of pending (unconsumed) messages exceeds this threshold, subsequent publishes will pause, preventing your outbox from growing unboundedly if the relay is down.

The Idempotent Inbox: Catching Duplicates at the Destination

The transactional outbox guarantees that every committed event will be delivered at least once. But "at least once" means duplicates are possible. Consider this scenario:

1. The relay polls the outbox and gets message #42
2. The relay delivers message #42 to the downstream system — success
3. The relay crashes before committing offset 42
4. The relay restarts, reads from its last committed offset (41), and delivers message #42 again

Without protection at the receiving end, the downstream system processes the same event twice. For an "order confirmed" event, this might trigger two shipments. For a "payment processed" event, it might charge the customer twice.

The idempotent inbox solves this. It's a PostgreSQL table with a UNIQUE constraint on an event identifier. When a message arrives:

1. The relay attempts an INSERT with the event's dedup key as the event_id
2. If the key already exists (duplicate delivery), the insert is silently skipped via ON CONFLICT DO NOTHING
3. Your application only sees each event once, regardless of how many times it was delivered

How the inbox works in practice

Each named inbox gets its own message table with this structure:

CREATE TABLE tide."payment-events_inbox" (
    id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_id       TEXT NOT NULL,
    source         TEXT,
    payload        JSONB,
    headers        JSONB,
    received_at    TIMESTAMPTZ DEFAULT now(),
    processed_at   TIMESTAMPTZ,
    retry_count    INT DEFAULT 0,
    last_error     TEXT,
    CONSTRAINT uq_payment_events_event_id UNIQUE (event_id)
);

The UNIQUE(event_id) constraint is the deduplication mechanism. It's simple, reliable, and leverages PostgreSQL's proven concurrency guarantees.

Creating an inbox

SELECT tide.inbox_create('payment-events',
  p_max_retries := 5,
  p_processed_retention_hours := 72,
  p_dlq_retention_hours := 168
);

Processing inbox messages

Your application reads from the inbox table and marks messages as processed after handling them:

-- Read the next batch of pending messages
SELECT id, event_id, payload, headers
FROM tide."payment-events_inbox"
WHERE processed_at IS NULL
  AND retry_count < 5
ORDER BY id
LIMIT 10;

-- After successfully processing a message
SELECT tide.inbox_mark_processed('payment-events', 'evt-001');

-- If processing fails (e.g., external API timeout)
SELECT tide.inbox_mark_failed('payment-events', 'evt-003',
  'Stripe API timeout after 30s');

When you call inbox_mark_failed, the retry_count is incremented and the last_error is recorded. Your application can retry later. After max_retries failures, the message is effectively in the dead-letter queue — it won't be picked up by normal processing loops.

The dead-letter queue

Messages that exhaust their retry budget aren't deleted — they remain in the inbox table for investigation. You can query them, examine the error history, and replay them once you've fixed the underlying issue:

-- Find all dead-letter messages
SELECT event_id, payload, last_error, retry_count
FROM tide."payment-events_inbox"
WHERE processed_at IS NULL
  AND retry_count >= 5;

-- Replay specific messages after fixing the issue
SELECT tide.replay_inbox_messages('payment-events',
  ARRAY['evt-003', 'evt-007', 'evt-012']);

Replaying resets the retry_count to zero, making the messages eligible for processing again.

Choosing dedup keys

The event_id should be deterministic and unique per logical event. The goal is that the same logical event always produces the same dedup key, regardless of how many times it's delivered:

The relay automatically generates appropriate dedup keys based on the source type when operating in reverse mode (external source → inbox).

End-to-End Exactly-Once: The Three Pillars Combined

When you combine the transactional outbox, the relay's offset tracking, and the idempotent inbox, you get effectively exactly-once delivery semantics end-to-end. Here's how the complete flow works:

1. Application:  BEGIN; INSERT business_data; outbox_publish(); COMMIT;
       ↓
2. Relay:        Polls outbox → gets messages where id > last_committed_offset
       ↓
3. Relay:        Delivers to sink (e.g., INSERT into inbox with dedup key)
       ↓
4. Relay:        On sink acknowledgment → commit_offset(last_delivered_id)
       ↓
5. Relay:        Marks outbox messages as consumed

Each stage is protected:

Edge cases handled

Relay crash after delivery, before offset commit: This is the most important edge case. The relay successfully delivered message #42 to the inbox, then crashed before recording offset 42. On restart, it re-delivers #42. The inbox's UNIQUE constraint on event_id catches the duplicate, and the insert is silently skipped. The application sees message #42 exactly once.

PostgreSQL failover: If the primary PostgreSQL instance fails over to a replica, the relay's advisory locks are automatically released (they're tied to the session). Another relay instance can acquire the locks and resume from the last committed offset. In-flight messages that weren't committed are re-delivered, and the inbox dedup catches any duplicates.

Sink temporarily unavailable: The relay retries with exponential backoff (100ms → 30s with jitter). Messages remain pending in the outbox — they're never lost. Once the sink recovers, delivery resumes automatically.

Duplicate outbox_publish calls: If your application accidentally publishes the same logical event twice (due to a retry at the application level), you can include a deterministic event_id in the headers. The inbox dedup key will catch duplicates at the receiving end. Alternatively, design your consumers to be naturally idempotent.

Guarantees summary

Limitations and honest caveats

pg_tide's exactly-once guarantee is strong, but it's important to understand the boundaries:

• Cross-sink atomicity: If you configure a single outbox to fan out to multiple sinks (e.g., Kafka and a webhook), and one delivery succeeds while the other fails, you'll have partial delivery. Use separate pipelines per sink for independent exactly-once guarantees per destination.

• External sink semantics: Exactly-once delivery into pg_tide inboxes is guaranteed because the inbox uses PostgreSQL's UNIQUE constraint. For external sinks (Kafka, NATS, Redis), the guarantee depends on the sink's acknowledgment semantics. If a sink acknowledges delivery but then loses the message internally, pg_tide cannot detect that. Choose sinks with strong durability guarantees for critical workloads.

• Clock skew and retention: Retention cleanup uses created_at timestamps. Extreme clock skew between PostgreSQL nodes could cause premature cleanup of messages that haven't been consumed yet. Always use NTP-synchronized hosts.

• "Effectively" vs. "truly" exactly-once: In distributed systems theory, true exactly-once delivery across system boundaries is provably impossible without two-phase commit. pg_tide achieves effectively exactly-once by combining at-least-once delivery with idempotent reception — the outcome is the same (each event is processed exactly once), but the mechanism involves potential redelivery that's silently deduplicated.

Comparison with Other Approaches

To understand why the transactional outbox pattern is valuable, it helps to see how it compares with alternatives:

Two-Phase Commit (2PC)

2PC coordinates writes across multiple systems using a prepare/commit protocol. It provides true atomicity but at severe cost: high latency, reduced availability (any participant failure blocks the entire transaction), and complexity. pg_tide avoids 2PC entirely — you write to one system, and the relay handles the rest asynchronously.

Change Data Capture (CDC) via Debezium

Debezium captures row-level changes from PostgreSQL's WAL (write-ahead log) and publishes them to Kafka. It doesn't require application changes, but you lose control over event format (events mirror table schemas, not business semantics) and require significant infrastructure (Kafka Connect, Kafka cluster, JVM). pg_tide gives you explicit control over what you publish.

Application-Level Retry with Compensation

Some systems retry failed broker publishes and compensate for duplicates on the consumer side. This "best effort" approach is fragile: it requires every consumer to implement idempotency, provides no centralized dedup mechanism, and becomes increasingly complex as the number of consumers grows. pg_tide centralizes deduplication in the inbox.

Direct Broker Writes (Accept the Risk)

For non-critical events (telemetry, analytics pings, real-time notifications), some teams accept the dual-write risk and publish directly to a broker. This is valid when message loss is acceptable. pg_tide is for when it isn't.

Practical Patterns

Publishing multiple events in one transaction

You can publish multiple events atomically:

BEGIN;
  UPDATE orders SET status = 'shipped' WHERE id = 42;
  UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 'SKU-001';

  -- Both events are published atomically
  SELECT tide.outbox_publish('orders',
    '{"order_id": 42, "status": "shipped"}'::jsonb,
    '{"event_type": "order.shipped"}'::jsonb
  );

  SELECT tide.outbox_publish('inventory',
    '{"product_id": "SKU-001", "quantity_change": -1}'::jsonb,
    '{"event_type": "inventory.decremented"}'::jsonb
  );
COMMIT;

Conditional publishing

Only publish when certain conditions are met:

BEGIN;
  UPDATE orders SET status = 'confirmed'
  WHERE id = 42 AND status = 'pending'
  RETURNING id INTO affected_id;

  -- Only publish if the update actually changed something
  IF affected_id IS NOT NULL THEN
    PERFORM tide.outbox_publish('orders',
      format('{"order_id": %s, "status": "confirmed"}', affected_id)::jsonb,
      '{"event_type": "order.confirmed"}'::jsonb
    );
  END IF;
COMMIT;

Including correlation IDs for tracing

Pass request or trace IDs through the headers so downstream systems can correlate events:

SELECT tide.outbox_publish('orders',
  '{"order_id": 42}'::jsonb,
  jsonb_build_object(
    'event_type', 'order.created',
    'correlation_id', 'req-abc-123',
    'trace_id', 'trace-xyz-456',
    'schema_version', '1.0'
  )
);

Consumption and Relay

Once messages are safely stored in the transactional outbox, they need to be delivered to downstream systems and tracked independently by each consumer. This page explains how pg_tide's consumer groups and relay pipelines work together to provide reliable, independent message consumption with automatic failover.

Consumer Groups: Independent Bookmarks in a Shared Stream

Think of a consumer group as a bookmark in a shared book. Multiple services might be interested in the same stream of outbox messages — one service sends emails, another updates a search index, a third feeds an analytics pipeline. Each of these services needs to track its own progress independently. That's exactly what a consumer group provides: an independent offset that records how far a particular consumer has read through the outbox.

If the email service crashes and restarts, it picks up right where it left off — at its own bookmark — without replaying messages that the analytics service has already processed, and without skipping messages that it hasn't yet seen.

Core concepts

A consumer group has these key properties:

• Independent progress — different groups read the same outbox at their own pace. The email sender might be at offset 500 while the analytics pipeline is at offset 2000. They don't interfere with each other.
• Offset tracking — each group records the ID of the last message it successfully processed. On restart, consumption resumes from that exact position.
• Heartbeats — consumers periodically signal that they're alive. Stale heartbeats indicate a dead consumer whose work might need to be redistributed.
• Visibility leases — when a consumer claims a batch of messages, it takes a time-limited lease. If it fails to commit the offset before the lease expires, the messages become available for another consumer to process.

Creating a consumer group

SELECT tide.create_consumer_group('email-sender', 'orders',
  p_auto_offset_reset := 'earliest'
);

The p_auto_offset_reset parameter determines where consumption starts if no offset has been committed yet:

Committing offsets

After your application (or the relay) successfully processes a batch of messages, it commits the offset to record its progress:

SELECT tide.commit_offset('email-sender', 'worker-1', 42);

This records that worker-1 in the email-sender group has processed all messages up to and including ID 42. On restart, this consumer will resume from message 43.

Offset commits are idempotent — committing the same offset twice is a no-op. They're also monotonic within a consumer — you should only ever commit a higher offset than the previous one.

Heartbeats and liveness

Consumers periodically send heartbeats to signal that they're alive and actively processing:

SELECT tide.consumer_heartbeat('email-sender', 'worker-1');

Heartbeats serve two purposes:

1. Monitoring — you can detect stale consumers by checking last_heartbeat against a threshold
2. Lease management — future versions of pg_tide may automatically reassign work from consumers that haven't heartbeated recently

The tide.consumer_lag view shows the current state of each consumer:

SELECT * FROM tide.consumer_lag;

 group_name      | outbox_name | consumer_id | committed_offset | lag  | last_heartbeat
-----------------+-------------+-------------+------------------+------+--------------------
 email-sender    | orders      | worker-1    |              500 | 1500 | 2025-01-15 10:30:00
 analytics       | orders      | relay-0     |             1800 |  200 | 2025-01-15 10:31:00
 search-indexer  | orders      | relay-0     |             2000 |    0 | 2025-01-15 10:31:02

In this example, the email sender is 1,500 messages behind the latest — it might be slow or stuck. The search indexer is fully caught up.

Visibility leases

Visibility leases prevent two consumers from processing the same batch of messages simultaneously. When a consumer claims a batch:

1. A lease is recorded in tide.tide_consumer_leases with a start ID, end ID, and expiry time
2. Other consumers in the same group won't see those messages until the lease expires
3. When the consumer commits the offset, the lease is released
4. If the consumer crashes without committing, the lease eventually expires and the messages become available again

This mechanism is similar to SQS's visibility timeout or Kafka's partition assignment — it provides at-most-once assignment of work within a consumer group.

Multiple groups, one outbox

The most powerful aspect of consumer groups is that a single outbox can serve many independent purposes:

-- The relay delivers to NATS for real-time notifications
SELECT tide.create_consumer_group('nats-relay', 'orders');

-- An analytics service reads the same events for data warehouse loading
SELECT tide.create_consumer_group('analytics-etl', 'orders');

-- An audit logger persists every event to compliance storage
SELECT tide.create_consumer_group('audit-log', 'orders');

-- A search indexer updates Elasticsearch
SELECT tide.create_consumer_group('search-index', 'orders');

Each group progresses independently. The NATS relay might be at offset 5000 while the analytics ETL is catching up at offset 3000. They don't interfere with each other, and the outbox doesn't need to know about any of them.

Consumer group lifecycle

-- Create a group
SELECT tide.create_consumer_group('my-group', 'events');

-- Drop a group (cascades: removes all offsets and leases)
SELECT tide.drop_consumer_group('my-group');

-- Idempotent creation (no error if already exists)
SELECT tide.create_consumer_group('my-group', 'events',
  p_if_not_exists := true);

Relay Pipelines: Bridging PostgreSQL to the Outside World

A relay pipeline defines how messages flow between pg_tide and external systems. While consumer groups track position, pipelines define destination — where should messages actually go?

Pipelines are configured directly in the database (not in config files) and discovered by the relay binary at runtime. This means you can create, modify, and delete pipelines entirely through SQL, and the relay picks up changes automatically via PostgreSQL's LISTEN/NOTIFY mechanism.

Two directions of flow

pg_tide supports two pipeline directions:

Forward pipelines (Outbox → External Sink): Messages flow from a pg_tide outbox to an external system. This is the most common pattern — your application publishes events to the outbox, and the relay delivers them to NATS, Kafka, Redis, webhooks, or any other configured sink.

┌──────────────┐         ┌──────────────┐         ┌──────────────┐
│  PostgreSQL  │         │  pg-tide     │         │  External    │
│  outbox      │────────▶│  relay       │────────▶│  system      │
│              │  poll   │              │ publish │  (NATS, etc) │
└──────────────┘         └──────────────┘         └──────────────┘

Reverse pipelines (External Source → Inbox): Messages flow from an external system into a pg_tide inbox. This is for receiving events from other services — the relay subscribes to an external source and writes incoming messages to an inbox table with deduplication.

┌──────────────┐         ┌──────────────┐         ┌──────────────┐
│  External    │         │  pg-tide     │         │  PostgreSQL  │
│  system      │────────▶│  relay       │────────▶│  inbox       │
│  (NATS, etc) │ subscribe│             │  insert │              │
└──────────────┘         └──────────────┘         └──────────────┘

Configuring a forward pipeline

Forward pipelines connect an outbox to an external sink:

SELECT tide.relay_set_outbox(
  'orders-to-kafka',     -- pipeline name (must be unique)
  'orders',              -- source outbox name
  'kafka',               -- sink type
  jsonb_build_object(    -- sink-specific configuration
    'brokers', 'broker1:9092,broker2:9092',
    'topic', 'order-events',
    'acks', 'all',
    'compression', 'snappy'
  ),
  p_batch_size := 200,   -- deliver messages in batches of 200
  p_enabled := true      -- start processing immediately
);

The config parameter is a JSONB object whose keys depend on the sink type. Each backend (NATS, Kafka, Redis, RabbitMQ, SQS, Webhook) has its own set of configuration options — see the Backends page for complete details.

Configuring a reverse pipeline

Reverse pipelines connect an external source to an inbox:

SELECT tide.relay_set_inbox(
  'stripe-webhooks',       -- pipeline name
  'payment-events',        -- target inbox name
  jsonb_build_object(      -- source-specific configuration
    'port', 8080,
    'path', '/webhooks/stripe',
    'auth_header', 'Bearer whsec_abc123'
  ),
  p_source := 'webhook',  -- source type
  p_batch_size := 50,
  p_idempotent := true     -- enable dedup key extraction
);

Pipeline lifecycle management

Pipelines support a full lifecycle through SQL:

-- Pause processing (messages accumulate in the outbox)
SELECT tide.relay_disable('orders-to-kafka');

-- Resume processing
SELECT tide.relay_enable('orders-to-kafka');

-- Delete permanently (removes config and stops processing)
SELECT tide.relay_delete('orders-to-kafka');

-- View a pipeline's current configuration
SELECT tide.relay_get_config('orders-to-kafka');

-- List all configured pipelines
SELECT tide.relay_list_configs();

Hot reload: no restart required

When you create, update, or delete a pipeline configuration, pg_tide fires a PostgreSQL notification:

pg_notify('tide_relay_config', '{"direction": "relay_outbox_config", "op": "INSERT", "name": "orders-to-kafka"}')

The relay binary listens for these notifications via LISTEN tide_relay_config. When a notification arrives, the relay:

1. Re-reads the pipeline catalog from the database
2. Starts any new pipelines
3. Stops any deleted pipelines
4. Reconfigures any modified pipelines

This means you can manage your entire pipeline lifecycle from SQL — no relay restarts, no config file deployments, no downtime. Add a new pipeline, and it starts processing within seconds.

Advisory lock coordination: automatic failover

In production, you typically run multiple relay instances for high availability. But if two relays tried to process the same pipeline simultaneously, you'd get duplicate deliveries. pg_tide prevents this using PostgreSQL advisory locks.

Each pipeline is protected by a unique advisory lock. When a relay instance starts up:

1. It reads the pipeline catalog
2. For each pipeline, it attempts to acquire an advisory lock (non-blocking)
3. If it gets the lock, it owns that pipeline and begins processing
4. If another instance already holds the lock, it skips that pipeline and moves on

This gives you:

• Automatic failover — if a relay dies, its PostgreSQL session ends, the advisory locks are released, and another instance acquires them within seconds
• No duplicate processing — only one relay processes each pipeline at any given time
• Horizontal distribution — with many pipelines and many relay instances, pipelines are naturally distributed across instances

# Instance A — might own pipelines 1, 3, 5
pg-tide --relay-group-id production --postgres-url ...

# Instance B — might own pipelines 2, 4, 6
pg-tide --relay-group-id production --postgres-url ...

# If A crashes, B acquires pipelines 1, 3, 5 within seconds

The relay group ID

The relay_group_id namespaces advisory locks. Relay instances with the same group ID compete for pipeline ownership — this is how HA failover works. Instances with different group IDs operate independently and can theoretically own the same pipeline simultaneously (though this would cause duplicate delivery and is rarely desirable).

# Production HA pair — same group, automatic failover between them
pg-tide --relay-group-id production --postgres-url ...
pg-tide --relay-group-id production --postgres-url ...

# Separate staging environment — different group, isolated
pg-tide --relay-group-id staging --postgres-url ...

Supported backends

Putting it all together

Here's a typical production setup with multiple pipelines serving different purposes:

-- Forward: order events go to NATS for real-time microservice communication
SELECT tide.relay_set_outbox('orders-realtime', 'orders', 'nats',
  jsonb_build_object(
    'url', 'nats://nats-cluster:4222',
    'subject', 'orders.{event_type}'
  )
);

-- Forward: order events also go to Kafka for long-term analytics
SELECT tide.relay_set_outbox('orders-analytics', 'orders', 'kafka',
  jsonb_build_object(
    'brokers', 'kafka:9092',
    'topic', 'orders-analytics',
    'compression', 'zstd'
  ),
  p_batch_size := 500
);

-- Reverse: incoming payment confirmations from a third-party webhook
SELECT tide.relay_set_inbox('payment-webhooks', 'payments',
  jsonb_build_object(
    'port', 8080,
    'path', '/webhooks/payments'
  ),
  p_source := 'webhook'
);

-- Forward: payment confirmations forwarded to an internal NATS subject
SELECT tide.relay_set_outbox('payments-internal', 'payment-notifications', 'nats',
  jsonb_build_object(
    'url', 'nats://nats-cluster:4222',
    'subject', 'payments.confirmed'
  )
);

Each pipeline operates independently with its own offset tracking, retry logic, and advisory lock. The relay binary handles all of them concurrently.

Outbox API

All outbox functions live in the tide schema.

tide.outbox_create

Create a new named outbox.

SELECT tide.outbox_create(
  p_name              TEXT,
  p_retention_hours   INT  DEFAULT 24,
  p_inline_threshold  INT  DEFAULT 10000
);

Errors:

• Raises an error if an outbox with the same name already exists.

Example:

SELECT tide.outbox_create('order-events', 48, 50000);

tide.outbox_publish

Publish a message to a named outbox. Runs within the caller's transaction.

SELECT tide.outbox_publish(
  p_name     TEXT,
  p_payload  JSONB,
  p_headers  JSONB
);

Behavior:

• Inserts into tide.tide_outbox_messages
• Fires pg_notify('tide_outbox_new', p_name) to wake the relay
• Errors if the outbox does not exist or is disabled

Example:

BEGIN;
  INSERT INTO orders (id, total) VALUES (42, 99.99);
  SELECT tide.outbox_publish('order-events',
    '{"order_id": 42, "total": 99.99}'::jsonb,
    '{"event_type": "order.created"}'::jsonb
  );
COMMIT;

tide.outbox_drop

Drop a named outbox and all its messages.

SELECT tide.outbox_drop(
  p_name       TEXT,
  p_if_exists  BOOLEAN DEFAULT false
);

Cascades: Removes all messages and consumer groups for this outbox.

tide.outbox_status

Get a status summary for a named outbox.

SELECT tide.outbox_status(p_name TEXT) → JSONB

Returns:

{
  "outbox_name": "orders",
  "pending_messages": 42,
  "total_messages": 1500,
  "oldest_pending_age_seconds": 3.7,
  "retention_hours": 24
}

tide.outbox_disable

Pause an outbox. Calls to outbox_publish will error while the outbox is disabled.

SELECT tide.outbox_disable(p_name TEXT);

tide.outbox_enable

Resume a previously disabled outbox.

SELECT tide.outbox_enable(p_name TEXT);

Views

tide.outbox_pending

Pending (unconsumed) messages per outbox:

SELECT * FROM tide.outbox_pending;

Inbox API

All inbox functions live in the tide schema.

tide.inbox_create

Create a named inbox with its message table.

SELECT tide.inbox_create(
  p_name                      TEXT,
  p_schema                    TEXT DEFAULT 'tide',
  p_max_retries               INT  DEFAULT 3,
  p_processed_retention_hours INT  DEFAULT 72,
  p_dlq_retention_hours       INT  DEFAULT 0
);

Creates: A table {schema}."{name}_inbox" with columns for dedup, retry tracking, and payload storage.

Example:

SELECT tide.inbox_create('payment-webhooks',
  p_max_retries := 5,
  p_processed_retention_hours := 168
);

tide.inbox_drop

Drop a named inbox and its message table.

SELECT tide.inbox_drop(
  p_name       TEXT,
  p_if_exists  BOOLEAN DEFAULT false
);

Cascades: Drops the inbox table and removes the config entry.

tide.inbox_mark_processed

Mark an inbox message as successfully processed.

SELECT tide.inbox_mark_processed(
  p_name      TEXT,
  p_event_id  TEXT
);

Sets processed_at = now() on the matching row. Idempotent — calling it on an already-processed message is a no-op.

tide.inbox_mark_failed

Mark an inbox message as failed. Increments retry_count and stores the error.

SELECT tide.inbox_mark_failed(
  p_name      TEXT,
  p_event_id  TEXT,
  p_error     TEXT
);

tide.inbox_status

Get status summary for an inbox (or all inboxes).

-- Single inbox
SELECT tide.inbox_status('payment-webhooks') → JSONB

-- All inboxes
SELECT tide.inbox_status() → JSONB

Returns (single inbox):

{
  "inbox_name": "payment-webhooks",
  "pending": 3,
  "dlq_count": 1
}

tide.replay_inbox_messages

Re-queue failed messages for reprocessing. Resets retry_count to 0 and clears last_error.

SELECT tide.replay_inbox_messages(
  p_name       TEXT,
  p_event_ids  TEXT[]
) → BIGINT

Returns: Number of messages successfully re-queued.

Example:

SELECT tide.replay_inbox_messages('payment-webhooks',
  ARRAY['evt-003', 'evt-007', 'evt-015']
);

Inbox Table Schema

Each inbox gets a table {schema}."{name}_inbox" with this structure:

Relay API

Functions for managing relay pipeline configurations. All live in the tide schema.

tide.relay_set_outbox

Configure a forward relay pipeline (outbox → external sink).

SELECT tide.relay_set_outbox(
  p_name        TEXT,
  p_outbox      TEXT,
  p_sink        TEXT,
  p_config      JSONB    DEFAULT '{}'::jsonb,
  p_batch_size  INT      DEFAULT 100,
  p_enabled     BOOLEAN  DEFAULT true
);

Upsert behavior: If a pipeline with the same name exists, its configuration is updated.

Example:

SELECT tide.relay_set_outbox('orders-to-nats', 'orders', 'nats',
  jsonb_build_object(
    'url', 'nats://localhost:4222',
    'subject', 'orders.{event_type}'
  ),
  p_batch_size := 200
);

tide.relay_set_inbox

Configure a reverse relay pipeline (external source → inbox).

SELECT tide.relay_set_inbox(
  p_name         TEXT,
  p_inbox        TEXT,
  p_config       JSONB    DEFAULT '{}'::jsonb,
  p_batch_size   INT      DEFAULT 100,
  p_source       TEXT     DEFAULT 'stdout',
  p_enabled      BOOLEAN  DEFAULT true,
  p_max_retries  INT      DEFAULT 3,
  p_idempotent   BOOLEAN  DEFAULT true
);

tide.relay_enable

Enable a previously disabled pipeline.

SELECT tide.relay_enable(p_name TEXT);

Fires pg_notify('tide_relay_config', name) to trigger hot-reload in the relay.

tide.relay_disable

Disable a pipeline (stops processing without deleting config).

SELECT tide.relay_disable(p_name TEXT);

tide.relay_delete

Permanently delete a pipeline configuration.

SELECT tide.relay_delete(p_name TEXT);

tide.relay_get_config

Retrieve the full configuration for a pipeline.

SELECT tide.relay_get_config(p_name TEXT) → JSONB

Returns the stored config JSONB for the named pipeline.

tide.relay_list_configs

List all configured relay pipelines.

SELECT tide.relay_list_configs() → JSONB

Returns a JSON array of all pipelines with their direction and enabled status:

[
  {"name": "orders-to-nats", "direction": "outbox", "enabled": true},
  {"name": "webhooks-in", "direction": "inbox", "enabled": true}
]

Consumer Groups API

Functions for managing consumer groups. All live in the tide schema.

tide.create_consumer_group

Create a named consumer group for an outbox.

SELECT tide.create_consumer_group(
  p_name               TEXT,
  p_outbox             TEXT,
  p_auto_offset_reset  TEXT     DEFAULT 'earliest',
  p_if_not_exists      BOOLEAN  DEFAULT false
);

Errors:

• Outbox must exist
• Group name must be unique (unless p_if_not_exists = true)
• p_auto_offset_reset must be one of: earliest, latest, none

tide.drop_consumer_group

Drop a consumer group and all its offset/lease records.

SELECT tide.drop_consumer_group(
  p_name       TEXT,
  p_if_exists  BOOLEAN DEFAULT false
);

tide.commit_offset

Commit a consumer's processing position.

SELECT tide.commit_offset(
  p_group       TEXT,
  p_consumer    TEXT,
  p_last_offset BIGINT
);

Behavior:

• Upserts into tide.tide_consumer_offsets
• Updates last_heartbeat to now()
• Releases any visibility lease held by this consumer

tide.consumer_heartbeat

Update the heartbeat timestamp for a consumer.

SELECT tide.consumer_heartbeat(
  p_group     TEXT,
  p_consumer  TEXT
);

Call this periodically (e.g., every 10 seconds) while processing to signal liveness.

Views

tide.consumer_lag

Per-consumer lag relative to the latest outbox message:

SELECT * FROM tide.consumer_lag;

Catalog Tables

pg_tide stores all state in relational tables within the tide schema. This page documents the underlying tables that power the SQL API.

tide.tide_outbox_config

One row per named outbox.

tide.tide_outbox_messages

Shared message store for all outboxes.

Indexes:

• idx_tide_outbox_messages_pending — partial index on (outbox_name, id) WHERE consumed_at IS NULL

tide.tide_consumer_groups

Named consumer groups with offset reset policy.

tide.tide_consumer_offsets

Per-consumer committed offsets and heartbeats.

tide.tide_consumer_leases

Visibility leases for in-flight message batches.

tide.tide_inbox_config

Named inbox configurations.

tide.relay_outbox_config

Forward relay pipeline definitions.

Triggers: relay_outbox_config_notify — fires pg_notify('tide_relay_config', ...) on changes.

tide.relay_inbox_config

Reverse relay pipeline definitions.

Triggers: relay_inbox_config_notify — fires pg_notify('tide_relay_config', ...) on changes.

tide.relay_consumer_offsets

Durable per-pipeline offset tracking for the relay binary.

Relay Configuration

The pg-tide relay binary is configured through three layers, applied in order of increasing priority:

1. Default values — sensible defaults built into the binary
2. TOML config file — specified via --config or PG_TIDE_CONFIG
3. CLI flags / environment variables — highest precedence

Pipeline configuration (outbox sources, sink destinations, batch sizes) lives in PostgreSQL — not in the TOML file. The relay loads pipeline config from the tide.relay_outbox_config and tide.relay_inbox_config catalog tables at startup and reloads dynamically via LISTEN/NOTIFY.

Quick Start

The only required parameter is the PostgreSQL connection URL:

pg-tide --postgres-url "postgres://user:pass@localhost:5432/mydb"

For production, use environment variables:

export PG_TIDE_POSTGRES_URL="postgres://relay:${DB_PASSWORD}@pghost:5432/app"
export PG_TIDE_METRICS_ADDR="0.0.0.0:9090"
export PG_TIDE_LOG_FORMAT="json"
export PG_TIDE_GROUP_ID="prod-relay"

pg-tide

TOML Configuration File

For complex setups, use a TOML file:

# relay.toml
postgres_url = "${ENV:DATABASE_URL}"
metrics_addr = "0.0.0.0:9090"
log_format = "json"
log_level = "info"
discovery_interval_secs = 30
default_batch_size = 100
relay_group_id = "production"
sink_max_inflight = 1000

pg-tide --config relay.toml

Configuration Reference

Environment Variable Substitution

Connection strings in TOML files support ${ENV:VAR_NAME} substitution:

postgres_url = "postgres://${ENV:DB_USER}:${ENV:DB_PASSWORD}@${ENV:DB_HOST}:5432/${ENV:DB_NAME}"

This resolves at relay startup time using the process environment. Unknown variables are left as-is (the relay will report a connection error rather than silently using a broken URL).

Relay Group ID

The relay_group_id parameter is critical for multi-deployment setups. It controls:

1. Advisory lock namespacing — each relay instance acquires a PostgreSQL advisory lock scoped to its group ID + pipeline name. Only one relay per group can own a given pipeline.
2. Consumer group offset tracking — progress is tracked per relay group, allowing multiple independent relay deployments to process the same outbox.

Single deployment (default):

pg-tide --relay-group-id "default"

Multi-region deployment:

# US region — processes orders outbox → US NATS
pg-tide --relay-group-id "us-east" --postgres-url "..."

# EU region — processes same orders outbox → EU NATS
pg-tide --relay-group-id "eu-west" --postgres-url "..."

Each group tracks its own offsets independently — the EU relay won't skip messages just because the US relay already processed them.

Pipeline Configuration (in PostgreSQL)

Pipelines are configured via SQL, not via the relay's TOML/CLI config. The relay discovers pipelines from two catalog tables:

Forward Pipelines (Outbox → Sink)

SELECT tide.relay_set_outbox(
  p_name     := 'orders-nats',       -- Pipeline name (unique)
  p_outbox   := 'orders',            -- Source outbox name
  p_sink     := 'nats',              -- Sink type
  p_config   := '{
    "url": "nats://localhost:4222",
    "subject": "orders.{event_type}"
  }'::jsonb
);

Reverse Pipelines (Source → Inbox)

SELECT tide.relay_set_inbox(
  p_name     := 'nats-orders-inbox',   -- Pipeline name (unique)
  p_inbox    := 'order_events',        -- Target inbox name
  p_source   := 'nats',               -- Source type
  p_config   := '{
    "url": "nats://localhost:4222",
    "subject": "orders.>",
    "consumer_name": "pg-tide-inbox"
  }'::jsonb
);

Enabling / Disabling Pipelines

-- Disable a pipeline (relay will stop it on next discovery cycle)
SELECT tide.relay_enable('orders-nats', false);

-- Re-enable
SELECT tide.relay_enable('orders-nats', true);

Pipeline changes are picked up via:

1. LISTEN/NOTIFY — immediate reaction to config changes
2. Periodic polling — every discovery_interval_secs as a fallback

Hot Reload

The relay watches for NOTIFY signals on the tide_relay_config_changed channel. When you modify a pipeline via tide.relay_set_outbox() or tide.relay_set_inbox(), the trigger fires a notification and the relay reloads within seconds — no restart required.

If LISTEN is interrupted (connection blip), the periodic discovery poll acts as a safety net.

High Availability

Run multiple relay instances with the same relay_group_id:

# Instance 1
pg-tide --relay-group-id "prod" --postgres-url "..."

# Instance 2 (standby — takes over if instance 1 dies)
pg-tide --relay-group-id "prod" --postgres-url "..."

Advisory locks ensure only one instance owns each pipeline at a time. If the owning instance dies, its locks are released and another instance acquires them on the next discovery cycle.

Backpressure

The sink_max_inflight parameter controls backpressure behavior:

• When the number of in-flight (unacknowledged) messages reaches this limit, the relay pauses polling from the outbox
• Once the sink acknowledges enough messages to drop below the threshold, polling resumes
• Set to 0 to disable backpressure (not recommended for production)

This prevents the relay from overwhelming a slow sink while still allowing high throughput for fast sinks.

Graceful Shutdown

On SIGTERM:

1. The relay stops accepting new pipeline ownership
2. Active pipelines finish their current batch
3. If batches don't complete within --drain-timeout seconds (default: 30), the relay exits
4. Unfinished messages will be redelivered when the relay restarts (at-least-once guarantee)

Example: Production Configuration

# /etc/pg-tide/relay.toml
postgres_url = "${ENV:DATABASE_URL}"
metrics_addr = "0.0.0.0:9090"
log_format = "json"
log_level = "info"
discovery_interval_secs = 10
default_batch_size = 500
relay_group_id = "production"
sink_max_inflight = 5000

# systemd unit or container entrypoint
PG_TIDE_DRAIN_TIMEOUT=60 pg-tide --config /etc/pg-tide/relay.toml

Catalog vs. TOML: Configuration Hierarchy

pg-tide has two places where configuration lives. This page explains which is the single source of truth and how to use each correctly.

The Rule: Catalog Is Primary, TOML Is Process Config

Managing Pipelines via SQL (Recommended)

All pipeline configuration should be managed through the tide schema SQL functions:

-- Create or update a forward pipeline (outbox → NATS):
SELECT tide.relay_set_outbox_v2('{
  "name":      "orders-to-nats",
  "outbox":    "orders",
  "sink_type": "nats",
  "config": {
    "url":     "nats://nats.svc.cluster.local:4222",
    "subject": "orders.{event_type}"
  },
  "batch_size": 50,
  "enabled":    true
}'::jsonb);

-- Disable a pipeline without deleting it:
SELECT tide.relay_disable('orders-to-nats');

-- Re-enable:
SELECT tide.relay_enable('orders-to-nats');

-- List all configured pipelines:
SELECT name, direction, enabled, config->>'sink_type' AS sink
FROM   tide.relay_outbox_config
UNION ALL
SELECT name, 'reverse', enabled, config->>'source_type'
FROM   tide.relay_inbox_config;

Changes take effect within one second thanks to the LISTEN/NOTIFY hot-reload introduced in v0.18.0. There is no need to restart the relay.

TOML File — Process Configuration Only

The TOML file (default: /etc/pg-tide/pg-tide.toml) configures the relay process, not the pipelines. It should contain only:

postgres_url        = "..."   # or use --postgres-url-file
relay_group_id      = "prod"
max_owned_pipelines = 50
max_connections     = 100
discovery_interval_secs = 30
default_batch_size  = 100
metrics_addr        = "0.0.0.0:9090"
log_level           = "info"
log_format          = "json"
drain_timeout_secs  = 30

A fully commented example is baked into the Docker image at /etc/pg-tide/pg-tide.example.toml. Copy it as a starting point:

docker cp pg-tide:/etc/pg-tide/pg-tide.example.toml ./pg-tide.toml

Startup Warning: TOML-Only Pipelines

If the TOML file configures a pipeline that is not present in the catalog (e.g. via a legacy [pipelines.*] section), the relay emits a WARN-level log entry at startup:

WARN pipeline "orders-to-nats" defined in TOML but not found in catalog — ignoring

The expected resolution is to create the pipeline via SQL using tide.relay_set_outbox_v2() or tide.relay_set_inbox_v2(), then remove the TOML definition.

Secret Interpolation

Sensitive values (passwords, API keys) should not be stored in the catalog JSONB config in plain text. Instead, use the ${ENV:VAR_NAME} or ${FILE:/path/to/secret} interpolation syntax in the config JSON:

SELECT tide.relay_set_outbox_v2('{
  "name":      "orders-to-kafka",
  "outbox":    "orders",
  "sink_type": "kafka",
  "config": {
    "brokers":  "kafka.svc:9092",
    "topic":    "orders",
    "sasl_password": "${ENV:KAFKA_SASL_PASSWORD}"
  }
}'::jsonb);

The relay resolves ${ENV:...} tokens at runtime, keeping the secret out of the database entirely.

See Also

• Configuration reference
• SQL API reference
• Operations runbooks

CLI Reference

The pg-tide binary is both the relay daemon and an operational toolkit. Run it without a subcommand to start the relay; use a subcommand for diagnostics, maintenance, and introspection.

Usage

pg-tide [OPTIONS] [COMMAND]

When COMMAND is omitted, the relay daemon starts. All subcommands are short-lived and exit after completing their task.

Global Options

These flags apply to both daemon mode and all subcommands.

Daemon Mode

pg-tide --postgres-url "postgres://relay:secret@db.internal:5432/app"

Starts the relay daemon. All pipeline configuration is loaded from PostgreSQL and hot-reloads on SIGHUP without restart. See Configuration for the TOML file format and pipeline schema.

HTTP Endpoints

Signals

Subcommands

doctor

Validates PostgreSQL connectivity, schema version, and catalog health.

pg-tide doctor [--postgres-url <URL>]

Checks performed:

• TCP connectivity and TLS handshake to PostgreSQL
• Existence of the tide schema
• Presence of all required catalog tables
• relay_consumer_offsets.last_change_id column (v0.12.0+ migration marker)
• Presence of tide.outbox_truncate_delivered() function (v0.15.0+)
• Count of configured forward and reverse pipelines

Exit codes: 0 = all checks passed, 1 = one or more failures.

Example output:

pg-tide doctor v0.16.0
Connecting to PostgreSQL...
  [OK] Connected to PostgreSQL
  [OK] Schema 'tide' exists
  [OK] Table tide.tide_outbox_config
  [OK] Table tide.relay_outbox_config
  [OK] relay_consumer_offsets.last_change_id column present
  [OK] tide.outbox_truncate_delivered() present (v0.15.0+)
  [INFO] 3 forward pipeline(s), 1 reverse pipeline(s) configured

pg-tide doctor: all checks passed.

Typical use: health check in CI, post-deploy validation, Kubernetes readinessProbe via a Job.

validate-config

Dry-runs source and sink factory construction for a named pipeline without processing any messages.

pg-tide validate-config --pipeline <NAME> [--postgres-url <URL>]

What it does:

1. Loads the pipeline config from tide.relay_outbox_config or tide.relay_inbox_config
2. Resolves all ${ENV:VAR} secret placeholders
3. Constructs the source implementation (e.g., outbox poller, Kafka consumer)
4. Constructs the sink implementation (e.g., Kafka producer, HTTP webhook)
5. Reports success or the first construction failure

No messages are read or published. Exit 0 = config is valid, 1 = failure.

Example:

pg-tide validate-config \
  --pipeline orders-kafka \
  --postgres-url "$DATABASE_URL"

pg-tide validate-config — pipeline: orders-kafka
  [OK] Secrets resolved
  [OK] Source 'outbox:orders' instantiated
  [OK] Sink 'kafka:orders.events' instantiated

validate-config: pipeline 'orders-kafka' configuration is valid.

Typical use: pre-flight check before enabling a new pipeline; CI step after updating sink credentials.

status

Prints a summary table of all configured relay pipelines.

pg-tide status [--postgres-url <URL>]

Columns:

Example:

PIPELINE                       DIRECTION  ENABLED  LAST_OFFSET    CONSUMER_LAG
--------------------------------------------------------------------------------
orders-kafka                   forward    yes      1842731        0
payments-kafka                 forward    yes      998201         3
webhooks-incoming              reverse    yes      0              0
audit-log                      forward    no       0              0

4 pipeline(s) configured.

Consumer lag is queried from the outbox table at snapshot time; it does not reflect in-flight messages being processed by a running relay.

sweep

Deletes consumed outbox messages that are past their retention window.

pg-tide sweep [--outbox <NAME>] [--postgres-url <URL>]

Calls tide.outbox_truncate_delivered() for each outbox. When --outbox is omitted, all outboxes are swept. Run this on a schedule to prevent unbounded growth of the tide_outbox_messages table.

Example:

# Sweep all outboxes
pg-tide sweep --postgres-url "$DATABASE_URL"

# Sweep a single outbox
pg-tide sweep --outbox orders --postgres-url "$DATABASE_URL"

pg-tide sweep v0.16.0
  [OK] Swept outbox 'orders': 12408 rows deleted
  [OK] Swept outbox 'payments': 4891 rows deleted

pg-tide sweep: 17299 total row(s) deleted from 2 outbox(es).

Typical use: cron job or Kubernetes CronJob running every hour.

# Kubernetes CronJob
schedule: "0 * * * *"
command: ["pg-tide", "sweep", "--postgres-url", "$(DATABASE_URL)"]

replay

Replay workbench for inspecting, debugging, and recovering from delivery failures. All replay subcommands are read-only or operate only on DLQ metadata — they never advance consumer offsets.

replay preview

Print outbox messages in an ID range as JSONL without consuming them.

pg-tide replay preview \
  --outbox <NAME> \
  [--from-id <ID>] \
  [--to-id <ID>] \
  [--limit <N>] \
  [--postgres-url <URL>]

Output is JSONL on stdout; progress is printed to stderr.

pg-tide replay preview --outbox orders --from-id 1840000 --limit 5

{"id":1840001,"outbox_name":"orders","payload":{"order_id":42},"headers":{},"created_at":"2026-05-07T10:00:01Z","consumed":true}
{"id":1840002,"outbox_name":"orders","payload":{"order_id":43},"headers":{},"created_at":"2026-05-07T10:00:02Z","consumed":false}

replay dry-run

Evaluate a pipeline's transforms against a sample of outbox messages and print the resulting envelopes to stdout — without publishing anything.

pg-tide replay dry-run \
  --pipeline <NAME> \
  [--from-id <ID>] \
  [--to-id <ID>] \
  [--limit <N>] \
  [--postgres-url <URL>]

Useful for verifying JMESPath transform expressions and wire format output before enabling a pipeline.

pg-tide replay dry-run --pipeline orders-kafka --limit 3

Dry-run transform evaluation for pipeline 'orders-kafka' (3 message(s)):
{"outbox_id":1840001,"event_id":"uuid-...","op":"c","payload":{...}}
{"outbox_id":1840002,"event_id":"uuid-...","op":"u","payload":{...}}
  [SKIP] id=1840003 (tombstone or filtered)

replay dlq-resolve

Mark a DLQ entry as resolved (closed without requeue).

pg-tide replay dlq-resolve \
  --pipeline <NAME> \
  --dedup-key <KEY> \
  [--postgres-url <URL>]

Sets resolved = true on the DLQ row. The message will not be retried. Use when the failure is expected or the downstream system has been manually updated.

replay dlq-requeue

Requeue a DLQ entry for another relay attempt.

pg-tide replay dlq-requeue \
  --pipeline <NAME> \
  --dedup-key <KEY> \
  [--postgres-url <URL>]

Marks the current DLQ entry resolved and inserts a fresh pending entry with attempt_count = 0. The running relay will pick it up on the next cycle.

asyncapi export

Generate an AsyncAPI 3.0 document from relay catalog metadata.

pg-tide asyncapi export \
  [--format yaml|json] \
  [--output <PATH>] \
  [--postgres-url <URL>]

Reads all configured outbox and inbox pipelines from PostgreSQL and emits an AsyncAPI 3.0 document describing each pipeline as a named channel, operation, and message schema. Useful for API documentation, consumer contract testing with Microcks, and downstream code generation.

pg-tide asyncapi export \
  --format yaml \
  --output relay-asyncapi.yaml \
  --postgres-url "$DATABASE_URL"

See Microcks Integration for a complete guide on using the exported spec for consumer contract testing.

Daemon Startup Examples

Minimal

pg-tide --postgres-url "postgres://user:pass@localhost:5432/mydb"

Production

pg-tide \
  --postgres-url "postgres://relay:secret@db.internal:5432/app" \
  --log-format json \
  --log-level info \
  --relay-group-id production \
  --metrics-addr 0.0.0.0:9090 \
  --drain-timeout 60

From config file

pg-tide --config /etc/pg-tide/relay.toml

Docker

docker run \
  -e PG_TIDE_POSTGRES_URL="postgres://..." \
  -p 9090:9090 \
  ghcr.io/trickle-labs/pg-tide:latest

Backends

The pg-tide relay supports multiple messaging backends as both sinks (forward mode: outbox → external system) and sources (reverse mode: external system → inbox). This page covers all available backends with their configuration, use cases, and operational guidance.

Choosing a Backend

Different backends suit different architectural needs. Use this decision matrix to pick the right one:

Quick recommendations

• Starting out / prototyping: NATS (default, zero configuration, fast)
• Enterprise data pipelines: Kafka (strongest durability and ordering guarantees)
• AWS-native infrastructure: SQS (fully managed, no servers to operate)
• Existing Redis stack: Redis Streams (reuse your Redis deployment)
• Third-party integrations: Webhook (push to any HTTP endpoint)
• Legacy AMQP systems: RabbitMQ (rich routing, mature ecosystem)

Feature Flags

Backends are feature-gated at compile time. Only enabled backends are compiled into the relay binary:

To build with specific backends:

# Only NATS and Kafka
cargo build --package pg-tide-relay --features "nats,kafka"

# All backends
cargo build --package pg-tide-relay --all-features

The official Docker image and GitHub release binaries include all backends.

NATS

NATS is the default and recommended backend for pg_tide. It provides extremely low-latency publish/subscribe messaging with optional JetStream durability. NATS is lightweight (single binary, no JVM), supports wildcards, and handles millions of messages per second.

When to use NATS: Real-time microservice communication, event fan-out, lightweight pub/sub where you want simplicity and speed. NATS is the "batteries included" choice for most pg_tide deployments.

Forward (Outbox → NATS)

SELECT tide.relay_set_outbox('orders-nats', 'orders', 'nats',
  jsonb_build_object(
    'url', 'nats://localhost:4222',
    'subject', 'orders.events'
  )
);

Configuration

Subject templates

The subject supports variable substitution from the message headers, allowing dynamic routing without relay-side logic:

• {outbox_name} — source outbox name
• {event_type} — value of the event_type key in the message headers
• {outbox_id} — the message ID (numeric)

Example: "orders.{event_type}" with a message that has "event_type": "order.created" in its headers will publish to subject "orders.order.created".

This is powerful for fan-out patterns: a single outbox can route events to many different NATS subjects based on their type, and downstream services can subscribe to only the events they care about using NATS wildcards.

Reverse (NATS → Inbox)

SELECT tide.relay_set_inbox('nats-to-inbox', 'incoming-events',
  jsonb_build_object(
    'url', 'nats://localhost:4222',
    'subject', 'external.events.>'
  ),
  p_source := 'nats'
);

Configuration

The queue_group option enables NATS queue subscriptions: if multiple relay instances subscribe to the same subject with the same queue group, NATS distributes messages among them (each message goes to one subscriber). This is useful for horizontal scaling of reverse pipelines.

NATS operational notes

• NATS Core (without JetStream) provides at-most-once delivery — if no subscriber is active, messages are lost. For durable delivery, enable JetStream on your NATS server.
• The relay handles NATS reconnection automatically with exponential backoff.
• For multi-server NATS clusters, provide any single server URL — the NATS client discovers other servers automatically.

Kafka

Apache Kafka is the industry standard for high-throughput event streaming. If you're building data pipelines, event-driven architectures at scale, or need strong ordering guarantees with long-term retention, Kafka is the natural choice.

When to use Kafka: High-volume event streaming (>10K events/sec), data pipelines feeding analytics/ML systems, scenarios requiring strong ordering guarantees, or when Kafka is already part of your infrastructure.

Forward (Outbox → Kafka)

SELECT tide.relay_set_outbox('events-kafka', 'events', 'kafka',
  jsonb_build_object(
    'brokers', 'broker1:9092,broker2:9092,broker3:9092',
    'topic', 'app-events',
    'acks', 'all',
    'compression', 'snappy',
    'key', '{event_type}'
  )
);

Configuration

Key (partition) strategy: The key field determines which Kafka partition each message goes to. Messages with the same key are guaranteed to land in the same partition, preserving ordering. Common patterns:

• {event_type} — all events of the same type go to the same partition
• {outbox_name} — partition by source outbox
• No key — round-robin distribution across partitions (best throughput, no ordering)

Reverse (Kafka → Inbox)

SELECT tide.relay_set_inbox('kafka-to-inbox', 'kafka-events',
  jsonb_build_object(
    'brokers', 'broker1:9092,broker2:9092',
    'topic', 'external-events',
    'group_id', 'pg-tide-consumer'
  ),
  p_source := 'kafka'
);

Configuration

Kafka operational notes

• Building with Kafka support requires librdkafka (or the bundled cmake build via rdkafka/cmake-build feature).
• Use acks=all for durability in production — it ensures the message is replicated before acknowledgment.
• For high throughput, use snappy or lz4 compression and increase p_batch_size to 200-500.
• The relay commits Kafka consumer offsets back to Kafka (for reverse pipelines) in addition to tracking them in pg_tide's own offset table.

Redis Streams

Redis Streams provide lightweight event streaming with consumer groups built on top of Redis. If you already run Redis and need simple, fast event delivery without the operational overhead of Kafka, Redis Streams is an excellent choice.

When to use Redis: You already have Redis in your stack, you need low-latency delivery, your event volume is moderate (<50K/sec), or you want minimal additional infrastructure.

Forward (Outbox → Redis Stream)

SELECT tide.relay_set_outbox('events-redis', 'events', 'redis',
  jsonb_build_object(
    'url', 'redis://localhost:6379',
    'stream', 'app:events',
    'maxlen', 100000
  )
);

Configuration

Reverse (Redis Stream → Inbox)

SELECT tide.relay_set_inbox('redis-to-inbox', 'redis-events',
  jsonb_build_object(
    'url', 'redis://localhost:6379',
    'stream', 'external:events',
    'group', 'pg-tide',
    'consumer', 'relay-0'
  ),
  p_source := 'redis'
);

Configuration

Redis operational notes

• Redis Streams durability depends on your Redis persistence configuration (AOF, RDB, or none). For production event delivery, enable AOF with appendfsync everysec.
• Use maxlen to prevent unbounded stream growth. Redis will trim the oldest entries when the limit is reached.
• For Redis Cluster, the relay connects to the cluster and routes to the correct shard based on the stream key.

RabbitMQ

RabbitMQ provides rich message routing through exchanges, bindings, and queues. It's ideal when you need complex routing patterns (topic-based, header-based, fanout) or when you're integrating with existing AMQP infrastructure.

When to use RabbitMQ: Complex routing requirements, existing AMQP infrastructure, work queue patterns where messages should be processed by exactly one consumer, or when you need message priority and TTL features.

Forward (Outbox → RabbitMQ)

SELECT tide.relay_set_outbox('events-rabbit', 'events', 'rabbitmq',
  jsonb_build_object(
    'url', 'amqp://user:pass@localhost:5672/%2f',
    'exchange', 'app.events',
    'routing_key', 'orders.created',
    'exchange_type', 'topic',
    'durable', true
  )
);

Configuration

Reverse (RabbitMQ → Inbox)

SELECT tide.relay_set_inbox('rabbit-to-inbox', 'amqp-events',
  jsonb_build_object(
    'url', 'amqp://user:pass@localhost:5672/%2f',
    'queue', 'incoming-events',
    'prefetch', 20
  ),
  p_source := 'rabbitmq'
);

Configuration

RabbitMQ operational notes

• For the URL, use %2f for the default vhost (/): amqp://user:pass@host:5672/%2f
• The relay declares the exchange if it doesn't exist (for forward mode). For reverse mode, the queue must already exist.
• Use durable=true in production to survive broker restarts.
• RabbitMQ's topic exchange routing keys support wildcards: orders.* matches orders.created, orders.shipped, etc.

SQS

Amazon SQS is a fully managed message queue service. It requires zero infrastructure management and integrates seamlessly with the AWS ecosystem (Lambda, ECS, Step Functions).

When to use SQS: AWS-native infrastructure, serverless consumers (Lambda), when you want zero queue management overhead, or when your team already uses AWS services extensively.

Forward (Outbox → SQS)

SELECT tide.relay_set_outbox('events-sqs', 'events', 'sqs',
  jsonb_build_object(
    'queue_url', 'https://sqs.us-east-1.amazonaws.com/123456789012/my-queue',
    'region', 'us-east-1'
  )
);

Configuration

Reverse (SQS → Inbox)

SELECT tide.relay_set_inbox('sqs-to-inbox', 'sqs-events',
  jsonb_build_object(
    'queue_url', 'https://sqs.us-east-1.amazonaws.com/123456789012/incoming',
    'region', 'us-east-1',
    'wait_time_seconds', 20,
    'max_messages', 10
  ),
  p_source := 'sqs'
);

Configuration

SQS authentication

The relay uses the standard AWS credential chain (in priority order):

1. Environment variables: AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY
2. AWS config/credentials files: ~/.aws/credentials
3. IAM instance role: Automatic on EC2/ECS/Lambda
4. EKS IRSA: Via web identity token (recommended for Kubernetes)

For EKS deployments, use IAM Roles for Service Accounts (IRSA) to avoid managing credentials directly.

SQS operational notes

• Use FIFO queues when ordering matters. Set message_group_id to group related messages (e.g., by customer ID or order ID).
• Standard queues provide higher throughput but best-effort ordering.
• Long polling (wait_time_seconds: 20) reduces costs by minimizing empty receives.
• SQS has a 256 KB message size limit. For larger payloads, consider the claim-check pattern: store the payload in S3 and put a reference in the SQS message.

Webhook

HTTP webhooks are the universal integration mechanism — any system with an HTTP endpoint can receive events from pg_tide, and any system that can send HTTP requests can push events into a pg_tide inbox.

When to use Webhooks: Third-party integrations (Stripe, Twilio, GitHub), push notifications to serverless functions, integrating with systems that don't support native messaging protocols, or receiving events from external services.

Forward (Outbox → HTTP Webhook)

Delivers outbox messages as HTTP POST requests to a configured URL:

SELECT tide.relay_set_outbox('events-webhook', 'events', 'webhook',
  jsonb_build_object(
    'url', 'https://api.example.com/webhooks/events',
    'timeout_ms', 5000,
    'headers', '{"Authorization": "Bearer token123", "X-Source": "pg-tide"}'
  )
);

Configuration

Request format

The relay sends each message as a JSON POST request:

POST /webhooks/events HTTP/1.1
Content-Type: application/json
X-PgTide-Dedup-Key: orders:42:0
X-PgTide-Event-Type: order.created
Authorization: Bearer token123

{"order_id": 42, "total": 99.99}

The X-PgTide-Dedup-Key header allows the receiver to implement idempotency. The X-PgTide-Event-Type header carries the event type from the outbox message headers.

Reverse (HTTP Webhook → Inbox)

Exposes an HTTP endpoint that accepts incoming webhook deliveries and writes them to an inbox:

SELECT tide.relay_set_inbox('webhook-receiver', 'incoming-hooks',
  jsonb_build_object(
    'port', 8080,
    'path', '/webhooks/incoming',
    'auth_header', 'Bearer whsec_your_secret'
  ),
  p_source := 'webhook'
);

Configuration

Dedup key extraction

For incoming webhooks, the relay extracts a dedup key from these headers (in priority order):

1. X-Request-ID
2. X-Idempotency-Key
3. X-Webhook-ID
4. Auto-generated UUID (fallback — use only if the sender doesn't provide idempotency keys)

The extracted key becomes the event_id in the inbox table, enabling deduplication of retried webhook deliveries.

Webhook operational notes

• Always use HTTPS for outbound webhooks in production (sensitive data in payloads, authentication headers).
• Set retry_codes to match the receiver's error semantics. 429 (rate limited) should always trigger retry.
• For inbound webhooks, validate the auth_header to prevent unauthorized writes to your inbox.
• Consider using a short timeout_ms (5000ms) for forward webhooks to avoid holding relay resources on slow receivers.

stdout and stdin (Development)

For development, testing, and debugging, the relay includes stdout (forward) and stdin (reverse) backends.

stdout prints delivered messages to the relay's standard output — useful for verifying that your pipeline configuration works without setting up an external system:

SELECT tide.relay_set_outbox('debug-pipeline', 'events', 'stdout');

stdin reads messages from standard input — useful for manual testing of inbox processing:

echo '{"event_id": "test-1", "payload": {"hello": "world"}}' | pg-tide --stdin-pipeline my-inbox

Common Configuration Patterns

TLS/SSL for all backends

Most backends support TLS via their URL scheme:

-- NATS with TLS
'url', 'nats://nats.example.com:4443'  -- with credentials file for mutual TLS

-- Kafka with SSL
'brokers', 'broker1:9093'  -- SSL port, configured via librdkafka

-- Redis with TLS
'url', 'rediss://redis.example.com:6380'  -- note: rediss:// (double-s)

-- RabbitMQ with TLS
'url', 'amqps://user:pass@rabbit.example.com:5671/%2f'  -- amqps:// scheme

-- Webhook with TLS
'url', 'https://api.example.com/webhooks'  -- always HTTPS in production

Naming conventions for pipelines

Choose pipeline names that describe the data flow clearly:

-- Good: describes source and destination
'orders-to-kafka'
'payment-webhooks-to-inbox'
'inventory-nats-fanout'

-- Bad: vague or too generic
'pipeline-1'
'my-pipeline'
'test'

Batching strategy

All forward pipelines support p_batch_size. The right batch size depends on your backend:

Error Handling

The pg-tide relay is designed to be resilient in the face of failures. This page covers how errors are categorized and handled at each stage of the pipeline, the retry strategy, graceful shutdown behavior, dead-letter queue management, and a complete reference of all error codes.

Error Philosophy

pg_tide's error handling follows a simple principle: transient errors are retried, permanent errors are logged and skipped. The relay never silently drops messages — every error is logged, counted in Prometheus metrics, and (for inbox-side failures) tracked in the dead-letter queue.

The relay distinguishes between:

• Transient errors — network timeouts, temporary unavailability, connection resets. These will succeed if retried.
• Permanent errors — malformed payloads, deserialization failures, invalid configuration. These will never succeed regardless of retries.

Retry Strategy

All transient errors trigger exponential backoff retry with jitter:

The backoff sequence looks like: 100ms, 200ms, 400ms, 800ms, 1.6s, 3.2s, 6.4s, 12.8s, 25.6s, 30s, 30s, 30s...

Jitter randomizes each delay by ±20%, so the actual sequence might be: 85ms, 220ms, 350ms, 900ms, etc. This prevents synchronized retry storms.

Error Categories

Connection errors (PostgreSQL)

Symptoms: Relay logs "PostgreSQL connection failed, retrying" or "postgres error"

What happens:

1. The relay logs a warning with connection details
2. Enters reconnection mode with exponential backoff (100ms → 30s)
3. All pipelines are paused (they can't function without the database)
4. On reconnect, advisory locks are re-acquired
5. Pipeline processing resumes from the last committed offset
6. No messages are lost — they remain pending in the outbox

Common causes:

• PostgreSQL is restarting or failing over
• Network partition between relay and database
• Connection pool exhaustion
• Authentication failure (password rotation)

Resolution: Usually self-healing. The relay reconnects automatically when PostgreSQL is available again. If the issue is persistent (auth failure), fix the credentials and the relay will reconnect on its next attempt.

Sink errors (delivery failures)

Symptoms: Relay logs "sink publish error" or "sink unhealthy", Prometheus counter pg_tide_relay_publish_errors_total increases.

What happens:

1. Messages remain pending in the outbox (they are never lost)
2. The relay retries delivery with exponential backoff until the sink recovers
3. Prometheus metrics track pg_tide_relay_publish_errors_total{pipeline="..."}
4. The health endpoint reports unhealthy (503) for affected pipelines
5. Once the sink recovers, delivery resumes automatically

Common causes:

• Downstream system (Kafka, NATS, webhook endpoint) is temporarily unavailable
• Network issues between relay and sink
• Sink is overloaded and rejecting new messages (backpressure)
• TLS certificate issues

Resolution: Usually self-healing. Monitor the error rate and investigate if it persists beyond expected maintenance windows.

Source errors (reverse mode)

Symptoms: Relay logs "source poll error" for reverse pipelines.

What happens:

1. The relay retries subscription/polling with exponential backoff
2. Once reconnected, consumption resumes from the last acknowledged position
3. No messages are skipped (the source tracks its own offset)

Common causes:

• External source (NATS, Kafka, SQS) is temporarily unavailable
• Subscription expired or was revoked
• Consumer group rebalancing (Kafka)

Payload errors (permanent)

Symptoms: Relay logs "payload decode error" or "unsupported outbox payload version"

What happens:

1. The error is logged with full context (outbox name, message ID, raw payload excerpt)
2. The message is skipped — it will never succeed regardless of retries
3. The offset advances past the bad message
4. Prometheus tracks the error count

Common causes:

• Application published malformed JSONB that the relay cannot interpret
• Message format version mismatch (relay expects v2, message is v1)
• Corruption (extremely rare)

Resolution: Investigate the specific message. Fix the publishing code if it's generating invalid payloads. For format mismatches, upgrade the relay or add backward-compatible handling.

Configuration errors

Symptoms: Relay logs "config error" or "invalid config for pipeline" at startup or after hot-reload.

What happens:

1. If the error is in the TOML file, the relay refuses to start
2. If the error is in a pipeline config (in PostgreSQL), that specific pipeline is skipped
3. Other pipelines continue to operate normally

Common causes:

• Missing required config key (e.g., no brokers for Kafka sink)
• Invalid value (e.g., non-numeric batch_size)
• Unsupported backend name

Resolution: Fix the configuration. For pipeline configs, update the JSONB in the database and the relay will pick up the correction via hot-reload.

Graceful Shutdown

When the relay receives SIGTERM or SIGINT:

1. Stop accepting new work — no new batches are fetched from the outbox
2. Drain in-flight messages — wait for currently-delivering batches to complete (up to a drain timeout)
3. Commit final offsets — record the last successfully delivered position
4. Release advisory locks — allow other relay instances to take over immediately
5. Close connections — cleanly disconnect from PostgreSQL and sinks
6. Exit with code 0 — signal success to the process manager

The drain timeout prevents the relay from hanging indefinitely if a sink is unresponsive during shutdown. Messages that weren't committed will be re-delivered by the next relay instance (and deduplicated by the inbox if applicable).

Dead-Letter Queue (Inbox Side)

For reverse pipelines that write to inboxes, messages that fail processing are managed through the inbox's built-in DLQ mechanism.

How messages enter the DLQ

1. Your application reads a message from the inbox and attempts to process it
2. Processing fails (external API timeout, validation error, business rule violation)
3. You call tide.inbox_mark_failed(inbox_name, event_id, error_message)
4. The message's retry_count is incremented and last_error is recorded
5. After max_retries failures, the message is effectively dead-lettered

Querying the DLQ

-- Find all dead-lettered messages in an inbox
SELECT event_id, payload, retry_count, last_error, received_at
FROM tide."my-inbox_inbox"
WHERE processed_at IS NULL
  AND retry_count >= 5  -- assuming max_retries = 5
ORDER BY received_at;

Investigating failures

-- Group DLQ messages by error pattern
SELECT
  left(last_error, 50) AS error_pattern,
  count(*) AS message_count,
  min(received_at) AS earliest,
  max(received_at) AS latest
FROM tide."my-inbox_inbox"
WHERE processed_at IS NULL AND retry_count >= 5
GROUP BY left(last_error, 50)
ORDER BY message_count DESC;

Replaying messages

After fixing the underlying issue, replay specific messages or all DLQ messages:

-- Replay specific messages
SELECT tide.replay_inbox_messages('my-inbox',
  ARRAY['evt-001', 'evt-002', 'evt-003']);

-- Replay all DLQ messages for an inbox
SELECT tide.replay_inbox_messages('my-inbox',
  (SELECT array_agg(event_id)
   FROM tide."my-inbox_inbox"
   WHERE processed_at IS NULL AND retry_count >= 5)
);

Replaying resets retry_count to 0, making messages eligible for normal processing again.

Extension Error Reference

Errors raised by pg_tide SQL functions:

Handling extension errors in PL/pgSQL

DO $$
BEGIN
  PERFORM tide.outbox_publish('maybe-missing', '{}'::jsonb, '{}'::jsonb);
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Publish failed: %', SQLERRM;
    -- Handle gracefully: log, retry, or use a fallback
END $$;

Relay Error Reference

Errors logged by the pg-tide relay binary:

Monitoring Errors

Prometheus metrics for error tracking

# Total delivery errors by pipeline (should be 0 in steady state)
rate(pg_tide_relay_publish_errors_total[5m])

# Unhealthy pipelines (immediate alert)
pg_tide_relay_pipeline_healthy == 0

# Error rate as a percentage of total deliveries
rate(pg_tide_relay_publish_errors_total[5m])
  / rate(pg_tide_relay_messages_published_total[5m])

Alerting rules

- alert: PgTideDeliveryErrors
  expr: rate(pg_tide_relay_publish_errors_total[5m]) > 0
  for: 2m
  labels:
    severity: warning
  annotations:
    summary: "Delivery errors on pipeline {{ $labels.pipeline }}"
    description: "The relay is experiencing delivery failures. Check sink availability."

- alert: PgTidePipelineDown
  expr: pg_tide_relay_pipeline_healthy == 0
  for: 1m
  labels:
    severity: critical
  annotations:
    summary: "Pipeline {{ $labels.pipeline }} is unhealthy"
    description: "Immediate investigation required. Messages are accumulating."

Log-based monitoring

With structured JSON logging (--log-format json), you can filter and alert on error logs:

{"level":"error","pipeline":"orders-to-kafka","error":"sink publish error: BrokerNotAvailable","msg":"delivery failed, will retry","timestamp":"2025-01-15T10:30:00Z"}

Key fields to monitor:

• level=error — any error-level log indicates a problem
• pipeline — identifies which pipeline is affected
• error — the specific error message for diagnosis

Monitoring

The pg-tide relay exposes Prometheus metrics and a health endpoint for observability.

Endpoints

Configure the port with --metrics-addr:

pg-tide --metrics-addr 0.0.0.0:9090

Prometheus Metrics

Counters

Gauges

Health Endpoint

curl http://localhost:9090/health

• 200 OK — all pipelines healthy
• 503 Service Unavailable — one or more pipelines unhealthy

Response body includes unhealthy pipeline names when degraded.

SQL-Level Monitoring

In addition to relay metrics, monitor from PostgreSQL:

-- Pending messages per outbox
SELECT * FROM tide.outbox_pending;

-- Consumer lag
SELECT * FROM tide.consumer_lag;

-- Pipeline status
SELECT tide.relay_list_configs();

Grafana Dashboard

Example PromQL queries for a Grafana dashboard:

# Message throughput (published/sec)
rate(pg_tide_relay_messages_published_total[5m])

# Error rate
rate(pg_tide_relay_publish_errors_total[5m])

# Consumer lag (from PostgreSQL — use a Postgres exporter)
pg_tide_consumer_lag{group_name="my-relay"}

# Pipeline health
pg_tide_relay_pipeline_healthy

Alerting Recommendations

Sinks Overview

When you publish a message to a pg_tide outbox, that message sits safely in your PostgreSQL database, waiting to be delivered somewhere useful. A sink is the destination where the pg_tide relay delivers those messages. Think of it like a postal service: your application drops a letter (message) into a mailbox (outbox), and the postal carrier (relay) delivers it to the recipient's address (sink).

pg_tide supports 30 different sinks, covering everything from traditional message queues like Apache Kafka and RabbitMQ, to cloud services like Amazon SQS and Google Cloud Pub/Sub, to modern data lakes like Apache Iceberg and Delta Lake, to notification services like Slack and PagerDuty. No matter where your messages need to go, there is likely a sink that fits your needs.

Choosing the Right Sink

Selecting a sink depends on what you are trying to accomplish. The table below groups sinks by category and highlights the primary use case for each. If you are building a new system and have flexibility in choosing your messaging infrastructure, start with the category that matches your architectural goals, then read the detailed page for each sink to understand the trade-offs.

Message Queues & Streaming

These sinks deliver messages to traditional message brokers and streaming platforms. They are ideal when you need durable, ordered message delivery to multiple consumers, or when you are integrating with an existing event-driven architecture.

Analytics & Data Lakes

These sinks write messages directly into analytical databases and data lake storage. They are ideal when your PostgreSQL events need to feed dashboards, machine learning pipelines, or long-term analytical storage without going through an intermediate message broker.

Notifications & Webhooks

These sinks deliver messages to notification services and HTTP endpoints. They are ideal for alerting, triggering external workflows, and integrating with third-party APIs that expect HTTP callbacks.

Connector Ecosystems

These sinks integrate with established connector frameworks, giving you access to hundreds of additional destinations through a single configuration. Instead of pg_tide implementing a direct connection to every possible system, these adapters let you leverage existing connector ecosystems.

Infrastructure

These sinks deliver messages to other PostgreSQL instances or output streams. They are useful for database-to-database messaging, testing, and debugging.

Common Configuration Patterns

Every sink is configured through the relay pipeline's JSONB configuration stored in PostgreSQL. The basic pattern looks like this:

SELECT tide.relay_set_outbox(
    'my-pipeline',
    'orders',
    'relay-group',
    '{
        "sink_type": "kafka",
        "brokers": "localhost:9092",
        "topic": "order-events"
    }'::jsonb
);

The sink_type field determines which sink implementation the relay uses. All other fields in the JSON object are sink-specific configuration. Every sink page documents its complete configuration reference.

Secret Management

Sensitive values like passwords, API keys, and connection strings should never be stored directly in the pipeline configuration. Instead, use secret interpolation:

{
    "sink_type": "kafka",
    "brokers": "${env:KAFKA_BROKERS}",
    "sasl_username": "${env:KAFKA_USERNAME}",
    "sasl_password": "${env:KAFKA_PASSWORD}"
}

The relay resolves ${env:VAR_NAME} tokens from environment variables and ${file:/path/to/secret} from files at startup. Resolved values are never written to logs or metric labels.

Delivery Guarantees

All sinks provide at-least-once delivery by default. The relay acknowledges messages in the outbox only after the sink confirms receipt. If the relay crashes between delivering a message and acknowledging it, the message will be delivered again on restart.

For sinks that support it, you can achieve exactly-once semantics by combining pg_tide's outbox deduplication key with the sink's native deduplication mechanism. Each sink page documents whether exactly-once is possible and how to configure it.

Error Handling

When a sink cannot accept a message (network failure, authentication error, malformed payload), the relay retries with exponential backoff. If retries are exhausted, the message is routed to the dead-letter queue (DLQ) for manual inspection and replay. The circuit breaker protects against cascading failures by temporarily halting delivery when a sink is consistently unavailable.

Next Steps

Browse the sink pages that match your use case, or start with the most common choices:

• New to event streaming? Start with NATS JetStream for simplicity
• Enterprise Kafka shop? Go to Apache Kafka
• Building a data lake? See Apache Iceberg or Object Storage
• Need webhooks? See HTTP Webhook
• Want maximum connector coverage? See Singer / Meltano

Apache Kafka

Apache Kafka is a distributed event streaming platform that serves as the backbone of real-time data architectures at thousands of organizations worldwide. Originally developed at LinkedIn and now maintained by the Apache Software Foundation, Kafka excels at handling high-throughput, fault-tolerant, ordered streams of events. When you connect pg_tide to Kafka, every message published to your PostgreSQL outbox is automatically delivered to Kafka topics, making your database changes available to any downstream system that speaks the Kafka protocol — from stream processors like Apache Flink to data warehouses like Snowflake.

If your organization already uses Kafka, connecting pg_tide is the simplest way to get your PostgreSQL events into the broader event-driven ecosystem without writing custom producer code or managing CDC infrastructure like Debezium. If you are evaluating message brokers, Kafka is an excellent choice when you need durable, ordered, replayable event streams at scale.

When to Use This Sink

Choose the Kafka sink when you need one or more of the following:

• High throughput — Kafka handles millions of messages per second across partitioned topics. If your outbox produces thousands of events per second, Kafka will keep up without breaking a sweat.
• Durable replay — Kafka retains messages for a configurable period (days, weeks, or forever with log compaction). Downstream consumers can replay the entire history or start from any point in time.
• Multiple consumers — Many different services need to independently consume the same stream of events. Kafka's consumer group model makes this natural.
• Existing Kafka ecosystem — Your organization already runs Kafka and your downstream consumers (Flink, ksqlDB, Materialize, Connect) expect Kafka topics.
• CDC compatibility — You want to produce Debezium-formatted change events that existing CDC-aware tools can consume natively.

Consider a different sink if you need sub-millisecond latency (NATS is faster for point-to-point), if you want zero operational overhead (SQS/Pub/Sub are fully managed), or if your total message volume is very low (Kafka's operational cost may not be justified).

How It Works

When the relay processes a batch of outbox messages destined for Kafka, it performs the following steps:

1. Fetch — The relay polls the outbox table for unacknowledged messages belonging to this pipeline's consumer group.
2. Transform — If JMESPath transforms are configured, the relay applies filter and projection expressions to each message payload.
3. Encode — Messages are serialized according to the configured wire format (native JSON, Debezium, or Avro via Schema Registry).
4. Route — The relay determines the target Kafka topic for each message using the configured topic template (static or dynamic based on message content).
5. Produce — Messages are sent to Kafka using the configured producer settings (compression, batching, acknowledgment level).
6. Acknowledge — Once Kafka confirms receipt (based on the acks setting), the relay commits the consumer group offset in PostgreSQL, marking those messages as delivered.

If any step fails, the relay retries with exponential backoff. If retries are exhausted, failed messages are routed to the dead-letter queue.

sequenceDiagram
    participant App as Application
    participant PG as PostgreSQL
    participant Relay as pg-tide relay
    participant Kafka as Kafka Cluster

    App->>PG: INSERT order + outbox_publish()
    Note over PG: Single transaction
    Relay->>PG: Poll outbox (batch)
    PG-->>Relay: Messages batch
    Relay->>Kafka: Produce (compressed, batched)
    Kafka-->>Relay: Ack (all replicas)
    Relay->>PG: Commit offset

Configuration

Minimal Configuration

The simplest possible Kafka sink configuration requires only the broker addresses and a topic name:

SELECT tide.relay_set_outbox(
    'orders-to-kafka',          -- pipeline name
    'orders',                    -- outbox name
    'kafka-relay',               -- consumer group
    '{
        "sink_type": "kafka",
        "brokers": "localhost:9092",
        "topic": "order-events"
    }'::jsonb
);

This connects to a local Kafka cluster without authentication, sends all messages to the order-events topic, and uses default producer settings. This is appropriate for development but not for production.

Production Configuration

A production-ready configuration includes authentication, compression, and tuned producer settings:

SELECT tide.relay_set_outbox(
    'orders-to-kafka',
    'orders',
    'kafka-relay',
    '{
        "sink_type": "kafka",
        "brokers": "${env:KAFKA_BROKERS}",
        "topic": "orders.events.{op}",
        "sasl_mechanism": "SCRAM-SHA-256",
        "sasl_username": "${env:KAFKA_USERNAME}",
        "sasl_password": "${env:KAFKA_PASSWORD}",
        "tls_enabled": true,
        "compression": "zstd",
        "acks": "all",
        "batch_size": 500,
        "linger_ms": 50,
        "idempotent": true,
        "request_timeout_ms": 30000
    }'::jsonb
);

Configuration Reference

Authentication

No Authentication (Development Only)

For local development with an unsecured Kafka cluster, no authentication configuration is needed. Simply provide the broker addresses:

{
    "sink_type": "kafka",
    "brokers": "localhost:9092",
    "topic": "dev-events"
}

This is not recommended for any environment accessible over a network.

SASL/PLAIN (Confluent Cloud)

Confluent Cloud and many managed Kafka services use SASL/PLAIN over TLS. This requires a username (API key) and password (API secret):

{
    "sink_type": "kafka",
    "brokers": "${env:CONFLUENT_BOOTSTRAP_SERVERS}",
    "topic": "my-topic",
    "sasl_mechanism": "PLAIN",
    "sasl_username": "${env:CONFLUENT_API_KEY}",
    "sasl_password": "${env:CONFLUENT_API_SECRET}",
    "tls_enabled": true
}

SASL/SCRAM-SHA-256

Self-hosted Kafka clusters often use SCRAM-SHA-256 for username/password authentication with stronger security than PLAIN:

{
    "sink_type": "kafka",
    "brokers": "kafka-1:9093,kafka-2:9093,kafka-3:9093",
    "topic": "events",
    "sasl_mechanism": "SCRAM-SHA-256",
    "sasl_username": "${env:KAFKA_USER}",
    "sasl_password": "${env:KAFKA_PASS}",
    "tls_enabled": true
}

mTLS (Certificate-Based)

For environments requiring mutual TLS authentication (common in financial services and regulated industries), provide client certificates:

{
    "sink_type": "kafka",
    "brokers": "kafka-1:9093,kafka-2:9093",
    "topic": "secure-events",
    "tls_enabled": true,
    "tls_ca_cert": "/etc/certs/ca.pem",
    "tls_client_cert": "/etc/certs/client.pem",
    "tls_client_key": "/etc/certs/client-key.pem"
}

Message Format

Each outbox message becomes a Kafka record with the following mapping:

Topic Routing

The topic field supports template variables that are resolved per-message:

• {stream_table} — The outbox name (e.g., orders)
• {op} — The operation type (insert, update, delete)
• {outbox_id} — The unique outbox message ID

For example, "events.{stream_table}.{op}" routes INSERT messages from the orders outbox to the topic events.orders.insert.

Wire Format Integration

When using the Debezium wire format, messages are produced in Debezium envelope format, making them compatible with tools like Apache Iceberg's Debezium sink connector, Flink CDC, ksqlDB, and Materialize:

{
    "sink_type": "kafka",
    "brokers": "localhost:9092",
    "topic": "dbserver1.public.orders",
    "wire_format": "debezium"
}

See the Debezium Wire Format page for details on the message structure.

Delivery Guarantees

The Kafka sink provides at-least-once delivery by default. With the idempotent producer enabled, it provides exactly-once semantics for the produce operation — Kafka's broker-side deduplication ensures that retried produce requests do not create duplicate records.

Combined with pg_tide's consumer group offset tracking, this means:

• A message is published to Kafka at least once (at-least-once from outbox to Kafka)
• With idempotent: true, Kafka deduplicates retried produces (effectively exactly-once on the produce side)
• If the downstream consumer also uses an idempotent inbox, end-to-end exactly-once is achieved

Acknowledgment Levels

The acks setting controls when Kafka considers a produce successful:

• "0" — The relay does not wait for any acknowledgment. Fastest, but messages can be lost if the leader fails before replicating.
• "1" — The leader broker acknowledges after writing to its local log. Messages can be lost if the leader fails before followers replicate.
• "all" — All in-sync replicas (ISR) must acknowledge. No data loss as long as at least one replica survives. Recommended for production.

Performance Tuning

Batch Size and Linger

The relay collects messages into batches before sending to Kafka. Larger batches improve throughput but increase latency:

• batch_size: 100 (default) — Good balance for most workloads
• batch_size: 500-1000 — Better throughput for high-volume pipelines
• linger_ms: 50-100 — Wait longer to fill batches; reduces request count at the cost of latency

Compression

Compression reduces network bandwidth and Kafka storage at the cost of CPU:

• "zstd" — Best compression ratio, good speed. Recommended for most workloads.
• "lz4" — Fastest compression/decompression, moderate ratio. Best when CPU is constrained.
• "snappy" — Good balance, widely supported. Safe default.
• "gzip" — Highest ratio but slowest. Use only when bandwidth is extremely limited.

Expected Throughput

Under typical conditions with acks: "all" and compression: "zstd":

Actual numbers depend on message size, network latency, Kafka cluster capacity, and replication factor.

Complete Example: Order Events to Kafka

This example demonstrates a complete pipeline from order creation in PostgreSQL to delivery on a Kafka topic.

1. Set Up the Outbox

-- Create the orders outbox with 48-hour retention
SELECT tide.outbox_create('orders', retention_hours => 48);

2. Publish Messages from Your Application

-- In your order processing transaction:
BEGIN;
INSERT INTO orders (id, customer_id, total, status)
VALUES (gen_random_uuid(), 'cust-789', 99.99, 'confirmed');

SELECT tide.outbox_publish(
    'orders',
    jsonb_build_object(
        'event_type', 'order.confirmed',
        'order_id', 'ord-12345',
        'customer_id', 'cust-789',
        'total', 99.99,
        'items', jsonb_build_array(
            jsonb_build_object('sku', 'WIDGET-A', 'qty', 2)
        )
    ),
    'ord-12345'  -- dedup_key
);
COMMIT;

3. Configure the Pipeline

SELECT tide.relay_set_outbox(
    'orders-to-kafka',
    'orders',
    'kafka-relay',
    '{
        "sink_type": "kafka",
        "brokers": "kafka:9092",
        "topic": "order-events",
        "compression": "zstd",
        "acks": "all",
        "idempotent": true,
        "batch_size": 100
    }'::jsonb
);
SELECT tide.relay_enable('orders-to-kafka');

4. Start the Relay

pg-tide --postgres-url "postgresql://relay_user:password@localhost:5432/mydb"

5. Verify Messages Arrive

Using the Kafka console consumer:

kafka-console-consumer \
    --bootstrap-server kafka:9092 \
    --topic order-events \
    --from-beginning

You should see your order event messages arriving as JSON payloads.

Compatibility

The pg_tide Kafka sink is compatible with:

• Apache Kafka 2.8+ (including KRaft mode)
• Confluent Cloud (fully managed)
• Confluent Platform (self-managed)
• Amazon MSK (with IAM or SASL auth)
• Redpanda (Kafka-compatible API)
• Aiven for Kafka
• Upstash Kafka (serverless)

Troubleshooting

"Connection refused" or "Broker not available"

The relay cannot reach the Kafka brokers. Check:

• Broker addresses are correct and include ports
• Network connectivity exists (firewall rules, security groups, VPC peering)
• DNS resolution works for broker hostnames
• TLS is enabled if the cluster requires it

"SASL authentication failed"

Authentication credentials are incorrect or misconfigured:

• Verify sasl_mechanism matches what the cluster expects
• Check that environment variables containing credentials are set
• For Confluent Cloud, ensure you're using the API key (not the cluster ID) as the username

"Topic does not exist"

The target topic has not been created and auto-creation is disabled on the cluster:

• Create the topic manually: kafka-topics --create --topic order-events --partitions 6 --replication-factor 3
• Or enable auto.create.topics.enable=true on the cluster (not recommended for production)

"Message too large"

The message payload exceeds max.message.bytes on the broker:

• Check your message payload sizes
• Increase max.message.bytes on the broker/topic configuration
• Consider using JMESPath projections to reduce payload size before delivery

Messages delivered but not in expected order

Kafka guarantees ordering only within a single partition. If you need message ordering:

• Set message_key to a field that should determine ordering (e.g., {dedup_key} for per-entity ordering)
• Messages with the same key always go to the same partition

Further Reading

• Wire Formats — Produce Debezium, Avro, or custom formats to Kafka
• Content-Based Routing — Route different event types to different topics
• Schema Registry — Enforce Avro/Protobuf schemas on produced messages
• Dead-Letter Queue — Handle delivery failures gracefully
• Circuit Breaker — Protect against Kafka cluster outages

NATS JetStream

NATS is a lightweight, high-performance messaging system designed for cloud-native applications. JetStream is NATS's built-in persistence layer that adds durable message storage, replay capabilities, and exactly-once delivery semantics to the core NATS protocol. When you connect pg_tide to NATS JetStream, your PostgreSQL outbox messages are delivered with sub-millisecond latency to any service subscribed to the relevant subjects, while JetStream ensures messages are persisted and can be replayed if a consumer was offline.

NATS is particularly well-suited for microservice architectures where you need fast, reliable communication between services without the operational complexity of running a Kafka cluster. Its subject-based addressing model makes routing intuitive, and its lightweight footprint means you can run it anywhere — from a single container in development to a globally distributed supercluster in production.

When to Use This Sink

Choose the NATS JetStream sink when your architecture values simplicity and speed:

• Low-latency messaging — NATS delivers messages in microseconds. If your downstream services need near-real-time notification of database changes, NATS is one of the fastest options available.
• Simple operations — NATS is a single binary with minimal configuration. Unlike Kafka, there is no ZooKeeper, no partition management, and no broker coordination to think about.
• Subject-based routing — NATS's hierarchical subject naming (e.g., orders.created, orders.shipped) provides natural topic routing without needing separate topic creation steps.
• Microservice communication — When your services communicate through events and you want a lightweight broker that scales horizontally with minimal fuss.
• Cloud-native deployments — NATS has first-class support for Kubernetes, runs efficiently in containers, and supports leaf nodes for edge computing scenarios.

Consider Kafka instead if you need very long retention periods (weeks/months), strict partition-level ordering guarantees, or compatibility with the Kafka ecosystem (Connect, Streams, ksqlDB).

How It Works

The relay connects to a NATS server (or cluster) and publishes messages to JetStream subjects. JetStream provides durable storage, so messages are persisted even if no consumer is currently subscribed. The flow is:

1. The relay fetches a batch of undelivered messages from the outbox.
2. Each message is published to the configured NATS subject (which can be templated per-message).
3. JetStream acknowledges persistence of each message.
4. The relay commits the consumer group offset in PostgreSQL.

NATS JetStream supports message deduplication based on a Nats-Msg-Id header. pg_tide automatically sets this header to the outbox message's dedup_key, which means that even if the relay retries a publish (after a network interruption, for example), NATS will not create duplicate messages in the stream.

Configuration

Minimal Configuration

SELECT tide.relay_set_outbox(
    'orders-to-nats',
    'orders',
    'nats-relay',
    '{
        "sink_type": "nats",
        "url": "nats://localhost:4222",
        "subject": "orders.events"
    }'::jsonb
);

Production Configuration

SELECT tide.relay_set_outbox(
    'orders-to-nats',
    'orders',
    'nats-relay',
    '{
        "sink_type": "nats",
        "url": "${env:NATS_URL}",
        "subject": "events.{stream_table}.{op}",
        "credentials_file": "${env:NATS_CREDS_FILE}",
        "tls_enabled": true,
        "tls_ca_cert": "/etc/certs/nats-ca.pem",
        "stream": "EVENTS",
        "batch_size": 200
    }'::jsonb
);

Configuration Reference

Authentication

No Authentication (Development)

For local development:

{
    "sink_type": "nats",
    "url": "nats://localhost:4222",
    "subject": "dev.events"
}

Credentials File (NATS.io Cloud / Production)

NATS credentials files contain both the JWT and the NKey seed. This is the recommended authentication method for NATS.io's managed service (Synadia Cloud):

{
    "sink_type": "nats",
    "url": "tls://connect.ngs.global",
    "subject": "myapp.events",
    "credentials_file": "/etc/nats/user.creds"
}

NKey Authentication

NKeys provide public-key authentication without passwords:

{
    "sink_type": "nats",
    "url": "nats://nats-server:4222",
    "subject": "events",
    "nkey_seed": "${env:NATS_NKEY_SEED}",
    "tls_enabled": true
}

Token Authentication

Simple token-based auth for smaller deployments:

{
    "sink_type": "nats",
    "url": "nats://nats-server:4222",
    "subject": "events",
    "token": "${env:NATS_TOKEN}"
}

Delivery Guarantees

The NATS JetStream sink provides exactly-once delivery when properly configured. This is achieved through the combination of:

1. JetStream message deduplication — pg_tide sets the Nats-Msg-Id header to the message's dedup_key. JetStream tracks published message IDs within its deduplication window and rejects duplicates silently.
2. Outbox offset tracking — The relay only commits offsets after JetStream acknowledges persistence.

This means that even if the relay crashes and restarts, re-published messages will be deduplicated by JetStream, preventing downstream consumers from seeing duplicates.

Subject Routing

NATS subjects use a dot-separated hierarchical namespace that makes routing intuitive. pg_tide's template variables map naturally to this model:

events.orders.insert     → new orders
events.orders.update     → order status changes  
events.payments.insert   → new payments
events.*.delete          → all deletes (wildcard subscription)

Configure dynamic subject routing with:

{
    "subject": "events.{stream_table}.{op}"
}

Downstream services can subscribe to exactly the events they care about using NATS wildcards (* for single token, > for multiple tokens).

Complete Example

1. Create the Outbox

SELECT tide.outbox_create('notifications', retention_hours => 24);

2. Configure the Pipeline

SELECT tide.relay_set_outbox(
    'notify-pipeline',
    'notifications',
    'nats-group',
    '{
        "sink_type": "nats",
        "url": "nats://localhost:4222",
        "subject": "notifications.{op}",
        "stream": "NOTIFICATIONS"
    }'::jsonb
);
SELECT tide.relay_enable('notify-pipeline');

3. Publish an Event

SELECT tide.outbox_publish(
    'notifications',
    '{"type": "order.shipped", "order_id": "ord-555", "customer": "alice@example.com"}'::jsonb,
    'ord-555-shipped'
);

4. Verify with NATS CLI

nats sub "notifications.>"
# Output: [notifications.insert] {"type": "order.shipped", ...}

Troubleshooting

"Connection refused"

NATS server is not reachable:

• Check the URL includes the correct port (default 4222)
• Verify network connectivity and firewall rules
• For NATS clusters, ensure at least one seed server is accessible

"Authorization violation"

Authentication or authorization failed:

• Verify credentials file path exists and is readable
• Check that the user/account has publish permission on the target subject
• For NKey auth, ensure the seed matches the configured user

"No responders" or "Stream not found"

JetStream is not configured for the target subject:

• Create the JetStream stream: nats stream add EVENTS --subjects "events.>"
• Or set the stream parameter to match an existing stream
• Verify JetStream is enabled on the NATS server (jetstream: enabled in config)

Further Reading

• Sources: NATS — Consuming from NATS into a pg_tide inbox
• Content-Based Routing — Advanced subject routing patterns
• Bidirectional Sync Tutorial — Using NATS for two-way communication

RabbitMQ

RabbitMQ is one of the most widely deployed open-source message brokers, trusted by tens of thousands of organizations for reliable message delivery. Built on the AMQP 0-9-1 protocol, RabbitMQ provides sophisticated routing capabilities through its exchange-and-queue model, making it particularly well-suited for scenarios where messages need to be routed to different consumers based on content, headers, or routing patterns. When you connect pg_tide to RabbitMQ, your outbox messages are published to exchanges where RabbitMQ's routing rules determine which queues (and ultimately which consumers) receive each message.

When to Use This Sink

Choose RabbitMQ when you need complex message routing patterns (topic exchanges, header-based routing, priority queues), when you are integrating with existing RabbitMQ infrastructure, or when you need per-message acknowledgment with sophisticated dead-letter handling built into the broker itself. RabbitMQ's management UI and mature tooling ecosystem also make it an excellent choice for teams that value operational visibility.

Configuration

Minimal Configuration

SELECT tide.relay_set_outbox(
    'orders-to-rabbit',
    'orders',
    'rabbit-relay',
    '{
        "sink_type": "rabbitmq",
        "url": "amqp://localhost:5672",
        "exchange": "events",
        "routing_key": "orders.created"
    }'::jsonb
);

Production Configuration

SELECT tide.relay_set_outbox(
    'orders-to-rabbit',
    'orders',
    'rabbit-relay',
    '{
        "sink_type": "rabbitmq",
        "url": "amqps://${env:RABBITMQ_USER}:${env:RABBITMQ_PASS}@${env:RABBITMQ_HOST}:5671/%2f",
        "exchange": "events",
        "exchange_type": "topic",
        "routing_key": "{stream_table}.{op}",
        "publisher_confirms": true,
        "mandatory": true,
        "tls_enabled": true,
        "batch_size": 100
    }'::jsonb
);

Configuration Reference

Delivery Guarantees

With publisher_confirms: true (the default), RabbitMQ acknowledges each message after it has been written to disk and (if mirrored) replicated to mirror nodes. This provides at-least-once delivery. Combined with RabbitMQ's built-in deduplication plugin or consumer-side idempotency, you can achieve effectively exactly-once processing.

Routing Patterns

RabbitMQ's routing model is more flexible than simple topic-based systems. The routing_key template combined with exchange types enables powerful patterns:

• Direct exchange: Messages with routing key orders.insert go only to queues bound with that exact key.
• Topic exchange: Messages with routing key orders.insert match queues bound to orders.*, orders.#, or *.insert.
• Fanout exchange: All messages go to all bound queues regardless of routing key (broadcast).
• Headers exchange: Route based on message headers rather than routing key.

Complete Example

-- Publish an order event
SELECT tide.outbox_publish(
    'orders',
    '{"order_id": "ord-99", "status": "paid", "amount": 149.99}'::jsonb,
    'ord-99-paid'
);

Verify with rabbitmqadmin:

rabbitmqadmin get queue=order-processing count=1

Troubleshooting

• "Connection refused" — Check RabbitMQ is running and the port is correct (5672 for AMQP, 5671 for AMQPS)
• "Access refused" — Verify username/password and that the user has publish permission on the exchange
• "Exchange not found" — Create the exchange first or set exchange_declare: true if supported
• Messages not arriving in queue — Check queue bindings match the routing key pattern

Further Reading

• Sources: RabbitMQ — Consuming from RabbitMQ into pg_tide inbox
• Content-Based Routing — Dynamic routing key templates

Redis Streams

Redis Streams is a log-like data structure built into Redis that combines the simplicity of Redis with the durability of an append-only log. Unlike Redis Pub/Sub (which is fire-and-forget), Streams persist messages and support consumer groups with acknowledgment semantics, making them suitable for reliable event delivery. When you connect pg_tide to Redis Streams, your outbox messages are appended to a Redis stream where multiple consumer groups can independently read and process them at their own pace.

Redis Streams is an excellent choice when you already run Redis in your infrastructure and want lightweight event streaming without deploying a separate message broker. It provides ordering guarantees within a single stream, consumer group support for load balancing, and the sub-millisecond latency that Redis is known for.

When to Use This Sink

Choose Redis Streams when you need lightweight, fast message delivery and already have Redis in your stack. It is particularly effective for real-time dashboards, caching invalidation, rate-limited work queues, and microservice communication where message volumes are moderate (thousands per second rather than millions). Consider Kafka or NATS for higher throughput requirements or when you need longer retention periods.

Configuration

Minimal Configuration

SELECT tide.relay_set_outbox(
    'events-to-redis',
    'events',
    'redis-relay',
    '{
        "sink_type": "redis",
        "url": "redis://localhost:6379",
        "stream_key": "events:outbox"
    }'::jsonb
);

Production Configuration

SELECT tide.relay_set_outbox(
    'events-to-redis',
    'events',
    'redis-relay',
    '{
        "sink_type": "redis",
        "url": "rediss://${env:REDIS_HOST}:6380",
        "password": "${env:REDIS_PASSWORD}",
        "stream_key": "events:{stream_table}",
        "maxlen": 100000,
        "batch_size": 200,
        "tls_enabled": true
    }'::jsonb
);

Configuration Reference

Delivery Guarantees

Redis Streams provides at-least-once delivery when combined with pg_tide's offset tracking. Messages are appended atomically to the stream using XADD, and the relay commits its offset only after Redis confirms the write. Redis Streams' consumer groups provide independent progress tracking for multiple downstream consumers.

Stream Management

Redis Streams grow indefinitely unless you configure trimming. The maxlen parameter caps the stream size:

{"maxlen": 100000}

This keeps at most 100,000 entries. Redis uses approximate trimming by default for performance, so the actual size may briefly exceed this limit. For time-based retention, use Redis's built-in XTRIM with MINID in a periodic cleanup job.

Complete Example

SELECT tide.outbox_publish(
    'cache_invalidation',
    '{"entity": "product", "id": "prod-42", "action": "updated"}'::jsonb,
    'prod-42-v7'
);

Verify with redis-cli:

redis-cli XRANGE events:cache_invalidation - + COUNT 5

Troubleshooting

• "Connection refused" — Verify Redis is running and accessible on the specified port
• "NOAUTH Authentication required" — Set the password parameter
• "OOM command not allowed" — Redis is out of memory; configure maxlen or increase Redis memory
• High memory usage — Streams without maxlen grow indefinitely; configure trimming

Further Reading

• Sources: Redis — Consuming from Redis Streams into pg_tide inbox
• Rate Limiting — Controlling message flow to Redis

Amazon SQS

Amazon Simple Queue Service (SQS) is a fully managed message queuing service provided by AWS. It requires zero operational overhead — there are no brokers to provision, no clusters to manage, and no capacity to plan. SQS automatically scales from one message per second to thousands, and you pay only for what you use. When you connect pg_tide to SQS, your outbox messages are delivered to SQS queues where they can trigger Lambda functions, feed ECS services, or be consumed by any AWS service or application that polls SQS.

SQS offers two queue types: Standard queues provide nearly unlimited throughput with at-least-once delivery, while FIFO queues guarantee exactly-once processing with strict message ordering. Both work seamlessly with pg_tide.

When to Use This Sink

Choose SQS when your infrastructure runs on AWS and you want a zero-maintenance message queue. SQS is particularly valuable for triggering Lambda functions (event-driven serverless), decoupling microservices within AWS, and building reliable work queues where messages must not be lost. The FIFO variant is excellent when you need both ordering and exactly-once delivery without managing broker infrastructure.

Configuration

Minimal Configuration

SELECT tide.relay_set_outbox(
    'orders-to-sqs',
    'orders',
    'sqs-relay',
    '{
        "sink_type": "sqs",
        "queue_url": "https://sqs.us-east-1.amazonaws.com/123456789/order-events",
        "region": "us-east-1"
    }'::jsonb
);

Production Configuration (FIFO Queue)

SELECT tide.relay_set_outbox(
    'orders-to-sqs',
    'orders',
    'sqs-relay',
    '{
        "sink_type": "sqs",
        "queue_url": "${env:SQS_QUEUE_URL}",
        "region": "${env:AWS_REGION}",
        "message_group_id": "{stream_table}",
        "deduplication_id": "{dedup_key}",
        "batch_size": 10
    }'::jsonb
);

Configuration Reference

Authentication

The relay uses the standard AWS credential chain: environment variables (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY), instance profile (EC2/ECS), or explicit credentials in the pipeline config. For production on AWS, use IAM roles attached to your ECS task or EC2 instance rather than explicit keys.

Delivery Guarantees

• Standard queues: At-least-once delivery with best-effort ordering. Messages may occasionally be delivered more than once.
• FIFO queues: Exactly-once processing with strict ordering within a message group. Set deduplication_id to {dedup_key} for automatic deduplication.

Complete Example

SELECT tide.outbox_publish(
    'orders',
    '{"event": "order.created", "order_id": "ord-100", "total": 299.00}'::jsonb,
    'ord-100-created'
);

The message appears in the SQS queue and can trigger a Lambda function:

aws sqs receive-message --queue-url $SQS_QUEUE_URL --max-number-of-messages 1

Troubleshooting

• "Access Denied" — The IAM role/user needs sqs:SendMessage and sqs:SendMessageBatch permissions on the queue
• "Queue does not exist" — Verify the queue URL is correct and the queue exists in the specified region
• "InvalidParameterValue" for FIFO — FIFO queues require message_group_id; ensure it's configured
• Duplicate messages in Standard queue — Expected behavior; implement idempotent consumers

Further Reading

• Sources: SQS — Consuming from SQS into pg_tide inbox
• Amazon Kinesis — For higher throughput streaming on AWS

Amazon Kinesis Data Streams

Amazon Kinesis Data Streams is a real-time data streaming service designed for high-volume, continuous data ingestion on AWS. Unlike SQS (which is a queue), Kinesis is a stream — data is retained for a configurable period and multiple consumers can independently read from the same stream at their own pace. When pg_tide delivers messages to Kinesis, they become available to real-time analytics applications, machine learning pipelines, and data lake ingestion processes running on AWS.

Kinesis is designed for scenarios where you need to process hundreds of thousands of records per second in real time. Each stream is composed of shards, and each shard provides 1 MB/s of write capacity and 2 MB/s of read capacity, allowing you to scale by adding more shards.

When to Use This Sink

Choose Kinesis when you need high-throughput real-time streaming on AWS, when you want multiple consumers reading the same data independently (Kinesis Analytics, Lambda, custom applications), or when you need data retention for replay purposes (up to 365 days). Kinesis integrates deeply with AWS services like Firehose, Analytics, and Lambda.

Configuration

Minimal Configuration

SELECT tide.relay_set_outbox(
    'events-to-kinesis',
    'events',
    'kinesis-relay',
    '{
        "sink_type": "kinesis",
        "stream_name": "pg-tide-events",
        "region": "us-east-1",
        "partition_key": "{dedup_key}"
    }'::jsonb
);

Configuration Reference

Delivery Guarantees

Kinesis provides at-least-once delivery. The relay uses the PutRecords API for batch ingestion and confirms delivery before committing offsets. Kinesis guarantees ordering within a partition key, so messages with the same partition_key value are always delivered in order.

Partition Strategy

The partition key determines which shard receives each record. Use {dedup_key} to keep all events for the same entity on the same shard (preserving per-entity ordering), or {stream_table} to group by outbox name. For maximum throughput distribution, use a high-cardinality key.

Troubleshooting

• "Stream not found" — Verify stream name and region are correct
• "ProvisionedThroughputExceededException" — Shard capacity exceeded; add more shards or reduce batch rate with the rate limiter
• "Access Denied" — IAM role needs kinesis:PutRecord and kinesis:PutRecords permissions

Further Reading

• Sources: Kinesis — Consuming from Kinesis into pg_tide inbox
• Amazon SQS — For simpler queuing without stream semantics

Google Cloud Pub/Sub

Google Cloud Pub/Sub is a fully managed, globally distributed messaging service built for reliability at scale. It decouples services by allowing publishers to send messages to topics without knowing who will receive them, and subscribers to receive messages without knowing who sent them. When pg_tide publishes to Pub/Sub, your outbox messages become available to any GCP service or application subscribed to the topic — from Cloud Functions and Cloud Run to Dataflow and BigQuery subscriptions.

Pub/Sub handles automatic scaling, message retention (up to 31 days), and global message routing without any infrastructure management. It is the natural choice for GCP-native architectures.

When to Use This Sink

Choose Pub/Sub when your infrastructure runs on Google Cloud Platform, when you need global message distribution across regions, or when you want deep integration with GCP services like Cloud Functions (event triggers), Dataflow (stream processing), and BigQuery (direct subscriptions for analytics). Pub/Sub supports ordering within ordering keys and scales to millions of messages per second without provisioning.

Configuration

Minimal Configuration

SELECT tide.relay_set_outbox(
    'events-to-pubsub',
    'events',
    'pubsub-relay',
    '{
        "sink_type": "pubsub",
        "project_id": "my-gcp-project",
        "topic": "outbox-events"
    }'::jsonb
);

Production Configuration

SELECT tide.relay_set_outbox(
    'events-to-pubsub',
    'events',
    'pubsub-relay',
    '{
        "sink_type": "pubsub",
        "project_id": "${env:GCP_PROJECT_ID}",
        "topic": "events-{stream_table}",
        "credentials_json": "${file:/etc/gcp/service-account.json}",
        "ordering_key": "{dedup_key}",
        "batch_size": 100
    }'::jsonb
);

Configuration Reference

Authentication

On GCP (GKE, Cloud Run, Compute Engine), use Workload Identity or the default service account — no explicit credentials needed. For external deployments, provide a service account JSON key file via credentials_json.

Delivery Guarantees

Pub/Sub provides at-least-once delivery by default. With ordering keys configured, messages sharing the same ordering key are delivered in publish order to subscribers. Combined with subscriber-side deduplication (using the message ID or the dedup_key attribute), you can achieve effectively exactly-once processing.

Complete Example

SELECT tide.outbox_publish(
    'analytics_events',
    '{"event": "page_view", "user_id": "u-456", "page": "/checkout"}'::jsonb,
    'pv-u456-checkout-1715000000'
);

Messages appear in the Pub/Sub topic and can be consumed by any subscriber:

gcloud pubsub subscriptions pull my-subscription --auto-ack --limit=5

Troubleshooting

• "Permission denied" — Service account needs roles/pubsub.publisher on the topic
• "Topic not found" — Create the topic first: gcloud pubsub topics create outbox-events
• "Ordering key too long" — Ordering keys must be ≤ 1024 bytes

Further Reading

• Sources: Pub/Sub — Subscribing to Pub/Sub messages into pg_tide inbox
• BigQuery — For direct analytics sink on GCP

Azure Service Bus

Azure Service Bus is Microsoft's enterprise message broker, providing reliable message delivery with advanced features like sessions, transactions, dead-lettering, and scheduled delivery. It serves as the backbone for many enterprise integration patterns on Azure. When pg_tide publishes to Service Bus, your outbox messages are delivered to queues or topics where Azure Functions, Logic Apps, or custom applications can process them with enterprise-grade reliability.

When to Use This Sink

Choose Azure Service Bus when your infrastructure runs on Azure, when you need enterprise messaging features (sessions for ordered processing, transactions, scheduled messages), or when you are integrating with Azure-native services like Azure Functions and Logic Apps. Service Bus is particularly strong for ordered message processing and complex routing scenarios within Azure.

Configuration

SELECT tide.relay_set_outbox(
    'events-to-servicebus',
    'events',
    'servicebus-relay',
    '{
        "sink_type": "servicebus",
        "connection_string": "${env:SERVICEBUS_CONNECTION_STRING}",
        "queue_or_topic": "outbox-events",
        "session_id": "{stream_table}",
        "batch_size": 50
    }'::jsonb
);

Configuration Reference

Delivery Guarantees

Service Bus provides at-least-once delivery with server-side duplicate detection available. When sending to session-enabled queues/topics, messages within the same session are delivered in FIFO order. The relay commits offsets only after Service Bus acknowledges receipt.

Troubleshooting

• "Unauthorized access" — Verify connection string has Send permission on the entity
• "Entity not found" — Queue/topic does not exist; create it in the Azure portal
• "Session ID required" — The target entity requires sessions; set session_id

Further Reading

• Sources: Service Bus — Receiving from Service Bus
• Azure Event Hubs — For higher throughput scenarios on Azure

Azure Event Hubs

Azure Event Hubs is a high-throughput event ingestion service on Azure, capable of receiving and processing millions of events per second. It is architecturally similar to Apache Kafka — events flow into partitions, consumer groups track progress independently, and data is retained for a configurable period. In fact, Event Hubs exposes a Kafka-compatible endpoint, making it a managed alternative to self-hosted Kafka on Azure. When pg_tide publishes to Event Hubs, your outbox messages become available to Azure Stream Analytics, Azure Functions, and any Kafka-compatible consumer.

When to Use This Sink

Choose Event Hubs when you need high-throughput event ingestion on Azure, when you want a managed Kafka-compatible service without cluster operations, or when you are building real-time analytics pipelines with Azure Stream Analytics. Event Hubs is designed for millions of events per second and integrates natively with the Azure data ecosystem.

Configuration

SELECT tide.relay_set_outbox(
    'events-to-eventhubs',
    'events',
    'eventhubs-relay',
    '{
        "sink_type": "eventhubs",
        "connection_string": "${env:EVENTHUBS_CONNECTION_STRING}",
        "event_hub_name": "outbox-events",
        "partition_key": "{stream_table}",
        "batch_size": 100
    }'::jsonb
);

Configuration Reference

Delivery Guarantees

Event Hubs provides at-least-once delivery. Events are durably stored across multiple replicas before the send is acknowledged. Ordering is guaranteed within a partition (determined by partition key).

Kafka Compatibility

Event Hubs exposes a Kafka-compatible endpoint. If you prefer, you can use the Kafka sink with Event Hubs' Kafka endpoint instead of the native Event Hubs protocol. The native sink is slightly more efficient as it uses the AMQP protocol directly.

Troubleshooting

• "Unauthorized" — Connection string needs Send claim on the Event Hub
• "Event Hub not found" — Verify the event hub name matches the entity in your namespace
• "Quota exceeded" — Throughput units exhausted; scale up the Event Hubs namespace

Further Reading

• Sources: Event Hubs — Consuming from Event Hubs
• Azure Service Bus — For enterprise queuing patterns

MQTT v5

MQTT (Message Queuing Telemetry Transport) is a lightweight publish/subscribe messaging protocol designed for constrained devices and low-bandwidth, high-latency networks. It has become the de facto standard for IoT (Internet of Things) communication, connecting everything from industrial sensors and smart home devices to connected vehicles and healthcare equipment. When pg_tide publishes to an MQTT broker, your outbox messages are delivered to MQTT topics where IoT devices, edge gateways, and backend services can subscribe.

MQTT v5 (the version pg_tide supports) adds features like message expiry, topic aliases, shared subscriptions, and user properties that make it suitable for more sophisticated use cases beyond basic IoT telemetry.

When to Use This Sink

Choose the MQTT sink when you need to deliver events to IoT devices or edge computing infrastructure, when you are integrating with industrial IoT platforms (HiveMQ, EMQX, AWS IoT Core, Azure IoT Hub), or when you need the lightweight protocol overhead that MQTT provides for bandwidth-constrained environments. MQTT's Quality of Service (QoS) levels let you choose between maximum performance (QoS 0), guaranteed delivery (QoS 1), and exactly-once delivery (QoS 2).

Configuration

SELECT tide.relay_set_outbox(
    'telemetry-to-mqtt',
    'device_commands',
    'mqtt-relay',
    '{
        "sink_type": "mqtt",
        "url": "mqtts://${env:MQTT_BROKER}:8883",
        "topic": "devices/{stream_table}/commands",
        "qos": 1,
        "username": "${env:MQTT_USER}",
        "password": "${env:MQTT_PASS}",
        "client_id": "pg-tide-relay-01",
        "tls_enabled": true
    }'::jsonb
);

Configuration Reference

Delivery Guarantees

Delivery guarantees depend on the QoS level:

• QoS 0 (At-most-once): Fire-and-forget. Fastest but messages can be lost.
• QoS 1 (At-least-once): Broker acknowledges receipt. Messages may be delivered more than once. Recommended for most use cases.
• QoS 2 (Exactly-once): Four-phase handshake ensures each message is delivered exactly once. Highest overhead but strongest guarantee.

Topic Hierarchy

MQTT topics use / as a separator (unlike NATS which uses .). pg_tide's template variables work naturally with MQTT's topic model:

devices/sensors/temperature   → sensor readings
commands/device-01/reboot    → device commands
events/orders/created        → business events

Troubleshooting

• "Connection refused" — Check broker URL, port (1883 for TCP, 8883 for TLS), and firewall rules
• "Not authorized" — Verify username/password or client certificate
• "Client ID already in use" — Each relay instance needs a unique client_id
• Messages not received by subscribers — Check topic name matches subscriber's subscription pattern (MQTT uses + and # wildcards)

Further Reading

• Sources: MQTT — Subscribing to MQTT topics into pg_tide inbox
• Rate Limiting — Protecting IoT brokers from overload

HTTP Webhook

An HTTP webhook is one of the most versatile ways to connect pg_tide to the outside world. Rather than requiring a specific message broker or protocol, webhooks deliver your outbox messages as HTTP POST requests to any URL endpoint. This makes webhooks ideal for integrating with third-party APIs, triggering serverless functions, notifying external services, and building custom integrations where the destination simply needs to accept an HTTP request.

When you configure a webhook sink, the pg_tide relay acts as an HTTP client that sends your outbox messages as JSON payloads to the configured URL. The relay handles retries, timeouts, signature verification, and error tracking automatically — all you need to provide is a URL and optionally some authentication headers.

When to Use This Sink

Choose the webhook sink when:

• Integrating with third-party APIs — Most SaaS platforms accept webhook-style HTTP callbacks. If the service you want to notify has a REST API or webhook endpoint, this sink works immediately.
• Triggering serverless functions — AWS Lambda (via function URLs or API Gateway), Google Cloud Functions, Azure Functions, and Cloudflare Workers all accept HTTP requests as triggers.
• Custom microservices — Your internal services expose HTTP endpoints for receiving events. Webhooks provide a simple, protocol-agnostic delivery mechanism.
• No broker infrastructure — You don't want to run Kafka or NATS just to deliver events to a single service. An HTTP webhook requires no message broker infrastructure at all.
• Prototyping and development — Webhooks are the fastest way to verify your outbox pipeline works end-to-end, since you can use services like webhook.site or ngrok to inspect deliveries.

Consider a dedicated message queue sink (Kafka, NATS, SQS) if you need fan-out to multiple consumers, message replay, or if the destination processes events asynchronously with its own consumer group model.

Configuration

Minimal Configuration

SELECT tide.relay_set_outbox(
    'orders-webhook',
    'orders',
    'webhook-relay',
    '{
        "sink_type": "webhook",
        "url": "https://api.example.com/webhooks/orders"
    }'::jsonb
);

Production Configuration

SELECT tide.relay_set_outbox(
    'orders-webhook',
    'orders',
    'webhook-relay',
    '{
        "sink_type": "webhook",
        "url": "${env:WEBHOOK_URL}",
        "headers": {
            "Authorization": "Bearer ${env:WEBHOOK_TOKEN}",
            "X-Source": "pg-tide"
        },
        "timeout_ms": 10000,
        "signature_secret": "${env:WEBHOOK_SIGNING_SECRET}",
        "signature_scheme": "hmac-sha256",
        "signature_header": "X-Signature-256"
    }'::jsonb
);

Configuration Reference

Authentication

Bearer Token

The most common webhook authentication pattern:

{
    "sink_type": "webhook",
    "url": "https://api.example.com/events",
    "headers": {
        "Authorization": "Bearer ${env:API_TOKEN}"
    }
}

API Key Header

Some services use a custom header for API key authentication:

{
    "sink_type": "webhook",
    "url": "https://api.example.com/ingest",
    "headers": {
        "X-API-Key": "${env:API_KEY}"
    }
}

HMAC Signature (Webhook Signing)

For secure webhook delivery, pg_tide can compute an HMAC-SHA256 signature of the request body and include it in a header. This allows the receiver to verify that the request genuinely came from pg_tide:

{
    "sink_type": "webhook",
    "url": "https://myservice.example.com/hooks",
    "signature_secret": "${env:SIGNING_SECRET}",
    "signature_scheme": "hmac-sha256",
    "signature_header": "X-Signature-256"
}

The receiver computes HMAC-SHA256(body, secret) and compares it to the header value using a timing-safe comparison.

Delivery Guarantees

The webhook sink provides at-least-once delivery. A delivery is considered successful when the target endpoint responds with an HTTP status code in the 2xx range. Any other response (4xx, 5xx, timeout, connection error) triggers a retry.

The retry sequence uses exponential backoff: 100ms → 200ms → 400ms → 800ms → ... up to a maximum of 30 seconds between attempts. After exhausting all retries, the message is routed to the dead-letter queue.

Important: Your webhook receiver should be idempotent. Because delivery is at-least-once, the same message may be delivered more than once in edge cases (e.g., the relay crashes after the endpoint processed the request but before acknowledging the outbox offset). Use the dedup_key included in the payload or headers to detect and skip duplicates.

URL Routing

The url field supports template variables for dynamic routing:

{
    "sink_type": "webhook",
    "url": "https://api.example.com/hooks/{stream_table}/{op}"
}

This routes INSERT events from the orders outbox to https://api.example.com/hooks/orders/insert.

Complete Example: Notify a Fulfillment Service

1. Create the Outbox

SELECT tide.outbox_create('fulfillment_events', retention_hours => 72);

2. Publish an Event

BEGIN;
UPDATE orders SET status = 'ready_to_ship' WHERE id = 'ord-123';

SELECT tide.outbox_publish(
    'fulfillment_events',
    jsonb_build_object(
        'event', 'order.ready_to_ship',
        'order_id', 'ord-123',
        'warehouse', 'us-east-1',
        'items', jsonb_build_array('SKU-001', 'SKU-002')
    ),
    'ord-123-ready'
);
COMMIT;

3. Configure the Pipeline

SELECT tide.relay_set_outbox(
    'fulfillment-webhook',
    'fulfillment_events',
    'webhook-group',
    '{
        "sink_type": "webhook",
        "url": "https://fulfillment.internal/api/v1/events",
        "headers": {
            "Authorization": "Bearer ${env:FULFILLMENT_API_KEY}",
            "Content-Type": "application/json"
        },
        "timeout_ms": 5000,
        "signature_secret": "${env:WEBHOOK_SECRET}",
        "signature_scheme": "hmac-sha256"
    }'::jsonb
);
SELECT tide.relay_enable('fulfillment-webhook');

4. Start the Relay

export FULFILLMENT_API_KEY="sk_live_abc123"
export WEBHOOK_SECRET="whsec_xyz789"
pg-tide --postgres-url "postgresql://relay@localhost:5432/mydb"

The fulfillment service will receive an HTTP POST with the event payload and a signature header it can verify.

Troubleshooting

"Connection refused" or timeout

The target URL is not reachable:

• Verify the URL is correct and the service is running
• Check DNS resolution for the hostname
• Verify network connectivity (firewalls, security groups, VPN)
• Check that timeout_ms is sufficient for the endpoint's response time

HTTP 401 / 403 responses

Authentication failed:

• Verify the Authorization header value is correct
• Check that API keys/tokens have not expired
• Ensure the token has permission to access the webhook endpoint

HTTP 429 (Too Many Requests)

The endpoint is rate-limiting your requests:

• Add a rate limiter to your pipeline configuration
• Consider increasing batch_size if the endpoint supports batch payloads
• Check the endpoint's rate limit documentation

Signature verification failures on the receiver

The receiver rejects the webhook signature:

• Ensure the signing secret matches on both sides
• Verify the signature_scheme matches what the receiver expects
• Check that the receiver is comparing against the raw request body (not a parsed/re-serialized version)

Further Reading

• Webhook Signature Verification — Detailed signature scheme documentation
• Rate Limiting — Protecting endpoints from overload
• Dead-Letter Queue — Handling persistent delivery failures
• Content-Based Routing — Dynamic URL routing based on message content

ClickHouse

ClickHouse is an open-source columnar database management system designed for real-time analytical queries on large datasets. It can process billions of rows per second, making it one of the fastest analytical databases available. When pg_tide delivers messages to ClickHouse, your PostgreSQL events become immediately queryable for real-time dashboards, log analytics, time-series analysis, and business intelligence workloads.

Unlike traditional message queues where data is consumed and deleted, ClickHouse stores your events permanently (or until you define a TTL), letting you run ad-hoc analytical queries across your entire event history. This makes it an excellent complement to pg_tide — your outbox provides reliable event delivery, and ClickHouse provides the analytical query engine.

When to Use This Sink

Choose ClickHouse when you need real-time analytics on your PostgreSQL events, when you want sub-second query performance on billions of rows, or when you are building observability platforms (log storage, metrics, traces). ClickHouse excels at time-series data, aggregation queries, and full-text search across structured event data. It is particularly cost-effective for high-volume workloads because its columnar compression achieves 10-50x data reduction.

Consider Snowflake or BigQuery if you prefer fully managed cloud services with zero operations, or Elasticsearch if your primary need is full-text search with fuzzy matching.

Configuration

Minimal Configuration

SELECT tide.relay_set_outbox(
    'events-to-clickhouse',
    'events',
    'clickhouse-relay',
    '{
        "sink_type": "clickhouse",
        "url": "http://localhost:8123",
        "database": "analytics",
        "table": "events"
    }'::jsonb
);

Production Configuration

SELECT tide.relay_set_outbox(
    'events-to-clickhouse',
    'events',
    'clickhouse-relay',
    '{
        "sink_type": "clickhouse",
        "url": "https://${env:CLICKHOUSE_HOST}:8443",
        "database": "analytics",
        "table": "events",
        "username": "${env:CLICKHOUSE_USER}",
        "password": "${env:CLICKHOUSE_PASSWORD}",
        "batch_size": 1000,
        "tls_enabled": true
    }'::jsonb
);

Configuration Reference

Table Schema

The relay inserts messages as structured rows. Create a ClickHouse table that matches your event schema:

CREATE TABLE analytics.events (
    event_id String,
    outbox_name String,
    event_type String,
    payload String,  -- JSON string
    dedup_key String,
    created_at DateTime64(3),
    op String
) ENGINE = ReplacingMergeTree(created_at)
ORDER BY (event_type, created_at)
TTL created_at + INTERVAL 90 DAY;

The ReplacingMergeTree engine automatically deduplicates rows with the same sort key during background merges, providing eventual deduplication even if the relay delivers a message twice.

Delivery Guarantees

ClickHouse provides at-least-once delivery. The relay uses batch INSERT operations and commits offsets only after ClickHouse confirms the insert succeeded. Using ReplacingMergeTree or dedup_key checks in your queries provides idempotent behavior.

Performance Tuning

ClickHouse performs best with large batch inserts (1,000+ rows). Small, frequent inserts create many small parts that require background merging. Configure:

• batch_size: 1000-5000 — Larger batches are more efficient for ClickHouse
• Adjust the relay's polling interval to accumulate larger batches during high throughput

Troubleshooting

• "Table not found" — Create the target table in ClickHouse before starting the pipeline
• "Column count mismatch" — Ensure the ClickHouse table schema matches the fields the relay produces
• "Too many parts" — Batch size is too small; increase batch_size to reduce insert frequency
• "Authentication failed" — Check username/password and that the user has INSERT permission

Further Reading

• Snowflake — Cloud data warehouse alternative
• BigQuery — Google Cloud analytics alternative
• Wire Formats — Customize how events are structured for ClickHouse

Snowflake

Snowflake is a cloud-native data warehouse that separates compute from storage, allowing you to scale query processing independently of data volume. It runs on AWS, Azure, and GCP, providing a single platform for data warehousing, data lakes, and data sharing across clouds. When pg_tide delivers messages to Snowflake, your PostgreSQL events flow directly into your data warehouse for analytics, reporting, and machine learning without requiring intermediate ETL pipelines.

When to Use This Sink

Choose Snowflake when your organization uses it as the primary data warehouse for analytics and BI, when you need to combine PostgreSQL event data with other data sources already in Snowflake, or when you want zero-maintenance analytical storage that scales automatically. Snowflake's semi-structured data support (VARIANT type) handles JSON event payloads natively, making it easy to query nested event data without predefined schemas.

Configuration

Production Configuration

SELECT tide.relay_set_outbox(
    'events-to-snowflake',
    'events',
    'snowflake-relay',
    '{
        "sink_type": "snowflake",
        "account": "${env:SNOWFLAKE_ACCOUNT}",
        "database": "ANALYTICS",
        "schema": "EVENTS",
        "table": "RAW_EVENTS",
        "warehouse": "INGEST_WH",
        "username": "${env:SNOWFLAKE_USER}",
        "private_key_path": "${env:SNOWFLAKE_KEY_PATH}",
        "batch_size": 500,
        "stage": "pg_tide_stage"
    }'::jsonb
);

Configuration Reference

Authentication

For production, use key-pair authentication rather than passwords:

1. Generate a key pair: openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out snowflake_key.p8 -nocrypt
2. Assign the public key to your Snowflake user: ALTER USER relay_user SET RSA_PUBLIC_KEY='...'
3. Reference the private key in your config: "private_key_path": "/etc/snowflake/key.p8"

How It Works

The relay uses a stage-based approach for efficient loading:

1. Messages are accumulated into micro-batches
2. Each batch is written as a compressed file to an internal Snowflake stage
3. A COPY INTO command loads the staged file into the target table
4. The stage file is removed after successful loading

This approach is more cost-effective than streaming inserts because Snowflake charges per-compute-second, and batch loading uses minimal warehouse time.

Table Schema

CREATE TABLE ANALYTICS.EVENTS.RAW_EVENTS (
    event_id VARCHAR,
    outbox_name VARCHAR,
    payload VARIANT,        -- Stores JSON natively
    dedup_key VARCHAR,
    operation VARCHAR,
    ingested_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

Cost Optimization

• Use an X-Small warehouse for ingestion (sufficient for most event volumes)
• Set warehouse auto-suspend to 60 seconds to minimize idle costs
• Batch sizes of 500-1000 reduce the number of COPY operations
• Consider using Snowpipe for continuous micro-batch loading in very high volume scenarios

Troubleshooting

• "Warehouse not running" — Ensure the warehouse is set to auto-resume, or start it manually
• "Insufficient privileges" — Grant USAGE on warehouse, INSERT on table, WRITE on stage
• "Key-pair authentication failed" — Verify the public key is assigned to the Snowflake user and the private key path is correct

Further Reading

• BigQuery — Google Cloud alternative
• ClickHouse — Self-hosted real-time analytics alternative
• Object Storage — Raw file-based data lake landing

BigQuery

Google BigQuery is a serverless, highly scalable data warehouse that can analyze petabytes of data with standard SQL. Unlike traditional warehouses that require provisioning, BigQuery separates storage from compute and charges only for queries run and data stored. When pg_tide delivers messages to BigQuery, your PostgreSQL events become immediately available for analytics, machine learning (via BigQuery ML), and visualization in Looker or Data Studio.

When to Use This Sink

Choose BigQuery when your analytics stack runs on Google Cloud, when you want truly serverless analytics with no capacity planning, or when you need to combine PostgreSQL event data with other GCP datasets. BigQuery's streaming insert API makes events queryable within seconds of delivery, and its columnar storage format provides excellent compression and query performance for analytical workloads.

Configuration

SELECT tide.relay_set_outbox(
    'events-to-bigquery',
    'events',
    'bq-relay',
    '{
        "sink_type": "bigquery",
        "project_id": "${env:GCP_PROJECT_ID}",
        "dataset": "event_analytics",
        "table": "raw_events",
        "credentials_json": "${file:/etc/gcp/service-account.json}",
        "batch_size": 500
    }'::jsonb
);

Configuration Reference

Insert Methods

• Streaming inserts (default): Events are queryable within seconds. Best for real-time analytics. Incurs streaming insert pricing.
• Load jobs: Events are batched into files and loaded periodically. Lower cost but higher latency (minutes). Best for cost-sensitive batch analytics.

Table Schema

CREATE TABLE event_analytics.raw_events (
    event_id STRING,
    outbox_name STRING,
    payload JSON,
    dedup_key STRING,
    operation STRING,
    published_at TIMESTAMP
)
PARTITION BY DATE(published_at)
CLUSTER BY outbox_name, operation;

Partitioning by date and clustering by common filter columns optimizes both cost and query performance.

Troubleshooting

• "Access Denied" — Service account needs roles/bigquery.dataEditor on the dataset
• "Table not found" — Create the table and dataset before starting the pipeline
• "Streaming insert quota exceeded" — BigQuery has per-table streaming limits; use load jobs for very high volumes
• "Invalid rows" — Schema mismatch between event structure and table schema

Further Reading

• Snowflake — Multi-cloud data warehouse alternative
• Google Cloud Pub/Sub — GCP messaging for intermediate processing

Apache Iceberg

Apache Iceberg is an open table format for large-scale analytical datasets, designed to bring reliability and simplicity to data lakes. Unlike raw files on object storage, Iceberg provides ACID transactions, schema evolution, time travel, and partition evolution — features traditionally associated with data warehouses, but available on open storage like S3, GCS, and ADLS. When pg_tide delivers messages to Iceberg, your PostgreSQL events become part of a queryable lakehouse that can be accessed by Spark, Trino, Flink, Snowflake, BigQuery, and dozens of other engines.

When to Use This Sink

Choose Apache Iceberg when you want the cost efficiency of object storage with the reliability of a data warehouse, when you need multi-engine access to the same data (Spark for ETL, Trino for ad-hoc queries, Flink for streaming), or when vendor lock-in is a concern and you prefer open formats. Iceberg is the foundation of the modern lakehouse architecture and is supported by all major cloud providers and query engines.

Configuration

SELECT tide.relay_set_outbox(
    'events-to-iceberg',
    'events',
    'iceberg-relay',
    '{
        "sink_type": "iceberg",
        "catalog_type": "rest",
        "catalog_uri": "${env:ICEBERG_CATALOG_URI}",
        "warehouse": "s3://my-lake/warehouse",
        "namespace": "analytics",
        "table": "events",
        "batch_size": 1000
    }'::jsonb
);

Configuration Reference

Catalog Types

• REST Catalog — The most portable option. Works with Tabular, Polaris, and any REST-compatible catalog.
• AWS Glue — Native integration with AWS analytics services (Athena, EMR, Redshift Spectrum).
• Hive Metastore — For Hadoop-based environments with existing Hive infrastructure.

How It Works

The relay accumulates messages into batches and writes them as Parquet data files to object storage. Each batch becomes an Iceberg append commit, maintaining full ACID transactional semantics. This means:

• Partial writes never become visible (atomic commits)
• Concurrent readers always see a consistent snapshot
• Failed writes are automatically cleaned up
• Time travel lets you query the state at any point in history

Delivery Guarantees

At-least-once delivery. If the relay restarts mid-batch, the uncommitted data files are orphaned and cleaned up by Iceberg's periodic orphan file removal. The re-delivered messages create a new commit. For exact deduplication, include the dedup_key as a column and deduplicate at query time.

Debezium Compatibility

When combined with the Debezium wire format, the Iceberg sink produces CDC-compatible records that standard Iceberg CDC consumers (like the Iceberg Flink connector) can process for upsert/delete semantics:

{
    "sink_type": "iceberg",
    "wire_format": "debezium",
    "catalog_type": "rest",
    "catalog_uri": "http://catalog:8181",
    "namespace": "cdc",
    "table": "orders"
}

Troubleshooting

• "Catalog not found" — Verify catalog_uri is reachable and the catalog service is running
• "Namespace/Table not found" — Create the table first using Spark, Trino, or the catalog API
• "Access denied to storage" — Check S3/GCS/ADLS credentials and bucket policies
• "Commit conflict" — Another writer committed concurrently; the relay will retry automatically

Further Reading

• Delta Lake — Alternative open table format (Databricks ecosystem)
• DuckLake — Lightweight lakehouse with PostgreSQL catalog
• Object Storage — Raw file storage without table format

Delta Lake

Delta Lake is an open-source storage framework that brings ACID transactions and scalable metadata handling to data lakes. Originally created by Databricks, Delta Lake has become a foundational technology in the Databricks ecosystem and is widely adopted beyond it. When pg_tide delivers messages to Delta Lake, your PostgreSQL events are written as Parquet files with a transaction log that enables time travel, schema enforcement, and reliable upserts.

When to Use This Sink

Choose Delta Lake when your analytics platform is built on Databricks or Spark, when you need ACID transactions on object storage, or when you want to support both streaming and batch queries on the same dataset. Delta Lake's integration with Databricks Unity Catalog provides governance, lineage tracking, and fine-grained access control.

Configuration

SELECT tide.relay_set_outbox(
    'events-to-delta',
    'events',
    'delta-relay',
    '{
        "sink_type": "delta",
        "table_uri": "s3://my-lake/delta/events",
        "storage_options": {
            "AWS_ACCESS_KEY_ID": "${env:AWS_ACCESS_KEY_ID}",
            "AWS_SECRET_ACCESS_KEY": "${env:AWS_SECRET_ACCESS_KEY}",
            "AWS_REGION": "us-east-1"
        },
        "batch_size": 1000
    }'::jsonb
);

Configuration Reference

How It Works

Each batch of messages is written as a Parquet data file to the Delta table location. The relay then atomically commits the file to the Delta transaction log (_delta_log/). This ensures that readers always see complete, consistent batches. Failed or partial writes do not affect the table state.

Delivery Guarantees

At-least-once delivery. Delta Lake's transaction log is append-only, and each commit is atomic. If the relay crashes before committing, the orphaned Parquet file is ignored. Duplicates can be handled using Delta Lake's MERGE operations or by deduplication during downstream queries.

Troubleshooting

• "Access denied" — Check storage credentials in storage_options
• "Table does not exist" — Create the table first using Spark or delta-rs, or enable auto-creation
• "Conflict during commit" — Concurrent writers detected; relay retries automatically

Further Reading

• Apache Iceberg — Alternative open table format (broader engine support)
• DuckLake — Lightweight lakehouse alternative
• Object Storage — Raw file storage without table format

DuckLake

DuckLake is a novel lakehouse architecture that combines Parquet data files with a PostgreSQL metadata catalog. Unlike Iceberg or Delta Lake (which store metadata as JSON files alongside data), DuckLake uses a relational database (PostgreSQL) as the source of truth for table metadata, schema, and transaction history. This design makes metadata operations (listing tables, schema evolution, time travel) dramatically faster while keeping data in efficient Parquet format on any object storage.

When pg_tide delivers messages to DuckLake, your events are written as Parquet files while the catalog metadata is maintained in PostgreSQL — potentially even in the same PostgreSQL instance that hosts your outbox. This creates an elegantly simple architecture where your database manages both the events and their analytical storage metadata.

When to Use This Sink

Choose DuckLake when you want a lightweight lakehouse that integrates naturally with PostgreSQL, when you want to query event data with DuckDB (including from the command line or embedded in applications), or when you prefer the simplicity of a single PostgreSQL database managing both operational and analytical metadata. DuckLake is particularly compelling for smaller teams that want lakehouse capabilities without the operational complexity of running a separate catalog service.

Configuration

SELECT tide.relay_set_outbox(
    'events-to-ducklake',
    'events',
    'ducklake-relay',
    '{
        "sink_type": "ducklake",
        "catalog_url": "postgresql://localhost:5432/analytics",
        "data_path": "s3://my-lake/ducklake/events",
        "table": "raw_events",
        "batch_size": 1000,
        "storage_options": {
            "AWS_REGION": "us-east-1"
        }
    }'::jsonb
);

Configuration Reference

Querying DuckLake Data

Once events are written, you can query them with DuckDB:

-- Attach the DuckLake catalog
ATTACH 'ducklake:postgresql://localhost:5432/analytics' AS lake;

-- Query your events
SELECT * FROM lake.raw_events
WHERE event_type = 'order.created'
  AND published_at > '2024-01-01';

Troubleshooting

• "Catalog connection failed" — Verify the PostgreSQL URL is reachable from the relay
• "Storage access denied" — Check cloud storage credentials in storage_options
• "Table not found" — Create the DuckLake table first using DuckDB

Further Reading

• Apache Iceberg — For broader engine ecosystem support
• Delta Lake — For Databricks/Spark integration

MongoDB

MongoDB is a document-oriented database that stores data as flexible JSON-like documents. It is widely used for applications that need schema flexibility, horizontal scaling, and the ability to store nested, hierarchical data naturally. When pg_tide delivers messages to MongoDB, your PostgreSQL events are written as documents to a collection, where they can be queried with MongoDB's rich query language, aggregated for analytics, or used to maintain a read-optimized view of your data.

When to Use This Sink

Choose MongoDB when your downstream consumers use MongoDB as their primary data store, when you want to maintain a denormalized read model of your PostgreSQL data in a document database, or when you need flexible schema storage for event data that evolves rapidly. MongoDB's document model maps naturally to JSON event payloads without requiring a predefined schema.

Configuration

SELECT tide.relay_set_outbox(
    'events-to-mongo',
    'events',
    'mongo-relay',
    '{
        "sink_type": "mongodb",
        "connection_string": "${env:MONGODB_URI}",
        "database": "events",
        "collection": "outbox_events",
        "batch_size": 500,
        "write_concern": "majority"
    }'::jsonb
);

Configuration Reference

Upsert Mode

When upsert: true, the relay uses the dedup_key as the document _id. This provides natural deduplication — re-delivered messages update the existing document rather than creating duplicates. This is particularly useful for maintaining a current-state view of entities:

{
    "sink_type": "mongodb",
    "connection_string": "mongodb+srv://...",
    "database": "orders",
    "collection": "current_state",
    "upsert": true,
    "upsert_key": "dedup_key"
}

Delivery Guarantees

With write_concern: "majority", MongoDB acknowledges writes only after they are replicated to a majority of replica set members, providing durable at-least-once delivery. With upsert mode, re-delivery is idempotent.

Troubleshooting

• "Authentication failed" — Check credentials in the connection string and verify the user has write access
• "Connection timeout" — Verify network connectivity; for Atlas, ensure your IP is in the access list
• "Write concern timeout" — Replica set members are unavailable; check cluster health

Further Reading

• Elasticsearch — For full-text search use cases
• ClickHouse — For columnar analytics