PostgreSQL

Production-grade PostgreSQL 16 with pgvector, partitioning, replication, PITR, and AI-ready extensions — tuned for nSelf from day one.

Quick Start

PostgreSQL starts automatically with nself start. No manual setup needed.

# Start full stack (PostgreSQL included)
nself start

# Open a psql shell
nself db shell

# Run a one-off query
nself db query "SELECT version();"

# Check PostgreSQL status
nself status | grep postgres

Access credentials are in your environment file:

# .env.dev (team defaults — never secrets)
POSTGRES_HOST=postgres
POSTGRES_PORT=5432
POSTGRES_DB=nself
POSTGRES_USER=nself
# POSTGRES_PASSWORD lives in .env.secrets (gitignored)

Architecture Overview

┌─────────────────────────────────────────────────────────┐
│                    nSelf Backend Stack                   │
│                                                         │
│  ┌──────────┐    ┌──────────┐    ┌─────────────────┐   │
│  │  Nginx   │───▶│  Hasura  │───▶│  PostgreSQL 16  │   │
│  │ (proxy)  │    │ GraphQL  │    │                 │   │
│  └──────────┘    └──────────┘    │ Extensions:     │   │
│                                  │  pgvector       │   │
│  ┌──────────┐    ┌──────────┐    │  pg_partman     │   │
│  │   Auth   │───▶│   Redis  │    │  pg_cron        │   │
│  │ (nHost)  │    │ (cache)  │    │  uuid-ossp      │   │
│  └──────────┘    └──────────┘    │  pgcrypto       │   │
│                                  │  pg_trgm        │   │
│  ┌──────────┐                    │  pg_stat_stmts  │   │
│  │  MinIO   │    All services    │  btree_gin      │   │
│  │(storage) │    bind 127.0.0.1  └─────────────────┘   │
│  └──────────┘    Nginx = sole external gateway          │
└─────────────────────────────────────────────────────────┘

One PostgreSQL instance per nSelf deploy. App isolation is achieved via table prefixes (np_*) and Hasura roles — not multiple Postgres instances.

Extensions

All extensions are pre-installed and enabled in the nSelf Docker image. You do not need to run CREATE EXTENSION manually.

pgvector — AI Embeddings

Required for ɳClaw's infinite memory system. Stores and queries high-dimensional vectors for semantic search.

