Offloading Read-Heavy PostgreSQL Traffic with a Scalingo Read Replica

Your Scalingo-hosted PostgreSQL database is not slow because of writes. It is slow because dashboards, list endpoints, and background reports are hammering the primary with SELECTs while the actual write workload is modest. The useful takeaway: a managed read replica can absorb that read traffic, but only if your application deliberately sends reads to a second endpoint. Nothing routes for you automatically.

The problem: reads drown out writes

A typical web app ends up with a read-to-write ratio of 10:1 or worse once product pages, search, and admin reporting pile onto the same database. On a single primary, every one of those queries competes for CPU, buffer cache, and connections with the writes that actually matter. Symptoms show up as rising connection counts, slower commit latency, and p95 response times that creep up during business hours.

Before reaching for a replica, be honest about the cause. A replica does not fix missing indexes, N+1 queries from an ORM, lock contention, or a connection pool sized for a much bigger machine. If pg_stat_statements shows one unindexed query dominating, fix that first — it will be slow on the replica too.

What a Scalingo replica actually gives you

On Scalingo, a read replica is best thought of as a second PostgreSQL endpoint attached to your database add-on. You get another host and port to connect to; Scalingo handles the replication plumbing. What you do not get is transparent load balancing. Your code must keep writes on the primary URL and send eligible SELECTs to the replica URL.

Plan-level details — whether your database plan includes replica support, in which regions, and at what price — change over time. Check the current Scalingo documentation and your dashboard before committing to this design. The rest of this post assumes you have confirmed a replica endpoint exists for your add-on and you have noted its host, port, database name, user, and TLS requirements.

Worked example: routing reads in Spring Boot

The cleanest pattern is two DataSources: a primary marked @Primary for writes, and a replica DataSource used by read-only service methods. A common approach is an AbstractRoutingDataSource keyed off Spring's transaction read-only flag:

@Configuration
public class DataSourceConfig {

  @Bean
  @Primary
  @ConfigurationProperties("spring.datasource.primary")
  public DataSource primaryDataSource() {
    return DataSourceBuilder.create().build();
  }

  @Bean
  @ConfigurationProperties("spring.datasource.replica")
  public DataSource replicaDataSource() {
    return DataSourceBuilder.create().build();
  }

  @Bean
  public DataSource routingDataSource(DataSource primaryDataSource,
                                      DataSource replicaDataSource) {
    var routing = new AbstractRoutingDataSource() {
      @Override
      protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager
            .isCurrentTransactionReadOnly() ? "replica" : "primary";
      }
    };
    routing.setTargetDataSources(Map.of(
        "primary", primaryDataSource,
        "replica", replicaDataSource));
    routing.setDefaultTargetDataSource(primaryDataSource);
    return routing;
  }
}

Then annotate read paths explicitly:

@Service
public class CatalogService {

  @Transactional(readOnly = true)
  public List<Product> search(String query) {
    // routed to the replica
  }

  @Transactional
  public Order placeOrder(Cart cart) {
    // routed to the primary
  }
}

Configuration comes from environment variables, which is how Scalingo exposes connection strings. Keep the primary URL in the variable your add-on provides and add a second variable for the replica URL. Two rules make this safe:

• Default to the primary. If the lookup key is missing or the replica is down at startup, writes must never accidentally target a read-only endpoint. A startup check that runs SELECT pg_is_in_recovery() against each DataSource and fails fast if the "primary" returns true is cheap insurance.
• Put routing behind a flag. An environment variable that disables replica routing lets you revert behavior with a redeploy, without a code change.

The trade-off you cannot design away: lag

Replication to the replica is asynchronous in practice. A row committed on the primary may not be visible on the replica for some short, variable window. This breaks read-your-writes flows: a user updates their profile, the next page load reads from the replica, and they see stale data.

The pragmatic policy is to route by freshness requirement, not by query type:

• Catalog browsing, search, public listings, analytics-style reports: replica.
• Anything immediately after a write in the same request, checkout, account settings: primary.

Measure the actual lag rather than assuming a number. Write a row on the primary, read it from the replica in a loop, and record how long until it appears under realistic load. PostgreSQL exposes replication delay via functions such as pg_last_wal_receive_lsn() / pg_last_wal_replay_lsn() on the replica, or now() - pg_last_xact_replay_timestamp() as a rough seconds-behind estimate. Document what you observe; do not promise a fixed bound.

Verify it actually worked

Do not trust the routing code — observe the traffic:

1. Deploy with the routing flag on. Run a known read-only endpoint, then check pg_stat_activity on the replica (or the database logs in the Scalingo dashboard) for your query. If it shows up on the primary instead, your routing key is wrong.
2. Run a modest load test against read-heavy endpoints. Compare primary CPU, connection count, and query latency before and after. You should see read connections shift to the replica and primary latency improve; if nothing moves, the traffic you targeted was not the traffic causing the load.
3. Exercise the failure path: temporarily misconfigure the replica URL in a staging app and confirm the app fails fast or falls back deliberately — never silently writes toward a replica.

Where to start

Add one replica, route one clearly read-only workload (search or catalog listing is a good candidate), and measure for a week: read/write split, observed lag, connection counts on both endpoints, and slow query logs. That data tells you whether the next move is a bigger replica plan, more indexes, or pulling reporting queries out of the transactional path entirely. The replica is a routing decision your code owns — treat it that way and it earns its cost.