PostgreSQL Nostr Relay Architecture Analysis

This document summarizes common approaches and best practices for building Nostr relays backed by PostgreSQL, based on industry-standard designs and existing open-source relay implementations.

Identified Projects

Nostrss: High-performance Rust-based relay.
Nostrgres: Focused on PostgreSQL integration with complex event filtering.
Relay-rs (Postgres branch): General purpose high-throughput relay.
Nostream (github.com/Cameri/nostream): Production-ready TypeScript relay backed by PostgreSQL and Redis.
Nostr-Relay-NestJS (github.com/CodyTseng/nostr-relay-nestjs): High-performance relay built with NestJS and PostgreSQL, utilizing Kysely for type-safe database interactions.

Detailed Analysis

1. Common Schema Patterns

Event Storage (JSONB): Most PostgreSQL-backed relays store the raw event as a JSONB object in a central events table. This provides flexibility for the evolving Nostr spec while allowing efficient extraction of fields.
Tag Denormalization: While the raw event is in JSONB, performant relays extract tags (like p, e, t, d) into a separate tags table with foreign key relationships to the events table. This avoids slow JSONB traversal during complex filter queries.
Author/Publisher Tracking: A dedicated authors or pubkeys table is typically used to maintain indexing on the pubkey field, enabling quick lookups of user activity.

2. Performance Optimization

GIN Indexes on JSONB: Crucial for filtering on specific event tags or custom properties stored within the event object. GIN indexes with jsonb_path_ops are generally preferred for equality checks.
Time-Series Partitioning: Given the append-only nature of Nostr, partitioning the events table by time (e.g., daily or weekly chunks) is highly recommended. This significantly improves query performance for recent data and simplifies data expiration/deletion.
Clustering: Clustering the events table by the timestamp index can reduce disk I/O, as it keeps temporally related events physically adjacent on the disk.

3. Schema Management Approaches

Migrations: Most mature relays utilize migration tools (like Flyway, Diesel migrations, or Golang-migrate) to version control database schema changes. This is critical for production stability.
Auto-generation: Some lightweight prototypes auto-generate schemas at startup. This is generally discouraged for production due to the risk of destructive changes, data loss, or blocking DDL operations on large tables.

Recommendations for Building a New Relay

Prioritize Denormalization: Do not rely solely on JSONB queries for high-traffic filters. Extract indexed tags into dedicated columns or tables.
Use Partitioning from Day One: Implementing native PostgreSQL partitioning (e.g., using pg_partman) is much easier before the dataset grows to millions of rows.
Connection Pooling: PostgreSQL requires aggressive connection management. Use a high-performance pooler like PgBouncer to handle the large number of concurrent, short-lived connections common in WebSocket-based relay traffic.
Asynchronous Writes: Decouple the WebSocket ingestion thread from the database writer thread to ensure that slow database writes do not impact the relay's responsiveness.

Nostream (github.com/Cameri/nostream)

A production-ready Nostr relay written in TypeScript (Node.js), backed by PostgreSQL 14+ and Redis. Uses Knex.js for versioned migrations.

PostgreSQL Schema

The schema is fully normalized — no JSONB blob for the whole event. Tags are stored separately and populated by a trigger.

events table — one row per event, tags kept as a JSONB column for the trigger to process:

CREATE TABLE events (
    id               UUID     PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_id         BYTEA    UNIQUE NOT NULL,  -- 32-byte hash
    event_pubkey     BYTEA    NOT NULL,
    event_kind       INTEGER  UNSIGNED NOT NULL,
    event_created_at INTEGER  UNSIGNED NOT NULL,
    event_content    TEXT     NOT NULL,
    event_tags       JSONB,                     -- raw tags array, source of truth for trigger
    event_signature  BYTEA    NOT NULL,
    remote_address   TEXT,
    expires_at       INTEGER,
    deleted_at       TIMESTAMP,
    event_deduplication JSONB,                 -- used for replaceable event conflict key
    first_seen       TIMESTAMP DEFAULT now()
);

-- Indexes
CREATE INDEX ON events (event_pubkey);
CREATE INDEX ON events (event_kind);
CREATE INDEX ON events (event_created_at);
CREATE INDEX ON events (expires_at);
CREATE INDEX event_tags_idx ON events USING GIN (event_tags);  -- GIN on raw JSONB

