Getting Started

This guide walks you through setting up PgCache as a caching proxy in front of your PostgreSQL database.

Prerequisites

Before installing PgCache, ensure your environment meets these requirements:

• PostgreSQL 16+ as your origin database
• Logical replication enabled on the origin (wal_level = logical)
• Docker for running PgCache

Configure the Origin Database

PgCache uses PostgreSQL logical replication to keep its cache synchronized. Your origin database needs logical replication enabled and a user with sufficient permissions.

Enable Logical Replication

Add or update the following settings in your PostgreSQL configuration (postgresql.conf) and restart the server:

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

If you’re running a managed PostgreSQL service (AWS RDS, Google Cloud SQL, Neon, etc.), check your provider’s documentation for enabling logical replication.

Database User Permissions

PgCache automatically creates and manages the publication and replication slot it needs on the origin database. The database user that PgCache connects with must have permission to do so:

• Superuser, or
• REPLICATION role attribute plus ownership of (or CREATE privilege on) the tables to be published

For example, to grant a dedicated user the required permissions:

ALTER ROLE pgcache_user REPLICATION;

On managed services where superuser access is unavailable, consult your provider’s documentation for granting replication permissions.

Install with Docker

PgCache is distributed as a Docker image. The container includes an embedded PostgreSQL instance for the cache database, so there’s nothing else to set up.

docker run -d -p 5432:5432 pgcache/pgcache \
  --upstream postgres://user:password@your-db-host:5432/myapp

This starts PgCache listening on port 5432, proxying to your origin database.

Docker Compose Example

For a more complete setup:

services:
  pgcache:
    image: pgcache/pgcache
    ports:
      - "5432:5432"
    environment:
      UPSTREAM_URL: postgres://user:password@db:5432/myapp
      NUM_WORKERS: 4
      PUBLICATION: pgcache_pub
      SLOT: pgcache_slot

Connect Your Application

Update your application’s database connection string to point at PgCache instead of your origin database directly:

# Before
DATABASE_URL=postgres://user:password@db-host:5432/myapp

# After — just change the host (and port if needed)
DATABASE_URL=postgres://user:password@pgcache-host:5432/myapp

Your application connects to PgCache using the same credentials it would use for the origin database. PgCache passes authentication through to the origin transparently.

Verify It Works

Once your application is connected through PgCache, you can verify caching is active by checking the metrics endpoint.

First, enable the metrics endpoint by adding the METRICS_SOCKET environment variable:

services:
  pgcache:
    image: pgcache/pgcache
    ports:
      - "5432:5432"
    environment:
      UPSTREAM_URL: postgres://user:password@db:5432/myapp
      METRICS_SOCKET: "0.0.0.0:9090"

Then query the metrics endpoint:

curl http://localhost:9090/metrics | grep pgcache_queries

Look for pgcache_queries_cache_hit and pgcache_queries_cache_miss counters to confirm caching is active. See Monitoring for the full list of available metrics.

Next Steps

Continue reading to learn more about PgCache:

• Configuration — Full reference for all settings
• How Caching Works — Understand what gets cached and how invalidation works
• Monitoring — Set up Prometheus metrics and alerting
• Compatibility — Supported PostgreSQL versions and query patterns

Configuration

PgCache is configured through environment variables passed to the Docker container, which map to CLI arguments internally. When multiple methods are used, CLI arguments take precedence over TOML file values.

Configuration Methods

TOML Configuration Reference

[origin] — Origin Database Connection

The PostgreSQL database that PgCache caches queries for.

[replication] — Replication Connection (Optional)

Override connection settings for CDC logical replication. Every field is optional and cascades from [origin] when not specified. This is useful when your application connects through a connection pooler like PgBouncer, but CDC replication needs a direct connection to PostgreSQL.

[cdc] — Change Data Capture

Settings for PostgreSQL logical replication. PgCache creates and manages the publication and replication slot automatically on the origin database using these names.

[listen] — Proxy Listener

[metrics] — Prometheus Metrics (Optional)

