c-relay/postgres_relays.md

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.

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

1. Prioritize Denormalization: Do not rely solely on JSONB queries for high-traffic filters. Extract indexed tags into dedicated columns or tables.
2. 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.
3. 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.
4. 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.

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
);