-- Unique index enforcing NIP-16 replaceable event semantics
CREATE UNIQUE INDEX replaceable_events_idx ON events (event_pubkey, event_kind)
    WHERE event_kind = 0 OR event_kind = 3
       OR (event_kind >= 10000 AND event_kind < 20000);

event_tags table — denormalized single-letter tags, populated by a PostgreSQL trigger on events:

CREATE TABLE event_tags (
    id         UUID  PRIMARY KEY DEFAULT uuid_generate_v4(),
    event_id   BYTEA NOT NULL,
    tag_name   TEXT  NOT NULL,   -- single-letter only (e.g. 'e', 'p', 't')
    tag_value  TEXT  NOT NULL
);

CREATE INDEX ON event_tags (event_id);
CREATE INDEX ON event_tags (tag_name, tag_value);

-- Trigger: on INSERT/UPDATE/DELETE to events, repopulate event_tags
CREATE OR REPLACE FUNCTION process_event_tags() RETURNS TRIGGER AS $$
DECLARE
  tag_element jsonb;
BEGIN
  DELETE FROM event_tags WHERE event_id = OLD.event_id;
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    FOR tag_element IN SELECT jsonb_array_elements(NEW.event_tags) LOOP
      IF length(trim('"' FROM (tag_element->0)::text)) = 1
         AND (tag_element->1)::text IS NOT NULL THEN
        INSERT INTO event_tags (event_id, tag_name, tag_value)
        VALUES (NEW.event_id,
                trim('"' FROM (tag_element->0)::text),
                trim('"' FROM (tag_element->1)::text));
      END IF;
    END LOOP;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_event_tags
AFTER INSERT OR UPDATE OR DELETE ON events
FOR EACH ROW EXECUTE FUNCTION process_event_tags();

