PostgreSQL is one of the most potent relational databases, but performance can become a bottleneck as queries become complex. One of the most effective ways to reduce query latency without modifying application logic is caching, and Readyset provides a seamless solution.

Readyset sits between your application and PostgreSQL, automatically caching queries and speeding up their response times considerably.

In this guide, we’ll walk through:

Installing Readyset on Ubuntu 24.04 using a .deb package;

Configuring Readyset to connect to your upstream PostgreSQL database;

Running queries and caching results using the Northwind sample database;

Managing cached queries to optimize performance.

The Northwind PostgreSQL Sample Database can be downloaded and imported as below:

#: download the database dump and restore it
wget https://raw.githubusercontent.com/pthom/northwind_psql/master/northwind.sql
psql -U <dbuser> -W -h <db IP or DNS> -d template1 -c "create database northwind"
psql -U <dbuser> -W -h <db IP or DNS> -d northwind -f northwind.sql

You need to pay attention to security. We recommend creating a GROUP and a USER with the relevant configurations and permissions before you connect Readyset to your databases, which we call upstream databases. On the upstream database, you need to configure the wal_level as logical so Readyset can snapshot the database you have queries for caching and set up a logical replication to keep caching up-to-date.

Some other considerations for configuring your PostgreSQL with Readyset are at https://readyset.io/docs/reference/configure-your-database/postgres/generic-db-directions

Step 1: Prerequisites

Before setting up Readyset, ensure that a PostgreSQL database is already running. Readyset requires access to the database to snapshot the schema and cache queries.

Key Recommendations

Readyset should be installed on a different host than the PostgreSQL database to avoid resource contention.

The database server must allow Readyset to connect remotely.

For this tutorial, we assume:

• PostgreSQL is running on 10.0.0.11:5432;
• The Readyset is running on 10.0.0.12:5433;
• A database named northwind is already restored;
• A PostgreSQL user (readyset) with appropriate permissions exists.

Step 2: Installing Readyset on Ubuntu 24.04

2.1 Download the Readyset .deb Package

$ wget https://github.com/readysettech/readyset/releases/download/stable-250123/readyset_1.11.0-1_amd64.deb

2.2 Install Readyset Using dpk

$ dpkg -i readyset_1.11.0-1_amd64.deb

After installation, you should see:

Selecting previously unselected package readyset.
(Reading database ... 168043 files and directories currently installed.)
Preparing to unpack readyset_1.11.0-1_amd64.deb ...
Unpacking readyset (1.11.0-1) ...
Setting up readyset (1.11.0-1) ...
Created symlink /etc/systemd/system/multi-user.target.wants/readyset.service → /usr/lib/systemd/system/readyset.service.

You also need to install the psql on the Readyset host:

$ apt install -y postgresql-client-common postgresql-client

To make sure Readyset will be able to connect to the upstream database host, give it a try:

$ pg_isready -d northwind -h 10.0.0.11 -p 5432 -U readyset
10.0.0.11:5432 - accepting connections

Step 3: Configuring Readyset

Before starting Readyset, configure it in /etc/readyset/readyset.conf.

3.1 Open the Configuration File

vim /etc/readyset/readyset.conf

3.2 Set the Database Connection

UPSTREAM_DB_URL="postgres://readyset:<pass>@10.0.0.11:5432/northwind"
LISTEN_ADDRESS="0.0.0.0:5433"

• UPSTREAM_DB_URL: PostgreSQL connection string for Readyset to snapshot the schema and proxy queries when needed.
• LISTEN_ADDRESS: 5433 is the default Readyset port, ensuring compatibility when running on a separate host.

3.3 Start the Readyset Service

$ systemctl start readyset.service

To check if it’s running:

$ systemctl status readyset.service

If you want to check logs:

$ tail -n100 /var/lib/readyset/
…
2025-03-04T19:07:22.948022Z  INFO replicators::noria_adapter: Snapshot finished
2025-03-04T19:07:22.988330Z  INFO replicators::postgres_connector::connector: confirmed_flush_lsn="0/1B8DF80" wal_status="reserved"
2025-03-04T19:07:23.004533Z  INFO replicators::noria_adapter: Streaming replication started

Step 4: Connecting to Readyset

Once Readyset is running, connect using the PostgreSQL client:

psql -U readyset -h 10.0.12 -W -p 5433 -d northwind

For more convenience when running queries, I will disable the “pager less” for returning result sets and enable the timing for the Readyset psql PostgreSQL client.

$ echo "\pset pager off" >> ~/.psqlrc
$ echo "\timing" >> ~/.psqlrc

Since Readyset acts as a proxy, queries sent here will be ready to be cached.

Step 5: Running Your First Cached Query

Run a real-world query using PostgreSQL’s Northwind database:

northwind=> SELECT order_id
    , order_date
    , shipped_date
    , company_name
    , contact_name
FROM orders t1 JOIN customers t2 ON t1.customer_id = t2.customer_id
              JOIN employees t3 ON t1.employee_id = t3.employee_id
WHERE t1.ship_country='Brazil';
order_id | order_date | shipped_date |      company_name      |   contact_name   
----------+------------+--------------+------------------------+-------------------
   10250 | 1996-07-08 | 1996-07-12   | Hanari Carnes          | Mario Pontes
   10253 | 1996-07-10 | 1996-07-16   | Hanari Carnes          | Mario Pontes
...
   11059 | 1998-04-29 |              | Ricardo Adocicados     | Janete Limeira
   11068 | 1998-05-04 |              | Queen Cozinha          | Lúcia Carvalho
