laantungir/c-relay-pg

Fork 0

Files

T

Laan Tungir 5577b57149 v2.1.9 - Rebrand repository, docs, scripts, and tooling from c-relay to c-relay-pg

2026-04-02 07:06:41 -04:00

22 KiB

Raw Permalink Blame History

c-relay-pg-pg Plan — PostgreSQL Backend + Multi-Instance + Dashboard

Overview

c-relay-pg-pg is a new project (separate repository) forked from c-relay-pg after the db_ops abstraction layer is complete. It replaces the SQLite backend with PostgreSQL, enabling horizontal scaling, external dashboards, and production-grade deployments.

Prerequisites

The db_ops.h / db_ops.c abstraction layer must be complete in c-relay-pg first — see c-relay-pg thread pool plan Phase 1.
The fork happens after Phase 1 of that plan is done and tested.

Why a Separate Project?

	c-relay-pg	c-relay-pg-pg
Database	SQLite (embedded)	PostgreSQL (client-server)
Deployment	Single binary + DB file	Binary + PostgreSQL server
Scaling	Single instance	Multiple instances + load balancer
Dashboard	Embedded web UI	Separate web server + Grafana
Target user	Personal relay, small community	Medium-large relay, production
Complexity	Minimal	More infrastructure

For the full analysis of why PostgreSQL was chosen over MySQL/MariaDB and other databases, see the database architecture analysis.

Architecture

flowchart TD
    subgraph c-relay-pg-pg - Production Deployment
        LB[nginx - TLS + load balancing] -->|WebSocket| R1[c-relay-pg-pg Instance 1]
        LB -->|WebSocket| R2[c-relay-pg-pg Instance 2]
        LB -->|HTTP| DASH[Dashboard]
        R1 -->|db_ops API| PGBACK[PostgreSQL Backend - db_ops_postgres.c]
        R2 -->|db_ops API| PGBACK
        PGBACK -->|libpq| PG[PostgreSQL Server]
        DASH -->|SQL| PG
        R1 <-->|LISTEN/NOTIFY| R2
    end

Phase 1: PostgreSQL Backend

Goal

Fork c-relay-pg into a new repository called c-relay-pg-pg. Replace the SQLite db_ops implementation with PostgreSQL using libpq. The db_ops.h interface stays identical — only the backend changes.

New Files

src/db_ops_sqlite.c — Renamed from db_ops.c (the Phase 1 SQLite implementation from c-relay-pg)
src/db_ops_postgres.c — New PostgreSQL implementation
src/db_ops.c — Thin dispatcher that calls the active backend

PostgreSQL Schema

-- PostgreSQL schema for c-relay-pg-pg
-- No event_tags table needed — JSONB + GIN handles everything

CREATE TABLE events (
    id TEXT PRIMARY KEY,
    pubkey TEXT NOT NULL,
    created_at BIGINT NOT NULL,
    kind INTEGER NOT NULL,
    event_type TEXT NOT NULL CHECK (event_type IN ('regular', 'replaceable', 'ephemeral', 'addressable')),
    content TEXT NOT NULL,
    sig TEXT NOT NULL,
    tags JSONB NOT NULL DEFAULT '[]',
    event_json TEXT NOT NULL,
    first_seen BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT
);

-- Core indexes
CREATE INDEX idx_events_pubkey ON events(pubkey);
CREATE INDEX idx_events_kind ON events(kind);
CREATE INDEX idx_events_created_at ON events(created_at DESC);
CREATE INDEX idx_events_kind_created_at ON events(kind, created_at DESC);
CREATE INDEX idx_events_pubkey_created_at ON events(pubkey, created_at DESC);
CREATE INDEX idx_events_pubkey_kind ON events(pubkey, kind);

-- THE KEY INDEX: GIN on JSONB tags — replaces the entire event_tags table
CREATE INDEX idx_events_tags ON events USING GIN (tags);

-- Partial index: only non-ephemeral events (what REQ queries actually filter)
CREATE INDEX idx_events_non_ephemeral ON events(created_at DESC) 
    WHERE kind < 20000 OR kind >= 30000;

-- Config table
CREATE TABLE config (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    data_type TEXT NOT NULL CHECK (data_type IN ('string', 'integer', 'boolean', 'json')),
    description TEXT,
    category TEXT DEFAULT 'general',
    requires_restart INTEGER DEFAULT 0,
    created_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,
    updated_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT
);

