random_page_cost on Postgres & AWS Aurora

Several people have already blogged about the significance of random_page_cost in Postgres and how it can be a game-changer for your setup and deployments that fetch data from Postgres. Especially if you run Postgres on some cloud, such as AWS, and it happens to run on relatively modern infrastructure - please read the sources below! By the way, a full list of various parameters available can be found here. And, of course, don't pick random numbers; instead, do your investigation first and test.

I'll just list a couple of notable sources:

• pganalyze
• shayon-dev
• amplitude.engineering

The reason I'm writing this blog post is not to state the obvious (at least in my case), because I had been trying to keep this setting close to the infra capabilities that production databases run on top of. In other words, I was trying to avoid the default value of 4. I want to use this blog post as a reminder to myself, that when you operate an AWS Aurora Cluster - of Postgres with multiple Read-Only replicas, if you try to set the parameter on the main write instance, for example:

1SHOW random_page_cost
2SET random_page_cost=1.2

This will not be propagated to the rest of the Read onlyh replicas. Instead, you need to either directly update the cluster_parameter_group on AWS if you do have the access rights, or add the setting in terraform (if this is the way you have provisioned initially your cluster).

Example:

 1cluster_config = {
 2 name     = "some_name"
 3 instance_type = "db.t4g.medium"
 4 engine = {
 5  engine_name    = "aurora-postgresql"
 6  engine_sql_version = "16"
 7  cluster_parameters = {
 8   random_page_cost = "1.2"
 9  }
10 }
11}