Is HAProxy safe to use for multi-query transactions?

Are there any limitations on when HAProxy can not be used? Let’s say I use the psql client on my laptop to connect to HAProxy which in turns connects to a cockroach cluster. I start a transaction with BEGIN;, then fire of a few statements with a delay of a few seconds in between, then COMMIT. From my understanding, HAProxy might send every query to a different cockroach node. Will the transaction still work? Or do I need to concatenate all queries and send them at once so they’ll be processed by a single node?

Hi @tobias0! Yes HAProxy should work quite well with CockroachDB. In fact the CLI tool includes a cockroach gen command that will generate a config for you. You can follow along with this guide if you want to try a simple demo.

I start a transaction with BEGIN; , then fire of a few statements with a delay of a few seconds in between, then COMMIT . From my understanding, HAProxy might send every query to a different cockroach node. Will the transaction still work? Or do I need to concatenate all queries and send them at once so they’ll be processed by a single node?

Actually, all these commands will go to the same node. The important thing to know is that transactions are scoped to an individual connection. In other words, if the connection is closed, then the transaction can no longer be used anymore – that means the same connection is used the whole time. The HAProxy roundrobin algorithm only selects a new node when a new incoming connection is created. Queries that are sent over an existing connection will all go to the same node. Check out the details on the HAProxy docs.