docker compose run --rm load, skip to the queries. To load your own empty SPQR cluster, choose a dataset below. To start a local cluster, follow Get Started.
Download a dataset
The examples use Small, which contains 1,000 chats. Each download includes the schema and data in a compressed SQL file.Small
100,000 messages · 1.5 MiB
Medium
10 million messages · 160 MiB
Big
100 million messages · 1.6 GiB
Huge
1 billion messages · 15.9 GiB
Load the demo
Use a dedicated, empty SPQR cluster with at least two shards. Your user needs permission to create and populate tables through the router and configure routing through its admin console. Start with empty routing metadata as well as empty databases. For example, the sample rules inexamples/init.sql already register users, which the demo creates as a reference table.
Set the router host, port, database, and user for your cluster. This example uses the local demo’s defaults.
7432 with the same host, database, and user. If you use a password file, include both ports. For a different admin port, pass -v admin_port=PORT to psql below.
Run this in your download directory. For another size, replace messenger-small with the file name of your dataset.
Which shard holds a chat?
Open a router session withdocker compose run --rm psql from the local demo directory, or psql -X from the shell you used to load your own cluster. Turn on routing notices and compare two chats at opposite ends of Small.
Read the routing rules
Switch from the router to its admin console in the samepsql session. Substitute your admin port for 7432 if needed.
chat_id as the key for messages and ds_chats as its distribution. The second gives the inclusive lower bound of each range and the shard that owns it. In Small on two shards the bounds are 1 and 501. Use that map to predict which shard will answer each query, then check the notices.
Where did these rules come from?
Where did these rules come from?
The loader ran these statements on the admin console before it created the tables and copied the rows through the router.
Who wrote the latest message in chat 1?
chats, chat_members, messages, and reactions share the chat_id key in the ds_chats distribution. Every shard also has a copy of users, so the shard that holds chat 1 can look up the author of its latest message.
Space Daily | Emre Adams | Honestly, happy birthday!. The notice names the same shard as the first count.
Which chats has a user joined?
A user’s memberships can span several chat IDs. To find them all, send the query to every shard with the scatter hint. The notice now lists all shards.Reset the demo
For the Docker setup, use the local cleanup commands. On your own cluster, exitpsql with \q and run the following command in the shell you used for loading. It deletes the seven demo tables and their routing rules. Substitute your admin port for 7432 if needed.