TLDR How Twitch uses PostgreSQL by Aaron Brashears

Real life with over 100 PG clusters in different regions.

Topology

As for Oct, 2016 Twitch

• has ~125 OLTP database hosts, 94% of it is PostgreSQL
• Some of the old and all the new are RDS
• One cluster (the oldest) serves ~300,000 transactions per second
• i2.8xlarge AWS instances allow to keep write latency down
• uses Auto Scaling Groups for read replicas
• uses ELB, but clients which cannot use the ELB use a local HAProxy

Some client applications remain in our own datacenter. Most are not sensitive to replication lag, so we set up read replicas on our own hardware to reduce query latency. To reduce replication bandwidth from AWS to our datacenter, there are only two first-level replication streams. From there, the replication cascades.

This lets clients have a query in a millisecond rather than a millisecond plus a 30ms round trip between DCs.

• each datacenter has a host which is not part of the live read set, to run expensive ad-hoc queries and reports which are not sensitive to stale data. hot_standby_feedback = off and max_standby_streaming_delay = 6h

Over the years, we frequently shipped code which ran expensive queries, for example an aggregation or a sequence scan. To address this, we set a time limit per statement with an appropriate statement_timeout for every role.

PGBouncer

• We provide a virtual schema name which aliases the database schema.
• Each schema is limited in number of connections, so clients can exhaust only their own connection pool. Example pgbouncer.ini

[databases]
site_sitedb = dbname=sitedb host=127.0.0.1 pool_size=70 port=5432
sso_sitedb = dbname=sitedb host=127.0.0.1 pool_size=8 port=5432

At one point, CPU was hitting 100% running PGBouncer

• We have configured 2 PgBouncer processes, each listening on a different port and thus splitting the load. A HAProxy is proxying to both of them.
• Same balancing configuration is on Primary / Master database.

At one point, there were two PGBouncers in front of the master — one in AWS and one in our DC. There were significant performance issues with the proxy when the cluster was in a different region than the master. Now we’re using HAProxy to communicate between the regions and only rely on the closer PGBouncer cluster.

Problems

• we saw a lot of “canceling statement due to conflict with recovery” errors — added statement_timeout and setting hot_standby_feedback=on .
• max_connections setting is encoded in the replication stream, so you cannot stream from a master with a higher value than on replica.
• Major Upgrades, 9.4 to 9.5 for example, require a long downtime or a logical replication stream. During the last one we used slony for logical replication — weeks of planning and downtime was only a minute, though it was a real burden.

Try okmeter.io — a monitoring solution that will show you everything about Postgresql and PgBouncer operations.