Spatial IaC Terraform & Pulumi for GIS

Provisioning Read Replicas for PostGIS Tile Queries

When a single PostGIS primary starts to buckle under the read pressure of on-the-fly vector tile generation — ST_AsMVT aggregations, bounding-box index scans, and geometry simplification all competing with ingestion writes — the fix is to offload those read-only spatial queries onto RDS read replicas. This guide covers provisioning replicas in Terraform, routing tile reads to them, and reasoning about replication lag on freshly-ingested layers. It extends PostGIS Cluster Provisioning within the Geospatial Resource Provisioning framework, and it pairs the storage tier with the Compute Node Orchestration layer that scales the tile renderers doing the reading.

When to Add a Replica: Signals and Triage

A read replica is the right answer to a specific shape of load, and the wrong answer to several others. Before provisioning, classify the pressure the primary is under — adding a replica to a write-bound or lock-bound primary buys nothing and adds lag risk.

  • CPU and read IOPS saturated by SELECTs, writes healthy: pg_stat_statements shows tile-generating queries (ST_AsMVT, ST_Intersects over a GiST index) dominating total time, while xact_commit on writes stays flat. This is the textbook replica case — the read workload is horizontally separable.
  • Connection slots exhausted by read fan-out: the publishing tier opens more pooled connections than the primary can serve, and FATAL: remaining connection slots appears. A replica moves the read connections off the primary; pair it with a pooler as covered below.
  • Write latency or lock waits, not read volume: if the primary is slow because of long index builds or VACUUM contention, a replica does not help — that is a primary-sizing or maintenance-window problem, and a replica will simply inherit the write stream as lag.
  • A single hot layer, not the whole database: if one frequently-tiled layer is the bottleneck, a materialized tile cache or a per-layer replica may beat scaling the entire cluster.

The decision tree below maps each signal to the right action so a replica is provisioned only when the load is genuinely read-separable.

Decision tree for adding a PostGIS read replica for tile queries Start from a PostGIS primary under pressure. Inspect pg_stat_statements and connection metrics. Branch on the dominant signal. If read-only tile queries dominate CPU and IOPS while writes stay healthy, provision a read replica and route tile reads to it. If connection slots are exhausted by read fan-out, add a replica behind a connection pooler. If the pressure is write latency or lock contention rather than read volume, a replica will not help — resize the primary or fix the maintenance window instead, because the replica would only inherit the write stream as lag. Primary under pressure tile reads slowing Inspect pg_stat_statements + connection metrics Reads dominate writes healthy Slots exhausted read fan-out Write / lock bound not read volume Provision replica, route tile reads Replica behind a connection pooler Resize primary / fix maintenance window A replica only helps read-separable load; write and lock contention need a different fix.

Prerequisites and Environment Assumptions

This guide assumes an existing managed PostGIS primary provisioned as in PostGIS Cluster Provisioning, and adds one or more read replicas to it:

  • Terraform >= 1.6 with hashicorp/aws pinned at ~> 5.60. Reuse the module structure from How to Structure Terraform Modules for PostGIS so the replica is a parameterized addition, not a copy-paste of the primary.
  • A source instance with automated backups enabled. RDS requires backup_retention_period > 0 on the primary before it will create a read replica; a primary with backups disabled cannot be a replication source.
  • Storage-encryption alignment. If the primary is encrypted, replicas inherit encryption and need the KMS key grants in place; a cross-region replica needs a key in the destination region.
  • A read-capable connection path. The replica sits in the same subnet group and security-group posture as the primary, accepting 5432 from the rendering tier only — never widened for the replica.

Step-by-Step Provisioning

Provision the replica, tune it for read-heavy spatial scans, then route tile reads to it — in that order, so routing never points at an instance that is not yet ready.

  1. Create the replica from the primary. Declare an aws_db_instance with replicate_source_db set to the primary’s identifier. Give the replica its own parameter group so read-optimized tuning does not disturb the primary’s write path.

  2. Tune the replica parameter group for read-heavy spatial scans. Tile generation runs large sort and hash operations over geometry, so work_mem matters more on the replica than on the primary, and effective_cache_size should reflect the replica’s own RAM. RDS memory parameters are integers in units of 8 kB blocks, never percentages or MB literals.

    Note: AWS RDS parameter-group memory values are expressed as an integer count of 8 kB blocks, not as bytes, megabytes, or percentages. To convert a target size to the RDS unit, divide the bytes by 8192: work_mem = 262144 blocks = 262144 × 8 kB = 2 GB, and effective_cache_size = 6291456 blocks = 6291456 × 8 kB ≈ 48 GB (a typical value for a 64 GB replica, leaving headroom for the OS and connections). shared_buffers is managed by RDS on the instance class and should not be set in a custom parameter group.

  3. Route read-only tile queries to the replica endpoint. Point the tile renderer’s read path at the replica’s endpoint (or a pooled endpoint fronting several replicas) while keeping writes and ingestion on the primary. The cleanest split is a read-only connection string consumed by the Compute Node Orchestration tile fleet, distinct from the writer string used by ingestion workers.

  4. Account for replica lag on freshly-ingested layers. Asynchronous replication means a layer just written to the primary is not instantly on the replica; a tile request for a brand-new feature can render stale until the WAL catches up. Route reads that must be read-your-writes (an editor previewing a just-saved edit) to the primary, and everything else — the vast majority of tile traffic — to the replica.

