SQLite vs PostgreSQL Backends in AgentsView: A Technical Comparison

AgentsView abstracts data persistence behind a dialect layer, defaulting to a local SQLite file with WAL mode while optionally supporting a read-only PostgreSQL backend for centralized analytics via the PGURL environment variable.

The kenn-io/agentsview repository implements a dual-backend architecture that lets operators run a lightweight, single-file database or connect to a remote PostgreSQL cluster. While both backends expose identical query APIs to the HTTP server, they differ fundamentally in concurrency models, full-text search implementations, and write paths.

Storage Models and Deployment Topology

SQLite: Local File with WAL Mode

By default, AgentsView initializes a single SQLite database file named agentsview.sqlite3 inside the directory specified by AGENTSVIEW_DATA_DIR (or the --data-dir flag). As implemented in internal/db/db.go, the connection enables Write-Ahead Logging (WAL) mode to improve read concurrency while serializing writes through SQLite’s file-locking mechanism. This design targets single-machine workstations where a user browses agent sessions offline.

PostgreSQL: Remote Read-Only Replica

When the PGURL environment variable or --pg-url CLI flag is present, AgentsView switches to the PostgreSQL backend. The connection logic in internal/postgres/connect.go parses the DSN using the pgx/v5 driver, configures SSL modes, and establishes a connection pool suitable for concurrent access. Unlike the SQLite backend, the PostgreSQL store is read-only for the running server; writes originate exclusively from the SQLite-to-PostgreSQL push mechanism.

Feature Parity and Query Divergence

Full-Text Search: FTS5 vs ILIKE

The most visible functional difference lies in full-text search capabilities.

• SQLite backend: Leverages the native FTS5 extension for tokenized, relevance-ranked searches over session content. The implementation resides in internal/db/search_content.go, which constructs MATCH queries against virtual FTS5 tables.
• PostgreSQL backend: Falls back to standard SQL pattern matching using ILIKE queries in internal/postgres/search_content.go, as the schema does not assume availability of PostgreSQL’s native full-text search extensions.

Dialect Abstraction in Query Building

Both backends share a common query interface defined in internal/db/query_dialect.go. The source code exposes PostgresQueryDialect() and SQLiteQueryDialect() functions that return dialect-specific SQL fragments—such as placeholder syntax ($1 vs ?) and pagination clauses—allowing the repository layer to remain agnostic of the underlying engine.

Write Path and Synchronization Strategy

The pg push Command

Because the PostgreSQL backend operates as a read-only analytics mirror, mutations occur through a deliberate synchronization step. The agentsview pg push CLI command (defined in cmd/agentsview/pg.go) invokes the logic in internal/postgres/push.go to:

1. Query the local SQLite database for sessions not yet present in PostgreSQL.
2. Compute content fingerprints to detect changes.
3. Batch-insert new rows into the remote PostgreSQL instance using the schema defined in internal/postgres/schema.go.

This architecture treats SQLite as the system of record and PostgreSQL as a read replica for shared dashboards.

Code-Level Implementation Details

Connection Initialization

Backend	File	Responsibility
SQLite	internal/db/db.go	Opens the local file, applies migrations, configures WAL mode.
PostgreSQL	internal/postgres/connect.go	Validates PGURL, sets up SSL, creates the pgx connection pool.

Store Interfaces

The server queries data through store objects that encapsulate backend-specific SQL:

• internal/postgres/store.go – Implements the read-only store interface for PostgreSQL, used by the HTTP handlers when --pg-url is active.
• internal/db/db.go – Exports the SQLite connection directly for both the sync engine and the local server mode.

Practical Configuration Examples

Start the server with the default SQLite backend:

export AGENTSVIEW_DATA_DIR=/var/lib/agentsview
agentsview serve --port 8080

Switch to the PostgreSQL backend for read-only serving:

export PGURL="postgres://user:secret@db.example.com:5432/agentsview?sslmode=require"
agentsview serve --pg-url=$PGURL

Push new local data to the remote PostgreSQL instance:

agentsview pg push --pg-url=$PGURL

The push command reads the SQLite WAL, filters already-synced rows using the fingerprint column, and upserts the remainder into the PostgreSQL tables.

Summary

• SQLite is the default, file-based backend suitable for single-user, offline workflows with FTS5 search and serialized writes.
• PostgreSQL is an optional, network-connected backend configured via PGURL, offering concurrent read access but requiring the pg push command to ingest data.
• Dialect abstraction in internal/db/query_dialect.go unifies SQL generation, while internal/postgres/push.go handles the unidirectional sync from SQLite to PostgreSQL.
• Full-text search diverges by backend: FTS5 for SQLite, ILIKE for PostgreSQL.

Frequently Asked Questions

Which database backend does AgentsView use by default?

AgentsView defaults to SQLite. If no PGURL environment variable or --pg-url flag is provided, the binary opens (or creates) an agentsview.sqlite3 file in the data directory and enables WAL mode for better concurrency.

Can the PostgreSQL backend accept direct writes from the AgentsView server?

No. According to the implementation in internal/postgres/store.go, the PostgreSQL backend is read-only for the server process. All writes must originate in the local SQLite database and propagate via the agentsview pg push command, which streams rows through internal/postgres/push.go.

How does full-text search performance compare between the two backends?

The SQLite backend uses the native FTS5 virtual table engine for indexed, tokenized searches, providing fast relevance-ranked results. The PostgreSQL backend currently uses simple ILIKE pattern matching (see internal/postgres/search_content.go), which performs full table scans and is less efficient for large text corpora unless additional PostgreSQL FTS extensions are manually added.

Is it possible to run AgentsView without any local SQLite file?

Only if you intend to use PostgreSQL strictly as a read-only viewer. However, the pg push mechanism requires a local SQLite source to hydrate the PostgreSQL tables. Therefore, a fully SQLite-less deployment is not supported by the current architecture; SQLite remains the system of record even when PostgreSQL is enabled.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}