(83 rows)


Time: 54.390 ms

Since this is the first run, PostgreSQL processes it as usual. The time is ~54 ms.

We can use the EXPLAIN LAST STATEMENT Readyset command to see where this query was executed:

northwind=> EXPLAIN LAST STATEMENT;
 Query_destination | Readyset_error 
-------------------+----------------
 upstream          | ok
(1 row)

As expected, the query was proxied to the upstream database.

Step 6: Checking Proxied Queries

To check if Readyset intercepted the query:

SHOW PROXIED QUERIES;

Expected output:

northwind=> SHOW PROXIED QUERIES;
      query id      |                              proxied query                              | readyset supported | count 
--------------------+-------------------------------------------------------------------------+--------------------+-------
 q_33cfda0e3ac49afc | SELECT                                                                 +| yes                |     0
                    |   "order_id",                                                          +|                    | 
                    |   "order_date",                                                        +|                    | 
                    |   "shipped_date",                                                      +|                    | 
                    |   "company_name",                                                      +|                    | 
                    |   "contact_name"                                                       +|                    | 
                    | FROM                                                                   +|                    | 
                    |   "orders" AS "t1"                                                     +|                    | 
                    |   JOIN "customers" AS "t2" ON ("t1"."customer_id" = "t2"."customer_id")+|                    | 
                    |   JOIN "employees" AS "t3" ON ("t1"."employee_id" = "t3"."employee_id")+|                    | 
                    | WHERE                                                                  +|                    | 
                    |   ("t1"."ship_country" = $1)                                            |                    | 
Time: 0.858 ms

The query is “readyset supported”, as seen on the above output.

Step 7: Creating a Cache for the Query

To explicitly cache this query:

northwind=> CREATE CACHE ALWAYS FROM q_33cfda0e3ac49afc;
Time: 923.076 ms

Step 8: Verifying the Cache

Check if the query is cached:

northwind=> SHOW CACHES;
      query id      |     cache name     |                                query text                                | fallback behavior | count 
--------------------+--------------------+--------------------------------------------------------------------------+-------------------+-------
 q_33cfda0e3ac49afc | q_33cfda0e3ac49afc | SELECT                                                                  +| no fallback       | 0
                    |                    |   "public"."orders"."order_id",                                         +|                   | 
                    |                    |   "public"."orders"."order_date",                                       +|                   | 
                    |                    |   "public"."orders"."shipped_date",                                     +|                   | 
                    |                    |   "public"."customers"."company_name",                                  +|                   | 
                    |                    |   "public"."customers"."contact_name"                                   +|                   | 
                    |                    | FROM                                                                    +|                   | 
                    |                    |   "public"."orders"                                                     +|                   | 
                    |                    |   JOIN "public"."customers" ON (                                        +|                   | 
                    |                    |     "public"."orders"."customer_id" = "public"."customers"."customer_id"+|                   | 
                    |                    |   )                                                                     +|                   | 
                    |                    |   JOIN "public"."employees" ON (                                        +|                   | 
                    |                    |     "public"."orders"."employee_id" = "public"."employees"."employee_id"+|                   | 
                    |                    |   )                                                                     +|                   | 
                    |                    | WHERE                                                                   +|                   | 
                    |                    |   ("public"."orders"."ship_country" = $1)                                |                   | 
(1 row)
Time: 2.332 ms

At this point, Readyset is caching this query!

Step 9: Running the Cached Query

Rerun the query:

northwind=> SELECT order_id
    , order_date
    , shipped_date
    , company_name
    , contact_name
FROM orders t1 JOIN customers t2 ON t1.customer_id = t2.customer_id
              JOIN employees t3 ON t1.employee_id = t3.employee_id
WHERE t1.ship_country='Brazil';
 order_id | order_date | shipped_date |      company_name      |   contact_name    
----------+------------+--------------+------------------------+-------------------
    10250 | 1996-07-08 | 1996-07-12   | Hanari Carnes          | Mario Pontes
    10253 | 1996-07-10 | 1996-07-16   | Hanari Carnes          | Mario Pontes
…
    11059 | 1998-04-29 |              | Ricardo Adocicados     | Janete Limeira
    11068 | 1998-05-04 |              | Queen Cozinha          | Lúcia Carvalho
(83 rows)

Time: 1.232 ms

The same query execution, now using the Readyset cache took ~1 ms.

If we again EXPLAIN LAST STATEMENT:

northwind=> EXPLAIN LAST STATEMENT;
 Query_destination | Readyset_error 
-------------------+----------------
 readyset          | ok
(1 row)

🚀 The query execution time dropped more than 50 times! Readyset serves the result from the created cache, avoiding a full database load.

Step 10: Removing a Cached Query

To remove the cache, run:

northwind=> DROP CACHE q_33cfda0e3ac49afc;
DELETE 1
Time: 16.189 ms

Verify that it's gone:

SHOW CACHES;

Conclusion

With Readyset, you can significantly improve PostgreSQL query performance without modifying application code.

Using Readyset improves efficiency and performance orders of magnitude;

Queries are cached reducing upstream PostgreSQL load;

With this strategy you don't need to add more replicas or hardware;

Create custom caches for the queries you want fully optimized;

Using SHOW CACHES helps track performance improvements;

The EXPLAIN LAST STATEMENT shows you where the last statement was executed;

You don’t need any code changes, you can start with readyset now.

Connect with us!

Play with our Cloud Demo: https://readyset.cloud/

Our Slack: https://go.readyset.io/community

Our Github: https://github.com/readysettech/readyset