-- Already enabled. Use vector columns directly:
CREATE TABLE np_memories (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  content     TEXT NOT NULL,
  embedding   vector(1536),           -- OpenAI ada-002 dimension
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- HNSW index for approximate nearest-neighbor search (fast at scale)
CREATE INDEX ON np_memories USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- IVFFlat index (slower build, faster query for large sets)
CREATE INDEX ON np_memories USING ivfflat (embedding vector_l2_ops)
  WITH (lists = 100);

-- Semantic similarity search
SELECT id, content, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM np_memories
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 20;

pg_partman — Automatic Partitioning

Time-based partitioning for high-volume tables like telemetry, events, and audit logs.

-- Create a partitioned telemetry table
CREATE TABLE np_telemetry (
  id          UUID NOT NULL DEFAULT gen_random_uuid(),
  event_type  TEXT NOT NULL,
  payload     JSONB,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Hand off partition management to pg_partman
SELECT partman.create_parent(
  p_parent_table   => 'public.np_telemetry',
  p_control        => 'created_at',
  p_type           => 'native',
  p_interval       => 'monthly',
  p_premake        => 3               -- create 3 months ahead
);

-- Maintenance runs via pg_cron (see below). Manual run:
CALL partman.run_maintenance_proc();

-- Retention: drop partitions older than 12 months
UPDATE partman.part_config
SET retention = '12 months', retention_keep_table = false
WHERE parent_table = 'public.np_telemetry';

pg_cron — Scheduled Jobs

-- Schedule partition maintenance daily at 02:00
SELECT cron.schedule('partition-maintenance', '0 2 * * *',
  $$CALL partman.run_maintenance_proc()$$);

-- Schedule soft-delete cleanup weekly
SELECT cron.schedule('purge-deleted', '0 3 * * 0',
  $$DELETE FROM np_users WHERE deleted_at < NOW() - INTERVAL '90 days'$$);

-- Schedule embedding index rebuild monthly
SELECT cron.schedule('reindex-vectors', '0 4 1 * *',
  $$REINDEX INDEX CONCURRENTLY np_memories_embedding_hnsw_idx$$);

-- List all scheduled jobs
SELECT * FROM cron.job;

-- Unschedule
SELECT cron.unschedule('partition-maintenance');

Other Key Extensions

nSelf Table Conventions

All nSelf-managed tables use the np_ prefix. This isolates nSelf system tables from user-created tables and enables per-app isolation within one Postgres instance.

Standard Column Set

CREATE TABLE np_example (
  -- Primary key: always UUID, always gen_random_uuid()
  id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Multi-app isolation: separates independent apps in one deploy
  -- See: Multi-Tenancy docs for Convention Wall details
  source_account_id  TEXT NOT NULL DEFAULT 'primary',

  -- Cloud SaaS tenancy (nullable — only set for nSelf Cloud customers)
  -- NEVER mix this with source_account_id
  tenant_id          UUID,

  -- Audit timestamps: always TIMESTAMPTZ, always NOT NULL for created_at
  created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Soft-delete: NULL = active, timestamp = deleted
  deleted_at         TIMESTAMPTZ,

  -- Your columns here
  name               TEXT NOT NULL
);

-- Auto-update updated_at on every write
CREATE TRIGGER np_example_updated_at
  BEFORE UPDATE ON np_example
  FOR EACH ROW EXECUTE FUNCTION np_set_updated_at();

Foreign Key Index Audit

Every foreign key must have a corresponding index. Missing FK indexes cause full sequential scans on joins. Run this audit after every schema migration:

-- Find foreign keys WITHOUT a supporting index
SELECT
  tc.table_name,
  kcu.column_name,
  ccu.table_name AS foreign_table,
  ccu.column_name AS foreign_column
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND NOT EXISTS (
  SELECT 1 FROM pg_indexes
  WHERE tablename = tc.table_name
  AND indexdef LIKE '%' || kcu.column_name || '%'
)
ORDER BY tc.table_name;

Soft-Delete Pattern

-- Soft delete (never hard DELETE in production)
UPDATE np_users SET deleted_at = NOW() WHERE id = $1;

-- Queries must always filter deleted rows
SELECT * FROM np_users WHERE deleted_at IS NULL AND id = $1;

-- Partial index: makes active-record queries fast, ignores deleted rows
CREATE INDEX idx_np_users_active ON np_users (id)
  WHERE deleted_at IS NULL;

-- RLS policy: hide deleted rows from GraphQL automatically
CREATE POLICY "hide_deleted" ON np_users
  USING (deleted_at IS NULL);

-- Permanent purge job (run via pg_cron after retention period)
DELETE FROM np_users
WHERE deleted_at < NOW() - INTERVAL '90 days';

Performance Tuning

Memory Settings

nSelf applies sensible defaults. Override in .env.dev or .env.prod:

# Recommended for 4GB RAM VPS (Hetzner CX23)
POSTGRES_SHARED_BUFFERS=1GB          # 25% of RAM
POSTGRES_EFFECTIVE_CACHE_SIZE=3GB   # 75% of RAM
POSTGRES_WORK_MEM=16MB              # Per sort/hash operation
POSTGRES_MAINTENANCE_WORK_MEM=256MB # VACUUM, CREATE INDEX

# For 8GB+ RAM VPS (Hetzner CX33)
POSTGRES_SHARED_BUFFERS=2GB
POSTGRES_EFFECTIVE_CACHE_SIZE=6GB
POSTGRES_WORK_MEM=32MB
POSTGRES_MAINTENANCE_WORK_MEM=512MB

Connection Pooling with PgBouncer

# Enable PgBouncer (transaction-mode pooling)
nself plugin install pgbouncer
nself build && nself start

# Environment config
PGBOUNCER_MAX_CLIENT_CONN=200
PGBOUNCER_DEFAULT_POOL_SIZE=20
PGBOUNCER_POOL_MODE=transaction

Slow Query Detection

-- Top 10 slowest queries (requires pg_stat_statements)
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round((total_exec_time / calls)::numeric, 2) AS avg_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Reset stats after tuning
SELECT pg_stat_statements_reset();

VACUUM and Autovacuum

-- Check bloat / autovacuum health
SELECT
  schemaname,
  relname AS table,
  n_dead_tup AS dead_rows,
  n_live_tup AS live_rows,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Manual VACUUM on a hot table
VACUUM ANALYZE np_telemetry;

-- Aggressive bloat removal (locks table briefly)
VACUUM FULL ANALYZE np_old_table;

Indexing Strategy

-- B-tree (default): equality, range, ORDER BY
CREATE INDEX idx_np_users_email ON np_users (email);

-- Partial index: filter frequent subsets (active records, unpaid invoices)
CREATE INDEX idx_np_users_active_email
  ON np_users (email)
  WHERE deleted_at IS NULL;

-- Composite: match multi-column WHERE clauses
CREATE INDEX idx_np_messages_conv_created
  ON np_messages (conversation_id, created_at DESC);

-- GIN: JSONB containment, full-text tsvector, array overlap
CREATE INDEX idx_np_docs_body_fts
  ON np_documents USING gin(to_tsvector('english', body));

CREATE INDEX idx_np_config_data
  ON np_config USING gin(data jsonb_path_ops);

-- HNSW: pgvector approximate nearest neighbor
CREATE INDEX idx_np_memories_vec
  ON np_memories USING hnsw (embedding vector_cosine_ops);

-- BRIN: very large append-only tables (time-series, logs)
CREATE INDEX idx_np_events_created_brin
  ON np_events USING brin(created_at);

-- Find unused indexes (wasted write overhead)
SELECT schemaname, relname AS table, indexrelname AS index,
       idx_scan AS scans, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Row-Level Security

RLS enforces data isolation at the database level — even if Hasura permissions have a bug, users cannot see other users' data.

-- Enable RLS on every multi-tenant table
ALTER TABLE np_users ENABLE ROW LEVEL SECURITY;
ALTER TABLE np_documents ENABLE ROW LEVEL SECURITY;

-- Policy: users see only their own rows
CREATE POLICY "own_rows" ON np_users
  USING (id = current_setting('request.jwt.claims', true)::json->>'sub'::uuid);

-- Policy: tenant isolation for Cloud SaaS
CREATE POLICY "tenant_isolation" ON np_documents
  USING (tenant_id = current_setting('hasura.user')::json->>'x-hasura-tenant-id'::uuid);

-- Policy: soft-delete visibility
CREATE POLICY "hide_deleted" ON np_users
  USING (deleted_at IS NULL);

-- Verify RLS is active
SELECT tablename, rowsecurity FROM pg_tables
WHERE schemaname = 'public' AND rowsecurity = true;

Backup and PITR

Continuous Backup with WAL Archiving

# Configure WAL archiving (in .env.prod)
POSTGRES_WAL_LEVEL=replica
POSTGRES_ARCHIVE_MODE=on
POSTGRES_ARCHIVE_COMMAND='nself-wal-archive %p %f'  # ships to MinIO/S3

# Take a base backup
nself db backup                    # Creates timestamped dump
nself db backup --format=tar       # Tar format (faster restore)
nself db backup --dest=s3://bucket # Ship directly to S3

# List available backups
nself db backup --list

# Restore from backup
nself db restore --file=backup-2026-05-07.dump
nself db restore --point-in-time="2026-05-07 14:30:00 UTC"  # PITR

Point-in-Time Recovery

# PITR workflow:
# 1. Stop production
nself stop

# 2. Restore base backup to new location
nself db restore --base-backup=2026-05-07T00:00:00Z --dest=/var/lib/postgresql/recovery

# 3. Write recovery config (generated by nself db restore --pitr)
# recovery.conf:
#   restore_command = 'nself-wal-restore %f %p'
#   recovery_target_time = '2026-05-07 14:30:00 UTC'
#   recovery_target_action = promote

# 4. Start PostgreSQL in recovery mode — it replays WAL to target time
nself start --recovery

# 5. Verify data, then promote replica to primary
nself db promote

Replication

# Streaming replication (hot standby for reads + failover)
# Primary .env.prod
POSTGRES_REPLICATION_USER=replicator
POSTGRES_REPLICATION_SLOTS=1
POSTGRES_MAX_WAL_SENDERS=3

# Standby server — run once to clone primary
nself db replica --init --primary=primary.host:5432

# Check replication lag
nself db replica --status

# Promote standby (on primary failure)
nself db replica --promote

-- Replication lag monitoring
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

Full-Text Search

-- Add tsvector column + GIN index for fast FTS
ALTER TABLE np_documents ADD COLUMN fts_vector tsvector;

UPDATE np_documents
SET fts_vector = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));