-- Auth rules table
CREATE TABLE auth_rules (
    id SERIAL PRIMARY KEY,
    rule_type TEXT NOT NULL CHECK (rule_type IN ('whitelist', 'blacklist', 'rate_limit', 'auth_required', 'wot_whitelist')),
    pattern_type TEXT NOT NULL CHECK (pattern_type IN ('pubkey', 'kind', 'ip', 'global')),
    pattern_value TEXT,
    active INTEGER NOT NULL DEFAULT 1,
    created_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,
    updated_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT
);

CREATE INDEX idx_auth_rules_lookup ON auth_rules(rule_type, pattern_type, pattern_value) WHERE active = 1;

-- Relay private key storage
CREATE TABLE relay_seckey (
    private_key_hex TEXT NOT NULL CHECK (length(private_key_hex) = 64),
    created_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT
);

-- Subscription logging
CREATE TABLE subscriptions (
    id SERIAL PRIMARY KEY,
    subscription_id TEXT NOT NULL,
    wsi_pointer TEXT NOT NULL,
    client_ip TEXT NOT NULL,
    event_type TEXT NOT NULL CHECK (event_type IN ('created', 'closed', 'expired', 'disconnected')),
    filter_json TEXT,
    events_sent INTEGER DEFAULT 0,
    created_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,
    ended_at BIGINT,
    duration INTEGER,
    UNIQUE(subscription_id, wsi_pointer)
);

-- IP ban persistence
CREATE TABLE ip_bans (
    ip TEXT PRIMARY KEY,
    failure_count INTEGER NOT NULL DEFAULT 0,
    ban_count INTEGER NOT NULL DEFAULT 0,
    banned_until BIGINT NOT NULL DEFAULT 0,
    first_failure BIGINT NOT NULL DEFAULT 0,
    has_authed_successfully INTEGER NOT NULL DEFAULT 0,
    last_success_at BIGINT NOT NULL DEFAULT 0,
    total_connections INTEGER NOT NULL DEFAULT 0,
    total_failures INTEGER NOT NULL DEFAULT 0,
    total_successes INTEGER NOT NULL DEFAULT 0,
    first_seen BIGINT NOT NULL DEFAULT 0,
    idle_failure_count INTEGER NOT NULL DEFAULT 0,
    idle_ban_count INTEGER NOT NULL DEFAULT 0,
    idle_banned_until BIGINT NOT NULL DEFAULT 0,
    idle_first_failure BIGINT NOT NULL DEFAULT 0
);

-- Materialized views for dashboard (refreshed periodically)
CREATE MATERIALIZED VIEW event_kinds_mv AS
SELECT kind, COUNT(*) as count,
       ROUND(COUNT(*) * 100.0 / NULLIF((SELECT COUNT(*) FROM events), 0), 2) as percentage
FROM events GROUP BY kind;

CREATE MATERIALIZED VIEW time_stats_mv AS
SELECT 'total' as period, COUNT(*) as total_events, COUNT(DISTINCT pubkey) as unique_pubkeys
FROM events
UNION ALL
SELECT '24h', COUNT(*), COUNT(DISTINCT pubkey)
FROM events WHERE created_at >= EXTRACT(EPOCH FROM NOW())::BIGINT - 86400
UNION ALL
SELECT '7d', COUNT(*), COUNT(DISTINCT pubkey)
FROM events WHERE created_at >= EXTRACT(EPOCH FROM NOW())::BIGINT - 604800;

CREATE MATERIALIZED VIEW top_pubkeys_mv AS
SELECT pubkey, COUNT(*) as event_count,
       ROUND(COUNT(*) * 100.0 / NULLIF((SELECT COUNT(*) FROM events), 0), 2) as percentage
FROM events GROUP BY pubkey ORDER BY event_count DESC LIMIT 20;

-- Unique indexes required for CONCURRENTLY refresh
CREATE UNIQUE INDEX idx_event_kinds_mv ON event_kinds_mv(kind);
CREATE UNIQUE INDEX idx_time_stats_mv ON time_stats_mv(period);
CREATE UNIQUE INDEX idx_top_pubkeys_mv ON top_pubkeys_mv(pubkey);

Key Implementation Details

Tag Queries with JSONB

The biggest win — replacing the event_tags subquery with JSONB containment:

