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 -p 9090:9090 pgcache/pgcache \
  --upstream postgres://user:password@your-db-host:5432/myapp

This starts PgCache listening on port 5432 (proxy) and port 9090 (Prometheus metrics), proxying to your origin database.

Docker Compose Example

For a more complete setup:

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

You can also mount a TOML configuration file for version-controlled settings:

services:
  pgcache:
    image: pgcache/pgcache
    ports:
      - "5432:5432"
      - "9090:9090"
    volumes:
      - ./pgcache.toml:/etc/pgcache/config.toml:ro
    environment:
      CONFIG_FILE: /etc/pgcache/config.toml

Deploy on AWS

PgCache is available as a pre-built AMI on the AWS Marketplace. The AMI includes PgCache and an embedded PostgreSQL cache database, configured automatically at launch via AWS SSM Parameter Store. Contact us for access, or see the AWS Marketplace guide for setup instructions.

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. The Docker image exposes Prometheus metrics on port 9090 by default.

Query the 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

AWS Marketplace

The PgCache AMI is a pre-built Amazon Machine Image that runs PgCache as a systemd service on Amazon Linux 2023 (ARM64/Graviton). It includes an embedded PostgreSQL 18 instance for the cache database — no additional infrastructure is needed.

On first boot, a bootstrap script fetches your database credentials from AWS Systems Manager (SSM) Parameter Store, configures PgCache, and starts the service automatically.

Prerequisites

• Origin PostgreSQL database with wal_level = logical enabled
• Database user with REPLICATION role attribute or superuser
• AWS CLI installed and configured (aws configure)

See Getting Started for detailed origin database setup.

Quick Start

1. Store your database URL in SSM

aws ssm put-parameter --name "/pgcache/prod/upstream-url" \
  --type SecureString \
  --value "postgres://user:password@host:5432/dbname?sslmode=require"

The path prefix (/pgcache/prod) is passed to the instance at launch — use any naming convention you like.

2. Create an IAM role

The EC2 instance needs permission to read SSM parameters:

# Create the role
aws iam create-role \
  --role-name pgcache-ec2 \
  --assume-role-policy-document '{
    "Version": "2012-10-17",
    "Statement": [{
      "Effect": "Allow",
      "Principal": {"Service": "ec2.amazonaws.com"},
      "Action": "sts:AssumeRole"
    }]
  }'

# Grant SSM read access
aws iam put-role-policy \
  --role-name pgcache-ec2 \
  --policy-name pgcache-ssm-read \
  --policy-document '{
    "Version": "2012-10-17",
    "Statement": [{
      "Effect": "Allow",
      "Action": ["ssm:GetParameter"],
      "Resource": "arn:aws:ssm:*:*:parameter/pgcache/*"
    }]
  }'

# Create an instance profile and attach the role
aws iam create-instance-profile --instance-profile-name pgcache-ec2
aws iam add-role-to-instance-profile \
  --instance-profile-name pgcache-ec2 \
  --role-name pgcache-ec2

Adjust the Resource ARN if you use a prefix other than /pgcache/.

3. Create a security group

PgCache listens on port 5432 (proxy) and 9090 (metrics). Restrict access to your application’s network:

aws ec2 create-security-group \
  --group-name pgcache \
  --description "PgCache proxy"

# Allow PostgreSQL traffic from your application subnet
aws ec2 authorize-security-group-ingress \
  --group-name pgcache \
  --protocol tcp --port 5432 \
  --cidr 10.0.0.0/16

# Allow metrics scraping
aws ec2 authorize-security-group-ingress \
  --group-name pgcache \
  --protocol tcp --port 9090 \
  --cidr 10.0.0.0/16

Replace the CIDR ranges with your actual network addresses.

4. Launch the instance

Create a user-data script that calls the bootstrap with your SSM prefix:

cat > /tmp/userdata.sh <<'EOF'
#!/bin/bash
/opt/pgcache/bootstrap.sh --ssm-prefix /pgcache/prod
EOF

Launch the instance:

