Citus Plugin For Pgsql - Quickstart

Config envs

// entrypoint.sh
curl https://install.citusdata.com/community/deb.sh | bash
apt update -y; apt install postgresql-11-citus-9.5  -y
docker-entrypoint.sh postgres

// docker-compose.sh
version: '3.5'
services:
  pgsql:
   image: registry.gitlab.com/stratifyd/backend-developers/postgres-sandbox:41deafcacbc33bd5efb30dac6ddb35ef50caa9c0
   #ports:
   #  - "5434:5432"
   environment:
     - POSTGRES_PASSWORD=$POSTGRES_PASSWORD
     - POSTGRES_USER=$POSTGRES_USER
     - POSTGRES_DB=$POSTGRES_DB
   volumes:
     - ".:/app"
     - "./data:/var/lib/postgresql/data"
   entrypoint: bash /app/entrypoint.sh
   stdin_open: true
   tty: true

echo shared_preload_libraries = 'citus' >> data/postgresql.conf

// 192.168.32.2 is pgsql container lan ip address. ip/32 is ip self.
echo host all all 192.168.32.2/32 trust >> data/pg_hba.conf

export POSTGRES_USER=zhipeng
export POSTGRES_PASSWORD=zhipeng
export POSTGRES_DB=app

docker-compose up -d

Enable citus plugin

// Enter pgsql environment
docker-compose exec pgsql psql postgres://$POSTGRES_USER:$POSTGRES_PASSWORD@localhost:5432/$POSTGRES_DB

\c db_5d4bb1e6f3b7078e9ad6a3db;

// show citus version
select citus_version();
// Citus 9.5.10 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

// enable citus plugin
CREATE EXTENSION citus;

// add worker
SELECT * from master_add_node('192.168.32.2', 5432);
// show workers
SELECT * FROM master_get_active_worker_nodes();
// node_name	node_port
// 192.168.32.2	5432

// update citus plugin if need
// ALTER EXTENSION citus UPDATE;

Test

explain analyse select f_hb_324d_uniqueid$numerical, count(*) from stream_611e44abc0b16a8064d8324d  group by f_hb_324d_uniqueid$numerical limit 10;

Limit  (cost=0.28..7.78 rows=10 width=16) (actual time=0.025..0.037 rows=10 loops=1)
  ->  GroupAggregate  (cost=0.28..750.20 rows=999 width=16) (actual time=0.025..0.036 rows=10 loops=1)
        Group Key: "f_hb_324d_uniqueid$numerical"
        ->  Index Only Scan using "f_hb_324d_uniqueid$numerical_xxx" on stream_611e44abc0b16a8064d8324d  (cost=0.28..735.21 rows=999 width=8) (actual time=0.019..0.029 rows=11 loops=1)
              Heap Fetches: 11
Planning Time: 0.069 ms
Execution Time: 0.059 ms

// If a new table is created
SELECT master_create_worker_shards('stream_611e44abc0b16a8064d8324d', 4, 2);
// If the table has data, you can set the global shard of the datebase
set citus.shard_count=8;

// convert to distributed table
SELECT create_distributed_table('stream_611e44abc0b16a8064d8324d', 'f_hb_324d_uniqueid$numerical');

// undistributed table
SELECT undistribute_table('stream_611e44abc0b16a8064d8324d');

explain analyse select f_hb_324d_uniqueid$numerical, count(*) from stream_611e44abc0b16a8064d8324d  group by f_hb_324d_uniqueid$numerical limit 10;

Limit  (cost=0.00..0.00 rows=10 width=16) (actual time=16.062..16.064 rows=10 loops=1)
  ->  Custom Scan (Citus Adaptive)  (cost=0.00..0.00 rows=100000 width=16) (actual time=16.061..16.062 rows=10 loops=1)
        Task Count: 32
        Tuple data received from nodes: 6680 bytes
        Tasks Shown: One of 32
        ->  Task
              Tuple data received from node: 210 bytes
              Node: host=192.168.32.2 port=5432 dbname=db_5d4bb1e6f3b7078e9ad6a3db
              ->  Limit  (cost=0.14..8.44 rows=10 width=16) (actual time=0.023..0.032 rows=10 loops=1)
                    ->  GroupAggregate  (cost=0.14..49.94 rows=60 width=16) (actual time=0.023..0.031 rows=10 loops=1)
                          Group Key: "f_hb_324d_uniqueid$numerical"
                          ->  Index Only Scan using "f_hb_324d_uniqueid$numerical_xxx_102553" on stream_611e44abc0b16a8064d8324d_102553 stream_611e44abc0b16a8064d8324d  (cost=0.14..49.04 rows=60 width=8) (actual time=0.019..0.026 rows=11 loops=1)
                                Heap Fetches: 11
                  Planning Time: 0.434 ms
                  Execution Time: 0.099 ms
Planning Time: 1.621 ms
Execution Time: 16.093 ms

// Viewing Sharding Rules
select * from pg_dist_partition;

//Verify the worker
SELECT * FROM master_get_active_worker_nodes();


-[ RECORD 1 ]+-----------------------------------------------------------------------------------------------------------------------
logicalrelid | stream_616d2986bd17682e66aedbb3
partmethod   | h
partkey      | {VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location -1}
colocationid | 6
repmodel     | c

partmethod: hash

partkey.varattno : Indicates that the fragment is the first field

Rebalance sharding without stopping

Only enterprise edition is supported

SELECT rebalance_table_shards();