// SQLite (current): subquery into event_tags table
// AND id IN (SELECT event_id FROM event_tags WHERE tag_name = ? AND tag_value IN (?))

// PostgreSQL: JSONB containment operator with GIN index
// AND tags @> '[["p", "pubkey_hex"]]'

// For multiple tag values:
// AND (tags @> '[["p", "val1"]]' OR tags @> '[["p", "val2"]]')

This eliminates the entire event_tags table (4 million rows, ~2 GB indexes in the current SQLite schema). The GIN index on JSONB handles everything in ~100–200 MB.

LISTEN/NOTIFY Integration

For cross-instance event broadcasting:

// In db_ops_postgres.c:
int db_notify_new_event(const char* event_id) {
    char cmd[128];
    snprintf(cmd, sizeof(cmd), "NOTIFY new_event, '%s'", event_id);
    PGresult* res = PQexec(g_pg_conn, cmd);
    PQclear(res);
    return 0;
}

// Called from the lws event loop every iteration:
int db_check_notifications(char* event_id_out, size_t max_len) {
    PQconsumeInput(g_pg_notify_conn);
    PGnotify* notify = PQnotifies(g_pg_notify_conn);
    if (notify) {
        strncpy(event_id_out, notify->extra, max_len);
        PQfreemem(notify);
        return 1;  // Got a notification
    }
    return 0;  // No notifications
}

Connection Management

// db_ops_postgres.c connection pool (simple version)
#define DB_POOL_SIZE 4

static PGconn* g_pg_read_pool[DB_POOL_SIZE];  // For REQ queries
static PGconn* g_pg_write_conn;                // For EVENT inserts
static PGconn* g_pg_notify_conn;               // For LISTEN/NOTIFY
static pthread_mutex_t g_pool_lock;

PGconn* db_get_read_connection(void) {
    pthread_mutex_lock(&g_pool_lock);
    // Round-robin or find idle connection
    // ...
    pthread_mutex_unlock(&g_pool_lock);
}

Build System Changes

# Makefile additions
DB_BACKEND ?= sqlite  # Default to sqlite, override with: make DB_BACKEND=postgres

ifeq ($(DB_BACKEND),postgres)
    MAIN_SRC += src/db_ops.c src/db_ops_postgres.c
    LIBS += -lpq
    CFLAGS += -DDB_BACKEND_POSTGRES
else
    MAIN_SRC += src/db_ops.c src/db_ops_sqlite.c
    CFLAGS += -DDB_BACKEND_SQLITE
endif

Data Migration Tool

A standalone tool to migrate existing SQLite data to PostgreSQL:

# migrate_to_postgres.sh
# 1. Export events from SQLite
sqlite3 old_relay.db ".mode csv" "SELECT id,pubkey,created_at,kind,event_type,content,sig,tags,event_json,first_seen FROM events" > events.csv

# 2. Import into PostgreSQL
psql -d crelay -c "\COPY events FROM 'events.csv' WITH CSV"

# 3. Migrate config
sqlite3 old_relay.db ".mode csv" "SELECT key,value,data_type,description,category,requires_restart FROM config" > config.csv
psql -d crelay -c "\COPY config(key,value,data_type,description,category,requires_restart) FROM 'config.csv' WITH CSV"

# 4. Migrate auth rules
sqlite3 old_relay.db ".mode csv" "SELECT rule_type,pattern_type,pattern_value,active FROM auth_rules" > auth_rules.csv
psql -d crelay -c "\COPY auth_rules(rule_type,pattern_type,pattern_value,active) FROM 'auth_rules.csv' WITH CSV"

# 5. Refresh materialized views
psql -d crelay -c "REFRESH MATERIALIZED VIEW event_kinds_mv; REFRESH MATERIALIZED VIEW time_stats_mv; REFRESH MATERIALIZED VIEW top_pubkeys_mv;"

Phase 2: Multi-Instance + Dashboard

Goal

Run multiple c-relay-pg-pg instances behind a load balancer with a separate dashboard web server, all sharing the same PostgreSQL database.

Components

nginx config — WebSocket load balancing with ip_hash stickiness
systemd template — c-relay-pg-pg@.service for multiple instances
LISTEN/NOTIFY integration — Cross-instance event broadcasting in the lws_service() loop
PgBouncer — Connection pooling between c-relay-pg-pg instances and PostgreSQL
Dashboard — Separate web server querying PostgreSQL directly
Materialized view refresh — Background job to refresh analytics views