Top-Level Settings

The log_level field supports tracing filter syntax for fine-grained control:

# Simple levels
log_level = "info"
log_level = "debug"

# Module-specific levels
log_level = "pgcache_lib::cache=debug,info"

Full Example Configuration

num_workers = 4
cache_size = 1073741824  # 1 GB
log_level = "info"

[origin]
host = "db.example.com"
port = 5432
user = "app_user"
password = "secret"
database = "myapp"
ssl_mode = "require"

# Optional: override replication connection
# Useful when origin is behind PgBouncer
[replication]
host = "db-direct.example.com"
port = 5432

[cdc]
publication_name = "pgcache_pub"
slot_name = "pgcache_slot"

[listen]
socket = "0.0.0.0:6432"

[metrics]
socket = "0.0.0.0:9090"

CLI Arguments Reference

All TOML fields can be set via command-line arguments. CLI values override TOML values.

Docker Environment Variables

When running the PgCache Docker image, these environment variables are available. They map to the entrypoint’s CLI options.

Docker Examples

Basic:

docker run -d -p 5432:5432 pgcache/pgcache \
  --upstream postgres://user@db:5432/myapp

With replication override (origin behind PgBouncer):

docker run -d -p 5432:5432 pgcache/pgcache \
  --upstream postgres://user@pgbouncer:6432/myapp \
  --replication-host db-direct.example.com \
  --replication-port 5432

With environment variables:

docker run -d -p 5432:5432 \
  -e UPSTREAM_URL=postgres://user@db:5432/myapp \
  -e NUM_WORKERS=8 \
  -e REPLICATION_HOST=db-direct.example.com \
  pgcache/pgcache

How Caching Works

PgCache is a transparent caching proxy that sits between your application and PostgreSQL. It automatically determines which queries can be cached, stores their results in a local PostgreSQL cache database, and uses CDC (Change Data Capture) to keep cached data synchronized with the origin.

Architecture

┌──────────┐    queries     ┌──────────┐    uncacheable    ┌──────────┐
│          │ ─────────────▶ │          │ ────────────────▶ │          │
│   App    │                │ pgcache  │                   │PostgreSQL│
│          │ ◀───────────── │          │ ◀──────────────── │ (origin) │
└──────────┘   responses    └────┬──┬──┘    responses      └─────┬────┘
                                 │  │                            │
                           cache │  │    CDC stream              │
                           read/ │  │   (logical replication)    │
                           write │  └────────────────────────────┘
                                 ▼
                            ┌──────────┐
                            │  Cache   │
                            │   DB     │
                            └──────────┘

1. Your application sends queries to PgCache as if it were a normal PostgreSQL server
2. PgCache analyzes each query to determine if it’s cacheable
3. Cacheable queries are served from the cache database when a cached result exists (cache hit)
4. Cache misses and non-cacheable queries are forwarded to the origin database
5. The CDC stream continuously processes changes from the origin, keeping cache entries fresh or invalidating when necessary

What Gets Cached

PgCache analyzes each incoming SQL statement and determines whether it can be safely cached. Only queries where PgCache can guarantee correct results are cached.

The list of supported query patterns is actively expanding. If your query isn’t cached today, it may be in a future release.

Cacheable Queries

• Single-table SELECT statements
• INNER JOIN, LEFT JOIN, and RIGHT JOIN queries with equality join conditions
• WHERE clauses using comparison operators: =, !=, <, <=, >, >=
• Boolean operators in WHERE: AND, OR
• IN and NOT IN expressions
• IS NULL and IS NOT NULL checks
• NOT expressions
• GROUP BY and HAVING clauses (aggregation is performed on cached rows)
• ORDER BY clauses (sorting is applied at retrieval time)
• Aggregate functions in the SELECT list (COUNT, SUM, AVG, etc.)
• Window functions (ROW_NUMBER, RANK, etc. with PARTITION BY and ORDER BY)
• CASE expressions
• Arithmetic expressions in the SELECT list
• Uncorrelated subqueries in SELECT, FROM (derived tables), and WHERE (IN, NOT IN, scalar), including nested subqueries
• Common Table Expressions: WITH (CTE) queries, including MATERIALIZED / NOT MATERIALIZED
• Set operations: UNION, UNION ALL, INTERSECT, EXCEPT
• Functions in the SELECT list

