Introduction

Since Aurora and RDS are managed services, parameter tuning is generally not required, but depending on requirements or performance issues, tuning may be necessary. This is a summary of those parameters.

List of Parameters to Consider Changing

Added as I notice them. Current parameter values (setting column) are extracted from Aurora and RDS using r5.large instance type.

Note that even though these are “parameters to consider changing,” the modifiable parameters differ between Aurora and RDS, and some cannot be changed. For example, checkpoint_timeout is typically adjusted for checkpoint tuning, but it appears that RDS allows changes while Aurora does not.

List of Non-Modifiable Parameters for RDS and Aurora PostgreSQL | my opinion is my own https://zatoima.github.io/aws-aurora-rds-postgresql-parameter-modifiable.html

SQL for Extraction

SELECT
    name,
    setting,
    boot_val,
    unit,
    context
FROM
    pg_settings
WHERE
    name IN ('max_connections','superuser_reserved_connections','lc_messages','archive_timeout','log_filename','log_rotation_age','log_rotation_size','log_min_duration_statement','log_hostname','log_timezone','shared_buffers','effective_io_concurrency','max_worker_processes','max_parallel_maintenance_workers','max_parallel_workers_per_gather','max_parallel_workers','wal_buffers','checkpoint_timeout','checkpoint_completion_target','random_page_cost','autovacuum_max_workers','deadlock_timeout')
ORDER BY 1;

Individual Parameter Notes

deadlock_timeout

Since deadlock detection is costly, it may be worth raising this above the default of 1000ms (1 second).

checkpoint_completion_target

checkpoint_timeout

Default values differ between Aurora and RDS. Consider changing if performance issues arise.

Note: Aurora has no concept of checkpoints itself, and memory coming into log_buffer flows directly to the storage side, so it’s unclear whether changing this has any effect.

Notes on PostgreSQL’s checkpoint_completion_target | my opinion is my own https://zatoima.github.io/postgresql-about-checkpoint_completion_target.html

log_filename

log_hostname

log_min_duration_statement

log_rotation_age

log_rotation_size

log_timezone

Change based on logging requirements.

max_connections

Compared to the OSS PostgreSQL default of 100, the parameter is already adjusted, but the actual number of connections depends on requirements. When the instance type is scaled up, max_connections increases as well.

autovacuum_max_workers

max_parallel_maintenance_workers

max_parallel_workers

max_parallel_workers_per_gather

max_worker_processes

Fine-tune when tuning Vacuum and Analyze.

shared_buffers

For Aurora, the default value in the DB parameter group is set to 75% of total memory. This is because Aurora PostgreSQL does not use double buffering and does not need the OS filesystem cache. Fine-tune if you want to allocate more to other memory parameters.

Understanding why there is a difference in shared_buffers DB parameter default values between Amazon RDS PostgreSQL and Aurora PostgreSQL https://aws.amazon.com/jp/premiumsupport/knowledge-center/rds-aurora-postgresql-shared-buffers/

wal_buffers

The amount of shared memory used for WAL data not yet written to disk. This buffer is written to disk at commit time. If writes are high and there are many CPUs, extending to a few MB can be effective, but the default value should generally be fine for both Aurora and RDS.

random_page_cost

Sets the planner’s estimate for the cost of a non-sequentially fetched disk page. Reducing this value makes index scans relatively more attractive. Since SSDs are generally faster than HDDs for random reads, setting this to 1.0 for SSD is common. It may be worth lowering below the default of 4.0 in some cases.

Parameters That Change with Instance Type Scale-Up

I investigated which parameters change among the above when scaling up. max_connections and shared_buffers change. On the other hand, despite increases in CPU and memory, parallelism settings and wal_buffers don’t change.

References

PostgresqlCO.NF: PostgreSQL configuration for humans https://postgresqlco.nf/doc/ja/param/

PostgreSQL 11 parameter dissection - Speaker Deck https://speakerdeck.com/ester41/postgresql-11-parameter?slide=2

PostgreSQL stable operation: failure prevention and detection https://www.ospn.jp/osc2014.enterprise/pdf/OSC2014_Enterprise_hp.pdf

PostgreSQL Performance Tuning - Qiita https://qiita.com/cuzic/items/f9b846e6171a54079d77

Best Practices for Migrating an Oracle Database to Amazon RDS PostgreSQL or Amazon Aurora PostgreSQL: Target Database Considerations for the PostgreSQL Environment | Amazon Web Services Blog https://aws.amazon.com/jp/blogs/news/best-practices-for-migrating-an-oracle-database-to-amazon-rds-postgresql-or-amazon-aurora-postgresql-target-database-considerations-for-the-postgresql-environment/