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

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

ash added the

enhancement

production-ready

labels

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

ash referenced this issue from a commit

2026-02-19 22:48:27 +00:00

feat(pgstore): add PG clustering, LISTEN/NOTIFY, competing projections (#31, #6, #3)

ash commented

2026-02-20 08:06:36 +00:00

Author

Owner

Deferred to post-v1. Deferred: single Postgres instance works fine for v1. Clustering when we scale.

**Deferred to post-v1.** Deferred: single Postgres instance works fine for v1. Clustering when we scale.

ash commented

2026-02-20 08:15:21 +00:00

Author

Owner

Re-prioritized to CRITICAL. YoYoPass requires clustering for production scalability. Not optional.

**Re-prioritized to CRITICAL.** YoYoPass requires clustering for production scalability. Not optional.

ash commented

2026-02-20 08:16:58 +00:00

Author

Owner

Postgres clustering implemented: ClusterStore (read/write pool splitting), PgLockRegistry (advisory locks for distributed single writer), PartitionManager (competing projections with FOR UPDATE SKIP LOCKED, lease-based assignment). All tested on real Postgres.

ash closed this issue

2026-02-20 08:16:58 +00:00

ash commented

2026-02-20 08:17:05 +00:00

Author

Owner

Postgres clustering: ClusterStore, PgLockRegistry, PartitionManager implemented and tested.

Rows
Columns

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

What

Components

Single Writer (Distributed)

Notifications

Competing Projections

Partition Strategy

API

Tests