After #473: optional PostgreSQL storage for shared Gortex deployments?

[peterkc]

Summary

I am asking whether an optional PostgreSQL Store adapter would be in scope after #473. SQLite would remain the default. Shared team access, local Git worktrees, and CI-maintained indexes are the use cases motivating the question.

Gortex would ship the adapter and connect to an external PostgreSQL server through pgx. This direction depends on #473 being merged and its storage lifecycle and consistency findings being addressed.

Team Use Case

A shared Gortex service would let a team query one authoritative index through authenticated MCP or HTTP. This avoids separate indexes on every developer machine and automation runner.

One service with SQLite may be sufficient for a small team. PostgreSQL becomes relevant when the deployment needs multiple Gortex processes, state independent of one service host, concurrent index publication, or managed recovery.

team clients
     |
     | authenticated MCP / HTTP
     v
Gortex service instances
     |
     | Store adapter shipped with Gortex
     v
external PostgreSQL server

Shared And Local Indexes

The shared and local indexes would own different states. The shared service would hold complete indexes for committed revisions, identified by stable repository and commit identities. Machine-local checkout paths are unsuitable as shared keys. Local SQLite would continue to hold uncommitted changes, unpublished branches, temporary repositories, and other machine-local state.

Current Gortex behavior treats a linked Git worktree as a distinct repository instance with its own local repository prefix, even though it shares Git metadata with the main checkout. A local SQLite store can therefore keep multiple worktree namespaces. Those local prefixes are separate from the stable repository and commit identity needed by a shared service.

For example:

shared PostgreSQL
  repository A @ main commit abc1234

developer machine
  main checkout       @ commit abc1234, clean  -> shared index is usable
  feature worktree    @ commit def5678, dirty  -> local worktree index
  release worktree    @ commit 789abcd, clean  -> shared if published, else local

Dirty worktree contents should remain local by default. A possible initial selection policy is:

• use the shared generation when it exactly matches the checked-out commit and compatible schema and extractor versions;
• use or build the local worktree index when the checkout is dirty, or the exact commit is not available remotely;
• never silently combine rows from different commits under one repository prefix.

A local-over-shared delta layer may be useful later. Its deletion, edge replacement, and query composition rules belong in a separate discussion.

Why PostgreSQL

PostgreSQL would provide one durable index authority for multiple trusted Gortex processes. Server-managed transactions and locking can coordinate index publication while unrelated queries continue. The database can outlive any one service host and use established backup, point-in-time recovery, replication, and monitoring tools.

These capabilities support rolling service deployments, separate indexing workers, multiple query-serving instances, and recovery independent of one machine. Separate local databases could serve different repository revisions.

Gortex would still need explicit generation publication and failure semantics. The SQLite sufficiency threshold needs measurement before implementation.

Backend Boundary

clients
   |
   | authenticated MCP / HTTP
   v
Gortex service
   |
   | internal Store contract
   v
SQLite (default) or external PostgreSQL (optional)

Gortex would ship the PostgreSQL adapter and connect directly to the external server through pgx. Clients would continue using MCP or HTTP, and trusted Gortex processes would hold the database credentials.

A separate storage service would add an RPC client, protocol versioning, authentication, retries, and another deployment while retaining the PostgreSQL implementation. That boundary fits a future storage API shared by several products. The direct adapter is smaller for the current use case.

Lifecycle, repository identity, atomic publication, and visible failure behavior can share backend conformance tests. Search needs a more explicit contract because SQLite FTS5 and PostgreSQL text search can differ in tokenization, ranking, and result order. Portable tests should cover agreed invariants; backend-specific tests should cover relevance.

CI As An Example Publisher

CI could keep committed default-branch or pull-request indexes current. A single SQLite-backed service could coordinate reindex requests. PostgreSQL becomes useful when trusted indexing jobs or service instances publish concurrently through shared storage.

Safe CI publication would require immutable commit identity, staging, atomic publish, stale-job rejection, and idempotent retry. The publication protocol belongs in a follow-up design. Dirty local worktrees would remain local.

Scope And Maintenance

Implementation would proceed in this order:

1. Settle Decommission the in-memory graph backend; SQLite becomes the only store #473 and establish a corrected, measured SQLite baseline.
2. Define portable storage invariants, stable repository and generation identity, and backend-specific search expectations.
3. Compare a central SQLite service, CI-built SQLite snapshots in object storage, and PostgreSQL against the workflow in Hey, I'm evaluating Gortex for our team's setup. We want to build the index in GitLab CI and deploy only the snapshot to a remote server, without the original source files on disk (to avoid cloning every repo onto the server). #474.
4. Implement PostgreSQL only when the required workload needs live shared writes or multiple service processes.
5. Treat CI publication details and local-over-shared overlays as follow-up designs.

