pgstack-io/pgstack: Postgres Replicas for Search and Audit

What PgStack Adds Beyond The Source Database

PgStack is designed for organizations that need to read data in ways that should not compete with transactional Postgres workloads. The source database remains the system of record. PgStack consumes changes from that database and exposes separate processing paths for Search and Audit. Search is a search-oriented replica for semantic, keyword, and hybrid retrieval. Audit is a history of row changes for automatic change tracking.

The distinction matters because a product database often has indexes, locking behavior, and query priorities selected for OLTP work. Search queries and historical inspection have different requirements. PgStack places indexing and queries behind a separate Postgres-compatible endpoint on port 54321, so the README says that those operations run outside the application database.

The intended users include teams building product search, AI retrieval, duplicate detection, audit trails, troubleshooting workflows, and customer activity feeds. The listed search use cases include finding related records and retrieving current application data as context for AI agents. Audit use cases include compliance reviews, identifying previous values, and recovering selected updated or deleted records.

PgStack does not replace Postgres. It assumes an existing source database and creates a companion service. That boundary is useful for evaluation because teams do not need to migrate their application connection or make the source database the search system. It also means that PgStack cannot provide features that are not backed by replicated source data or the configured indexing fields.

Logical Replication And The PgStack Processing Pipeline

The documented architecture begins with logical replication from the user's Postgres database. PgStack requires the source database to run with logical replication enabled. The README instructs operators to check the setting with `SHOW wal_level` and to change it to `logical` when necessary.

The architecture diagram identifies a CDC component, NATS, and a Processor. This indicates that database changes move through a change-data-capture path and a message broker before the processor updates PgStack's replica data. The repository layout supports this reading because the project contains separate `cdc/`, `processor/`, and `server/` Go directories, and the Dockerfile builds one Go binary for each of those areas.

Audit processing records changes made while PgStack is running. The README does not say that Audit reconstructs changes that occurred before the service started. That distinction affects adoption. If a team requires a complete historical record predating the first PgStack start, it would need another source of historical data or a different procedure, which is not documented here.

Search uses configured source tables that have primary keys. Existing rows are indexed at startup, and later changes keep the index current. Source table names are mapped into Search names, with `public.products` becoming `search.public_products`. Search requires at least one configured table when it is enabled. Without `pgstack.yaml`, Audit is enabled and Search is disabled.

Docker Setup And First Audit Query

The quickstart uses a development Postgres database and a superuser connection for automatic setup. First, confirm that logical replication is available. Run this in the source Postgres session:

SHOW wal_level;

If the result is not `logical`, the README documents this change and a Postgres restart:

ALTER SYSTEM SET wal_level = 'logical';

Start the PgStack container with the source database URL. This command publishes the PgStack server on host port 54321:

docker run --rm --name pgstack \
  -p 54321:54321 \
  -e DATABASE_URL='postgres://postgres:[email protected]:5432/postgres' \
  ghcr.io/pgstack-io/pgstack:latest

On Docker Desktop, `host.docker.internal` resolves to the computer hosting Docker. On Linux Docker Engine, the README says to add `--add-host=host.docker.internal:host-gateway` so that the name resolves to the host gateway. For a remote database, the URL must use a hostname reachable from the container. Reserved characters in the source password must be URL-encoded.

After the container is running, connect with `psql` to the PgStack endpoint:

psql 'postgres://localhost:54321/pgstack'

Query `audit.changes` to inspect operations, before and after values, and commit times. The README's example filters for `public.products` and orders by `committed_at` descending.

Configuring Keyword, Semantic, And Hybrid Search

Search requires a configuration file that selects source tables and separates indexed text from stored result columns. Save the following as `pgstack.yaml`, changing the table and columns to match the source database:

audit:
  enabled: false
search:
  enabled: true
  tables:
    - name: public.products
      indexColumns: [name, description]
      storeColumns: [id, name, description]

`indexColumns` supply the text used for search. `storeColumns` are returned by queries and can be used for filters. The README says to include primary-key columns in `storeColumns` so they can be returned in results. Source names are transformed into Search names, so the example becomes `search.public_products`.

