In our previous blog post, we showed how the performance of repeating SELECT queries can be improved by leveraging an external cache. As shown in the figure below, in our simple experiment, the response time of a repeating SELECT query improved by 86% with around 20% degradation in first time execution of cacheable queries, which seems an acceptable trade-off for workloads with often repeating queries.    In this blog post, we provide steps and scripts describing how to install and setup Pgpool on an Ubuntu VM to act as a connection pooler and query cache to an Azure Database for PostgreSQL server. Memcached is used as the backing cache store as shown below. At the end of the blog post, we share a Quickstart ARM template which allows you to quickly provision an Ubuntu VM with Pgpool-II preconfigured with an Azure Database for PostgreSQL server for quick proof-of-concept testing.   In our Quickstart template below, Pgpool query cache is configured with default settings. One exception is using Memcached instead of shared memory option as the backing cache. You can fine-tune additional query caching settings based on your workload and expertise.   Note that if you don't require Pgpool's unique features like query caching, we recommend using a simpler connection pooler like PgBouncer with Azure Database for PostgreSQL.   The steps below represent an example scenario that you can modify to your needs by changing configuration settings in pgpool.conf (on Ubuntu, the Pgpool config file is located at /etc/pgpool2/pgpool.conf), followed by a Pgpool restart to pick them up: service pgpool2 restart    Steps to manually setup Pgpool on Ubuntu VM Steps below provide a setup example. The complete bash script for the steps below can be download from our Github repository. (If you want to use the quick ARM template instead, scroll to the next section). We assume the existence of three environment variables: SERVERNAME: Short name of Azure PostgreSQL server, e.g. pgtestsrvUSERNAME: Short username (without @servername) of Azure PostgreSQL server, e.g. pguserPASSWORD: Password of Azure PostgreSQL user # Install Pgpool II and Memcached apt-get -y install pgpool2 memcached # Pgpool query cache requires a work directory to record table oids mkdir -p /var/log/pgpool/oiddir chown postgres:postgres /var/log/pgpool/oiddir # Before modifying Pgpool and Memcached config files, save them cp /etc/pgpool2/pgpool.conf /etc/pgpool2/pgpool.conf.save cp /etc/pgpool2/pool_hba.conf /etc/pgpool2/pool_hba.conf.save cp /etc/memcached.conf /etc/memcached.conf.save # Create self-signed certificate to allow connecting to Pgpool using SSL # Note: Adjust this step to meet your security requirements openssl req -new -newkey rsa:4096 -days 3650 -nodes -x509 -subj "/C=US/ST=Denial/L=Seattle/O=Dis/CN=www.pgpooltest.org" -keyout /etc/pgpool2/server.key -out /etc/pgpool2/server.crt # Modify Pgpool config # Turn SSL on sed -i 's/ssl = off/ssl = on/g' /etc/pgpool2/pgpool.conf # Use SSL key and cert created above sed -i "s/#ssl_key = '.\/server.key'/ssl_key = '\/etc\/pgpool2\/server.key'/g" /etc/pgpool2/pgpool.conf sed -i "s/#ssl_cert = '.\/server.cert'/ssl_cert = '\/etc\/pgpool2\/server.crt'/g" /etc/pgpool2/pgpool.conf # Accept all incoming connections sed -i "s/listen_addresses = 'localhost'/listen_addresses = '*'/g" /etc/pgpool2/pgpool.conf # Set Azure PostgreSQL backend sed -i "s/backend_hostname0 = 'localhost'/backend_hostname0 = '$SERVERNAME.postgres.database.azure.com'/g" /etc/pgpool2/pgpool.conf # Use pool_hba.conf for client authentication sed -i 's/enable_pool_hba = off/enable_pool_hba = on/g' /etc/pgpool2/pgpool.conf # Enable query cache sed -i "s/memory_cache_enabled = off/memory_cache_enabled = on/g" /etc/pgpool2/pgpool.conf # Use Memcached as query cache store sed -i "s/memqcache_method = 'shmem'/memqcache_method = 'memcached'/g" /etc/pgpool2/pgpool.conf # Require all clients connecting to Pgpool to authenticate with password (scram-sha-256 in this case) echo "host all all 0.0.0.0/0 scram-sha-256" > /etc/pgpool2/pool_hba.conf # Write pool_passwd used by Pgpool for password authentication with Azure PostgreSQL backend echo $USERNAME@$SERVERNAME:$PASSWORD > /etc/pgpool2/pool_passwd chmod 600 /etc/pgpool2/pool_passwd chown postgres:postgres /etc/pgpool2/* # Modify Memcached config # Allow Memcached to use 1024 MB of memory sed -i "s/m 64/m 1024/g" /etc/memcached.conf # Start Pgpool and Memcached services # Start Memcached service memcached restart # Set defaults for Pgpool service echo "PGPOOL_CONFIG_FILE=/etc/pgpool2/pgpool.conf" >> /etc/default/pgpool2 echo "PGPOOL_HBA_CONFIG_FILE=/etc/pgpool2/pool_hba.conf" >> /etc/default/pgpool2 echo "PGPOOL_PCP_CONFIG_FILE=/etc/pgpool2/pcp.conf" >> /etc/default/pgpool2 echo "PGPOOL_PID_FILE=/var/run/postgresql/pgpool.pid" >> /etc/default/pgpool2 update-rc.d pgpool2 defaults # Start Pgpool service pgpool2 restart   Deploy Pgpool using Quickstart ARM template If you would like to skip the above steps and want to quickly setup and test Pgpool with Azure DB for PostgreSQL, you can click on Deploy button below. It will take you to an ARM template form. Once you provide all the parameter values, it will provision an Ubuntu VM with Pgpool-II installed, configured and running, connected to an Azure Database for PostgreSQL server. Note that the template uses a self-signed certificate to enable SSL connections to Pgpool, which is something you might want to revisit for production use. The template is available in our GitHub repository.   Once you have deployed the ARM Template successfully without any errors, you will be able to see the hostname, sshCommand and psqlCommand as part of Deployment Outputs.     hostname is the Public DNS for the Ubuntu VM hosting Pgpool and Memcached sshCommand provides the ssh command to connect to the Ubuntu VM hosting Pgpool and MemcachedpsqlCommand provides the psql command to connect the PostgreSQL Server   psql "host={dnsLabelPrefix}.{region}.cloudapp.azure.com port=5432 dbname={your_database} user={your_username}" For Example: psql "host=pgpoolvm.westus2.cloudapp.azure.com port=5432 dbname=postgres user=pguser@pgtestsrv"   Hope this helps!   Benjamin SatzgerPrincipal Software Engineer, Microsoft   Parikshit SavjaniSenior Program Manager, Microsoft