Multi-Instance Architecture

flowchart TD
    INTERNET[Internet - Nostr Clients] -->|wss://relay.example.com| NGINX[nginx reverse proxy - TLS termination + load balancing]
    
    NGINX -->|ws://localhost:8888| R1[c-relay-pg-pg Instance 1 - port 8888]
    NGINX -->|ws://localhost:8889| R2[c-relay-pg-pg Instance 2 - port 8889]
    NGINX -->|ws://localhost:8890| R3[c-relay-pg-pg Instance 3 - port 8890]
    NGINX -->|http://localhost:3000| DASHWEB[Dashboard Web Server]
    
    R1 -->|libpq connection pool| PGPOOL[PgBouncer - connection pooler]
    R2 -->|libpq connection pool| PGPOOL
    R3 -->|libpq connection pool| PGPOOL
    
    PGPOOL -->|pooled connections| PG[PostgreSQL Primary]
    DASHWEB -->|read queries| PG
    
    PG -->|streaming replication| REPLICA[Read Replica - optional]
    DASHWEB -.->|heavy analytics| REPLICA

nginx Load Balancing Config

# nginx.conf - WebSocket load balancing for c-relay-pg-pg
upstream relay_backends {
    # ip_hash ensures a client always hits the same instance
    # (important for WebSocket session stickiness)
    ip_hash;
    
    server 127.0.0.1:8888;
    server 127.0.0.1:8889;
    server 127.0.0.1:8890;
}

server {
    listen 443 ssl;
    server_name relay.example.com;
    
    # TLS config...
    
    location / {
        proxy_pass http://relay_backends;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
        proxy_set_header X-Real-IP $remote_addr;
        proxy_read_timeout 86400;  # Keep WebSocket alive for 24h
    }
    
    # Dashboard on separate path
    location /dashboard {
        proxy_pass http://127.0.0.1:3000;
    }
}

Session stickiness (ip_hash) ensures that once a client connects to Instance 2, all their subsequent WebSocket frames go to Instance 2. This is important because subscriptions are held in-memory per instance.

Starting Multiple Instances

Each instance is the same binary, just on a different port, all pointing to the same PostgreSQL:

# Instance 1
./build/c_relay_pg_x86 --port 8888 --db-host localhost --db-name crelay &

# Instance 2
./build/c_relay_pg_x86 --port 8889 --db-host localhost --db-name crelay &

# Instance 3
./build/c_relay_pg_x86 --port 8890 --db-host localhost --db-name crelay &

Or with systemd template units:

# /etc/systemd/system/c-relay-pg-pg@.service
[Unit]
Description=C-Relay-PG-PG Nostr Instance %i
After=postgresql.service

[Service]
ExecStart=/opt/c-relay-pg-pg/c_relay_pg_x86 --port %i --db-host localhost --db-name crelay
Restart=always
User=c-relay-pg

[Install]
WantedBy=multi-user.target

systemctl enable c-relay-pg-pg@8888 c-relay-pg-pg@8889 c-relay-pg-pg@8890
systemctl start c-relay-pg-pg@8888 c-relay-pg-pg@8889 c-relay-pg-pg@8890

Cross-Instance Event Broadcasting

When Instance 1 receives a new EVENT and stores it in PostgreSQL, Instance 2 and Instance 3 need to know about it so they can broadcast to their connected subscribers.

sequenceDiagram
    participant Client_A as Client A - connected to Instance 1
    participant I1 as Instance 1
    participant PG as PostgreSQL
    participant I2 as Instance 2
    participant I3 as Instance 3
    participant Client_B as Client B - connected to Instance 2
    participant Client_C as Client C - connected to Instance 3

    Client_A->>I1: EVENT - new kind:1 note
    I1->>PG: INSERT INTO events...
    PG-->>I1: OK
    I1->>I1: broadcast to local subscribers
    I1->>PG: NOTIFY new_event with event_id
    
    Note over PG: PostgreSQL delivers notification to all listeners
    
    PG-->>I2: NOTIFY: new_event event_id
    PG-->>I3: NOTIFY: new_event event_id
    
    I2->>PG: SELECT event_json FROM events WHERE id = event_id
    PG-->>I2: event JSON
    I2->>I2: match against local subscriptions
    I2->>Client_B: EVENT message - if subscription matches
    
    I3->>PG: SELECT event_json FROM events WHERE id = event_id
    PG-->>I3: event JSON
    I3->>I3: match against local subscriptions
    I3->>Client_C: EVENT message - if subscription matches