Not Cached

These query patterns are passed through directly to the origin database:

• Non-SELECT statements: INSERT, UPDATE, DELETE, DDL statements
• LIMIT / OFFSET: Queries with result limiting
• FULL JOIN and CROSS JOIN
• Correlated subqueries: Subqueries that reference outer table columns
• LATERAL subqueries
• Pattern matching: LIKE, ILIKE
• Range operators: BETWEEN, ANY, ALL
• Existence checks: EXISTS, NOT EXISTS
• RECURSIVE CTEs
• Functions outside the SELECT list: Function calls in WHERE, JOIN, or other clauses

Queries that aren’t cached still work — they’re transparently forwarded to the origin with no overhead beyond the proxy hop.

Cache Maintenance

PgCache uses PostgreSQL logical replication (CDC) to keep its cache synchronized with the origin database. This is fundamentally different from TTL-based caching — PgCache knows about changes as they happen and can often update cached data in place rather than simply invalidating it.

How CDC Works

1. PgCache creates a publication and replication slot on the origin database and subscribes to changes
2. When data changes on the origin (INSERT, UPDATE, DELETE, TRUNCATE), the change event is streamed to PgCache via logical replication
3. PgCache identifies which cached queries are affected by the change
4. Affected cache entries are updated directly when possible, or invalidated and re-populated on the next query

Cache Size Management

When cache_size is configured, PgCache enforces a maximum cache size in bytes. When the limit is reached, the oldest cached queries are evicted to make room for new cache entries.

Consistency Guarantees

PgCache provides eventual consistency between the cache and origin:

• Changes on the origin are streamed to PgCache via CDC with minimal delay
• Replication lag is tracked and exposed as metrics (pgcache.cdc.lag_seconds, pgcache.cdc.lag_bytes) so you can monitor freshness
• The cache is fully reset on every PgCache startup, ensuring a clean state with no stale data carried over
• Uncacheable queries always go directly to the origin, so they’re always consistent

The lag between a write on the origin and the corresponding cache invalidation is typically sub-second, depending on network latency and origin server load.

Monitoring

PgCache exposes Prometheus-compatible metrics via an HTTP endpoint, giving you visibility into query performance, cache behavior, CDC replication health, and connection state.

Enabling Metrics

Add a [metrics] section to your configuration file:

[metrics]
socket = "0.0.0.0:9090"

Or use the CLI argument:

pgcache --config pgcache.toml --metrics_socket 0.0.0.0:9090

Metrics are then available at http://<address>:9090/metrics in Prometheus exposition format. Histograms report p50, p95, and p99 quantiles.

Prometheus Scrape Configuration

scrape_configs:
  - job_name: pgcache
    static_configs:
      - targets: ['pgcache-host:9090']

Available Metrics

Query Counters

Track how queries flow through PgCache.

Latency Histograms

All latency metrics are in seconds and report p50, p95, and p99 quantiles.

Per-Stage Timing

Detailed breakdown of where time is spent within PgCache:

Connection Metrics

CDC / Replication Metrics

Monitor the health and throughput of the CDC replication stream.

Cache State Metrics

Writer Queue Depths

CDC Handler Metrics

Protocol Metrics

Key PromQL Queries

Cache Hit Ratio

rate(pgcache_queries_cache_hit_total[5m])
/
(rate(pgcache_queries_cache_hit_total[5m]) + rate(pgcache_queries_cache_miss_total[5m]))

Query Latency (p95)

pgcache_query_latency_seconds{quantile="0.95"}

CDC Replication Lag

pgcache_cdc_lag_seconds

Cache Size Utilization

pgcache_cache_size_bytes / pgcache_cache_size_limit_bytes

