Spatial IaC Terraform & Pulumi for GIS

Tuning RDS Parameter Groups for PostGIS Workloads

A managed PostgreSQL instance arrives configured for a generic transactional workload: many small queries, short-lived sorts, modest working sets. A PostGIS instance serving tiles and running raster analysis is the opposite on every axis — geometry columns are large, spatial joins sort and hash aggressively, GiST index builds are memory-hungry, and a single ST_Intersects against un-generalized polygons can allocate more working memory than a hundred ordinary queries combined. Tuning the parameter group is therefore not an optimisation; it is the difference between a database that serves a tile fleet and one that spills every spatial sort to disk. This guide extends PostGIS Cluster Provisioning within Geospatial Resource Provisioning, and it begins with the unit mistake that invalidates most tuning attempts before they start.

Units matter and are not obvious. Managed PostgreSQL expresses memory parameters in 8 kB blocks, not bytes and not percentages. shared_buffers = 2048 is 16 MB, not 2 GB. A value written as a percentage string is accepted by the API in some parameter contexts and silently means something else. Every memory value in this guide is in 8 kB blocks unless stated otherwise, and every value you copy from a self-managed postgresql.conf tutorial is not.

Symptom identification and triage

Four observable symptoms map to four distinct parameters, and treating the wrong one is the usual reason tuning “does nothing”.

Spatial sorts spilling to disk. EXPLAIN (ANALYZE, BUFFERS) on a tile query shows Sort Method: external merge Disk: 84MB. The sort did not fit in working memory and was written to temporary files. This is work_mem, and it is per sort node per connection — a single query with three sort nodes can allocate three times the value, which is why raising it globally is dangerous.

Poor cache behaviour under tile load. The buffer cache hit ratio sits well below the high nineties while the working set would comfortably fit in memory. Geometry columns are wide, so a PostGIS working set is larger than the row count suggests. This is shared_buffers.

Sequential scans where an index exists. The planner chooses a sequential scan on a table with a valid GiST index. Frequently this is random_page_cost left at the spinning-disk default of 4.0 on an instance backed by solid-state storage, which makes the planner systematically overestimate the cost of index access.

Index builds and vacuums that take hours. A GiST index rebuild on a large geometry table is dominated by maintenance_work_mem, which defaults low. This one is safe to set generously because it applies only to maintenance operations, of which few run concurrently.

Symptom to parameter mapping for PostGIS tuning Four rows pairing an observable symptom with the parameter responsible. External merge sorts written to disk during a tile query indicate insufficient work memory, which is allocated per sort node per connection and therefore multiplies dangerously. A buffer cache hit ratio below the high nineties indicates shared buffers too small for a working set made large by wide geometry columns. A sequential scan chosen despite a valid GiST index usually indicates random page cost left at the spinning-disk default of four on solid-state storage. Index builds and vacuums measured in hours indicate maintenance work memory, which is safe to raise because few maintenance operations run at once. Sort Method: external merge Disk — a tile query spilling work_mem per sort node, per connection cache hit ratio below the high nineties under tile load shared_buffers geometry makes the set wide sequential scan despite a valid GiST index random_page_cost 4.0 is a spinning-disk default GiST rebuilds and vacuums measured in hours maintenance_work_mem safe to raise generously

Prerequisites and environment assumptions

Terraform 1.6 or later with hashicorp/aws pinned at ~> 5.60. A custom parameter group rather than the default one, because the default group cannot be modified and the attempt fails confusingly. Knowledge of the instance’s memory in gigabytes, since every value below is a fraction of it. A representative workload to measure against — tuning against an idle instance produces numbers that are arithmetic rather than evidence.

Understand which parameters require a reboot before you plan. shared_buffers and max_connections are static: applying them schedules a pending change that takes effect at the next reboot, and a team that applies and then measures without rebooting will conclude the change did nothing. work_mem, random_page_cost and maintenance_work_mem are dynamic and take effect immediately.

Step-by-step tuning

  1. Set shared_buffers to about a quarter of instance memory. For a 32 GB instance that is 8 GB, which in 8 kB blocks is 8 * 1024 * 1024 / 8 = 1048576. Going much beyond a quarter rarely helps on managed PostgreSQL because the operating system cache is doing useful work with the remainder, and going far beyond it can degrade performance.

  2. Set work_mem from the concurrency budget, not from instinct. The safe ceiling is roughly (instance_memory − shared_buffers) / max_connections / expected_sort_nodes_per_query. For a 32 GB instance with 8 GB of buffers, 200 connections and two sort nodes per query, that is about 60 MB. Spatial queries genuinely need more than a transactional default, but a value that is safe at ten connections will exhaust memory at two hundred.

  3. Raise maintenance_work_mem generously. 2 GB is reasonable on a large instance. This governs index builds, VACUUM and CREATE INDEX, and since few run concurrently the multiplication risk that constrains work_mem does not apply.

  4. Lower random_page_cost to match the storage. On solid-state storage 1.1 is a defensible value; the default 4.0 encodes an assumption about seek latency that has not been true for the storage class most spatial databases run on for a decade. This single change often converts the sequential scans in symptom three into index scans.

  5. Set effective_cache_size to roughly three quarters of instance memory. It allocates nothing; it tells the planner how much cache it may assume exists, and leaving it at a small default makes the planner pessimistic about index access in exactly the way that hurts spatial queries.

  6. Apply, reboot if a static parameter changed, then measure. Re-run the query that showed the symptom and confirm the plan changed.

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

variable "instance_memory_gb" { type = number }
variable "max_connections" { type = number }

