pg_shard and pgbouncer

Skip to first unread message

armand pirvu

Sep 2, 2015, 3:44:42 PM9/2/15
to pg_shard users

one master
two workers

  person_id bigint NOT NULL,
  first_name character varying(64),
  middle_name character varying(64),
  last_name character varying(64),
  title character varying(64),
  prefix character varying(64),
  suffix character varying(64),
  CONSTRAINT person_pkey PRIMARY KEY (person_id)

2 shards per worker
pgbouncer installed on all three of them

postgres = host=localhost dbname=postgres
psharddb = host=localhost dbname=psharddb

listen_addr = *
listen_port = 6432

-bash-4.1$ cat pg_worker_list.conf  6432  6432 

50KinsertsV2.sql just statements like

insert into person values ...

-bash-4.1$ time psql -p 6432 -dpsharddb -q -f 50KinsertsV2.sql > /dev/null

real 0m26.680s
user 0m0.683s
sys 0m0.595s

Whether I use pgbouncer or not same timing

Not sure what I am doing wrong.

Any hints / ideas ?

And if this is not the place, but rather the pgbounce list my apologies

Thank  you
-- Armand

Onder Kalaci

Sep 8, 2015, 9:11:42 AM9/8/15
to pg_shard users
Hey Armand,

First thing to mention is that, pg_shard's INSERT rate increases if you create multiple connections to the master node and execute INSERTs in parallel. However, the psql command you shared is supposed to create a single connection to the master node and execute the INSERTs in the 50KinsertsV2.sql file sequentially. 

Also, pgbouncer is mostly useful when a large number of clients connect to the server. So, with a single connection, I'd not expect that pgbouncer helps us to get better performance. 

My suggestion would be to first parallelize the INSERTs that are executed, then try to measure the difference with pgbouncer.

Hope this helps,

Armand Pirvu (home)

Sep 8, 2015, 9:46:37 PM9/8/15
to Onder Kalaci, pg_shard users
Roger that Onder

For some reason I thought I did not understand the basics and whilst the question was silly , thank you for a concise clarification and confirmation so to speak


You received this message because you are subscribed to the Google Groups "pg_shard users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
To post to this group, send email to
To view this discussion on the web visit
For more options, visit

Reply all
Reply to author
0 new messages