Postgres cluster support: advisory locks, LISTEN/NOTIFY, competing projections #31

New issue

Open

opened 2026-02-19 22:28:40 +00:00 by ash · 0 comments

ash commented

2026-02-19 22:28:40 +00:00

Owner

What

Full clustering support using only Postgres. No NATS required.

Components

Single Writer (Distributed)

pg_advisory_xact_lock(hash(stream_id)) — one instance per stream
Lock auto-releases on crash/disconnect
Fallback: multi-writer with OCC if advisory lock fails

Notifications

LISTEN/NOTIFY on event append
Each instance listens on eskit_events channel
Payload: global sequence number
Auto-reconnect on connection loss

Competing Projections

Partition assignment table
SELECT ... FOR UPDATE SKIP LOCKED for claiming partitions
Heartbeat/lease renewal
Instance dies → partitions released → others claim them

Partition Strategy

By tenant ID (multi-tenant apps)
By stream ID hash (general purpose)
By entity type (domain-based)
Configurable

API

cluster := pgstore.NewCluster(db, pgstore.ClusterConfig{
    InstanceID:    "node-1",
    SingleWriter:  true,
    Partitions:    8,
    LeaseTimeout:  30 * time.Second,
    Notifier:      pgstore.ListenNotify(),
})

Tests

3 instances, single writer per stream guaranteed
Instance kill → lock releases → another takes over
LISTEN/NOTIFY: event written → all instances notified
Competing projections: each event processed exactly once
Partition rebalancing on scale up/down
Connection loss recovery

## What Full clustering support using only Postgres. No NATS required. ## Components ### Single Writer (Distributed) - `pg_advisory_xact_lock(hash(stream_id))` — one instance per stream - Lock auto-releases on crash/disconnect - Fallback: multi-writer with OCC if advisory lock fails ### Notifications - `LISTEN/NOTIFY` on event append - Each instance listens on `eskit_events` channel - Payload: global sequence number - Auto-reconnect on connection loss ### Competing Projections - Partition assignment table - `SELECT ... FOR UPDATE SKIP LOCKED` for claiming partitions - Heartbeat/lease renewal - Instance dies → partitions released → others claim them ### Partition Strategy - By tenant ID (multi-tenant apps) - By stream ID hash (general purpose) - By entity type (domain-based) - Configurable ## API ```go cluster := pgstore.NewCluster(db, pgstore.ClusterConfig{ InstanceID: "node-1", SingleWriter: true, Partitions: 8, LeaseTimeout: 30 * time.Second, Notifier: pgstore.ListenNotify(), }) ``` ## Tests - 3 instances, single writer per stream guaranteed - Instance kill → lock releases → another takes over - LISTEN/NOTIFY: event written → all instances notified - Competing projections: each event processed exactly once - Partition rebalancing on scale up/down - Connection loss recovery