Tag queries (#e, #p, etc.) join event_tags rather than traversing the JSONB array.

users and invoices tables — for paid relay admission (Lightning payments):

CREATE TABLE users (
    pubkey          BYTEA    PRIMARY KEY,
    is_admitted     BOOLEAN  DEFAULT FALSE,
    balance         BIGINT   DEFAULT 0,      -- in msats
    tos_accepted_at DATETIME
);

CREATE TABLE invoices (
    id               UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    pubkey           BYTEA NOT NULL,
    bolt11           TEXT  NOT NULL,
    amount_requested BIGINT UNSIGNED NOT NULL,
    amount_paid      BIGINT UNSIGNED,
    unit             ENUM('msats','sats','btc'),
    status           ENUM('pending','completed','expired'),
    description      TEXT,
    confirmed_at     DATETIME,
    expires_at       DATETIME
);

How Redis Is Used

Redis is not used for subscription fan-out. Subscription delivery is handled entirely in-process: each WebSocketAdapter holds a Map<subscriptionId, filters[]>, and a broadcast event walks all connected clients and filters locally.

Redis has two actual roles:

Sliding-window rate limiting — The SlidingWindowRateLimiter uses Redis sorted sets to track request timestamps per key (pubkey, IP, etc.) within a rolling time window:
- ZREMRANGEBYSCORE key 0 (now - period) — evict old entries
- ZADD key timestamp member — record this hit
- ZRANGE key 0 -1 — count hits in window
- EXPIRE key period — auto-cleanup
- Keys follow the pattern <pubkey>:events:<period> or <ip>:message:<period>
General-purpose cache — A RedisAdapter wraps the client with get/set/hasKey helpers, used for caching admission checks (e.g. <pubkey>:is-admitted). The event handler has a TODO comment noting that the userRepository.findByPubkey() call should be replaced with a cache lookup — meaning this is partially implemented.

Schema Definitions

Relay-rs (Postgres)

-- Events table
CREATE TABLE "event" (
	id bytea NOT NULL,
	pub_key bytea NOT NULL,
	created_at timestamp with time zone NOT NULL,
	kind integer NOT NULL,
	"content" bytea NOT NULL,
	hidden bit(1) NOT NULL DEFAULT 0::bit(1),
	delegated_by bytea NULL,
	first_seen timestamp with time zone NOT NULL DEFAULT now(),
	expires_at timestamp(0) with time zone,
	CONSTRAINT event_pkey PRIMARY KEY (id)
);
CREATE INDEX event_created_at_idx ON "event" (created_at,kind);
CREATE INDEX event_pub_key_idx ON "event" (pub_key);
CREATE INDEX event_delegated_by_idx ON "event" (delegated_by);
CREATE INDEX event_expires_at_idx ON "event" (expires_at);

-- Tags table
CREATE TABLE "tag" (
	id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
	event_id bytea NOT NULL,
	"name" varchar NOT NULL,
	value bytea NULL,
	value_hex bytea NULL,
	CONSTRAINT tag_fk FOREIGN KEY (event_id) REFERENCES "event"(id) ON DELETE CASCADE,
	CONSTRAINT unique_constraint_name UNIQUE (event_id, "name", value, value_hex)
);
CREATE INDEX tag_event_id_idx ON tag USING btree (event_id, name);
CREATE INDEX tag_value_idx ON tag USING btree (value);
CREATE INDEX tag_value_hex_idx ON tag USING btree (value_hex);

-- Account table
CREATE TABLE "account" (
    pubkey varchar NOT NULL,
    is_admitted BOOLEAN NOT NULL DEFAULT FALSE,
    balance BIGINT NOT NULL DEFAULT 0,
    tos_accepted_at TIMESTAMP,
    CONSTRAINT account_pkey PRIMARY KEY (pubkey)
);

-- Invoice table
CREATE TYPE status AS ENUM ('Paid', 'Unpaid', 'Expired');
CREATE TABLE "invoice" (
    payment_hash varchar NOT NULL,
    pubkey varchar NOT NULL,
    invoice varchar NOT NULL,
    amount BIGINT NOT NULL,
    status status NOT NULL DEFAULT 'Unpaid',
    description varchar,
    created_at timestamp,
    confirmed_at timestamp,
    CONSTRAINT invoice_payment_hash PRIMARY KEY (payment_hash),
    CONSTRAINT invoice_pubkey_fkey FOREIGN KEY (pubkey) REFERENCES account (pubkey) ON DELETE CASCADE
);

Nostr-Relay-NestJS (PostgreSQL via Kysely)

-- Events table
CREATE TABLE events (
    id            CHAR(64) PRIMARY KEY,
    pubkey        CHAR(64) NOT NULL,
    created_at    BIGINT   NOT NULL,
    kind          INTEGER  NOT NULL,
    tags          JSONB    NOT NULL DEFAULT '[]',
    generic_tags  TEXT[]   NOT NULL DEFAULT '{}',
    content       TEXT     NOT NULL DEFAULT '',
    sig           CHAR(128) NOT NULL,
    expired_at    BIGINT,
    d_tag_value   TEXT,
    delegator     CHAR(64),
    create_date   TIMESTAMP NOT NULL DEFAULT now(),
    update_date   TIMESTAMP NOT NULL DEFAULT now(),
    delete_date   TIMESTAMP
);

-- Indices
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE INDEX generic_tags_kind_idx ON events USING gin (generic_tags, kind);
CREATE INDEX pubkey_kind_created_at_idx ON events (pubkey, kind, created_at);
CREATE INDEX delegator_kind_created_at_idx ON events (delegator, kind, created_at);
CREATE INDEX created_at_kind_idx ON events (created_at, kind);

-- Generic tags table (for optimized tag queries)
CREATE TABLE generic_tags (
    id         SERIAL PRIMARY KEY,
    tag        TEXT    NOT NULL,
    author     CHAR(64) NOT NULL,
    kind       INTEGER  NOT NULL,
    event_id   CHAR(64) NOT NULL REFERENCES events(id),
    created_at BIGINT   NOT NULL
);

-- Indices for tag filtering
CREATE UNIQUE INDEX g_tag_event_id_idx ON generic_tags (tag, event_id);
CREATE INDEX g_tag_kind_created_at_idx ON generic_tags (tag, kind, created_at);
CREATE INDEX g_tag_created_at_idx ON generic_tags (tag, created_at);

Nostr-Relay-NestJS features a hybrid storage model where tags are stored both in a JSONB field (for general lookup) and a normalized generic_tags table (for optimized tag query performance). The schema uses Kysely for type-safe interaction.

12 KiB Raw Permalink Blame History