Readyset Demo

This demo sets up a Readyset instance and a sample database on your local machine using Docker.

💡

If you want to connect Readyset to your own database, please follow the directions in the install Readyset documentation.

💡

Before starting, make sure you have Docker Engine version >= 19.03.0 and Docker Compose V2 OR Docker Desktop >= 4.1.0 and the MySQL or Postgres client installed.

1. Download and run the Readyset Docker compose file

curl -L -o compose.yml                                  \
  "https://readyset.io/quickstart/compose.postgres.yml" \
  && docker compose pull                                \
  && docker compose up -d

2. Import sample data

curl -L -s "https://readyset.io/quickstart/imdb-postgres.sql" \
  | psql 'postgresql://postgres:readyset@127.0.0.1:5433/testdb'

⚠️

Data loading will be slow on Macs with Apple Silicon.

3. Connect and explore the dataset

Connect to Readyset.

psql 'postgresql://postgres:readyset@127.0.0.1:5433/testdb'

Enable query timing.

\timing

Run a sample query. Note that since we have not created a cache, this query is served directly by Postgres.

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;

You'll see similar results:

 count
-------
  2418
(1 row)
 
Time: 154.980 ms

4. Cache a query!

⚠️

Query performance will be slow on Macs with Apple Silicon.

Using the CREATE CACHE FROM SQL extension, cache the query in Readyset like so:

CREATE CACHE FROM 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;

Now that Readyset is going to cache the query, run the same query to populate the cache:

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;

Same results as earlier:

 count(*)
----------
     2418
(1 row)
 
Time: 154.980 ms

This most recent query ran against Postgres, but now the Readyset cache has been populated.
Run the query again and this time it's served by Readyset:

 count(*)
----------
     2418
(1 row)
 
Time: 2.073 ms

5. View Cached Queries

Use the SHOW CACHES command to see which queries are currently being cached by Readyset:

      query id      |     cache name     |                       query text                       | fallback behavior | count
--------------------+--------------------+--------------------------------------------------------+-------------------+-------
 q_bccd97aea07c545f | q_bccd97aea07c545f | SELECT                                                +| fallback allowed  | 4
                    |                    |   count(*)                                            +|                   |
                    |                    | FROM                                                  +|                   |
                    |                    |   "title_ratings"                                     +|                   |
                    |                    |   JOIN "title_basics" ON (                            +|                   |
                    |                    |     "title_ratings"."tconst" = "title_basics"."tconst"+|                   |
                    |                    |   )                                                   +|                   |
                    |                    | WHERE                                                 +|                   |
                    |                    |   (                                                   +|                   |
                    |                    |     ("title_basics"."startyear" = $1)                 +|                   |
                    |                    |     AND ("title_ratings"."averagerating" > 5)         +|                   |
                    |                    |   )                                                    |                   |
(1 row)

6. Update the underlying data

You'll notice that query you've run a few times returns the count of movies in '00 that had a rating greater than 5 (i.e. 2,418 movies). "Battlefield Earth" was a movie released in '00 that received poor ratings. For instance, run this query:

 SELECT title_basics.tconst, title_basics.primarytitle, title_ratings.averagerating, title_ratings.numvotes 
 FROM title_basics                                                
 INNER JOIN title_ratings on title_ratings.tconst = title_basics.tconst                                                                                                               
 WHERE title_basics.primarytitle = 'Battlefield Earth';

You'll see it scored an average rating of 2.5:

  tconst   |   primarytitle    | averagerating | numvotes
-----------+-------------------+---------------+----------
 tt0185183 | Battlefield Earth |           2.5 |    80451
(1 row)
 
Time: 63.621 ms

It was, indeed, an awful movie. Nevertheless, historical revisionism is fun when you have full control of the data. You can grab the id for "Battlefield Earth" (tt0185183) and update its average rating accordingly:

UPDATE title_ratings
SET averagerating = 5.1
WHERE tconst = 'tt0185183';

7. The cache is auto-updated!

Rerun the previously cached query that returns the count of movies:

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;

And bingo! The count has been increased by one (i.e 2,419 vs 2,418).

 count(*)
----------
     2419
(1 row)
 
Time: 2.617 ms

8. Try more queries!

Explore the dataset and test Readyset's performance with additional queries.

View proxied queries:

SHOW PROXIED QUERIES;

Remove a cache:

DROP CACHE <query id>;

Onboard