A second backend adds schema migrations, dialect drift, a larger CI matrix, operational documentation, and ongoing compatibility work. The implementation proposal should include ownership of the PostgreSQL adapter, migrations, conformance tests, and CI coverage.

That maintenance surface may still be too large for the project. I am asking for that judgment before producing implementation pull requests.

Non-Goals

• Replacing SQLite as the default or requiring PostgreSQL for every team.
• Giving ordinary clients direct database access.
• Publishing dirty local worktrees by default.
• Requiring live migration or a local-over-shared overlay in the first slice.
• Introducing a storage plugin framework or separate storage service.
• Defining per-repository authorization for a shared index; that is a separate service-layer concern.

Alternatives

One Gortex service with SQLite remains the preferred first team deployment. It is simpler and may be sufficient when one service instance and host are acceptable.

Issue #474 describes a related deployment: build the index in CI and serve it remotely without cloning every repository onto the server.

Before implementing PostgreSQL, Gortex should evaluate publishing immutable SQLite index generations to object storage such as S3, Google Cloud Storage, or Azure Blob Storage. CI could build and close the SQLite database, publish it with a manifest and checksum, and update a generation pointer only after the complete artifact is available. A Gortex service could then download, verify,
and atomically activate that generation.

This model preserves SQLite search behavior and may be sufficient when CI is the only publisher, committed indexes are read-mostly, and downloading a new generation is acceptable. It would not provide live multi-process writes, incremental shared updates, or database transactions across publishers.

A hybrid deployment could use object storage for immutable index and source artifacts, PostgreSQL for a live shared index when concurrent writers are required, and local SQLite for dirty worktrees. That additional topology should follow measured need rather than become the first implementation.

Independent per-client indexes remove the shared service. They duplicate indexing work, consume more resources, and can return different results as their freshness diverges.

Direct PostgreSQL access from clients would expose credentials and make database compatibility part of the client contract. It is outside this proposal.

Questions

1. Is a shared single- or multi-instance Gortex service within the project's intended scope?
2. Is an optional PostgreSQL adapter an acceptable maintenance surface after Decommission the in-memory graph backend; SQLite becomes the only store #473?
3. Should the existing Store contracts remain the backend boundary?
4. Which search behaviors should every backend preserve, and which relevance behavior can remain backend-specific?
5. Would immutable SQLite snapshots in object storage satisfy the expected CI and team workflow before Gortex adds another live database backend?
6. What benchmark, conformance, and operational evidence would you want before reviewing implementation work?

Related:

• Decommission the in-memory graph backend; SQLite becomes the only store #473
• Hey, I'm evaluating Gortex for our team's setup. We want to build the index in GitLab CI and deploy only the snapshot to a remote server, without the original source files on disk (to avoid cloning every repo onto the server). #474
• Storage review of #473

[zzet]

Hey @peterkc,

Long story short: the centralised store layer is on the roadmap; however, the scope of required changes is bigger (and slightly different from what is in the question).

Is a shared single- or multi-instance Gortex service within the project's intended scope?

Yes, shared access is within the project scope. And in the architecture already present, there are 3 main components:

• a store interface to use various persistent options
• an overlay mechanism to handle the graph/code change drift
• the federative mode with the possibility to connect the chain of gortex servers

But it's an early stage, and before the project comes to the point, a few required (and quite big) changes should be made, and those changes are going to be unblocked soon.

Is an optional PostgreSQL adapter an acceptable maintenance surface after #473?

Yes, and a few other experimental solutions in the list too. The project may benefit more not only from PostgreSQL, but also from graph-based solutions, like FalkorDB. Those alternatives give out-of-the-box way more performant traversal functionality, which the project will benefit from. So, I don't think it requires locking only on the SQL-like options.

Should the existing Store contracts remain the backend boundary?

No definite answer to the question. And the Store interface is not written in stone.

Which search behaviors should every backend preserve, and which relevance behavior can remain backend-specific?

The most critical part of the question is: which relevance behavior. Gortex (before SQLite) had under the hood the https://github.com/LadybugDB/ladybug, however, I decided to remove this integration due to inconsistent results.
Speaking about the storage and the 'local' vs 'remote' setup - the local setup introduces strict requirements, while with remote the project has more flexibility and not everything should be in the same store. This part of the design is drafted. In case such a functionality responsibility segregation is not in place, the store should have implemented everything that SQLite supports at the moment.

Would immutable SQLite snapshots in object storage satisfy the expected CI and team workflow before Gortex adds another live database backend?

No, because part of the functionality relies on the source code.

What benchmark, conformance, and operational evidence would you want before reviewing implementation work?

This is an important and big question, which I can respond to later once I invest more time to answer it.