Astrodevil

Posted on Feb 27

How to Make Your Postgres Database 100x Faster and 50% Cheaper with Readyset Cloud🚀⚡💵

#opensource #database #cloud #webdev

If you're running a PostgreSQL database on AWS RDS, you should probably be familiar with one of these problems:

Your read-heavy queries are taking longer to complete.
Adding read replicas helped but drastically increased your server costs.
Cache invalidation is becoming too much to manage.
Your application needs faster response times, but you're not sure how to do it!

I also faced similar issues and was looking for easy ways to improve performance in our application when I came across an awesome tool called Readyset. It sits between your application and database, intelligently caching complex queries while automatically handling invalidation. This setup can significantly boost performance without requiring major changes to your existing infrastructure.

In this tutorial, I’ll share how we set up Readyset Cloud with our existing AWS RDS PostgreSQL database, built a high-performance movie database and started caching slow queries that were slowing us down.

Prerequisites

Before diving in, make sure you have:

✅A PostgreSQL database (version 13.0 or higher) hosted on AWS RDS

✅Access to AWS console with permissions to modify RDS parameters

✅Database superuser credentials for setting up replication

Setting Up Your Database

Let's start by setting up a movie database that mirrors real-world usage patterns. First, connect to your PostgreSQL database using psql to verify connectivity:

psql -h your-database.region.rds.amazonaws.com -p 5432 -d your_database -U your_username

If everything works, you should get the output below on your terminal:

Then download the IMDB sample dataset for PostgreSQL:

curl -L -O "<https://readyset.io/quickstart/imdb-postgres.sql>"

Connect to your database and import the dataset:

psql -h <database-url> -U <username> -d <database-name> -f imdb-postgres.sql

This creates our base tables with movie data that we'll optimize with Readyset.

Now, let's verify the data import by connecting to your database:

SELECT count(*) FROM title_ratings
JOIN title_basics ON title_ratings.tconst = title_basics.tconst
WHERE title_basics.startyear = 2000 AND title_ratings.averagerating > 5;

If the data was successfully imported into your AWS RDS database, you will see a similar output below in your terminal.

Configuring AWS RDS

Now we need to prepare your RDS instance for Readyset Cloud to enable logical replication on our RDS instance, we'll need to modify your database parameter group. This is important for Readyset to maintain real-time synchronization with your primary database. To do that, follow the steps below:

Navigate to the AWS RDS console and create a new parameter group or modify your existing one
Set the following parameters:
Click the Create button to save the parameter group to your database instance.
Click on new parameter group and modify: Set rds.logical_replication = 1

Now, apply these to database:

Select your RDS instance
Click on Modify button.
Parameter group: choose readyset-params
Select Apply immediately and Save.

These changes require a database restart, so plan for a maintenance window.

Connecting to Readyset Cloud

Now that our database is properly configured, let's set up Readyset Cloud. The process is straightforward with the following steps:

Create an account on Readyset Cloud
Create a new project for our database

Run the compatibility checker script to ensure our database is properly configured

bash -c "$(curl -sSL <https://raw.githubusercontent.com/readysettech/readyset/main/quickstart/check_db_postgres.sh>)"

The above script will prompt us for our database connection string. If our instance is not publicly accessible we will require to have aws cli configured to fetch the VPC information.

Enter your AWS RDS database connection string in the format: postgresql://user:password@host:port/db_name to continue.

Then prompt will require us to select where our database is running on AWS. Type r for RDS.

Finally, we will prompted to enter our RDS instance, enter it to continue.

The tool will validate our database configuration and provide the necessary information for creating our Readyset cache instance.

Creating a Readyset Cache Instance

Now let’s use the information from the compatibility checker to create a new cache instance instance in your Readyset Project:

Click the Create Cache Instance button, enter the cluster name, your region(should match the one on your RDS), cluster size

Fill in the credentials fields using the provided configuration from running the compatibility checker script.

Now wait for some time for Readyset to finish setting up the instance cluster.

Identifying and Caching Slow Queries

Let's identify and optimize some common movie database queries. First, click on the Connect button in your cluster page to view your connection string.

Then connect to your Readyset instance using psql. Ensure you fill in those values in your connection string:

psql postgresql://postgres:password@pghostname.us-east-1.rds.amazonaws.com:5432/postgres

Now, check if the tables are ready for caching:

SHOW READYSET STATUS;

Now let's look at the queries that could benefit from caching:

SHOW PROXIED QUERIES;

Go back to your readyset cloud cache cluster, you’ll find the proxied query in the Ready to cache tab.

Click the Cache query button to cache it.

Measuring Real Performance Gains

Let's measure the actual improvement in query performance. Run the same query before and after caching to see the difference:

\\timing on
SELECT 
    COUNT(*)
FROM 
    title_ratings
JOIN 
    title_basics 
ON 
    title_ratings.tconst = title_basics.tconst
WHERE 
    title_basics.startyear = 1910 
    AND title_ratings.averagerating > 8;

Before caching (against your primary database):

Now run the same query through Readyset after caching the query:

By caching our queries with Readyset Cloud, we can clearly see a performance gain of approximately 98% over the original query time, allowing users to access data faster than before.

Production Monitoring

For production use, you'll want to monitor several key metrics. Connect to your Readyset instance and check the cache status:

SHOW READYSET STATUS;

Monitor cached queries and their performance:

SHOW CACHES;

Conclusion

Readyset Cloud provides you with a robust PostgreSQL performance boost that simultaneously reduces expenses by optimizing cache management.

By following this tutorial, you have learned the steps to:

Configure your AWS RDS instance for Readyset
Set up and connect to Readyset Cloud
Identify and cache slow queries in your movies database.
Monitor performance in production

Review the cache queries regularly while you adjust the caching method to suit your changing application patterns and system requirements. With proper setup and monitoring, Readyset will deliver significant performance improvements maintain data consistency and reduce infrastructural costs.

Quick links: