getting recommendation on RDS POSTGRESQL: "Use a DB instance type with RDS Optimized Reads"

Recommendation: Use a DB instance type with RDS Optimized Reads for RDS PostgreSQL

The AWS recommendation to use a DB instance type with RDS Optimized Reads for your RDS PostgreSQL instance is in response to excessive temporary object creation. RDS Optimized Reads significantly enhance query performance by utilizing local NVMe SSD storage for temporary objects, which is especially beneficial for workloads involving large joins, sorts, or aggregations.

Alternatives to Upgrading:

If upgrading to a more expensive instance type isn't feasible, you can explore these alternatives to optimize your current setup:

Optimize Queries:

• Refactor queries to reduce the creation of temporary tables or large intermediate results.
• Use PostgreSQL’s EXPLAIN ANALYZE to identify inefficient execution plans and optimize slow queries.

https://www.postgresql.org/docs/current/sql-explain.html

Improve Indexing:

• Ensure that your tables are properly indexed to avoid full-table scans, which can lead to the creation of temporary objects.

Increase work_mem:

• PostgreSQL uses disk space for large sort and hash operations. By increasing the work_mem setting, you can reduce disk I/O by allowing more data to be processed in memory, reducing reliance on temporary objects. Learn more about tuning parameters in PostgreSQL memory configuration.

https://www.postgresql.org/docs/current/runtime-config-resource.html

AutoVacuum Tuning:

• Fine-tune your auto-vacuum settings to prevent table bloat and improve overall performance. This ensures the database operates more efficiently, reducing the need for temporary storage.

https://www.postgresql.org/docs/current/routine-vacuuming.html

Use Provisioned IOPS:

• If your workload is bottlenecked by I/O, consider enabling Provisioned IOPS (io1/io2) on your current instance to improve disk throughput without switching to an NVMe-based instance type.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html#USER_PIOPS

Leverage Read Replicas:

• For read-heavy workloads, consider offloading some of your queries to a read replica. This reduces the load on the primary instance, allowing for better resource allocation and performance on the master node.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.html

Upgrading to RDS Optimized Reads:

• If these alternatives don’t provide sufficient performance improvement, upgrading to an instance type that supports RDS Optimized Reads would be the next step. These instance types utilize local NVMe SSD storage for temporary tables, improving I/O performance significantly.

Recommended NVMe-based Instance Types:

1. R6gd (Memory-Optimized): Best for memory-intensive workloads, with NVMe SSD for improved temporary table performance. More details on instance types can be found in the Amazon RDS DB instance classes documentation.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.DBInstanceClass.html

2. M6gd (General Purpose): Offers a balance between compute and memory with faster reads using local SSD storage.
3. R5d (Memory-Optimized): Great for applications that need a lot of memory and fast disk performance.
4. M5d (General Purpose): Suitable for general workloads with NVMe SSD for better temporary storage performance.

Choosing the Right Instance Type:

• For memory-intensive workloads, consider R6gd or R5d instances.
• For a balanced approach, M6gd or M5d instances provide an effective combination of compute, memory, and SSD storage.

Version Requirements:

Ensure your RDS PostgreSQL version supports Optimized Reads:

• Available for PostgreSQL versions 13.10 and higher, 14.7 and higher, and 15.2 and higher.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.optimizedreads.html

Monitoring After Upgrade: After upgrading, monitor the performance using CloudWatch metrics such as FreeLocalStorage ReadIOPSLocalStorage , and WriteIOPSLocalStorage to ensure that your workload benefits from the upgrade.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MonitoringOverview.html