SG_ID=$(aws ec2 describe-security-groups \
  --group-names pgcache \
  --query 'SecurityGroups[0].GroupId' --output text)

aws ec2 run-instances \
  --image-id ami-XXXXXXXXX \
  --instance-type m6g.large \
  --key-name your-keypair \
  --iam-instance-profile Name=pgcache-ec2 \
  --network-interfaces "AssociatePublicIpAddress=false,DeviceIndex=0,Groups=${SG_ID}" \
  --user-data file:///tmp/userdata.sh

Set AssociatePublicIpAddress=true if you need external access.

5. Connect your application

Update your connection string to point at the PgCache instance:

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

# After
DATABASE_URL=postgres://user:password@pgcache-instance:5432/myapp

PgCache passes authentication through to the origin transparently — use the same credentials as before.

SSM Parameters

Store parameters under your chosen prefix. Only the upstream URL is required.

A separate replication URL is useful when your application connects through a connection pooler but CDC needs a direct PostgreSQL connection.

Bootstrap Options

Non-secret settings can be passed as flags in the user-data script:

/opt/pgcache/bootstrap.sh --ssm-prefix /pgcache/prod \
  --workers 4 \
  --cache-size 4294967296 \
  --allowed-tables users,orders,products

See Configuration for details on all settings.

Recommended Instance Types

The AMI is available on ARM64 Graviton m6g and m6gd instances from medium through 4xlarge. The embedded PostgreSQL is auto-tuned: 25% of RAM for shared_buffers, workers default to half of vCPUs.

Instance Store (NVMe)

Instance types with local NVMe storage — the m6gd variants — are automatically detected at boot. When present, the cache database is stored on the local NVMe disk instead of EBS, providing significantly lower I/O latency. No configuration is needed; the bootstrap script handles detection, formatting, and mounting.

Since cached data is rebuilt from the origin on every startup, the ephemeral nature of instance store is not a concern — there is no data loss risk.

Managing a Running Instance

View status and logs

sudo systemctl status pgcache          # Service status
sudo journalctl -u pgcache -f          # PgCache logs (live)
sudo tail -f /var/log/postgresql/cache.log  # PostgreSQL logs

Modify configuration

sudo vi /etc/pgcache/config.toml
sudo systemctl restart pgcache

Only PgCache needs to be restarted — the embedded PostgreSQL cache database does not.

Re-run bootstrap from SSM

If you update SSM parameters, regenerate the config:

sudo systemctl stop pgcache
sudo /opt/pgcache/bootstrap.sh --ssm-prefix /pgcache/prod

Metrics

Prometheus metrics are available at http://<instance>:9090/metrics. See Monitoring for the full list of available metrics.

Architecture

The AMI runs two systemd services:

• postgresql-cache.service — Embedded PostgreSQL 18 on port 5433 (localhost only). Stores cached query results.
• pgcache.service — PgCache proxy on port 5432. Accepts client connections, serves cached results, forwards non-cacheable queries to the origin.

Each instance automatically uses its EC2 instance ID as a CDC suffix, so multiple PgCache instances can run against the same origin database without publication or slot name conflicts.

File locations

Security

• Credentials are stored as SecureString in SSM and fetched at boot — never passed through user-data.
• The config file (/etc/pgcache/config.toml) is readable only by root and the postgres user.
• The embedded PostgreSQL listens on 127.0.0.1:5433 only — not externally accessible.
• Use security groups to restrict access to port 5432 and 9090 to trusted networks.
• For encrypted client connections, store TLS certificates in SSM using the /tls-cert and /tls-key parameters.

Configuration

PgCache supports three configuration methods: a TOML config file, CLI arguments, and Docker environment variables. These can be combined — CLI arguments override TOML file values, and Docker environment variables are mapped to CLI arguments by the container entrypoint.

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

General

Cache Behavior

The fields in this section plus log_level can be changed at runtime via the HTTP admin API (PUT /config) without restarting. See Monitoring for the admin endpoints.

Pinned Queries

TLS

Full Example Configuration

