Skip to content
Snippets Groups Projects

Research: Sharding for Postgres

    • Open Issue created by Artyom Kartasov @akartasov

    Promising prototypes:

    Designs

      Child items ...

      2. Activity

      • Nikolay Samokhvalov assigned to @akartasov

        assigned to @akartasov

      • Artyom Kartasov added Flow::In Progress label

        added Flow::In Progress label

      • Artyom Kartasov assigned to @akartasov

        assigned to @akartasov

      • Artyom Kartasov
        Artyom Kartasov @akartasov ·
        Author Maintainer

        SPQR

        What is needed (at least) to complete ?

        • build interaction with world-type shards
        • find solution for sequences and indexes, for example:
          • rewrite queries (an analog of vschema in Vitess)
          • generate custom sequences for each shard (undesirable)
        • SQL
          • extended support for SQL expressions (not all implemented)
          • parser problems (doesn't parse many queries) - choose another one or fork and expand the existing one
        • routing rules
          • support sharding for multiple columns
          • implement missing routing rules
          • multi-shard queries
        • DB schema management
          • verify and apply DB schema changes
          • resharding
        • configure auth and credentials
        • docs and description of base concepts
        • tests and benchmarks

        Design

        spqr

        Configure routing rules

        $ docker-compose run --entrypoint /bin/bash client
root@spqr_client:/go# connect_adm.sh 

psql (13.6 (Debian 13.6-1.pgdg110+1), server console)
Type "help" for help.

db1=?> create sharding column w_id;
                     fortune                      
--------------------------------------------------
 created sharding column [w_id], err %!w(MISSING)
(1 row)

db1=?> add key range 1 10 sh1 krid1;
         add key range          
--------------------------------
 created key range from 1 to 10
(1 row)

db1=?> add key range 11 20 sh2 krid2;
          add key range          
---------------------------------
 created key range from 11 to 20
(1 row)

        Usage

        root@spqr_client:/go# connect.sh
psql (13.6 (Debian 13.6-1.pgdg110+1), server spqr)
Type "help" for help.

db1=?> insert into x (w_id) values(1);
ROUTER NOTICE: process Frontend for user user1 db1
ROUTER NOTICE: rerouting your connection
ROUTER NOTICE: rerouting state qrouter.ShardMatchState <nil>
ROUTER NOTICE: matched datashard routes [0xc0001459a0]
ROUTER NOTICE: initialize single datashard server conn
ROUTER NOTICE: adding datashard sh1
ROUTER NOTICE: executing your query &{insert into x (w_id) values(1);}
INSERT 0 1
db1=?> select * from x where w_id <= 10;
ROUTER NOTICE: rerouting your connection
ROUTER NOTICE: rerouting state qrouter.ShardMatchState <nil>
ROUTER NOTICE: matched datashard routes [0xc0003ce120]
ROUTER NOTICE: initialize single datashard server conn
ROUTER NOTICE: adding datashard sh1
ROUTER NOTICE: executing your query &{select * from x where w_id <= 10;}
 w_id 
------
    1
(1 row)

db1=?> insert into x (w_id) values(11);
ROUTER NOTICE: rerouting your connection
ROUTER NOTICE: rerouting state qrouter.ShardMatchState <nil>
ROUTER NOTICE: matched datashard routes [0xc00042c260]
ROUTER NOTICE: initialize single datashard server conn
ROUTER NOTICE: adding datashard sh2
ROUTER NOTICE: executing your query &{insert into x (w_id) values(11);}
INSERT 0 1
db1=?> select * from x where w_id <= 10;
ROUTER NOTICE: rerouting your connection
ROUTER NOTICE: rerouting state qrouter.ShardMatchState <nil>
ROUTER NOTICE: matched datashard routes [0xc00030a140]
ROUTER NOTICE: initialize single datashard server conn
ROUTER NOTICE: adding datashard sh1
ROUTER NOTICE: executing your query &{select * from x where w_id <= 10;}
 w_id 
------
    1
(1 row)

db1=?> select * from x where w_id <= 20;
ROUTER NOTICE: rerouting your connection
ROUTER NOTICE: rerouting state qrouter.ShardMatchState <nil>
ROUTER NOTICE: matched datashard routes [0xc00030a220]
ROUTER NOTICE: initialize single datashard server conn
ROUTER NOTICE: adding datashard sh2
ROUTER NOTICE: executing your query &{select * from x where w_id <= 20;}
 w_id 
------
   11
(1 row)

        Benchmarks

        Benchmarks using pgbench are not available since SPQR doesn't support some queries yet (for instance, truncate). So, using a custom script

        Benchmark max TPS

        SPQR shows ~4x fewer tps than direct Postgres shard

        Details

        pgbench configuration:

        1. 16 clients, 2 threads
        2. 32 clients, 2 threads
        3. 64 clients, 2 threads
        4. 96 clients, 2 threads

        Setup:

        • 6 cores (Intel Core i5-8400T CPU @ 1.70GHz × 6)
        • 24 GiB RAM

        Benchmark file simple.sql 

        -- \setrandom bid 1:n accounts
\set aid random(1, 100000)
-- \setrandom bid 1:n branches
\set bid random(1, 100000)
-- \setrandom tid 1:n tellers
\set tid random(1, 100000)
-- \setrandom delta -5000 5000
\set delta random(-5000,5000)

BEGIN;

UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

END;

        SPQR

        $ pgbench -t 1000 -c 16 -j 2 -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1
transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 16000/16000
latency average = 17.172 ms
tps = 931.766465 (including connections establishing)
tps = 931.783793 (excluding connections establishing)


$ pgbench -t 1000 -c 32 -j 2 -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1
transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 32
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 32000/32000
latency average = 34.375 ms
tps = 930.910941 (including connections establishing)
tps = 930.919062 (excluding connections establishing)


$ pgbench -t 1000 -c 64 -j 2 -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1
transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 64
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 64000/64000
latency average = 68.047 ms
tps = 940.532398 (including connections establishing)
tps = 940.535921 (excluding connections establishing)


$ pgbench -t 1000 -c 96 -j 2 -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1
transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 96
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 96000/96000
latency average = 101.659 ms
tps = 944.334859 (including connections establishing)
tps = 944.337465 (excluding connections establishing)

        Direct Postgres shard

        $ pgbench -t 1000 -c 16 -j 2 -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1
transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 16000/16000
latency average = 4.369 ms
tps = 3662.372136 (including connections establishing)
tps = 3665.774379 (excluding connections establishing)


$ pgbench -t 1000 -c 32 -j 2 -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1
transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 32
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 32000/32000
latency average = 8.261 ms
tps = 3873.446689 (including connections establishing)
tps = 3875.853352 (excluding connections establishing)


$ pgbench -t 1000 -c 64 -j 2 -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1
transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 64
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 64000/64000
latency average = 17.534 ms
tps = 3650.042099 (including connections establishing)
tps = 3650.546060 (excluding connections establishing)


$ pgbench -t 1000 -c 96 -j 2 -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1
transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 96
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 96000/96000
latency average = 24.713 ms
tps = 3884.660834 (including connections establishing)
tps = 3885.146506 (excluding connections establishing)

        Benchmarks latency (TPS limited)

        The test Postgres shard has a roughly constant latency (0.7-0.8ms), the SPQR router has (1.2-1.3ms) in the "comfort" tps zone (~500).

        As tps rises, latency increases dramatically:

        • ~4ms with 1000 tps,
        • ~12ms with 2000 tps,
        • ~50ms with 2500 tps,
        • >1000ms with 3000+ tps
        Details

        pgbench configuration:

        1. 4 clients, 4 threads: 500/1000 tps
        2. 8 clients, 8 threads: 500/1000/2000/2500/2750/3000 tps
        3. 16 clients, 16 threads: 1000 tps
        4. 32 clients, 32 threads: 1000 tps
        5. 64 clients, 64 threads: 1000 tps

        Setup:

        • 6 cores (Intel Core i5-8400T CPU @ 1.70GHz × 6) - CPU utilization ~33-50%
        • 24 GiB RAM

        Benchmark file simple.sql 

        \set aid random(1, 100000)

SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

        SPQR

        pgbench -t 10000 -c 4 -j 4 -R 500 -P 30 -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 10000
number of transactions actually processed: 40000/40000
latency average = 1.266 ms
latency stddev = 1.114 ms
rate limit schedule lag: avg 0.220 (max 15.152) ms
tps = 491.668577 (including connections establishing)
tps = 491.671934 (excluding connections establishing)


pgbench -t 10000 -c 4 -j 4 -R 1000 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 10000
number of transactions actually processed: 40000/40000
latency average = 4.366 ms
latency stddev = 5.952 ms
rate limit schedule lag: avg 2.707 (max 30.742) ms
tps = 1003.752459 (including connections establishing)
tps = 1003.772217 (excluding connections establishing)
statement latencies in milliseconds:
         0.024  \set aid random(1, 100000)
         1.636  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 4 -j 4 -R 3000 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 10000
number of transactions actually processed: 40000/40000
latency average = 445.004 ms
latency stddev = 252.434 ms
rate limit schedule lag: avg 443.594 (max 969.073) ms
tps = 2801.259753 (including connections establishing)
tps = 2801.376763 (excluding connections establishing)
statement latencies in milliseconds:
         0.015  \set aid random(1, 100000)
         1.394  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 1000 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 4.236 ms
latency stddev = 6.114 ms
rate limit schedule lag: avg 1.975 (max 41.077) ms
tps = 979.509857 (including connections establishing)
tps = 979.528534 (excluding connections establishing)
statement latencies in milliseconds:
         0.026  \set aid random(1, 100000)
         2.235  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 2000 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 7.656 ms
latency stddev = 8.697 ms
rate limit schedule lag: avg 5.462 (max 60.452) ms
tps = 1970.101708 (including connections establishing)
tps = 1970.139964 (excluding connections establishing)
statement latencies in milliseconds:
         0.017  \set aid random(1, 100000)
         2.177  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 6 -j 6 -R 2500 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

number of clients: 6
number of threads: 6
number of transactions per client: 10000
number of transactions actually processed: 60000/60000
latency average = 12.289 ms
latency stddev = 11.389 ms
rate limit schedule lag: avg 10.447 (max 92.029) ms
tps = 2439.886137 (including connections establishing)
tps = 2439.965675 (excluding connections establishing)
statement latencies in milliseconds:
         0.015  \set aid random(1, 100000)
         1.827  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 2750 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 52.701 ms
latency stddev = 57.376 ms
rate limit schedule lag: avg 50.012 (max 324.221) ms
tps = 2732.192838 (including connections establishing)
tps = 2732.265667 (excluding connections establishing)
statement latencies in milliseconds:
         0.018  \set aid random(1, 100000)
         2.671  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 3000 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1249.946 ms
latency stddev = 759.645 ms
rate limit schedule lag: avg 1247.055 (max 2792.955) ms
tps = 2745.181677 (including connections establishing)
tps = 2745.313593 (excluding connections establishing)
statement latencies in milliseconds:
         0.017  \set aid random(1, 100000)
         2.875  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 3000 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1221.813 ms
latency stddev = 720.291 ms
rate limit schedule lag: avg 1218.949 (max 2928.095) ms
tps = 2770.578892 (including connections establishing)
tps = 2770.654550 (excluding connections establishing)
statement latencies in milliseconds:
         0.015  \set aid random(1, 100000)
         2.848  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 16 -j 16 -R 1000 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 16
number of transactions per client: 10000
number of transactions actually processed: 160000/160000
latency average = 4.143 ms
latency stddev = 6.139 ms
rate limit schedule lag: avg 1.280 (max 36.672) ms
tps = 983.396252 (including connections establishing)
tps = 983.410887 (excluding connections establishing)
statement latencies in milliseconds:
         0.028  \set aid random(1, 100000)
         2.835  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 32 -j 32 -R 1000 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 32
number of threads: 32
number of transactions per client: 10000
number of transactions actually processed: 320000/320000
latency average = 4.423 ms
latency stddev = 6.445 ms
rate limit schedule lag: avg 0.866 (max 51.322) ms
tps = 978.190049 (including connections establishing)
tps = 978.198354 (excluding connections establishing)
statement latencies in milliseconds:
         0.030  \set aid random(1, 100000)
         3.527  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 64 -j 64 -R 1000 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 64
number of threads: 64
number of transactions per client: 10000
number of transactions actually processed: 640000/640000
latency average = 4.551 ms
latency stddev = 6.678 ms
rate limit schedule lag: avg 0.557 (max 71.708) ms
tps = 980.568469 (including connections establishing)
tps = 980.572268 (excluding connections establishing)
statement latencies in milliseconds:
         0.030  \set aid random(1, 100000)
         3.964  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

        Direct Postgres shard

        pgbench -t 10000 -c 4 -j 4 -R 500 -P 30 -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 10000
number of transactions actually processed: 40000/40000
latency average = 0.968 ms
latency stddev = 0.265 ms
rate limit schedule lag: avg 0.152 (max 10.719) ms
tps = 496.396546 (including connections establishing)
tps = 496.414013 (excluding connections establishing)


pgbench -t 10000 -c 4 -j 4 -R 1000 -P 10 -r -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 10000
number of transactions actually processed: 40000/40000
latency average = 0.837 ms
latency stddev = 0.444 ms
rate limit schedule lag: avg 0.180 (max 17.825) ms
tps = 985.736494 (including connections establishing)
tps = 985.808805 (excluding connections establishing)
statement latencies in milliseconds:
         0.033  \set aid random(1, 100000)
         0.623  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 4 -j 4 -R 3000 -P 10 -r -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 10000
number of transactions actually processed: 40000/40000
latency average = 0.871 ms
latency stddev = 0.553 ms
rate limit schedule lag: avg 0.332 (max 9.632) ms
tps = 2959.318357 (including connections establishing)
tps = 2960.008784 (excluding connections establishing)
statement latencies in milliseconds:
         0.039  \set aid random(1, 100000)
         0.500  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 1000 -P 10 -r -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 0.853 ms
latency stddev = 0.244 ms
rate limit schedule lag: avg 0.136 (max 10.092) ms
tps = 999.297015 (including connections establishing)
tps = 999.345161 (excluding connections establishing)
statement latencies in milliseconds:
         0.039  \set aid random(1, 100000)
         0.678  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

pgbench -t 10000 -c 8 -j 8 -R 2000 -P 10 -r -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 0.780 ms
latency stddev = 0.403 ms
rate limit schedule lag: avg 0.168 (max 12.335) ms
tps = 1978.520768 (including connections establishing)
tps = 1978.726381 (excluding connections establishing)
statement latencies in milliseconds:
         0.043  \set aid random(1, 100000)
         0.570  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 6 -j 6 -R 2500 -P 10 -r -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

query mode: simple
number of clients: 6
number of threads: 6
number of transactions per client: 10000
number of transactions actually processed: 60000/60000
latency average = 0.831 ms
latency stddev = 0.496 ms
rate limit schedule lag: avg 0.232 (max 17.122) ms
tps = 2440.640678 (including connections establishing)
tps = 2440.971341 (excluding connections establishing)
statement latencies in milliseconds:
         0.046  \set aid random(1, 100000)
         0.553  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 2750 -P 10 -r -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 0.800 ms
latency stddev = 0.462 ms
rate limit schedule lag: avg 0.206 (max 16.878) ms
tps = 2695.942828 (including connections establishing)
tps = 2696.541711 (excluding connections establishing)
statement latencies in milliseconds:
         0.051  \set aid random(1, 100000)
         0.543  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 3000 -P 10 -r -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 0.787 ms
latency stddev = 0.449 ms
rate limit schedule lag: avg 0.209 (max 11.998) ms
tps = 2961.723381 (including connections establishing)
tps = 2962.243329 (excluding connections establishing)
statement latencies in milliseconds:
         0.052  \set aid random(1, 100000)
         0.526  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 16 -j 16 -R 1000 -P 30 -r -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 16
number of transactions per client: 10000
number of transactions actually processed: 160000/160000
latency average = 0.860 ms
latency stddev = 0.205 ms
rate limit schedule lag: avg 0.118 (max 11.097) ms
tps = 971.476574 (including connections establishing)
tps = 971.515096 (excluding connections establishing)
statement latencies in milliseconds:
         0.040  \set aid random(1, 100000)
         0.701  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 32 -j 32 -R 1000 -P 10 -r -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 32
number of threads: 32
number of transactions per client: 10000
number of transactions actually processed: 320000/320000
latency average = 0.887 ms
latency stddev = 0.271 ms
rate limit schedule lag: avg 0.116 (max 33.516) ms
tps = 977.774383 (including connections establishing)
tps = 977.861239 (excluding connections establishing)
statement latencies in milliseconds:
         0.045  \set aid random(1, 100000)
         0.726  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 32 -j 32 -R 1000 -P 10 -r -n -f ./config-example/simple.sql -p 7432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

scaling factor: 1
query mode: simple
number of clients: 64
number of threads: 64
number of transactions per client: 10000
number of transactions actually processed: 640000/640000
latency average = 0.884 ms
latency stddev = 0.346 ms
rate limit schedule lag: avg 0.131 (max 54.920) ms
tps = 976.828626 (including connections establishing)
tps = 976.879615 (excluding connections establishing)
statement latencies in milliseconds:
         0.044  \set aid random(1, 100000)
         0.709  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

        Benchmarks latency (TPS limited) with logging disabled

        The test Postgres shard has a roughly constant latency (0.7-0.8ms), the SPQR router has (1.2-1.4ms).

        As tps rises, latency doesn't increases dramatically

        Details The same configuration and setup 
        pgbench -t 10000 -c 8 -j 8 -R 500 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1.016 ms
latency stddev = 0.517 ms
rate limit schedule lag: avg 0.125 (max 15.788) ms
tps = 494.449012 (including connections establishing)
tps = 494.453170 (excluding connections establishing)
statement latencies in milliseconds:
         0.039  \set aid random(1, 100000)
         0.852  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 1000 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1 2>&1 | grep -v -e 'client \|aid\|^$\|ROUTER NOTICE'
progress: 10.0 s, 997.1 tps, lat 1.110 ms stddev 0.494, lag 0.164 ms
progress: 20.0 s, 1011.7 tps, lat 1.178 ms stddev 0.534, lag 0.173 ms
progress: 30.0 s, 985.5 tps, lat 1.165 ms stddev 0.438, lag 0.165 ms
progress: 40.0 s, 1010.5 tps, lat 1.187 ms stddev 0.505, lag 0.173 ms
progress: 50.0 s, 984.9 tps, lat 1.202 ms stddev 0.494, lag 0.181 ms
progress: 60.0 s, 993.0 tps, lat 1.181 ms stddev 0.480, lag 0.168 ms
progress: 70.0 s, 993.9 tps, lat 1.194 ms stddev 0.477, lag 0.170 ms
pghost: ::1 pgport: 8432 nclients: 8 nxacts: 10000 dbName: sslmode=disable dbname=db1
transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1.178 ms
latency stddev = 0.503 ms
rate limit schedule lag: avg 0.172 (max 14.358) ms
tps = 981.792326 (including connections establishing)
tps = 981.805281 (excluding connections establishing)
statement latencies in milliseconds:


pgbench -t 10000 -c 8 -j 8 -R 1500 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1 2>&1 | grep -v -e 'client \|aid\|^$\|ROUTER NOTICE'
progress: 10.0 s, 1481.5 tps, lat 1.248 ms stddev 0.696, lag 0.240 ms
progress: 20.0 s, 1508.5 tps, lat 1.282 ms stddev 0.780, lag 0.260 ms
progress: 30.0 s, 1514.6 tps, lat 1.291 ms stddev 0.712, lag 0.248 ms
progress: 40.0 s, 1526.6 tps, lat 1.244 ms stddev 0.650, lag 0.236 ms
progress: 50.0 s, 1505.7 tps, lat 1.256 ms stddev 0.649, lag 0.236 ms
pghost: ::1 pgport: 8432 nclients: 8 nxacts: 10000 dbName: sslmode=disable dbname=db1
transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1.261 ms
latency stddev = 0.698 ms
rate limit schedule lag: avg 0.243 (max 14.822) ms
tps = 1480.197643 (including connections establishing)
tps = 1480.222968 (excluding connections establishing)


pgbench -t 10000 -c 8 -j 8 -R 2000 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1 2>&1 | grep -v -e 'client \|aid\|^$\|ROUTER NOTICE'
progress: 10.0 s, 2009.0 tps, lat 1.207 ms stddev 0.771, lag 0.278 ms
progress: 20.0 s, 2002.9 tps, lat 1.290 ms stddev 0.860, lag 0.313 ms
progress: 30.0 s, 2008.6 tps, lat 1.336 ms stddev 0.868, lag 0.323 ms
progress: 40.0 s, 1947.8 tps, lat 1.299 ms stddev 0.793, lag 0.306 ms
pghost: ::1 pgport: 8432 nclients: 8 nxacts: 10000 dbName: sslmode=disable dbname=db1
transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1.282 ms
latency stddev = 0.824 ms
rate limit schedule lag: avg 0.305 (max 12.405) ms
tps = 1967.735580 (including connections establishing)
tps = 1967.772584 (excluding connections establishing)


pgbench -t 10000 -c 8 -j 8 -R 2500 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1 2>&1 | grep -v -e 'client \|aid\|^$\|ROUTER NOTICE'
progress: 20.0 s, 2502.2 tps, lat 1.336 ms stddev 0.888, lag 0.375 ms
progress: 30.0 s, 2513.6 tps, lat 1.423 ms stddev 1.125, lag 0.423 ms
pghost: ::1 pgport: 8432 nclients: 8 nxacts: 10000 dbName: sslmode=disable dbname=db1
transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1.347 ms
latency stddev = 1.011 ms
rate limit schedule lag: avg 0.387 (max 26.905) ms
tps = 2477.810845 (including connections establishing)
tps = 2477.897324 (excluding connections establishing)


pgbench -t 10000 -c 8 -j 8 -R 2750 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1 2>&1 | grep -v -e 'client \|aid\|^$\|ROUTER NOTICE'
progress: 10.0 s, 2778.4 tps, lat 1.318 ms stddev 1.130, lag 0.424 ms
progress: 20.0 s, 2744.7 tps, lat 1.183 ms stddev 1.099, lag 0.378 ms
pghost: ::1 pgport: 8432 nclients: 8 nxacts: 10000 dbName: sslmode=disable dbname=db1
transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1.314 ms
latency stddev = 1.109 ms
rate limit schedule lag: avg 0.420 (max 20.767) ms
tps = 2717.310711 (including connections establishing)
tps = 2717.374826 (excluding connections establishing)


pgbench -t 10000 -c 8 -j 8 -R 3000 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1 2>&1 | grep -v -e 'client \|aid\|^$\|ROUTER NOTICE'
progress: 10.0 s, 3008.9 tps, lat 1.249 ms stddev 1.207, lag 0.438 ms
progress: 20.0 s, 2978.1 tps, lat 1.566 ms stddev 1.096, lag 0.542 ms
pghost: ::1 pgport: 8432 nclients: 8 nxacts: 10000 dbName: sslmode=disable dbname=db1
transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1.418 ms
latency stddev = 1.145 ms
rate limit schedule lag: avg 0.491 (max 15.930) ms
tps = 2973.925173 (including connections establishing)
tps = 2974.002421 (excluding connections establishing)


pgbench -t 50000 -c 8 -j 8 -R 5000 -P 10 -r -n -f ./config-example/simple.sql -p 8432 -h ::1 -U user1 -d "sslmode=disable dbname=db1" --protocol simple db1

transaction type: ./config-example/simple.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 50000
number of transactions actually processed: 400000/400000
latency average = 1.537 ms
latency stddev = 9.071 ms
rate limit schedule lag: avg 1.094 (max 180.853) ms
tps = 4987.967380 (including connections establishing)
tps = 4988.014252 (excluding connections establishing)
statement latencies in milliseconds:
         0.076  \set aid random(1, 100000)
         0.367  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
        Edited by Artyom Kartasov
      • Artyom Kartasov
        Artyom Kartasov @akartasov ·
        Author Maintainer

        pgcat

        Summary:

        • how to interact with database objects, table relations
        • how to reshard data
        • looks more mature than SPQR (less TODO and implement me notes)
        • requires manual steps to prepare each shard (see snippets)
        • requires an explicit state definition (shard or sharding key) before query

        Snippets

        Benchmarks

        Local benchmarks

        PgCat shows 1.6-1.8x fewer tps than direct Postgres

        Details

        pgbench configuration:

        1. 16 clients, 2 threads
        2. 32 clients, 2 threads
        3. 64 clients, 2 threads
        4. 128 clients, 2 threads

        Setup:

        • 6 cores (Intel Core i5-8400T CPU @ 1.70GHz × 6)
        • 24 GiB RAM

        PgCat

        $ pgbench -i shard0 -p 6432 -h 127.0.0.1
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data...
100000 of 100000 tuples (100%) done (elapsed 0.02 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.

$ pgbench -t 1000 -c 16 -j 2 -p 6432 -h 127.0.0.1 -S --protocol extended shard0
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: extended
number of clients: 16
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 16000/16000
latency average = 0.945 ms
tps = 16927.900370 (including connections establishing)
tps = 17014.602086 (excluding connections establishing)

$ pgbench -t 1000 -c 32 -j 2 -p 6432 -h 127.0.0.1 -S --protocol extended shard0
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: extended
number of clients: 32
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 32000/32000
latency average = 1.737 ms
tps = 18420.046665 (including connections establishing)
tps = 18444.587869 (excluding connections establishing)

$ pgbench -t 1000 -c 64 -j 2 -p 6432 -h 127.0.0.1 -S --protocol extended shard0
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: extended
number of clients: 64
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 64000/64000
latency average = 4.145 ms
tps = 15439.385104 (including connections establishing)
tps = 15443.811398 (excluding connections establishing)

$ pgbench -t 1000 -c 128 -j 2 -p 6432 -h 127.0.0.1 -S --protocol extended shard0
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: extended
number of clients: 128
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 128000/128000
latency average = 7.002 ms
tps = 18280.635745 (including connections establishing)
tps = 18284.154285 (excluding connections establishing)

        Direct Postgres

        $ pgbench -i postgres -p 5432 -h 127.0.0.1 -U postgres 
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data...
100000 of 100000 tuples (100%) done (elapsed 0.02 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.

$ pgbench -t 1000 -c 16 -j 2 -p 5432 -h 127.0.0.1 -U postgres -S --protocol extended postgres
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: extended
number of clients: 16
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 16000/16000
latency average = 0.520 ms
tps = 30798.798564 (including connections establishing)
tps = 31243.267575 (excluding connections establishing)

$ pgbench -t 1000 -c 32 -j 2 -p 5432 -h 127.0.0.1 -U postgres -S --protocol extended postgres
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: extended
number of clients: 32
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 32000/32000
latency average = 1.030 ms
tps = 31080.546976 (including connections establishing)
tps = 31269.169593 (excluding connections establishing)

$ pgbench -t 1000 -c 64 -j 2 -p 5432 -h 127.0.0.1 -U postgres -S --protocol extended postgres
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: extended
number of clients: 64
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 64000/64000
latency average = 1.967 ms
tps = 32533.884229 (including connections establishing)
tps = 32634.277623 (excluding connections establishing)

$ pgbench -t 1000 -c 128 -j 2 -p 5432 -h 127.0.0.1 -U postgres -S --protocol extended postgres
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: extended
number of clients: 128
number of threads: 2
number of transactions per client: 1000
number of transactions actually processed: 128000/128000
latency average = 4.125 ms
tps = 31027.294752 (including connections establishing)
tps = 31058.666807 (excluding connections establishing)

        Benchmarks latency (TPS limited)

        The test Postgres container has a roughly constant latency (0.7-0.8ms), the PgCat router has (1.4-1.7ms).

        Details

        pgbench configuration:

        1. 4 clients, 4 threads: 500/1000 tps
        2. 8 clients, 8 threads: 500/1000/1500/2000/2500/2750/3000 tps
        3. 16 clients, 16 threads: 1000 tps
        4. 32 clients, 32 threads: 1000 tps

        Setup:

        • 6 cores (Intel Core i5-8400T CPU @ 1.70GHz × 6) - CPU utilization ~15-65%
        • 24 GiB RAM

        Benchmark file simple.sql 

        \set aid random(1, 100000)

SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

        PgCat

        pgbench -t 10000 -c 4 -j 4 -R 500 -P 10 -r -n -f ./tests/pgbench/select.sql -p 6432 -h 127.0.0.1 --protocol simple shard0

progress: 10.0 s, 494.6 tps, lat 1.653 ms stddev 0.450, lag 0.264 ms
progress: 20.0 s, 511.6 tps, lat 1.665 ms stddev 0.413, lag 0.263 ms
progress: 30.0 s, 474.1 tps, lat 1.665 ms stddev 0.410, lag 0.265 ms
progress: 40.0 s, 504.5 tps, lat 1.653 ms stddev 0.425, lag 0.262 ms
progress: 50.0 s, 495.8 tps, lat 1.644 ms stddev 0.415, lag 0.252 ms
progress: 60.0 s, 504.7 tps, lat 1.637 ms stddev 0.494, lag 0.267 ms
progress: 70.0 s, 494.0 tps, lat 1.644 ms stddev 0.490, lag 0.260 ms
progress: 80.0 s, 487.4 tps, lat 1.626 ms stddev 0.466, lag 0.265 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 10000
number of transactions actually processed: 40000/40000
latency average = 1.649 ms
latency stddev = 0.447 ms
rate limit schedule lag: avg 0.262 (max 6.225) ms
tps = 489.253915 (including connections establishing)
tps = 489.259925 (excluding connections establishing)
statement latencies in milliseconds:
         0.004  \set aid random(1, 100000)
         1.382  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;



pgbench -t 10000 -c 4 -j 4 -R 1000 -P 10 -r -n -f ./tests/pgbench/select.sql -p 6432 -h 127.0.0.1 --protocol simple shard0

progress: 10.0 s, 1004.7 tps, lat 1.666 ms stddev 0.706, lag 0.404 ms
progress: 20.0 s, 995.8 tps, lat 1.639 ms stddev 0.717, lag 0.402 ms
progress: 30.0 s, 1013.2 tps, lat 1.685 ms stddev 0.711, lag 0.414 ms
progress: 40.0 s, 980.8 tps, lat 1.688 ms stddev 0.729, lag 0.427 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 10000
number of transactions actually processed: 40000/40000
latency average = 1.670 ms
latency stddev = 0.716 ms
rate limit schedule lag: avg 0.412 (max 8.217) ms
tps = 993.887751 (including connections establishing)
tps = 993.912881 (excluding connections establishing)
statement latencies in milliseconds:
         0.004  \set aid random(1, 100000)
         1.255  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;



pgbench -t 10000 -c 8 -j 8 -R 500 -P 10 -r -n -f ./tests/pgbench/select.sql -p 6432 -h 127.0.0.1 --protocol simple shard0

progress: 10.0 s, 498.2 tps, lat 1.544 ms stddev 0.369, lag 0.182 ms
progress: 20.0 s, 502.4 tps, lat 1.507 ms stddev 0.412, lag 0.172 ms
progress: 30.0 s, 489.5 tps, lat 1.463 ms stddev 0.422, lag 0.167 ms
progress: 40.0 s, 496.5 tps, lat 1.550 ms stddev 0.344, lag 0.174 ms
progress: 50.0 s, 481.3 tps, lat 1.551 ms stddev 0.334, lag 0.169 ms
progress: 60.0 s, 500.9 tps, lat 1.556 ms stddev 0.340, lag 0.172 ms
progress: 70.0 s, 501.4 tps, lat 1.571 ms stddev 0.336, lag 0.176 ms
progress: 80.0 s, 499.8 tps, lat 1.567 ms stddev 0.335, lag 0.179 ms
progress: 90.0 s, 489.1 tps, lat 1.229 ms stddev 0.531, lag 0.147 ms
progress: 100.0 s, 508.4 tps, lat 0.885 ms stddev 0.483, lag 0.116 ms
progress: 110.0 s, 491.5 tps, lat 1.533 ms stddev 0.372, lag 0.175 ms
progress: 120.0 s, 500.3 tps, lat 1.582 ms stddev 0.321, lag 0.177 ms
progress: 130.0 s, 487.9 tps, lat 1.556 ms stddev 0.328, lag 0.173 ms
progress: 140.0 s, 489.5 tps, lat 1.599 ms stddev 0.371, lag 0.187 ms
progress: 150.0 s, 501.5 tps, lat 1.577 ms stddev 0.350, lag 0.187 ms
progress: 160.0 s, 480.5 tps, lat 1.592 ms stddev 0.374, lag 0.182 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1.493 ms
latency stddev = 0.422 ms
rate limit schedule lag: avg 0.172 (max 8.840) ms
tps = 489.885633 (including connections establishing)
tps = 489.899865 (excluding connections establishing)
statement latencies in milliseconds:
         0.004  \set aid random(1, 100000)
         1.317  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 1000 -P 10 -r -n -f ./tests/pgbench/select.sql -p 6432 -h 127.0.0.1 --protocol simple shard0

progress: 10.0 s, 1000.6 tps, lat 1.547 ms stddev 0.461, lag 0.235 ms
progress: 20.0 s, 1001.9 tps, lat 1.511 ms stddev 0.500, lag 0.230 ms
progress: 30.0 s, 990.0 tps, lat 1.535 ms stddev 0.428, lag 0.227 ms
progress: 40.0 s, 983.3 tps, lat 1.434 ms stddev 0.534, lag 0.218 ms
progress: 50.0 s, 997.5 tps, lat 1.484 ms stddev 0.486, lag 0.224 ms
progress: 60.0 s, 1000.1 tps, lat 1.517 ms stddev 0.560, lag 0.228 ms
progress: 70.0 s, 994.3 tps, lat 1.536 ms stddev 0.425, lag 0.226 ms
progress: 80.0 s, 987.7 tps, lat 1.529 ms stddev 0.551, lag 0.239 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1.512 ms
latency stddev = 0.497 ms
rate limit schedule lag: avg 0.229 (max 14.454) ms
tps = 988.175229 (including connections establishing)
tps = 988.193756 (excluding connections establishing)
statement latencies in milliseconds:
         0.004  \set aid random(1, 100000)
         1.280  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;



pgbench -t 10000 -c 8 -j 8 -R 1500 -P 10 -r -n -f ./tests/pgbench/select.sql -p 6432 -h 127.0.0.1 --protocol simple shard0

progress: 10.0 s, 1505.7 tps, lat 1.542 ms stddev 0.619, lag 0.295 ms
progress: 20.0 s, 1506.2 tps, lat 1.534 ms stddev 0.818, lag 0.311 ms
progress: 30.0 s, 1506.2 tps, lat 1.538 ms stddev 0.629, lag 0.292 ms
progress: 40.0 s, 1505.7 tps, lat 1.578 ms stddev 0.755, lag 0.319 ms
progress: 50.0 s, 1499.2 tps, lat 1.431 ms stddev 0.735, lag 0.285 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1.517 ms
latency stddev = 0.720 ms
rate limit schedule lag: avg 0.299 (max 13.941) ms
tps = 1484.140644 (including connections establishing)
tps = 1484.185755 (excluding connections establishing)
statement latencies in milliseconds:
         0.004  \set aid random(1, 100000)
         1.214  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 2000 -P 10 -r -n -f ./tests/pgbench/select.sql -p 6432 -h 127.0.0.1 --protocol simple shard0

progress: 10.0 s, 1998.3 tps, lat 1.546 ms stddev 0.973, lag 0.382 ms
progress: 20.0 s, 2024.9 tps, lat 1.594 ms stddev 0.856, lag 0.389 ms
progress: 30.0 s, 1989.3 tps, lat 1.545 ms stddev 0.819, lag 0.369 ms
progress: 40.0 s, 1955.1 tps, lat 1.603 ms stddev 0.891, lag 0.393 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1.573 ms
latency stddev = 0.887 ms
rate limit schedule lag: avg 0.384 (max 16.778) ms
tps = 1969.618071 (including connections establishing)
tps = 1969.705857 (excluding connections establishing)
statement latencies in milliseconds:
         0.003  \set aid random(1, 100000)
         1.186  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 2500 -P 10 -r -n -f ./tests/pgbench/select.sql -p 6432 -h 127.0.0.1 --protocol simple shard0

progress: 10.0 s, 2482.5 tps, lat 1.755 ms stddev 1.224, lag 0.547 ms
progress: 20.0 s, 2509.1 tps, lat 1.596 ms stddev 0.870, lag 0.446 ms
progress: 30.0 s, 2513.7 tps, lat 1.631 ms stddev 0.953, lag 0.471 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1.664 ms
latency stddev = 1.024 ms
rate limit schedule lag: avg 0.489 (max 16.797) ms
tps = 2460.392453 (including connections establishing)
tps = 2460.484259 (excluding connections establishing)
statement latencies in milliseconds:
         0.003  \set aid random(1, 100000)
         1.172  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 2750 -P 10 -r -n -f ./tests/pgbench/select.sql -p 6432 -h 127.0.0.1 --protocol simple shard0

progress: 10.0 s, 2757.2 tps, lat 1.935 ms stddev 1.638, lag 0.715 ms
progress: 20.0 s, 2788.3 tps, lat 1.682 ms stddev 1.143, lag 0.545 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1.797 ms
latency stddev = 1.368 ms
rate limit schedule lag: avg 0.616 (max 21.052) ms
tps = 2748.179851 (including connections establishing)
tps = 2748.330484 (excluding connections establishing)
statement latencies in milliseconds:
         0.003  \set aid random(1, 100000)
         1.178  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 3000 -P 10 -r -n -f ./tests/pgbench/select.sql -p 6432 -h 127.0.0.1 --protocol simple shard0

progress: 10.0 s, 3001.1 tps, lat 1.700 ms stddev 1.140, lag 0.581 ms
progress: 20.0 s, 2983.1 tps, lat 1.723 ms stddev 1.365, lag 0.616 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 1.748 ms
latency stddev = 1.504 ms
rate limit schedule lag: avg 0.631 (max 27.003) ms
tps = 2951.138716 (including connections establishing)
tps = 2951.317308 (excluding connections establishing)
statement latencies in milliseconds:
         0.003  \set aid random(1, 100000)
         1.115  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 5000 -P 10 -r -n -f ./tests/pgbench/select.sql -p 6432 -h 127.0.0.1 --protocol simple shard0

progress: 10.0 s, 4982.8 tps, lat 3.382 ms stddev 9.680, lag 2.645 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 2.747 ms
latency stddev = 7.795 ms
rate limit schedule lag: avg 1.982 (max 90.818) ms
tps = 4932.922787 (including connections establishing)
tps = 4933.656400 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  \set aid random(1, 100000)
         0.764  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 5000 -P 10 -r -n -f ./tests/pgbench/select.sql -p 6432 -h 127.0.0.1 --protocol simple shard0
progress: 10.0 s, 4980.7 tps, lat 2.758 ms stddev 4.505, lag 1.884 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 2.265 ms
latency stddev = 3.770 ms
rate limit schedule lag: avg 1.439 (max 60.268) ms
tps = 4904.545378 (including connections establishing)
tps = 4904.996646 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  \set aid random(1, 100000)
         0.824  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;



pgbench -t 50000 -c 8 -j 8 -R 5000 -P 10 -r -n -f ./tests/pgbench/select.sql -p 6432 -h 127.0.0.1 --protocol simple shard0

progress: 10.0 s, 4990.2 tps, lat 1.947 ms stddev 4.289, lag 1.232 ms
progress: 20.0 s, 4986.7 tps, lat 2.174 ms stddev 2.544, lag 1.272 ms
progress: 30.0 s, 5016.6 tps, lat 2.581 ms stddev 5.866, lag 1.765 ms
progress: 40.0 s, 4971.4 tps, lat 1.073 ms stddev 1.089, lag 0.426 ms
progress: 50.0 s, 5014.4 tps, lat 0.672 ms stddev 1.048, lag 0.257 ms
progress: 60.0 s, 4991.4 tps, lat 0.744 ms stddev 0.888, lag 0.256 ms
progress: 70.0 s, 4999.2 tps, lat 1.551 ms stddev 2.402, lag 0.855 ms
progress: 80.0 s, 4933.7 tps, lat 0.712 ms stddev 0.745, lag 0.235 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 50000
number of transactions actually processed: 400000/400000
latency average = 1.435 ms
latency stddev = 3.013 ms
rate limit schedule lag: avg 0.790 (max 89.778) ms
tps = 4955.120810 (including connections establishing)
tps = 4955.214643 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  \set aid random(1, 100000)
         0.644  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;



pgbench -t 10000 -c 16 -j 16 -R 1000 -P 30 -r -n -f ./tests/pgbench/select.sql -p 6432 -h 127.0.0.1 --protocol simple shard0

progress: 30.0 s, 989.1 tps, lat 1.483 ms stddev 0.562, lag 0.165 ms
progress: 60.0 s, 989.8 tps, lat 1.473 ms stddev 0.345, lag 0.161 ms
progress: 90.0 s, 997.3 tps, lat 1.470 ms stddev 0.314, lag 0.158 ms
progress: 120.0 s, 1015.0 tps, lat 1.464 ms stddev 0.356, lag 0.159 ms
progress: 150.0 s, 981.0 tps, lat 1.486 ms stddev 0.323, lag 0.160 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 16
number of transactions per client: 10000
number of transactions actually processed: 160000/160000
latency average = 1.433 ms
latency stddev = 0.481 ms
rate limit schedule lag: avg 0.159 (max 28.999) ms
tps = 981.222148 (including connections establishing)
tps = 981.309326 (excluding connections establishing)
statement latencies in milliseconds:
         0.004  \set aid random(1, 100000)
         1.270  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

pgbench -t 10000 -c 32 -j 32 -R 1000 -P 80 -r -n -f ./tests/pgbench/select.sql -p 6432 -h 127.0.0.1 --protocol simple shard0
progress: 80.0 s, 1001.8 tps, lat 1.473 ms stddev 0.432, lag 0.137 ms
progress: 160.0 s, 999.1 tps, lat 1.448 ms stddev 0.406, lag 0.135 ms
progress: 240.0 s, 1006.3 tps, lat 1.438 ms stddev 0.362, lag 0.133 ms
progress: 320.0 s, 983.8 tps, lat 1.469 ms stddev 0.350, lag 0.135 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 32
number of threads: 32
number of transactions per client: 10000
number of transactions actually processed: 320000/320000
latency average = 1.458 ms
latency stddev = 0.389 ms
rate limit schedule lag: avg 0.135 (max 16.664) ms
tps = 983.809199 (including connections establishing)
tps = 983.845318 (excluding connections establishing)
statement latencies in milliseconds:
         0.004  \set aid random(1, 100000)
         1.319  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

        Direct Postgres container

        pgbench -t 10000 -c 4 -j 4 -R 500 -P 10 -r -n -f ./tests/pgbench/select.sql -p 5432 -h 127.0.0.1 -U postgres --protocol simple postgres

progress: 10.0 s, 491.0 tps, lat 0.904 ms stddev 0.198, lag 0.160 ms
progress: 20.0 s, 495.2 tps, lat 0.881 ms stddev 0.199, lag 0.155 ms
progress: 30.0 s, 496.2 tps, lat 0.904 ms stddev 0.191, lag 0.160 ms
progress: 40.0 s, 501.2 tps, lat 0.873 ms stddev 0.216, lag 0.158 ms
progress: 50.0 s, 493.8 tps, lat 0.456 ms stddev 0.229, lag 0.099 ms
progress: 60.0 s, 497.0 tps, lat 0.759 ms stddev 0.295, lag 0.144 ms
progress: 70.0 s, 509.3 tps, lat 0.777 ms stddev 0.252, lag 0.140 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 10000
number of transactions actually processed: 40000/40000
latency average = 0.802 ms
latency stddev = 0.270 ms
rate limit schedule lag: avg 0.146 (max 3.485) ms
tps = 486.932297 (including connections establishing)
tps = 486.953656 (excluding connections establishing)
statement latencies in milliseconds:
         0.004  \set aid random(1, 100000)
         0.652  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;



pgbench -t 10000 -c 4 -j 4 -R 1000 -P 10 -r -n -f ./tests/pgbench/select.sql -p 5432 -h 127.0.0.1 -U postgres --protocol simple postgres
progress: 10.0 s, 1000.0 tps, lat 0.864 ms stddev 0.218, lag 0.175 ms
progress: 20.0 s, 994.9 tps, lat 0.857 ms stddev 0.230, lag 0.178 ms
progress: 30.0 s, 1010.5 tps, lat 0.866 ms stddev 0.212, lag 0.179 ms
progress: 40.0 s, 970.6 tps, lat 0.841 ms stddev 0.283, lag 0.175 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 10000
number of transactions actually processed: 40000/40000
latency average = 0.858 ms
latency stddev = 0.237 ms
rate limit schedule lag: avg 0.177 (max 5.640) ms
tps = 986.639573 (including connections establishing)
tps = 986.729141 (excluding connections establishing)
statement latencies in milliseconds:
         0.004  \set aid random(1, 100000)
         0.677  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;



pgbench -t 10000 -c 8 -j 8 -R 500 -P 10 -r -n -f ./tests/pgbench/select.sql -p 5432 -h 127.0.0.1 -U postgres --protocol simple postgres
progress: 10.0 s, 509.9 tps, lat 0.905 ms stddev 0.190, lag 0.142 ms
progress: 20.0 s, 501.7 tps, lat 0.910 ms stddev 0.168, lag 0.143 ms
progress: 30.0 s, 509.4 tps, lat 0.899 ms stddev 0.209, lag 0.143 ms
progress: 40.0 s, 499.0 tps, lat 0.908 ms stddev 0.162, lag 0.141 ms
progress: 50.0 s, 492.9 tps, lat 0.914 ms stddev 0.163, lag 0.144 ms
progress: 60.0 s, 498.2 tps, lat 0.912 ms stddev 0.179, lag 0.145 ms
progress: 70.0 s, 506.9 tps, lat 0.909 ms stddev 0.166, lag 0.143 ms
progress: 80.0 s, 487.7 tps, lat 0.913 ms stddev 0.168, lag 0.145 ms
progress: 90.0 s, 500.8 tps, lat 0.911 ms stddev 0.175, lag 0.145 ms
progress: 100.0 s, 494.7 tps, lat 0.906 ms stddev 0.165, lag 0.141 ms
progress: 110.0 s, 500.4 tps, lat 0.908 ms stddev 0.170, lag 0.144 ms
progress: 120.0 s, 501.9 tps, lat 0.910 ms stddev 0.168, lag 0.144 ms
progress: 130.0 s, 503.7 tps, lat 0.905 ms stddev 0.157, lag 0.141 ms
progress: 140.0 s, 500.1 tps, lat 0.912 ms stddev 0.176, lag 0.147 ms
progress: 150.0 s, 511.0 tps, lat 0.905 ms stddev 0.187, lag 0.144 ms
progress: 160.0 s, 452.6 tps, lat 0.919 ms stddev 0.188, lag 0.151 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 0.910 ms
latency stddev = 0.176 ms
rate limit schedule lag: avg 0.144 (max 5.005) ms
tps = 489.825927 (including connections establishing)
tps = 489.841742 (excluding connections establishing)
statement latencies in milliseconds:
         0.005  \set aid random(1, 100000)
         0.761  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 1000 -P 10 -r -n -f ./tests/pgbench/select.sql -p 5432 -h 127.0.0.1 -U postgres --protocol simple postgres
progress: 10.0 s, 992.3 tps, lat 0.818 ms stddev 0.200, lag 0.140 ms
progress: 20.0 s, 981.8 tps, lat 0.791 ms stddev 0.186, lag 0.131 ms
progress: 30.0 s, 999.3 tps, lat 0.812 ms stddev 0.223, lag 0.138 ms
progress: 40.0 s, 981.9 tps, lat 0.817 ms stddev 0.177, lag 0.137 ms
progress: 50.0 s, 994.2 tps, lat 0.817 ms stddev 0.190, lag 0.137 ms
progress: 60.0 s, 1001.7 tps, lat 0.809 ms stddev 0.173, lag 0.134 ms
progress: 70.0 s, 987.3 tps, lat 0.824 ms stddev 0.164, lag 0.136 ms
progress: 80.0 s, 984.7 tps, lat 0.805 ms stddev 0.281, lag 0.140 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 0.813 ms
latency stddev = 0.203 ms
rate limit schedule lag: avg 0.137 (max 10.048) ms
tps = 977.647184 (including connections establishing)
tps = 977.697291 (excluding connections establishing)
statement latencies in milliseconds:
         0.004  \set aid random(1, 100000)
         0.672  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;



pgbench -t 10000 -c 8 -j 8 -R 1500 -P 10 -r -n -f ./tests/pgbench/select.sql -p 5432 -h 127.0.0.1 -U postgres --protocol simple postgres

progress: 10.0 s, 1508.0 tps, lat 0.795 ms stddev 0.238, lag 0.146 ms
progress: 20.0 s, 1492.6 tps, lat 0.806 ms stddev 0.228, lag 0.148 ms
progress: 30.0 s, 1494.2 tps, lat 0.782 ms stddev 0.243, lag 0.144 ms
progress: 40.0 s, 1504.4 tps, lat 0.751 ms stddev 0.244, lag 0.140 ms
progress: 50.0 s, 1486.2 tps, lat 0.784 ms stddev 0.240, lag 0.145 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 0.785 ms
latency stddev = 0.237 ms
rate limit schedule lag: avg 0.145 (max 10.205) ms
tps = 1485.109252 (including connections establishing)
tps = 1485.248649 (excluding connections establishing)
statement latencies in milliseconds:
         0.004  \set aid random(1, 100000)
         0.637  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 2000 -P 10 -r -n -f ./tests/pgbench/select.sql -p 5432 -h 127.0.0.1 -U postgres --protocol simple postgres

progress: 10.0 s, 2007.8 tps, lat 0.783 ms stddev 0.272, lag 0.158 ms
progress: 20.0 s, 1981.6 tps, lat 0.804 ms stddev 0.385, lag 0.165 ms
progress: 30.0 s, 1999.5 tps, lat 0.782 ms stddev 0.297, lag 0.158 ms
progress: 40.0 s, 1971.6 tps, lat 0.781 ms stddev 0.310, lag 0.159 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 0.788 ms
latency stddev = 0.318 ms
rate limit schedule lag: avg 0.160 (max 11.622) ms
tps = 1969.267080 (including connections establishing)
tps = 1969.528731 (excluding connections establishing)
statement latencies in milliseconds:
         0.004  \set aid random(1, 100000)
         0.624  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 2500 -P 10 -r -n -f ./tests/pgbench/select.sql -p 5432 -h 127.0.0.1 -U postgres --protocol simple postgres

progress: 10.0 s, 2489.5 tps, lat 0.769 ms stddev 0.377, lag 0.168 ms
progress: 20.0 s, 2480.4 tps, lat 0.777 ms stddev 0.365, lag 0.169 ms
progress: 30.0 s, 2478.8 tps, lat 0.764 ms stddev 0.323, lag 0.165 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 0.771 ms
latency stddev = 0.351 ms
rate limit schedule lag: avg 0.167 (max 16.563) ms
tps = 2464.803630 (including connections establishing)
tps = 2465.180036 (excluding connections establishing)
statement latencies in milliseconds:
         0.003  \set aid random(1, 100000)
         0.600  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 2750 -P 10 -r -n -f ./tests/pgbench/select.sql -p 5432 -h 127.0.0.1 -U postgres --protocol simple postgres

progress: 10.0 s, 2783.8 tps, lat 0.777 ms stddev 0.484, lag 0.188 ms
progress: 20.0 s, 2762.8 tps, lat 0.765 ms stddev 0.436, lag 0.183 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 0.770 ms
latency stddev = 0.470 ms
rate limit schedule lag: avg 0.185 (max 18.633) ms
tps = 2723.599950 (including connections establishing)
tps = 2724.045948 (excluding connections establishing)
statement latencies in milliseconds:
         0.003  \set aid random(1, 100000)
         0.582  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 8 -j 8 -R 3000 -P 10 -r -n -f ./tests/pgbench/select.sql -p 5432 -h 127.0.0.1 -U postgres --protocol simple postgres

progress: 10.0 s, 2981.9 tps, lat 0.781 ms stddev 0.494, lag 0.193 ms
progress: 20.0 s, 2977.5 tps, lat 0.766 ms stddev 0.383, lag 0.182 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 0.767 ms
latency stddev = 0.449 ms
rate limit schedule lag: avg 0.186 (max 14.090) ms
tps = 2961.917618 (including connections establishing)
tps = 2962.556426 (excluding connections establishing)
statement latencies in milliseconds:
         0.003  \set aid random(1, 100000)
         0.578  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;



pgbench -t 10000 -c 8 -j 8 -R 5000 -P 10 -r -n -f ./tests/pgbench/select.sql -p 5432 -h 127.0.0.1 -U postgres --protocol simple postgres

progress: 10.0 s, 5021.2 tps, lat 0.811 ms stddev 2.195, lag 0.332 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 80000/80000
latency average = 0.761 ms
latency stddev = 1.766 ms
rate limit schedule lag: avg 0.285 (max 63.918) ms
tps = 4963.955498 (including connections establishing)
tps = 4965.324364 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  \set aid random(1, 100000)
         0.474  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 16 -j 16 -R 1000 -P 30 -r -n -f ./tests/pgbench/select.sql -p 5432 -h 127.0.0.1 -U postgres --protocol simple postgres

progress: 30.0 s, 1006.8 tps, lat 0.837 ms stddev 0.398, lag 0.131 ms
progress: 60.0 s, 1001.2 tps, lat 0.826 ms stddev 0.171, lag 0.122 ms
progress: 90.0 s, 1000.5 tps, lat 0.786 ms stddev 0.218, lag 0.119 ms
progress: 120.0 s, 999.6 tps, lat 0.816 ms stddev 0.193, lag 0.120 ms
progress: 150.0 s, 999.0 tps, lat 0.802 ms stddev 0.216, lag 0.117 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 16
number of transactions per client: 10000
number of transactions actually processed: 160000/160000
latency average = 0.812 ms
latency stddev = 0.252 ms
rate limit schedule lag: avg 0.122 (max 25.322) ms
tps = 985.260238 (including connections establishing)
tps = 985.424482 (excluding connections establishing)
statement latencies in milliseconds:
         0.004  \set aid random(1, 100000)
         0.687  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;


pgbench -t 10000 -c 32 -j 32 -R 1000 -P 160 -r -n -f ./tests/pgbench/select.sql -p 5432 -h 127.0.0.1 -U postgres --protocol simple postgres
progress: 160.0 s, 1000.0 tps, lat 0.804 ms stddev 0.209, lag 0.118 ms
transaction type: ./tests/pgbench/select.sql
scaling factor: 1
query mode: simple
number of clients: 32
number of threads: 32
number of transactions per client: 10000
number of transactions actually processed: 320000/320000
latency average = 0.821 ms
latency stddev = 0.186 ms
rate limit schedule lag: avg 0.119 (max 29.997) ms
tps = 983.402968 (including connections establishing)
tps = 983.461665 (excluding connections establishing)
statement latencies in milliseconds:
         0.004  \set aid random(1, 100000)
         0.697  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
        Edited by Artyom Kartasov
        • Artyom Kartasov
          Artyom Kartasov @akartasov ·
          Author Maintainer

          Routers latency summary

          pgbench configuration:

          • number of transactions each client runs: 10000
          • clients: 8
          • threads: 8
          • protocol: simple

          Setup:

          • 6 cores (Intel Core i5-8400T CPU @ 1.70GHz × 6) - CPU utilization ~15-65%
          • 24 GiB RAM

          Benchmark file simple.sql:

          \set aid random(1, 100000)

SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

          Latency average (ms)

          tps Postgres Pgbouncer SPQR log SPQR PgCat
          500 0.910 1.115 1.266 1.016 1.493
          1000 0.813 1.034 4.236 1.178 1.512
          1500 0.785 1.015 - 1.261 1.517
          2000 0.788 0.990 7.656 1.282 1.573
          2500 0.771 0.971 12.289 1.347 1.664
          2750 0.770 0.972 52.701 1.314 1.797
          3000 0.767 0.979 1249.946 1.418 1.748
          5000 0.761 0.846 - 1.537 2.265

          * Postgres: Docker container (version 13)

          ** Pgbouncer: balancer to Docker container

          Edited by Artyom Kartasov
        • Nikolay Samokhvalov
          Nikolay Samokhvalov @NikolayS ·
          Owner

          Thanks @akartasov, this is really helpful

        • Please register or sign in to reply
      • Anna removed Flow::In Progress label

        removed Flow::In Progress label

      Please register or sign in to reply