Let’s set up the simplest possible installation scenario - when you have two shards and one router.

​
Setting up sharding

First, you need to configure the rules by which the router will decide which of the shards to send each request to.

For this purpose, SPQR has an administrative console. This is an app that works by PostgreSQL protocol and you can connect to it by usual psql. You can find the console port in your config file.

➜  psql "host=localhost sslmode=disable user=demo dbname=demo port=7432"
SPQR router admin console
Here you can set up your own routing rules
------------------------------------------------
You can find the documentation here
https://github.com/pg-sharding/spqr/tree/master/docs

psql (14.5 (Homebrew), server console)
Type "help" to get help.

demo=> SHOW shards;
  listing data shards  
-----------------------
 datashard with ID shard1
 datashard with ID shard2
(2 rows)

To make all things work, the router needs to know the following:

  • What tables do you query
  • Which columns in each table should the router search for
  • Types of these columns
  • Mapping from [range of values] to [shard number]

Let’s create a distribution first:

➜ psql "host=localhost sslmode=disable user=demo dbname=demo port=7432"
demo=> CREATE DISTRIBUTION ds1 COLUMN TYPES integer;
         add distribution         
----------------------------------
 created distribution with id ds1
(1 row)

The next step is to specify a list of tables and columns.

demo=> ALTER DISTRIBUTION ds1 ATTACH RELATION orders DISTRIBUTION KEY id;
                 attach table               
--------------------------------------------
 attached relation orders to distribution ds1
(1 row)

demo=> ALTER DISTRIBUTION ds1 ATTACH RELATION item DISTRIBUTION KEY order_id;
                 attach table                 
---------------------------------------------
 attached relation item to distribution ds1
(1 row)

And at the end specify a list of ranges: which values to route to which shard. Note: The right bound is infinity if there are no key ranges.

CREATE KEY RANGE krid1 FROM 1 ROUTE TO shard1 FOR DISTRIBUTION ds1;
         add key range          
--------------------------------
 created key range with bound 1
(1 row)

CREATE KEY RANGE krid2 FROM 1000 ROUTE TO shard2 FOR DISTRIBUTION ds1;
            add key range          
-----------------------------------
 created key range with bound 1000
(1 row)

Here we go! You can play with some SELECTs or INSERTs.

​
Connect to SPQR router

Now we can connect to proxy a.k.a. router and play with it. Please use psql again, but this time connect to a different port.

➜ psql "host=localhost sslmode=disable user=demo dbname=demo port=6432"
psql (13.3, server 9.6.22)
Type "help" for help.

demo=> CREATE TABLE orders (
    id SERIAL NOT NULL PRIMARY KEY,
    customer_id INT,
    order_date DATE
);
NOTICE: send query to shard(s) : shard01,shard02
CREATE TABLE

demo=> CREATE TABLE items (
    id SERIAL NOT NULL PRIMARY KEY,
    order_id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR,
);
NOTICE: send query to shard(s) : shard01,shard02
CREATE TABLE

Then, populate it with an order:

demo=> BEGIN;
BEGIN
demo=> INSERT INTO orders (id, customer_id, order_data) VALUES (777, 123456, '2024-01-08');
NOTICE: send query to shard(s) : shard01
INSERT 0 1
demo=> INSERT INTO items (id, order_id, name) VALUES (1, 777, 'elephant');
INSERT 0 1
demo=> COMMIT;
COMMIT

NOTICE messages are disabled by default, specify show_notice_messages setting in the router config to enable them

The order can be found on the first shard:

demo=> SELECT * FROM orders WHERE id = 777;
NOTICE: send query to shard(s) : shard01
  id  | customer_id | order_data 
------+-------------+--------------
  777 |   123456    | '2024-01-08'
(1 row)

SPQR can handle such queries as SELECT * FROM table but we don’t recommend using it. This feature is implemented in a non-transactional way.

demo=> SELECT * FROM orders;
NOTICE: send query to shard(s) : shard01,shard02
  id  | customer_id | order_data 
------+-------------+--------------
  777 |   123456    | '2024-01-08'
(1 row)

Was this page helpful?

Welcome to SPQRSharding