Log Configuration

In addition to metrics, PgCache supports configurable logging via the log_level setting. This uses tracing’s EnvFilter syntax:

# Show info and above for all modules
log_level = "info"

# Debug logging for the cache subsystem, info for everything else
log_level = "pgcache_lib::cache=debug,info"

# Trace-level logging (very verbose)
log_level = "trace"

You can also set the RUST_LOG environment variable for one-off debugging sessions.

Compatibility

PgCache is designed to be transparent to client applications. This page covers supported PostgreSQL versions, query patterns, client libraries, and known limitations.

PostgreSQL Version Support

The origin database must have wal_level = logical enabled for CDC replication.

Note: On PostgreSQL 17 and below, changing the search_path during a session is not supported. PgCache uses the search path at connection time to resolve table references.

Supported Query Patterns

PgCache caches queries that it can safely evaluate and invalidate. All other queries are transparently forwarded to the origin.

Cached

Not Cached (Forwarded to Origin)

Queries that aren’t cached still work correctly — they pass through to the origin with no functional difference.

Client Library Compatibility

PgCache speaks the PostgreSQL wire protocol. Any client library that connects to PostgreSQL will work with PgCache without modifications.

ORMs and query builders work as expected — PgCache analyzes the SQL they generate the same way it handles hand-written queries.

TLS Support

For Docker deployments, TLS certificates can be passed as base64-encoded environment variables (PGCACHE_TLS_CERT, PGCACHE_TLS_KEY).

Note: Channel binding must be disabled for client connections to PgCache. Set channel_binding=disable in your client connection string or library configuration.

Connection Pooler Compatibility

PgCache works alongside connection poolers like PgBouncer. A common pattern is:

• Application connects to PgCache (caching proxy)
• PgCache connects to PgBouncer for regular queries (via [origin])
• PgCache connects directly to PostgreSQL for CDC replication (via [replication])

This is configured using the [replication] section to override the origin connection for logical replication, which requires a direct PostgreSQL connection.

[origin]
host = "pgbouncer.example.com"
port = 6432
user = "app_user"
database = "myapp"

[replication]
host = "postgres-direct.example.com"
port = 5432

Known Limitations

• Cache reset on startup: The cache database is fully reset each time PgCache starts. This ensures a clean state but means the cache is cold after a restart.
• Single-node: PgCache runs as a single process with an embedded cache. There is no distributed cache or multi-node support.
• COPY protocol: The PostgreSQL COPY protocol is not proxied through PgCache.
• LISTEN/NOTIFY: PostgreSQL asynchronous notifications are not proxied.
• Replication management: PgCache creates and manages its own publication and replication slot on the origin. If PgCache is down for an extended period, WAL can accumulate on the origin. Monitor pg_replication_slots on the origin.

Changelog

0.3.4 — 2026-02-12

Query Language Support

• LEFT JOIN and RIGHT JOIN queries with equality join conditions

Performance

• Streaming cache population (rows streamed directly instead of buffered)
• Multi-value upserts for cache population (batched inserts instead of one per row)

Bug Fixes

• Fixed cache table not populated properly for self-joins
• Fixed parentheses missing when deparsing logical expressions
• Preserved error context when bubbling errors up

Internals

• Local connection handling for cache worker
• Refactored integration tests to use more precise cache hit/miss detection

0.3.3 — 2026-02-09

Query Language Support

• Uncorrelated subqueries in SELECT, FROM (derived tables), and WHERE (IN, NOT IN, scalar)
• Nested subqueries (multi-level)
• Non-recursive CTEs (WITH ... AS), including MATERIALIZED / NOT MATERIALIZED
• CDC invalidation for subqueries and CTEs with directional semantics (inclusion vs exclusion vs scalar)

Observability

• Queue depth metrics for all unbounded channels
• Wait time metrics in worker for incoming work and database connection waits

Internals

• Subquery nesting depth factors into query complexity scoring
• Cache population ordering ensures inner/simpler queries are populated before outer queries that depend on them
• Moved constraint handling from cached query into update queries
• Improved LSN confirmation handling
• Respect column ordering when registering a table