locals {
  # UNITS: these parameters are in 8 kB blocks. 1 GB = 131072 blocks.
  blocks_per_gb = 131072

  shared_buffers_blocks = floor(var.instance_memory_gb * local.blocks_per_gb * 0.25)

  # work_mem is per sort node per connection. Two sort nodes per query is a
  # conservative assumption for spatial joins; three is not unusual.
  work_mem_blocks = floor(
    (var.instance_memory_gb * local.blocks_per_gb * 0.75) / var.max_connections / 2
  )

  maintenance_work_mem_blocks = 2 * local.blocks_per_gb

  # effective_cache_size allocates nothing — it is advice to the planner.
  effective_cache_size_blocks = floor(var.instance_memory_gb * local.blocks_per_gb * 0.75)
}

resource "aws_db_parameter_group" "postgis" {
  name   = "postgis-tuned-16"
  family = "postgres16"

  parameter {
    name         = "shared_buffers"
    value        = tostring(local.shared_buffers_blocks)
    apply_method = "pending-reboot" # static: nothing changes until a reboot
  }

  parameter {
    name         = "work_mem"
    value        = tostring(local.work_mem_blocks)
    apply_method = "immediate"
  }

  parameter {
    name         = "maintenance_work_mem"
    value        = tostring(local.maintenance_work_mem_blocks)
    apply_method = "immediate"
  }

  parameter {
    # Not a memory parameter — a plain float, and the one change that most often
    # converts a sequential scan into an index scan on SSD-backed storage.
    name         = "random_page_cost"
    value        = "1.1"
    apply_method = "immediate"
  }

  parameter {
    name         = "effective_cache_size"
    value        = tostring(local.effective_cache_size_blocks)
    apply_method = "immediate"
  }

  parameter {
    # Surfaces the slow spatial queries this tuning exists to fix.
    name         = "shared_preload_libraries"
    value        = "pg_stat_statements"
    apply_method = "pending-reboot"
  }
}
Dividing instance memory, and where work_mem multiplies A bar representing total instance memory. About a quarter is reserved for shared buffers, a fixed allocation. The remaining three quarters serve the operating system cache and per-connection working memory. Work memory is not a single allocation: the configured value is multiplied by the number of sort nodes in a query and again by the number of concurrent connections, so a value that is comfortable during a test at ten connections can exhaust memory entirely at two hundred. Effective cache size allocates nothing at all and merely advises the planner how much cache to assume. shared_buffers ~25%, fixed OS cache + per-connection working memory ~75%, shared and contended Total instance memory work_mem multiplies value × sort nodes per query × concurrent connections safe at ten connections, fatal at two hundred effective_cache_size allocates nothing — it is advice to the planner.

Verification

Confirm the values landed in the units you intended, which is the check that catches the block-versus-byte error: SHOW shared_buffers returns a human-readable size, so a configuration you believed was 8 GB and which reports 64MB tells you immediately that the arithmetic was wrong by a factor of 128.

Then confirm the behaviour changed. Re-run the query from the symptom with EXPLAIN (ANALYZE, BUFFERS) and look for Sort Method: quicksort Memory: where external merge Disk: used to be, and for an index scan where a sequential scan used to be. Check pg_stat_statements ordered by total execution time and confirm the query you tuned for has moved down the list. Finally, apply load: a work_mem that is correct for one session and wrong for two hundred looks perfect until the fleet scales, so validate under the concurrency the tile tier actually produces, which is bounded by the pool arithmetic in Vector Tile Service Provisioning.

The two plan lines that confirm the tuning worked Before tuning, the query plan reports a sort method of external merge with a disk figure, meaning the sort did not fit in working memory and was written out, and it reports a sequential scan on the table despite a valid spatial index. After tuning, the same query reports a quicksort with a memory figure and an index scan on the geometry column. Those two lines are what confirm the change reached the workload rather than only the parameter group, and they are far more informative than any latency measurement taken in isolation. Before Sort Method: external merge Disk: 84MB the sort did not fit — it was written out Seq Scan on parcels a valid GiST index, and the planner declined it After Sort Method: quicksort Memory: 61MB work_mem now holds it Index Scan using parcels_geom_idx random_page_cost now matches the storage These two lines say more than any latency measurement taken on its own.

Preventing recurrence

  • Derive the values, never copy them. A parameter group with literal numbers is correct for one instance size and silently wrong after a resize. Computing from instance_memory_gb keeps them right.
  • Assert the units in CI. A plan-contract assertion that memory parameters resolve to integers in a plausible block range catches the percentage-string and byte-count errors before apply, as described in Testing and Validation for Spatial IaC.
  • Set per-role overrides instead of raising the global. ALTER ROLE analytics SET work_mem gives the heavy analytical role its memory without multiplying it across the whole tile fleet.
  • Re-tune after a resize. Changing the instance class without regenerating the parameter group leaves the database configured for the old machine, which is the most common way a scale-up produces no improvement.

Frequently Asked Questions

Why is my `shared_buffers` change having no effect?

It is a static parameter. The change is pending until the instance reboots, and until then the running database uses the old value. Check the parameter group status for pending-reboot and schedule the reboot in a maintenance window.

Can I express these as a percentage of instance memory?

Some managed parameter contexts accept expressions referencing instance memory, but mixing those with block integers in one group is a reliable way to produce a value nobody can interpret later. Compute the integer in the module, where the arithmetic is visible and testable, and keep the group unambiguous.

How high can `work_mem` go for raster analysis?

As high as the concurrency budget allows, which for an analytical role on a dedicated connection can be several hundred megabytes. The rule is unchanged: it is multiplied by sort nodes and by concurrent connections, so grant the high value to the specific role that needs it rather than to every session.

Does lowering `random_page_cost` risk making bad plans?

It shifts the planner toward index access, which is usually correct on solid-state storage and is what you want for spatial predicates. If a specific query regresses, the honest fix is to look at its statistics and its index rather than to restore a cost model that describes hardware you are not running.