8 Strategies to Optimize Amazon Aurora PostgreSQL Costs

‍

In 2024, the adoption of Amazon Aurora for PostgreSQL surged, with over 4175 companies worldwide leveraging its capabilities, accounting for a growing market share of 1.27%.^[1] Aurora PostgreSQL simplifies the setup, operation, and scalability of PostgreSQL server deployments in the cloud. Despite its growing popularity, optimizing costs remains a key priority for organizations utilizing Aurora PostgreSQL.

In this post, we'll explore strategies for optimizing Aurora PostgreSQL costs, its pricing structure, and customer breakdown. Understanding these factors empowers businesses to make informed decisions, maximizing resources and achieving cost efficiencies in database operations.

Aurora PostgreSQL Customers by Products and Services

The breakdown of Aurora PostgreSQL customers by products and services reveals the primary areas where this database solution is utilised. It serves as the database solution for a diverse customer base. Companies, with employee ranges exceeding 10,000, rely on Amazon Aurora for robust and scalable database management.^[1]

Amazon Aurora PostgreSQL Pricing

This pricing breakdown for Amazon Aurora PostgreSQL encompasses various instance configurations and usage scenarios, offering a comprehensive overview of costs associated with storage, CPU usage, data transfer, and support.^[2]

Pricing Example

Scenario: Data API pricing with mixed INSERTS and read requests

Data API is called to query Aurora PostgreSQL-Compatible with the ExecuteStatement API call. There are 4.1 million API requests a month in total. 2.1 million of the 4.1M requests are inserted into Aurora. Each has data in an API payload size of 64 KB/request. 2 million of the 4.1M total requests read Aurora data. Of these, 1 million had a data returned payload of less than 32KB and 1 million had a payload size between 32.01KB up to 64KB.^[2]

Overall, the total cost for Data API usage amounts to $1.47+$1.05-$0.35 which equals to $2.17.

Strategies to reduce Amazon Aurora PostgreSQL Costs

1. Diagnosing table and index bloat

• Managing table and index bloat in Aurora PostgreSQL is important for saving storage costs and speeding up your database. Bloat happens because PostgreSQL's Multiversion Concurrency Control (MVCC) system keeps old versions of data, which can lead to unnecessary storage use.To check for bloat, you can use the pgstattuple extension. This tool helps you see how much old, unused data (dead tuples) is in your tables and how much space you can get back. By doing this regularly, you could save 5%-15% on your costs.
• Keep an eye on metrics like dead tuple counts and reclaimable space. These metrics show you how much bloat is affecting your storage and performance. With this information, you can make smart choices to optimize your database and reduce costs effectively.
• This code demonstrates how to diagnose table bloat in Aurora PostgreSQL using the 'pgstattuple' extension, enabling users to gather statistics on dead tuples and assess the impact on storage and performance.^[3]

CREATE EXTENSION pgstattuple;

SELECT * FROM pgstattuple('table_name');

2. Implementing Serverless

• Amazon Aurora Serverless decreases PostgreSQL expenses by adjusting capacity automatically as per demand, eliminating the need for manual resizing or excessive provisioning. Its pay-per-use approach bills users solely for the utilized resources, resulting in savings, particularly during periods of low activity. Moreover, functionalities like pause and resume effectively lower costs by halting database operations when there's no activity. With optimized configurations and monitoring tools, Aurora Serverless ensures resources are used efficiently, enabling scalable operations without breaking the bank.^[6]

Amazon Aurora Serverless empowers us to reduce customer costs for dynamic workloads and liberate internal resources by eliminating manual or monitored database resizing." - Joshua Strebel, Founder & CEO of Pagely^[5]

3. Simulating hyperscale data growth

• Aurora PostgreSQL costs are saved through efficient resource utilization and performance optimization achieved by simulating hyperscale data growth and utilizing a load simulator. By gaining insights into I/O usage and partitioning the database after reaching a significant data size of 33 TB, resource management is improved, leading to better performance.
• The efficient utilization of resources, such as the CPU of a db.r6g.8xlarge instance, ensures that transactions are processed effectively, maximizing the value derived from the infrastructure. This optimization enables efficient scaling and cost reduction, as fewer resources are required to handle large data volumes, resulting in overall savings in Aurora PostgreSQL costs.
• The following figure shows more details of ordering system load simulation.^[4]

4. Configuring memory management parameters

• Adjusting parameters like rds.memory_allocation_guard or rds.enable_memory_management in the DB cluster parameter group allows users to customize how the database manages memory. This customization ensures that the database allocates memory resources efficiently, matching the workload's needs.
• By doing this, users can avoid over-provisioning, which means they won't be paying for more resources than they actually need, thus reducing unnecessary costs. Fine-tuning memory management parameters also helps prevent critical situations where the database runs out of memory, which could lead to database restarts and performance issues. By proactively managing memory, users can maintain stable performance, reduce downtime, and prevent operational disruptions.
• All of these benefits contribute to overall cost savings because they help optimize resource usage and minimize the risk of costly performance problems. So, by adjusting these parameters, users can ensure that their database operates efficiently, maintains stable performance, and avoids unnecessary costs.