Mount the file at `/app/pgstack.yaml` when starting the container:

docker run --rm --name pgstack \
  -p 54321:54321 \
  -e DATABASE_URL='postgres://postgres:[email protected]:5432/postgres' \
  --mount type=bind,src="$PWD/pgstack.yaml",dst=/app/pgstack.yaml,readonly \
  ghcr.io/pgstack-io/pgstack:latest

After startup indexing completes, use `keyword_rank` for BM25 ranking:

SELECT id, name FROM search.public_products
ORDER BY keyword_rank('rain jacket') LIMIT 10;

For semantic and hybrid ranking, add `-e OPENAI_API_KEY` to the Docker command. Without that variable, indexing remains keyword-only and PgStack makes no embedding API calls. The README states that indexed text and semantic or hybrid queries are sent to OpenAI's `text-embedding-3-small` when semantic indexing is enabled. Restarting rebuilds the index and can incur embedding charges again.

Operational Limits, Costs, And License Boundaries

The first constraint is source replication. PgStack depends on Postgres logical replication, and the documented setup uses a development database with a superuser connection for automatic setup. The README does not describe a production checklist for connection privileges, replication slot management, replication lag, or recovery after outages. Those areas require verification before PgStack is used as an operational audit system.

The second constraint is OpenAI dependence for semantic and hybrid search. Keyword-only mode avoids embedding API calls, but semantic indexing sends indexed text and queries to OpenAI's `text-embedding-3-small` model. Teams must account for external service availability, data transfer, account billing, and the cost of rebuilding embeddings. The repository does not document an alternative embedding provider or a local embedding configuration.

Audit coverage also has a boundary. The README states that Audit records changes made while PgStack is running. It does not document backfilling pre-existing history, retention policies, deletion controls, or rollback behavior for audit records. A compliance review should therefore ask how historical coverage, retention, access control, and recovery are handled before treating this endpoint as the complete audit archive.

The project is open source under `AGPL-3.0`, an OSI-approved license according to the README. That license has implications for distribution and network use that require review by the adopting organization. The repository also presents PgStack Cloud as a managed production option, but the README does not give its pricing or service terms. The Go repository is split into `cdc`, `processor`, and `server` components, which gives contributors clear boundaries, but the supplied documentation does not describe a supported upgrade path or schema migration policy.

PgStack Versus Keeping Search And Audit In The Primary Database

PgStack's practical alternative is to keep search indexes, audit queries, and historical inspection in the existing Postgres deployment. That approach avoids introducing another container and a second data path, but it places retrieval and change-tracking workloads on the same database that the application uses for transactions. PgStack instead uses logical replication and isolates those workloads behind a separate Postgres-compatible endpoint.

The difference is architectural rather than a claim that one database is always faster. In the PgStack design, CDC feeds NATS and a Processor, while Search and Audit are separate replica purposes. Search also adds an optional external dependency through OpenAI for embeddings. A team that already has a carefully isolated Postgres search replica and needs no semantic retrieval may prefer the simpler single-database arrangement. A team that wants source data replicated into dedicated search and audit views may find PgStack's separation more useful.

Another boundary is scope. PgStack is not documented as a backup service, a replacement for the source database, or a general change-data-capture platform for arbitrary downstream systems. Its documented output is a server exposing Audit and Search data. Adoption should therefore begin with the actual retrieval and audit requirements, not with an assumption that every PostgreSQL workload belongs in PgStack.

Editorial conclusion

Adopt PgStack when you need a separate Postgres-compatible endpoint for audit history or search, and your source database supports logical replication. Do not choose it as a general-purpose backup, a replacement for the source database, or a zero-cost local search engine. Verify first that wal_level is logical, that the connection user can perform the documented automatic setup, and that the OpenAI data and billing implications are acceptable. For a current v2.0.0 deployment, the repository records its last push on 2026-10-08.

Frequently asked questions

Official sources

1. Issues
2. License: AGPL-3.0
3. pgstack-io/pgstack on GitHub
4. README
5. Releases