[databases]
* = host=postgresql-server port=5432 user=mediacloud

[pgbouncer]

; Actual logging done to STDOUT
logfile = /dev/null

pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
unix_socket_dir = /var/run/postgresql

auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 600
default_pool_size = 600
log_connections = 0
log_disconnections = 0
stats_period = 600
server_login_retry = 1

# PyCharm doesn't work without this one:
# https://github.com/Athou/commafeed/issues/559
ignore_startup_parameters = extra_float_digits

[databases]
* = host=postgresql-server port=5432 user=mediacloud

[pgbouncer]

; Actual logging done to STDOUT
logfile = /dev/null

pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
unix_socket_dir = /var/run/postgresql

auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = session
server_reset_query = DISCARD ALL

# Maximum number of client connections allowed
max_client_conn = 5000

# How many server connections to allow per user/database pair
default_pool_size = 450

# Do not allow more than this many server connections per database (regardless
# of user)
max_db_connections = 500

log_connections = 0
log_disconnections = 0
stats_period = 600
server_login_retry = 1

# PyCharm doesn't work without this one:
# https://github.com/Athou/commafeed/issues/559
ignore_startup_parameters = extra_float_digits

Increase max. PgBouncer client connection count to 4096, limit pool size to 512
Increase max. PgBouncer client connection count to 4096, limit pool size to 512

We run PgBouncer in front of PostgreSQL because PostgreSQL itself is not that good at managing a lot of connections at once:

https://www.percona.com/blog/2018/06/27/scaling-postgresql-with-pgbouncer-you-may-need-a-connection-pooler-sooner-than-you-expect/

Simply put, PgBouncer opens a bunch of connections to PostgreSQL and shares those connections between a large number of clients (containers that we run) more efficiently than PostgreSQL itself would. How many connections will PgBouncer make to the PostgreSQL and how many clients will it let connect to itself is all defined in pgbouncer.ini. Also, PgBouncer's configuration can't go over PostgreSQL's own connection limit which is defined with a `max_connections` property and is currently set at 610 concurrent connections:

https://github.com/mediacloud/backend/blob/59d5f03ca060e78e79a6bd52a0d598626ccaff9a/apps/postgresql-server/conf/postgresql.conf#L11

Some time Friday afternoon (in my timezone), PostgreSQL started complaining about *too many connections*, in which case PostgreSQL stops accepting new attempts to connect (which is bad).

Given that most (but not all) connecting that we do to PostgreSQL is through PgBouncer, I've looked into its configuration and found out that it's not configured properly. If I read the configuration reference correctly:

https://www.pgbouncer.org/config.html

...PgBouncer was configured to both let only up to 600 clients to connect, and to make 600 connections to PostgreSQL itself, pretty much making PgBouncer itself rather useless.

So, I've configured it as follows:

* Now up to 5000 clients will be allowed to connect to PgBouncer (`max_client_conn`)
* Those (up to) 5000 clients will share a pool of up to 450 connections to PostgreSQL (`default_pool_size`)
* That's probably a no-op in our case, but PgBouncer won't create more than 500 connections to PostgreSQL (`max_db_connections`)

That way we let more clients connect at once, and reduce the risk of going over the 610 connection limit in PostgreSQL itself.

This is most likely still not too optimal, proper way to set those parameters would be to do some benchmarking, but that will have to wait for that day when we'll have no other things to do :)

@jtotoole, could you read through that PgBouncer blog post? Any questions are more than welcome too.