CREATE INDEX idx_np_docs_fts ON np_documents USING gin(fts_vector);

-- Keep it current with a trigger
CREATE FUNCTION np_documents_fts_update() RETURNS trigger AS $$
BEGIN
  NEW.fts_vector := to_tsvector('english', coalesce(NEW.title,'') || ' ' || coalesce(NEW.body,''));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER np_documents_fts
  BEFORE INSERT OR UPDATE ON np_documents
  FOR EACH ROW EXECUTE FUNCTION np_documents_fts_update();

-- Search with ranking
SELECT id, title, ts_rank(fts_vector, query) AS rank
FROM np_documents, to_tsquery('english', 'nself & backend') query
WHERE fts_vector @@ query
ORDER BY rank DESC
LIMIT 20;

-- Fuzzy search with pg_trgm
SELECT id, name FROM np_users
WHERE similarity(name, 'Alic') > 0.3
ORDER BY similarity(name, 'Alic') DESC;

JSONB Patterns

-- Store arbitrary config per record
CREATE TABLE np_plugin_config (
  plugin_name   TEXT PRIMARY KEY,
  settings      JSONB NOT NULL DEFAULT '{}'
);

-- GIN index for containment queries
CREATE INDEX idx_plugin_config_settings
  ON np_plugin_config USING gin(settings jsonb_path_ops);