PostgreSQL LISTEN/NOTIFY Implementation

Built into PostgreSQL — no additional infrastructure needed:

// === In the relay's event loop (modified lws_service loop) ===

// Setup: create a dedicated connection for LISTEN
PGconn* notify_conn = PQconnectdb("host=localhost dbname=crelay");
PQexec(notify_conn, "LISTEN new_event");
int notify_fd = PQsocket(notify_conn);  // Get the socket fd for poll()

// After storing an event:
void on_event_stored(PGconn* write_conn, const char* event_id) {
    char notify_cmd[128];
    snprintf(notify_cmd, sizeof(notify_cmd),
             "NOTIFY new_event, '%s'", event_id);
    PQexec(write_conn, notify_cmd);
}

// In the main event loop (runs every lws_service iteration):
void check_cross_instance_events(PGconn* notify_conn) {
    // Non-blocking check for notifications
    PQconsumeInput(notify_conn);
    
    PGnotify* notify;
    while ((notify = PQnotifies(notify_conn)) != NULL) {
        // Another instance stored a new event
        const char* event_id = notify->extra;
        
        // Fetch the event and check against local subscriptions
        cJSON* event = db_get_event_by_id(event_id);
        if (event) {
            broadcast_event_to_subscriptions(event);
            cJSON_Delete(event);
        }
        
        PQfreemem(notify);
    }
}

Performance: LISTEN/NOTIFY adds ~1–5ms latency for cross-instance delivery. For a Nostr relay, this is imperceptible — clients already expect network latency.

Payload limit: NOTIFY payloads are limited to 8000 bytes. For event IDs (64 hex chars), this is fine.

Alternative: Redis Pub/Sub

If you later need even lower latency or more sophisticated routing:

// Using hiredis (Redis C client)
redisContext* redis = redisConnect("127.0.0.1", 6379);

// After storing event:
redisCommand(redis, "PUBLISH new_event %s", event_json);

// Subscriber (in each instance):
redisCommand(redis, "SUBSCRIBE new_event");
// Then poll for messages in the event loop

Redis adds sub-millisecond pub/sub but requires running a Redis server. For most relays, PostgreSQL LISTEN/NOTIFY is sufficient.

Connection Pooling with PgBouncer

Each relay instance needs multiple database connections. Without pooling, 3 instances × 10 connections = 30 PostgreSQL backend processes. With PgBouncer:

# /etc/pgbouncer/pgbouncer.ini
[databases]
crelay = host=127.0.0.1 port=5432 dbname=crelay

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
pool_mode = transaction    # Return connection to pool after each transaction
max_client_conn = 200      # Total connections from all relay instances
default_pool_size = 20     # Actual PostgreSQL connections

Relay instances connect to PgBouncer (port 6432) instead of PostgreSQL directly (port 5432). PgBouncer multiplexes 200 client connections onto 20 actual PostgreSQL connections.

Zero-Downtime Deployment

sequenceDiagram
    participant LB as nginx
    participant I1 as Instance 1 - v1.0
    participant I2 as Instance 2 - v1.0
    participant I3 as Instance 3 - v1.0
    participant I1_NEW as Instance 1 - v1.1

    Note over LB,I3: Normal operation: 3 instances serving traffic
    
    LB->>I1: Mark upstream as down
    Note over I1: Drain: wait for existing connections to close or timeout
    I1->>I1: Graceful shutdown
    
    Note over LB: Traffic now goes to I2 and I3 only
    
    I1_NEW->>I1_NEW: Start with new binary
    I1_NEW->>LB: Health check passes
    LB->>I1_NEW: Mark upstream as up
    
    Note over LB,I1_NEW: Instance 1 now running v1.1, repeat for I2 and I3

Rolling deploy script:

#!/bin/bash
# rolling_deploy.sh - Zero-downtime deployment