terraform {
  required_version = ">= 1.6.0"
  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "~> 5.60"
    }
  }
}

variable "primary_identifier" { type = string }
variable "replica_count" {
  type    = number
  default = 1
}

# Read-optimized parameter group for the replica tier.
# All memory values are integer counts of 8 kB blocks (see the Note above).
resource "aws_db_parameter_group" "replica_read_tuning" {
  name   = "postgis-replica-read-params"
  family = "postgres15"

  parameter {
    name  = "work_mem"
    value = "262144" # 2 GB in 8 kB blocks — large sorts for ST_AsMVT aggregation
  }
  parameter {
    name  = "effective_cache_size"
    value = "6291456" # ~48 GB in 8 kB blocks — reflects the replica's own RAM
  }
  parameter {
    name  = "random_page_cost"
    value = "1.1" # SSD-backed GiST index scans
  }
}

# One or more read replicas of the existing primary.
resource "aws_db_instance" "postgis_replica" {
  count                = var.replica_count
  identifier           = "spatial-prod-replica-${count.index}"
  replicate_source_db  = var.primary_identifier # makes this a read replica
  instance_class       = "db.r6g.2xlarge"
  parameter_group_name = aws_db_parameter_group.replica_read_tuning.name

  # Replicas inherit storage encryption and the subnet/SG posture of the source.
  storage_encrypted   = true
  auto_minor_version_upgrade = false # keep engine minor pinned for extension parity

  # Never expose the replica more widely than the primary.
  publicly_accessible = false
  skip_final_snapshot = true # replicas hold no unique data; snapshot the primary

  tags = {
    Role      = "read-replica"
    Workload  = "tile-queries"
    ManagedBy = "terraform"
  }
}

output "replica_endpoints" {
  value = aws_db_instance.postgis_replica[*].endpoint
}

Verification

Confirm the replica is serving reads and tracking the primary before you shift real tile traffic:

  • Replication is live and lag is bounded. Check the CloudWatch ReplicaLag metric and, on the replica, SELECT now() - pg_last_xact_replay_timestamp(); — lag should sit in the low seconds under normal write load. A steadily climbing value means the replica cannot keep up with the write stream.
  • Reads land on the replica. Run a representative tile query (SELECT ST_AsMVT(...) over a bbox) against the replica endpoint and confirm it returns; SELECT pg_is_in_recovery(); must return t on the replica, proving you are reading the standby, not the primary.
  • Writes are refused on the replica. An attempted INSERT on the replica must fail with cannot execute INSERT in a read-only transaction, confirming the read path cannot accidentally mutate the standby.
  • Extension parity holds. SELECT postgis_full_version(); on the replica must match the primary’s pinned extension matrix from the PostGIS Cluster Provisioning baseline — a minor-version drift produces subtly different geometry results between tiers.

Preventing Recurrence

  • Codify the read/write split. Keep the writer and read-only connection strings as separate module outputs so a renderer can never accidentally send ST_AsMVT load to the primary or an editor’s write to the replica. This is the output-contract discipline from How to Structure Terraform Modules for PostGIS.
  • Alert on lag, not just on failure. Page when ReplicaLag exceeds the freshness budget for your layers, because a lagging replica silently serves stale tiles long before it errors.
  • Front replicas with a pooler. Put PgBouncer or RDS Proxy ahead of the replica tier so the Compute Node Orchestration fleet can scale renderers without exhausting replica connection slots.
  • Recompute memory units on resize. Every work_mem and effective_cache_size value is in 8 kB blocks tied to the instance class — recalculate them whenever the replica class changes, and never introduce a percentage or MB literal that RDS will reject.

Frequently Asked Questions

Why do freshly-ingested layers sometimes render stale tiles from the replica?

RDS read replicas use asynchronous replication, so a feature just written to the primary is not instantly present on the replica until the WAL replays. Route read-your-writes requests (an editor previewing a just-saved edit) to the primary and send the bulk of tile traffic to the replica, where a few seconds of lag is invisible.

Should I set shared_buffers on the replica parameter group?

No. On managed RDS, shared_buffers is set by AWS according to the instance class and must not appear in a custom parameter group. Tune work_mem and effective_cache_size instead, both expressed as integer counts of 8 kB blocks.

A replica will not create — what is the most common cause?

The source instance has automated backups disabled. RDS requires backup_retention_period > 0 on the primary before it can act as a replication source. Enable backups on the primary, then provision the replica.

Will a read replica fix write latency on the primary?

No. A replica offloads read-only queries; it does nothing for a primary that is slow because of long index builds, VACUUM contention, or write locks. That is a primary-sizing or maintenance-window problem, and the replica would simply inherit the write stream as lag.