gem-hunter

PgStack is Postgres read replicas for Search and Audit.

• Search: a search-optimized replica for semantic, keyword, and hybrid retrieval.
• Audit: a history of row changes for automatic data change tracking.

PgStack replicates your data while your existing Postgres remains the source of truth. Indexing and queries run outside your application database through a separate Postgres-compatible endpoint.

Run with one Docker command, or use PgStack Cloud for managed production workloads.

• Highlights
• Use Cases
• Quickstart
• Usage
  • Audit
  • Search
  • Configuration
• Architecture
• OSS vs Cloud
• License

• Specialized Replicas: continuously synchronized, with Search and Audit isolated from OLTP workloads.
• Easy Setup: starts in a single container with your database URL.
• Postgres Compatibility: integrates with clients and tools in the Postgres ecosystem.
• Open-Source: released under the OSI-approved AGPL-3.0 license.

• Search
  • Product Search: find relevant information across operational records and knowledge bases.
  • AI and RAG Retrieval: retrieve current application data as context for AI agents and assistants.
  • Duplicate Detection: surface related tickets, issues, and requests expressed in different words.
  • Find Similar: discover related companies, people, jobs, articles, and other application records.
• Audit
  • Audit Trails and Compliance: track record changes to support data audits and compliance reviews.
  • Troubleshooting: investigate unexpected data issues and understand how your data changed.
  • Selective Data Recovery: identify previous values to recover updated or deleted records.
  • Customer Activity Feeds: turn data changes into activity feeds in your product.

Use a development Postgres database and a superuser connection for automatic setup. Check logical replication with:

SHOW wal_level;

If the result is not logical, enable it and restart Postgres:

ALTER SYSTEM SET wal_level = 'logical';

Start PgStack with your database URL:

docker run --rm --name pgstack \
  -p 54321:54321 \
  -e DATABASE_URL='postgres://postgres:postgres@host.docker.internal:5432/postgres' \
  ghcr.io/pgstack-io/pgstack:latest

On Docker Desktop, host.docker.internal points to your computer. On Linux Docker Engine, add --add-host=host.docker.internal:host-gateway to make that name resolve to the host gateway. For a remote database, use its reachable hostname. URL-encode reserved characters in the source password.

The image supports Linux amd64 and arm64, including Apple Silicon. latest tracks stable releases.

Audit is enabled by default. It records changes made while PgStack is running. After changing a row in your source database, connect to the PgStack server running on port 54321 from another terminal on your host:

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

Query audit.changes:

-- Inspect changes to one table.
SELECT operation, before, after, committed_at
FROM audit.changes
WHERE schema = 'public' AND "table" = 'products'
ORDER BY committed_at DESC
LIMIT 50;

-- Filter on values captured in the changed row.
SELECT operation, after->>'name' AS name, committed_at
FROM audit.changes
WHERE after->>'name' ILIKE '%jacket%'
ORDER BY committed_at DESC;

Search works with tables that have primary keys. Save this as pgstack.yaml, changing the table and columns to match your database:

# Search only. Set audit.enabled to true to also capture change history.
audit:
  enabled: false
search:
  enabled: true
  tables:
    - name: public.products
      indexColumns: [name, description]
      storeColumns: [id, name, description]

indexColumns provide the text to search. storeColumns are returned by queries and available for filtering; include primary-key columns to return them in results. Source names become Search names such as public.products → search.public_products.

Mount your configuration when starting the container:

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

Existing rows are indexed at startup, and subsequent changes keep the index current. Once indexing completes, connect to the PgStack server running on port 54321 from another terminal on your host:

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

Search with BM25 keyword ranking:

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

For semantic and hybrid search, add -e OPENAI_API_KEY to docker run. Without a key, indexing stays keyword-only and makes no embedding API calls.

-- Find related meaning.
SELECT id, name FROM search.public_products
ORDER BY semantic_rank('something for wet weather') LIMIT 10;

-- Combine semantic and keyword relevance.
SELECT id, name FROM search.public_products
ORDER BY hybrid_rank('waterproof rain jacket') LIMIT 10;

Rankings sort best matches first. Use stored columns in WHERE filters. With semantic indexing enabled, indexed text and semantic/hybrid queries are sent to OpenAI's text-embedding-3-small, billed to your account. Restarting rebuilds the index and can incur embedding charges again.

Use an optional pgstack.yaml file, mounted as shown in the Search example. Without it, Audit is enabled and Search is disabled. Enable either feature or both; Search requires at least one configured table when enabled. Invalid configuration is rejected before source setup. See pgstack.example.yaml.

                     Your Postgres
                           |
                           | logical replication
                           v
+----------------- PgStack container -------------------+
|                                                       |
|               CDC  -->  NATS  -->  Processor          |
|                          |                            |
|                 +--------+--------+                   |
|                 |                 |                   |
|                 v                 v                   |
|          Audit: Iceberg       Search: BM25            |
|            + Parquet        + Lance vectors           |
|                 |                 |                   |
|                 +--------+--------+                   |
|                          |                            |
|                          v                            |
|              Postgres-compatible server               |
|                 with embedded DuckDB                  |
|                                                       |
+--------------------------+----------------------------+
                           |
                           | wire protocol
                           v
                   Postgres clients

Audit stores Parquet with Iceberg metadata. Search uses Lance for BM25 and optional vectors. DuckDB runs inside PgStack's Postgres-compatible server to execute SQL over this data.

The open-source core provides the Search replica and Audit history through SQL. PgStack Cloud adds the managed platform:

• Web Control Panel: database connections, projects, and product settings.
• Team Management: organizations, team membership, and shared projects.
• Audit Trail UI: change history, filters, and record inspection.
• Fine-Grained Configuration: table and column selection for Search and Audit.
• Flexible Audit Retention: configurable history for troubleshooting and compliance.
• Managed Embeddings: included embedding generation without a separate model API account.
• Autoscaling: managed capacity for Audit and Search workloads.
• Production Security: encryption, tenant isolation, IP allow rules, and SSH and VPN tunneling.
• Automatic Updates: service improvements, fixes, and infrastructure maintenance.
• Support: priority response and escalation options, with dedicated Slack support.

The bundled image is designed for local development and evaluation. For production workloads, PgStack Cloud provides a fully managed service.

This project is distributed under AGPL-3.0.

See CONTRIBUTING.md for development. For vulnerability reports, see SECURITY.md.