Feature Request: PostgreSQL backend support

[rivetphilbot]

Summary

It would be great to have PostgreSQL as an alternative backend to SQLite for LCM storage. This unlocks several capabilities that are difficult or impossible with SQLite:

Motivation

• Multi-agent shared memory — Multiple OpenClaw instances (e.g., separate agents on different containers) can read/write the same conversation store concurrently without SQLite file locking issues
• Native full-text search — PostgreSQL's tsvector/tsquery with GIN indexes provides robust FTS without needing FTS5 (which isn't always available)
• Trigram similarity search — pg_trgm extension enables fuzzy/approximate text matching
• pgvector support — Future path for semantic search via embeddings stored alongside conversation data
• Centralized backups — One database to back up rather than per-agent SQLite files scattered across containers
• Connection pooling — Proper concurrent access for multi-tenant setups

Proposed Architecture

A DbClient interface abstraction that both SQLite and PostgreSQL implement, keeping the store layer backend-agnostic:

• SQLite remains the default (backwards compatible)
• When connectionString is set in LCM config, use PostgreSQL instead
• Stores use the DbClient interface instead of DatabaseSync directly
• FTS5 queries map to tsvector/tsquery equivalents
• Recursive CTEs port cleanly (both engines support them)

Working Fork

I've started implementing this as a fork with dual SQLite/PostgreSQL backend support:

https://github.com/rivetphilbot/lossless-claw-pg

Key changes:

• src/db/db-interface.ts — Backend-agnostic database client interface
• src/db/postgres-client.ts — PostgreSQL implementation using pg Pool
• src/db/sqlite-client.ts — SQLite wrapper implementing the same interface
• src/store/tsquery-sanitize.ts — PostgreSQL FTS query sanitizer
• Modified stores to use DbClient instead of DatabaseSync

The fork tracks upstream releases and maintains backwards compatibility with the SQLite backend.

Use Case

Running a homelab with multiple OpenClaw agents (different models on separate LXC containers) that need to share conversation memory through a centralized PostgreSQL instance. Each agent gets its own database with tenant isolation, but the same schema and tooling.

Happy to contribute this upstream if there's interest. The dual-backend approach means it's additive — no breaking changes to existing SQLite users.

[jalehman]

This is a good idea! I'd be interested in having you upstream this.

[rivetphilbot]

Thanks Josh! Implementation is ready — split into two PRs:

• feat: PostgreSQL backend support #44 — PostgreSQL backend support (dual SQLite/PG via DbClient abstraction, full-text search with tsvector/tsquery, GIN indexes, clean migration path)
• feat: embedding-based semantic search via pgvector #45 — Embedding-based semantic search via pgvector (builds on feat: PostgreSQL backend support #44, adds text-embedding-3-small integration, embed-on-insert, lcm_grep mode="semantic" with cosine distance)

Both are additive — SQLite remains the default, PG activates when connectionString is set in config. Running in production on a multi-agent homelab setup with shared PostgreSQL backing 3 OpenClaw instances.

Happy to iterate on review feedback.

[rel]

Happy to test this if it’ll help. Running into lock issues in a multi-agent environment.

[rivetphilbot]

That's exactly the use case this solves — SQLite's file locking breaks down with concurrent agents. PR #44 adds PostgreSQL as an alternative backend (SQLite stays default). Once it merges, you'd set LCM_CONNECTION_STRING and LCM_BACKEND=postgres and the lock issues go away. Happy to help you get set up once it lands — a multi-agent environment like yours is exactly what the new transaction model was designed for.

[BasuruK]

+1 great addition. Kept running into locks when there are more agents.