216 lines
11 KiB
Markdown
216 lines
11 KiB
Markdown
# Thread Pool PostgreSQL Optimization Plan
|
||
|
||
## Current State
|
||
|
||
### Thread Inventory
|
||
| Thread | Name | Role | PG Connection | Config Key |
|
||
|--------|------|------|--------------|------------|
|
||
| lws-main | `lws-main` | WebSocket event loop, HTTP, NIP-11, admin events, subscription matching, broadcast, monitoring event generation | `g_pg_conn` (main) | N/A — always runs |
|
||
| reader-1 | `db-read-1` | Async REQ and COUNT query execution | `g_thread_pg_conn` (TLS) | `thread_pool_readers` (default: 1) |
|
||
| writer | `db-write` | Async EVENT store, delete, sub logging, IP ban save, WAL checkpoint | `g_thread_pg_conn` (TLS) | N/A — always 1 |
|
||
| event-worker | `event-worker` | Async EVENT ingestion — duplicate check, signature validation, `store_event_core()`, ephemeral event handling | Own `worker_db` via `db_open_worker_connection()` | N/A — always 1 |
|
||
|
||
**Total: 4 threads, 4 PG connections**
|
||
|
||
### event-worker Thread (websockets.c — separate from thread pool)
|
||
|
||
The `event-worker` is a dedicated async pipeline for incoming EVENT messages, defined in [`src/websockets.c`](src/websockets.c:543). It is **not** part of the thread pool — it has its own job queue, mutex, and condvar.
|
||
|
||
**What it does for each incoming EVENT:**
|
||
1. Checks if event ID already exists in DB (`event_id_exists_in_db()`)
|
||
2. Validates the event with `nostr_validate_unified_request()` (signature check)
|
||
3. For ephemeral events (kind 20000-29999): marks for broadcast only, no storage
|
||
4. For regular events: calls `store_event_core()` to insert into PostgreSQL
|
||
5. Pushes a completion struct and wakes `lws-main` via `lws_cancel_service()`
|
||
6. `lws-main` picks up the completion, sends OK response, and broadcasts to subscribers
|
||
|
||
**Why it exists separately from the thread pool writer:**
|
||
- The thread pool writer handles generic DB operations (sub logging, IP bans, etc.)
|
||
- The event-worker handles the full EVENT ingestion pipeline including validation and duplicate detection — not just the DB insert
|
||
- It was added to move CPU-intensive signature verification off the main thread
|
||
|
||
**Relationship to thread pool writer:**
|
||
There is overlap — both can write to the events table. The event-worker calls `store_event_core()` directly, while the thread pool writer uses `db_insert_event_with_json()`. Some EVENT paths still go through the thread pool writer (sync path for admin/DM/protected events).
|
||
|
||
### What Runs on lws-main Today (Shouldn't Block)
|
||
- WebSocket accept/read/write for all Nostr clients
|
||
- JSON parsing of REQ/EVENT/CLOSE/COUNT messages
|
||
- Event signature verification (CPU-bound)
|
||
- Subscription matching and broadcast (iterates all subscriptions)
|
||
- NIP-11 HTTP responses
|
||
- NIP-42 auth challenge generation
|
||
- Admin event processing (kind 23456) — includes NIP-44 decrypt + config changes
|
||
- **Monitoring event generation** — queries DB, signs events, broadcasts (BLOCKS)
|
||
- **Sync config reads** — `get_config_value()` hits PG on main thread (BLOCKS)
|
||
|
||
### SQLite-Era Constraints (No Longer Apply)
|
||
- Single writer thread was required (SQLite serializes writes)
|
||
- WAL checkpoint job was required (SQLite-specific)
|
||
- Reader/writer separation was critical for avoiding SQLITE_BUSY
|
||
- `db_wal_checkpoint_truncate()` on writer shutdown — SQLite-only
|
||
|
||
---
|
||
|
||
## Changes for PostgreSQL
|
||
|
||
### Phase 1: Increase Reader Thread Count
|
||
|
||
**What:** Change default reader count from 1 to 4.
|
||
|
||
**Why:** With SQLite, multiple readers had diminishing returns due to file-level locking. PostgreSQL handles concurrent reads perfectly — each reader has its own connection and queries execute in parallel.
|
||
|
||
**Files:**
|
||
- `src/main.c` line ~2524: Change default from 1 to 4
|
||
- Config key `thread_pool_readers` already exists — just change the default
|
||
|
||
**Risk:** Low. Each reader opens its own PG connection via `db_open_worker_connection()`. PostgreSQL default `max_connections = 100` easily handles 4-8 readers.
|
||
|
||
### Phase 2: Allow Multiple Writer Threads
|
||
|
||
**What:** Make writer thread count configurable (default: 2). Convert the single `pthread_t writer` to an array like readers.
|
||
|
||
**Why:** PostgreSQL supports concurrent writes with row-level locking. The single-writer bottleneck was a SQLite constraint. With multiple writers, EVENT inserts from different clients can execute in parallel.
|
||
|
||
**Files:**
|
||
- `src/thread_pool.h`: Add `int writer_threads` to `thread_pool_config_t`
|
||
- `src/thread_pool.c`:
|
||
- Change `pthread_t writer` to `pthread_t* writers` + `int writer_count`
|
||
- `thread_pool_init()`: create N writer threads
|
||
- `thread_pool_shutdown()`: join all writer threads
|
||
- `writer_worker_main()`: set thread name to `db-write-N`
|
||
- `src/main.c`: Add config key `thread_pool_writers` (default: 2)
|
||
|
||
**Risk:** Medium. Need to verify that no write job assumes serialized execution. Current write jobs:
|
||
- `STORE_EVENT` — PG handles concurrent inserts with ON CONFLICT
|
||
- `LOG_SUB_CREATED/CLOSED/DISCONNECTED` — independent rows, safe
|
||
- `UPDATE_SUB_EVENTS_SENT` — updates by sub_id, safe
|
||
- `IP_BAN_SAVE` — writes to ip_bans table, safe
|
||
- `WAL_CHECKPOINT` — SQLite-only, skip for PG (see Phase 3)
|
||
|
||
All write jobs operate on independent rows or use conflict resolution. Multiple writers are safe.
|
||
|
||
### Phase 3: Remove SQLite-Specific WAL Checkpoint Logic
|
||
|
||
**What:** Make WAL checkpoint a no-op when running PostgreSQL backend.
|
||
|
||
**Why:** PostgreSQL manages its own WAL internally. The `db_wal_checkpoint_passive()` and `db_wal_checkpoint_truncate()` calls are SQLite-specific.
|
||
|
||
**Files:**
|
||
- `src/thread_pool.c`:
|
||
- `execute_wal_checkpoint_job()`: Return OK immediately if PG backend
|
||
- `writer_worker_main()` shutdown path: Skip `db_wal_checkpoint_truncate()` if PG backend
|
||
- `src/db_ops.c` or `src/db_ops_postgres.c`: Ensure WAL checkpoint functions are no-ops for PG
|
||
|
||
**Risk:** Low. These are already effectively no-ops since the PG backend doesn't implement them, but making it explicit avoids log noise.
|
||
|
||
### Phase 4: Move Monitoring Event Generation Off Main Thread
|
||
|
||
**What:** Run monitoring queries and event generation on a reader thread instead of lws-main.
|
||
|
||
**Why:** The monitoring system (`generate_monitoring_event_for_type()` in `src/api.c`) currently:
|
||
1. Queries PostgreSQL for stats (blocks main thread 5-50ms)
|
||
2. Creates and signs a Nostr event (CPU work on main thread)
|
||
3. Broadcasts to subscriptions (main thread)
|
||
|
||
Step 1 and 2 should happen on a worker thread. Only step 3 (broadcast) needs to happen on lws-main.
|
||
|
||
**Approach:** Add a new job type `THREAD_POOL_JOB_MONITORING_QUERY` to the read queue. The reader thread executes the query and prepares the event JSON. The completion callback on lws-main just broadcasts the pre-built event.
|
||
|
||
**Files:**
|
||
- `src/thread_pool.h`: Add `THREAD_POOL_JOB_MONITORING_QUERY` job type and payload struct
|
||
- `src/thread_pool.c`: Add `execute_monitoring_query_job()` handler
|
||
- `src/api.c`: Refactor `generate_monitoring_event_for_type()` to submit async job
|
||
- `src/websockets.c`: Handle monitoring completion in the event loop callback
|
||
|
||
**Risk:** Medium. The monitoring event signing requires the relay private key. Need to pass it to the worker thread or pre-compute the unsigned event and sign on completion.
|
||
|
||
### Phase 5: Add Configurable Thread Pool Sizing via Config Events
|
||
|
||
**What:** Allow thread pool reader/writer counts to be set via the admin config event system.
|
||
|
||
**Config keys:**
|
||
- `thread_pool_readers` — number of reader threads (default: 4)
|
||
- `thread_pool_writers` — number of writer threads (default: 2)
|
||
- `thread_pool_max_queue_depth` — max pending jobs per queue (default: 4096)
|
||
|
||
**Note:** Thread count changes require relay restart (cannot dynamically resize thread pool).
|
||
|
||
**Files:**
|
||
- `src/default_config_event.h`: Add defaults for new config keys
|
||
- `src/main.c`: Read config values when initializing thread pool
|
||
|
||
**Risk:** Low. Config keys already work this way for `thread_pool_readers`.
|
||
|
||
### Phase 6: Fix Error Messages (Cosmetic)
|
||
|
||
**What:** Update SQLite-specific error messages in thread pool to be backend-neutral.
|
||
|
||
**Files:**
|
||
- `src/thread_pool.c` line 508: "Reader worker failed to open SQLite connection" → "Reader worker failed to open database connection"
|
||
- `src/thread_pool.c` line 530: "Writer worker failed to open SQLite connection" → "Writer worker failed to open database connection"
|
||
|
||
**Risk:** None.
|
||
|
||
---
|
||
|
||
## Target Thread Layout After Implementation
|
||
|
||
```
|
||
c_relay_pg process
|
||
├── lws-main — WebSocket event loop (never blocks on DB)
|
||
├── db-read-1 — Async REQ/COUNT/monitoring queries
|
||
├── db-read-2 — Async REQ/COUNT/monitoring queries
|
||
├── db-read-3 — Async REQ/COUNT/monitoring queries
|
||
├── db-read-4 — Async REQ/COUNT/monitoring queries
|
||
├── event-worker — Async EVENT ingestion (validate + store)
|
||
├── db-write-1 — Async sub logging, IP bans, misc writes
|
||
└── db-write-2 — Async sub logging, IP bans, misc writes
|
||
```
|
||
|
||
**Total: 8 threads, 8 PG connections** (configurable)
|
||
|
||
---
|
||
|
||
## PostgreSQL Connection Budget
|
||
|
||
| Component | Connections | Notes |
|
||
|-----------|------------|-------|
|
||
| lws-main | 1 | Sync config reads, admin event processing |
|
||
| Reader threads (4) | 4 | One per reader |
|
||
| event-worker | 1 | EVENT ingestion pipeline |
|
||
| Writer threads (2) | 2 | Sub logging, IP bans, misc |
|
||
| **Total per instance** | **8** | Well within PG default max_connections=100 |
|
||
| Future: admin-ws | +1 | When/if added later |
|
||
| Future: pg-listener | +1 | When/if added later |
|
||
| **Future total** | **10** | Still very comfortable |
|
||
|
||
For load balancing with N relay instances: N × 8 connections. With 10 instances = 80 connections. PgBouncer recommended at that scale.
|
||
|
||
---
|
||
|
||
## Implementation Order
|
||
|
||
| Phase | Description | Complexity | Impact |
|
||
|-------|-------------|-----------|--------|
|
||
| 1 | Increase reader count to 4 | Trivial — one line change | High — parallel query execution |
|
||
| 2 | Multiple writer threads | Medium — refactor writer array | Medium — parallel event inserts |
|
||
| 3 | Remove WAL checkpoint logic | Low — conditional no-op | Low — cleaner code |
|
||
| 4 | Move monitoring off main thread | Medium — new job type + async flow | High — unblocks event loop |
|
||
| 5 | Config event integration | Low — add config keys | Low — operational convenience |
|
||
| 6 | Fix error messages | Trivial | None — cosmetic |
|
||
|
||
**Recommended start:** Phase 1 + 6 (trivial, immediate benefit), then Phase 3 (cleanup), then Phase 4 (biggest architectural improvement), then Phase 2 (nice-to-have), then Phase 5 (polish).
|
||
|
||
---
|
||
|
||
## Future: Load Balancing Considerations
|
||
|
||
The thread pool design is already compatible with horizontal scaling because:
|
||
|
||
1. **No shared in-process state between instances** — all state is in PostgreSQL
|
||
2. **Subscription matching is per-instance** — each instance tracks its own connected clients
|
||
3. **Cross-instance event notification** via PostgreSQL LISTEN/NOTIFY (future phase)
|
||
4. **Connection pooling** via PgBouncer when connection count becomes a concern
|
||
|
||
The only addition needed for multi-instance is a LISTEN/NOTIFY subscriber thread that receives "new event stored" notifications from PostgreSQL and triggers subscription matching on the local instance. This is the `pg-listener` thread discussed earlier — it can be added independently of the thread pool changes in this plan.
|