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;
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.
Play with our Cloud Demo: https://readyset.cloud/
Our Slack: https://go.readyset.io/community
Our Github: https://github.com/readysettech/readyset
Vinicius Grippa
Michael Virga