-- Containment query (uses GIN index)
SELECT * FROM np_plugin_config
WHERE settings @> '{"enabled": true}';

-- Path query
SELECT settings->'smtp'->>'host' AS smtp_host
FROM np_plugin_config WHERE plugin_name = 'mail';

-- Merge/update nested key without overwriting siblings
UPDATE np_plugin_config
SET settings = settings || '{"debug": true}'
WHERE plugin_name = 'ai';

-- JSONPath (PostgreSQL 12+)
SELECT * FROM np_plugin_config
WHERE jsonb_path_exists(settings, '$.smtp.port ? (@ > 500)');

Monitoring

-- Table sizes (top 10 largest)
SELECT
  relname AS table,
  pg_size_pretty(pg_total_relation_size(oid)) AS total,
  pg_size_pretty(pg_relation_size(oid)) AS data,
  pg_size_pretty(pg_total_relation_size(oid) - pg_relation_size(oid)) AS indexes
FROM pg_class
WHERE relkind = 'r' AND relname LIKE 'np_%'
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 10;

-- Active connections
SELECT count(*), state, wait_event_type, wait_event
FROM pg_stat_activity
GROUP BY state, wait_event_type, wait_event
ORDER BY count DESC;

-- Lock contention
SELECT pid, query, state, wait_event, now() - query_start AS duration
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY duration DESC;

nSelf ships a pre-configured Grafana dashboard for PostgreSQL via the monitoring bundle (Prometheus + Postgres Exporter). Run nself status to see the dashboard URL.

Security

Best Practices

• Always use gen_random_uuid() for primary keys — not sequential integers. UUIDs prevent enumeration attacks and work across distributed systems.
• Never hand-edit docker-compose.yml — it is generated by nself build. Postgres config changes go in .env.dev / .env.prod.
• Never connect to Postgres directly from application code — all data access goes through Hasura GraphQL. Hasura enforces permissions.
• Soft-delete by default — never hard-DELETE in production unless you have explicit retention requirements.
• Index every foreign key — run the FK audit query after every migration.
• Partition tables over 50M rows — use pg_partman for time-based data (telemetry, events, logs).
• Test RLS policies with SET ROLE — verify that policies actually restrict data before deploying.
• Schedule VACUUM via pg_cron — don't rely solely on autovacuum for high-churn tables.

Related

• Hasura GraphQL — API layer over this database
• Multi-Tenancy — Convention Wall: source_account_id vs tenant_id
• Schema Design — np_* conventions, soft-delete, audit log
• Migrations — Drizzle migration workflow
• Database Management — Backup, restore, seeds, diff