5. Fine-Tuning Memory Parameters

• Optimizing memory parameters in Amazon Aurora PostgreSQL can save costs by enhancing database performance and resource allocation. When we fine-tune parameters like work_mem and logical_decoding_work_mem, we ensure that the database efficiently uses memory according to its workload. This prevents slowdowns and reduces disk writes, leading to improved efficiency.
• By scaling database operations efficiently through this strategic approach, businesses can avoid over utilizing resources and, consequently, curb unnecessary costs.
• This example demonstrates how to tune memory parameters for an Aurora PostgreSQL database using psql commands.

psql -h your-endpoint -U your-username -d your-database-name -c "SET work_mem TO '64MB';"
psql -h your-endpoint -U your-username -d your-database-name -c "SET logical_decoding_work_mem TO '512MB';"
psql -h your-endpoint -U your-username -d your-database-name -c "SET log_temp_files TO 0;"

6. Implementing parallel query

• Enabling the parallel query feature in your Aurora PostgreSQL cluster can reduce costs by enhancing query performance and resource utilization. When parallel query is enabled, the database can distribute the workload across multiple CPU cores, leading to faster query processing times.
• By partitioning large tables and effectively utilizing indexes, you can further improve query performance, reducing the amount of data scanned during query execution and speeding up data retrieval.
• Regularly monitoring performance allows you to identify optimization opportunities and fine-tune configuration settings as needed, ensuring efficient operation of your Aurora PostgreSQL cluster and minimizing operational costs associated with database maintenance and query processing.

Netflix's Cloud Data Architect, Jyoti Shandil, attests to the success of Aurora's parallel query feature, citing a reduction in query time from 32 to 3 minutes and a downgrade in instance type, resulting in significant cost savings.^[5]

7. Fast failover

• Fast failover with Amazon Aurora PostgreSQL helps reduce costs by minimizing downtime and ensuring continuous availability of your database. By implementing strategies such as setting TCP keepalive parameters aggressively and configuring your application for fast failover, you can significantly reduce the time it takes to recover from failures.
• This rapid recovery translates to lower downtime, which in turn minimizes the impact on your operations and reduces potential revenue loss. Ultimately, by leveraging fast failover capabilities, you can optimize resource utilization, mitigate risks, and lower operational costs associated with managing Aurora PostgreSQL databases.
• Here's an example code demonstrating how to configure TCP keepalive parameters and set up a JDBC connection string for fast failover in Java:

import java.sql.*;

public class AuroraConnectionManager {
    public static void main(String[] args) {
        try {
            Class.forName("org.postgresql.Driver");
            String jdbcUrl = "jdbc:postgresql://<primary_endpoint>:5432,<read_only_endpoint>:5432/postgres?user=<username>&password=<password>&tcpKeepAlive=true&targetServerType=primary";
            Connection connection = DriverManager.getConnection(jdbcUrl);
            // Use connection...
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

‍

8. Connection churn with pooling

• Reducing costs in Aurora PostgreSQL relies on efficiently managing connection churn. Implementing connection pooling, such as with RDS Proxy, caches and reuses connections, cutting down on resource consumption caused by frequent connections.
• Monitoring tools like PostgreSQL's logging and Performance Insights help assess the impact of connection churn, allowing optimization of workloads to minimize unnecessary connections and enhance response times. This ultimately leads to cost savings by maximizing resource efficiency and improving overall database performance.
• Here's a example demonstrating how to use pgbench to benchmark an Aurora PostgreSQL DB cluster with connection pooling:

pgbench -h your-proxy-endpoint -U your-username -p 5432 -T 60 -c 20 your-database-name

This pgbench command will simulate 20 client connections concurrently performing transactions on the specified database through the RDS Proxy for a duration of 60 seconds. Replace placeholders like your-proxy-endpoint, your-username, and your-database-name with your actual values.

Conclusion

In summary, these strategies offer a comprehensive approach to cost reduction and optimization for Amazon Aurora PostgreSQL.^[3]

By addressing storage bloat, resource utilization, query performance, and operational efficiency, businesses can minimize costs while maximizing database value. Whether through serverless architecture, memory parameter fine-tuning, or fast failover implementation, these ideas ensure efficient operations and significant savings.

‍

References

1. Amazon Aurora - Market Share, Competitor Insights in Relational Databases‍

2. Amazon RDS for PostgreSQL Pricing – Amazon Web Services‍

3. Best practices with Amazon Aurora PostgreSQL‍

4. Reduce read I/O cost of your Amazon Aurora PostgreSQL database with range partitioning‍

5. Amazon Aurora Customer Testimonials | MySQL PostgreSQL Relational Database‍

6. Using Aurora Serverless v2