Why PostgreSQL for Everything

Status: Decided. 210 tables, 5 schemas, 11.5M+ rows.

Context

The system needs to store: relational personal data (messages, health records, calendar events, contacts, financial transactions), time-series metrics (Prometheus remote write is an option), vector embeddings for semantic search, configuration state for the manifest system, Home Assistant recorder history, Authelia session state, Grafana metadata, and n8n workflow execution history.

The conventional approach is to use purpose-built databases for each data type: a time-series database for metrics (InfluxDB, TimescaleDB), a vector database for embeddings (Pinecone, Weaviate, Qdrant), a key-value store for sessions and cache (Redis for cache is still here), and a relational database for everything else.

Decision

One PostgreSQL 16 instance handles everything except the Redis cache layer.

• pgvector extension provides vector storage and HNSW index for semantic search
• Schema isolation (public, memory, therapy, ha, brewing) organizes data domains
• LISTEN/NOTIFY provides the event bus for real-time dashboard SSE streaming
• n8n, Authelia, Grafana, and Home Assistant recorder all connect to the same Postgres instance, each with their own database or schema

The instance runs with the official pgvector image for Postgres 16, which extends the official Postgres 16 image with pgvector pre-installed.

What Was Rejected and Why

A separate vector database (Qdrant, Weaviate, Pinecone): The memory server stores 129K memories with 4096-dimensional embeddings. pgvector with binary quantization HNSW provides competitive recall at this scale. More importantly, it eliminates an operational dependency. Every additional database is a service to monitor, backup, upgrade, and debug. The memory data already lives in Postgres; a separate vector DB would require syncing data between stores and running two different healthcheck/backup strategies for the same semantic memory content. pgvector keeps it in one place.

The HNSW index uses binary quantization (binary_quantize(embedding)::bit(4096) with hamming distance) for the candidate pass, then exact cosine distance for reranking. This two-stage retrieval provides 95%+ recall at a fraction of the storage and compute cost of full-precision HNSW.

InfluxDB or TimescaleDB for metrics: Prometheus handles time-series metrics and owns its own storage. Sending metrics to Postgres as well would duplicate data. The Prometheus remote-write receiver is enabled for future use but not actively written to. The existing 210 tables in Postgres do not include raw metrics time-series.

SQLite per service: n8n supports SQLite as its backend. Authelia supports SQLite. Using SQLite for each service would be simpler to set up initially, but would mean multiple databases with no shared connection pool, no cross-database queries, and separate backup targets for each. Centralizing everything in Postgres means one backup covers all persistent state.

Redis for everything: Redis is present for the memory server’s BullMQ job queue and search-result cache (5-minute TTL), and for Authelia session storage. These are the right uses for Redis: ephemeral, high-throughput, loss-tolerant. Persistent personal data is not the right use for Redis, and it is not used that way.

Schema Isolation

The therapy schema is a hard isolation boundary. No application role other than the dedicated therapy role can read or write to it. It does not appear in cross-schema views like all_messages. This is enforced at the role level, not just by convention. The remote MCP role explicitly cannot access therapy tables.

This makes it possible to reason about data privacy at the schema boundary. Tier 1 data (therapy sessions, clinical records) stays in therapy.*. Tier 2 and Tier 3 data lives in public.*, memory.*, ha.*, brewing.*.

LISTEN/NOTIFY as an Event Bus

The dashboard streams real-time updates over Server-Sent Events. Rather than a separate message broker (RabbitMQ, Kafka, Pub/Sub), the dashboard API uses PostgreSQL NOTIFY triggered by database functions.

Channels in use: dashboard_events, dispatch_update, council_update, github_updated, entity_extraction. The Fastify API server listens on these channels via a persistent Postgres connection and forwards events to connected SSE clients.

At single-user scale, this works perfectly. The NOTIFY payload size limit (8KB) is sufficient for all current event types. If the system scaled to many concurrent users, a dedicated message broker would become necessary.

Consequences

What works well:

• One backup covers all persistent state. A single pg_dump of the primary database captures messages, health records, memories, calendar events, semantic vectors, n8n state, Authelia state, and Grafana metadata in a single operation.
• The role matrix (superuser, app, readonly, remote_rw, ha_role, grafana_role, therapy_app) provides fine-grained access control without an external auth layer.
• Cross-domain queries are possible. A query that joins health records with calendar events with messages to build a daily summary is a single SQL statement, not an ETL pipeline.
• pgvector’s two-stage HNSW retrieval handles 129K memories at 4096 dimensions with sub-100ms latency.

Tradeoffs accepted:

• 210 tables requires schema discipline. Tables are organized into logical groups and documented. The schema_migrations table provides a full audit trail of every schema change.
• Postgres is a single point of failure for most services. If Postgres goes down, n8n, the dashboard, Authelia, Grafana metadata, and the memory server all lose their backends simultaneously. The 6GB memory limit and conservative shared_buffers tuning reduce OOM risk. The hourly backup ensures recovery is possible.
• The memory server embedding pipeline (4096-dim vectors via qwen3-embedding:8b on Atlas) produces large index sizes. The HNSW index with binary quantization keeps this manageable, but it requires the pgvector binary quantization support added in pgvector 0.7+.