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:
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)