for port in 8888 8889 8890; do
    echo "Deploying instance on port $port..."
    
    # 1. Tell nginx to stop sending new connections
    sed -i "s/server 127.0.0.1:$port;/server 127.0.0.1:$port down;/" /etc/nginx/nginx.conf
    nginx -s reload
    
    # 2. Wait for existing connections to drain (30 seconds)
    sleep 30
    
    # 3. Stop old instance
    systemctl stop c-relay-pg-pg@$port
    
    # 4. Deploy new binary
    cp ./build/c_relay_pg_x86 /opt/c-relay-pg-pg/c_relay_pg_x86
    
    # 5. Start new instance
    systemctl start c-relay-pg-pg@$port
    
    # 6. Wait for health check
    sleep 5
    
    # 7. Re-enable in nginx
    sed -i "s/server 127.0.0.1:$port down;/server 127.0.0.1:$port;/" /etc/nginx/nginx.conf
    nginx -s reload
    
    echo "Instance on port $port deployed successfully"
done

Dashboard Options

With PostgreSQL, the dashboard can be built with any technology:

Grafana — Point directly at PostgreSQL, zero custom code
Custom web app — React/Vue frontend + any backend (Node, Python, Go) querying PostgreSQL
Embedded in c-relay-pg-pg — Keep current approach but queries go through db_ops to PostgreSQL (no longer blocks event loop since PostgreSQL handles concurrency)

Scaling Scenarios

Scenario	Instances	Connections	Events/hour	Setup
Current	1	~1,200	56	Single process + SQLite
Small upgrade	2	~2,500	500	2 instances + PostgreSQL
Medium relay	4	~5,000	5,000	4 instances + PostgreSQL + PgBouncer
Large relay	8	~10,000	50,000	8 instances + PostgreSQL + read replica
Multi-region	2–4 per region	~50,000	500,000	Multiple servers + PostgreSQL replication

Cost Perspective

Running multiple relay instances with PostgreSQL on a single VPS:

PostgreSQL: ~200–500 MB RAM baseline
PgBouncer: ~10 MB RAM
Each relay instance: ~50–100 MB RAM
Dashboard web server: ~50–100 MB RAM
4 instances + PostgreSQL + PgBouncer + dashboard: ~1–2 GB total RAM
A $20/month VPS with 4 cores and 4 GB RAM handles this easily
A $40/month VPS with 8 cores and 8 GB RAM handles 8 instances comfortably

Risk Mitigation

Risk	Mitigation
PostgreSQL connection failures	`db_ops` returns error codes. Relay logs errors but doesn't crash. Reconnection logic with exponential backoff.
Performance regression	Benchmark before/after. PostgreSQL should be faster for complex queries, similar for simple ones.
Data loss during migration	Migration tool is read-only on SQLite. PostgreSQL import is idempotent (INSERT ON CONFLICT).
SQLite fallback needed	Keep `db_ops_sqlite.c` working. Compile-time flag switches backends. Can always go back.
LISTEN/NOTIFY message loss	NOTIFY is transactional — if the INSERT commits, the NOTIFY is guaranteed. Missed notifications during reconnect handled by periodic full-sync.
PgBouncer adds latency	Transaction pooling mode adds <0.1ms. Negligible compared to query time.

Relationship to c-relay-pg

This project depends on the db_ops.h abstraction layer built in c-relay-pg thread pool plan Phase 1. The interface is identical — only the backend implementation changes:

c-relay-pg: db_ops.c → SQLite calls via sqlite3_*
c-relay-pg-pg: db_ops_postgres.c → PostgreSQL calls via libpq (PQexec, PQgetvalue, etc.)

The db_ops.h header file is shared between both projects. Changes to the interface should be coordinated.

22 KiB Raw Permalink Blame History Unescape Escape

c-relay-pg-pg Plan — PostgreSQL Backend + Multi-Instance + Dashboard

Overview

Prerequisites

Why a Separate Project?

Architecture

Phase 1: PostgreSQL Backend

Goal

New Files

PostgreSQL Schema

Key Implementation Details

Tag Queries with JSONB

LISTEN/NOTIFY Integration

Connection Management

Build System Changes

Data Migration Tool

Phase 2: Multi-Instance + Dashboard

Goal

Components

Multi-Instance Architecture

nginx Load Balancing Config

Starting Multiple Instances

Cross-Instance Event Broadcasting

PostgreSQL LISTEN/NOTIFY Implementation

Alternative: Redis Pub/Sub

Connection Pooling with PgBouncer

Zero-Downtime Deployment

Dashboard Options

Scaling Scenarios

Cost Perspective

Risk Mitigation

Relationship to c-relay-pg

22 KiB

Raw Permalink Blame History