# SQLite vs PostgreSQL Backends in AgentsView: A Technical Comparison

> Compare SQLite and PostgreSQL backends in AgentsView. Learn how to use SQLite for local data and PostgreSQL for centralized analytics. Optimize your data strategy.

- Repository: [Kenn Software/agentsview](https://github.com/kenn-io/agentsview)
- Tags: technical-comparison
- Published: 2026-06-20

---

**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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/cmd/agentsview/pg.go)) invokes the logic in [`internal/postgres/push.go`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go) | Opens the local file, applies migrations, configures WAL mode. |
| **PostgreSQL** | [`internal/postgres/connect.go`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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:

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

```

Switch to the **PostgreSQL** backend for read-only serving:

```bash
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:

```bash
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`](https://github.com/kenn-io/agentsview/blob/main/internal/db/query_dialect.go) unifies SQL generation, while [`internal/postgres/push.go`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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.