0.3.2 — 2026-02-05

Query Language Support

• Set operations (UNION, INTERSECT, EXCEPT) in select statements

Internals

• Refactored CacheQuery to use QueryExpr and ResolvedQueryExpr for set operation support

0.3.1 — 2026-02-04

Query Language Support

• Arithmetic expressions
• ORDER BY in aggregate functions
• IS [NOT] NULL expressions
• IN expressions
• Window functions
• CASE expressions
• Functions in select target list
• Subqueries in select statements
• GROUP BY, HAVING, and LIMIT clauses (LIMIT queries not cached for now)

Performance

• Split writer command queue into separate queues for query registrations and CDC handling
• Refactored query registration to avoid blocking writer processing
• Concurrent processing of queries when handling INSERT and UPDATE CDC messages
• Multi-table join support with optimized invalidation

2026-01-30

Observability

• Prometheus metrics endpoint
• Metrics for writer receive queue sizes
• CDC operation metrics
• Query processing timing instrumentation

Performance

• Cache database connection pool for worker threads
• Pre-computed join column equivalences for invalidation (replaces AST traversal)
• More precise invalidation for joins (skips when UPDATE doesn’t modify join columns or query constraints)

Reliability

• Refined behavior during cache failures

2026-01-24

Features

• Separate replication connection settings
• Domain and enum type support in query registration
• Log level configuration setting
• Partitioned table support
• Duplicate query registration prevention

Performance

• Fixed pool of tasks for query registration
• Generation tracking bundled with simple query

Error Handling

• Switched to rootcause crate for location-tracking error reports
• attach_loc() context throughout codebase

2026-01-12

Security & Connectivity

• TLS support for client and origin connections
• Origin database password support
• Automatic replication slot and publication creation on startup

Bug Fixes

• Fixed TLS session ticket handling causing connection hangs
• Fixed select! panic on stream closure
• PostgreSQL identifier quoting where necessary
• Stripped SCRAM-SHA-256-PLUS from SASL options (unsupported)

2026-01-03

Cache Management

• Cache size limit enforcement
• Clean cache database on start/restart
• Generation-based cache purging with row removal
• Generation tracking for inserts and updates
• Unlogged cache tables for performance
• Initial cache size tracking

Reliability

• Cache thread restart on failure detection
• Proper CDC/cache thread failure detection

2025-12-20

Schema Support

• Full search path support
• Explicit schema qualification for all cache tables
• Schema-aware table metadata lookup
• Resolved AST with aliases and deparse

2025-12-04

Protocol Support

• Binary parameters in extended query protocol
• Correct column types returned from cache

Code Quality

• Refactored integration tests
• Split proxy.rs into modules

2025-11-12

Observability

• Initial metrics implementation

Bug Fixes

• Switched to sequential CDC event handling
• Fixed CDC insert message handling

2025-10-17

Protocol Support

• Extended query protocol support
• Order By in queries
• Parameter references in queries

Performance

• Optimized cache query handling
• DataRow message grouping
• SIGINT handling with graceful shutdown

Query Analysis

• Constraint propagation for cache invalidation
• Multiple join support

2025-09-30

Query Support

• Chained AND/OR expressions
• Table aliases
• VALUES clause
• Subqueries (select target list and table position)

Internals

• Generic nodes() iterator for AST traversal
• Reworked CDC message support for joins

2025-08-08 — Initial Release

Core Architecture

• Transparent PostgreSQL caching proxy
• Simple query protocol support
• Worker thread architecture for cache handling
• MD5 and SCRAM authentication passthrough

Query Support

• Query cacheability analysis
• Initial join support
• Comparison operators
• Custom AST with fingerprinting and deparse

Cache Invalidation

• CDC-based invalidation via logical replication
• TRUNCATE message handling
• Relation message handling (invalidates on schema change)

Configuration

• TOML configuration file support
• Transaction handling (queries bypass cache during transactions)