Skip to content

replicas.mdx

Latest commit

 

History

History
302 lines (232 loc) · 10.3 KB

replicas.mdx

File metadata and controls

302 lines (232 loc) · 10.3 KB
Raw
title Read Replicas
icon BookOpen
description Distribute read load across PostgreSQL replicas with per-query SQL annotations and automatic health management.

Read replicas let you distribute read traffic across multiple database instances. Instead of sending every query to a single primary, you can route eligible reads to replicas, reducing load on the primary and improving read throughput for heavy workloads.

PgBeam supports two routing modes: per-query annotations and auto read routing. With annotations, you explicitly mark queries that should go to a replica using a SQL comment. With auto read routing, PgBeam automatically sends all SELECT queries to replicas without annotations. You choose the mode that fits your consistency requirements.

When to use read replicas

Read replicas are a good fit when:

  • Your primary database is CPU or connection constrained by read traffic
  • You have analytics or reporting queries that can tolerate slightly stale data
  • You want to separate OLTP traffic (primary) from heavier read patterns (replica)
  • You are running dashboards or background jobs that do not need real-time data

Read replicas are not a good fit when:

  • Every read must see the latest committed write (no replication lag tolerance)
  • Your bottleneck is write throughput, not read throughput
  • You have very few distinct queries that would benefit from routing

Add a replica

Register one or more read replicas for a database. Each replica needs its own connection details. PgBeam connects to it independently from the primary.

<Tabs items={["Dashboard", "API", "CLI"]}> Navigate to your project, select a database, and click Add Replica. Enter the replica's connection details: host, port, database name, and SSL mode.

```bash curl -X POST \ https://api.pgbeam.com/v1/projects/{projectId}/databases/{databaseId}/replicas \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{ "host": "replica.example.com", "port": 5432, "database": "mydb", "ssl_mode": "verify-full" }' ``` ```bash pgbeam replicas add --database-id \ --host replica.example.com \ --port 5432 \ --database mydb \ --ssl-mode verify-full ``` You can add multiple replicas. PgBeam distributes annotated reads across all healthy replicas using round-robin.

Route queries to replicas

Annotate individual queries with /* @pgbeam:replica */ to route them to a replica:

/* @pgbeam:replica */ SELECT * FROM products WHERE active = true;

PgBeam strips the annotation before forwarding the query, so the upstream database never sees the comment.

Routing rules

Query type Where it goes
Read with @pgbeam:replica Round-robin across healthy replicas
Read without annotation Primary database
Write (INSERT/UPDATE/DELETE) Primary database
Any query inside a transaction Primary database

Why annotations are the default

Automatic read/write splitting is convenient but introduces a subtle problem: replication lag. If your application writes a row and immediately reads it back, an automatic splitter might route the read to a replica that hasn't received the write yet, returning stale or missing data.

Annotations are the default because they ensure you make a conscious decision about which queries can tolerate lag. Queries where consistency matters stay on the primary by default.

Auto read routing

For workloads where most reads can tolerate replication lag, you can enable auto read routing on a per-database basis. When enabled, PgBeam automatically routes all SELECT queries to replicas without requiring the @pgbeam:replica annotation.

<Tabs items={["Dashboard", "API", "CLI"]}> Navigate to your project, select a database, and go to Settings. Enable Auto read routing.

```bash curl -X PATCH \ https://api.pgbeam.com/v1/projects/{projectId}/databases/{databaseId} \ -H "Authorization: Bearer " \ -H "Content-Type: application/json" \ -d '{ "auto_read_routing": true }' ``` ```bash pgbeam db update --auto-read-routing ```

Requirements

  • At least one replica must be configured on the database
  • Disabled by default to preserve backwards compatibility

What changes with auto read routing

Query type Without auto routing With auto routing
SELECT without annotation Primary database Round-robin across replicas
SELECT with @pgbeam:replica Round-robin across replicas Round-robin across replicas
INSERT / UPDATE / DELETE Primary database Primary database
Any query inside a transaction Primary database Primary database