num_workers = 4
cache_size = 1073741824  # 1 GB
# cache_policy = "clock"       # or "fifo"
# admission_threshold = 2      # clock policy only
log_level = "info"

# Optional: restrict caching to specific tables
# allowed_tables = ["users", "orders", "products"]

# Optional: pin queries in cache at startup
# pinned_queries = [
#     "SELECT * FROM settings",
#     "SELECT * FROM categories",
# ]

# Optional: pin entire tables (shorthand for pinned_queries)
# pinned_tables = ["settings", "categories"]

[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

With a mounted TOML config file:

docker run -d -p 5432:5432 -p 9090:9090 \
  -v ./pgcache.toml:/etc/pgcache/config.toml:ro \
  -e CONFIG_FILE=/etc/pgcache/config.toml \
  pgcache/pgcache

With table allowlist and pinned tables:

docker run -d -p 5432:5432 \
  -e UPSTREAM_URL=postgres://user@db:5432/myapp \
  -e ALLOWED_TABLES=users,orders,products \
  -e PINNED_TABLES=users,products \
  pgcache/pgcache

With pinned queries:

docker run -d -p 5432:5432 pgcache/pgcache \
  --upstream postgres://user@db:5432/myapp \
  --pinned-queries "SELECT * FROM config;SELECT * FROM lookup"

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
• Correlated subqueries: EXISTS, NOT EXISTS, scalar, IN, NOT IN — automatically decorrelated into equivalent JOINs for caching. Only equality correlation predicates are supported
• Common Table Expressions: WITH (CTE) queries, including MATERIALIZED / NOT MATERIALIZED
• Set operations: UNION, UNION ALL, INTERSECT, EXCEPT
• Functions in the SELECT list
• Immutable functions in WHERE and JOIN clauses (lower(), upper(), abs(), etc.)
• LIMIT / OFFSET: Queries differing only in LIMIT share a cache entry
• Pattern matching: LIKE, NOT LIKE, ILIKE, NOT ILIKE
• Range expressions: BETWEEN, NOT BETWEEN, BETWEEN SYMMETRIC
• Array comparisons: = ANY(...), op ALL(...)
• Mixed wildcards and columns: SELECT *, id, name FROM ...

Not Cached

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

• Non-SELECT statements: INSERT, UPDATE, DELETE, DDL statements
• FULL JOIN and CROSS JOIN
• LATERAL subqueries
• Non-equality correlated predicates: Correlated subqueries using <, >, or OR-connected correlation
• RECURSIVE CTEs
• Non-immutable functions outside the SELECT list: Stable or volatile function calls in WHERE, JOIN, or other clauses
• Locking clauses: FOR UPDATE, FOR SHARE, etc.
• Views: Queries against views are forwarded to the origin

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. The publication is dynamically managed — only tables with active cached queries are included, reducing CDC overhead
3. When data changes on the origin (INSERT, UPDATE, DELETE, TRUNCATE), the change event is streamed to PgCache via logical replication
4. PgCache identifies which cached queries are affected by the change
5. Affected cache entries are updated directly when possible, or invalidated and re-populated on the next query

Table Allowlist

By default, PgCache considers all tables for caching. You can restrict caching to specific tables using the allowed_tables setting. When configured, only queries where every referenced table is in the allowlist will be cached. Other queries are forwarded to the origin. This is useful when you want fine-grained control over which data is cached.

allowed_tables = ["users", "orders", "products"]

Pinned Queries

Pinned queries are pre-populated into the cache at startup and protected from eviction. They are automatically re-populated after CDC invalidation. This ensures that critical queries are always served from cache.

pinned_queries = [
    "SELECT * FROM settings",
    "SELECT * FROM categories",
]

# Shorthand: pin entire tables
pinned_tables = ["settings", "categories"]

Each entry in pinned_tables is expanded to SELECT * FROM {table} and merged with pinned_queries.

Single-table pinned queries with no joins or subqueries are always kept up to date via CDC — they are updated in place and never invalidated. This makes them ideal for reference tables like settings, categories, or lookup data.

Cache Admission

Not every cacheable query is immediately populated into the cache. With the default CLOCK policy, a query must be seen multiple times (controlled by admission_threshold, default 2) before PgCache invests resources in caching it. This prevents one-off queries from consuming cache space. Queries that were previously cached and then invalidated by CDC events are fast-readmitted without waiting for the threshold.

Predicate Subsumption

When a new query arrives, PgCache checks whether its result set is already covered by an existing cached query. If so, the result can be served directly from the cached superset without querying the origin. Subsumption is per-column: for each column the cached query constrains, the new query’s constraint on that column must be implied by the cached constraint.

Examples of supported patterns:

• Full coverage — cached SELECT * FROM users covers SELECT * FROM users WHERE id = 1
• Narrower equality — cached WHERE tenant_id = 1 covers WHERE tenant_id = 1 AND status = 'active'
• Tighter range — cached WHERE value > 50 covers WHERE value > 100
• Range containment — cached WHERE value BETWEEN 10 AND 200 covers WHERE value >= 50 AND value <= 150
• Point within range — cached WHERE value > 50 covers WHERE value = 100
• IN-set subset — cached WHERE id IN (1, 2, 3, 4, 5) covers WHERE id IN (2, 3) and WHERE id = 4
• Range covers IN-set — cached WHERE value > 0 covers WHERE value IN (50, 100)

Subsumption is tracked via the pgcache.cache.subsumptions metric.

Cache Size Management

When cache_size is configured, PgCache enforces a maximum cache size in bytes. When the limit is reached, queries are evicted using a CLOCK (second-chance) algorithm: recently-accessed queries get a reprieve, while idle queries are evicted first. A FIFO policy is also available via the cache_policy setting.

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

The PgCache Docker image enables metrics on port 9090 by default. Publish the port to access them:

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

To use a different port, set the METRICS_PORT environment variable:

docker run -d -p 5432:5432 -p 8080:8080 \
  -e METRICS_PORT=8080 \
  -e UPSTREAM_URL=postgres://user:password@db:5432/myapp \
  pgcache/pgcache

When running pgcache outside Docker, add a [metrics] section to your TOML configuration:

[metrics]
socket = "0.0.0.0:9090"

Or use the CLI argument:

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

The HTTP server exposes several endpoints:

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.

CDC Connection Resilience

If the replication connection drops, pgcache automatically switches to forwarding all queries to the origin database while attempting to reconnect. The /readyz endpoint continues to return 200 during this period since the proxy is still serving queries (via origin). Once reconnected and the replication slot LSN is verified, cache dispatch resumes automatically.

Monitor pgcache.cdc.lag_bytes and pgcache.cdc.lag_seconds for replication health. A sudden spike in origin latency (pgcache.origin.latency_seconds) alongside cache hit ratio dropping to zero may indicate the CDC connection is temporarily down and queries are being forwarded.

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

Status Endpoint

The GET /status endpoint returns a JSON object with real-time cache, CDC, and per-query information. Status data is gathered on demand from the cache writer via message passing (2-second timeout). If the cache thread is unresponsive, the endpoint returns 503.

Response Structure

{
  "cache": {
    "size_bytes": 10485760,
    "size_limit_bytes": 1073741824,
    "generation": 42,
    "tables_tracked": 5,
    "policy": "clock",
    "queries_registered": 12,
    "uptime_ms": 3600000,
    "cache_hits": 15230,
    "cache_misses": 487
  },
  "cdc": {
    "tables": ["public.users", "public.orders"],
    "last_received_lsn": 12345678,
    "last_flushed_lsn": 12345600,
    "lag_bytes": 78
  },
  "queries": [
    {
      "fingerprint": 9876543210,
      "sql_preview": "SELECT * FROM users WHERE ...",
      "tables": ["public.users"],
      "state": "cached",
      "cached_bytes": 2048,
      "max_limit": null,
      "pinned": false,
      "hit_count": 1523,
      "miss_count": 12,
      "idle_duration_ms": 245,
      "registered_duration_ms": 3580000,
      "cached_duration_ms": 3500000,
      "invalidation_count": 3,
      "readmission_count": 2,
      "eviction_count": 1,
      "subsumption_count": 45,
      "population_count": 4,
      "last_population_duration_ms": 120,
      "total_bytes_served": 4915200,
      "population_row_count": 500,
      "cache_hit_latency": {
        "count": 1523,
        "mean_us": 245.3,
        "p50_us": 210,
        "p95_us": 480,
        "p99_us": 920,
        "min_us": 85,
        "max_us": 3200
      }
    }
  ]
}

Cache Status Fields

Per-Query Metrics

Each entry in the queries array includes operational metrics:

### Health Checks

Use `/healthz` and `/readyz` as Kubernetes or load balancer health probes:

```yaml
# Kubernetes example
livenessProbe:
  httpGet:
    path: /healthz
    port: 9090
readinessProbe:
  httpGet:
    path: /readyz
    port: 9090

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.

Telemetry

PgCache collects anonymous telemetry to help us understand how the software is being used. Telemetry is enabled by default and can be disabled at any time.

What We Collect

A small JSON payload is sent once at startup and then every 24 hours:

{
  "id": "a1b2c3d4-random-uuid",
  "version": "0.4.8",
  "pg_version": "16.2",
  "os": "linux",
  "arch": "x86_64",
  "deployment": "docker",
  "event": "heartbeat",
  "metrics": {
    "cached_queries": 12,
    "cache_hit_rate_pct": 72,
    "queries_served_24h": 15000,
    "uptime_hours": 168
  }
}

What We Never Collect

• SQL query text or query plans
• Table names, column names, or schema information
• Database credentials or connection strings
• IP addresses (not stored by the telemetry endpoint)
• Hostnames or environment variables
• Row data or query results

Why We Collect It

• Understand how many active PgCache instances exist
• Know which PostgreSQL versions to prioritize for compatibility
• See whether users are getting value from the cache (hit rates)
• Guide development priorities based on real usage patterns

How to Disable

Any of the following methods will disable telemetry:

Environment variable (recommended for Docker):

PGCACHE_TELEMETRY=off

TOML configuration file:

telemetry = false

CLI argument:

pgcache --telemetry_off ...

When telemetry is disabled, no network requests are made and no data is sent.

Technical Details

• Telemetry runs on a dedicated background thread and never affects proxy performance
• All network requests use a 10-second timeout and failures are silently ignored
• The instance ID is generated in memory at startup and not persisted to disk
• Data is sent via HTTPS POST to telemetry.pgcache.com

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

require encrypts the connection without verifying the server certificate (suitable for self-signed certs). verify-full additionally verifies the certificate against trusted public CAs.

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.4.8 — 2026-04-10

Features

• Range-based predicate subsumption — subsumption now handles inequality and BETWEEN constraints. A cached WHERE value > 50 serves narrower queries like WHERE value > 100, WHERE value BETWEEN 60 AND 80, or WHERE value = 100.
• IN-set constraint support — WHERE col IN (v1, v2, ...) is extracted as a first-class constraint used for subsumption (subset, point, and range checks) and CDC row filtering. NOT IN is handled as individual inequality constraints.
• Dynamic runtime configuration — cache_size, cache_policy, admission_threshold, allowed_tables, and log_level can be adjusted at runtime via new HTTP admin endpoints (GET /config, PUT /config, POST /config/reload). The TOML file remains the source of truth and is updated in place with comments preserved.
• Anonymous telemetry — PgCache reports coarse usage statistics (version, OS, Postgres version, cache hit rate, approximate query volume) to help guide development. Telemetry is enabled by default and can be disabled with PGCACHE_TELEMETRY=off, telemetry = false in TOML, or --telemetry_off on the CLI. See Telemetry for details.

Internals

• Constraint propagation through join equivalences now handles IN-sets (e.g., a.id IN (1,2) AND a.id = b.id propagates to b.id IN (1,2))

0.4.7 — 2026-03-27

Reliability

• CDC connection resilience — when the replication connection is lost, pgcache now preserves the cache and transparently forwards all queries to the origin database. On reconnect, it verifies the replication slot’s LSN hasn’t advanced past its last acknowledged position; if safe, cache dispatch resumes without a full restart. If the slot was dropped or the LSN diverged, pgcache performs a clean restart. Reconnection uses exponential backoff (500ms–30s).

Internals

• Improved LSN tracking in keep-alive handling — the CDC processor now advances its position to the logical decoder’s read cursor on keep-alive messages, enabling more accurate reconnect decisions
• Set TCP_NODELAY on sockets

0.4.6 — 2026-03-23

Features

• Per-query operational metrics — the /status endpoint now reports detailed metrics for each cached query: hit_count, miss_count, invalidation_count, eviction_count, readmission_count, subsumption_count, population_count, total_bytes_served, population_row_count, and cache_hit_latency histogram (p50/p95/p99/min/max/mean)
• Duration tracking — per-query idle_duration_ms (time since last hit), registered_duration_ms (time since first seen), cached_duration_ms (time since last population), and last_population_duration_ms
• Global cache status metrics — /status cache object now includes queries_registered, uptime_ms, cache_hits, and cache_misses

Internals

• Updated for latest hotpath profiling API changes
• Updated and cleaned up dependencies

0.4.5 — 2026-03-16

Features

• Admin HTTP endpoints — the metrics HTTP server now also serves /healthz (liveness), /readyz (readiness), and /status (JSON cache, CDC, and query status).
• Database mismatch detection — when a client connects requesting a different database than the cache is configured for, pgcache logs a warning and proxyies all queries to origin for that connection.

Performance

• Pool sizing from num_workers — cache connection pool and population worker pool sizes now scale automatically based on num_workers

Bug Fixes

• SELECT * column order — fixed SELECT * returning columns in non-deterministic order from cache.

Observability

• New per-stage timing metrics: pgcache.query.stage.queue_wait_seconds, pgcache.query.stage.conn_wait_seconds, pgcache.query.stage.spawn_wait_seconds — measure time waiting in worker queue, waiting for a cache database connection, and waiting for worker task spawn

Internals

• Replaced hand-rolled HTTP server with hyper
• Refactored TableMetadata column storage from BiHashMap to ColumnStore (ordered Vec + name index)
• Refactored test utilities into separate submodules (context, assertions, http, metrics, process, pgproto)
• Updated and cleaned up dependencies

0.4.4 — 2026-03-07

Reliability

• Graceful shutdown — SIGINT now triggers coordinated shutdown via cancellation tokens propagated to all threads. The publication and replication slot are removed during the shutdown.

Breaking Changes

• CLI argument --cache_tables renamed to --allowed_tables to match the TOML configuration field name

0.4.3 — 2026-03-06

Features

• Table allowlist — restrict caching to specific tables via allowed_tables configuration. Queries referencing non-allowlisted tables are forwarded to the origin.
• Pinned queries — pre-populate and permanently cache specific queries at startup via pinned_queries configuration. Pinned queries are protected from eviction and automatically re-populated after CDC invalidation
• Pinned tables — convenience configuration (pinned_tables) that expands table names into SELECT * FROM {table} pinned queries
• Predicate subsumption — skip cache population when a new query’s data is already covered by existing cached results (e.g., SELECT * FROM users WHERE id = 1 served immediately from a cached SELECT * FROM users)
• Config file support in Docker — mount a TOML config file into the container via CONFIG_FILE env var or --config CLI arg

Observability

• New pgcache.queries.allowlist_skipped metric counts queries rejected by the table allowlist
• New pgcache.cache.subsumptions metric counts queries served via predicate subsumption
• New pgcache.cache.subsumption_latency_seconds histogram tracks subsumption detection latency
• CORS headers added to the metrics HTTP endpoint

Docker

• New environment variables: CONFIG_FILE, ALLOWED_TABLES, PINNED_QUERIES, PINNED_TABLES
• New CLI arguments: --config, --allowed-tables, --pinned-queries, --pinned-tables
• UPSTREAM_URL is no longer required when a config file is provided

Internals

• Dynamic publication management reduces CDC bandwidth by only subscribing to tables with active cached queries
• EcoString used in table constraints for memory efficiency

0.4.2 — 2026-02-28

Bug Fixes

• Fixed TLS reads losing unconsumed ciphertext across poll calls
• Fixed TLS partial writes dropping data

Observability

• New pgcache.cache.freshness_hits metric counts cached queries confirmed fresh (not invalidated) by each CDC event

0.4.1 — 2026-02-27

Security & Connectivity

• ssl_mode = "require" now matches PostgreSQL semantics: encrypts the connection without verifying the server certificate, allowing connections to servers with self-signed or private CA certificates
• New ssl_mode = "verify-full" option: encrypts the connection and verifies the server certificate against trusted public CAs (the previous behavior of require)

0.4.0 — 2026-02-27

Query Language Support

• Correlated scalar subqueries
• Correlated IN/ANY subqueries
• Correlated NOT IN/ALL subqueries
• IS TRUE, IS NOT TRUE, IS FALSE, IS NOT FALSE, IS UNKNOWN, IS NOT UNKNOWN expressions
• Locking clauses (FOR UPDATE, FOR SHARE, etc.) are now properly forwarded to the origin as uncacheable

Performance

• More precise CDC invalidation for DELETE events on inner join tables

Internals

• Split ast.rs and resolved.rs into separate submodule files for maintainability
• Refactored query dispatch, connection handling, query registration, and population streaming
• Replaced boxed CacheableQuery with Arc-based sharing

0.3.8 — 2026-02-23

Query Language Support

• EXISTS and NOT EXISTS subqueries

Bug Fixes

• Fixed count(*) handling in cached function expressions

Internals

• Refactored update query generation to work exclusively with the resolved AST
• Reorganized transform and query update logic into a clearer file layout
• Replaced expect() calls with proper error handling
• Grouped related fields in ConnectionState for better organization

0.3.7 — 2026-02-22

Query Language Support

• Predicate pushdown for subqueries and CTEs
• Mixed wildcards and explicit columns in SELECT lists (SELECT *, id, name FROM ...)

Internals

• Overhauled extended query protocol handling for improved Parse/Bind/Describe/Execute support

0.3.6 — 2026-02-18

Query Language Support

• Immutable function calls in WHERE and JOIN clauses (WHERE lower(name) = 'foo') — function volatility loaded from pg_proc at startup

Performance

• Cache admission policy — queries must be seen multiple times before being cached (configurable via admission_threshold, default 2)
• CLOCK eviction — second-chance algorithm replaces FIFO (configurable via cache_policy)
• Fast readmission of CDC-invalidated queries — metadata retained so re-seen queries skip the admission threshold

Observability

• New metrics: pgcache.cache.queries_pending, pgcache.cache.queries_invalidated, pgcache.cache.readmissions
• Improved error reporting when the metrics server fails to start

0.3.5 — 2026-02-16

Query Language Support

• LIMIT and OFFSET clause caching — queries differing only in LIMIT/OFFSET share a cache entry, with automatic re-population when a larger limit is requested
• BETWEEN, NOT BETWEEN, BETWEEN SYMMETRIC, and NOT BETWEEN SYMMETRIC expressions
• ANY and ALL expressions (= ANY(ARRAY[...]), op ALL(...)) with array literals and parameters
• LIKE, NOT LIKE, ILIKE, and NOT ILIKE pattern matching expressions

Performance

• Inequality constraint checking for CDC invalidation — UPDATE and DELETE events on joined tables are filtered using <, <=, >, >=, and != constraints, reducing unnecessary cache invalidation
• CDC worker sends commands directly to the writer thread, eliminating an intermediate message hop through the runtime event loop

Bug Fixes

• NULL values in CDC update queries now carry type casts (NULL::typename) for correct PostgreSQL type inference in VALUES clauses

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

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)