Add support for pgbouncer or make max connections configurable

[mariusc23]

Hi! Thanks so much for this project. We use it for our marketing site and love how fast and simple it is.

We noticed goatcounter is using a lot of pg connections and was wondering if you can add support for pgbouncer. We deploy our database on DigitalOcean managed databases and would like to use it with their connection pool (pgbouncer).

I tried it in pgbouncer transaction mode and it was not working. I was getting an error like bind message supplies X parameters, but prepared statement "" requires Y. I also tried it in session mode with 4 connections and that worked but it was not stable, frequently hanging. Is there a minimum number of connections you would recommend?

Alternatively, can you make the max connections configurable via an environment variable?

https://github.com/arp242/zdb/blob/master/drivers/pq/pq.go#L78-L79

For context, the cheapest DO managed database supports only 22 connections and the second tier 47, so goatcounter setting a max of 25 can eat up a lot of those.

Thanks, Marius

[arp242]

I don't know why I set this to 25. I think I just put in a number there four years ago before I knew about PostgreSQL's connection woes.

The default should probably be lower; it's intended as a "sane default that should work well for most users", and this clearly isn't that.

Alternatively, can you make the max connections configurable via an environment variable?

This should probably be configurable, yeah. Ideally, being part of the connection string would be best IMO:

goatcounter -db 'postgres+dbname=goatcounter max_open_conn=10 max_idle_conn=5'

But need to parse the string for that. I'll need to have a look if that's feasible, I know the pgx library provides some methods for that, but I avoided migrating to that because it pulls in quite a dependency tree which I want to avoid.

Also adding non-standard things in there maybe isn't the best idea ... need to have a think and see what works.

Otherwise, can add a new flag. I'd like to avoid adding random environment variables, since it's not used for anything else right now.

---

As for pgbouncer, I set it up with:

[~]% tail -n100 pgbouncer.*
==> pgbouncer.auth <==
"martin" "martin"

==> pgbouncer.ini <==
[databases]
template1   = host=/tmp dbname=template1
goatcounter = host=/tmp dbname=goatcounter

[pgbouncer]
unix_socket_dir = /tmp/pgb
admin_users = martin
auth_type = trust
auth_file = pgbouncer.auth

And run it as:

[~]% pgbouncer -v pgbouncer.ini

Connecting with this works:

[~]% psql -h /tmp/pgb -p 6432 goatcounter
()=# select site_id, cname from sites;
 site_id │     cname
─────────┼────────────────
       1 │ new.arp242.net

Time: 1.169 ms

All of this took some time to figure out, so just some notes for future reference.

---

Connecting with GoatCounter doesn't work though:

[~gc](master)% goatcounter serve -db 'postgresql+dbname=goatcounter host=/tmp/pgb port=6432' -dev -listen :8080
goatcounter: zdb.Connect: pq.Connect: pq: unsupported startup parameter: extra_float_digits

It looks like the pq library always adds this; the issue for this mentions a workaround, add ignore_startup_parameters = extra_float_digits to the pgbouncer.ini file, so do that.

After that, running it against pgbouncer with the above command seems to work, at least in a quick test. I did a bunch of different things, and didn't see any errors like you're reporting. So if you have some more information on that (what you did, more details from pgbouncer if it provides any) then I can take a further look.

[arp242]

In the 2.3 release there's a -dbconn flag that you can use to control the number of connections.

Other than that, pgbouncer seemed to work when I tested it (see previous comment), so closing this for now. Let me know if you're still having problems and we can look further.