When to use auto read routing

Auto read routing is a good fit when:

  • The majority of your reads can tolerate replication lag
  • You want the simplicity of automatic routing without annotating every query
  • Your workload is read-heavy and you want to offload the primary

It is not a good fit when:

  • Many reads require read-your-writes consistency
  • You need fine-grained control over which queries go to replicas
  • Replication lag is unpredictable and varies significantly
With auto read routing enabled, a `SELECT` immediately after an `INSERT` may return stale data if the replica hasn't caught up. For read-after-write consistency, wrap both statements in a transaction. Queries inside transactions always go to the primary.

ORM and driver examples

Most ORMs support raw SQL or template literals where you can include the annotation:

<Tabs items={["Prisma", "Drizzle", "psycopg", "pgx", "JDBC"]}> ts const products = await prisma.$queryRaw` /* @pgbeam:replica */ SELECT * FROM products WHERE active = true `;

```ts import { sql } from "drizzle-orm"; 
const products = await db.execute(
  sql`/* @pgbeam:replica */ SELECT * FROM products WHERE active = true`,
);
```
```python cur.execute( "/* @pgbeam:replica */ SELECT * FROM products WHERE active = true" ) ``` ```go rows, err := pool.Query(ctx, "/* @pgbeam:replica */ SELECT * FROM products WHERE active = true") ``` ```java ResultSet rs = stmt.executeQuery( "/* @pgbeam:replica */ SELECT * FROM products WHERE active = true"); ```

Combining replicas with caching

Replica routing and caching can work together. A query can be both replica-routed and cached:

/* @pgbeam:replica */ /* @pgbeam:cache maxAge=300 */ SELECT * FROM products;

The evaluation order is:

  1. PgBeam checks the cache first
  2. On a cache miss, the query is routed to a replica (if annotated) or the primary
  3. The result is cached for future requests

This means cache hits are served from the local data plane without contacting any upstream at all: not the primary and not the replica.

Testing and debugging

Use debug mode to verify which upstream handled each query:

SET pgbeam.debug = on;
/* @pgbeam:replica */ SELECT 1;
-- NOTICE: pgbeam:cache miss

The NOTICE output reports the cache status for the query: hit, miss, or a stale hit (with age, ttl, and swr on a hit). A replica-routed read is not cached, so it shows as a miss.

Health checks and failover

PgBeam runs background health checks against each replica independently. The health check system is automatic. There is nothing to configure.

Event What PgBeam does
Replica health check fails Replica is removed from rotation
Replica recovers Re-added to rotation after consecutive successes
All replicas are unhealthy Annotated reads fall back to the primary database

This means replica failures are transparent to your application. A /* @pgbeam:replica */ query always succeeds. It just falls back to the primary if no healthy replica is available.

Replication lag considerations

PostgreSQL streaming replication is asynchronous by default. This means there is always some delay (typically milliseconds, but potentially seconds under load) between a write on the primary and the same data appearing on a replica.

Queries safe for replica routing:

  • Product catalogs, blog posts, static content lookups
  • Analytics and reporting queries
  • Search results, recommendations, leaderboards
  • Configuration and feature flag reads

Queries that should stay on the primary:

  • Reading data immediately after writing it ("read-your-writes")
  • Queries used in transactional flows where consistency is critical
  • Real-time balance checks, inventory counts, or seat availability

Limitations

  • Without auto read routing enabled, replica routing only applies to queries with the /* @pgbeam:replica */ annotation.
  • Queries inside transactions always go to the primary, even if annotated or with auto read routing enabled. This prevents split-brain reads within a transaction.
  • All replicas receive equal traffic via round-robin. Weighted routing is not supported.
  • PgBeam does not provision or manage replicas. You create them in your database provider and register them in PgBeam.

Further reading

  • Caching: Combine replica routing with query caching
  • Routing & Regions: How global routing interacts with replica routing
  • Resilience: Health check details and failover behavior